Если вам нужно быстро ранжировать значения без сортировки данных в таблице, вы можете использовать функцию RANK. За это:
1. В дополнительную колонку, в которой будем указывать оценку, вставляем функцию РАНГ (вводим в ячейку =РАНГ и выбираем из списка предложенную функцию 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))) ;два))
Растягиваем эту формулу на все оставшиеся ячейки.
Как вы можете видеть в прикрепленном файле, это решение работает очень хорошо и дает желаемый результат.
Обратите внимание на квадратные скобки до и после формулы. Эти скобки указывают, что формула применяется к массиву. Чтобы добиться того же результата, введите формулу в ячейку, а затем нажмите комбинацию клавиш Ctrl+Shift+Enter.
При каждом изменении количества проданных товаров список 10 лучших продаж будет автоматически перестраиваться.
Наслаждаться!
Для ранжирования данных в Excel используются статистические функции РАНГ, РАНГ.РВ, РАНГ.СР. Все они возвращают номер числа в отсортированном списке числовых значений. Рассмотрим подробнее синтаксис, примеры.
Пример функции РАНГ в Excel
Функция используется при сортировке списка чисел. То есть позволяет узнать значение числа по отношению к другим числовым значениям. Если отсортировать список в порядке возрастания, функция вернет позицию числа. Например, в массиве чисел {30;2;26} число 2 будет иметь ранг 1; 26 -2; 30 -3 (как наибольшее значение в списке).
Синтаксис функции:
- Количество. По которому вы хотите определить номер в рейтинге.
- Связь. Массив чисел или диапазон ячеек с числовыми значениями. Если указать в качестве аргумента только числа, функция вернет ошибку. Нечисловым значениям номер не присваивается.
- Заказ. Способ сортировки чисел в списке. Опции: аргумент равен «0» или опущен — максимальному номеру в списке присваивается значение 1 (как если бы список был отсортирован по убыванию); Аргумент — любое ненулевое число: номер ранга 1 соответствует наименьшему числу в списке (как если бы список был отсортирован в порядке возрастания).
Определим ранжирование чисел в списке без повторений:
Аргумент, определяющий способ сортировки чисел, равен «0». Поэтому в этой функции значениям были присвоены номера от большего к меньшему. Максимальному числу 87 присваивается номер 1.
В третьем столбце приведена формула с возрастающим рангом.
Определим количество значений в списке, где есть повторяющиеся значения.
Повторяющиеся номера выделены желтым цветом. Все они имеют одинаковый номер. Например, цифре 7 во втором столбце соответствует цифра 9 (и во второй, и в девятой строке); в третьем столбце — 3. Но ни у одного из чисел во втором столбце не будет 10, а в третьем — 4.
Чтобы избежать повторения диапазонов (иногда это мешает пользователю решить задачу), используется следующая формула:
Вы можете установить ограничения для работы функции. Например, вам нужно только ранжировать значения от 0 до 30. Чтобы решить задачу, примените функцию ЕСЛИ (=ЕСЛИ(A2
Значения, соответствующие заданному условию, выделяются серым цветом. Для чисел больше 30 отображается пустая строка.
Пример функции РАНГ.РВ в Excel
В версиях Excel с 2010 года появилась функция RANK.EQ. Это абсолютная аналогия предыдущей функции. Синтаксис такой же. Буквы «PB» в названии означают, что если формула найдет одинаковые значения, функция вернет число с наивысшим рангом (то есть первый элемент, найденный в списке равных).
Как видно из примера, эта функция обрабатывает повторяющиеся числа в списке так же, как и в обычной формуле. Если необходимо избежать повторения диапазонов, используем другую формулу (см выше).
Пример функции РАНГ.СР в Excel
Возвращает номера числового значения в списке (порядковый номер относительно других значений). То есть выполняет ту же задачу. Возвращает среднее, только если найдены идентичные значения.
Вот вывод функции:
Формула в нисходящем столбце: =ДИАПАЗОН.СРЕДНИЙ(A2,$A$2:$A$9,0). Итак, функция присвоила значению 87 среднее число 1,5.
Допустим, в списке чисел есть три повторяющихся значения (выделены оранжевым цветом).
Функция присвоила каждому из них ранг 5, что является средним значением 4, 5 и 6.
Сравним работу двух функций:
Помните, что эти две функции работают только в Excel 2010 и более поздних версиях. В более ранних версиях для этой цели можно было использовать формулу массива.
Загрузите примеры функции ранжирования RANK в Excel.
Поэтому все описанные выше примеры позволяют автоматизировать работу по классификации данных и классификации исходных значений.
Вам нужно присвоить номер каждому числу в Excel, чтобы вы могли сортировать их по этому номеру? Вы можете создавать сложные структуры для текстовых данных, но для числовых данных в Excel есть специальная функция СОРТИРОВКА. Она относится к статическим функциям и может быть весьма полезной. В статье также рассказываем о новых функциях Excel 2010 RANK.CP() и RANK.РВ()
Как пользоваться. Функция РАНГ в Excel
Функция RANK довольно проста в использовании. Набор аргументов также прост, а именно:
ДИАПАЗОН(число, ссылка;)
Описание аргументов:
- Число: число, для которого нужно определить ранг/место в массиве
- Ссылка: массив значений, в котором определяется ранг/место числа
- Порядок является необязательным аргументом.
. Если поставить 0 или не заполнять этот аргумент, то ранг определяется в порядке убывания. Те, у кого наибольшее число 2000, будут на 1-м месте.
. Если поставить любое другое число, максимальное значение будет ниже последнего места.
Он подробно показывает, как использовать функцию на первом изображении.
Примечание. Если в списке есть 2 одинаковых номера, в нашем случае 1400, им будут присвоены одинаковые значения ранга: 2, что означает второе место. В этом случае следующей позицией будет уже не 3, а 4 (1250). Довольно важное свойство, которое устраивало не всех. Поэтому разработчики пошли дальше и сделали в Excel 2010 дополнительные функции RANK.CP() и RANK.PB.
РАНГ.CP
Если есть 1400 3 или более чисел с одинаковым значением, всем им будет присвоен ранг = 2, это для обычной функции RANGE. Функция RANK.AVG вычисляет место как среднее из 3 мест, то есть 3 места занимают второе, третье и четвертое места
RANK даст всем 3 числам второе место.
RANK.CH поместит тройку на четвертое место.
РАНГ.РВ
Все просто: это та же функция СОРТИРОВКА только для новых версий Excel: 2010 и ранее.
Пример файла
Вы можете прочитать о том, как ранжировать и найти наибольшую ценность в этой статье
Поделитесь нашей статьей в своих социальных сетях:
(Посещение 6309 раз, 1 посещение сегодня)