Функция РАНГ() в 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;"<"&A7)+1

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

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



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

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

=СЧЁТЕСЛИМН($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;"<"&A60:A67)+1 .

Такой подход использется в статьях Отбор уникальных значений с сортировкой в MS EXCEL и Динамическая сортировка таблицы в MS EXCEL .


Комментарии

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

Аноним, 9 декабря 2015 г.
Как расТянуть функцию ранг по вертикали
Михаил, 19 июля 2016 г.
Вот вариант: р а н г Если серьезно, то я не понял ваш вопрос
Михаил, 19 июля 2016 г.
Посмотрите эту статью http://excel2.ru/articles/marker-zapolneniya-v-ms-excel
Аноним, 20 июля 2016 г.
а как быть, если нужно найти ранг по двум и более условиям? Например место в футбольной группе? То есть две команды в группе набрали одинаковое количество очков, но одна команда, забила больше другой и, соответственно, ее ранг выше
Михаил, 27 июля 2016 г.
Набранное количество очков - всегда целое число. Той, которая забила больше, можно прибавить 0,5, тогда ее ранг будет больше.
Аноним, 11 февраля 2017 г.
Добрый день, Михаил! Подскажите, пожалуйста, как подсчитать среднее значение рангов в подвыборке (часть массива с определенными критериями). Я использую вот такую формулу для определения рангов в подвыборке (хочу ранги только для первых 13 элементов): =РАНГ.СР(ЕСЛИ(C2:C41=$C$2;ЕСЛИ(ЕЧИСЛО(A2:A41);A2:A41));ЕСЛИ(C2:C41=$C$2;ЕСЛИ(ЕЧИСЛО(A2:A41);A2:A41));1) При отображении значений через F9 получаем вот такую структуру, то есть структуры двух диапазонов совпадают =РАНГ.СР({141:136:167:150:ЛОЖЬ:159:140:86:160:97:90:84:98:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ};141:136:167:150:ЛОЖЬ:159:140:86:160:97:90:84:98:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ};1) А на выходе получаем ошибки в расчетов рангов: {#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!} Когда я считаю для всего диапазона ранги через массив по формуле: =РАНГ.СР(A2:A14;A2:A14;1) получаем структуру (пропуск в данных почему-то заменяется на 0? =РАНГ.СР({141:136:167:150:0:159:140:86:160:97:90:84:98};{141:136:167:150:0:159:140:86:160:97:90:84:98};1) На выходе получаем, там где был пропуск, который заменился на 0, теперь стал #Н/Д: {8:6:12:9:#Н/Д:10:7:2:11:4:3:1:5} Вопрос: 1. как мне правильно написать формулу массива для расчета массива рангов в подвыборке с дальнейшем применением к ней: СРЗНАЧ и других Описательных статистик? 2. Почему во втором варианте пропуск заменяется на 0, а потом этот 0 становится #Н/Д? Как-то не логично, по идеи 0 - это тоже число и ему должно присваиваться ранг. Заранее спасибо. Скрин ниже:
Михаил, 12 февраля 2017 г.
Добрый день, Нечисловые значения функцией РАНГ игнорируются, поэтому формула =РАНГ.СР(A2;$A$2:$A$7;1) возвращает ошибку для пустых ячеек (не смотря на то, что массив {141:136:167:150:0:159} содержит 0). Похоже, что функция РАНГ не использует массив {141:136:167:150:0:159}, а напрямую обращается к ячейкам. Я бы сначала избавился от строк с пустыми ячейками в столбце А, см. http://excel2.ru/articles/udalyaem-v-ms-excel-propuski-v-spiske-formuly, затем приступил бы к вычислению рангов.
Аноним, 12 февраля 2017 г.
Михаил, большое спасибо за совет. Сейчас буду пробовать.
Илона, 30 сентября 2020 г.
Добрый день! Подскажите , для ячейки, которая в списке по условию всего одна эта формула дает значение 2 =СЧЁТЕСЛИМН($A$3:$A$22;A3;$B$3:$B$22;">"&B3)+1
Евгений, 22 октября 2020 г.
У меня 1. Я взял на листе РАНГ с условием Ввел вместо Аджика, Аджика 2. формула вернула 1. Тоже сделал с балыком. Условие в столбце А(группа).
(только для авторизованных пользователей)

© Copyright 2013 - 2021 Excel2.ru. All Rights Reserved