Stiahnite si ukážkový zošit
Tento tutoriál vám ukáže, ako vypočítať „percentil if“, pričom sa získa kth percentil v zúrivosti hodnôt s kritériami.
Funkcia PERCENTILE
Na výpočet k sa používa funkcia PERCENTILEth percentil hodnôt v rozsahu, kde k je hodnota percentilu medzi 0 a 1 vrátane.
= PERCENTILNÉ ($ D $ 2: $ D $ 10,0,75)
To však vyžaduje percentil celého rozsahu hodnôt. Namiesto toho na vytvorenie „percentilu If“ použijeme vo vzorci poľa funkciu PERCENTILE spolu s funkciou IF.
PERCENTILNÉ, AK
Kombináciou PERCENTILE a IF vo vzorci poľa môžeme v podstate vytvoriť funkciu „PERCENTLE IF“, ktorá funguje podobne ako vstavaná funkcia AVERAGEIF. Poďme sa pozrieť na príklad.
Ponúkame zoznam skóre, ktoré študenti dosiahli v dvoch rôznych predmetoch:
Predpokladá sa, že nás požiadajú, aby sme našli 75th percentily skóre dosiahnutých pre každý predmet takto:
Aby sme to dosiahli, môžeme vnoriť funkciu IF pomocou predmet ako naše kritériá vo funkcii PERCENTILE takto:
= PERCENTILE (IF (=,),)
= PERCENTILE (IF ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), 0,75)
Pri použití Excelu 2022 a staršieho musíte zadať vzorec poľa stlačením CTRL + SHIFT + ENTER (namiesto VSTÚPIŤ), čo hovorí Excelu, že vzorec vo vzorci poľa. Budete vedieť, že je to vzorec poľa podľa zložených zátvoriek, ktoré sa objavujú okolo vzorca (pozri horný obrázok).
Ako funguje vzorec?
Funkcia If vyhodnotí každú bunku v našom rozsahu kritérií ako PRAVDU alebo NEPRAVDU, čím sa vytvoria dve polia:
= PERCENTILE (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)
Ďalej funkcia IF vytvorí jediné pole, ktoré nahradí každú hodnotu hodnotou FALSE, ak nie je splnená jej podmienka.
= PERCENTILE ({0,81; FALSE; FALSE; 0,42; FALSE; 0,63; FALSE; 0,58; FALSE}, 0,75)
Teraz funkcia PERCENTILE preskočí FALSE hodnoty a vypočíta 75th percentil zostávajúcich hodnôt (0,72 je 75th percentil).
PERCENTILNÉ, AK S viacnásobnými kritériami
Ak chcete vypočítať PERCENTILNÝ IF s viacerými kritériami (podobne ako funguje vstavaná funkcia AVERAGEIFS), môžete tieto kritériá jednoducho vynásobiť:
= PERCENTILE (IF ((=)*(=),),)
= PERCENTILE (IF (($ D $ 2: $ D $ 10 = $ H2)*($ C $ 2: $ C $ 10 = $ G2), $ E $ 2: $ E $ 10), 0,75)
Ďalším spôsobom, ako zahrnúť viacero kritérií, je vložiť do vzorca viac príkazov IF.
Tipy a triky:
- Pokiaľ je to možné, vždy odkazujte na polohu (k) z pomocnej bunky a referencie zámku (F4), pretože to uľahčí automatické vypĺňanie vzorcov.
- Ak používate Excel 2022 alebo novší, vzorec môžete zadať bez CTRL + SHIFT + ENTER.
- Ak chcete získať mená študentov, ktorí dosiahli najlepšie známky, skombinujte to s INDEX MATCH.
PERCENTILNÉ, AK v Tabuľkách Google
Funkcia PERCENTILE IF funguje v Tabuľkách Google úplne rovnako ako v programe Excel:
