Как быстро и легко определить ранг в Excel

В этом руководстве объясняются особенности формул ранжирования и показано, как определить ранг в Microsoft Excel на основе нескольких критериев, ранжировать данные по группам, рассчитывать процентильный ранг, как определить места по возрастанию или по убыванию, если есть несколько одинаковых значений, и многое другое.

Когда вам нужно определить относительное положение числа в списке чисел, проще всего отсортировать числа по возрастанию или убыванию. Если по какой-то причине сортировка невозможна, формула ранга в Excel станет идеальным инструментом для выполнения этой работы.

Содержание

Функция РАНГ

Функция РАНГ в Excel возвращает порядок (или ранг) числового значения по сравнению с другими значениями в том же списке. Другими словами, она сообщает вам, какое значение является самым высоким, вторым по величине и т. д.

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

С ее помощью можно рассчитать ранг числа, начиная с наибольшего значения (как если бы оно было отсортировано в порядке убывания) или наименьшего значения (как если бы оно было отсортировано по возрастанию).

Синтаксис РАНГ следующий:

РАНГ(число; ссылка; [порядок])

Где:

Число (обязательно) — значение, для которого определяется ранг.

Ссылка (обязательно) — список числовых значений для ранжирования (или ссылка на список чисел).

Порядок (необязательно) — число, указывающее, как ранжировать значения:

Если 0 или опущено, значения ранжируются в порядке убывания, т.е. от наибольшего к наименьшему.
Если 1 или любое другое ненулевое значение, значения рассматриваются в порядке возрастания, т.е. от наименьшего к наибольшему.

Функция РАНГ.РВ

РАНГ.РВ (в английской версии RANK.EQ) — это улучшенная версия функции РАНГ, представленная в Excel 2010. Она имеет тот же синтаксис, что и РАНГ, и работает по той же логике: если несколько значений одинаковы, всем таким значениям присваивается наивысший для них ранг. (РВ означает «равный»).

РАНГ.РВ(число; ссылка; [порядок])

В Excel 2007 и более ранних версиях всегда следует использовать функцию РАНГ. В Excel 2010, Excel 2013 и Excel 2016 вы можете использовать РАНГ или РАНГ.РВ. Однако было бы разумно использовать РАНГ.РВ, потому что РАНГ может быть удалена из списка доступных функций в любой момент (правда, уже более 10 лет этого не происходит).

Функция РАНГ.СР

РАНГ.СР (в английсой версии RANK.AVG) — это еще одна функция для определения рейтинга в Excel, доступная только в Excel 2010, Excel 2013 и более поздних версиях.

Она имеет тот же синтаксис, что и две ранее рассмотренные нами функции:

РАНГ.СР(число; ссылка; [порядок])

Разница в том, что если более одного числа имеют один и тот же ранг, возвращается средний ранг (СР означает «средний»). То есть, если два значения имеют одинаковое значение и поэтому должны иметь одинаковый ранг, то присваивается среднее значение (например, два одинаковых значения, которые занимают 3-е место, будут иметь ранг 3.5)

5 фактов, которые вы должны знать о РАНГ в Excel

Любая формула ранга в Excel работает только для числовых значений: положительных и отрицательных чисел, нулей, значений даты и времени. Нечисловые значения просто игнорируются.
Не обязательно сортировать значения в списке перед использованием РАНГ.
Все функции РАНГ возвращают один и тот же ранг для повторяющихся значений и пропускают последующее значение ранга (например 1, 1, 3, 3, 5, 6), как будет показано в примерах ниже.
В Excel 2010 и более поздних версиях функция РАНГ заменена на РАНГ.РВ и РАНГ.СР. В целях обратной совместимости старая функция РАНГ по-прежнему работает во всех версиях Excel, но в будущем она может стать недоступной.
Если число не найдено среди списка значений в аргументе ссылка, любая из трёх функций ранжирования вернет ошибку #Н/Д.

Базовая формула ранга Excel (от самого высокого к самому низкому)

Чтобы лучше понять, как определяется ранг в Excel, взгляните на этот скриншот:

Все три формулы ранжируют числа в столбце B в порядке убывания (аргумент порядок опущен):

