ПЕРЦЕНТИЛНИ функции на Excel - Изчислете k -ти процентил

Изтеглете Примерна работна книга

Изтеглете примерната работна книга

Този урок демонстрира как да използвате ПЕРЦЕНЦИЛНА функция на Excel в Excel за изчисляване на процентила.

ПЕРЦЕНТИЛЕН Общ преглед на функцията

Функцията PERCENTILE Изчислява k -ия процентил.

За да използвате PERCENTILE функцията за работен лист на Excel, изберете клетка и въведете:

(Забележете как се появяват входовете на формулата)

PERCENTILE функция Синтаксис и входове:

1	= PERCENTILE (масив, k)

масив - Масив от числа.

к - Стойността на процентила между 0 и 1.

Какво представлява PERCENTILE функцията?

PERCENTILE е една от статистическите функции на Excel. Той връща k-тия персентил на даден диапазон от данни.

PERCENTILE е функция за „съвместимост“

От Excel 2010 Microsoft замени PERCENTILE с две варианти: PERCENTILE.INC и PERCENTILE. EXC.

PERCENTILE все още работи, така че по -старите електронни таблици, които го използват, ще продължат да функционират нормално. Ако обаче не се нуждаете от електронната си таблица, за да остане съвместима с по -старите версии на Excel, трябва да използвате PERCENTILE.INC или PERCENTILE.EXC.

Ще обсъдим и трите, за да разберете тънкостите на всеки от тях.

Какво е перцентил?

Процентилът ви казва как дадена стойност се сравнява с другите стойности в даден диапазон. Когато стойността е на k-тия процентил, това означава, че тя е по-висока от k% от другите стойности в групата.

Например, ако сте мъж, живеещ в САЩ и сте с височина 180 фута, сте на 71 -ия процентил - по -висок сте от 71% от другите мъже в САЩ. Ако сте жена и висок 180 фута, сте на 99 -то място^th процентил - вие сте по -високи от 99% от другите жени.

Когато имате работа с процентили, обърнете внимание, че 50^th процентилът е равен на медианата <> на данните.

Функцията Excel PERCENTILE ви казва каква е граничната точка за даден процентил - което означава, колко висока трябва да бъде стойността, за да бъде в k -тия процентил.

Как да използвате функцията PERCENTILE

Използвайте PERCENTILE по следния начин:

1	= ПЕРЦЕНЦИЛНО (C4: C13, F3)

Тук имаме няколко приятели, които са решили да изчислят 80^th персентил на височината на тяхната група и виждаме резултата - 183,4см.

Първият аргумент във функцията е диапазонът от данни - техните височини, които сме изложили в C4: C13.

Следващият аргумент е процентилът. В този пример поставих персентила в клетка F3 и след това посочих тази клетка във функцията. По този начин е лесно да промените процентила и да видите новите резултати.

Можете обаче да поставите процентила директно във формулата, ако искате. Можете да го определите като десетичен знак, например:

1	= ПЕРЦЕНЦИЛНО (C4: C13, .8)

Или като процент, например:

1	= ПРОЦЕНТИЛ (C4: C13,80%)

И всички те връщат една и съща стойност.

Няколко неща, които трябва да знаете с PERCENTILE:

• Ако процентилът, който дефинирате, не е число, функцията ще върне #VALUE! грешка
• Ако процентилът е по -малък от 0 или по -голям от 1, ще получите #NUM! грешка
• Текстът и празните клетки във вашия диапазон от данни ще бъдат игнорирани

Два метода за изчисляване на процентили

Нека подредим данните си за секунда:

Може би се чудите защо 80^th процентилът не е 183, тъй като Чандлър е 8^th най -високият човек в групата.

Е, всъщност има много начини за математическо изчисляване на процентила и няма стандартизиран или предпочитан метод за всички ситуации. Това е нещо, за което статистиците спорят в академичните статии.

Excel ви дава два начина да направите това, което ни води до PERCENTILE.INC и PERCENTILE.EXC.

