Preuzmite primjer radne knjige
Ovaj će vodič pokazati kako izračunati "percentil ako", dohvativši kth percentil u bijesu vrijednosti s kriterijima.
PERCENTILNA funkcija
PERCENTILNA funkcija koristi se za izračunavanje kth percentil vrijednosti u rasponu gdje k je vrijednost percentila između 0 i 1 uključivo.
= PERCENTILNO ($ D $ 2: $ D $ 10,0.75)
Međutim, to uzima percentil cijelog raspona vrijednosti. Umjesto toga, za stvaranje "Percentila If", koristit ćemo PERCENTILNU funkciju zajedno s IF funkcijom u formuli niza.
PERCENTILNO AKO
Kombinirajući PERCENTILE i IF u formuli niza, možemo u biti stvoriti funkciju "PERCENTLE IF" koja radi slično kao što radi ugrađena funkcija AVERAGEIF. Prođimo kroz primjer.
Imamo popis bodova koje su postigli učenici u dva različita predmeta:
Pretpostavimo da smo zamoljeni da pronađemo 75th percentili bodova postignuti za svaki predmet ovako:
Da bismo to postigli, možemo ugnijezditi IF funkciju sa predmet kako naši kriteriji unutar PERCENTILA funkcioniraju ovako:
= PERCENTILNO (AKO (=,),)
= PERCENTILNO (AKO ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), 0,75)
Kada koristite Excel 2022 i starije verzije, morate unijeti formulu polja pritiskom na CTRL + SHIFT + ENTER (umjesto UNESI), govoreći Excelu da je formula u formuli niza. Znat ćete da je to formula niza po uvojljenim zagradama koje se pojavljuju oko formule (pogledajte gornju sliku).
Kako funkcionira formula?
Funkcija If procjenjuje svaku ćeliju u našem rasponu kriterija kao TRUE ili FALSE, stvarajući dva niza:
= PERCENTILNO (IF ({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)
Zatim IF funkcija stvara jedan niz, zamjenjujući svaku vrijednost s FALSE ako njezin uvjet nije ispunjen.
= PERCENTILNO ({0,81; FALSE; FALSE; 0,42; FALSE; 0,63; FALSE; 0,58; FALSE}, 0,75)
Sada PERCENTILNA funkcija preskače FALSE vrijednosti i izračunava 75th percentil preostalih vrijednosti (0,72 je 75th postotak).
PERCENTILNO AKO s više kriterija
Za izračun PERCENTILNOG IF-a s više kriterija (slično kao što radi ugrađena funkcija AVERAGEIFS), možete jednostavno pomnožiti kriterije zajedno:
= PERCENTILNO (AKO ((=)*(=),),)
= PERCENTILNO (AKO (($ D $ 2: $ D $ 10 = $ H2)*($ C $ 2: $ C $ 10 = $ G2), $ E $ 2: $ E $ 10), 0,75)
Drugi način uključivanja više kriterija je umetanje više IF izraza u formulu.
Savjeti i trikovi:
- Gdje je moguće, uvijek referencirajte položaj (k) iz pomoćne ćelije i referencu zaključavanja (F4) jer će to olakšati formule za automatsko punjenje.
- Ako koristite Excel 2022 ili noviji, formulu možete unijeti bez CTRL + SHIFT + ENTER.
- Da biste dohvatili imena učenika koji su postigli najbolje ocjene, kombinirajte ovo s INDEX MATCH.
PERCENTILNO AKO u Google tablicama
PERCENTILNA IF funkcija radi potpuno isto u Google tablicama kao i u Excelu: