Prenesite primer delovnega zvezka
Ta vadnica bo pokazala, kako izračunati "percentil if" in pridobiti kth percentil v besu vrednot s kriteriji.
PERCENTILNA funkcija
PERCENTILNA funkcija se uporablja za izračun kth percentil vrednosti v območju, kjer k je vrednost percentila med 0 in vključno 1.
= PERCENTILNO ($ D $ 2: $ D $ 10,0.75)
Vendar to vzame percentil celotnega obsega vrednosti. Namesto tega bomo za oblikovanje »percentila If« uporabili funkcijo PERCENTILE skupaj s funkcijo IF v formuli matrike.
PERCENTILNO ČE
S kombinacijo PERCENTILE in IF v formuli matrike lahko v bistvu ustvarimo funkcijo »PERCENTLE IF«, ki deluje podobno, kot deluje vgrajena funkcija AVERAGEIF. Pojdimo skozi primer.
Imamo seznam rezultatov, ki so jih učenci dosegli v dveh različnih predmetih:
Predvidevamo, da najdemo 75th percentili ocen, doseženi pri vsakem predmetu, na naslednji način:
Da bi to dosegli, lahko funkcijo IF ugnezdimo z predmet saj naši kriteriji znotraj PERCENTILA delujejo tako:
= PERCENTILNO (ČE (=,),)
= PERCENTILNO (ČE ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), 0,75)
Ko uporabljate Excel 2022 in starejše, morate formulo matrike vnesti s pritiskom na CTRL + SHIFT + ENTER (namesto ENTER), ki Excelu pove, da je formula v formuli matrike. Za kodrno oklepaje, ki se pojavijo okoli formule, boste vedeli, da gre za matrično formulo (glejte zgornjo sliko).
Kako deluje formula?
Funkcija If oceni vsako celico v našem obsegu meril kot TRUE ali FALSE in ustvari dve matriki:
= PERCENTILNO (ČE ({TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {0,99; 0,8; 0,93; 0,42; 0,87; 0,63; 0,71; 0,58; 0,73}), 0,75)
Nato funkcija IF ustvari eno matriko in vsako vrednost nadomesti z FALSE, če njen pogoj ni izpolnjen.
= PERCENTILNO ({0,81; FALSE; FALSE; 0,42; FALSE; 0,63; FALSE; 0,58; FALSE}, 0,75)
Zdaj funkcija PERCENTILE preskoči vrednosti FALSE in izračuna 75th percentil preostalih vrednosti (0,72 je 75th percentil).
PERCENTILNO IF z več merili
Če želite izračunati PERCENTILNO IF z več merili (podobno kot deluje vgrajena funkcija AVERAGEIFS), lahko preprosto pomnožite merila skupaj:
= PERCENTILNO (ČE ((=)*(=),),)
= PERCENTILNO (ČE (($ D $ 2: $ D $ 10 = $ H2)*($ C $ 2: $ C $ 10 = $ G2), $ E $ 2: $ E $ 10), 0,75)
Drug način za vključitev več meril je, da v formulo vstavite več stavkov IF.
Namigi in triki:
- Kadar koli je mogoče, se vedno sklicujte na položaj (k) iz pomožne celice in na referenčno ključavnico (F4), saj boste tako olajšale formule za samodejno polnjenje.
- Če uporabljate Excel 2022 ali novejši, lahko formulo vnesete brez CTRL + SHIFT + ENTER.
- Če želite pridobiti imena učencev, ki so dosegli najvišje ocene, to združite s INDEX MATCH.
PERCENTILNO ČE v Google Preglednicah
PERCENTILNA IF funkcija v Google Preglednicah deluje popolnoma enako kot v Excelu:
