Динамический "Топ-N + Другие" в сводной таблице
В аналитических отчетах и дашбордах весьма часто встречается потребность вывести итоговые значения в формате сводной таблицы вида "Топ-N + другие":
Причем это самое N (на картинке равное 5) хотелось бы иметь динамическим, т.е. менять на 3-5-10... и т.д. и чтобы сводная таблица при этом "на лету" пересчитывалась, отображая топ заданного размера и собирая все остальные меньшие значения в группу "Другие".
Сводные таблицы Excel так, к сожалению, пока не умеют, но если доработать исходные данные с помощью запроса Power Query, то задача решается относительно легко.
В качестве исходных данных возьмем вот такую несложную таблицу заказов:
Здесь всё просто: одна строка - один заказ, а в ячейке H2 у нас будет храниться значение, задающее количество элементов в топе (остальные будут сгруппированы под именем Другие, например).
Шаг 1. Добавляем параметр N в Power Query
Первое, что нам нужно будет сделать - это добавить в Power Query значение N (жёлтой ячейки) как параметра, с тем, чтобы использовать его в дальнейшем в запросе анализа данных из большой таблицы заказов. Для этого:
- Выделяем жёлтую ячейку H2
- Даём ей имя в левой части строки формул - стираем H2, вводим имя (например Топ) и жмём Enter. Или же можно воспользоваться Диспетчером Имен на вкладке Формулы (Formulas - Name Manager).
- Загружаем созданный именованный диапазон в Power Query с помощью команды Данные - Из таблицы/диапазона (Data - From Table / Range).
- В редакторе запросов Power Query удаляем в правой панели все шаги кроме первого, а затем щёлкаем правой кнопкой мыши по ячейке со значением N и выбираем команду Детализация (Drill down), чтобы получить на выходе не таблицу, состоящую из одной строки и одного столбца, а числовое значение нашего параметра N.
- Чтобы вернуться в Excel выбираем на вкладке Главная команду Закрыть и загрузить - Закрыть и загрузить в... (Home - Close & Load - Close & Load to...) и затем вариант Только создать подключение (Only create connection).
В итоге справа в панели запросов должен появиться наш созданный числовой параметр:
Шаг 2. Определяем ранги товаров
Теперь превратим нашу большую таблицу заказов в динамическую (умную) сочетанием клавиш Ctrl+T или командой Главная - Форматировать как таблицу (Home - Format as Table) и загрузим её в Power Query тем же способом - кнопкой Из таблицы / диапазона с вкладки Данные (Data - From Table / Range).
Затем выполним группировку по товарам, просуммировав стоимости для каждого из них, выбрав на вкладке Преобразование команду Группировать по (Transform - Group by):
Отсортируем полученную таблицу по убыванию итоговых стоимостей (через кнопку фильтра в строке заголовка) и добавим к ней столбец с порядковым номером строки, т.е. местом в топе (рангом) каждого товара через вкладку Добавление столбца - Столбец индекса - От 1 (Add column - Index column - From 1):
Теперь нам нужно добавить вычисляемый столбец с проверкой: если ранг в столбце Индекс меньше или равен 5, т.е. значению нашего параметра Топ, то мы выводим этот ранг, в противном случае - выводим слово "Другие". Это легко реализовать кнопкой Условный столбец там же на вкладке Добавление столбца (Add Column - Conditional Column):
Само собой, нам нужно, чтобы в этой проверке сравнение происходило не с жёстко прописанной константой, а с нашим параметром Топ, который мы берём из жёлтой ячейки. Так что подменяем в строке формул пятёрку на имя Топ:
Полученная таблица нам потребуется чуть позже, так что имеет смысл для удобства переименовать этот шаг (он должен называться Условный столбец добавлен), чтобы легко вернуться к нему в будущем. Для этого можно щёлкнуть по шагу в правой панели правой кнопкой мыши и выбрать команду Переименовать (Rename) или нажать клавишу F2. Назовем его, например, Таблица рангов.
Шаг 3. Добавляем ранги к каждому заказу
Теперь нам нужно добавить вычисленные ранги (числа от 1 до N и слово "Другие") к нашей исходной таблице заказов. Можно, конечно, загрузить её в Power Query ещё раз (или дублировать запрос) и потом выполнить объединение с полученной выше таблицей, но гораздо проще сделать иначе.
Жмём на кнопку fx в строке формул Power Query - создается новый шаг, который ссылается на предыдущий - в строке формул за это отвечает формула:
=#"Таблица рангов"
Вместо ссылки на предыдущий шаг вводим в строку формул ссылку на второй шаг в нашем запросе, где таблица была ещё не сгруппирована:
=#"Измененный тип"
Теперь полученную таблицу нужно объединить с таблицей рангов, полученной нами ранее. Для этого на вкладке Главная жмём на кнопку Объединить запросы (Home - Merge Queries), выбираем в качестве нижней таблицы пока ту же самую Таблицу 1 и выделяем в обеих таблицах ключевой столбец для связи - Наименование:
После нажатия на ОК подправляем в строке формул третий аргумент в функции Table.NestedJoin - заменяем название таблицы Пользовательский1 на имя шага Таблица рангов, где мы ранее получили нашу сгруппированную таблицу с позициями в топе для каждого товара:
Теперь можно развернуть вложенные таблицы с помощью кнопки с двойными стрелками в шапке столбца Пользовательский1 и извлечь из вложенных таблиц столбец Ранг - получим колонку, где напротив каждой сделки будет стоять либо номер в топе, либо слово "Другие":
Шаг 4. Строим сводную таблицу
Осталось построить сводную таблицу по полученным данным. Для этого идём на вкладку Главная - Закрыть и загрузить - Закрыть и загрузить в... (Home - Close&Load - Close&Load to...) и выбираем в открывшемся окне опцию Отчет сводной таблицы (Pivot table):
Если в вашей версии Excel такой опции ещё нет (она появилась сравнительно недавно), то можно выбрать вместо неё вариант Только создать подключение (Only create connection), а затем команды Вставка - Сводная таблица - Использовать внешний источник данных - кнопка Выбрать подключение (Insert - Pivot table - Use external data source - Choose connection).
В созданной сводной таблице закидываем поля Ранг и Наименование в область строк, а Стоимость в область значений и немного "допиливаем" внешний вид:
- На вкладке Конструктор выбираем Макет отчёта - Показать в табличной форме (Design - Report Layout - Show in tabular form).
- Убираем промежуточные итоги - там же с помощью кнопки Промежуточные итоги (Subtotals).
- Сортируем сводную по возрастанию по столбцу Ранг и по убыванию по столбцу Стоимость кнопками сортировки на вкладке Данные (Data).
- Добавляем гистограммы условного форматирования на вкладке Данные - Условное форматирование - Гистограммы (Home - Conditional Formatting - Data bars).
Теперь можно смело менять значение N в жёлтой ячейке и жать кнопку Обновить всё на вкладке Данные (Data - Refresh All) - наша сводная таблица построенная по запросу Power Query будет отображать нужное количество элементов в топе, группируя при этом остальных в блок Другие.
Ссылки по теме
- План-факт анализ в сводной таблице с помощью Power Pivot и Power Query
- Сводная таблица с текстом в значениях
- Сводная таблица по Модели Данных
подскажите пожалуйста, как сделать версию этого отчета с суммой по "Другие", и без вывода списка по Другим.
еще раз спасибо