Generisk formel
=FILTER(data,(range>=LARGE(IF(criteria),n))*(criteria))
Resumé
For at filtrere data for at vise de øverste n-værdier, der opfylder specifikke kriterier, kan du bruge FILTER-funktionen sammen med LARGE og IF-funktionerne. I det viste eksempel er formlen i F5:
=FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b"))
hvor data (B5: D16), gruppe (C5: C16) og score (D5: D16) er navngivet områder.
Forklaring
Denne formel bruger FILTER-funktionen til at hente data baseret på en logisk test konstrueret med LARGE og IF-funktionerne. Resultatet er de 3 bedste scores i gruppe B.
FILTER-funktionen anvender kriterier med inkluderingsargumentet. I dette eksempel er kriterier konstrueret med boolsk logik som denne:
(score>=LARGE(IF(group="b",score),3))*(group="b")
Venstre side af udtrykket målretter scoringer større end eller lig med den 3. højeste score i gruppe B:
score>=LARGE(IF(group="b",score),3)
IF-funktionen bruges til at sikre, at LARGE kun arbejder med gruppe B-scores. Fordi vi har 12 scoringer i alt, returnerer IF en matrix med 12 resultater som denne:
(FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83)
Bemærk, at de eneste scores, der overlever operationen, er fra gruppe B. Alle andre scores er FALSE. Denne matrix returneres direkte til LARGE som arrayargumentet:
LARGE((FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83),3)
LARGE ignorerer de FALSE værdier og returnerer den 3. højeste score, 83.
Vi kan nu forenkle formlen til:
=FILTER(data,(score>=83)*(group="b"))
der løser:
=FILTER(data,(0;0;0;0;0;1;0;0;0;1;0;1))
Endelig returnerer FILTER poster for Mason, Annie og Cassidy, der spilder ind i området F5: H7.
Sorter resultater efter score
FILTER returnerer som standard matchende poster i samme rækkefølge som de vises i kildedataene. For at sortere resultater i faldende rækkefølge efter score kan du rede den originale FILTER-formel inde i SORT-funktionen på denne måde:
=SORT(FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b")),3,-1)
Her returnerer FILTER resultater direkte til SORT-funktionen som matrixargument. Sorteringsindeks er indstillet til 3 (score) og sorteringsrækkefølge er indstillet til -1 for faldende rækkefølge.
