Download eksemplet på projektmappe
Denne vejledning vil demonstrere, hvordan man beregner "percentil if" ved at hente kth percentil i et raseri af værdier med kriterier.
PERCENTILE Funktion
PERCENTILE -funktionen bruges til at beregne kth percentil af værdier i et område, hvor k er percentilværdien mellem 0 og 1 inklusive.
= PERCENTIL ($ D $ 2: $ D $ 10,0,75)
Dette tager imidlertid percentilen af hele værdiområdet. I stedet vil vi bruge PERCENTILE -funktionen sammen med IF -funktionen i en matrixformel for at oprette en “Percentil hvis”.
PERCENTILE IF
Ved at kombinere PERCENTILE og IF i en matrixformel kan vi i det væsentlige oprette en "PERCENTLE IF" -funktion, der fungerer på samme måde som den indbyggede AVERAGEIF-funktion. Lad os gå gennem et eksempel.
Vi har en liste over scoringer opnået af elever i to forskellige fag:
Antages, at vi bliver bedt om at finde 75th percentiler scoringer opnået for hvert emne sådan:
For at opnå dette kan vi indlejre en IF -funktion med emne som vores kriterier inde i PERCENTILE fungerer sådan:
= PERCENTIL (HVIS (=,),)
= PERCENTIL (HVIS ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), 0,75)
Når du bruger Excel 2022 og tidligere, skal du indtaste matrixformlen ved at trykke på CTRL + SKIFT + ENTER (i stedet for GÅ IND), fortæller Excel, at formlen i en matrixformel. Du ved, at det er en matrixformel af de krøllede parenteser, der vises omkring formlen (se øverste billede).
Hvordan fungerer formlen?
If -funktionen evaluerer hver celle i vores kriterieinterval som SAND eller FALSK og skaber to arrays:
= PERCENTIL (HVIS ({SAND; FALSK; FALSK; SAND; FALSK; FALSK; SAND; FALSK}, {0,99; 0,8; 0,93; 0,42; 0,87; 0,63; 0,71; 0,58; 0,73}), 0,75)
Dernæst opretter IF -funktionen et enkelt array, der erstatter hver værdi med FALSK, hvis betingelsen ikke er opfyldt.
= PERCENTIL ({0,81; FALSK; FALSK; 0,42; FALSK; 0,63; FALSK; 0,58; FALSK}, 0,75)
Nu springer funktionen PERCENTILE over de FALSKE værdier og beregner 75th percentil af de resterende værdier (0,72 er 75th percentil).
PERCENTILE HVIS med flere kriterier
For at beregne PERCENTILE HVIS med flere kriterier (svarende til hvordan den indbyggede AVERAGEIFS-funktion fungerer), kan du simpelthen gange kriterierne sammen:
= PERCENTIL (HVIS ((=)*(=),),)
= PERCENTIL (HVIS (($ D $ 2: $ D $ 10 = $ H2)*($ C $ 2: $ C $ 10 = $ G2), $ E $ 2: $ E $ 10), 0,75)
En anden måde at inkludere flere kriterier på er at indlejre flere IF -sætninger i formlen.
Tips og tricks:
- Hvor det er muligt, skal du altid henvise til positionen (k) fra en hjælpercelle og låsehenvisning (F4), da dette vil gøre formler til automatisk udfyldning lettere.
- Hvis du bruger Excel 2022 eller nyere, kan du indtaste formlen uden CTRL + SKIFT + ENTER.
- For at hente navne på elever, der opnåede topkarakterer, kombineres med dette med INDEX MATCH.
PERCENTILE IF i Google Sheets
Funktionen PERCENTILE IF fungerer nøjagtig det samme i Google Sheets som i Excel:
