Как в excel ранжировать значения. Выполнение ранжирования в Microsoft Excel. Пример функции ранг.ср в excel

Функция РАНГ (RANK) в Excel используется для присвоения ранга значению из списка относительно других чисел.

Что возвращает функция

Возвращает число, обозначающее ранг числа относительно других значений из списка.

Синтаксис

=RANK(number, ref, ) – английская версия

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

number (число) – число, которому вы хотите присвоить ранг;
ref (ссылка) – диапазон чисел, относительно которых вы хотите задать ранг;
([порядок]) (опционально) – число, определяющее каким подходом будет присвоен ранг (по убыванию или по возрастанию).

Дополнительная информация

Если аргумент Order (порядок) :

равен “0” или не указан, то самое большое число будет иметь значение “1”;
равен “1” – самое маленькое число получит ранг “1”.
одинаковые значения в списке получают одинаковый ранг
если в списке два числа с рангом “2”, то следующее число будет иметь ранг “4”

Команда Microsoft представила новую функцию RANK.EQ . Она работает точно так же, как функция RANK (РАНГ) . Команда MS рекомендует использовать эту новую функцию для повышения точности. Функция RANK (РАНГ) была сохранена для совместимости.

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

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

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

Сортировка данных

Начнем с самого простого и доступного варианта — сортировки.

Мы уже частично разбирали как можно структурировать данные с помощью .
Вкратце, для сортировки необходимо выделить диапазон с данными и на панели вкладок выбрать Главная -> Редактирование -> Сортировка и фильтр , а далее указать по какому критерию нужно произвести сортировку.

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

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

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

Ранжирование данных

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

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

Синтаксис и описание функции:

Число (обязательный аргумент) — число для которого вычисляется ранг;
Ссылка (обязательный аргумент) — массив или ссылка на массив чисел;
Порядок (необязательный аргумент) — способ упорядочения.
Если аргумент равен 0 или не указан, то значение 1 присваивается максимальному элементу в списке (условно говоря, сортируем по убыванию), в ином случае значение 1 присваивается минимальному элементу (сортируем по возрастанию).

Эта функция доступна во всех версиях Excel, однако начиная с Excel 2010 на ее замену добавлены РАНГ.РВ и РАНГ.СР , а РАНГ оставлена для совместимости с Excel 2007, давайте подробнее остановимся на их работе.

Функции РАНГ.РВ и РАНГ.СР в Excel

Синтаксис и описание функций:

РАНГ.РВ(число; ссылка; [порядок])
Возвращает ранг числа в списке чисел: его порядковый номер относительно других чисел в списке; если несколько значений имеют одинаковый ранг, возвращается высший ранг из этого набора значений.

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

Как мы видим отличие заключаются лишь в типе ранжирования совпадающих элементов данных.

В случае с РАНГ.РВ равным элементам присваивается высший ранг.
В нашем примере категориям Ноутбуки и Мультиварки соответствует одинаковое значение элемента — 710, который является 3 по порядку убывания, соответственно обоим значениям присваивается высший ранг — 3.
Для РАНГ.СР для этих же значений устанавливается их средний ранг, т.е. среднее между 3 и 4 порядковыми номерами — 3,5.

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

Автоматическая сортировка

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

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

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

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

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

Спасибо за внимание!
Если у вас остались вопросы - пишите в комментариях.

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

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

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

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

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

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

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

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

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

Используем для ячейки 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.

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

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

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

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

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

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

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

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

В качестве шапки для сортировки мы будем использовать клетку 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, выбрать интересующий нас месяц, а таблица покажет ранжирование уже исходя из этого месяца.

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

2. Заполняем аргументы в открывшемся окне: «Число» - указываем первое значение в нашей таблице в той же строке, где находится формула.

3. «Ссылка» - указываем весь массив данных, т.е. диапазон со всеми числами (значениями продаж).

4. Фиксируем границы этого диапазона (нажимаем F4 на клавиатуре) для того, чтобы при протягивании в дальнейшем адрес диапазона не «съезжал» и нажимаем ОК.

5. Протягиваем, формулу на все ячейки столбца «рейтинг» вниз.

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

Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее "перевести")

В данной статье будет рассмотрено несколько статистических функций приложения Excel:

