Рейтинг в excel как сделать

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

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