Generieke formule
=FILTER(data,(range>=LARGE(IF(criteria),n))*(criteria))
Samenvatting
Als u gegevens wilt filteren om de bovenste n waarden weer te geven die aan specifieke criteria voldoen, kunt u de functie FILTER samen met de functies LARGE en IF gebruiken. In het getoonde voorbeeld is de formule in F5:
=FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b"))
waarbij gegevens (B5: D16), groep (C5: C16) en score (D5: D16) benoemde bereiken zijn.
Uitleg
Deze formule gebruikt de FILTER-functie om gegevens op te halen op basis van een logische test die is opgebouwd met de LARGE- en IF-functies. Het resultaat is de top 3 scores in groep B.
De FILTER-functie past criteria toe met het include-argument. In dit voorbeeld worden criteria als volgt geconstrueerd met booleaanse logica:
(score>=LARGE(IF(group="b",score),3))*(group="b")
De linkerkant van de uitdrukking richt zich op scores die groter zijn dan of gelijk zijn aan de op twee na hoogste score in groep B:
score>=LARGE(IF(group="b",score),3)
De ALS-functie wordt gebruikt om ervoor te zorgen dat LARGE alleen werkt met scores van groep B. Omdat we in totaal 12 scores hebben, retourneert IF een array met 12 resultaten als volgt:
(FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83)
Merk op dat de enige scores die de operatie overleven, afkomstig zijn uit groep B. Alle andere scores zijn FALSE. Deze array wordt direct teruggestuurd naar LARGE als het array-argument:
LARGE((FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83),3)
LARGE negeert de FALSE-waarden en retourneert de op twee na hoogste score, 83.
We kunnen de formule nu vereenvoudigen om:
=FILTER(data,(score>=83)*(group="b"))
die besluit:
=FILTER(data,(0;0;0;0;0;1;0;0;0;1;0;1))
Ten slotte retourneert FILTER records voor Mason, Annie en Cassidy, die in het bereik F5: H7 terechtkomen.
Sorteer resultaten op score
FILTER retourneert standaard overeenkomende records in dezelfde volgorde waarin ze in de brongegevens staan. Om resultaten in aflopende volgorde op score te sorteren, kunt u de originele FILTER-formule als volgt in de SORT-functie nesten:
=SORT(FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b")),3,-1)
Hier retourneert FILTER resultaten rechtstreeks naar de functie SORT als het matrixargument. Sorteerindex is ingesteld op 3 (score) en sorteervolgorde is ingesteld op -1, voor aflopende volgorde.
