Как посчитать ранг в Excel: РАНГ.РВ, повторы и НАИБОЛЬШИЙ для премий отдела

Премию дают трём лучшим, бонус — тем, кто выше середины, а план пересматривают для последних мест. Ранг в Excel находит эти места без перестановки строк: по справке Microsoft функция РАНГ.РВ даёт номер места числа в списке, а нечисловые значения в ссылке игнорирует^[1]. Перестановка строк тоже показала бы порядок, но испортила бы исходную таблицу.

Путь к рейтингу короткий: найти столбец с числами, ввести РАНГ.РВ, проверить места на одинаковых значениях, исправить повторы и #Н/Д и протянуть формулу с закреплённым диапазоном на свои данные. Ранг в Excel редко ошибается заметно: перепутанный порядок или сползший диапазон дают правдоподобные, но чужие места.

Ключевые факты: ранг в Excel

• РАНГ.РВ возвращает место числа в списке; при порядке 0 или пустом аргументе наибольшее число получает 1^[1].
• МЕДИАНА принимает до 255 аргументов и при чётном количестве чисел берёт среднее двух центральных^[7].
• Справка Microsoft называет функцию РАНГ устаревшей: она сохранена для совместимости^[2].
• Нечисловые значения в диапазоне ранга игнорируются^[1].
• РАНГ.СР отличается от РАНГ.РВ тем, что при повторах возвращает средний ранг, а не одинаковое верхнее место^[5].

Как работает ранг в Excel: функция РАНГ.РВ и порядок

Ранг в Excel показывает место числа в списке: у наибольшего значения ранг 1. Синтаксис по справке: =РАНГ.РВ(число;ссылка;[порядок])^[1]. В английском интерфейсе функция называется RANK.EQ.

Если порядок равен 0 или опущен, ранги считаются так, будто список отсортирован по убыванию; любое ненулевое значение даёт ранг по возрастанию^[1]. По возрастанию ранжируют, когда лучший результат — минимальный: время, брак, срок доставки. Ранг в Excel считается для любых чисел — сумм, баллов, процентов и дат.

Функция ранга в Excel с одинаковыми числами ведёт себя по-особому: они получают одинаковый ранг и сдвигают следующие места. Справка приводит пример: если 10 дважды стоит на 5-м месте, 11 получит ранг 7, а ранга 6 не будет^[1].

Справка Microsoft относит РАНГ.РВ к Excel 2016, Excel 2019, Excel 2021, Excel 2024 и Microsoft 365, включая Mac^[1]. Старая функция РАНГ заменена новыми и, по справке, может стать недоступной в последующих версиях Excel^[2]. В Google Таблицах есть RANK, RANK.EQ и RANK.AVG с теми же аргументами^[3].

Разобравшись с аргументами, переходим к рейтингу на живой таблице.

Пошаговый рейтинг менеджеров по продажам

Ранг каждого менеджера считает одна формула РАНГ.РВ, протянутая вниз с закреплённым диапазоном. Пример — продажи менеджеров за месяц, в тысячах.

