Generisk formel
=FILTER(data,(range>=LARGE(IF(criteria),n))*(criteria))
Sammanfattning
För att filtrera data för att visa de bästa n-värdena som uppfyller specifika kriterier kan du använda FILTER-funktionen tillsammans med LARGE och IF-funktionerna. I exemplet som visas är formeln i F5:
=FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b"))
där data (B5: D16), grupp (C5: C16) och poäng (D5: D16) heter intervall.
Förklaring
Denna formel använder FILTER-funktionen för att hämta data baserat på ett logiskt test konstruerat med LARGE- och IF-funktionerna. Resultatet är de tre bästa poängen i grupp B.
FILTER-funktionen tillämpar kriterier med argumentet include. I detta exempel är kriterier konstruerade med boolesk logik så här:
(score>=LARGE(IF(group="b",score),3))*(group="b")
Vänster sida av uttrycket riktar sig mot poäng som är större än eller lika med den 3: e högsta poängen i grupp B:
score>=LARGE(IF(group="b",score),3)
IF-funktionen används för att se till att LARGE bara arbetar med grupp B-poäng. Eftersom vi har totalt 12 poäng returnerar IF en matris med 12 resultat så här:
(FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83)
Lägg märke till att de enda poängen som överlever operationen är från grupp B. Alla andra poäng är FALSKA. Denna matris returneras direkt till STOR som arrayargumentet:
LARGE((FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83),3)
LARGE ignorerar FALSE-värdena och returnerar den 3: e högsta poängen, 83.
Vi kan nu förenkla formeln till:
=FILTER(data,(score>=83)*(group="b"))
som löser sig att:
=FILTER(data,(0;0;0;0;0;1;0;0;0;1;0;1))
Slutligen returnerar FILTER skivor för Mason, Annie och Cassidy, som rinner ut i intervallet F5: H7.
Sortera resultat efter poäng
FILTER returnerar som standard matchande poster i samma ordning som de visas i källdata. För att sortera resultat i fallande ordning efter poäng kan du bygga in den ursprungliga FILTER-formeln i SORT-funktionen så här:
=SORT(FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b")),3,-1)
Här returnerar FILTER resultat direkt till SORT-funktionen som arrayargument. Sorteringsindex är inställt på 3 (poäng) och sorteringsordning är satt till -1 för fallande ordning.