PERCENTILE.INC

Функцията Excel PERCENTILE.INC е същата като PERCENTILE. Частта „INC“ е съкращение за включително, тъй като може да изчисли всеки валиден процентил (тоест всичко от 0% до 100%).

Използваш го така:

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

Както можете да видите, той връща същия резултат, който PERCENTILE направи по -рано.

И така, да се върнем на причината 80^th процентилът не е точно равен на височината на Чандлър. Не забравяйте, че тук правим приобщаващо изчисление, така че включваме всички k стойности от 0 до 1 (или 0% до 100%).

Това означава, че най -късата ни приятелка Джанис е на 0^th процентил, а най -високият Ричард е 100^th процентил. Всички останали приятели са на равни стъпки между двете и това увеличение е равно на 1/(n-1), където n е броят на точките от данни в диапазона.

В нашия случай това е:

1	1 / (10 - 1) = 11.111…

Това означава, че Чандлър всъщност не е на 80 години^th процентил, той е на 77.777^th процентил. Ако включим този номер в PERCENTILE.INC, трябва да получим височината на Чандлър …

… и го правим.

Линейна интерполация

Сега, когато посочената от вас стойност k не е кратна на 1/(n-1), PERCENTILE.INC ще приложи процес, наречен линейна интерполация, за да изчисли резултата. Това звучи сложно, но основно означава, че Excel ще се спре на стойност между двете.

И така, защо получихме 183.4, когато поискахме 80^th процентил по -рано?

Изчислението работи както следва:

• 80 -те^th процентил пада между Чандлър и Рос, Чандлър на 77.777^th процентил, а Рос на 88.888 -и.
• 80 -те^th персентил е с 2.222% пред ръста на Чандлър
• Знаем, че има 11% разстояние между височините на Чандлър и Рос
• 222% / 11.111% = 20% след закръгляване. Сега знаем, че 80 -те^th процентилът е 20% от пътя между височините на Рос и Чандлър.
• Разликата между височината на Рос от 185 см и височината на Чандлър от 183 см е 2 см
• 20% от 2 см е 0,4 см
• Добавете това към височината на Чандлър и получаваме 183,4 см

PERCENTILE.EXC

Използвате PERCENTILE.EXC по същество по същия начин:

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

Добре, значи имаме същите данни, искаме един и същ процентил, но имаме различни резултати. Защо така?

Това е така, защото PERCENTILE.EXC изключва първата и последната стойност, когато изчислява своите процентни интервали. Виж отдолу:

Вместо интервалът да е равен на 1/(n-1), с PERCENTILE.EXC интервалът е 1/(n+1) или в този случай 9.091%.

Всичко останало работи по същия начин, както при PERCENTILE.INC. Отново 80^th процентилът е между Чандлър и Рос, така че Excel прилага същия метод на линейна интерполация:

• 80 -те^th процентил пада между Чандлър и Рос, Чандлър на 72.727^th процентил, а Рос на 81.818 -та.
• 80 -те^th персентил е 7.272% пред ръста на Чандлър
• Знаем, че има 9.091% разстояние между височините на Чандлър и Рос
• 272% / 9.091% = 80% след закръгляване. Сега знаем, че 80 -те^th процентилът е 80% от пътя между височините на Рос и Чандлър.
• Разликата между височината на Рос от 185 см и височината на Чандлър от 183 см е 2 см
• 80% от 2 см е 1,6 см
• Добавете това към височината на Чандлър и получаваме 184,6 см

PERCENTILE в Google Таблици

Функцията PERCENTILE работи абсолютно същото в Google Sheets като в Excel:

ПЕРЦЕНЦИЛНИ Примери във VBA

Можете също да използвате функцията PERCENTILE във VBA. Тип:
application.worksheetfunction.percentile (масив, k)
За аргументите на функцията (масив и т.н.) можете или да ги въведете директно във функцията, или да определите променливи, които да използвате вместо това.

Връщане към списъка с всички функции в Excel