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

Функция РАНГ — Служба поддержки Майкрософт

Excel

Формулы и функции

Другие функции

Другие функции

Функция РАНГ

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for 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, поэтому мы рекомендуем использовать новые функции.

Дополнительные сведения о новых функциях см. в разделах Функция РАНГ.СР и Функция РАНГ.РВ.

Синтаксис

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

Аргументы функции РАНГ описаны ниже.

• 
  Число     Обязательный. Число, для которого определяется ранг.

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

• org/ListItem»>

  
  Порядок     Необязательный. Число, определяющее способ упорядочения.

  Если значение аргумента «порядок» равно 0 или опущено, ранг числа определяется в Microsoft Excel так, как если бы ссылка была списком, отсортированным в порядке убывания.

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

Замечания

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

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

  Поправочный коэффициент для связанных рангов = [СЧЕТ(ссылка) + 1 – РАНГ(число, ссылка, 0) – РАНГ(число, ссылка, 1)]/2.

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

Пример

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

Как в таблицах Excel ранжировать значения — Трюки и приемы в Microsoft Excel

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

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

Excel 2010 содержит новую функцию, названную РАНГ.CP, которая исправляет эту проблему. На рис. 98.1 показана таблица, где используется два метода для ранжирования значений в столбце. Первый метод (столбец С) применяет функцию РАНГ, а в столбце D задается новая функция РАНГ.СР. Диапазон В2:В16 назван Продажи.

Рис. 98.1. Ранжирование данных с помощью функции РАНГ и формул массивов

Обратите внимание на то, что два человека стоят на третьем месте. Функция РАНГ присваивает каждому из них значение 3, а функция РАНГ.CP — 3,5. Кроме того, три человека стоят на десятом месте. Функция РАНГ дает каждому из них ранг 10, а функция РАНГ.CP, с другой стороны, дает им троим ранг 11, что является средним для 10,11 и 12.

Формулы, которые используют функцию РАНГ.CP, вернут ошибку в предыдущих версиях Excel. Если вы собираетесь делиться книгой с теми, кто использует версию Excel ниже 2010, то можете задействовать формулу массива вместо функции РАНГ.СР.

