ПЕРЦЕНЦІЛЬНІ функції Excel - обчислення k -го процентиля

Завантажити Приклад робочої книги

Завантажте зразок робочої книги

Цей підручник демонструє, як користуватися ПЕРЦЕНЦІЛЬНА функція Excel в Excel для обчислення процентиля.

ПЕРЦЕНЦІЛЬНИЙ Огляд функції

Функція PERCENTILE Обчислює k -й процентиль.

Щоб використовувати ПЕРЦЕНЦІЛЬНУ функцію робочого аркуша Excel, виберіть клітинку та введіть:

(Зверніть увагу, як виглядають вхідні дані формул)

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

1	= ПЕРЦЕНТИЛЬНИЙ (масив, k)

масив - Масив чисел.

k - значення процентилю між 0 і 1.

Що таке ПЕРЦЕНЦИЛЬНА функція?

ПЕРЦЕНТИЛЬ - одна зі статистичних функцій Excel. Він повертає k-й процентиль заданого діапазону даних.

ПЕРЦЕНЦІЛЬ - це функція сумісності

Станом на Excel 2010 Microsoft замінила PERCENTILE двома варіантами: PERCENTILE.INC та PERCENTILE. EXC.

PERCENTILE все ще працює, тому старіші таблиці, які його використовують, продовжуватимуть працювати у звичайному режимі. Однак, якщо вам не потрібно, щоб ваша електронна таблиця залишалася сумісною зі старішими версіями Excel, вам слід використовувати PERCENTILE.INC або PERCENTILE.EXC.

Ми обговоримо всі три, щоб ви зрозуміли тонкощі кожного з них.

Що таке процентиль?

Процентиль показує, як значення порівнюється з іншими значеннями в даному діапазоні. Якщо значення знаходиться на k-му процентилі, це означає, що воно перевищує k% інших значень у групі.

Наприклад, якщо ви чоловік, який проживає в США, і у вас зріст 180 футів, ви перебуваєте в 71 -му процентилі - ви вище 71% інших чоловіків у США. Якщо ви жінка і маєте зріст 180 футів, ви на 99 -му^го процентиль - ви вищі за 99% інших жінок.

Коли ви маєте справу з процентилями, зверніть увагу, що 50^го процентиль дорівнює медіані <> даних.

Функція Excel PERCENTILE повідомляє вам, що є граничною точкою для певного процентиля - тобто, наскільки високим має бути значення, щоб воно знаходилося в k -му процентилі.

Як користуватися функцією PERCENTILE

Використовуйте PERCENTILE так:

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

Тут у нас є кілька друзів, які вирішили обчислити 80^го процентиль висоти їх групи, і ми бачимо результат - 183,4 см.

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

Наступний аргумент - процентиль. У цьому прикладі я помістив процентиль у комірку F3, а потім посилався на цю клітинку у функції. Таким чином, легко змінити процентиль і побачити нові результати.

Тим не менш, ви можете вставити процентиль безпосередньо у формулу, якщо хочете. Ви можете визначити його як десятковий, наприклад:

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

Або у відсотках, наприклад:

1	= ПЕРЦЕНЦІЛЬ (C4: C13,80%)

І всі вони повертають однакове значення.

Кілька речей, про які слід знати з PERCENTILE:

• Якщо визначений процентиль не є числом, функція поверне #VALUE! помилка
• Якщо процентиль менше 0 або більше 1, ви отримаєте #NUM! помилка
• Текст та порожні клітинки у вашому діапазоні даних будуть ігноруватися

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

Давайте на секунду упорядкуємо наші дані:

Ви можете запитати, чому 80^го процентиль не 183, оскільки Чендлеру 8^го найвища людина в групі.

Ну, насправді існує багато способів математичного обчислення процентиля, і немає стандартизованого чи бажаного методу для всіх ситуацій. Про це статистики сперечаються в наукових працях.

Excel пропонує вам два способи зробити це, що призводить нас до PERCENTILE.INC та PERCENTILE.EXC.

PERCENTILE.INC

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

Ви використовуєте його так:

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

Як бачите, він повертає той самий результат, що і PERCENTILE раніше.

Отже, повертаючись до того, чому 80^го процентиль не зовсім дорівнює зростанню Чендлера. Пам’ятайте, що тут ми проводимо інклюзивний розрахунок, тому включаємо всі значення k від 0 до 1 (або від 0% до 100%).

Це означає, що наша найкоротша подруга, Дженіс, знаходиться на 0^го процентиль, а найвищий Річард - 100^го процентиль. Усі інші друзі мають рівні кроки між ними, і цей приріст дорівнює 1/(n-1), де n-кількість точок даних у діапазоні.

У нашому випадку це:

1	1 / (10 - 1) = 11.111…

Це означає, що Чендлеру насправді немає 80 років^го процентиль, він на рівні 77,777^го процентиль. Якщо ми підключаємо це число до PERCENTILE.INC, ми повинні отримати зріст Чендлера…

… і ми робимо.

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

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

Тож чому ми отримали 183,4, коли попросили 80^го процентиль раніше?

Розрахунок працює таким чином:

• 80^го процентиль падає між Чендлером та Россом, Чандлером на 77,777^го процентиль, а Росс на 88.888 -му.
• 80^го процентиль на 2,222% випереджає зріст Чендлера
• Ми знаємо, що відстань між висотами Чандлера та Росса становить 11%
• 222% / 11.111% = 20% після округлення. Тепер ми знаємо, що 80^го процентиль становить 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^го процентиль знаходиться між Чандлером та Россом, тому Excel застосовує той самий метод лінійної інтерполяції:

• 80^го процентиль падає між Чандлером та Россом, Чендлером на 72.727^го процентиль, а Росс на 81.818 -му.
• 80^го процентиль на 7,272% випереджає зріст Чендлера
• Ми знаємо, що між висотами Чандлера та Росса є відстань 9,091%
• 272% / 9,091% = 80% після округлення. Тепер ми знаємо, що 80^го процентиль становить 80% шляху між висотами Росса і Чендлера.
• Різниця між ростом Росса 185 см і зростом Чендлера 183 см становить 2 см
• 80% 2 см - це 1,6 см
• Додайте це до зросту Чендлера, і ми отримаємо 184,6 см

PERCENTILE у Google Таблицях

Функція PERCENTILE працює точно так само в Google Таблицях, як і в Excel:

ПЕРЦЕНЦІЛЬНІ Приклади у VBA

Ви також можете використовувати функцію PERCENTILE у VBA. Тип:
application.worksheetfunction.percentile (масив, k)
Для аргументів функції (масиву тощо) ви можете або ввести їх безпосередньо у функцію, або визначити змінні для використання замість них.

Повернення до списку всіх функцій Excel