Привет, коллега, на связи Ленивый аналитик)
Поняла, что мне даже не нужно придумывать темы для своего контента - их подкидывает моя работа с завидной регулярностью.
Ну а я человек простой, вижу что-то интересное - делюсь))
Только я поделилась в своем Телеграмм-канале, как сделать в сводной таблице ТОП-n значений, как мне прилетел запрос сделать отчет, который бы выводил определенное количество ТОП-значений списка - и это значение должно быть динамическим.
Ну то есть, сегодня заказчик хочет видеть ТОП-5, а завтра ТОП-10. Можно так?
Можно, причем достаточно просто, если обратиться к Power Query.
>>> если ты еще не знаком с Power Query, то посмотри на моем канале подборку Power Query <<<
1. Дано
(для тех, кто подписан на меня и в телеге поясню, что покажу на тех же цифрах, т.к. на реальных данных, конечно же, нельзя)
Список населенных пунктов и процент выполнения плана продаж.
Всего в списке 215 населенных пунктов.
2. Загрузим эти данные в Power Query
Для этого щелкнем в любой ячейке внутри диапазона данных, перейдем на вкладку Данные - Получить данные - Из других источников - Из таблицы/диапазона (или из блока Получить и преобразовать данные выбрать тот же пункт)
Excel предложит вам создать таблицу ("умную таблицу") - проверьте, чтобы была установлена галочка Таблица с заголовками, и нажмите Ок. Диапазон ячеек определяется автоматически, но можно дополнительно проверить его корректность.
Таблица загружена в редактор Power Query.
Переименуем запрос для наглядности.
Также на этом этапе можно сделать все необходимые преобразования данных.
3. Загрузим в сводную таблицу
На вкладке Главная выбираем выпадающее меню Закрыть и загрузить - Закрыть и загрузить в...
Выбираем Отчет сводной таблицы - Новый лист - нажимаем Ок.
Создаем сводную таблицу, как обычно.
Перетягиваем Населенный пункт в Строки а Выполнение плана продаж - в Значения.
4. Отсортируем таблицу
В созданной сводной таблице Населенные пункты по умолчанию отсортированы по алфавиту.
Нам необходимо отсортировать их по убыванию Выполнения плана продаж.
Для этого в выпадающем меню строк выберем Дополнительные параметры сортировки.
Установим переключатель По убыванию и выберем из списка Сумма по полю Выполнение плана продаж %.
Теперь поле Населенный пункт будет отсортировано уже не по алфавиту, а по убыванию процента выполнения плана.
5. Настроим формат значений
Щелкнем правой кнопкой мыши на любом значении, выберем Параметры поля значений - Числовой формат - и настроим нужный формат, у меня это Процентный.
(к слову, я очень сильно не рекомендую настраивать формат значений для сводной таблицы через вкладку Главная, как для обычных ячеек - оно потом ведет себя непредсказуемо)
6. А теперь сделаем динамический ТОП
Если потребитель нашего отчета - человек творческий, и ему нужно видеть, то 5, то 25 ТОП значений, то поможем ему в этом.
Для этого создадим небольшую табличку из одного заголовка и одной ячейки - в ней можно будет менять величину ТОП списка.
Загрузим ее в Power Query так же, как основную таблицу (см. пункт 2)
В редакторе Power Query перейдем к основной таблице с данными о выполнении плана и отсортируем ее по убыванию Выполнения плана продаж.
Перейдем на вкладку Добавление столбца - Столбец индекса - От 1.
Добавим второй вспомогательный запрос.
Для этого перейдем на вкладку Добавление столбца - Настраиваемый столбец.
В поле формулы напишите
= название вашего вспомогательного запроса
И нажмите Ок.
Получили табличный столбец, который необходимо развернуть, нажав на стрелки.
Изменим тип данных для нового столбца на Целое число.
Добавим условный столбец Фильтр.
Вкладка Добавление столбца - Условный столбец.
Дадим ему имя Фильтр и укажем следующую конструкцию.
Она буквально означает, что все наши самые верхние значения списка, которые имеют индекс меньше, чем указанное значения ТОПа, будут помечены 1. Остальные 0.
А теперь отфильтруем столбец фильтр по значению 1.
Осталось нажать Закрыть и загрузить - Закрыть и загрузить в... - Только создать подключение.
(это нужно, чтобы загруженная вспомогательная табличка со значение ТОПа не выгрузилась еще раз в файл в виде результата запроса)
Готово!
Мы видим ТОП-5 населенных пунктов по выполнению плана продаж.
Вы скажете, что это можно было бы сделать и без квери (как раз этот случай я описывала в последнем посте в своем Телеграмм-канале).
Но - наш вариант динамический!
Поменяйте значение в ячейке ТОП и обновите сводную таблицу.
Да, мы сделали эту историю интерактивной!
Можно это действие повесить на кнопку с макросом. Чтобы ваш творческий заказчик вообще был счастлив)))
В общем, твори и помни, что для Excel нет ничего невозможного!
И подпишись на мой Телеграмм, там полезно.