1. Выберите ячейку C2 и введите =РАНГ.РВ(B2;.
2. Выделите B2:B7 и нажмите F4, чтобы диапазон стал $B$2:$B$7.
3. Закройте скобку и нажмите Enter — у Ивановой появится 3.
4. Протяните формулу до C7.
5. Проверьте строки с одинаковыми продажами и последнее место.

На этом листе ранг вышел таким, как в таблице: Сидоров первый, Петров и Козлова делят 4-е место, Орлов шестой, а 5-го места нет^[4]. С третьим аргументом 1 Орлов с наименьшими продажами получил ранг 1^[4].

Без знака доллара формула ранга сползает при протягивании: диапазон смещается вниз, и нижние строки сравниваются с укороченным списком. Подробнее о закреплении — на странице про абсолютные ссылки и клавишу F4. Если в списке появится слово, например «отпуск», ранг посчитается только среди чисел^[1].

Проверка на граничных строках обязательна: формула ранга не сообщает, что место поделено, а премию за 4-е место получат двое. Соберите этот рейтинг в тренажёре: урок меняет продажи и проверяет, что диапазон закреплён. Однако для статистики и уникальных номеров правило «равным — одно место» не годится: там нужны другие формулы.

Чем отличаются РАНГ.РВ, РАНГ.СР и место без повторов

РАНГ.РВ даёт равным числам одно место, РАНГ.СР — среднее из занятых мест. Третий вариант — уникальное место по порядку строк — собирают из ранга и подсчёта уже встреченных значений.

Все результаты получены на листе-примере^[4]. РАНГ.СР при одинаковых значениях возвращает средний ранг^[5]: 380 делят 4-е и 5-е места, отсюда 4,5. Такой ранг нужен в статистике, например в ранговой корреляции.

Справка РАНГ.РВ даёт и поправку, которая превращает одинаковое место в средний ранг: (СЧЁТ(ссылка)+1-РАНГ.РВ(число;ссылка;0)-РАНГ.РВ(число;ссылка;1))/2^[1]. Для неповторяющегося числа поправка равна 0, а для 380 на нашем листе — 0,5^[4].

Место без повторов держится на диапазоне $B$2:B5: начало закреплено, а конец растёт при протягивании. Кто стоит в таблице выше, тот получает меньший номер, поэтому после перестановки строк номера могут поменяться. Ранг в Excel с таким счётчиком подходит для выдачи имён по местам: каждое место занято ровно одной строкой.

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

Медиана и НАИБОЛЬШИЙ: середина ряда и первые 3 места

Ранг отвечает на вопрос о месте числа, а НАИБОЛЬШИЙ возвращает число на заданном месте. Две функции дополняют друг друга: =НАИБОЛЬШИЙ($B$2:$B$7;1) даёт максимум, а при k, равном числу точек, — минимум^[6].

Результаты получены на листе-примере^[4]. НАИБОЛЬШИЙ возвращает #ЧИСЛО!, если массив пуст, k не больше 0 или k больше числа точек^[6]; на листе k = 7 при шести продажах дал именно эту ошибку^[4].

Медиана — середина ряда: МЕДИАНА принимает до 255 аргументов, а при чётном количестве чисел возвращает среднее двух средних значений^[7]. Отсортированные продажи — 295, 380, 380, 420, 460, 510, середина лежит между 380 и 420, отсюда 400. Медиана пяти чисел на том же листе дала ровно центральное значение — 380^[4].

Текст, логические значения и пустые ячейки МЕДИАНА пропускает, а нули учитывает^[7]. На листе пустая ячейка в диапазоне оставила медиану 400, а ноль на её месте сдвинул медиану к 380^[4].

Именно поэтому для «типичного» менеджера смотрят на медиану, а для премии лучшим — на место в рейтинге.

Когда ранг не подойдёт: группы, текст и видимые строки

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

• Если нужно место внутри отдела или региона, РАНГ.РВ сравнит число со всем диапазоном: ранг по условию собирают подсчётом по двум условиям.
• Если искомого числа нет в списке, функция вернёт #Н/Д: на листе ранг числа 400 дал именно эту ошибку^[4].
• При тексте в диапазоне ранг посчитается только среди чисел^[1] — сначала переведите числа, сохранённые как текст, в числовой формат.
• Когда рейтинг нужен только по видимым строкам, ранг не поможет: у РАНГ.РВ нет аргумента, который пропускал бы скрытые строки^[1], а сумму и среднее по видимым строкам считает ПРОМЕЖУТОЧНЫЕ.ИТОГИ^[8].
• Если по месту нужно подтянуть имя, при повторяющихся рангах поиск найдёт только первого — сначала сделайте место без повторов, а затем подставьте имя связкой ИНДЕКС и ПОИСКПОЗ.

Ранг внутри отдела удобно держать в отдельном столбце рядом с общим местом: так видно и положение в компании, и положение среди коллег, а формулы проверяются независимо. Коды 9 и 109 в ПРОМЕЖУТОЧНЫЕ.ИТОГИ складывают видимые строки; разница в строках, скрытых вручную: код 109 их пропускает^[8]. Остальные статистические и расчётные функции собраны в обзоре формул Excel для анализа данных.