Как ранжировать данные в excel
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Как ранжировать значения по группам в Excel
Вы можете использовать следующие формулы для ранжирования значений по группам в Excel:
Формула 1: ранжирование значений по группам
=SUMPRODUCT(( $A$2:$A$13 = A2 )\*( $B$2:$B$13 > B2 ))+1 Эта конкретная формула находит ранг значения в ячейке B2 , принадлежащего группе в ячейке A2 .
Эта формула присваивает ранг 1 наибольшему значению, 2 — второму наибольшему значению и т. д.
Формула 2: ранжирование значений по группам (обратный порядок)
=SUMPRODUCT(( $A$2:$A$13 = A2 )\*( $B$2:$B$13 < B2 ))+1 Эта конкретная формула находит обратный ранг значения в ячейке B2 , принадлежащего группе в ячейке A2 .
Эта формула присваивает ранг 1 наименьшему значению, 2 — второму наименьшему значению и т. д.
В следующих примерах показано, как использовать каждую формулу в Excel.
Пример 1. Ранжирование значений по группам в Excel
Предположим, у нас есть следующий набор данных в Excel, который показывает очки, набранные баскетболистами в разных командах:
Мы можем использовать следующую формулу для ранжирования очков по командам:
=SUMPRODUCT(( $A$2:$A$13 = A2 )\*( $B$2:$B$13 > B2 ))+1 Мы введем эту формулу в ячейку C2 , затем скопируем и вставим формулу в каждую оставшуюся ячейку в столбце C:
Вот как интерпретировать значения в столбце C:
- Игрок с 22 очками за Mavs занимает второе место по количеству очков среди игроков Mavericks.
- Игрок с 28 очками за Mavs занимает первое место по количеству очков среди игроков Mavericks.
- Игрок с 31 очком за «шпоры» занимает 2-е место по количеству очков среди игроков «шпор».
Пример 2. Ранжирование значений по группам (обратный порядок) в Excel
Еще раз предположим, что у нас есть следующий набор данных в Excel, который показывает очки, набранные баскетболистами в разных командах:
Мы можем использовать следующую формулу для ранжирования очков в обратном порядке по командам:
=SUMPRODUCT(( $A$2:$A$13 = A2 )\*( $B$2:$B$13 < B2 ))+1 Мы введем эту формулу в ячейку C2 , затем скопируем и вставим формулу в каждую оставшуюся ячейку в столбце C:
Вот как интерпретировать значения в столбце C:
- Игрок с 22 очками за Mavs занимает 3-е место по наименьшему количеству очков среди игроков Mavericks.
- Игрок с 28 очками за Mavs занимает 4-е место по наименьшему количеству очков среди игроков Mavericks.
- Игрок с 31 очком за «шпоры» занимает 4-е место по наименьшему количеству очков среди игроков «шпор».
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:
Примеры функции РАНГ для ранжирования списков по условию в 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 Formula Examples
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры
Как ранжировать элементы по нескольким критериям в Excel
Вы можете использовать комбинацию функций RANK.EQ() и COUNTIFS() в Excel для ранжирования элементов по нескольким критериям.
В следующем примере показано, как использовать эти функции для ранжирования элементов в списке по нескольким критериям в Excel.
Пример. Ранжирование по нескольким критериям в Excel
Предположим, у нас есть следующий набор данных в Excel, который показывает общее количество очков и передач для восьми разных баскетболистов.
Теперь предположим, что мы хотим ранжировать каждого игрока по следующим критериям:
- Во-первых, ранжируйте каждого игрока на основе очков.
- Затем ранжируйте каждого игрока на основе передач.
Мы можем использовать следующую формулу для выполнения этого ранжирования по нескольким критериям:
=RANK.EQ( $B2 , $B$2:$B$9 ) + COUNTIFS( $B$2:$B$9 , $B2 , $C$2:$C$9 , ">" & $C2 ) Мы можем ввести эту формулу в ячейку D2 нашей электронной таблицы, а затем скопировать и вставить формулу во все остальные ячейки в столбце D:
Из вывода мы видим, что Энди получает ранг 1 , потому что он делит наибольшее количество очков с Бернардом. Однако у Энди больше передач, чем у Бернарда, поэтому он получает ранг 1 , а Бернард получает ранг 2 .
Каждый игрок оценивается аналогичным образом, сначала на основе их общего количества очков, а затем на основе их общего количества передач.
Если вместо этого мы хотим выполнить ранжирование по нескольким критериям в обратном порядке («лучший» игрок получает наивысший рейтинг), мы можем использовать следующую формулу:
=RANK.EQ( $B2 , $B$2:$B$9 , 1) + COUNTIFS( $B$2:$B$9 , $B2 , $C$2:$C$9 , " Мы можем ввести эту формулу в ячейку D2, а затем скопировать и вставить формулу во все остальные ячейки в столбце D:
Обратите внимание, что ранжирование полностью противоположно предыдущему примеру. Игрок с наибольшим количеством очков и передач (Энди) теперь имеет рейтинг 8 .
Точно так же у Бернарда теперь рейтинг 7.И так далее.
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные функции в Excel: