Если вам нужно быстро построить оценку значений, не прибегая к сортировке данных в таблице, вы можете использовать функцию 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 (как наибольшее значение в списке).
Синтаксис функции:
- Число. Поэтому необходимо определить номер в рейтинге.
- Связь. Массив чисел или диапазон ячеек с числовыми значениями. Если вы укажете только числа в качестве аргумента, функция вернет ошибку. Нечисловым значениям не присваиваются номера.
- Порядок. Способ сортировки чисел в списке. Опции: аргумент равен «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 и более ранних.