Персентиль что такое в excel

Обновлено: 05.07.2024

Функция ПЕРСЕНТИЛЬ в 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-ый процентиль. При увеличении процентиля в выборку попадает больше табличных значений:

На экзамене он отражает, насколько хорошо вы прошли экзамен в индивидуальном порядке. Процентный балл рассчитывается на основе приведенной ниже формулы.

Процент = Набранные баллы / Общий балл х 100 Например, если баллы 80, 80/100 х 100 = 80%

Excel Percentile Function

Процентиль - это показатель вашей успеваемости по отношению к другим, он также зависит от оценок других учеников.

Процентиль = количество студентов, набравших меньше, чем вы / общее количество студентов х 100

Предположим, если ваш счет или оценки 60-й, из 100 студентов это означает, что ваш результат лучше, чем у 60 человек, и, следовательно, ваш процентиль равен 60%. Здесь указано, на какой процент студентов вы опережаете, в том числе и себя

Когда вы вводите функцию PERCENTILE в ячейке Excel, появляются два варианта функции Percentile: функции PERCENTILE.EXC и PERCENTILE.INC. (Объясняется на следующей странице)

Начиная с Excel 2010 года, функция Percentile заменена функциями PERCENTILE.EXC и PERCENTILE.INC.
Функция Excel Percentile используется для составления отчетов о результатах экзаменов или тестов, таких как GRE, GMAT и других вступительных экзаменах.
PERCENTILE.INC - это обновленная версия, которая чаще всего используется для расчета процентиля.
PERCENTILE.INC используется как функция листа и функция VBA в Excel

Определение

Функция процентиля вычисляет K- й процентиль для набора данных или диапазона значений на основе предоставленного значения k

ПРОЦЕНТНАЯ Формула в Excel

Ниже приведена формула Excel PERCENTILE:

Функция Excel Percentile имеет нижеупомянутый аргумент

Массив (обязательный или обязательный аргумент): массив значений данных, для которого вы хотите вычислить K-й процентиль.

K (обязательный или обязательный аргумент): это значение процентиля, которое вы ищете, или значение требуемого процентиля (от 0% до 100% или от 0 до 1)

Например, если вы хотите узнать значение для 60-го процентиля, вы будете использовать «0, 6» в качестве значения вашего процентиля. (Примечание: K - это любой процент, выраженный в десятичном виде, например, 0, 20 для 20%)

PERCENTILE.INC (INC включительно): означает, что значение k находится в диапазоне от 0 до 1 включительно. Когда значение K находится в диапазоне от нуля до единицы, вы получите результат.

PERCENTILE.EXC (EXC является эксклюзивным): значение k находится в диапазоне от 0 до 1 эксклюзивно. Ошибка возникает, если вы используете значение K, которое находится вне допустимого диапазона для набора данных.

Как использовать функцию PERCENTILE в Excel?

PERCENTILE Функция очень проста в использовании. Давайте теперь посмотрим, как использовать функцию PERCENTILE в Excel с помощью нескольких примеров.

Вы можете скачать этот шаблон Excel функции PERCENTILE здесь - Шаблон Excel функции PERCENTILE

PERCENTILE в Excel - Пример № 1

Функция PERCENTILE для расчета 95- го процентиля на вступительном экзамене

В приведенной ниже таблице она содержит имя студента в столбце B (от B8 до B24) и их оценку в столбце C (от C8 до C24). Мне нужно узнать оценку для 95-го процентиля

Применим функцию PERCENTILE в ячейке «E8». Выберите ячейку «E8». где должна быть применена функция PERCENTILE.

Появляется диалоговое окно, в котором необходимо заполнить или ввести аргументы для функции PERCENTILE.INC, т.е. = PERCENTILE (массив, k)

ie = PERCENTILE.INC (C8: C24, 0.95) Здесь данные оценки присутствуют в диапазоне (от C8 до C24), для которого нам нужно применить функцию PERCENTILE.INC

Чтобы ввести аргумент Array, щелкните внутри ячейки C8, и вы увидите выбранную ячейку, затем выберите ячейки до C24. Таким образом, будет выбран диапазон столбцов, т.е. C8: C24

K, это значение процентиля, которое мы ищем. Здесь мне нужно узнать значение для 95-го процентиля, поэтому я буду использовать «0, 95» в качестве значения процентиля. (K - любой процент, выраженный в десятичной форме, то есть 0, 95 для 95%).

После ввода обоих аргументов. = PERCENTILE.INC (C8: C24, 0, 95), т. Е. Возвращает результат для 95-го процентиля, т. Е. 93, 6, как результат в ячейке E8. 95- й процентиль падает между 93 и 96. Excel интерполировал между 93 и 96 баллами, чтобы получить результат 93, 6.

PERCENTILE в Excel - Пример № 2

Функция PERCENTILE для расчета 90- го процентиля на вступительном экзамене

В приведенной ниже таблице она содержит имя студента в столбце G (от G6 до G20) и их оценку по экзамену GRE в столбце H (от H6 до H20). Мне нужно узнать счет для 90-го процентиля

Давайте применим функцию PERCENTILE.INC в ячейке «J8». Выберите ячейку «J8». где ПРОЦЕНТНЫЙ. Функция INC должна быть применена.

ie = PERCENTILE.INC (H6: H20, 0.90) Здесь данные оценки присутствуют в диапазоне (от H6 до H20), для которого нам нужно применить функцию PERCENTILE.INC

Чтобы ввести аргумент Array, щелкните внутри ячейки H6, и вы увидите выбранную ячейку, затем выберите ячейки до H20. Таким образом, будет выбран диапазон столбцов, т.е. H6: H20

K, это значение процентиля, которое мы ищем. Здесь мне нужно выяснить значение для 90-го процентиля. Итак, я буду использовать «0, 90» в качестве значения процентиля. (K - любой процент, выраженный в десятичном виде, то есть 0, 90 для 90%).

после ввода обоих аргументов. = PERCENTILE.INC (H6: H20, 0.90), т. Е. Возвращает результат для 90-го процентиля, т.е. 95, 6, как результат в ячейке J8. 90- й процентиль падает между 95 и 96. Excel интерполировал между 95 и 96 баллами, чтобы получить результат 95, 6

Что нужно помнить о функции PERCENTILE в Excel

В процентильной функции аргумент массива также может быть введен как отдельный номер в указанном ниже формате.

= ПРОЦЕНТНЫЙ ((6, 8, 10, 12), 0, 35)