Функция МАКС

Возвращает максимальное числовое значение из списка аргументов.

Синтаксис: =МАКС

Пример использования:

=МАКС ({1;2;3;4;0;-5;5;"50"}) – возвращает результат 5, при этом строка «50» игнорируется, т.к. задана в массиве.
=МАКС (1;2;3;4;0;-5;5;"50") – результатом функции будет 50, т.к. строка явно задана в виде отдельного аргумента и может быть преобразована в число.
=МАКС (-2; ИСТИНА) – возвращает 1, т.к. логическое значение задано явно, поэтому не игнорируется и преобразуется в единицу.

Функция МИН

Возвращает минимальное числовое значение из списка аргументов.

Синтаксис: =МИН (число1; [число2]; …), где число1 является обязательным аргументом, все последующие аргументы (до число255) необязательны. Аргумент может принимать числовые значения, ссылки на диапазоны и массивы. Текстовые и логические значения в диапазонах и массивах игнорируются.

Пример использования:

=МИН ({1;2;3;4;0;-5;5;"-50"}) – возвращает результат -5, текстовая строка игнорируется.
=МИН (1;2;3;4;0;-5;5;"-50") – результатам функции будет -50, так как строка «-50» задана в виде отдельного аргумента и может быть преобразована в число.
=МИН (5; ИСТИНА) – возвращает 1, так как логическое значение задано явно в виде аргумента, поэтому не игнорируется и преобразуется в единицу.

Функция НАИБОЛЬШИЙ

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

Синтаксис: =НАИБОЛЬШИЙ (массив; n), где

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

Пример использования:

На изображении приведено 2 диапазона. Они полностью совпадают, кроме того, что в первом столбце диапазон отсортирован по убыванию, он представлен для наглядности. Функция ссылается на диапазон ячеек во втором столбце и возвращает элемент, являющийся 3 наибольшим значением.

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

Функция НАИМЕНЬШИЙ

Возвращает значение элемента, являвшегося n-ым наименьшим, из указанного множества элементов. Например, третий наименьший, шестой наименьший.

Синтаксис: =НАИМЕНЬШИЙ (массив; n), где

массив – диапазон ячеек либо массив элементов, содержащий числовые значения. Текстовые и логические значения игнорируются.
n – натуральное число (кроме нуля), указывающее позицию элемента в порядке возрастания. Если задать дробное число, то оно округляется до целого в меньшую сторону (дробные числа меньше единицы возвращают ошибку). Если аргумент превышает количество элементов множества, то функция возвращает ошибку.

Массив или диапазон НЕ обязательно должен быть отсортирован.

Пример использования:

Функция РАНГ

Возвращает позицию элемента в списке по его значению, относительно значений других элементов. Результатом функции будет не индекс (фактическое расположение) элемента, а число, указывающее, какую позицию занимал бы элемент, если список был отсортирован либо по возрастанию либо по убыванию.
По сути, функция РАНГ выполняет обратное действие функциям НАИБОЛЬШИЙ и НАИМЕНЬШИЙ, т.к. первая находит ранг по значению, а последние находят значение по рангу.
Текстовые и логические значения игнорируются.

число – обязательный аргумент. Числовое значение элемента, позицию которого необходимо найти.
ссылка – обязательный аргумент, являющийся ссылкой на диапазон со списком элементов, содержащих числовые значения.
порядок – необязательный аргумент. Логическое значение, отвечающее за тип сортировки:
- ЛОЖЬ – значение по умолчанию. Функция проверяет значения по убыванию.
- ИСТИНА – функция проверяет значения по возрастанию.

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

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

Пример использования:

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

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

Функция СРЗНАЧ

Возвращает среднее арифметическое значение заданных аргументов.

Синтаксис: =СРЗНАЧ (число1; [число2]; …), где число1 является обязательным аргументом, все последующие аргументы (до число255) необязательны. Аргумент может принимать числовые значения, ссылки на диапазоны и массивы. Текстовые и логические значения в диапазонах и массивах игнорируются.

Пример использования:

Результатом выполнения функции из примера будет значение 4, т.к. логические и текстовые значения будут проигнорированы, а (5 + 7 + 0 + 4)/4 = 4.

Функция СРЗНАЧА

