Як розрахувати ПРОЦЕНТИЛЬ в Excel (проста формула + приклади)

Процентиль – це статистична метрика, яка часто використовується під час роботи з даними.

Вона дає нам уявлення про положення конкретної точки даних, по відношенню до всього масиву цих даних (тобто позиції/рангу в наборі даних).

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

У цій статті я розповім усе, що вам потрібно знати про функції для обчислення проценти в Excel, і покажу приклади розрахунку 90-го або 50-го відсотка в Excel.

Отже, давайте приступимо!

Що таке відсотки? Просте пояснення!

Відсоток говорить вам про відносне положення точки даних щодо всього набору цих даних.

Наприклад, у нас є оцінки 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)

де:

  1. array- це діапазон осередків, в яких знаходяться значення, для яких потрібно дізнатися K-й процентиль.
  2. k — це значення між 0 та 1 (або ви можете вказати %), яке задає k-й процентиль. Наприклад, якщо ви хочете розрахувати 90-й процентиль, це буде 0,9 або 90%, а для 50-го процентиля це буде 0,5 або 50%.

Синтаксис залишається таким самим для функцій PERCENTILE та PERCENTILE.EXC.

Обчислення 90-го відсотка в Excel (або 50-го відсотка)

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

Як розрахувати ПРОЦЕНТИЛЬ в Excel (проста формула + приклади)

Нижче наведено формулу для обчислення 90-го процентиля:

=PERCENTILE.INC(A2:A21;90%)

Як розрахувати ПРОЦЕНТИЛЬ в Excel (проста формула + приклади)

У наведеній вище формулі я використав 90% як значення k. Також можна використовувати 0,9, щоб обчислити 90%.

Результат цієї формули вказує, що 90% значень цього набору даних лежить нижче 95,3.

Зауважте, що для роботи цієї формули вам не потрібно сортувати дані. Сортування та видача кінцевого результату це те, що функція PERCENTILE робить автоматично.

Так само, якщо потрібно обчислити 50-й процентиль, можна використовувати наведену нижче формулу:

=PERCENTILE.INC(A2:A21;50%)

PERCENTILE.INC vs PERCENTILE.EXC – в чим різниця ?

Якщо вам цікаво, чому в Excel існують дві окремі функції відсотка, зараз це поясню.

Коли ви використовуєте функцію PERCENTILE.INC, вона обчислює процентиль, використовуючи перше та останнє значення у наборі даних. Якщо вам потрібно виключити перше та останнє значення з розрахунку, вам потрібно використовувати функцію PERCENTILE.EXC.

Найчастіше можна використовувати функцію PERCENTILE.INC.

Але якщо ми вже заговорили про різницю між цими функціями, я наочно продемонструю її.

Допустимо, у нас є набір даних, як показано на малюнку нижче, де я розрахував різні проценти (в стовпці C), використовуючи як функцію PERCENTILE.INC, так і функцію PERCENTILE.EXC.

Як розрахувати ПРОЦЕНТИЛЬ в Excel (проста формула + приклади)

Як ви можете помітити, крім того, що результати кожної функції різні, ще й функція 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.

Сподіваюся, вам допомогло це керівництво!

nBook
Додати коментар