Во всех версиях Excel формула РАНГ:

=РАНГ($B2;$B$2:$B$7)

В Excel 2010 и новее:

=РАНГ.РВ($B2;$B$2:$B$7)

=РАНГ.СР($B2;$B$2:$B$7)

Разница заключается в том, как эти формулы обрабатывают повторяющиеся значения. Как видите, один и тот же балл появляется дважды, в ячейках B5 и B6, что влияет на результат:

Формулы Excel РАНГ и РАНГ.РВ присваивают 4-е место обоим повторяющимся оценкам. Следующая по величине оценка (Геннадий) занимает 6-е место. Место 5 никому не присваивается.
Формула РАНГ.СР виртуально присваивает каждому дубликату разный ранг (4 и 5 в этом примере) и возвращает среднее значение этих рангов (4,5). Опять же, 5-е место никому не присвоено.

Как найти ранг в Excel — примеры формул

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

Как ранжировать в Excel от низшего к высшему

Как показано в приведенном выше примере, чтобы ранжировать числа от самого высокого к самому низкому, вы используете одну из формул ранга Excel с аргументом порядок, установленным на 0 или опущенным (по умолчанию).

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

Например, чтобы оценить время выполнения задания, вы можете использовать любую из следующих формул:

=РАНГ(B2;$B$2:$B$7;1)

=РАНГ.РВ(B2;$B$2:$B$7;1)

Обратите внимание, что мы фиксируем диапазон в аргументе ссылка, используя абсолютные ссылки на ячейки , чтобы они не менялись при копировании формулы вниз по столбцу.

В результате наименьшее значение (самое быстрое время) получает первое место, а наибольшее значение (самое медленное время) получает самый низкий ранг 6.

Как присвоить ранг без повторений мест

Как указывалось ранее, все функции ранжирования Excel возвращают одинаковый ранг для одинаковых чисел. Если вы этого не хотите, используйте одну из следующих формул для присвоения уникального ранга каждому числу.

Ранг без повторов мест от большего к меньшему

Чтобы однозначно ранжировать оценки студентов в порядке убывания, используйте следующую формулу:

=РАНГ.РВ(B4;$B$3:$B$8)+СЧЁТЕСЛИ($B$3:B4;B4)-1

Как видите, список чисел содержит одинаковые баллы. Студенты с такими баллами занимают на основе этой формулы разное место. Оно будет выше у того, кто находится ближе к началу списка.

Другой способ однозначно ранжировать числа в Excel — сложить две функции СЧЕТЕСЛИ:

=СЧЁТЕСЛИ($B$3:$B$8;">"&$B3)+СЧЁТЕСЛИ($B$3:B3;B3)

Первая функция определяет, сколько значений больше или меньше числа, подлежащего ранжированию, в зависимости от того, ранжируете ли вы по убыванию или по возрастанию соответственно.
Вторая функция (с «расширяющимся диапазоном» $B$3:B3, как в приведенном выше примере) получает количество значений, равное числу.

Результат будет точно такой же, как на скриншоте выше. Можете выбрать для себя любую из формул.

Ранг без повторов по возрастанию

Чтобы ранжировать результаты времени выполнения задания в порядке возрастания без дублирования, используйте следующую формулу:

=РАНГ.РВ(B3;$B$3:$B$8;1)+СЧЁТЕСЛИ($B$3:B3;B3)-1

Ранг без повторов от меньшего к большему

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

Ранг без повторов и без пропуска позиций

В наших предыдущих примерах мы рассмотрели, как присвоить ранг без повторов, если имеются одинаковые значения. Но не всех может устроить то, что более высокое место получает тот, кто просто находится выше в списке.

Давайте рассмотрим подход, когда одинаковые значения получают одинаковое место, но при этом не происходит пропуск позиций, как в функциях РАНГ и РАНГ.РВ.

Здесь нам помогут функции СУММПРОИЗВ и СЧËТЕСЛИ.

=СУММПРОИЗВ((B3<=$B$3:$B$10)/СЧЁТЕСЛИ($B$3:$B$10;$B$3:$B$10))

Ранг без повторов и без пропуска позиций в Excel

Как видите, здесь мы имеем два вторых и два третьих места. Формула определяет места без пропусков, подряд.

