Generisk formel
=FILTER(data,(range>=LARGE(IF(criteria),n))*(criteria))
Sammendrag
For å filtrere data for å vise de øverste n-verdiene som oppfyller spesifikke kriterier, kan du bruke FILTER-funksjonen sammen med STOR og IF-funksjonene. I eksemplet vist er formelen i F5:
=FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b"))
der data (B5: D16), gruppe (C5: C16) og poengsum (D5: D16) er kalt områder.
Forklaring
Denne formelen bruker FILTER-funksjonen for å hente data basert på en logisk test konstruert med STOR og IF-funksjonene. Resultatet er de 3 beste resultatene i gruppe B.
FILTER-funksjonen bruker kriterier med argumentet inkluderer. I dette eksemplet er kriterier konstruert med boolsk logikk som dette:
(score>=LARGE(IF(group="b",score),3))*(group="b")
Venstre side av uttrykket retter seg mot poeng større eller lik den 3. høyeste poengsummen i gruppe B:
score>=LARGE(IF(group="b",score),3)
IF-funksjonen brukes til å sikre at LARGE bare fungerer med gruppe B-poeng. Fordi vi har 12 poengsum totalt, returnerer IF en matrise med 12 resultater slik:
(FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83)
Legg merke til at de eneste resultatene som overlever operasjonen er fra gruppe B. Alle andre poeng er FALSE. Denne matrisen returneres direkte til STOR som arrayargumentet:
LARGE((FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83),3)
LARGE ignorerer FALSE-verdiene og returnerer 3. høyeste poengsum, 83.
Vi kan nå forenkle formelen til:
=FILTER(data,(score>=83)*(group="b"))
som løser å:
=FILTER(data,(0;0;0;0;0;1;0;0;0;1;0;1))
Til slutt returnerer FILTER poster for Mason, Annie og Cassidy, som renner ut i området F5: H7.
Sorter resultatene etter poengsum
FILTER returnerer som standard samsvarende poster i samme rekkefølge som de vises i kildedataene. For å sortere resultatene i synkende rekkefølge etter poengsum, kan du hekke den originale FILTER-formelen i SORT-funksjonen slik:
=SORT(FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b")),3,-1)
Her returnerer FILTER resultatene direkte til SORT-funksjonen som array-argument. Sorteringsindeks er satt til 3 (score) og sorteringsrekkefølge er satt til -1, for synkende rekkefølge.
