Excel PERCENTILNÉ funkcie - Vypočítajte kth percentil

Stiahnite si ukážkový zošit

Stiahnite si ukážkový zošit

Tento návod ukazuje, ako používať Funkcia Excel PERCENTILE v Exceli na výpočet percentilu.

Prehľad funkcií PERCENTILE

Funkcia PERCENTILE vypočíta kth percentil.

Ak chcete použiť funkciu pracovného hárka PERCENTILE Excelu, vyberte bunku a zadajte:

(Všimnite si, ako sa zobrazujú vstupy vzorcov)

Funkcia PERCENTILE Syntax a vstupy:

1	= PERCENTILE (pole, k)

pole - Súbor čísel.

k - hodnota percentilu v rozsahu 0 až 1.

Čo je to funkcia PERCENTILE?

PERCENTILE je jednou zo štatistických funkcií programu Excel. Vráti k-tý percentil daného rozsahu údajov.

PERCENTILE je funkcia „kompatibility“

V programe Excel 2010 spoločnosť Microsoft nahradila PERCENTILE dvoma variáciami: PERCENTILE.INC a PERCENTILE. EXC.

PERCENTILE stále funguje, takže staršie tabuľky, ktoré ho používajú, budú naďalej fungovať normálne. Ak však nepotrebujete, aby vaša tabuľka zostala kompatibilná so staršími verziami Excelu, mali by ste použiť PERCENTILE.INC alebo PERCENTILE.EXC.

Diskutujeme o všetkých troch, aby ste porozumeli vstupom a výstupom každého z nich.

Čo je to percentil?

Percentil vám povie, ako sa hodnota porovná s ostatnými hodnotami v danom rozsahu. Keď je hodnota na k-tom percentile, znamená to, že je vyššia ako k% ostatných hodnôt v skupine.

Ak ste napríklad muž žijúci v USA a máte výšku 6 stôp (180 cm), ste v 71. percentile - ste vyšší ako 71% ostatných mužov v USA. Ak ste žena a máte 180 cm na 6 stôp, je vám 99^th percentil - ste vyšší ako 99% ostatných žien.

Keď máte do činenia s percentilmi, všimnite si, že 50^th percentil sa rovná mediánu <> údajov.

Funkcia Excel PERCENTILE vám povie, čo je hraničný bod pre daný percentil - to znamená, ako vysoká musí byť hodnota, aby sa nachádzala v k -tom percentile.

Ako používať funkciu PERCENTILE

Použite PERCENTILE takto:

1	= PERCENTILNÉ (C4: C13, F3)

Tu máme niekoľko priateľov, ktorí sa rozhodli vypočítať 80^th percentil výšky ich skupiny a vidíme výsledok - 183,4 cm.

Prvým argumentom vo funkcii je rozsah údajov - ich výšky, ktoré sme rozvrhli v C4: C13.

Ďalším argumentom je percentil. V tomto prípade som vložil percentil do bunky F3 a potom som odkazoval na túto bunku vo funkcii. Týmto spôsobom je ľahké zmeniť percentil a zobraziť nové výsledky.

Môžete však pridať percentil priamo do vzorca, ak chcete. Môžete ho definovať ako desatinné miesto takto:

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

Alebo v percentách takto:

1	= PERCENTIL (C4: C13,80%)

A všetky tieto vracajú rovnakú hodnotu.

Pri PERCENTILE je potrebné si uvedomiť niekoľko vecí:

• Ak vami definovaný percentil nie je číslo, funkcia vráti #HODNOTU! chyba
• Ak je percentil menší ako 0 alebo väčší ako 1, dostanete #ČÍSLO! chyba
• Textové a prázdne bunky vo vašom rozsahu údajov budú ignorované

Dve metódy výpočtu percentilov

Dajme na chvíľu do poriadku naše údaje:

Možno sa čudujete, prečo 80^th percentil nie je 183, pretože Chandler je 8^th najvyššia osoba v skupine.

V skutočnosti existuje mnoho spôsobov, ako vypočítať percentil matematicky, a neexistuje žiadna štandardizovaná alebo preferovaná metóda pre všetky situácie. O tomto sa štatistici hádajú v akademických prácach.

Excel vám ponúka dva spôsoby, ako to urobiť, čím sa dostávame k súborom PERCENTILE.INC a PERCENTILE.EXC.

PERCENTILE.INC

