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

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

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

Предположим, имеется вариационный ряд данных с минимальным и максимальным значениями, обозначаемых P0 и P100 соответственно. K-й перцентиль – это некоторое значение X из данного ряда, которое делит все имеющиеся в нем значения на две группы: K% значений, которые меньше X, и оставшиеся значения (то есть 1-K%), которые превышают X.

Для определения перцентилей необходимо:

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

Примечание:

Функция ПЕРСЕНТИЛЬ считается устаревшей после выхода MS Office версии 2010 года, в которую были включены функции ПРОЦЕНТИЛЬ.ИСКЛ и ПРОЦЕНТИЛЬ.ВКЛ, которые в совокупности предлагают расширенный функционал для расчетов. Рассматриваемая функция была оставлена для совместимости с более старыми версиями табличного редактора.

Читайте также: Примеры функции КВАРТИЛЬ в Excel для расчета медианы квартиля.

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

Пример 1. В магазин будет завезена новая партия обуви. Ранее в рамках маркетингового исследования были записаны размеры ног 10 случайных клиентов. На основании имеющихся данных определить размер обуви, являющийся пороговым значением для 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 раз.

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

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

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

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

Что такое функция ПЕРСЕНТИЛЬ и как с ней работать в Excel

Функция имеет следующий синтаксис:

=ПЕРСЕНТИЛЬ(массив;k)

Описание аргументов:

• массив – обязательный для заполнения, принимает статический массив числовых данных или ссылку на диапазон ячеек с числами, для которых требуется вычислить значение k-го перцентиля;
• k – обязательный для заполнения, принимает числовые значения из диапазона от 0 до 1 (оба включительно), характеризующие номер перцентили для расчета (например, 0,25 – 25-я перцентиль, 0,5 – 50-я перцентиль).

Примечания:

1. Перцентиль удобен для установления критериев отбора каких-либо данных. Например, на вступительных экзаменах почти все студенты не смогли преодолеть проходной порог (минимальное количество баллов для поступления в ВУЗ). Чтобы избежать недобора, можно ввести другой критерий – перцентиль, который поможет отобрать лучших абитуриентов на основании имеющихся данных о баллах за экзамены, а не установленных ранее критериев (проходного балла).
2. Если исследуемый ряд (указан в виде аргумента массив) содержит нечисловые данные (текст, логические ИСТИНА или ЛОЖЬ, имена), функция исключает их из расчетов. Например, =ПЕРСЕНТИЛЬ({1;4;5;7;11};0,5) вернет значение 5, а =ПЕРСЕНТИЛЬ({1;4;"е";7;11};0,5) – 5,5.
3. Функция возвращает код ошибки #ЗНАЧ!, если аргумент k указан в виде нечисловых данных (имя или текст, не преобразуемые в число). Нечисловые данные, преобразуемые к числам, являются допустимыми вариантами указания аргумента k. Например, =ПЕРСЕНТИЛЬ({1;4;5;7;11};”0,5”) вернет значение 5, =ПЕРСЕНТИЛЬ({1;4;5;7;11};ИСТИНА) – 11.
4. Рассматриваемая функция генерирует код ошибки #ЧИСЛО!, если аргумент k задан в виде числа не из диапазона допустимых значений, то есть >1 или <0.
5. Для значений k, не кратных 1/(n-1), функция интерполирует данные для расчетов (n – число элементов массива).