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

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

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

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

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

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

5. Распространите формулу на все ячейки в «столбце оценки.

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

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

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

Первый этап.

Сначала мы сортируем все продажи в порядке убывания и выбираем 10 лучших.

Для этого я решил использовать функцию

ОТЛИЧНО . 

Наша формула выглядит так:

= БОЛЬШОЙ ($ C $ 4: $ C $ 19, СТРОКА (КОСВЕННАЯ («1:» & СТРОКИ ($ C $ 4: $ C $ 19))))

где C4: C19 — это диапазон количества проданных товаров.

В результате мы получаем список из 10 лучших продаж. Следующая трудная часть.

Второй этап.

Как я могу присвоить номерам названия продуктов?

Если вы уверены, что количество проданных товаров

это никогда не будет прежним

(т.е не будет повторяющихся значений), поэтому мы можем использовать функции ИНДЕКС и ПОИСК, чтобы найти совпадающее название продукта для выбранного номера продажи.

Наша формула может выглядеть так:

= ИНДЕКС ($ B $ 4: $ B $ 19; ПОИСК (F4; $ C $ 4: $ C $ 19,0); 1)

И будет отлично работать.

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

Для первого продукта воспользуемся формулой:

= ИНДЕКС ($ B $ 4: $ B $ 19; ПОИСК (F4; $ C $ 4: $ C $ 19,0); 1)

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

= КОСВЕННО («Лист1!» & АДРЕС (ЕСЛИ (Лист1! $ C $ 4: $ C $ 19 = F5; СТРОКА (Лист1! $ B $ 4: $ B $ 19); 65536); СЧЁТЕСЛИ (F4: F5; F5)); 2))

Растягиваем эту формулу для всех остальных ячеек.

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

Обратите внимание на фигурные скобки перед формулой и после нее. Эти скобки указывают на то, что формула была применена к массиву. Чтобы получить тот же результат, введите формулу в ячейку, затем нажмите комбинацию клавиш Ctrl + Shift + Enter.

С каждым изменением количества проданных товаров список Топ-10 продаж будет автоматически обновляться.

Повеселись!

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

Функция используется при ранжировании в списке чисел. То есть позволяет узнать величину числа относительно других числовых значений. Если вы отсортируете список в порядке возрастания, функция вернет позицию числа. Например, в массиве чисел {30; 2; 26} число 2 будет иметь ранг 1; 26-2; 30 –3 (как наибольшее значение в списке).

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

1. Число. Поэтому необходимо определить номер в рейтинге.
2. Связь. Массив чисел или диапазон ячеек с числовыми значениями. Если вы укажете только числа в качестве аргумента, функция вернет ошибку. Нечисловым значениям не присваиваются номера.
3. Порядок. Способ сортировки чисел в списке. Опции: аргумент равен «0» или опущен — значение 1 присваивается максимальному номеру в списке (как если бы список был отсортирован в порядке убывания); аргументом является любое ненулевое число — номер ранжирования 1 присваивается самому низкому номеру в списке (как если бы список был отсортирован в порядке возрастания).

Определяем ранжирование номеров в списке без повторений:

Аргумент, определяющий способ сортировки чисел, равен «0». Следовательно, в этой функции номерам присвоены значения от самого высокого до самого низкого. Максимальному числу 87 присваивается число 1.

Третий столбец показывает формулу в порядке возрастания.

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

Повторяющиеся номера выделяются желтым цветом. Им присваивается одинаковый номер. Например, цифре 7 во втором столбце присваивается номер 9 (как во второй строке, так и в девятом); в третьем столбце — 3. Но ни одно из чисел во втором столбце не будет иметь 10, а в третьем — 4.

Чтобы ранги не повторялись (иногда мешает пользователю решить задачу), используется следующая формула:

вы можете установить ограничения для работы функции. Например, вам просто нужно ранжировать значения от 0 до 30. Для решения проблемы используйте функцию ЕСЛИ (= ЕСЛИ (A2

Значения, соответствующие указанному условию, выделены серым. Для чисел больше 30 отображается пустая строка.

Пример функции РАНГ.РВ в Excel

В версиях Excel, начиная с 2010 года, появилась функция RANK.RV. Это абсолютный аналог предыдущей функции. Синтаксис такой же. Буквы «PB» в имени означают, что если формула находит те же значения, функция вернет номер наивысшего ранжирования (то есть первый элемент, найденный в списке равных).

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

Пример функции РАНГ.СР в Excel

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

Вот результат работы функции:

Формула в столбце «по убыванию»: = RANK.CP (A2; $ A $ 2: $ A $ 9; 0). Таким образом, функция присвоила значение 87 среднему числу 1,5.

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

Функция присвоила каждому из них рейтинг 5, что является средним значением 4, 5 и 6.

Давайте сравним, как работают две функции:

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

Загрузите примеры функции ранжирования RANK в Excel.

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

Вам нужно присвоить номер каждому номеру в Excel, чтобы вы могли отсортировать их по этому номеру? Вы можете создавать сложные конструкции для текстовых данных, но для числовых данных в Excel есть специальная функция RANK. Это статическая функция, которая может быть очень полезной. В статье также рассказывается о новых функциях Excel 2010 RANK.CP () и RANK.RV()

Как пользоваться. Функция РАНГ в Excel

Функция RANK довольно проста в использовании. Набор аргументов также прост, а именно:

РЕЙТИНГ (число; ссылка;)

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

• Число — число, по которому вы хотите определить ранг / позицию в массиве
• Ссылка: массив значений, в котором определяется степень / положение числа
• Заказ не является обязательным.

… Если вы введете 0 или не заполните этот аргумент, ранг определяется в порядке убывания. Те, у кого больше всего 2000, будут на 1-м месте.

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

На первом изображении подробно показано, как использовать функцию.

Примечание. Если в списке 2 одинаковых номера — в нашем случае 1400, то будут присвоены одинаковые значения ранга — 2, то есть второе место. В этом случае следующим местом будет уже не 3, а 4 (1250). Достаточно важное свойство, которое подошло далеко не всем. Поэтому разработчики пошли дальше и создали в Excel 2010 дополнительные функции RANK.CP () и RANK.RV.

РАНГ.CP

Если числа с одинаковым значением 1400 равны 3 или более, всем им будет присвоен ранг = 2 — это для обычной функции РАНГ. Функция RANK.SR рассчитает рейтинг как среднее значение 3 позиций, т.е. 3 места классифицируются вторым, третьим и четвертым

RANK предоставит всем трем номерам второе место.

RANK.CH присвоит всем троим четвертое место.

РАНГ.РВ

все просто: та же функция RANK только для более новых версий Excel — 2010 и более ранних.