Персентиль что такое в excel
Сначала разберемся на примерах, что такое процентиль , затем рассмотрим соответствующие функции MS EXCEL.
Задача. Проектируют койку на круизном лайнере. Необходимо, чтобы 95% пассажиров помещались на койке в полный рост. Как вычислить длину койки?
Для решения задачи потребуется найти рост, ниже которого 95% населения. Для этого нужно сделать репрезентативную выборку , скажем, из 2000 человек, отсортировать значения выборки по возрастанию , потом определить значение с позицией равной 1901 (2000*95%+1). Пусть найденный рост оказался равен 190 см. Ответ : Длина койки должна быть 190 см (+ запас для комфортного размещения на койке).
Значение 190 см называется 95%-й процентилью данной выборки , т.е. 95% опрошенных людей имеет рост СОВЕТ : Понятие процентиля связано с понятием квантиля функции распределения . Поэтому имеет смысл освежить в памяти понятия функции распределения и обратной функции .
На основании вышесказанного сформулируем определение для процентили : K-й Процентиль представляет такое собой значение Х в наборе данных, которое разделяет набор на две части: одна часть содержит K процентов данных, меньших Х , а другая часть содержит все остальные значения набора (т.е. 1- K процентов данных б о льших Х).
Приведем алгоритм для нахождения k -й процентили выборки:
- отсортировать значениявыборки по возрастанию (пусть в выборке всего N значений);
- найти такую позицию в списке , для которой k% значений оказалось бы меньше этого значения. Это можно сделать с помощью формулы N*k%+1 (затем, округлить его до целого );
- значение, находящееся в этой позиции, и будет k-й процентилью (примерно), т.к. k% значений массива данных будет меньше этого значения.
Примечание : Более точный алгоритм расчета процентилей дан ниже в разделе про функцию ПРОЦЕНТИЛЬ.ВКЛ() .
Еще одна задача . Зачет «автоматом» поставят только тем студентам, которые в течение семестра набрали в течение семестра больше баллов, чем 90%-я Процентиль (другими словами 10% лучшим студентам поставят зачет «автоматом»).
Так как порог установлен в процентилях , то заданному % студентов придется сдавать экзамен вне зависимости от набранных баллов (т.е. 90% студентов в любом случае будут сдавать экзамен). А вот если бы порог был установлен в абсолютных значениях, например, 380 баллов из 400, то вполне вероятна ситуация, когда половине студентов поставили бы «автоматом» (если бы они, конечно, набрали бы больше 380 баллов). Или наоборот, при общих слабых результатах ни один студент не получил бы зачет «автоматом». Установка порога в процентилях создает предпосылки здоровой конкуренции (или, наоборот, сговора: даже если никто особо не учился, то в любом случае 10% получат зачет «автоматом»).
Решим эту задачу, используя заданные значения выборки . Пусть всего 120 студентов, значения баллов за семестр разместим в диапазоне A8:A127 (см. Файл примера , лист Пример-Студенты ). Максимальный суммарный балл = 400. Порог получения зачета «автоматом» - больше баллов, чем 90%-я Процентиль .
Понятно, чтобы определить тех студентов, которые получат зачет «автоматом» нужно отсортировать их по набранным баллам и отобрать 10% (т.е. 12 студентов) с максимальными баллами. Но, чтобы студенты сами определились, начинать ли им готовиться к экзамену или нет, достаточно сообщить им проходной балл (90%-ю процентиль ). Рассчитаем этот проходной балл.
Для наглядности построим Гистограмму распределения с накоплением .
90%-ю процентиль можно найти с помощью формулы =НАИМЕНЬШИЙ(A8:A127;ЦЕЛОЕ(120*0,9)+1)
Эта формула создана на основе алгоритма, приведенного выше. Результат формулы - 334 балла.
Как видно из рисунка выше, количество значений массива (студентов), у которых баллы хуже, действительно равно 108 (90% от 120). Следовательно, как и предполагалось, 12 студентов получат зачет «автоматом».
Примечание : Найденное значение процентили 334 является приблизительным. Точное значение дает формула =ПРОЦЕНТИЛЬ.ВКЛ(A8:A127;0,9) , которое равно 331,4. О том как работает функция ПРОЦЕНТИЛЬ.ВКЛ() читайте ниже.
Как показывает опыт, для данных выборки K -я процентиль не всегда отделяет точно К процентов значений, которые меньше ее. Например, в нашем примере найдем 80%-ю процентиль. Оказывается, что только 79% значений меньше 80%-й процентили (318). Это происходит из-за округления. Для выборок с большим количеством значений (>100) обычно наблюдается хорошее соответствие. Повторы значений также могут привести к несоответствию значения процентиля и соответствующего % значений (см. ниже).
Примечание : Процентили часто называют перцентилями (с этим соглашается и MS WORD) или центилями . В версии MS EXCEL 2007 и более ранних использовалась функция ПЕРСЕНТИЛЬ() , которая оставлена для совместимости. Но, начиная с версии EXCEL 2010, появились функции ПРОЦЕНТИЛЬ.ВКЛ() и ПРОЦЕНТИЛЬ.ИСКЛ() – английское название PERCENTILE.EXC(), а Условное форматирование предлагает настроить правило с использованием именно процентилей . В свою очередь, надстройка Пакет Анализа имеет инструмент Ранг и Персентиль . Google также отдает предпочтение процентилям , выдавая гораздо больше результатов на запрос «процентиль», чем на запрос «перцентиль» (на начало 2016 года).
Таким образом, для процентилей используется 3 названия: процентиль (MS EXCEL, Google) , персентиль (MS EXCEL) , перцентиль (MS WORD) .
Ниже детально рассмотрим как работает функция ПРОЦЕНТИЛЬ.ВКЛ() и создадим ее аналог с помощью альтернативной формулы. Также рассмотрим функцию ПРОЦЕНТРАНГ.ВКЛ() и кривую процентилей .
СОВЕТ : Нижеследующие разделы следует читать пользователям, владеющими базовыми понятиями математической статистики (случайная величина, функция распределения) .
Функция ПРОЦЕНТИЛЬ.ВКЛ()
Начиная с версии MS EXCEL 2010 для расчета процентилей используется функция ПРОЦЕНТИЛЬ.ВКЛ() – английское название PERCENTILE.INC(). В более ранних версиях MS EXCEL использовался ее аналог - функция ПЕРСЕНТИЛЬ() .
Напомним определение процентиля , данное выше: K-й Процентиль представляет такое собой значение Х в наборе данных, которое разделяет набор на две части: одна часть содержит K% данных, меньших Х , а другая часть содержит все остальные значения набора (т.е. 1- K % данных б о льших Х).
Разберем детально как работает функция ПРОЦЕНТИЛЬ.ВКЛ() .
Пусть имеется массив значений ( выборка ). В массиве 49 значений, массив расположен в диапазоне B15:B63 , имеются повторы значений , массив для удобства отсортирован по возрастанию (см. файл примера , лист ПРОЦЕНТИЛЬ.ВКЛ ).
Проанализируем, что мы получили.
Как видно из рисунка ниже первое значение ( минимальное , равное 1) является 0-й процентилью .
Соответственно, 1-й процентилью (100% процентилью ), является максимальное значение равное 120 (см. файл примера лист ПРОЦЕНТИЛЬ.ВКЛ).
Поясним эту формулу. Для вычисления процентили принимается, что весь диапазон значений массива (от мин до макс) разбит n значениями на равные интервалы (их всего n-1). Соответственно, 1/(n-1), это «ширина» интервала (весь диапазон равен 1 или 100%). Обратите внимание, что «ширина» интервала не зависит от данных, а только от их количества. В нашем случае «ширина» интервала равна 0,0208 или 2,08%.
Приведем алгоритм расчета 12,50%-процентили функцией ПРОЦЕНТИЛЬ.ВКЛ() (см. ячейку С21 ):
- ПРОЦЕНТИЛЬ.ВКЛ() определяет «ширину» интервала (в долях или процентах): =1/(49-1)=0,0208;
- подсчитывает Количество интервалов , которые были укладываются в 12,50%, т.е. =12,50%/2,08%=6 (значение процентиля кратно ширине интервала, т.е. делится нацело);
- 6-й интервал располагается между числами массива 10 и 11. Верхняя граница последнего 6-го интервала равна 11;
- Следовательно, 12,50%-я процентиль равна 11 (см. ячейку B21 ).
По аналогии с непрерывной функцией распределения (см. статью про квантили ), получается, что 12,50% значений должно быть меньше полученного числа 11 (в соответствии с определением процентиля ). Фактически получается, что таких значений 6 (1; 2; 3; 4; 5; 10) и процент значений меньших 11 равен 12,24%=6/49 (причины расхождения: повторы и небольшое количество значений).
Альтернативный расчет процентили с помощью формул приведен в файле примера .
Примечание : Некоторые значения процентилей имеют специальные названия:
- 25-я процентиль называется 1-й квартилью;
- 50-я процентиль называется Медианой (2-я квартиль);
- 75-я процентиль называется 3-й квартилью.
Функция ПРОЦЕНТРАНГ.ВКЛ() и Кривая процентилей
Функция ПРОЦЕНТРАНГ.ВКЛ() используется для оценки относительного положения значения в массиве. Для заданного значения функция вычисляет сколько значений в массиве меньше или равно ему. Точнее - какой процент значений массива меньше или равен ему. Результат функции называется процентиль-ранг (percentile rank) . Понятно, что для максимального значения процентиль-ранг равен 0,00%, а для наименьшего - 100% (все значения массива меньше или равны ему).
Функция ПРОЦЕНТРАНГ.ВКЛ() , английская версия – PERCENTRANK(), является, в каком-то смысле, обратной функции ПРОЦЕНТИЛЬ.ВКЛ() : т.е. задавая в качестве аргумента значение из массива, функция ПРОЦЕНТРАНГ.ВКЛ() вернет значение процентили кратной 1/(n-1).
Как видно из рисунка выше, для повторяющихся значений функция ПРОЦЕНТРАНГ.ВКЛ() вернет, естественно, одинаковые значения. Также поступает функция РАНГ.РВ() или РАНГ() (см. статью Функция РАНГ() в MS EXCEL ).
Действительно, функции РАНГ.РВ() и ПРОЦЕНТРАНГ.ВКЛ() очень похожи. Первая возвращает позицию числа в массиве в зависимости от его значения. Вторая, в принципе, делает тоже самое, но результат выводится в % от общего количества значений в массиве.
Как видно из картинки выше, чтобы получить процентиль-ранг необходимо значение ранга уменьшить на 1 и разделить на n-1. Значение ранга , естественно, должно быть отсортировано по возрастанию .
По выборке можно оценить функцию распределения Генеральной совокупности , из которой взята данная выборка. Для этой цели построим Кривую процентилей (percentile curve или percentile rank plot). Кривая процентилей представляет собой график зависимости процентиль-ранга от значений выборки .
Возьмем выборку состоящую из 100 значений (см. файл примера лист Кривая процентилей ). Значения содержатся в диапазоне А5:А104 .
Сначала построим таблицу частот для каждого из значений выборки .
Примечание : В отличие от Гистограммы , где кумулятивная таблица частот строится для интервалов значений, таблицу частот для Кривой процентилей строят для каждого из значений выборки .
Из таблицы видно (столбец Частота нарастающим итогом ), что примерно 1 процент значений меньше или равен значения 3,27, примерно 2 процента на уровне или ниже 3,28, 5 процентов на уровне или ниже 3,29, и так далее. График Кривой процентилей для этих данных приведен на картинке ниже.
СОВЕТ : Про построение графиков см. статью Основные типы диаграмм .
Следует отметить, что использование данных из таблицы приведет к точечному виду кривой (так как процентиль-ранг будет изменяться скачком для каждого значения выборки ). Поэтому, сглаженная кривая, построенная на основе этих данных будет лучше представлять оцениваемую функцию распределения (пунктирная кривая).
Построив пунктирную кривую, становится ясно, зачем нам пришлось вводить понятие процентиль-ранга: процентиль-ранг – является приблизительной вероятностью выбрать случайную величину меньше или равную соответствующему значению (сравните с определением функции распределения). Это, в частности следует из расчета процентиль-ранга по формуле =СЧЁТЕСЛИ($A$5:$A$104;"
Обратите внимание, что при построении Кривой процентилей никакие значения из выборки не были удалены или сгруппированы. В этом смысле, построение Кривой процентилей это более точная процедура для оценки вида функции распределения , чем построение Гистограммы данных (так как информация не теряется в процессе построения). Правда, для этого требуется достаточно большая выборка (лучше >100 значений).
Примечание : Формула =(РАНГ.РВ(A5;$A$5:$A$104;1)-1)/ (СЧЁТ($A$5:$A$104)-1) эквивалентна формуле =ПРОЦЕНТРАНГ.ВКЛ($A$5:$A$104;A5;5)
Возвращает процентил значений k-th в диапазоне. Используйте эту функцию для установления порога принятия. Например, вы можете проверить кандидатов, которые набрали больше 90 процентила.
Эта функция была заменена на одну или несколько новых функций, которые могут обеспечить улучшенную точность и имена которых лучше отражают их использование. Эта функция по-прежнему доступна для совместимости с более ранними версиями Excel. Однако, если обратная совместимость не требуется, необходимо использовать новые функции с этого времени, так как они более точно описывают свои функции.
Дополнительные сведения о новых функциях см. в Percentile_Inc и Percentile_Exc методах.
Синтаксис
выражения. Percentile (Arg1, Arg2)
выражение Переменная, представляюная объект WorksheetFunction .
Параметры
Имя | Обязательный или необязательный | Тип данных | Описание |
---|---|---|---|
Arg1 | Обязательный | Variant | Массив — массив или диапазон данных, определяя относительное положение. |
Arg2 | Обязательный | 64-разрядное число с плавающей запятой двойной точности. | K — процентильное значение в диапазоне 0.1 включительно. |
Возвращаемое значение
64-разрядное число с плавающей запятой двойной точности.
Примечания
Если k не является нескольким из 1/(n - 1), процентил интерполирует, чтобы определить значение в k-th percentile.
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Функция ПЕРСЕНТИЛЬ в Excel предназначена для определения k-й доли перцентили для числовых значений исследуемого интервала и возвращает соответствующий результат.
Метод перцентилей в Excel по функции ПЕРСЕНТИЛЬ с примерами
Предположим, имеется вариационный ряд данных с минимальным и максимальным значениями, обозначаемых P0 и P100 соответственно. K-й перцентиль – это некоторое значение X из данного ряда, которое делит все имеющиеся в нем значения на две группы: K% значений, которые меньше X, и оставшиеся значения (то есть 1-K%), которые превышают X.
Для определения перцентилей необходимо:
- Отсортировать значения в исследуемом ряде данных в порядке возрастания.
- Найти некоторое значение в отсортированном ряде, для которого K% значений будут меньшими данного значения. При ручном расчете можно использовать формулу n*K%-1, где n – число элементов в исследуемом ряде значений.
- Определенное выше значение является K-й перцентилю по определению.
Функция ПЕРСЕНТИЛЬ считается устаревшей после выхода MS Office версии 2010 года, в которую были включены функции ПРОЦЕНТИЛЬ.ИСКЛ и ПРОЦЕНТИЛЬ.ВКЛ, которые в совокупности предлагают расширенный функционал для расчетов. Рассматриваемая функция была оставлена для совместимости с более старыми версиями табличного редактора.
Пример расчета перцентиля с использованием функции ПЕРСЕНТИЛЬ в Excel
Пример 1. В магазин будет завезена новая партия обуви. Ранее в рамках маркетингового исследования были записаны размеры ног 10 случайных клиентов. На основании имеющихся данных определить размер обуви, являющийся пороговым значением для 90% клиентов.
Вид таблицы данных:
Для расчета используем функцию:
- B3:B12 – исследуемый ряд значений;
- 0,9 – число, указывающее, что необходим поиск 90-й перцентили (0,9=90%).
В результате вычислений формулы получен 90 перцентиль. Найденное значение не соответствует ни одному из рассматриваемого ряда, поскольку функция ПЕРСЕНТИЛЬ выполнила интерполяцию данных. 90% клиентов покупают обувь до 41 размера включительно.
Как рассчитать перцентиль в Excel с помощью функции ПЕРСЕНТИЛЬ
Пример 2. В таблице введен ряд некоторых значений. Необходимо:
- Определить, во сколько раз 80-й перцентиль превышает 20-й перцентиль.
- Рассчитать 40-й перцентиль без использования рассматриваемой функции.
Вид таблицы данных:
Для поиска значения соотношения используем следующую запись:
То есть, для исследуемого ряда значений 80-й перцентиль превышает 20-й почти в 4,5 раз.
Альтернативный способ нахождения перцентиля – следующая формула:
Данная запись соответствует формуле, указанной в определении понятия перцентиль. Результат вычислений:
Рядом (справа) указано значение, полученное с использованием функции ПЕРСЕНТИЛЬ:
Значения отличаются, поскольку рассматриваемая функция выполняет интерполяцию данных.
Процентильная шкала определяет позицию определенного значения среди других данных в базе. Процентили в первую очередь используются для описания стандартных результатов тестов. Если же результат в стандартном тесте находится в 90-ом процентиле – это значит, что данный результат является выше чем 90% результатов показателей которые принимают участие в тесте. Другими словами, результат находится среди 10% самых высоких показателей, использованных в тесте.
Пример вычисления формулы процентиля в Excel
Перцентили (они же процентили или персентили) часто применяются в анализе данных. Они являются инструментом для оценки результатов на фоне целой группы данных. С их помощью можно, например, определить персентильную классификацию работника по его годовому обороту.
В программе Excel персентильную классификацию можно легко определить при использовании функции ПЕРСЕНТИЛЬ. Данная функция имеет 2 аргументы:
- Массив – диапазон исходных данных.
- К – значение найденного процентиля (чаще всего число в десятичной дроби диапазоном от 0 и до 1).
В примере, изображенном ниже на рисунке ячейка D6 содержит значение, которое является результатом вычисления ниже указанной формулы – число 0,75 процентиля данных диапазоне ячеек $B$2:$B$19:
Результат выше приведенной формулы указывает на то, что каждый работник, для которого годовые обороты превышают 52651 работает лучше, чем 75% всех остальных сотрудников.
Ячейка D15 содержит результат вычисления формулы, которая возвращает число 25 процентиля данных в диапазоне ячеек $B$2:$B$19.
Результат выше приведенной формулы указывает на то, что каждый работник, для которого годовой оборот не превышает 24656 находится среди 25% самых слабых сотрудников.
В данном примере используется условное форматирование использующие выше приведенные значения перцентилей. Значения больше чем 75 перцентиля выделены зеленым цветом, а значения меньше чем 25 перцентиля выделены красным цветом.
Два правила условного форматирования для одного диапазона ячеек в Excel
Чтобы создать описанную схему автоматического выделения ячеек по условию пользователя, выполните целый ряд следующих действий шаг за шагом:
- Выделите диапазон ячеек B2:B19, которые будут автоматически выделятся цветом по условию формулы и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило». В результате чего высветится окно как ниже на рисунке:
- В верхней части окна находится список опций. Выберите из него опцию «Использовать формулу для определения форматируемых ячеек». Данная опция служит для преобразования формата в ячейках в зависимости от их значений с помощью определенной формулы с логическим выражением. Если в результате вычисления формулы будет возвращено логическое значение ИСТИНА, тогда к текущей ячейке будет применено условное форматирование.
- В полю ввода введите формулу с логическим выражением, которая представлена ниже в этом разделе. Данная формула проверяет: если значение в целевой ячейке B2 меньше чем значение 25 перцентиля, тогда ей присваивается новый формат красного цвета фона для экспонирования.
В результате к одному и тому же диапазону ячеек одновременно применяется 2 правила условного форматирования. В следствии чего одна группа значений выделена красным цветом значение которых меньше 25% показателей, а вторая группа – зеленым >75%.
Термин ″процентиль″ (percentile) часто встречается в зарубежной литературе про инвестиции и вообще в статистических исследованиях – одно из таких исследований я упоминал в этой статье. Другими вариантами русских названий являются ″персентиль″ и ″перцентиль″. Думаю, что небольшая известность термина в России не вполне заслужена, поскольку нередко именно в процентилях удобно выразить полученные результаты.
Процентиль это определенная часть выборки данных
Но задачу можно поставить и по-другому: нужно сравнить результаты студентов не с проходным баллом в 66 пунктов, а между собой. Зачем это нужно? В данном случае, например, для объективной оценки сложность теста, что достигается группировкой результатов.
Процентили делят всю выборку на определенные части. Например, пятый процентиль охватывает 5% объема выборки. Предположим, показатель Ивана равен пятому процентилю. Это означает, что Иван написал тест лучше, чем 5% студентов (10 человек из 200 получили от нуля до 70 баллов). Не густо, поскольку в этом случае остальные 190 человек набрали больше, чем 70. Значит, тест был очень легкий и порог в 66 баллов можно и повысить.
Функция Гаусса
Процентиль можно пояснить и на примере симметричного распределения Гаусса, которое часто встречается в статистике для оценки веса, роста и т.п. На рисунке выше показаны 25, 50, 75 и 100 процентили. Случаи 25 и 75-ого процентиля, включающие четверть и три четверти выборки соответственно, называются квартилями .
Чем более высок процентиль, тем больше данных он включает
Расчет процентиля в Excel
Процентиль несложно вычислить по формуле:
но проще обрабатывать массив данных одноименной функцией Excel. Для примера возьмем произвольную выборку полученных баллов и рассчитаем в ней процентили:
Функция PERCENTILE (ПЕРСЕНТИЛЬ) включает в себя ввод диапазона ячеек данных (А1:А10) и значения процентиля К, деленного на 100%. Т.е. в данном случае ввод 0.3 означает нахождение тридцатого процентиля. Смысл расчета: к 30-му процентилю будут относиться все результаты, меньше или равные 7.9.
Если мы хотим узнать процентиль участника, получившего 10 баллов, то это несложно сделать, варьируя значение К до тех пор, пока значение в ячейке С12 не станет близким 10. Получится примерно 34-ый процентиль. При увеличении процентиля в выборку попадает больше табличных значений:
Итого, в 80-ый процентиль попадает уже 8 табличных значений из 10, которые меньше или равны 47.2. При этом подчеркнуть результат можно диапазоном процентилей — например, между восьмидесятым и сотым. В этом случае значения будут лежать между 47.2 и 67 (максимальным числом выборки).
Читайте также: