Динамический "Топ-N + Другие" в сводной таблице


В аналитических отчетах и дашбордах весьма часто встречается потребность вывести итоговые значения в формате сводной таблицы вида "Топ-N + другие":

Финальная сводная с Топ-N

Причем это самое N (на картинке равное 5) хотелось бы иметь динамическим, т.е. менять на 3-5-10... и т.д. и чтобы сводная таблица при этом "на лету" пересчитывалась, отображая топ заданного размера и собирая все остальные меньшие значения в группу "Другие".

Сводные таблицы Excel так, к сожалению, пока не умеют, но если доработать исходные данные с помощью запроса Power Query, то задача решается относительно легко.

В качестве исходных данных возьмем вот такую несложную таблицу заказов:

Исходные данные

Здесь всё просто: одна строка - один заказ, а в ячейке H2 у нас будет храниться значение, задающее количество элементов в топе (остальные будут сгруппированы под именем Другие, например).

Шаг 1. Добавляем параметр N в Power Query

Первое, что нам нужно будет сделать - это добавить в Power Query значение N (жёлтой ячейки) как параметра, с тем, чтобы использовать его в дальнейшем в запросе анализа данных из большой таблицы заказов. Для этого:

  1. Выделяем жёлтую ячейку H2
  2. Даём ей имя в левой части строки формул - стираем H2, вводим имя (например Топ) и жмём Enter. Или же можно воспользоваться Диспетчером Имен на вкладке Формулы (Formulas - Name Manager).
  3. Загружаем созданный именованный диапазон в Power Query с помощью команды Данные - Из таблицы/диапазона (Data - From Table / Range).
  4. В редакторе запросов Power Query удаляем в правой панели все шаги кроме первого, а затем щёлкаем правой кнопкой мыши по ячейке со значением N и выбираем команду Детализация (Drill down), чтобы получить на выходе не таблицу, состоящую из одной строки и одного столбца, а числовое значение нашего параметра N.
  5. Чтобы вернуться в Excel выбираем на вкладке Главная команду Закрыть и загрузить - Закрыть и загрузить в... (Home - Close & Load - Close & Load to...) и затем вариант Только создать подключение (Only create connection).

В итоге справа в панели запросов должен появиться наш созданный числовой параметр:

Созданный параметр N

Шаг 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):

Условный столбец

Само собой, нам нужно, чтобы в этой проверке сравнение происходило не с жёстко прописанной константой, а с нашим параметром Топ, который мы берём из жёлтой ячейки. Так что подменяем в строке формул пятёрку на имя Топ:

Заменяем константу N на параметр

Полученная таблица нам потребуется чуть позже, так что имеет смысл для удобства переименовать этот шаг (он должен называться Условный столбец добавлен), чтобы легко вернуться к нему в будущем. Для этого можно щёлкнуть по шагу в правой панели правой кнопкой мыши и выбрать команду Переименовать (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).

В созданной сводной таблице закидываем поля Ранг и Наименование в область строк, а Стоимость в область значений и немного "допиливаем" внешний вид:

  1. На вкладке Конструктор выбираем Макет отчёта - Показать в табличной форме (Design - Report Layout - Show in tabular form).
  2. Убираем промежуточные итоги - там же с помощью кнопки Промежуточные итоги (Subtotals).
  3. Сортируем сводную по возрастанию по столбцу Ранг и по убыванию по столбцу Стоимость кнопками сортировки на вкладке Данные (Data).
  4. Добавляем гистограммы условного форматирования на вкладке Данные - Условное форматирование - Гистограммы (Home - Conditional Formatting - Data bars).
В итоге должно получиться примерно так:

Финальная сводная с Топ-N

Теперь можно смело менять значение N в жёлтой ячейке и жать кнопку Обновить всё на вкладке Данные (Data - Refresh All) - наша сводная таблица построенная по запросу Power Query будет отображать нужное количество элементов в топе, группируя при этом остальных в блок Другие.

Ссылки по теме

 


16.08.2022 14:00:10
Спасибо большое Николай. По такой же логике Топ И и Боттом И в одной сводке как можно сделать?
23.08.2022 14:07:43
Николай, добрый день! Метод просто супер! Давно уже искал, но ничего путного не находил. Единственный недостаток - невозможно его применить в модели, когда даты в связанной таблице и необходимо сделать срез по годам. Ведь рейтинг из года в год меняется.
Наверх