Аналогична функции СРЗНАЧ за исключением того, что истинные логические значения в диапазонах приравниваются к 1, а ложные значения и текст приравнивается к нулю.

Пример использования:

Возвращаемое значение в следующем примере 2,833333, так как текстовые и логические значения принимаются за ноль, а логическое ИСТИНА приравнивается к единице. Следовательно, (5 + 7 + 0 + 0 + 4 + 1)/6 = 2,833333.

Функция СРЗНАЧЕСЛИ

Вычисляет среднее арифметическое значение для ячеек, отвечающих заданному условию.

Синтаксис: =СРЗНАЧЕСЛИ (диапазон; условие; [диапазон_усреднения]), где

диапазон – обязательный аргумент. Диапазон ячеек для проверки.
условие – обязательный аргумент. Значение либо условие проверки. Для текстовых значений могут быть использованы подстановочные символы (* и?). Условия типа больше, меньше записываются в кавычках.
диапазон_усреднения – необязательный аргумент. Ссылка на ячейки с числовыми значениями для определения среднего арифметического. Если данный аргумент опущен, то используется аргумент «диапазон».

Пример использования:

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

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

Функция СРЗНАЧЕСЛИМН

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

Синтаксис: =СРЗНАЧЕСЛИМН (диапазон_усреднения; диапазон_условия1; условие1; [диапазон_условия2]; [условие2]; …), где

диапазон_усреднения – обязательный аргумент. Ссылка на ячейки с числовыми значениями для определения среднего арифметического.
диапазон_условия1 – обязательный аргумент. Диапазон ячеек для проверки.
условие1 – обязательный аргумент. Значение либо условие проверки. Для текстовых значений могут быть использованы подстановочные символы (* и?). Условия типа больше, меньше заключаются в кавычки.

Все последующие аргументы от диапазон_условия2 и условие2 до диапазон_условия127 и условие127 являются необязательными.

Пример использования:

Используем таблицу из примера предыдущей функции с добавлением городов для сотрудников. Выведем среднюю заработную плату для электриков в городе Москва.
Результат выполнения функции 25 000.
Функция принимает в расчет только те значения, которые подходят под все условия.

Функция СЧЁТ

Подсчитывает количество числовых значений в диапазоне.

Синтаксис: =СЧЁТ (значение1; [значение2]; …), где значение1 – обязательный аргумент, принимающий значение, ссылку на ячейку, диапазон ячеек или массив. Аргументы от значение2 до значение255 являются необязательными и аналогичными значение1.

Логические значения в диапазонах и массивах игнорируются. Если такое значение задано явно в аргументе, то оно учитывается как число.

Пример использования:

=СЧЁТ (1; 2; "5") – результат функции 3, т.к. строка «5» конвертируется в число.
=СЧЁТ ({1; 2; "5"}) – результатом выполнения функции будет значение 2, так как, в отличие от первого примера, число в виде строки записано в массиве, поэтому не будет преобразовано.
=СЧЁТ (1; 2; ИСТИНА) – результат функции 3. Если бы логическое значение находилось бы в массиве, то оно не засчиталось как число.

Функция СЧЁТЕСЛИ

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

Синтаксис: =СЧЁТЕСЛИ (диапазон; критерий), где

диапазон – обязательный аргумент. Принимает ссылку на диапазон ячеек для проверки на условие.
критерий – обязательный аргумент. Критерий проверки, содержащий значение либо условия типа больше, меньше, которые необходимо заключать в кавычки. Для текстовых значений можно использовать подстановочные символы (* и?).

Пример использования:

Функция СЧЁТЕСЛИМН

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

Синтаксис: =СЧЁТЕСЛИМН (диапазон1; критерий1; [диапазон2]; [критерий2]; …).

Пример использования:

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

Функция СЧЁТЗ

Подсчитывает непустые ячейки в указанном диапазоне.

Синтаксис: =СЧЁТЗ (значение1; [значение2]; …), где значение1 является обязательным аргумент, все последующие аргументы до значение255 необязательны. В качестве значения может содержаться ссылка на ячейку или диапазон ячеек.

Пример использования:

Функция возвращает значение 4, т.к. ячейка A3 содержит текстовую функцию, возвращающую пустую строку.