Если же вам нужен обратный рейтинг, то есть, от меньшего к большему, то просто измените в формуле знак «<» на «>»:

=СУММПРОИЗВ((B3>=$B$3:$B$10)/СЧЁТЕСЛИ($B$3:$B$10;$B$3:$B$10))

Тогда формула определит вам два четвертых и два пятых места:

Ранжирование по возрастанию без пропусков мест

Ранг с условием

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

В нашем примере рассчитаем рейтинг следующим образом:

Сначала оцените балл первого теста (основной критерий).
Если есть одинаковые значения, определите лучшего при помощи результата второго теста (дополнительный критерий).

Могу предложить 3 варианта формул расчета рангов:

=РАНГ.РВ($B2;$B$2:$B$7)+СЧЁТЕСЛИМН($B$2:$B$7;$B2;$C$2:$C$7;">"&$C2)

=СЧЁТЕСЛИ($B$2:$B$7;">"&$B2)+СЧЁТЕСЛИМН($B$2:$B$7;$B2;$C$2:$C$7;">"&$C2)+1

=СЧЁТЕСЛИ($B$2:$B$7;">"&$B2)+СУММПРОИЗВ(--($B$2:$B$7=$B2);--($C$2:$C$7>$C2))+1

Результаты вы видите на скриншоте ниже:

Как видите, каждая из этих формул Excel состоит из двух основных частей:

Первая часть формулы подсчитывает вхождения ранжируемого значения. Обратите внимание: мы фиксируем диапазон подсчёта с помощью абсолютных ссылок, но не фиксируем строку значения, чтобы после копирования вниз по столбцу формула проверяла значение в каждой строке.
Вторая часть формулы определяет, сколько значений в столбце дополнительного критерия больше текущего значения дополнительного критерия, если основные критерии одинаковы.

Как рассчитать процентильный ранг

В статистике процентиль — это значение, ниже которого падает определенный процент значений в данном наборе данных. Например, если 70% учащихся имеют результат вашего теста равный или ниже определенного балла, процентильный рейтинг этого балла равен 70.

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

Итак, общая формула процентильного ранга Excel выглядит следующим образом:

РАНГ.РВ( самая верхняя_ячейка ; диапазон ; 1)/СЧËТ( диапазон )

Для расчета процентильного ранга наших студентов формула принимает следующий вид:

=РАНГ.РВ(B2;$B$2:$B$9;1)/СЧЁТ($B$2:$B$9)

Чтобы результаты отображались корректно, обязательно установите для ячеек формулы процентный формат:

Как видите, для максимального значения балла (94) процентиль равен 100%, то есть все значения меньше или равны ему. Для балла 89 перцентиль показывает, что меньше или равны этому баллу 87.5% всех оценок студентов.

Как ранжировать числа в несмежных ячейках

В ситуациях, когда вам необходимо ранжировать несмежные ячейки, укажите эти ячейки непосредственно в аргументе ссылка формулы ранга Excel в виде объединения ссылок, фиксируя их знаком $. Например:

=РАНГ($B2;($B$2;$B$4;$B$6))

Чтобы предотвратить появление ошибки Excel в неранжированных ячейках, заключите формулу РАНГ в функцию ЕСЛИОШИБКА, например:

=ЕСЛИОШИБКА(РАНГ($B2;($B$2;$B$4;$B$6)); "")

Как ранжировать числа в несмежных ячейках Excel

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

=ЕСЛИОШИБКА(РАНГ.РВ($B2;range); "")

Как ранжировать в Excel по группам

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

Функция РАНГ не может решить эту проблему, поэтому мы собираемся использовать более сложную формулу СУММПРОИЗВ:

Ранжирование по группам от большего к меньшему :

=СУММПРОИЗВ(($A2=$A$2:$A$7)*($C2<$C$2:$C$7))+1

Ранжирование по группам от меньшего к большему :

=СУММПРОИЗВ(($A2=$A$2:$A$7)*($C2>$C$2:$C$7))+1

где:

A2:A7 — учебные группы.
C2:C7 — баллы, подлежащие ранжированию.

В этом примере вы можете видеть, как работают обе эти формулы:

