Как в excel сделать ранжирование: Функции РАНГ, РАНГ.РВ и РАНГ.СР для ранжирования данных в Excel

Как ранжировать в Excel по нескольким критериям — шаг за шагом

Приложения

0 2.620 3 минут на чтение

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

ранжирование

Это относится к классификации, которая устанавливает порядок элементов в соответствии с одним или несколькими критериями оценки. Таким образом, он ставит на первое место то, которое, по его мнению, имеет большее значение, чем второе значение, и так далее.

В Excel при создании рейтинга рекомендуем использовать PivotTables если у тебя есть версия 2013 года или выше . С ним проще и удобнее работать, тем более, если вы разбираетесь в большом количестве данных.

Также было бы неплохо, если вы рассматриваете формулу массива. Это очень хорошо работает, если вы используете интерактивная панель управления ou динамические данные (когда данные часто меняются, например, раз в неделю или раз в месяц).

Создайте рейтинг в Excel с более чем одним критерием

Кто-то сказал: «С иллюстрациями мы лучше понимаем друг друга». Что ж, мы объясним вам на примере, чтобы вы поняли, как используйте формулы . У нас есть таблица с 4 столбцами. Столбец A содержит список людей. Столбец B — это количество (#) транзакций. Столбец C — сумма общих продаж. В столбце D мы разместим рейтинг или позиции.

• Наша цель — найти лучших продавцов на основе их продаж. В столбце Positions мы напишем формулу для достижения нашей цели.
• Мы будем использовать Функция иерархии . Сосредоточьтесь на первом критерии. Продажи в столбце C. Обратите внимание, что данные начинаются с ячейки C2 по ячейку C11.
• Напишем: ИЕРАРХИЯ.EQV (C2; C2: C11; 0). Мы ставим «0», чтобы получить правильный порядок.
• Мы должны добавить абсолютные ссылки, чтобы получить правильный рейтинг. Окончательная формула выглядит так: HIERARCH.EQV (C2; $ C $ 2: $ C $ 11; 0) .
• Затем копируем формулу из всей колонки D. И начинает появляться рейтинг самых продаваемых.
• Для тай-брейка добавьте в формулу, и это будет выглядеть так: HIERARCH.EQV (C2; $ C $ 2: $ C $ 11; 0) + COUNTIF ($ C $ 2: C2; C2) = 1.

Есть еще один способ связать себя узами брака с учетом 2-го критерия . Количество сделок в столбце B. Сравнивая с этими данными, мы дойдем до тай-брейка. Это еще одна альтернативная формула, которая одновременно охватывает 2 критерия: = COUNTIF ($ C $ 2: $ C $ 11, «>» & C1) + 1 + СУММПРОИЗВ (- ($ C $ 1: $ C $ 99 = C1), — ($ B $ 1: $ B $ 99> B1 )).  

Ранее мы включили функция иерархии . Он состоит в том, чтобы вернуть нам позицию, которую занимает значение в определенном диапазоне значений. Его ссылка: ИЕРАРХИЯ (число; ссылка; порядок).

• Число относится к значению или положению, которое мы хотим знать в группе элементов.
• Ссылкой будет матрица, в которой мы найдем числовое значение.
• Командир. Это число (0 или 1). Определите, как мы будем ранжировать значения на основе базового уровня. Таким образом, если вы введете 0, он будет искать позицию в порядке убывания. Если вы введете 1, он будет искать позицию в порядке возрастания. 

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

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

Подобные предметы

Функция РАНГ() в EXCEL. Примеры и описание

Функция

РАНГ(

)

, английский вариант RANK(),

возвращает

ранг числа в списке чисел. Ранг числа — это его величина относительно других значений в списке. Например, в массиве {10;20;5} число 5 будет иметь ранг 1, т.к. это наименьшее число, число 10 — ранг 2, а 20 — ранг 3 (это ранг по возрастанию, когда наименьшему значению присваивается ранг 1). Если список отсортировать, то ранг числа будет его позицией (если нет повторов).

Синтаксис

РАНГ

(

число

;

ссылка

;порядок)

Число

— число, для которого определяется ранг.

Ссылка

— ссылка на список чисел (диапазон ячеек с числами). Напрямую массив задать нельзя, формула =РАНГ(10;{10:50:30:40:50}) работать не будет. Но, если ввести формулу

=РАНГ(B7;$A$7:$A$11)

, то она будет работать (хотя ячейка

B7

— вне списка с числами). Если в

B7

содержится число вне списка с числами, то формула вернет ошибку #Н/Д.

Нечисловые значения в ссылке игнорируются. Числам, сохраненным в текстовом формате, ранг также не присваивается, функция воспринимает их как текст.

Порядок

— число, определяющее способ упорядочения.

• Если порядок равен 0 (нулю) или опущен, то MS EXCEL присваивает ранг=1 максимальному числу, меньшим значениям присваиваются б
  
  о
  
  льшие ранги.
• Если порядок — любое ненулевое число, то то MS EXCEL присваивает ранг=1 минимальному числу, б
  
  о
  
  льшим значениям присваиваются б
  
  о
  
  льшие ранги.

Примечание

: Начиная с MS EXCEL 2010 для вычисления ранга также используются функции

РАНГ.СР()

и

РАНГ.РВ()

. Последняя функция аналогична

РАНГ()

.

Определяем ранг в списке без повторов

Если список чисел находится в диапазоне

A7:A11

, то формула

=РАНГ(A7;$A$7:$A$11)

определит ранг числа из ячейки

А7

(см.

файл примера

).

Т.к. аргумент

порядок

опущен, то MS EXCEL присвоил ранг=1 максимальному числу (50), а максимальный ранг (5 = количеству значений в списке) — минимальному (10).

Альтернативный вариант:

=СЧЁТЕСЛИ($A$7:$A$11;»>»&A7)+1

В столбце

С

приведена формула

=РАНГ(A7;$A$7:$A$11;1)

с рангом по возрастанию, ранг=1 присвоен минимальному числу. Альтернативный вариант:

=СЧЁТЕСЛИ($A$7:$A$11;»

Если исходный список

отсортировать

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

Ранг по условию

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

В

файле примера

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

=СЧЁТЕСЛИМН($A$3:$A$22;A3;$B$3:$B$22;»>»&B3)+1

В столбце А содержатся названия группы, в столбце В — значения.

Связь функций

НАИБОЛЬШИЙ()

/

НАИМЕНЬШИЙ()

и

РАНГ()

Функции

НАИБОЛЬШИЙ()

и

РАНГ()

являются взаимодополняющими в том смысле, что записав формулу

=НАИБОЛЬШИЙ($A$7:$A$11;РАНГ(A7;$A$7:$A$11))

мы получим тот же исходный массив

A7:A11

.

Определяем ранг в списке с повторами

Если список содержит

повторы

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

РАНГ()

или

РАНГ.РВ()

) или среднее значение, если

РАНГ.СР()

). Наличие повторяющихся чисел влияет на ранги последующих чисел. Например, если в списке целых чисел, отсортированных по возрастанию, дважды встречается число 10, имеющее ранг 5, число 11 будет иметь ранг 7 (ни одно из чисел не будет иметь ранга 6).

