Как ранжировать по количеству баллов в Excel

Обновлено: 22.11.2024

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

Формула

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

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

  1. Число (обязательный аргумент) — это значение, для которого нам нужно найти ранг.
  2. Ref (обязательный аргумент). Может быть списком, массивом или ссылкой на числа.
  3. Порядок (необязательный аргумент). Это число, указывающее, как будет выполняться ранжирование (по возрастанию или по убыванию).
    • 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, чтобы начать карьеру в сфере корпоративных финансов и инвестиционно-банковских услуг, от Института корпоративных финансов.

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 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

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

Описание

Возвращает ранг числа в списке чисел. Ранг числа — это его размер относительно других значений в списке. (Если бы вы сортировали список, ранг числа был бы его позицией.)

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

Дополнительную информацию о новых функциях см. в разделах Функция RANK.AVG и Функция RANK.EQ.

Синтаксис

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

Требуется номер. Число, ранг которого вы хотите найти.

Требуется ссылка. Массив или ссылка на список чисел. Нечисловые значения в ref игнорируются.

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

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

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

Примечания

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

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

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

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

Пример

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

Функция РАНГ в 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 (справа на этой странице) и попробуйте.

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

Вас может заинтересовать:

Объединение нескольких листов или книг в один рабочий лист или книгу может стать сложной задачей в вашей повседневной работе. Но, если у вас есть Kutools for Excel, его мощная утилита — Combine может помочь вам быстро объединить несколько рабочих листов, рабочих книг в один рабочий лист или книгу.

Kutools for Excel включает более 300 удобных инструментов Excel. Бесплатно попробовать без ограничений в течение 60 дней. Подробнее Скачать бесплатную пробную версию сейчас

  • Повторное использование всего: добавляйте наиболее часто используемые или сложные формулы, диаграммы и другие объекты в избранное и быстро используйте их повторно в будущем.
  • Более 20 текстовых функций: извлечение числа из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
  • Инструменты объединения: несколько книг и листов в одну; Объединение нескольких ячеек/строк/столбцов без потери данных; Объединить повторяющиеся строки и суммировать.
  • Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Одна рабочая книга для нескольких файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
  • Вставить, пропуская скрытые/отфильтрованные строки; Подсчет и сумма по цвету фона; Массовая рассылка персонализированных электронных писем нескольким получателям.
  • Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировка по неделям, дням, частоте и т. д.; Фильтр по полужирному шрифту, формулам, комментариям.
  • Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Уникальные ранжированные номера диапазонов в порядке убывания

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

В качестве примера возьмем данные на снимке экрана ниже. Вы можете видеть, что в диапазоне A2:A11 есть несколько повторяющихся чисел.

<р>1. Выберите B2, скопируйте и вставьте формулу =РАНГ(A2,$A$2:$A$11,0)+СЧЁТЕСЛИ($A$2:A2,A2)-1 в строку формул, затем нажмите клавишу Enter. Смотрите скриншот:

<р>2. Затем номер рейтинга отображается в ячейке B2. Выберите ячейку B2 и поместите курсор в ее нижний правый угол, когда появится маленький черный крестик, перетащите его вниз к ячейке B11. Тогда уникальный рейтинг успешен. Смотрите скриншот:

Уникальное ранжирование номеров диапазонов в порядке возрастания

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

<р>1. Выберите ячейку B2, скопируйте и вставьте формулу =РАНГ(A2,$A$2:$A$11,1)+СЧЁТЕСЛИ($A$2:A2,A2)-1 в строку формул, затем нажмите клавишу Enter. Затем первое ранговое число отображается в ячейке B2.

<р>2. Выберите ячейку B2, перетащите маркер заполнения вниз к ячейке B11, после чего уникальное ранжирование будет завершено.

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