Сводная по таблице с многострочной шапкой

Предположим, что в качестве источника данных вам досталась вот такая "красота":

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

Приятная на вид таблица, с которой, тем не менее, страшно неудобно работать. Представьте, например, что вам нужно по этой таблице:

  • Вычислить отличие факта от плана по каждому товару за каждый год.
  • Сравнить между собой одинаковые кварталы за разные годы.
  • Посчитать суммарный или средний факт по каждому товару.
  • Сравнить продажи апельсинового сока разных наименований.

Любое из перечисленных выше действий будет требовать кардинального перелопачивания таблицы, ввода большого количества формул, ручного их копирования и т.п.

Поэтому (капитан Очевидность!) это не нужно делать формулами. Гораздо проще и быстрее реализовать подобное будет с помощью сводных таблиц - гибкого, мощного и намного более подходящего инструмента для такой задачи.

Одна проблема - в Microsoft Excel нельзя построить сводную таблицу, если у неё многострочная шапка, а именно это мы и имеем в нашем примере.

Нас выручит Power Query - мощная надстройка, встроенная в Excel, начиная с 2016-й версии (а для Excel 2010 и 2013 её можно бесплатно скачать с сайта Microsoft).

Шаг 1. Грузим данные в Power Query

Самый простой путь в нашем случае - это выделить весь диапазон с данными (начиная с ячейки B2 и до конца таблицы) и дать ему имя на вкладке Формулы - Задать имя (Formulas - Create name). Давайте оригинально назовём его Данные, например:

Создаем именованный диапазон

Затем, проверив, что выделена вся таблица, выберем на вкладке Данные (или на вкладке Power Query, если вы установили её как отдельную надстройку на Excel 2010-2013), команду Из таблицы / Диапазона (From Table/Range). Наша "красивая" таблица загрузится в редактор запросов Power Query:

Данные загружены в Power Query

Обратите внимание, что объединенные ячейки разъединились, а образовавшиеся в результате пустые ячейки теперь заполнены null - специальным словом, обозначающим в Power Query "пустоту" или отсутствие чего-либо.

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

Удаляем лишний шаг

Шаг 2. Преобразуем таблицу в плоскую

Дальнейшие преобразования будут состоять из нескольких операций:

  1. Во-первых, давайте избавимся от лишних пустых строк (см. строки 9 или 26, например) с помощью команды Главная - Удалить строки - Удалить пустые строки (Home - Remove rows - Remove empty rows):

    Удаляем пустые строки

    При необходимости, можно избавиться и от любых других лишних строк (например, строк с итогами и т.п.) с помощью фильтра, сняв в нём соответствующую галочку.
  2. Чтобы заполнить пустоты с null, выделим два первых столбца (удерживая клавишу Ctrl) и выберем на вкладке Преобразование команду Заполнить - Вниз (Transform - Fill - Down):

    Заполняем вниз

  3. Теперь нам нужно временно склеить три первых столбца в один. Для этого выделим их и выберем команду Преобразование - Столбец "Текст" - Объединить столбцы (Transform - Merge Columns), указав в качестве временного разделителя любой символ, который не встречается в наших данных, например точку с запятой:

    Склеиваем столбцы

  4. Теперь выполним транспонирование таблицы там же на вкладке Преобразование, нажав кнопку Транспонировать в левом верхнем углу (Transform - Transpose). Строки и столбцы в наших данных поменяются местами:

    Транспонируем

  5. Удалим уже знакомым способом пустые столбцы, превратившиеся после транспонирования в пустые строки, командой Главная - Удалить строки - Удалить пустые строки (Home - Remove rows - Remove empty rows).
  6. Заполним годами и кварталами пустые ячейки в первых двух столбцах командой Преобразование - Заполнить - Вниз (Transform - Fill - Down), как мы уже делали ранее.
  7. Поднимем текст из первой строки в шапку, выбрав на вкладке Главная команду Использовать первую строку в качестве заголовков (Home - Use first row as headers). Склеенные через точку с запятой категория, наименование и вид заменят стандартные Column1,2,3...

    Поднимаем заголовки

  8. Теперь выделим первых три столбца и, щёлкнув по заголовку любого из них, выберем команду Отменить свёртывание других столбцов (Unpivot other columns). Все столбцы кроме выделенных превратятся в новые строки, где название столбца и числовое значение попадут в разные ячейки:

    Отмена свертывания

  9. Осталось разделить слипшиеся категорию, наименование и вид в столбце Атрибут с помощью команды Главная - Разделить столбец - По разделителю (Home - Split column - By delimiter):

    Разделяем слипшийся столбец

  10. Ну, и для пущей красоты переименуем получившиеся столбцы двойным щелчком мыши по заголовкам:

    Переименовываем столбцы
На этом приведение исходных данных в порядок успешно завершено и можно переходить к построение сводной таблицы.

Шаг 3. Строим сводную

На Главной вкладке Power Query выберем команду Закрыть и загрузить - Закрыть и загрузить в... (Home - Close & Load - Close & Load to...):

Выгружаем данные в Excel

В следующем окне можно пойти двумя путями:

Выбор места для выгрузки

Если у вас есть в этом окне опция Отчет сводной таблицы (Pivot Table Report) - смело выбирайте его и жмите на ОК. На новом листе будет создана сводная таблица по нашим данным из Power Query.

Если у вас старая версия Excel, то такой опции в этом окне может не быть. Тогда придется "идти в обход":

  1. Выбираем вариант Только создать подключение (Create connection only),
  2. После возвращения в Excel на вкладке Вставка жмём кнопку Сводная таблица (Insert - Pivot Table)
  3. В окне создания сводной выбираем опцию Использовать внешний источник данных (Use external data source).
  4. В открывшемся окне выбираем наше подключение из Power Query:

Строим сводную таблицу по подключению из Power Query

И в том, и в другом случае результатом будет сводная, построенная по нашей исходной красивой, но неудобной таблице:

Готовая сводная таблица

Теперь наши данные можно крутить-вертеть-анализировать любым нужным образом легко и красиво :)

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




09.11.2020 14:53:28
Здравствуйте, Николай.
Подскажите, пожалуйста, для каких надобностей Вы предполагаете использовать данные из столбца Общий Итог (План + Факт)?
Можно ли вместо него вывести в Сводной таблице вычисляемое поле доля Факта в Плане или Перевыполнение/Недовыполнение Плана?
13.11.2020 13:33:58
В PQ нужно добавить столбец сведения (план/факт), столбец значений - значения. Тем самым появится возможность добавить вычисляемый столбец в сводной таблице.
12.11.2020 07:08:30
Добрый день. Подскажите пожалуйста. Для чего вначале необходимо именовать диапазон? и будет ли автоматически обноляться данные если добавить еще строки в первоначальную "красивую" таблицу?
18.11.2020 15:46:08
Неименованный диапазон в Power Query не загрузить.
Другой вариант - преобразовать "красивую" исходную таблицу в динамическую ("умную") с помощью сочетания клавиш Ctrl+T. В этом случае и новые добавленные впоследствии строки и столбцы автоматом подтянутся и в Power Query (с именованным диапазоном так не будет).
19.11.2020 08:33:46
спасибо!
попытался воспользоваться в надстройке PLEX функцией Динамический диапазон, но Power Query его не видит.
Наверх