Как ранжировать положительные и отрицательные числа отдельно

Если ваш список чисел содержит как положительные, так и отрицательные значения, функция Excel РАНГ мгновенно проранжирует их все. Но что, если вы хотите, чтобы положительные и отрицательные числа ранжировались отдельно?

Используйте одну из следующих формул, чтобы получить отдельный рейтинг для положительных и отрицательных значений:

Ранжируйте положительные числа по убыванию (от большего к меньшему):

=ЕСЛИ($A3>0;СЧЁТЕСЛИ($A$3:$A$11;">"&A3)+1;"")

Ранжируйте положительные числа по возрастанию (от меньшего к большему):

=ЕСЛИ($A3>0;СЧЁТЕСЛИ($A$3:$A$11;">0")-СЧЁТЕСЛИ($A$3:$A$11;">"&$A3);"")

Ранжируйте отрицательные числа по убыванию:

=ЕСЛИ($A3<0;СЧЁТЕСЛИ($A$3:$A$11;"<0")-СЧЁТЕСЛИ($A$3:$A$11;"<"&$A3);"")

Ранжируйте отрицательные числа по возрастанию:

=ЕСЛИ($A3<0;СЧЁТЕСЛИ($A$3:$A$11;"<"&$A3)+1;"")

Результаты будут выглядеть примерно так:

Как ранжировать положительные и отрицательные числа отдельно

Примечание. Все приведенные выше формулы игнорируют нулевые значения , поскольку 0 не принадлежит ни множеству положительных, ни множеству отрицательных чисел. Чтобы включить нули в свой рейтинг, замените >0 и <0 на >=0 и <=0 соответственно, где этого требует логика ваших расчетов.

Например, чтобы ранжировать положительные числа и нули от большего к меньшему, используйте следующую формулу:

=ЕСЛИ($A3>=0;СЧЁТЕСЛИ($A$3:$A$11;">"&A3)+1;"")

Как ранжировать данные в Excel, игнорируя нулевые значения

Как вы уже знаете, формула РАНГ Excel обрабатывает все числа: положительные, отрицательные и нули. Но в некоторых случаях мы просто хотим ранжировать ячейки с данными, игнорируя нулевые значения. В сети можно найти несколько возможных решений этой задачи, но формула РАНГ ЕСЛИ, на мой взгляд, является наиболее универсальной:

Ранг по убыванию, игнорируя ноль:

=ЕСЛИ($B2=0;""; ЕСЛИ($B2>0; РАНГ($B2;$B$2:$B$10); РАНГ($B2;$B$2:$B$10) - СЧЁТЕСЛИ($B$2:$B$10;0)))

Ранг по возрастанию, игнорируя ноль:

=ЕСЛИ($B2=0;""; ЕСЛИ($B2>0; РАНГ($B2;$B$2:$B$10;1) - СЧЁТЕСЛИ($B$2:$B$10;0); РАНГ($B2;$B$2:$B$10;1)))

Где B2:B10 — диапазон чисел, подлежащих ранжированию.

Эта формула прекрасно работает как с положительными, так и с отрицательными числами, оставляя нулевые значения вне ранжирования:

Как рассчитать ранг в Excel по абсолютному значению

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

Задачу можно решить с помощью одной из приведенных ниже формул, в основе которой лежит функция ABS, возвращающая абсолютное значение числа:

Ранг по абсолютной величине по убыванию:

=СУММПРОИЗВ((ABS(A3)<=ABS(A$3:A$8))*(A$3:A$8<>"")) - СУММПРОИЗВ((ABS(A3)=ABS($A$3:$A$8))*(A$3:A$8<>""))+1

Ранг по абсолютному значению по возрастанию:

=СУММПРОИЗВ((ABS(A3)>=ABS(A$3:A$8))*(A$3:A$8<>"")) - СУММПРОИЗВ((ABS(A3)=ABS($A$3:$A$8))*(A$3:A$8<>""))+1

В результате ранг отрицательных чисел определяется так, как если бы они были положительными числами:

Если вы хотите получить N самых больших значений, а не их рейтинг, используйте функцию НАИБОЛЬШИЙ.

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

Надеюсь, эти примеры будут вам полезны.