Last ned eksemplet på arbeidsbok
Denne opplæringen vil demonstrere hvordan du beregner "prosentil hvis", og henter kth prosentil i et raseri av verdier med kriterier.
PERSENTIL Funksjon
PERCENTILE -funksjonen brukes til å beregne kth prosentil av verdier i et område hvor k er prosentilen mellom 0 og 1 inkludert.
= PERSENTIL ($ D $ 2: $ D $ 10,0,75)
Dette tar imidlertid prosentilen av hele verdiområdet. I stedet, for å opprette en “prosentandel hvis”, bruker vi PERCENTILE -funksjonen sammen med IF -funksjonen i en matriseformel.
PERCENTILE IF
Ved å kombinere PERCENTILE og IF i en matriseformel, kan vi i hovedsak lage en "PERCENTLE IF" -funksjon som fungerer omtrent som den innebygde AVERAGEIF-funksjonen. La oss gå gjennom et eksempel.
Vi har en liste over poengsummer oppnådd av studenter i to forskjellige fag:
Antatt at vi blir bedt om å finne 75th prosentiler av score oppnådd for hvert emne slik:
For å oppnå dette kan vi hekke en IF -funksjon med Emne som våre kriterier inne i PERCENTILE -funksjonen slik:
= PERSENTIL (HVIS (=,),)
= PERSENTIL (HVIS ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), 0,75)
Når du bruker Excel 2022 og tidligere, må du angi matriseformelen ved å trykke CTRL + SKIFT + ENTER (i stedet for TAST INN), og forteller Excel at formelen i en matriseformel. Du vet at det er en matriseformel av de krøllete parentesene som vises rundt formelen (se bildet øverst).
Hvordan fungerer formelen?
If -funksjonen evaluerer hver celle i våre kriterier som TRUE eller FALSE, og oppretter to matriser:
= PERSENTIL (HVIS ({SANN; FALSK; FALSK; SANN; FALSK; FALSK; SANN; FALSK}, {0,99; 0,8; 0,93; 0,42; 0,87; 0,63; 0,71; 0,58; 0,73}), 0,75)
Deretter oppretter IF -funksjonen en enkelt matrise, som erstatter hver verdi med FALSK hvis betingelsen ikke er oppfylt.
= PERSENTIL ({0,81; FALSK; FALSK; 0,42; FALSK; 0,63; FALSK; 0,58; FALSK}, 0,75)
Nå hopper PERCENTILE -funksjonen over FALSE -verdiene og beregner 75th prosentilen av de gjenværende verdiene (0,72 er 75th prosentil).
PERCENTILE IF med flere kriterier
For å beregne PERCENTILE HVIS med flere kriterier (ligner på hvordan den innebygde AVERAGEIFS-funksjonen fungerer), kan du ganske enkelt multiplisere kriteriene sammen:
= PERSENTIL (HVIS ((=)*(=),),)
= PERSENTIL (HVIS (($ D $ 2: $ D $ 10 = $ H2)*($ C $ 2: $ C $ 10 = $ G2), $ E $ 2: $ E $ 10), 0.75)
En annen måte å inkludere flere kriterier på er å nest flere IF -setninger i formelen.
Tips og triks:
- Der det er mulig, må du alltid referere til posisjonen (k) fra en hjelpecelle og låsehenvisning (F4), da dette vil gjøre formler for automatisk fylling enklere.
- Hvis du bruker Excel 2022 eller nyere, kan du skrive inn formelen uten CTRL + SKIFT + ENTER.
- For å hente navnene på elevene som oppnådde toppkarakterene, kombiner med dette med INDEX MATCH.
PERCENTILE IF i Google Regneark
PERCENTILE IF -funksjonen fungerer nøyaktig det samme i Google Sheets som i Excel:
