Асимметричная сводная на наборах
Постановка задачи
Предположим, у нас есть таблица продаж с товарами, категориями, брендами, датами и суммами:
По ней нам необходимо построить следующий отчёт сводной таблицы:
Обратите особое внимание на её структуру:
- в строках нужно посчитать итоги по категориям;
- только по категории Спорт нужно показать три интересующих нас бренда - Peak, Pulse и Trail (исключив бренд Sprint);
- по столбцам идут годы, но за 2024 год нужен только итог по выручке, 2025 год нужно разбить на кварталы, а в 2026 году показать помесячно как выручку, так и количество проданных товаров.
То есть нам нужна асимметричная структура отчёта, где разные ветки отчёта раскрыты на разную глубину.
Что такое наборы и зачем они нужны в Excel
Сводные таблицы в Excel хороши всем, кроме одного маленького, но иногда очень неприятного ограничения: они всегда строят симметричную структуру. Если мы, например, закинули в строки поля Категория и Бренд, то Excel честно покажет бренды сразу для всех категорий. А если в столбцы добавили поля Год, Квартал и Месяц, то такая 3-уровневая структура тоже будет применяться ко всем годам одинаково. Без вариантов.
Но в реальной жизни часто хочется чуть большего. Например:
- по одной категории показать детализацию до товаров, а по остальным оставить только итоги;
- вывести в разных столбцах не одни и те же поля значений, а разные;
- сделать сводную, где в строках или столбцах идут не все элементы подряд, а только вручную выбранные комбинации;
- собрать «красивую» управленческую форму отчёта, а не типовую симметричную "простыню".
Обычными настройками сводной таблицы такую конструкцию собрать, к сожалению, не получится. Зато можно использовать малоизвестный инструмент Excel под названием наборы - это вручную заданный список элементов или их комбинаций, которые Excel должен показать в строках или столбцах.
Технически этот механизм пришёл из OLAP-кубов (это один из видов баз данных) и работает только со сводными, построенными по модели данных Power Pivot. То есть обычная классическая сводная по диапазону или «умной» таблице здесь не подойдёт - исходные данные нужно сначала обязательно добавить в модель данных и только потом строить по ним сводную с использованием наборов.
Давайте рассмотрим весь процесс пошагово.Шаг 1. Создаём симметричную сводную по модели данных
Сначала строим сводную таблицу по модели данных, используя команды Вставка - Сводная таблица (Insert - Pivot Table) и включив при этом флажок Добавить эти данные в модель данных (Add this data to Data Model):
Создаём сводную таблицу стандартным образом, перетаскивая нужные поля в строки, столбцы и значения. Раскрываем структуру примерно так, как она должна выглядеть в будущем отчёте:
- в область строк помещаем поля Категория и Бренд
- в область столбцов кладём Год, Квартал и Месяц
- в область значений - поля Выручка и Количество
На этом этапе сводная ещё будет огромной симметричной "простынёй" - не пугайтесь. Наша задача сейчас - просто подготовить заготовку.
Шаг 2. Создаём набор по строкам
Ставим курсор внутрь сводной таблицы и выбираем на вкладке Анализ сводной таблицы команду Поля, элементы и наборы - Создать набор на основе элементов строк (Pivot Table Analyze - Fields, Items and Sets - Create Set Based on Row Items). Excel откроет окно, где каждая строка будущего отчёта будет представлена отдельным элементом набора.
Здесь можно:
- удалить ненужные строки с помощью кнопки Удалить строку (Delete Row);
- поменять порядок элементов, двигая их выше-ниже с помощью кнопок со стрелками;
- добавить новые строки или скопировать существующие:
- переименовать набор понятным образом:
- если наборов у вас много, то поместить его в определённую папку для удобства.
По сути, мы вручную говорим Excel: «Покажи мне не всю стандартную иерархию, а вот именно эти строки и именно в таком порядке».
После нажатия ОК Excel заменит обычные поля в строках на созданный набор. В результате сводная станет асимметричной.
Шаг 3. Создаём набор по столбцам
Тот же трюк можно провернуть и со столбцами. Выбираем на вкладке Анализ сводной таблицы команду Поля, элементы и наборы - Создать набор на основе элементов столбцов (Pivot Table Analyze - Fields, Items and Sets - Create Set Based on Column Items) и задаём в диалоговом окне необходимую нам структуру вложенности, Причём не обязательно, чтобы все эти показатели имели одинаковую вложенность по годам, кварталам и месяцам.
После этого сводная примет требуемый вид и по колонкам. Останется лишь настроить мелочи, вроде форматирования или переименования полей для большей наглядности:
Несколько важных нюансов
- Наборы работают только в сводных таблицах по модели данных или OLAP-источнику. Если команда создания набора у вас неактивна, то, скорее всего, сводная построена по обычному диапазону.
- После создания набора структура строк или столбцов становится неавтоматической. Это удобно для красивых статических отчётов, но требует аккуратности: если в данных появятся новые категории, бренды или месяцы, то они не всегда автоматически окажутся в вашем наборе.
- В такой сводной таблице нет привычных кнопок фильтрации с треугольничками в шапке, т.к. наборы, по сути, уже являются пользовательскими фильтрами.
- Промежуточные итоги в такой сводной могут вести себя необычно - например, включать в себя удалённые из набора элементы. Чтобы это контролировать, щёлкните по сводной правой кнопкой мыши и выберите команду Параметры сводной таблицы - Итоги и фильтры (Pivot table properties - Totals and filters) и включите/выключите флажок Включить отобранные фильтром элементы в итоги по набору.
- Наборы можно в любой момент отредактировать через диспетчер наборов командой Поля, элементы и наборы - Управление наборами (Manage sets).
Ссылки по теме
- Преимущества сводной по модели данных
- План-факт анализ в сводной по модели данных с Power Pivot
- База данных в Excel с Power Pivot
