Как посчитать рейтинг в excel

Число — число, для которого определяется ранг.

Ссылка — ссылка на список чисел (диапазон ячеек с числами). Напрямую массив задать нельзя, формула =РАНГ(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;"

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

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

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

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

В столбце А содержатся названия группы, в столбце В — значения.

Связь функций НАИБОЛЬШИЙ() / НАИМЕНЬШИЙ() и РАНГ()

Функции НАИБОЛЬШИЙ() и РАНГ() являются взаимодополняющими в том смысле, что записав формулу =НАИБОЛЬШИЙ($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;" .

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

Читайте также:  Как изменить папку хранения резервных копий itunes

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

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

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


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


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


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


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


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

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

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

Пожалуйста скачайте пример по ссылке ниже, что бы было проще понять те действия, которые будут описаны ниже:

Первый этап.

Во-первых, давайте отсортируем все продажи по убыванию и выберем 10 лучших.

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

Наша формула выглядит следующим образом:

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

В результате мы получаем лист Топ-10 продаж. Далее, более сложная часть.

Второй этап.

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

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

никогда не будет одинаковым

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

Наша формула может выглядеть следующим образом:

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

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

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

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

Протягиваем эту формулу для всех оставшихся ячеек.

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

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

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

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

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

Функция используется при ранжировании в перечне чисел. То есть позволяет узнать величину числа относительно других числовых значений. Если отсортировать список по возрастанию, то функция вернет позицию числа. Например, в массиве чисел <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 года, появилась функция РАНГ.РВ. Это абсолютный аналог предыдущей функции. Синтаксис такой же. Буквы «РВ» в названии указывают на то, что при обнаружении формулой одинаковых значений функция вернет высший номер ранжирования (то есть первого обнаруженного элемента в перечне равных).

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

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

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

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

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

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

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

Сравним работу двух функций:

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

Скачать примеры функции ранжирования РАНГ в Excel.

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

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

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

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

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

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

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

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

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

РАНГ.CP

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

РАНГ присвоит всем 3 числам второе место.

РАНГ.CH присвоит всем троим четвертое место.

РАНГ.РВ

Все просто — это та же функция РАНГ только для новых версий Excel — 2010 и старше.

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

Adblock detector