Excel PERCENTILE Funkce - Vypočítejte kth percentil

Stáhněte si ukázkový sešit

Stáhněte si ukázkový sešit

Tento výukový program ukazuje, jak používat Funkce Excel PERCENTILE v Excelu pro výpočet percentilu.

Přehled funkcí PERCENTILE

Funkce PERCENTILE Vypočítá kth percentil.

Chcete -li použít funkci listu PERCENTILE Excel, vyberte buňku a zadejte:

(Všimněte si, jak se zobrazují vstupy vzorců)

Funkce PERCENTILE Syntaxe a vstupy:

1	= PERCENTILE (pole, k)

pole - Pole čísel.

k - Hodnota percentilu mezi 0 a 1.

Co je to funkce PERCENTILE?

PERCENTILE je jednou ze statistických funkcí Excelu. Vrátí k-tý percentil daného rozsahu dat.

PERCENTILE je funkce „kompatibility“

V aplikaci Excel 2010 společnost Microsoft nahradila PERCENTILE dvěma variantami: PERCENTILE.INC a PERCENTILE. EXC.

PERCENTILE stále funguje, takže starší tabulky, které ho používají, budou nadále fungovat normálně. Pokud však nepotřebujete, aby vaše tabulka zůstala kompatibilní se staršími verzemi Excelu, měli byste použít PERCENTILE.INC nebo PERCENTILE.EXC.

Všechny tři probereme, abyste porozuměli vstupům a výstupům každého z nich.

Co je to percentil?

Percentil vám řekne, jak se hodnota porovnává s ostatními hodnotami v daném rozsahu. Když je hodnota na k-tém percentilu, znamená to, že je vyšší než k% ostatních hodnot ve skupině.

Pokud jste například muž žijící v USA a jste vysoký 6 stop (180 cm), jste v 71. percentilu - jste vyšší než 71% ostatních mužů v USA. Pokud jste žena a máte 180 stop na výšku, je vám 99^th percentil - jste vyšší než 99% ostatních žen.

Když máte co do činění s percentily, všimněte si, že 50^th percentil se rovná mediánu <> dat.

Funkce Excel PERCENTILE vám řekne, jaký je mezní bod pro daný percentil - což znamená, jak vysoká hodnota musí být, aby byla v k -tém percentilu.

Jak používat funkci PERCENTILE

Použijte PERCENTILE takto:

1	= PERCENTILNÍ (C4: C13, F3)

Zde máme několik přátel, kteří se rozhodli vypočítat 80^th percentil výšky jejich skupiny a vidíme výsledek - 183,4 cm.

Prvním argumentem ve funkci je rozsah dat - jejich výšky, které jsme rozvrhli v C4: C13.

Dalším argumentem je percentil. V tomto případě jsem vložil percentil do buňky F3 a poté odkazoval na tuto buňku ve funkci. Tímto způsobem je snadné změnit percentil a zobrazit nové výsledky.

Pokud však chcete, můžete vložit vzorec přímo do vzorce. Můžete jej definovat jako desetinné číslo takto:

1	= PERCENTILNÍ (C4: C13, .8)

Nebo v procentech takto:

1	= PERCENTILNÍ (C4: C13,80%)

A všechny tyto vracejí stejnou hodnotu.

U PERCENTILE je třeba si uvědomit několik věcí:

• Pokud vámi definovaný percentil není číslo, funkce vrátí #HODNOTU! chyba
• Pokud je percentil menší než 0 nebo větší než 1, získáte #ČÍSLO! chyba
• Text a prázdné buňky ve vašem datovém rozsahu budou ignorovány

Dvě metody výpočtu percentilů

Pojďme si na chvíli dát do pořádku naše data:

Možná se divíte, proč 80^th percentil není 183, protože Chandler je 8^th nejvyšší osoba ve skupině.

Ve skutečnosti existuje mnoho způsobů, jak vypočítat percentil matematicky, a žádná standardizovaná nebo preferovaná metoda pro všechny situace. To je něco, o čem statistici argumentují v akademických novinách.

Excel vám nabízí dva způsoby, jak toho dosáhnout, čímž se dostáváme na stránky PERCENTILE.INC a PERCENTILE.EXC.

PERCENTILE.INC

