Как быстро рассчитать процентиль или квартиль без учета нулей в Excel?
Примеры функции ПЕРСЕНТИЛЬ для расчета перцентиля в Excel
С помощью функции ПЕРСЕНТИЛЬ Excel можно вычислить k-й процентиль для числовых значений в исследуемом диапазоне, а затем вернуть результаты.
Метод перцентилей в Excel по функции ПЕРСЕНТИЛЬ с примерами
Имеется вариационный ряд данных с минимальным и максимальным значениями, обозначаемыми P0 и P100 соответственно. К-й процентиль — это некоторое значение Х из этого ряда, которое разбивает все значения в нем на две категории: K% значений меньше X и остальные значения (1-K%), которые больше X.
Для определения процентилей
- Отсортируйте значения в исследуемом наборе данных в порядке возрастания.
- Найдите в отсортированном ряду определенное значение, для которого K% значений будет меньше заданного значения. В случае ручного расчета можно использовать формулу n*K%-1, где n — количество элементов в исследуемом ряду значений.
- Значение, определенное выше, является K процентилем по определению.
Функция ПЕРСЕНТАЦИЯ стала устаревшей с выходом версии MS Office 2010, которая включает функции PROCENTIL.EXCL и PROCENTIL.ON, которые вместе обеспечивают лучшую функциональность вычислений. Эта функция была сохранена для совместимости с более старыми версиями электронной таблицы.
Пример расчета перцентиля с использованием функции ПЕРСЕНТИЛЬ в Excel
Пример 1. В магазин поступает новая партия обуви. В рамках маркетингового исследования были измерены размеры стопы десяти случайных покупателей. На основе имеющихся данных определите размер обуви, который является пороговым для 90% покупателей.
Вид таблицы данных:
Мы будем использовать функцию для его вычисления:
- B3:B12 — серия проверенных значений
- 0.9 — число, указывающее на необходимость поиска 90-го процентиля (0.9 = 90%).
В результате расчета по формуле получается значение 90-го процентиля. Найденное значение не соответствует ни одному из рассмотренных рядов, поскольку функция ПЕРСЕНТИЛЬ интерполировала данные. 90% покупателей покупают обувь до 41 размера включительно.
Как рассчитать перцентиль в Excel с помощью функции ПЕРСЕНТИЛЬ
Пример 2. В таблицу введен ряд некоторых значений. Необходимо:
- Определите, во сколько раз 80-й процентиль превышает 20-й процентиль.
- Вычислите 40-й процентиль без использования рассматриваемой функции.
Вид таблицы данных:
Чтобы найти значение этого коэффициента, используйте следующую нотацию:
В исследованном ряду значений 80-й процентиль превышает 20-й процентиль почти в 4.5 раза.
Альтернативным методом определения перцентиля является следующая формула:
Эта запись соответствует формуле, указанной в определении перцентиля. Результат вычислений:
Значение, полученное при помощи функции PERSENTIL, показано напротив (справа):
Значения отличаются потому, что рассматриваемая функция интерполирует данные.