Сегодня мы научимся создавать список Топ 10. В качестве исходного материала мы будем использовать список продуктов с соответствующим количеством продаж по каждому продукту за выбранный период времени.
То, что мы хотим получить в конце - это сгенерированный список из 10 самых продаваемых товаров. Также мы хотим, что бы этот список автоматически обновлялся при каждом изменении количества продаж товаров и мы не хотим использовать VBA макросы для упрощения задачи.
Пожалуйста скачайте пример по ссылке ниже, что бы было проще понять те действия, которые будут описаны ниже:
Скачать пример.
Для этого я решил использовать функцию НАИБОЛЬШИЙ.
Наша формула выглядит следующим образом:
В результате мы получаем лист Топ-10 продаж. Далее, более сложная часть.
Если Вы уверены, что количество проданных товаров никогда не будет одинаковым (т.е. не будет повторяющихся значений), то мы можем использовать функции ИНДЕКС и ПОИСКПОЗ для поиска соответствующего наименования продукта выбранному количеству продаж.
Наша формула может выглядеть следующим образом:
Но, если количество продаж может повторяться, то предыдущая формулы будет возвращать одинаковое наименование продукта для каждого повторяющегося числа.Это явно не то, что мы хотим получить.Поэтому мы будем использовать несколько другой подход.
Для первого продукта мы воспользуемся формулой:
Как Вы можете видеть в приложенном файле, это решение отлично работает и дает нужный результат.
Обратите внимание на фигурные скобки перед и после формулы. Эти скобки обозначают что формула применена для массива. Что бы Вам добиться такого же результата, то внесите в ячейку формулу, а после нажмите комбинацию клавиш Ctrl+Shift+Enter.
При каждом изменении количества проданных товаров, перечень Топ-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 продаж будет автоматически перестраиваться.
Наслаждайтесь!
0 comments:
Отправить комментарий
Спасибо за комментарий.