Ladda ner exempelarbetsboken
Denna handledning kommer att visa hur man beräknar "percentilen om" och hämtar kth percentil i ett raseri av värden med kriterier.
PERCENTIL Funktion
Funktionen PERCENTIL används för att beräkna kth procent av värden i ett område där k är percentilvärdet mellan 0 och 1 inklusive.
= PERCENTIL ($ D $ 2: $ D $ 10,0,75)
Detta tar emellertid percentilen för hela värdeintervallet. I stället, för att skapa en "Percentil Om", använder vi funktionen PERCENTILE tillsammans med IF -funktionen i en matrisformel.
PERCENTIL OM
Genom att kombinera PERCENTILE och IF i en matrisformel kan vi i huvudsak skapa en "PERCENTLE IF" -funktion som fungerar ungefär som hur den inbyggda AVERAGEIF-funktionen fungerar. Låt oss gå igenom ett exempel.
Vi har en lista över poäng som elever uppnått i två olika ämnen:
Antag att vi blir ombedda att hitta 75: anth procentiler av poäng som uppnås för varje ämne så här:
För att åstadkomma detta kan vi häcka en IF -funktion med ämne som våra kriterier inuti PERCENTILE fungerar så här:
= PERCENTIL (OM (=,),)
= PERCENTIL (OM ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), 0,75)
När du använder Excel 2022 och tidigare måste du ange matrisformeln genom att trycka på CTRL + SKIFT + ENTER (istället för STIGA PÅ), berättar för Excel att formeln i en matrisformel. Du vet att det är en matrisformel av de lockiga parenteserna som visas runt formeln (se översta bilden).
Hur fungerar formeln?
If -funktionen utvärderar varje cell i vårt kriterieintervall som TRUE eller FALSE och skapar två matriser:
= PERCENTIL (OM ({SANT; FALSKT; FALSKT; SANT; FALSKT; FALSKT; FALSKT}, {0,99; 0,8; 0,93; 0,42; 0,87; 0,63; 0,71; 0,58; 0,73}), 0,75)
Därefter skapar IF -funktionen en enda array som ersätter varje värde med FALSE om dess villkor inte uppfylls.
= PERCENTIL ({0,81; FALSKT; FALSKT; 0,42; FALSKT; 0,63; FALSKT; 0,58; FALSKT}, 0,75)
Nu hoppar PERCENTILE -funktionen över FALSE -värdena och beräknar 75th percentilen av de återstående värdena (0,72 är 75th procentil).
PERCENTILE OM med flera kriterier
För att beräkna PERCENTILE OM med flera kriterier (liknande hur den inbyggda AVERAGEIFS-funktionen fungerar) kan du helt enkelt multiplicera kriterierna tillsammans:
= PERCENTIL (OM ((=)*(=),),)
= PERCENTIL (OM (($ D $ 2: $ D $ 10 = $ H2)*($ C $ 2: $ C $ 10 = $ G2), $ E $ 2: $ E $ 10), 0.75)
Ett annat sätt att inkludera flera kriterier är att fästa fler IF -uttalanden i formeln.
Tips och tricks:
- Om möjligt, hänvisa alltid till positionen (k) från en hjälparcell och låsreferens (F4) eftersom detta kommer att göra formler för automatisk fyllning enklare.
- Om du använder Excel 2022 eller senare kan du ange formeln utan CTRL + SKIFT + ENTER.
- För att hämta namnen på elever som uppnådde toppbetyg, kombinera med detta med INDEX MATCH.
PERCENTIL OM i Google Kalkylark
Funktionen PERCENTILE IF fungerar exakt samma i Google Kalkylark som i Excel:
