Как ранжировать данные в Excel

Обновлено: 03.07.2024

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

Формула

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

Функция RANK использует следующие аргументы:

Число (обязательный аргумент) — это значение, для которого нам нужно найти ранг.
Ref (обязательный аргумент). Может быть списком, массивом или ссылкой на числа.
Порядок (необязательный аргумент). Это число, указывающее, как будет выполняться ранжирование (по возрастанию или по убыванию).
- 0 – используется для убывания.
- 1 — используется для возрастания
- Если мы опустим аргумент, он примет значение по умолчанию 0 (в порядке убывания). Он примет любое ненулевое значение как значение 1 (в порядке возрастания).

Прежде чем мы продолжим, нам нужно знать, что функция RANK была заменена функциями RANK.EQ и RANK.AVG. Чтобы обеспечить обратную совместимость, RANK по-прежнему работает в Excel 2016 (последняя версия), но в будущем он может быть недоступен. Если вы введете эту функцию в Excel 2016, она отобразит желтый треугольник с восклицательным знаком.

Как использовать функцию RANK в Excel?

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

Пример 1

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

Для ранжирования в порядке убывания мы будем использовать формулу =RANK(B2,($C$5:$C$10),0), как показано ниже:

Результат, который мы получаем, показан ниже:

Как показано выше, функция RANK присваивает повторяющимся числам одинаковый ранг. Однако наличие повторяющихся номеров влияет на ранги последующих номеров. Например, как показано выше, в списке целых чисел, отсортированных в порядке возрастания, число 100 встречается дважды с рангом 4. Следующее значение (25) будет иметь ранг 6 (ни одно число не будет иметь ранг 5).

Если нам нужны уникальные ранги, мы можем использовать формулу:

=РАНГ(C5,$C$5:C$10,0)+СЧЁТЕСЛИ($C$5:C5,C5)-1

Мы получим следующие результаты:

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

=RANK.EQ(C5,$C$5:C$10,1)+СЧЁТЕСЛИ($C$5:C5,C5)-1

В обеих формулах все дело в функции СЧЁТЕСЛИ. Мы использовали COUNTIF, чтобы узнать, сколько раз встречается ранжированное число. В формуле СЧЁТЕСЛИ диапазон состоит из одной ячейки ($C$5:C5). Поскольку мы заблокировали только первую ссылку ($C$5), последняя относительная ссылка (C5) изменяется в зависимости от строки, в которую копируется формула. Таким образом, для строки 7 диапазон расширяется до $C$5:C10, и значение в C10 сравнивается с каждой из указанных выше ячеек.

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

Для рангов, встречающихся во второй раз, функция СЧЁТЕСЛИ возвращает 2. Вычитая 1, мы повышаем ранг на 1 балл, что предотвращает дублирование. Если произойдет более 3 вхождений одного и того же значения, COUNTIF()-1 добавит 2 к их рейтингу и т. д.

Что нужно помнить о функции RANK

Дополнительные ресурсы

Спасибо, что прочитали руководство CFI по важным функциям Excel! Потратив время на изучение и освоение этих функций, вы значительно ускорите свой финансовый анализ. Чтобы узнать больше, ознакомьтесь с этими дополнительными ресурсами CFI:

Функции Excel для финансов Excel для финансов В этом руководстве по Excel для финансов представлены 10 основных формул и функций, которые необходимо знать, чтобы стать отличным финансовым аналитиком в Excel.
Усовершенствованные формулы Excel, которые необходимо знать Усовершенствованные формулы Excel, которые необходимо знать Эти расширенные формулы Excel крайне важны для понимания и выведут ваши навыки финансового анализа на новый уровень. Загрузите нашу бесплатную электронную книгу Excel!
Сочетания клавиш Excel для ПК и Mac Ярлыки Excel для ПК Mac Сочетания клавиш Excel — список наиболее важных и распространенных сочетаний клавиш MS Excel для пользователей ПК и Mac, специалистов в области финансов и бухгалтерского учета. Сочетания клавиш ускоряют ваши навыки моделирования и экономят время. Изучите редактирование, форматирование, навигацию, ленту, специальную вставку, работу с данными, редактирование формул и ячеек и другие сочетания клавиш.

Бесплатное руководство по Excel

Чтобы овладеть искусством работы с Excel, ознакомьтесь с БЕСПЛАТНЫМ ускоренным курсом CFI по Excel. Основы Excel — формулы для финансов Вы ищете ускоренный курс по Excel? Получите бесплатное обучение Excel для карьеры в области корпоративных финансов и инвестиционно-банковской деятельности от Института корпоративных финансов. , который научит вас, как стать опытным пользователем Excel. Изучите самые важные формулы, функции и сочетания клавиш, чтобы уверенно проводить финансовый анализ.

Запустите бесплатный курс CFI по Excel прямо сейчас Основы Excel - формулы для финансов Вы ищете ускоренный курс Excel? Пройдите бесплатное обучение Excel, чтобы начать карьеру в сфере корпоративных финансов и инвестиционно-банковских услуг, от Института корпоративных финансов.

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

Как использовать функцию RANK

Если вы зададите функции RANK число и список чисел, она сообщит вам ранг этого числа в списке либо в порядке возрастания, либо в порядке убывания.

Например, на снимке экрана ниже показан список из 10 результатов тестов учащихся в ячейках B2:B11.

Чтобы найти рейтинг первого балла учащегося в ячейке B2, введите следующую формулу в ячейку C2:

=РАНГ(B2,$B$2:$B$11)

Затем скопируйте формулу из ячейки C2 в ячейку C11, и оценки будут ранжированы в порядке убывания.

Аргументы функции RANK

У функции RANK есть 3 аргумента:

число: в приведенном выше примере ранжируемое число находится в ячейке B2.
ref: Мы хотим сравнить число со списком чисел в ячейках $B$2:$B$11. Используйте абсолютную ссылку ($B$2:$B11) вместо относительной ссылки (B2:B11), чтобы диапазон ссылок оставался прежним, когда вы копируете формулу в ячейки ниже.

порядок: (необязательный) Этот аргумент сообщает Excel, следует ли ранжировать список в порядке возрастания или убывания.

Используйте ноль или оставьте этот аргумент пустым, чтобы найти позицию в списке в порядке убывания. В приведенном выше примере аргумент порядка был оставлен пустым, чтобы найти ранг в порядке убывания.
=РАНГ(B2,$B$2:$B$11)
Для возрастания введите 1 или любое другое число, кроме нуля.
Если бы вы сравнивали результаты в гольфе, вы могли бы ввести 1, чтобы ранжировать в порядке возрастания.
=РАНГ(B2,$B$2:$B$11 ,1 )

Порядок функций RANK

В функции RANK третий аргумент (порядок) является необязательным. Аргумент порядка сообщает Excel, следует ли ранжировать список в порядке возрастания или убывания.

По убыванию

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

Самое большое число получает 1 ранг.
5-е по величине число получает 5-й ранг.

По возрастанию

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

Самое маленькое число получает 1 ранг.
Пятое наименьшее число получает рейтинг 5.

Гибкая формула

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

Например, введите 1 в ячейке E1 и укажите ссылку на ячейку E1 для аргумента заказа.

ПРИМЕЧАНИЕ. Обязательно используйте абсолютную ссылку ($E$1), если формула будет скопирована в другие строки. Если вы используете относительную ссылку (E1), ссылка будет меняться в каждой строке.

=РАНГ(B2,$B$2:$B$6,$E$1)

Связав ячейку, вы можете быстро увидеть разные результаты, не меняя формулу. Введите ноль в ячейку E1 или удалите число, и ранг изменится на Убывающий порядок.

Использовать флажок

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

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

В примере файла функции RANK есть пример флажка на листе RankOrderCheck. Инструкции по добавлению флажка и использованию его в формуле см. в моем блоге Использование результата флажка в формуле Excel.

Другие примеры гибких формул

Дополнительные примеры гибких формул см.

как использовать раскрывающиеся списки для единиц функции ПРЕОБРАЗОВАТЬ
как выбрать параметры для функций ПРОМЕЖУТОЧНЫЙ ИТОГ и АГРЕГАТ.

РАНГ Функциональные связи

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

В приведенном выше примере последние два балла в списке совпадают — 38. Два студента, Айви и Джо, оба заняли 4-е место.
Следующий по величине балл (36 баллов Эда) занимает 6-е, а не 5-е место, поскольку перед ним 5 учащихся.

Если вам нужно разорвать ничью или присудить денежные призы на основе ничьей, ниже приведены примеры.

Разорвать связи с помощью функции RANK

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

Посмотрите это видео, чтобы увидеть шаги, а письменные инструкции находятся под видео.

Разорвать связи с помощью функции RANK

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

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

Рассчитать десятичную сумму для одинаковых результатов

Я добавил время тестирования в столбец C и формулу TieBreak в столбец E.

=ЕСЛИ(СЧЁТЕСЛИ($B$2:$B$11,B2)>1,
РАНГ(C2,$C$2:$C$11,1)/100,0)

Как работает формула тай-брейка

Формула Tie Break использует функции СЧЁТЕСЛИ и РАНГ, обернутые функцией ЕСЛИ, чтобы увидеть, следует ли добавить десятичную сумму, разрешающую ничью, к исходному рангу.

