Как найти место в 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

.

Функция РАНГ() при применении возвращает в виде результата номер позиции элемента в конкретно определённом списке. Сам результат представляет собой число, которое показывает, какое бы место занимал элемент в этой строке, если бы указанный диапазон был отсортирован по возрастанию или по убыванию.

Примеры использования функции РАНГ в Excel

Синтаксис функции:

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

Описание аргументов:

• — число: указание на ячейку, позицию которой необходимо вычислить;
• — ссылка: указание на диапазон ячеек, с которыми будет производиться сравнение;
• — порядок: значение, которое указывает на тип сортировки: 0 – сортировка по убыванию, 1 – по возрастанию.

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

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



Как ранжировать список по возрастанию в Excel

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

Используем для ячейки C2 формулу =РАНГ(B2;$B$2:$B$7;0), для ячейки D2 – формулу =РАНГ.РВ(B2;$B$2:$B$7;0), а для ячейки E2 – формулу =РАНГ.СР(B2;$B$2:$B$7;0). Протянем все формулы на ячейки ниже.

Таким образом, видно, что ранжирование по функциям РАНГ() и РАНГ.РВ() не отличается: есть два ученика, которые заняли второе место, третьего места нет, а также есть два ученика, которые заняли четвёртое место, пятого места также не существует. Ранжирование было произведено по высшим из возможных вариантов.

В то же время функция РАНГ.СР() присвоила совпавшим ученикам среднее значение из мест, которые они могли бы занимать, если бы сумма баллов, например, была с разницей в один балл. Для второго и третьего места среднее значение – 2,5; для четвёртого и пятого – 4,5.

Ранжирование товаров по количеству в прайсе

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

Добавим колонку ранжирования и в ячейку C2 впишем следующую формулу: =РАНГ.РВ($B2;$B$2:$B$10;0). Протянем эту формулу вниз и получим следующий результат распределения мест:

Теперь нам потребуются три дополнительные колонки для создания удобной для восприятия таблицы. В первой колонке у нас будет записаны порядковые номера, во второй – отображены наименования товара, в третьей – их количество. Для того, чтобы таблица работала корректно и обновляла значения при их изменении в колонках А и B, применим к ячейке F2 формулу:

а к ячейке G2 – формулу:

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

Расчет рейтинга продавцов по количеству продаж в Excel

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

В качестве шапки для сортировки мы будем использовать клетку H1. Выделим её и перейдём в меню «ДАННЫЕ — Работа с данными — Проверка данных».

В окне «Проверка вводимых значений» в качестве типа данных выберем «Список» и укажем диапазон ячеек, в которых записаны месяцы. Так будет реализовано выпадающее меню со списком месяцев для удобства ранжирования. Диапазон выглядит следующим образом: =$B$1:$G$1.

Для ячейки H2 требуется определить формулу. Добавим в функцию РАНГ.СР() её обязательные элементы. Для этого включим в качестве аргумента число функцию ИНДЕКС(), где в аргументе массив определим общий диапазон интересующих нас данных в виде значения $B$2:$G$5. В качестве аргумента номер_строки функции укажем 0, а в качестве аргумента номер_столбца – функцию ПОИСКПОЗ().

Для того, чтобы функция ПОИСКПОЗ() работала корректно, укажем на диапазон, который будет интересовать нас при выборе месяца. Аргументы этой функции будут выглядеть следующим образом: $H$1;$B$1:$G$1;0, где $H$1 – ячейка с выбором месяца, значение которого будет искаться в диапазоне $B$1:$G$1 с полным соответствием.

В качестве аргумента ссылка функции РАНГ.СР() будет использована функция СМЕЩ(), позволяющая возвращать ссылку на ячейку, находящуюся в некотором известном отдалении от указываемой ячейки. Проще говоря, мы указываем ячейку $B$2 как основу, а затем, не смещая её по строкам, указываем с помощью уже известной функции ПОИСКПОЗ() с аргументами $H$1;$B$1:$G$1;0. Добавим ко второму аргументу функции СМЕЩ() значение «-1», т.к. для первой строки нам понадобится значение 0, для второй – 1 и т.д.

Для записи необязательного, но в нашем случае важного аргумента высота воспользуемся простой функцией СЧЁТЗ(), которая поможет подсчитать количество ячеек, заполненных в диапазоне $B$2:$B$5. В качестве аргумента ширина укажем значение «1».

Таким образом, итоговая формула для ячейки H2 будет выглядеть следующим образом:

Как видно, в диапазоне H2:H5 отобразилось ранжирование работников по количеству продаж оборудования за январь. Теперь мы можем, кликнув на ячейку H1, выбрать интересующий нас месяц, а таблица покажет ранжирование уже исходя из этого месяца.

ТРЕНИНГИ

   Быстрый старт
   Расширенный Excel
   Мастер Формул
   Прогнозирование
   Визуализация
   Макросы на VBA

КНИГИ

   Готовые решения
   Мастер Формул
   Скульптор данных

ВИДЕОУРОКИ

ОНЛАЙН-КУРСЫ

ФОРУМ

   Excel
   Работа
   PLEX

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

Техническая поддержка сайта