Funkce Excel PERCENTILE.INC je stejná jako PERCENTILE. Část „INC“ je zkratka pro inkluzivní, protože dokáže vypočítat jakýkoli platný percentil (tj. Cokoli od 0% do 100%).

Používáte to takto:

1	= PERCENTILE.INC (C4: C13, F3)

Jak vidíte, vrací stejný výsledek, který PERCENTILE udělal dříve.

Vraťme se tedy k tomu, proč 80^th percentil není přesně stejný jako Chandlerova výška. Pamatujte, že zde provádíme inkluzivní výpočet, takže zahrnujeme všechny hodnoty k od 0 do 1 (nebo 0% až 100%).

To znamená, že naše nejkratší kamarádka Janice je na 0^th percentil a Richard, nejvyšší, je 100^th percentil. Všichni ostatní přátelé jsou mezi nimi ve stejných přírůstcích a tento přírůstek se rovná 1/(n-1), kde n je počet datových bodů v rozsahu.

V našem případě je to:

1	1 / (10 - 1) = 11.111…

To znamená, že Chandler ve skutečnosti není na 80^th percentil, je na 77,777^th percentil. Pokud toto číslo připojíme do PERCENTILE.INC, měli bychom získat Chandlerovu výšku…

… A my ano.

Lineární interpolace

Nyní, kdykoli zadaná hodnota k není násobkem 1/(n-1), PERCENTILE.INC použije k výpočtu výsledku proces zvaný lineární interpolace. Zní to složitě, ale v zásadě to znamená, že se Excel ustálí na hodnotě mezi nimi.

Proč jsme tedy dostali 183,4, když jsme požádali o 80^th percentil dříve?

Výpočet funguje následovně:

• 80^th percentil spadá mezi Chandlera a Rosse, Chandler na 77,777^th percentil a Ross na 88.888.
• 80^th percentil je 2,222% před Chandlerovou výškou
• Víme, že mezi výškami Chandlera a Rosse je 11% vzdálenost
• 222% / 11,111% = 20% po zaokrouhlení. Nyní víme, že 80^th percentil je 20% cesty mezi výškami Rosse a Chandlera.
• Rozdíl mezi Rossovou výškou 185 cm a Chandlerovou výškou 183 cm je 2 cm
• 20% ze 2 cm je 0,4 cm
• Když to přidáme k Chandlerově výšce, dostaneme 183,4 cm

PERCENTILE.EXC

PERCENTILE.EXC používáte v podstatě stejným způsobem:

1	= PERCENTILE.EXC (C4: C13, F3)

Dobře, máme tedy stejná data, chceme stejný percentil, ale máme jiné výsledky. Proč je to tak?

Je to proto, že PERCENTILE.EXC vylučuje první a poslední hodnoty při výpočtu intervalů percentilu. Viz. níže:

Spíše než se interval rovná 1/(n-1), u PERCENTILE.EXC je interval 1/(n+1), nebo v tomto případě 9,091%.

Všechno ostatní funguje stejně jako u PERCENTILE.INC. Opět 80^th percentil je mezi Chandlerem a Rossem, takže Excel používá stejnou metodu lineární interpolace:

• 80^th percentil spadá mezi Chandlera a Rosse, Chandler na 72,727^th percentil a Ross na 81,818.
• 80^th percentil je 7,272% před Chandlerovou výškou
• Víme, že mezi výškami Chandlera a Rosse je 9,091% vzdálenost
• 272% / 9,091% = 80% po zaokrouhlení. Nyní víme, že 80^th percentil je 80% cesty mezi výškami Rosse a Chandlera.
• Rozdíl mezi Rossovou výškou 185 cm a Chandlerovou výškou 183 cm je 2 cm
• 80% ze 2 cm je 1,6 cm
• Když to přidáme k Chandlerově výšce, dostaneme 184,6 cm

PERCENTILE v Tabulkách Google

Funkce PERCENTILE funguje v Tabulkách Google úplně stejně jako v Excelu:

PERCENTILNÍ Příklady ve VBA

Ve VBA můžete také použít funkci PERCENTILE. Typ:
application.worksheetfunction.percentile (pole, k)
Argumenty funkce (pole atd.) Můžete buď zadat přímo do funkce, nebo definovat proměnné, které se místo toho použijí.

Zpět na seznam všech funkcí v Excelu