Download het voorbeeldwerkboek
Deze tutorial laat zien hoe je "percentiel als" kunt berekenen, waarbij de k . wordt opgehaalde percentiel in een woede van waarden met criteria.
PERCENTIEL Functie
De PERCENTIEL-functie wordt gebruikt om de k . te berekenene percentiel van waarden in een bereik waarbij k is de percentielwaarde tussen 0 en 1 inclusief.
=PERCENTIEL($D$2:$D$10,0.75)
Dit neemt echter het percentiel van het gehele waardenbereik in beslag. Om een "percentiel als" te maken, gebruiken we in plaats daarvan de PERCENTIEL-functie samen met de ALS-functie in een matrixformule.
PERCENTIEL ALS
Door PERCENTIEL en ALS te combineren in een matrixformule, kunnen we in wezen een "PERCENTIEL ALS" -functie maken die vergelijkbaar is met hoe de ingebouwde AVERAGEIF-functie werkt. Laten we een voorbeeld doornemen.
We hebben een lijst met scores die studenten hebben behaald in twee verschillende vakken:
Stel dat we worden gevraagd om de 75 . te vindene percentielen van behaalde scores voor elk onderwerp, zoals:
Om dit te bereiken, kunnen we een ALS-functie nesten met de onderwerp als onze criteria binnen de PERCENTIEL functie als volgt:
=PERCENTIEL(ALS(=, ),)
=PERCENTIEL(IF($C$2:$C$10=$F3,$D$2:$D$10),0,75)
Wanneer u Excel 2022 en eerder gebruikt, moet u de matrixformule invoeren door op te drukken CTRL + SHIFT + ENTER (in plaats van BINNENKOMEN), Excel vertellen dat de formule in een matrixformule. U weet dat het een matrixformule is aan de accolades die rond de formule verschijnen (zie bovenste afbeelding).
Hoe werkt de formule?
De If-functie evalueert elke cel in ons criteriabereik als TRUE of FALSE, waardoor twee arrays worden gemaakt:
=PERCENTIEL(ALS({WAAR; ONWAAR; ONWAAR; WAAR; ONWAAR; WAAR; ONWAAR; WAAR; ONWAAR}, {0,99; 0,8; 0,93; 0,42; 0,87; 0,63; 0,71; 0,58; 0,73}), 0,75)
Vervolgens maakt de IF-functie een enkele array, waarbij elke waarde wordt vervangen door FALSE als niet aan de voorwaarde wordt voldaan.
=PERCENTIEL({0.81;FALSE;FALSE;0.42;FALSE;0.63;FALSE;0.58;FALSE}; 0.75)
Nu slaat de PERCENTIEL-functie de FALSE-waarden over en berekent de 75e percentiel van de resterende waarden (0,72 is de 75e percentiel).
PERCENTIEL ALS met meerdere criteria
Om PERCENTIEL ALS te berekenen met meerdere criteria (vergelijkbaar met hoe de ingebouwde AVERAGEIFS-functie werkt), kunt u de criteria eenvoudig met elkaar vermenigvuldigen:
=PERCENTIEL(ALS((=)*(=),),)
=PERCENTIEL(IF(($D$2:$D$10=$H2)*($C$2:$C$10=$G2),$E$2:$E$10),0,75)
Een andere manier om meerdere criteria op te nemen, is door meer IF-instructies in de formule te nesten.
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.
PERCENTIEL ALS in Google Spreadsheets
De PERCENTIEL ALS-functie werkt precies hetzelfde in Google Spreadsheets als in Excel:
