Ранжирование с RANK и PERCENTILE

Часто Вам нужно знать не только само значение, но и его положение внутри группы. Этот продавец на первом или на двенадцатом месте? Этот результат входит в лучшие 10%?

На эти вопросы отвечают два семейства функций: RANK показывает порядковое место, а PERCENTILE — значение на заданной границе. Вместе они превращают исходные числа в рейтинги и ориентиры.

RANK.EQ определяет положение значения в диапазоне. Укажите значение, диапазон и флаг порядка.

• Порядок 0 (или пропущенный) сортирует от большего к меньшему.
• Порядок 1 сортирует от меньшего к большему.

Здесь самый высокий показатель продаж получает ранг 1.

Обратите внимание на знаки доллара: $B$2:$B$20. Диапазон должен оставаться фиксированным при копировании формулы вниз, тогда как ссылка на значение B2 изменяется на B3, B4 и так далее.

Если забыть знаки доллара, диапазон будет смещаться с каждой строкой, и ранги окажутся бессмысленными. Абсолютные ссылки для диапазона здесь необходимы.

RANK.EQ присваивает одинаковым значениям одинаковый ранг, а затем пропускает следующий. Если два значения делят второе место, оба получают 2, а следующее — 4 (третьего места нет).

Если вместо этого Вам нужен средний ранг для одинаковых значений, используйте RANK.AVG. Два значения, делящие второе и третье места, получат по 2.5.

Чтобы получить уникальные ранги даже при совпадениях, добавьте вспомогательный подсчёт COUNTIF. Он подсчитывает, сколько равных значений находится выше текущей строки, и добавляет это смещение.

Так Вы гарантированно получите последовательность 1, 2, 3, 4 без повторов, что удобно для таблиц лидеров.

PERCENTILE.INC отвечает на вопрос: какое значение находится на заданной процентной отметке? Передайте диапазон и дробь от 0 до 1.

Приведённая ниже формула возвращает 90-й процентиль — значение, ниже которого находится 90% данных. Это идеально подходит для задания порогов вроде «лучшие 10% исполнителей».

Квартили делят данные на четыре части. Функция QUARTILE.INC — это сокращённая запись: первая квартиль равна 25-му процентилю, вторая — 50-му (медиане), а третья — 75-му.

Формула ниже возвращает третью квартиль, что эквивалентно PERCENTILE.INC(A2:A101,0.75).

Есть два варианта: PERCENTILE.INC (включительный, принимает значения 0 и 1) и PERCENTILE.EXC (исключительный, принимает только значения между 0 и 1, никогда не возвращая крайние значения).

Для большинства повседневных отчётов стандартным выбором является .INC. Используйте .EXC, если следуете статистическим соглашениям, исключающим граничные точки.

RANK определяет позицию, а PERCENTILE — значение на заданной границе. PERCENTRANK.INC выполняет обратную операцию по отношению к процентилю: по заданному значению определяет, какому процентилю оно соответствует.

Если учащийся набрал 88 баллов, а PERCENTRANK возвращает 0.92, значит, этот учащийся показал результат выше, чем 92% группы. Умножьте значение на 100, чтобы отобразить его в процентах.

RANK и PERCENTILE работают только с числами. Текстовые подписи и пустые ячейки внутри диапазона игнорируются, поэтому случайно оставшийся заголовок не нарушит расчёт ранга.

Однако будьте внимательны с числами, сохранёнными как текст: они полностью пропускаются, из-за чего изменяются все ранги и процентили. Если ранг выглядит неверным, убедитесь, что все значения являются настоящими числами, а не текстом, выровненным по левому краю.

У вас есть 20 менеджеров по продажам, а их объёмы продаж указаны в столбце B. Вы хотите определить ранг каждого менеджера и проверить, входит ли он в верхнюю квартиль.

• Ранг: =RANK.EQ(B2,$B$2:$B$21,0)
• Признак верхней квартили сравнивает объём его продаж с 75-м процентилем.

Формула ниже возвращает для каждого менеджера значение «Лидер» или «Стандартный» в зависимости от граничного значения.

Проверьте своё понимание функций ранжирования.

Вы научились определять положение значений в группе:

• RANK.EQ возвращает порядковую позицию; порядок 0 означает сначала наибольшее значение, а 1 — сначала наименьшее. Зафиксируйте диапазон с помощью $.
• RANK.AVG усредняет ранги одинаковых значений; добавьте COUNTIF, чтобы получить уникальные ранги.
• PERCENTILE.INC возвращает значение на заданной границе; QUARTILE.INC — это сокращённая запись для 25-го, 50-го и 75-го процентилей.
• PERCENTRANK.INC выполняет обратную операцию: определяет процентиль для заданного значения.