Во-первых, формула TieBreak проверяет, ЕСЛИ во всем списке встречается более одного экземпляра числа:
ЕСЛИ(СЧЁТЕСЛИ($B$2:$B$11,B2)>1
Если имеется более одного экземпляра, Times ранжируется в возрастающем порядке, потому что чем меньше время, тем лучше:
RANK(C2,$C$2:$C$11 ,1 )
Затем эта сумма делится на 100, чтобы получить десятичную сумму. Позже вы добавите это десятичное число к исходному Рангу.
Примечание. Делитель 100 можно заменить другим числом, если вы работаете с более длинным списком.
/100
Наконец, чтобы завершить функцию ЕСЛИ, если есть только один экземпляр ранга, результат для TieBreak равен нулю.
,0)

Рассчитать окончательный рейтинг

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

В этом примере два студента заняли четвертое место. Джо потребовалось 27 минут, чтобы пройти тест, и его время заняло 5-е место. Айви выполнила тест за 29 минут, и ее время заняло 9-е место.

Формула тай-брейка добавляет десятичную дробь 0,09 к счету Айви и 0,05 к счету Джо. В финальном рейтинге Джо с 4,05 балла выше, чем Айви с 4,09 балла.

Разделение выигрыша при равном рейтинге

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

Разделение выигрыша при равном рейтинге

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

Ниже приведена примерная таблица призов, показывающая сумму, присуждаемую за каждый ранг. В этом примере, если 3 игрока занимают 1-й ранг, они разделят общую сумму (10+9+8=27) на 1-й, 2-й и 3-й ранги.

Каждый из 3 игроков на первом месте получает 9 (27/3 = 9), а игрок со следующим наивысшим результатом будет занимать 4-е место и зарабатывать 7.

Рассчитать сумму разделения

Чтобы разделить сумму приза между равными игроками, в формуле приза используется функция СРЗНАЧ, а функция СМЕЩ находит диапазон ячеек для усреднения. Эта формула вводится в ячейку D2 и копируется в ячейку D11.

=СРЗНАЧ(СМЕЩ($K$1,C2,0, СЧЁТЕСЛИ($C$2:$C$11,C2)))

Как работает формула приза

В формуле приза используется функция СРЗНАЧ, а функция СМЕЩ находит диапазон ячеек для усреднения.