Посмотрите на рис. 98.1 и введите следующую формулу массива в строку 4. Затем скопируйте ее в нижестоящие ячейки. Результат будет точно таким же, как и при использовании функции РАНГ.СР:
=СУММ(1*(B4.

Формула массива представляет собой особый вид формулы, которая работает с внутренними массивами данных. При вводе формулы массива вы должны нажать Ctrl+Shift+Enter (а не просто Enter), чтобы указать, что это формула массива. Excel добавит фигурные скобки вокруг формулы, напоминая вам, что это формула массива. Если вы не нажмете Ctrl+Shift+Enter, формула не будет работать.

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

Функция

РАНГ — Служба поддержки Майкрософт

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.

Синтаксис

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

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

• org/ListItem»>

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

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

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

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

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

Примечания

• org/ListItem»>

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

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

  Поправочный коэффициент для одинаковых рангов = [COUNT(ref) + 1 – RANK(число, ref, 0) – RANK(число, ref, 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 и другие способы расчета рейтинга

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

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

• РАНГ в Excel
• РАНГ.EQ в Excel
• РАНГ.СРЕДНИЙ в Excel
• Базовая формула ранжирования Excel (от высшего к низшему)
• Как найти ранг в Excel — примеры формул
  • Как ранжировать числа от самого низкого до самого высокого
  • Как однозначно ранжировать данные в Excel
  • Как ранжировать в Excel на основе нескольких критериев
  • Как рассчитать процентильный ранг в Excel
  • Ранговые значения в несмежных ячейках
  • Как ранжировать в Excel по группе
  • Ранжируйте положительные и отрицательные числа отдельно
  • Как ранжировать числа в Excel, игнорируя нули
  • Вычислить ранг в Excel по абсолютному значению
  • Получить N наибольших или наименьших значений

Функция Excel РАНГ

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

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

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

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

Где:

Число (обязательно) — значение, ранг которого вы хотите найти.

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

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

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

Функция Excel RANK.EQ

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

RANK.EQ(число,ссылка,[порядок])

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

Функция Excel RANK.AVG

RANK.AVG — это еще одна функция для определения ранга в Excel, доступная только в Excel 2010, Excel 2013, Excel 2016 и более поздних версиях.

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

RANK.AVG(число,ссылка,[порядок])

Разница в том, что если несколько чисел имеют одинаковый ранг, возвращается средний ранг (AVG означает «средний»).

4 вещи, которые вы должны знать о ранге в Excel

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

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

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

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

Во всех версиях Excel 2003–2016:

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

В Excel 2010–2016:

=РАНГ.EQ($B2,$B$2:$B$7)

=РАНГ. СРЕДН($B2,$B$2:$B$7 )

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

• Формулы RANK и RANK.EQ присваивают обеим повторяющимся оценкам ранг 2. Следующая наивысшая оценка (Даниэла) занимает 4-е место. Ранг 3 никому не дается.
• Формула РАНГ.СРЕДНИЙ присваивает каждому дубликату разный ранг (в этом примере 2 и 3) и возвращает среднее значение этих рангов (2,5). Опять же, 3 ранг никому не присваивается.

Как использовать RANK в Excel – примеры формул

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

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

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

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

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

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

=РАНГ.EQ(B2,$B$2:$B$7,1)

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

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

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

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

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

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

=РАНГ.EQ(B2,$B$2:$B$7)+СЧЁТЕСЛИ($B $2:B2,B2)-1

Уникальный рейтинг от низшего к высшему

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

=РАНГ.EQ(B2,$B$2:$B$7,1) + СЧЁТЕСЛИ ($B$2:B2,B2)-1

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

В обеих формулах функция СЧЕТЕСЛИ с умным использованием относительных и абсолютных ссылок на ячейки делает свое дело. Короче говоря, вы используете СЧЁТЕСЛИ, чтобы узнать, сколько вхождений ранжируемого числа есть в вышеуказанных ячейках, включая ячейку числа. В самой верхней строке, где вы вводите формулу, диапазон состоит из одной ячейки ($B$2:B2). Но поскольку вы блокируете только первую ссылку ($B$2), последняя относительная ссылка (B2) изменяется в зависимости от строки, в которую копируется формула. Таким образом, для строки 7 диапазон расширяется до $B$2:B7, а значение в ячейке B7 сравнивается с каждой из указанных выше ячеек.

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

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

Альтернативное решение для разрыва связей Excel RANK

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

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

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

=СЧЁТЕСЛИ($B$2:$B$7,">"&$B2)+СЧЁТЕСЛИ($B$2:B2) ,В2)

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

Рейтинг в Excel на основе нескольких критериев

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

В нашем образце данных давайте добавим общие баллы в столбец C и рассчитаем ранг следующим образом:

• Сначала ранжируем с Math Score (основные критерии)
• Если ничья, разбить ее с помощью Total Score (вторичные критерии)

Чтобы это сделать, мы будем использовать обычную формулу РАНГ/РАНГ. EQ, чтобы найти ранг, и функцию СЧЁТЕСЛИМН, чтобы разрешить равенство:

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

По сравнению с приведенным выше примером эта формула ранжирования более объективна: Тимоти занимает 2-е место ^-е , потому что его общий балл выше, чем у Юлии:

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

Часть формулы RANK очевидна, а функция СЧЁТЕСЛИМН делает следующее:

• Первая пара диапазон_критериев / критериев ($B$2:$B$7,$B2) подсчитывает вхождения ранжируемого значения. Обратите внимание, мы исправляем диапазон с абсолютными ссылками, но не блокируйте строку критериев ($B2), чтобы формула проверяла значение в каждой строке отдельно.
• Вторая пара ($C$2:$C$7,»>»&$C2) определяет, на сколько общих баллов больше, чем общий балл ранжируемого значения.

Поскольку СЧЁТЕСЛИМН работает с логикой И, т. е. подсчитывает только те ячейки, которые соответствуют всем заданным условиям, для Тимати возвращается 0, поскольку ни один другой учащийся с таким же баллом по математике не имеет более высокого общего балла. Следовательно, ранг Тимоти, возвращенный RANK.EQ, не изменился. Для Джулии функция СЧЁТЕСЛИМН возвращает 1, потому что у одного учащегося с таким же баллом по математике сумма выше, поэтому её ранг увеличивается на 1. Если ещё один учащийся имеет такой же балл по математике и общий балл ниже, чем у Тимати и Юлии , его/ее ранг будет увеличен на 2 и так далее.

Альтернативные решения для ранжирования чисел с несколькими критериями

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

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

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

Результат этих формул точно такой же, как показано выше.

Как рассчитать ранг процентиля в Excel

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

Чтобы получить процентильный ранг в Excel, используйте функцию RANK или RANK.EQ с ненулевым аргументом порядка для ранжировать числа от меньшего к большему, а затем разделить ранг на количество чисел. Итак, универсальный Формула Excel Percentile Rank выглядит следующим образом:

RANK.EQ( topmost_cell , range ,1)/COUNT( range )

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

=RANK.EQ(B2,$B$2:$B$7,1)/COUNT($B$2:$B$7)

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

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

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

=RANK(B2,($B$2,$B$4,$B$6))

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

Обратите внимание, что повторяющемуся числу также присваивается ранг, хотя ячейка B5 не включена в формулу:

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

=ЕСЛИОШИБКА(РАНГ(B2,диапазон), "")

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

При работе с записями, организованными в какую-либо структуру данных, данные могут принадлежать к различным группам, и вам может потребоваться ранжировать числа в каждой группе отдельно. Функция Excel RANK не может решить эту проблему, поэтому мы собираемся использовать более сложную формулу СУММПРОИЗВ:

Ранжирование по группам в порядке по убыванию :

=СУММПРОИЗВ((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 — это числа, которые необходимо ранжировать.

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

Как работает эта формула
По сути, формула оценивает 2 условия:

• Сначала вы проверяете группу (A2=$A$2:$A$7). Эта часть возвращает массив значений TRUE и FALSE в зависимости от того, принадлежит ли элемент диапазона к той же группе, что и A2.
• Во-вторых, вы проверяете счет. Чтобы ранжировать значения от наибольшего к наименьшему ( в порядке убывания ), используйте условие (C2<$C$2:$C$11), которое возвращает TRUE для ячеек, больших или равных C2, и FALSE в противном случае.

Поскольку в терминах Microsoft Excel ИСТИНА = 1 и ЛОЖЬ = 0, умножение двух массивов дает массив из 1 и 0, где 1 возвращается только для строк, в которых выполняются оба условия.

Затем СУММПРОИЗВ складывает элементы массива 1 и 0, таким образом, возвращая 0 для наибольшего числа в каждой группе. И вы добавляете 1 к результату, чтобы начать ранжирование с 1.

Формула ранжирования чисел внутри групп от наименьшего к наибольшему ( по возрастанию заказ ) работает по той же логике. Разница в том, что СУММПРОИЗВ возвращает 0 для наименьшего числа в определенной группе, поскольку ни одно число в этой группе не удовлетворяет условию 2 ^и (C2>$C$2:$C$7). Опять же, вы заменяете нулевой ранг 1-м рангом, добавляя 1 к результату формулы.

Вместо СУММПРОИЗВ можно использовать функцию СУММ для сложения элементов массива. Но для этого потребуется использовать формулу массива, заполняемую с помощью Ctrl + Shift + Enter. Например:

=СУММ((A2=$A$2:$A$7)*(C2<$C$2:$C$7))+1

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

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

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

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

=ЕСЛИ($A2>0,СЧЁТЕСЛИ($A$2:$A$10,">"&A2)+1,"")

Ранг положительных чисел по возрастанию:

=ЕСЛИ($A2>0 ,СЧЁТЕСЛИ($A$2:$A$10,">0")-СЧЁТЕСЛИ($A$2:$A$10,">"&$A2),"")

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

= ЕСЛИ($A2<0,СЧЁТЕСЛИ($A$2:$A$10,"<0")-СЧЁТЕСЛИ($A$2:$A$10,"<"&$A2),"")

Ранг отрицательных чисел по возрастанию:

=ЕСЛИ($A2<0,СЧЁТЕСЛИ($A$2:$A$10,"<"&$A2)+1,"")

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

Как работают эти формулы функция ЕСЛИ проверяет, больше ли число нуля.

Формула ранжирования положительных чисел в порядке возрастания работает несколько иначе:

Если число больше 0, первое СЧЁТЕСЛИ получает общее количество положительных чисел в наборе данных, а второе СЧЁТЕСЛИ определяет, как многие значения выше этого числа. Затем вы вычитаете последнее из первого и получаете желаемый ранг. В этом примере есть 5 положительных значений, 1 из которых больше, чем A2. Итак, вы вычитаете 1 из 5, таким образом получая ранг 4 для A2.

Формулы ранжирования отрицательных чисел основаны на аналогичной логике.

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

Например, чтобы ранжировать положительные числа и нули от большего к меньшему, используйте следующую формулу: =ЕСЛИ($A2>=0,СЧЁТЕСЛИ($A$2:$A$10,">"&A2)+1,"" )

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

Как вы уже знаете, формула РАНГ Excel обрабатывает все числа: положительные, отрицательные и нули. Но в некоторых случаях мы просто хотим ранжировать ячейки с данными, игнорируя 0 значений. В Интернете вы можете найти несколько возможных решений этой задачи, но формула Excel РАНГ ЕСЛИ, на мой взгляд, является наиболее универсальной: ",ЕСЛИ($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), RANK($B2,$B$2:$B$10,1)))

Где B2:B10 — диапазон ранжируемых чисел.

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

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

Вот как формула Excel РАНГ ЕСЛИ ранжирует числа от наибольшего к наименьшему, игнорируя нули:

• Первый ЕСЛИ проверяет, равно ли число 0, и если да, возвращает пустую строку:

  ЕСЛИ($B2=0,"", …)

• Если число не равно нулю, второй ЕСЛИ проверяет, больше ли оно 0, и если больше, то обычная функция RANK/RANK.EQ вычисляет его ранг:

  ЕСЛИ($B2>0,РАНГ($B2,$B$2:$B$10),…)

• Если число меньше 0, вы корректируете ранжирование по нулевому счету. В этом примере 4 положительных числа и 2 нуля. Таким образом, для наибольшего отрицательного числа в ячейке B10 формула Excel RANK вернет 7. Но мы пропускаем нули, и поэтому нам нужно скорректировать ранг на 2 балла. Для этого вычтем из ранга количество нулей:

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

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

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

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

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

Ранг ABS по убыванию:

=СУММПРОИЗВ((ABS(A2)<=ABS(A$2:A$7)) * (A$2:A$7<>"")) - СУММПРОИЗВ((ABS(A2)= ABS($A$2:$A$7)) * (A$2:A$7<>""))+1

Ранг ABS по возрастанию:

=СУММПРОИЗВ((ABS(A2)>=ABS(A$2 :A$7)) * (A$2:A$7<>"")) - СУММПРОИЗВ((ABS(A2)=ABS($A$2:$A$7)) * (A$2:A$7<>"") )+1

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

Как получить N наибольших или наименьших значений

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