Funkcia Excel PERCENTILE.INC je rovnaká ako PERCENTILE. Časť „INC“ je skratka pre inkluzívne, pretože dokáže vypočítať ľubovoľný platný percentil (to znamená čokoľvek od 0% do 100%).

Používate to takto:

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

Ako vidíte, vracia rovnaký výsledok, aký urobil PERCENTILE predtým.

Vráťme sa však k tomu, prečo 80^th percentil nie je presne rovnaký ako Chandlerova výška. Nezabudnite, že tu robíme inkluzívny výpočet, takže zahrnujeme všetky hodnoty k od 0 do 1 (alebo 0% až 100%).

To znamená, že naša najkratšia priateľka Janice je na 0^th percentil a Richard, najvyšší, je 100^th percentil. Všetci ostatní priatelia sú medzi nimi v rovnakých prírastkoch a tento prírastok sa rovná 1/(n-1), kde n je počet dátových bodov v rozsahu.

V našom prípade je to:

1	1 / (10 - 1) = 11.111…

To znamená, že Chandler v skutočnosti nemá 80 rokov^th percentil, je na 77,777^th percentil. Ak toto číslo zapojíme do PERCENTILE.INC, mali by sme získať Chandlerovu výšku …

… a my áno.

Lineárna interpolácia

Teraz, kedykoľvek zadaná hodnota k nie je násobkom 1/(n-1), PERCENTILE.INC použije na výpočet výsledku proces nazývaný lineárna interpolácia. Znie to komplikovane, ale v zásade to znamená, že Excel sa ustáli na hodnote medzi nimi.

Prečo sme teda dostali 183,4, keď sme žiadali 80^th percentil skôr?

Výpočet funguje nasledovne:

• 80^th percentil padá medzi Chandlera a Rossa, Chandlera na 77,777^th percentil a Ross na 88.888.
• 80^th percentil je 2,222% pred Chandlerovou výškou
• Vieme, že medzi výškami Chandlera a Rossa je 11% vzdialenosť
• 222% / 11,111% = 20% po zaokrúhlení. Teraz vieme, že 80^th percentil je 20% cesty medzi výškami Rossa a Chandlera.
• Rozdiel medzi Rossovou výškou 185 cm a Chandlerovou výškou 183 cm je 2 cm
• 20% z 2 cm je 0,4 cm
• Keď to pripočítame k Chandlerovej výške, dostaneme 183,4 cm

PERCENTILE.EXC

PERCENTILE.EXC používate v podstate rovnakým spôsobom:

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

Dobre, takže máme rovnaké údaje, chceme rovnaký percentil, ale máme iné výsledky. Prečo je to tak?

Dôvodom je, že PERCENTILE.EXC vylučuje prvé a posledné hodnoty pri výpočte jeho percentilových intervalov. Pozri nižšie:

Namiesto toho, aby sa interval rovnal 1/(n-1), pri PERCENTILE.EXC je interval 1/(n+1), alebo v tomto prípade 9,091%.

Všetko ostatné funguje rovnako ako s PERCENTILE.INC. Opäť 80^th percentil je medzi Chandlerom a Rossom, takže Excel používa rovnakú metódu lineárnej interpolácie:

• 80^th percentil padá medzi Chandlera a Rossa, Chandlera na 72,727^th percentil a Ross na 81,818.
• 80^th percentil je 7,272% pred Chandlerovou výškou
• Vieme, že medzi výškami Chandlera a Rossa je 9,091% vzdialenosť
• 272% / 9,091% = 80% po zaokrúhlení. Teraz vieme, že 80^th percentil je 80% cesty medzi výškami Rossa a Chandlera.
• Rozdiel medzi Rossovou výškou 185 cm a Chandlerovou výškou 183 cm je 2 cm
• 80% z 2 cm je 1,6 cm
• Keď to pripočítame k Chandlerovej výške, dostaneme 184,6 cm

PERCENTILE v Tabuľkách Google

Funkcia PERCENTILE funguje v Tabuľkách Google úplne rovnako ako v programe Excel:

PERCENTILNÉ Príklady vo VBA

Vo VBA môžete použiť aj funkciu PERCENTILE. Typ:
application.worksheetfunction.percentile (pole, k)
Pokiaľ ide o argumenty funkcie (pole atď.), Môžete ich buď zadať priamo do funkcie, alebo definovať premenné, ktoré sa namiesto toho použijú.

Vráťte sa do zoznamu všetkých funkcií v programe Excel