Stáhněte si ukázkový sešit
Tento tutoriál předvede, jak vypočítat „percentil if“ a načíst kth percentil ve vzteku hodnot s kritérii.
Funkce PERCENTILE
Funkce PERCENTILE se používá k výpočtu kth percentil hodnot v rozsahu kde k je hodnota percentilu mezi 0 a 1 včetně.
= PERCENTILNÍ ($ D $ 2: $ D $ 10,0,75)
To však vyžaduje percentil celého rozsahu hodnot. Místo toho pro vytvoření „percentilu If“ použijeme ve vzorci pole funkci PERCENTILE spolu s funkcí IF.
PERCENTILNÍ IF
Kombinací PERCENTILE a IF v maticovém vzorci můžeme v podstatě vytvořit funkci „PERCENTLE IF“, která funguje podobně jako vestavěná funkce AVERAGEIF. Pojďme se podívat na příklad.
Máme seznam skóre dosažených studenty ve dvou různých předmětech:
Předpokládá se, že jsme požádáni, abychom našli 75th percentily skóre dosažených pro každý předmět takto:
Abychom toho dosáhli, můžeme vnořit funkci IF pomocí předmět jako naše kritéria uvnitř funkce PERCENTILE takto:
= PERCENTILNÍ (IF (=,),)
= PERCENTILE (IF ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), 0,75)
Pokud používáte Excel 2022 a starší, musíte zadat vzorec pole stisknutím CTRL + SHIFT + ENTER (namísto VSTUP), říká Excelu, že vzorec ve vzorci pole. Poznáte to podle maticového vzorce podle složených závorek, které se objevují kolem vzorce (viz horní obrázek).
Jak funguje vzorec?
Funkce If vyhodnotí každou buňku v našem rozsahu kritérií jako PRAVDA nebo NEPRAVDA a vytvoří dvě pole:
= PERCENTILNÍ (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)
Dále funkce IF vytvoří jedno pole, které nahradí každou hodnotu FALSE, pokud není splněna jeho podmínka.
= PERCENTILNÍ ({0,81; FALSE; FALSE; 0,42; FALSE; 0,63; FALSE; 0,58; FALSE}, 0,75)
Nyní funkce PERCENTILE přeskočí FALSE hodnoty a vypočítá 75th percentil zbývajících hodnot (0,72 je 75th percentil).
PERCENTILNÍ IF s více kritérii
Chcete-li vypočítat PERCENTILNÍ IF s více kritérii (podobně jako funguje vestavěná funkce AVERAGEIFS), můžete kritéria jednoduše vynásobit:
= PERCENTILNÍ (IF ((=)*(=),),)
= PERCENTILE (IF (($ D $ 2: $ D $ 10 = $ H2)*($ C $ 2: $ C $ 10 = $ G2), $ E $ 2: $ E $ 10), 0,75)
Dalším způsobem, jak zahrnout více kritérií, je vnořit do vzorce více příkazů IF.
Tipy a triky:
- Pokud je to možné, vždy odkazujte na polohu (k) z pomocné buňky a referenční hodnoty zámku (F4), protože to usnadní automatické vyplňování vzorců.
- Pokud používáte Excel 2022 nebo novější, můžete vzorec zadat bez CTRL + SHIFT + ENTER.
- Chcete -li získat jména studentů, kteří dosáhli nejlepších známek, zkombinujte to s INDEX MATCH.
PERCENTILNÍ IF v Tabulkách Google
Funkce PERCENTILE IF funguje v Tabulkách Google úplně stejně jako v Excelu:
