Download het voorbeeldwerkboek
Deze zelfstudie laat zien hoe u "grote als" of "kleine als" kunt berekenen, waarbij de op één na grootste (of kleinste) waarde wordt opgehaald op basis van criteria.
GROTE & KLEINE Functies
De LARGE-functie wordt gebruikt om de n-de grootste waarde (k) in een array te berekenen, terwijl de SMALL-functie de kleinste n-waarde retourneert.
Om een "Large If" te maken, gebruiken we de LARGE-functie samen met de IF-functie in een matrixformule.
GROTE ALS
Door LARGE (of SMALL) en IF te combineren in een matrixformule, kunnen we in wezen een "LARGE IF" -functie maken die vergelijkbaar is met hoe de ingebouwde SUMIF-formule werkt. Laten we een voorbeeld doornemen.
We hebben een lijst met cijfers die studenten hebben behaald in twee verschillende vakken:
Stel dat we worden gevraagd om de top drie behaalde cijfers voor elk onderwerp te vinden, zoals zo:
Om dit te bereiken, kunnen we een ALS-functie nesten met de onderwerp als onze criteria in de LARGE-functie als volgt:
=GROOT(ALS(=, ),)
=GROOT(ALS($C$2:$C$10=$F3,$D$2:$D$10),G$2)
Wanneer u Excel 2022 en eerder gebruikt, moet u de formule invoeren door op . te drukken CTRL + SHIFT + ENTER om de accolades rond de formule te krijgen.
Hoe werkt de formule?
De formule werkt door elke cel in ons criteriabereik te evalueren als WAAR of ONWAAR.
De hoogste cijferwaarde (k=1) vinden in Math:
=GROOT(ALS($C$2:$C$10=$F3,$D$2:$D$10),G$2)
=GROOT(ALS({WAAR; ONWAAR; ONWAAR; WAAR; ONWAAR; WAAR; ONWAAR; WAAR; ONWAAR}, {0,81; 0,8; 0,93; 0,42; 0,87; 0,63; 0,71; 0,58; 0,73}), 1)
Vervolgens vervangt de IF-functie elke waarde door FALSE als niet aan de voorwaarde wordt voldaan.
=GROOTTE({0.81;FALSE;FALSE;0.42;FALSE;0.63;FALSE;0.58;FALSE},1)
Nu slaat de LARGE-functie de FALSE-waarden over en berekent de grootste (k=1) van de resterende waarden (0,81 is de grootste waarde tussen 0,42 en 0,81).
KLEINE ALS
Dezelfde techniek kan in plaats daarvan ook worden toegepast met de SMALL-functie.
=KLEIN(ALS($C$2:$C$10=$F3,$D$2:$D$10),G$2)
LARGE IF met meerdere criteria
Om GROTE ALS met meerdere criteria te gebruiken (vergelijkbaar met hoe de ingebouwde SOMMEN-formule werkt), nest u gewoon meer ALS-functies in de LARGE-functie als volgt:
=GROOT(ALS(=, ALS(=, )),)
=GROOT(IF($D$2:$D$18=$H3,IF($B$2:$B$18=$G3,$E$2:$E$18)),I$2)
Een andere manier om meerdere criteria op te nemen, is door de criteria met elkaar te vermenigvuldigen, zoals weergegeven in dit artikel
Tips en trucs:
- Verwijs waar mogelijk altijd naar de positie (k) vanuit een helpercel en vergrendel verwijzing (F4) omdat dit het automatisch invullen van formules gemakkelijker maakt.
- Als u Excel 2022 of nieuwer gebruikt, kunt u de formule invoeren zonder Ctrl + Shift + Enter.
- Combineer dit met INDEX MATCH . om de namen op te halen van studenten die de hoogste cijfers hebben behaald
