Как быстро рассчитать процентиль или квартиль без учета нулей в Excel?

Примеры функции ПЕРСЕНТИЛЬ для расчета перцентиля в Excel

С помощью функции ПЕРСЕНТИЛЬ Excel можно вычислить k-й процентиль для числовых значений в исследуемом диапазоне, а затем вернуть результаты.

Метод перцентилей в Excel по функции ПЕРСЕНТИЛЬ с примерами

Имеется вариационный ряд данных с минимальным и максимальным значениями, обозначаемыми P0 и P100 соответственно. К-й процентиль — это некоторое значение Х из этого ряда, которое разбивает все значения в нем на две категории: K% значений меньше X и остальные значения (1-K%), которые больше X.

Для определения процентилей

1. Отсортируйте значения в исследуемом наборе данных в порядке возрастания.
2. Найдите в отсортированном ряду определенное значение, для которого K% значений будет меньше заданного значения. В случае ручного расчета можно использовать формулу n*K%-1, где n — количество элементов в исследуемом ряду значений.
3. Значение, определенное выше, является K процентилем по определению.

Функция ПЕРСЕНТАЦИЯ стала устаревшей с выходом версии MS Office 2010, которая включает функции PROCENTIL.EXCL и PROCENTIL.ON, которые вместе обеспечивают лучшую функциональность вычислений. Эта функция была сохранена для совместимости с более старыми версиями электронной таблицы.

Пример расчета перцентиля с использованием функции ПЕРСЕНТИЛЬ в Excel

Пример 1. В магазин поступает новая партия обуви. В рамках маркетингового исследования были измерены размеры стопы десяти случайных покупателей. На основе имеющихся данных определите размер обуви, который является пороговым для 90% покупателей.

Вид таблицы данных:

Мы будем использовать функцию для его вычисления:

1. B3:B12 — серия проверенных значений
2. 0.9 — число, указывающее на необходимость поиска 90-го процентиля (0.9 = 90%).

В результате расчета по формуле получается значение 90-го процентиля. Найденное значение не соответствует ни одному из рассмотренных рядов, поскольку функция ПЕРСЕНТИЛЬ интерполировала данные. 90% покупателей покупают обувь до 41 размера включительно.

Как рассчитать перцентиль в Excel с помощью функции ПЕРСЕНТИЛЬ

Пример 2. В таблицу введен ряд некоторых значений. Необходимо:

1. Определите, во сколько раз 80-й процентиль превышает 20-й процентиль.
2. Вычислите 40-й процентиль без использования рассматриваемой функции.

Вид таблицы данных:

Чтобы найти значение этого коэффициента, используйте следующую нотацию:

В исследованном ряду значений 80-й процентиль превышает 20-й процентиль почти в 4.5 раза.

Альтернативным методом определения перцентиля является следующая формула:

Эта запись соответствует формуле, указанной в определении перцентиля. Результат вычислений:

Значение, полученное при помощи функции PERSENTIL, показано напротив (справа):

Значения отличаются потому, что рассматриваемая функция интерполирует данные.