Иногда это не удобно и требуется, чтобы ранги не повторялись (например, при определении призовых мест, когда нельзя занимать нескольким людям одно место).

В этом нам поможет формула

=РАНГ(A37;A$37:A$44)+СЧЁТЕСЛИ(A$37:A37;A37)-1

Предполагается, что исходный список с числами находится в диапазоне

А37:А44

.

Примечание

. В

MS EXCEL 2010

добавилась функция

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

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

файле примера

дается пояснение работы этой функции. Также добавилась функция

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

Если несколько значений имеют одинаковый ранг, возвращается среднее.

Массив рангов

Для построения некоторых сложных

формул массива

может потребоваться

массив

рангов, т.е. тот же набор рангов, но в одной ячейке.

Как видно из картинки выше, значения из диапазона

В60:В67

и в ячейке

D60

совпадают. Такой массив можно получить с помощью формулы

=РАНГ(A60:A67;A60:A67)

или с помощью формулы

=СЧЁТЕСЛИ(A60:A67;»>»&A60:A67)+1

Ранги по возрастанию можно получить с помощью формулы

=РАНГ(A60:A67;A60:A67;1)

или

=СЧЁТЕСЛИ(A60:A67;»

Такой подход использется в статьях

Отбор уникальных значений с сортировкой в MS EXCEL

и

Динамическая сортировка таблицы в MS EXCEL

.

Функция

RANK.EQ — служба поддержки Майкрософт

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel для Mac 2011 Excel Starter 2010 Дополнительно…Меньше

В этой статье описаны синтаксис формулы и использование функции РАНГ в Microsoft Excel.

Описание

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

Если бы вы сортировали список, ранг числа был бы его позицией.

Синтаксис

РАНГ.EQ(номер,ссылка,[порядок])

Синтаксис функции RANK.EQ имеет следующие аргументы:

• Номер      Обязательно. Число, ранг которого вы хотите найти.

• Ref      Обязательно. Массив или ссылка на список чисел. Нечисловые значения в Ref игнорируются.

• Заказ      Необязательно. Число, указывающее, как ранжировать число.

Примечания

• org/ListItem»>

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

• Если порядок является любым ненулевым значением, Excel ранжирует номер, как если бы ссылка была списком, отсортированным в порядке возрастания.

• RANK.EQ присваивает повторяющимся числам одинаковый ранг. Однако наличие повторяющихся номеров влияет на ранги последующих номеров. Например, в списке целых чисел, отсортированных в порядке возрастания, если число 10 встречается дважды и имеет ранг 5, то 11 будет иметь ранг 7 (ни одно число не будет иметь ранг 6).

• Для некоторых целей можно использовать определение ранга, учитывающее ничьи. В предыдущем примере вам нужен пересмотренный ранг 5,5 для числа 10. Это можно сделать, добавив следующий поправочный коэффициент к значению, возвращаемому функцией RANK.EQ. Этот поправочный коэффициент подходит как для случая, когда ранг вычисляется в порядке убывания (порядок = 0 или опущен) или в порядке возрастания (порядок = ненулевое значение).

  Поправочный коэффициент для одинаковых рангов = [COUNT(ref) + 1 – RANK.EQ(число, ссылка, 0) – RANK.EQ(число, ссылка, 1)]/2.

  В примере из рабочей тетради RANK.EQ(A3,A2:A6,1) равно 3. Поправочный коэффициент равен (5 + 1 – 2 – 3)/2 = 0,5, а пересмотренный ранг, учитывающий равенство, равен 3 + 0,5 = 3,5. Если число встречается в ref только один раз, поправочный коэффициент будет равен 0, так как RANK.EQ не нужно корректировать для равенства.

Пример

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

РАНГ в Excel (формула, примеры)

Функция Excel РАНГ (оглавление)

• РАНГ в Excel
• РАНГ Формула в Excel
• Как использовать функцию РАНГ в Excel?

Функция ранжирования в Excel используется для определения наилучшего положения в последовательности любой выбранной ячейки из заданной иерархии или диапазона, что применимо только к числу. И это потому, что Ранг можно измерить только числами. Если у нас есть число 5 и мы хотим найти ранг (или позицию) любого числа, нам просто нужно выбрать диапазон, а затем выбрать порядок, в котором мы хотим ранг.

Формула РАНГ в Excel:

Ниже приведена формула РАНГ в Excel:

Объяснение функции РАНГ в Excel

Формула РАНГ в Excel включает два обязательных аргумента и один необязательный аргумент.

• Число: Это значение или число, которое мы хотим найти для ранга.
• Ref: Это список чисел в диапазоне или в массиве, с которым вы хотите сравнить свой «Число».
• [Заказ]: Хотите ли вы, чтобы ваш рейтинг в порядке возрастания или убывания. Введите 0 для убывания и введите 1 для возрастания.

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

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

Три различных типа функций РАНГ в Excel

Если вы начнете вводить функцию РАНГ в Excel, он покажет вам 3 типа функций РАНГ.

1. РАНГ.СРЕДНИЙ
2. РАНГ.EQ
3. РАНГ

В Excel 2007 и более ранних версиях была доступна только функция RANK. Однако позже функция RANK была заменена функциями RANK.AVG и RANK.EQ.

Хотя функция RANK все еще работает в последних версиях, она может быть недоступна в будущих версиях.

Как использовать функцию РАНГ в Excel?

РАНГ Функция в Excel очень проста и удобна в использовании. Давайте разберемся с работой функции RANK в Excel на примере формулы RANK.

Вы можете скачать этот шаблон Excel для функции RANK здесь – Шаблон Excel для функции RANK

Пример №1

В общей сложности 12 команд недавно участвовали в турнире Кабадди. У меня есть названия команд, их общее количество очков в первых двух столбцах.

Я должен ранжировать каждую команду, сравнивая ее с другими командами в списке.

Поскольку RANK работает только для совместимости в более ранних версиях, здесь я использую функцию RANK.EQ вместо функции RANK.

Примечание: Оба работают одинаково.

Примените функцию RANK.EQ в ячейке C2.

Таким образом, вывод будет:

Мы можем перетащить формулу, используя Ctrl + D или дважды щелкнув в правом углу ячейки C2. Таким образом, результат будет:

Примечание: Я не указал номер заказа. Поэтому excel по умолчанию ранжирует в порядке убывания.

• =RANK.EQ (B2, $B$2:$B$13) вернул число (ранг) 12. Всего в этом списке у меня 12 команд. Эта команда набрала 12 очков, что является самым низким показателем среди всех 12 рассмотренных нами команд. Поэтому формула ранжировала его как 12, т.е. последний ранг.
• =RANK.EQ (B3, $B$2:$B$13) вернул число (ранг) 1. Эта команда набрала 105 очков, что является самым высоким показателем среди всех 12 команд, которые мы принимали во внимание. Поэтому формула присвоила ему 1, т.е. первый ранг.

Вот как функция RANK или RANK.EQ помогает нам узнать рейтинг каждой команды при сравнении друг с другом в одной группе.

Пример #2

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

Рассмотрим приведенные ниже данные для этого примера. У меня есть имя игрока с битой и его средние данные за карьеру.

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

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

Таким образом, вывод будет:

Мы можем перетащить формулу, используя Ctrl + D или дважды щелкнув в правом углу ячейки С2. Таким образом, результат будет таким:

Если я применю к этим данным формулу RANK, Сачин и Дравид получат ранг 1.

Я хочу сказать, что если формула находит два повторяющихся значения, то она должна показать 1 для первого найденного значения и следующего значения для другого числа.

Есть много способов найти уникальные ранги в таких случаях. В этом примере я использую RANK.EQ с функцией COUNTIF.

Таким образом, вывод будет:

Мы можем перетащить формулу, используя Ctrl + D или дважды щелкнув в правом углу ячейки D2. Таким образом, результат будет таким:

Формула, которую я здесь использовал, выглядит так:

=РАНГ.EQ (B2, $B$2:$B$6) это найдет ранг для этого набора.

СЧЁТЕСЛИ ($B$2:B2, B2) – 1. 9Формула 0192 СЧЁТЕСЛИ поможет здесь. Для первой ячейки я упомянул, что $B$2:B2 означает в этом диапазоне, каково общее количество значения B2, затем вычесть это значение из 1.

Первый РАНГ возвращает 1, а СЧЁТЕСЛИ возвращает 1, но поскольку мы -1, становится нулем; следовательно, 1+0 = 1. Для Сачина РАНГ остается равным 1.

Для Дравида мы получили ранг равным 2. Здесь РАНГ возвращает 1, а СЧЁТЕСЛИ возвращает 2, но, поскольку мы упомянули -1, он становится 1, поэтому 1 + 1 = 2 Ранг Дравида равен 2, а не 1.

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

Что следует помнить

• Функция RANK заменена на RANK.EQ в версии 2010 и более поздних версиях.
• Функция РАНГ в Excel может принимать только числовые значения. Что-либо, кроме числовых значений, мы получим ошибку #ЗНАЧ!
• Если номер, который вы тестируете, отсутствует в списке номеров, мы получим #N/A! Ошибка.
• Функция РАНГ в Excel дает тот же ранг в случае повторяющихся значений.