Функция СРЗНАЧ вычисляет сумму для каждого игрока на основе определенного диапазона ячеек:
СРЗНАЧ(
Функция СМЕЩ возвращает диапазон с суммами, используемыми для среднего значения:
СМЕЩ(
В формуле OFFSET первым аргументом является ячейка ссылки. В данном примере это ячейка K1 — заголовок столбца «Суммы призов».
2 000 долларов,
В формуле OFFSET второй аргумент — это количество строк вниз от эталонной ячейки, с которого начинается усреднение ячеек. Ранги перечислены в порядке возрастания, поэтому для ранга 1 ячейки для усреднения будут начинаться на 1 строку ниже опорной ячейки с $K$1. Ранг первого игрока указан в ячейке C2, поэтому используйте его в формуле
C2,
В формуле OFFSET третий аргумент – это количество столбцов справа от опорной ячейки, с которых начинается усреднение ячеек. Вы хотите найти суммы в том же столбце, поэтому число равно нулю
0,
В формуле OFFSET четвертый аргумент – это количество строк, которые необходимо включить в диапазон. Это должно быть количество игроков, имеющих одинаковое количество очков в этом ранге. Функция СЧЁТЕСЛИ будет подсчитывать экземпляры ранга в столбце C, которые равны рангу в C2.
COUNTIF($C$2:$C$11,C2)

Рейтинг ЕСЛИ Формула

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

На снимке экрана ниже представлены записи о продажах за две недели.

Для 2 и 4 января продажи в первую неделю – самые высокие, поэтому им следует присвоить 1 рейтинг.
На второй неделе 10 января имеет самые высокие продажи, поэтому на этой неделе он должен иметь рейтинг 1.

Нет функции RANKIF

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

Введите эту формулу в ячейку D2 и скопируйте ее в последнюю строку с данными:

=COUNTIFS([Неделя], [@Неделя], [Продажи], ">"&[@Продажи])+1

Как это работает

Первый критерий в формуле проверяет наличие других продаж с таким же номером недели:

=СЧЁТЕСЛИМН([Неделя], [@Неделя]

Второй критерий позволяет найти товары с большей суммой в столбце "Продажи".

[Продажи],">"&[@Продажи])

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

Например, на первой неделе посмотрите на продажи за 3 января – 237.

Есть 2 даты с большими продажами на первой неделе – 2 и 4 января.
Добавьте 1 к этому числу, и рейтинг 3 января будет равен 3.

Получить образец файла

Получите заархивированный пример файла Excel RANK Function. Файл имеет формат xlsx и не содержит макросов.

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

<р>1.Если третий аргумент опущен (или равен 0), Excel ставит наибольшее число первым, второе по величине число — вторым и т. д.

Примечание: когда мы перетаскиваем эту формулу вниз, абсолютная ссылка ($A$1:$A$9) остается неизменной, а относительная ссылка (A1) меняется на A2, A3, A4 и т. д.

<р>2. Если третий аргумент равен 1, Excel ранжирует наименьшее число первым, второе наименьшее число — вторым и т. д.

Примечание: функция RANK — это старая функция. Microsoft Excel рекомендует использовать новую функцию RANK.EQ в Excel 2010 или более поздней версии. Между этими двумя функциями нет никакой разницы.

<р>3. Функция РАНГ.СРЕДНИЙ в Excel 2010 или более поздних версиях возвращает средний ранг, если несколько чисел имеют одинаковый ранг.

<р>4. Чтобы создать чистую функцию RANK, используйте именованный диапазон вместо абсолютной ссылки на диапазон.

Объяснение: именованный диапазон данных относится к диапазону A1:A9.

Если вы используете Excel 365 или Excel 2021, создайте следующую формулу RANK. Эта формула удивит и впечатлит ваших коллег.

<р>5. Мы хотим показать первые 3 результата. Введите значение 3 в ячейку D2.

<р>6. Чтобы найти третий по величине результат, используйте функцию НАИБОЛЬШИЙ ниже. Именованный диапазон очков относится к диапазону B2:B8.

<р>7. Приведенная ниже функция ФИЛЬТР показывает всех учащихся с оценкой выше или равной третьей по величине оценке.

Примечание: функция ФИЛЬТР, введенная в ячейку F2, заполняет несколько ячеек. Вау!

<р>8. Наконец, добавьте функцию SORT для сортировки по второму столбцу (второй аргумент) в порядке убывания (третий аргумент).

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

<р>9. Эта формула RANK работает для каждого N. Например, введите значение 5 в ячейку D2, чтобы отобразить первые 5 результатов.

Примечание: эта формула динамического массива, введенная в ячейку F2, заполняет несколько ячеек. Такое поведение в Excel 365/2021 называется сбросом. Совет: скачайте файл Excel (справа на этой странице) и попробуйте.

< /p>

Как ранжировать данные по встречаемости в Excel?

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

Ранжировать данные по встречаемости с помощью Kutools for Excel’s Advanced Sort

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

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

<р>1. Скопируйте и вставьте список данных в другое место и щелкните Данные > Удалить дубликаты.Смотрите скриншот:

<р>2. Затем нажмите OK > OK, чтобы закрыть диалоговые окна. Смотрите скриншот:

< img class="lazyload" data-src="https://cdn.extendoffice.com/images/stories/doc-excel/rank-by-occrrences/doc-rank-by-occurrences-4.jpg" alt=" ранжировать документ по количеству вхождений 4" />

Теперь уникальные ссылки извлечены. Смотрите скриншот:

< img class="lazyload" data-src="https://cdn.extendoffice.com/images/stories/doc-excel/rank-by-occrrences/doc-rank-by-occurrences-6.jpg" alt=" ранжировать документ по количеству вхождений 6" />

<р>3. В следующем столбце извлеченных данных, например, E2, введите эту формулу = СЧЁТЕСЛИ ($ A $ 2: $ A $ 12, D2), чтобы подсчитать количество раз, когда каждые данные появляются в исходном списке, нажмите клавишу «Ввод» и перетащите маркер заполнения. вниз, чтобы применить эту формулу к ячейкам.

В формуле A2:A12 — это исходный список данных, который вы используете, а D2 — ссылка, которую вы считаете.

<р>4. Затем в следующем столбце подсчитанных данных нажмите F2, а затем введите эту формулу = RANK (E2, $ E $ 2: $ E $ 6), нажмите клавишу Enter и перетащите маркер заполнения вниз, чтобы вычислить рейтинг. Смотрите скриншот:

В формуле E2 – это ячейка, в которой вы хотите рассчитать рейтинг, а E2:E6 – диапазон, который вы используете.

Ранжировать данные по вхождению с помощью Kutools for Excel’s Advanced Sort

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

После установки Kutools for Excel сделайте следующее: (Бесплатно загрузите Kutools for Excel сейчас!)

<р>1. Выберите данные, которые вы используете, и нажмите Kutools Plus > Сортировка > Расширенная сортировка. Смотрите скриншот:

<р>2. Затем в диалоговом окне «Расширенная сортировка» выберите столбец, который вы хотите ранжировать и отсортировать, выберите «Частота» в раскрывающемся списке «Сортировать по» и укажите порядок сортировки. Смотрите скриншот:

<р>3. Нажмите «ОК». Данные отсортированы по частоте от наименьшей к наибольшей.

Читайте также: