Процентиль – це статистичний показник, який зазвичай застосовують при аналізі даних.
Цей елемент допомагає зрозуміти розташування певного пункту інформації в контексті всього набору даних (тобто його позицію чи ранг у загальному масиві).
У реальному житті я спостерігав, як процентиль застосовується під час конкурсних іспитів, де вам надають процентиль на основі вашого результату. Це допомагає усвідомити, яке місце ви займаєте порівняно з іншими учасниками, які також проходили цей іспит.
У цій статті я поділюсь усіма необхідними відомостями щодо функцій для розрахунку процентилю в Excel, а також продемонструю, як обчислити 90-й або 50-й процентиль у цій програмі.
Тож розпочнемо!
Що таке процентиль? Легке пояснення!
Процентиль вказує на те, як розташована конкретна точка даних порівняно із загальним набором даних.
Наприклад, ми маємо оцінки 100 студентів і бачимо, що 90-й процентиль становить 84. Це означає, що якщо студент отримав 84 бали, то його результат перевищує показники 90% інших студентів.
У цьому випадку, якщо 50-й процентиль становить 60, то це свідчить про те, що особа, яка отримала оцінку 60, перевершила 50% людей із нижчими результатами і поступилася 50% людям з вищими оцінками.
Цей підхід переважає просту оцінку, оскільки забезпечує глибше усвідомлення всіх доступних відомостей.
Наприклад, якщо я повідомлю вам, що ваш результат становить 90, ви не зможете відразу оцінити своє місце порівняно з іншими. Однак, якщо я скажу, що ваш бал знаходиться на 90-му процентилі, ви швидко зрозумієте, що ви досягли кращих результатів, ніж 90% тих, хто складав іспит.
Визначити процентиль в Excel не складно, оскільки програма містить спеціально розроблені функції для цих цілей.
Функції, що дозволяють розрахувати ПРОЦЕНТИЛЬ в Excel
У Excel є три способи обчислення процентилю. Якщо у вас версія Excel 2010 або новіша, ви матимете змогу скористатися всіма трьома функціями.
- PERCENTILE — це застаріла функція, яка в даний час не використовується. Хоча ви можете її застосовувати, рекомендується використовувати новіші версії (якщо вони доступні у вашій версії Excel). Аргумент k цієї функції — значення в межах від 0 до 1.
- PERCENTILE.INC — це сучасна функція, яка функціонує аналогічно до PERCENTILE. Зазвичай краще використовувати саме цю функцію. Аргумент k тут так само знаходиться в межах від 0 до 1.
- PERCENTILE.EXC — діє подібно до PERCENTILE.INC, але з однією особливістю: аргумент k для цієї функції приймає значення від 0 до 1, однак виключає значення k від 0 до 1/(N+1), а також від N/(N+1) до 1, де N — це розмір вибірки.
Іншими словами, скористайтеся функцією PERCENTILE.INC (якщо ж у вас Excel 2007 або старіша версія, тоді використовуйте функцію PERCENTILE).
Ось структура функції PERCENTILE.INC в Excel:
=PERCENTILE.INC(діапазон;k)
у якому:
- array – це набір клітинок, в яких розташовані дані, для яких необхідно визначити k-й процентиль.
- k – це число в межах від 0 до 1 (або ви можете вказати у відсотках), що визначає k-й процентиль. Наприклад, якщо ви маєте намір обчислити 90-й процентиль, це буде 0,9 або 90%, а для 50-го процентиля – 0,5 або 50%.
Синтаксис для функцій PERCENTILE та PERCENTILE.EXC залишається незмінним.
Обчислення 90-го процентиля в Excel (або 50-го процентиля)
Припустимо, у вас є певний масив даних, як видно на зображенні нижче, і ви хочете визначити 90-й процентиль для цього масиву.
Ось формула для розрахунку 90-го процентиля:
=PERCENTILE.INC(A2:A21;90%)
У наведеній раніше формулі я застосував 90% як значення k. Можна також скористатися 0,9 для розрахунку 90%.
Ця формула показує, що 90% всіх значень у цьому наборі даних знаходяться нижче 95,3.
Зверніть увагу, що для коректного використання цієї формули немає необхідності впорядковувати дані. Процес сортування та отримання фінального результату функція PERCENTILE виконує самостійно.
Для обчислення 50-го процентиля можна скористатися такою формулою:
=PERCENTILE.INC(A2:A21;50%)
Чим відрізняються PERCENTILE.INC та PERCENTILE.EXC?
Якщо ви цікавитеся, чому в Excel є дві різні функції для розрахунку процентилю, я зараз вам це роз’ясню.
Коли ви користуєтеся функцією PERCENTILE.INC, вона визначає процентиль на основі першого та останнього значення в наборі даних. Якщо вам потрібно виключити ці значення з обчислення, використовуйте функцію PERCENTILE.EXC.
Найбільш часто застосовується функція PERCENTILE.INC.
Оскільки ми вже торкнулися теми відмінностей між цими функціями, я продемонструю це на прикладі.
Припустимо, ми маємо деякий набір даних, ілюстрований на зображенні нижче, в якому я обчислив різні процентилі (в колонці C), застосовуючи функцію PERCENTILE.INC та функцію PERCENTILE.EXC.
Як видно, результати кожної з функцій відрізняються. Додатково, функція PERCENTILE.EXC виводить помилку #NUM!, якщо ми спробуємо розрахувати 0-й або 100-й процентиль.
Фактично, функція PERCENTILE.EXC поверне помилку для значень, що знаходяться в інтервалі між:
- 0 і 1/(N+1)
- N/(N+1) і 1
де N — це кількість рядків інформації в наборі даних (таблиці) (у даному випадку 10).
Таким чином, функція видасть помилку #NUM! для всіх значень k, які знаходяться в межах від 0 до 1/11 або від 10/11 до 1.
Тому приємно, що функція PERCENTILE.EXC доступна, але в більшості випадків ми скористаємося функціями PERCENTILE або PERCENTILE.INC.
Сподіваюся, цей посібник виявився для вас корисним!
