Как использовать функцию Rank и СУММПРОИЗВ в Excel с помощью функции СЧЁТЕСЛИ

Ранжирование элементов в порядке возрастания или убывания с использованием комбинации функций СЧЁТЕСЛИ, RANK.EQ и СУММПРОИЗВ относительно просто. Вы можете сортировать элементы с дублированием или без него и сохранять контроль, чтобы пропускать или не пропускать какие-либо ранги в последовательности.

Присваивая одинаковые ранги нескольким идентичным записям, функция RANK.EQ пропускает числа в общей последовательности. Чтобы избежать этого пропуска, используйте функцию СУММПРОИЗВ с функцией СЧЁТЕСЛИ. Эта комбинация ранжирует повторяющиеся значения без пропуска чисел.

Различные функции ранжирования в Excel

Прежде чем мы более подробно рассмотрим его реализацию, давайте кратко рассмотрим его две другие формы, найденные в Excel; RANK.AVG и RANK.

В более ранних версиях Excel была доступна только функция РАНГ. Однако, начиная с Excel 2010, были добавлены две дополнительные функции ранжирования, а именно RANK.AVG и RANK.EQ. Давайте рассмотрим их различия, поскольку вы, вероятно, увидите их все в Excel.

RANK.EQ Функция: RANK.EQ – это новая улучшенная версия функции RANK, которая поддерживается в последних версиях Excel. Обе функции, однако, одинаковы и служат одной цели. Что бы вы ни использовали, результат будет одинаковым.

Функция RANK: функция RANK, с другой стороны, доступна для совместимости с Excel 2007 и более ранними версиями и может быть удалена в любой из новых версий Excel. Сегодня все используют самые последние версии Excel, поэтому всегда используйте функцию RANK.EQ, когда вам нужно поделиться листами с другими.

КЛАССИФИЦИРОВАТЬ. AVG: когда какая-либо последовательность рангов содержит дубликаты, функция RANK.AVG присваивает средний ранг каждому набору дубликатов. Таким образом, он служит той же цели для ранжирования элементов, но его основная цель – отловить дубликаты.

Поскольку эта статья посвящена вычислению рангов для списка элементов, давайте посмотрим, как функция RANK.EQ будет ранжировать их. Кроме того, вы увидите его использование в сочетании с функцией СЧЁТЕСЛИ.

Перед реализацией формулы давайте кратко рассмотрим синтаксис функции RANK.EQ.

Обзор функции ранжирования в Excel

Итак, в функции RANK.EQ есть три аргумента; номер, ссылка и заказ. Первые два аргумента являются обязательными, а третий – необязательным. Чтобы выполнить RANK.EQ для массива чисел, вы всегда должны указывать первые два аргумента.

Давайте посмотрим на функцию каждого аргумента:

1. Число: этот аргумент относится к числу, ранг которого вы хотите узнать.
2. Ссылка: Аргумент Ссылка относится к массиву списков чисел в последовательности. Имейте в виду, что ссылка игнорирует любые нечисловые значения, найденные в массиве.
3. Порядок: Несмотря на то, что это необязательный аргумент, вы можете указать функцию RANK для ранжирования элементов в порядке убывания или возрастания, выбрав в этом аргументе 0 или 1. По умолчанию, если вы не укажете его, элементы будут ранжироваться в порядке убывания.

Пример функции ранжирования в Excel

Итак, теперь, когда вы понимаете аргументы функции RANK.EQ, давайте применим ее на практике к набору данных, чтобы увидеть, как она работает.

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

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

Первый аргумент – это число, которое вы хотите ранжировать, то есть процентное соотношение Джеймса Уокера и других студентов в нижнем ряду для этого примера. Итак, выберите ячейку B2 в качестве первого аргумента.

Контрольный диапазон в этом случае будет от ячеек B2 до B11, и давайте выберем порядок возрастания в аргументе порядка. Ниже вы можете увидеть реализацию функции RANK.EQ. Формула выглядит так:

 =RANK.EQ(B2,$B$2:$B$11,0)

Нажатие Enter приведет к выполнению формулы, и вы получите звание Джеймса Уокера.

В этом примере Джеймс Уокер занимает 5- ^е место в классе по своему проценту, что означает, что его процент является 5- ^м наивысшим по сравнению с остальными числами в массиве.

Использование функции RANK в сочетании с функцией COUNTIF

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

Чтобы лучше понять это, давайте посмотрим, как RANK.EQ работает вместе с COUNTIF. Формула выглядит так:

 =RANK.EQ(B2,$B$2:$B$11,0)+COUNTIF($B$2:B2,B2)-1

Реализация этой формулы решит проблему пропуска чисел.

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

Таким образом, использование RANK.EQ с COUNTIF решает половину проблемы, но не дает желаемого результата.

Связанный: Новые функции Excel в Интернете для повышения вашей производительности

Использование функции СУММПРОИЗВ с функцией СЧЁТЕСЛИ

Чтобы ранжировать учащихся в списке путем присвоения одинаковых рангов для эквивалентных процентов, не пропуская ни одного числа, вы можете использовать функцию СУММПРОИЗВ с СЧЁТЕСЛИ.

Взгляните на формулу ниже:

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

Создавая результат для учащихся, вы можете напрямую использовать формулу СУММПРОИЗВ как замену функции РАНГ. Для недублирующих вычислений ранжирования вы можете использовать функцию RANK.EQ отдельно или с функцией COUNTIF.

Изменение порядка окончательного результата

На вкладке «Данные» щелкните группу « Сортировка и фильтр » и выберите порядок возрастания, чтобы упорядочить рейтинги.

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

Связано: как таблица данных в Microsoft Excel позволяет сравнивать результаты

Ранжируйте элементы с помощью функций RANK.EQ и SUMPRODUCT

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

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