Процентиль что это в эксель
Что такое процентиль?
Что такое процентиль?
Термин ″процентиль″ (percentile) часто встречается в зарубежной литературе про инвестиции и вообще в статистических исследованиях – одно из таких исследований я упоминал в этой статье. Другими вариантами русских названий являются ″персентиль″ и ″перцентиль″. Думаю, что небольшая известность термина в России не вполне заслужена, поскольку нередко именно в процентилях удобно выразить полученные результаты.
Процентиль это определенная часть выборки данных
Возьмем простой пример. Группа студентов из 200 человек пишет тест, состоящий из 100 вопросов. Проходной порог, когда тест считается сданным, составляет 2/3 правильных ответов, т.е. не менее 66. Что получается с точки зрения отдельного студента?
Допустим, Иван правильно ответил на 70 вопросов. Задачу он выполнил — тест засчитан. Результат каждого участника теста также сравнивается с числом 66: если правильных ответов больше, тест сдан. В результате формируется список сдавших и не сдавших: каждый студент проходил через это. Пока ничего нового.
Но задачу можно поставить и по-другому: нужно сравнить результаты студентов не с проходным баллом в 66 пунктов, а между собой. Зачем это нужно? В данном случае, например, для объективной оценки сложность теста, что достигается группировкой результатов.
Вернемся к Ивану, который получил 70 правильных ответов. Много это или мало по сравнению с остальными? Это и покажет процентиль.
Процентили делят всю выборку на определенные части. Например, пятый процентиль охватывает 5% объема выборки. Предположим, показатель Ивана равен пятому процентилю. Это означает, что Иван написал тест лучше, чем 5% студентов (10 человек из 200 получили от нуля до 70 баллов). Не густо, поскольку в этом случае остальные 190 человек набрали больше, чем 70. Значит, тест был очень легкий и порог в 66 баллов можно и повысить.
Но в том же самом тесте может быть и обратная ситуация: результат Ивана равен 90-ому процентилю. Это значит, что Иван написал тест лучше, чем 90% студентов. Или по другому: только 10% (20 человек) набрали более 70 правильных ответов. Следовательно, тест был весьма трудным. Преимущество метода еще и в том, что разбивкой на процентили можно сравнивать тесты с разным числом участников.
Функция Гаусса
Чем более высок процентиль, тем больше данных он включает
Расчет процентиля в Excel
Процентиль несложно вычислить по формуле:
но проще обрабатывать массив данных одноименной функцией Excel. Для примера возьмем произвольную выборку полученных баллов и рассчитаем в ней процентили:
Функция PERCENTILE (ПЕРСЕНТИЛЬ) включает в себя ввод диапазона ячеек данных (А1:А10) и значения процентиля К, деленного на 100%. Т.е. в данном случае ввод 0.3 означает нахождение тридцатого процентиля. Смысл расчета: к 30-му процентилю будут относиться все результаты, меньше или равные 7.9.
Если мы хотим узнать процентиль участника, получившего 10 баллов, то это несложно сделать, варьируя значение К до тех пор, пока значение в ячейке С12 не станет близким 10. Получится примерно 34-ый процентиль. При увеличении процентиля в выборку попадает больше табличных значений:
Итого, в 80-ый процентиль попадает уже 8 табличных значений из 10, которые меньше или равны 47.2. При этом подчеркнуть результат можно диапазоном процентилей — например, между восьмидесятым и сотым. В этом случае значения будут лежать между 47.2 и 67 (максимальным числом выборки).
Функция ПЕРСЕНТИЛЬ
Возвращает k-ю персентиль для значений из интервала. Эта функция используется для определения порога приемлемости. Например, можно принять решение экзаменовать только тех кандидатов, которые набрали большее количество баллов, чем 90-ая персентиль.
Эта функция была заменена одной или несколькими новыми функциями, которые обеспечивают более высокую точность и имеют имена, лучше отражающие их назначение. Хотя эта функция все еще используется для обеспечения обратной совместимости, она может стать недоступной в последующих версиях Excel, поэтому мы рекомендуем использовать новые функции.
Дополнительные сведения о новых функциях см. в статьях Функция ПРОЦЕНТИЛЬ.ИСКЛ и Функция ПРОЦЕНТИЛЬ.ВКЛ.
Синтаксис
Аргументы функции ПЕРСЕНТИЛЬ описаны ниже.
Массива — обязательный аргумент. Массив или диапазон данных, который определяет относительное положение.
K Обязательный. Значение процентили в интервале от 0 до 1, включая эти числа.
Замечания
Если k не является числом, возвращается #VALUE! значение ошибки #ЗНАЧ!.
Если k 1, функция ПЕРСЕНТИЛЬ возвращает значение ошибки #ЧИСЛО!.
Пример
В этом примере мы видим 30-ю процентиль списка в ячейках E2:E5.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Примеры функции ПЕРСЕНТИЛЬ для расчета перцентиля в Excel
Функция ПЕРСЕНТИЛЬ в Excel предназначена для определения k-й доли перцентили для числовых значений исследуемого интервала и возвращает соответствующий результат.
Метод перцентилей в Excel по функции ПЕРСЕНТИЛЬ с примерами
Предположим, имеется вариационный ряд данных с минимальным и максимальным значениями, обозначаемых P0 и P100 соответственно. K-й перцентиль – это некоторое значение X из данного ряда, которое делит все имеющиеся в нем значения на две группы: K% значений, которые меньше X, и оставшиеся значения (то есть 1-K%), которые превышают X.
Для определения перцентилей необходимо:
Функция ПЕРСЕНТИЛЬ считается устаревшей после выхода MS Office версии 2010 года, в которую были включены функции ПРОЦЕНТИЛЬ.ИСКЛ и ПРОЦЕНТИЛЬ.ВКЛ, которые в совокупности предлагают расширенный функционал для расчетов. Рассматриваемая функция была оставлена для совместимости с более старыми версиями табличного редактора.
Пример расчета перцентиля с использованием функции ПЕРСЕНТИЛЬ в Excel
Пример 1. В магазин будет завезена новая партия обуви. Ранее в рамках маркетингового исследования были записаны размеры ног 10 случайных клиентов. На основании имеющихся данных определить размер обуви, являющийся пороговым значением для 90% клиентов.
Вид таблицы данных:
Для расчета используем функцию:
В результате вычислений формулы получен 90 перцентиль. Найденное значение не соответствует ни одному из рассматриваемого ряда, поскольку функция ПЕРСЕНТИЛЬ выполнила интерполяцию данных. 90% клиентов покупают обувь до 41 размера включительно.
Как рассчитать перцентиль в Excel с помощью функции ПЕРСЕНТИЛЬ
Пример 2. В таблице введен ряд некоторых значений. Необходимо:
Вид таблицы данных:
Для поиска значения соотношения используем следующую запись:
То есть, для исследуемого ряда значений 80-й перцентиль превышает 20-й почти в 4,5 раз.
Альтернативный способ нахождения перцентиля – следующая формула:
Данная запись соответствует формуле, указанной в определении понятия перцентиль. Результат вычислений:
Рядом (справа) указано значение, полученное с использованием функции ПЕРСЕНТИЛЬ:
Значения отличаются, поскольку рассматриваемая функция выполняет интерполяцию данных.
Что такое функция ПЕРСЕНТИЛЬ и как с ней работать в Excel
Функция имеет следующий синтаксис:
Процентили в EXCEL
history 19 ноября 2016 г.
Задача. Проектируют койку на круизном лайнере. Необходимо, чтобы 95% пассажиров помещались на койке в полный рост. Как вычислить длину койки?
Приведем алгоритм для нахождения k -й процентили выборки:
Понятно, чтобы определить тех студентов, которые получат зачет «автоматом» нужно отсортировать их по набранным баллам и отобрать 10% (т.е. 12 студентов) с максимальными баллами. Но, чтобы студенты сами определились, начинать ли им готовиться к экзамену или нет, достаточно сообщить им проходной балл (90%-ю процентиль ). Рассчитаем этот проходной балл.
90%-ю процентиль можно найти с помощью формулы =НАИМЕНЬШИЙ(A8:A127;ЦЕЛОЕ(120*0,9)+1)
Как видно из рисунка выше, количество значений массива (студентов), у которых баллы хуже, действительно равно 108 (90% от 120). Следовательно, как и предполагалось, 12 студентов получат зачет «автоматом».
Как показывает опыт, для данных выборки K -я процентиль не всегда отделяет точно К процентов значений, которые меньше ее. Например, в нашем примере найдем 80%-ю процентиль. Оказывается, что только 79% значений меньше 80%-й процентили (318). Это происходит из-за округления. Для выборок с большим количеством значений (>100) обычно наблюдается хорошее соответствие. Повторы значений также могут привести к несоответствию значения процентиля и соответствующего % значений (см. ниже).
Таким образом, для процентилей используется 3 названия: процентиль (MS EXCEL, Google) , персентиль (MS EXCEL) , перцентиль (MS WORD) .
Функция ПРОЦЕНТИЛЬ.ВКЛ()
Проанализируем, что мы получили.
Соответственно, 1-й процентилью (100% процентилью ), является максимальное значение равное 120 (см. файл примера лист ПРОЦЕНТИЛЬ.ВКЛ).
Поясним эту формулу. Для вычисления процентили принимается, что весь диапазон значений массива (от мин до макс) разбит n значениями на равные интервалы (их всего n-1). Соответственно, 1/(n-1), это «ширина» интервала (весь диапазон равен 1 или 100%). Обратите внимание, что «ширина» интервала не зависит от данных, а только от их количества. В нашем случае «ширина» интервала равна 0,0208 или 2,08%.
Приведем алгоритм расчета 12,50%-процентили функцией ПРОЦЕНТИЛЬ.ВКЛ() (см. ячейку С21 ):
По аналогии с непрерывной функцией распределения (см. статью про квантили ), получается, что 12,50% значений должно быть меньше полученного числа 11 (в соответствии с определением процентиля ). Фактически получается, что таких значений 6 (1; 2; 3; 4; 5; 10) и процент значений меньших 11 равен 12,24%=6/49 (причины расхождения: повторы и небольшое количество значений).
Если бы в массиве вместо 5 было значение 6, то значение 0,08-й процентили было бы равно 5,68 (4 – левая граница интервала + (6-4)*0,84=1,68).
Примечание : Некоторые значения процентилей имеют специальные названия:
Функция ПРОЦЕНТРАНГ.ВКЛ() и Кривая процентилей
Как видно из рисунка выше, для повторяющихся значений функция ПРОЦЕНТРАНГ.ВКЛ() вернет, естественно, одинаковые значения. Также поступает функция РАНГ.РВ() или РАНГ() (см. статью Функция РАНГ() в MS EXCEL ).
Действительно, функции РАНГ.РВ() и ПРОЦЕНТРАНГ.ВКЛ() очень похожи. Первая возвращает позицию числа в массиве в зависимости от его значения. Вторая, в принципе, делает тоже самое, но результат выводится в % от общего количества значений в массиве. 
Из таблицы видно (столбец Частота нарастающим итогом ), что примерно 1 процент значений меньше или равен значения 3,27, примерно 2 процента на уровне или ниже 3,28, 5 процентов на уровне или ниже 3,29, и так далее. График Кривой процентилей для этих данных приведен на картинке ниже. 
Следует отметить, что использование данных из таблицы приведет к точечному виду кривой (так как процентиль-ранг будет изменяться скачком для каждого значения выборки ). Поэтому, сглаженная кривая, построенная на основе этих данных будет лучше представлять оцениваемую функцию распределения (пунктирная кривая).
Построив пунктирную кривую, становится ясно, зачем нам пришлось вводить понятие процентиль-ранга: процентиль-ранг – является приблизительной вероятностью выбрать случайную величину меньше или равную соответствующему значению (сравните с определением функции распределения). Это, в частности следует из расчета процентиль-ранга по формуле =СЧЁТЕСЛИ($A$5:$A$104;»
Примечание : Формула =(РАНГ.РВ(A5;$A$5:$A$104;1)-1)/ (СЧЁТ($A$5:$A$104)-1) эквивалентна формуле =ПРОЦЕНТРАНГ.ВКЛ($A$5:$A$104;A5;5)
Как посчитать процентиль по формуле ПЕРСЕНТИЛЬ в Excel
Процентильная шкала определяет позицию определенного значения среди других данных в базе. Процентили в первую очередь используются для описания стандартных результатов тестов. Если же результат в стандартном тесте находится в 90-ом процентиле – это значит, что данный результат является выше чем 90% результатов показателей которые принимают участие в тесте. Другими словами, результат находится среди 10% самых высоких показателей, использованных в тесте.
Пример вычисления формулы процентиля в Excel
Перцентили (они же процентили или персентили) часто применяются в анализе данных. Они являются инструментом для оценки результатов на фоне целой группы данных. С их помощью можно, например, определить персентильную классификацию работника по его годовому обороту.
В программе Excel персентильную классификацию можно легко определить при использовании функции ПЕРСЕНТИЛЬ. Данная функция имеет 2 аргументы:
Результат выше приведенной формулы указывает на то, что каждый работник, для которого годовые обороты превышают 52651 работает лучше, чем 75% всех остальных сотрудников.
Результат выше приведенной формулы указывает на то, что каждый работник, для которого годовой оборот не превышает 24656 находится среди 25% самых слабых сотрудников.
В данном примере используется условное форматирование использующие выше приведенные значения перцентилей. Значения больше чем 75 перцентиля выделены зеленым цветом, а значения меньше чем 25 перцентиля выделены красным цветом.
Два правила условного форматирования для одного диапазона ячеек в Excel
Чтобы создать описанную схему автоматического выделения ячеек по условию пользователя, выполните целый ряд следующих действий шаг за шагом: