Как рассчитать ПРОЦЕНТИЛЬ в 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-ый процентиль для этого набора данных.

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

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

В приведенной выше формуле я использовал 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.

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

Надеюсь, вам помогло данное руководство!