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

Процентильная шкала определяет позицию определенного значения среди других данных в базе. Процентили в первую очередь используются для описания стандартных результатов тестов. Если же результат в стандартном тесте находится в 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 перцентиля, тогда ей присваивается новый формат красного цвета фона для экспонирования.
=B2<=ПЕРСЕНТИЛЬ($B$2:$B$19;0,25)
Нажмите на кнопку «Формат». Появится окно «Формат ячеек», в котором находятся все опции для форматирования шрифта, границы и фона ячеек. Главное укажите красный фон заливки. После указания своих пользовательских опций форматирования нажмите на кнопки ОК на все открытых окнах чтобы подтвердить и применить все изменения.

Снова выделите диапазон ячеек B2:B19 и на этот раз уже выберите инструмент «ГЛАВНАЯ»-«Условное форматирование»-«Управление правилами». В результате появится окно «Диспетчер правил условного форматирования», где находится наше первое правило. Для создания второго правила в этом же окне нажмите на кнопку «Создать правило».

И снова в появившемся окне «Создание правила форматирования» выберите опцию «Использовать формулу для определения форматируемых ячеек».
В полю ввода формул на этот раз введите другую формул представленную на этом шаге. Данная формула проверяет: если значение в целевой ячейке B2 больше чем 75 персентиля, тогда применить для нее новый формат (зеленый фон).

Снова нажмите на кнопку «Формат» где на этот раз следует указать зеленый фон заливки для ячеек. После чего нажмите на кнопку ОК для всех открытых окон.

В результате к одному и тому же диапазону ячеек одновременно применяется 2 правила условного форматирования. В следствии чего одна группа значений выделена красным цветом значение которых меньше 25% показателей, а вторая группа – зеленым >75%.