Сводная по таблице с многострочной шапкой
Предположим, что в качестве источника данных вам досталась вот такая "красота":
Приятная на вид таблица, с которой, тем не менее, страшно неудобно работать. Представьте, например, что вам нужно по этой таблице:
- Вычислить отличие факта от плана по каждому товару за каждый год.
- Сравнить между собой одинаковые кварталы за разные годы.
- Посчитать суммарный или средний факт по каждому товару.
- Сравнить продажи апельсинового сока разных наименований.
Любое из перечисленных выше действий будет требовать кардинального перелопачивания таблицы, ввода большого количества формул, ручного их копирования и т.п.
Поэтому (капитан Очевидность!) это не нужно делать формулами. Гораздо проще и быстрее реализовать подобное будет с помощью сводных таблиц - гибкого, мощного и намного более подходящего инструмента для такой задачи.
Одна проблема - в 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:
Обратите внимание, что объединенные ячейки разъединились, а образовавшиеся в результате пустые ячейки теперь заполнены null - специальным словом, обозначающим в Power Query "пустоту" или отсутствие чего-либо.
Также Power Query попытался автоматически распознать типы данных в каждом столбце, добавив в правой панели Примененные шаги (Applied Steps) шаг Измененный тип (Changed Type). Делать такое пока рановато, так что этот шаг можно смело удалить, щёлкнув по крестику слева от его названия:
Шаг 2. Преобразуем таблицу в плоскую
Дальнейшие преобразования будут состоять из нескольких операций:
- Во-первых, давайте избавимся от лишних пустых строк (см. строки 9 или 26, например) с помощью команды Главная - Удалить строки - Удалить пустые строки (Home - Remove rows - Remove empty rows):
При необходимости, можно избавиться и от любых других лишних строк (например, строк с итогами и т.п.) с помощью фильтра, сняв в нём соответствующую галочку.
- Чтобы заполнить пустоты с null, выделим два первых столбца (удерживая клавишу Ctrl) и выберем на вкладке Преобразование команду Заполнить - Вниз (Transform - Fill - Down):
- Теперь нам нужно временно склеить три первых столбца в один. Для этого выделим их и выберем команду Преобразование - Столбец "Текст" - Объединить столбцы (Transform - Merge Columns), указав в качестве временного разделителя любой символ, который не встречается в наших данных, например точку с запятой:
- Теперь выполним транспонирование таблицы там же на вкладке Преобразование, нажав кнопку Транспонировать в левом верхнем углу (Transform - Transpose). Строки и столбцы в наших данных поменяются местами:
- Удалим уже знакомым способом пустые столбцы, превратившиеся после транспонирования в пустые строки, командой Главная - Удалить строки - Удалить пустые строки (Home - Remove rows - Remove empty rows).
- Заполним годами и кварталами пустые ячейки в первых двух столбцах командой Преобразование - Заполнить - Вниз (Transform - Fill - Down), как мы уже делали ранее.
- Поднимем текст из первой строки в шапку, выбрав на вкладке Главная команду Использовать первую строку в качестве заголовков (Home - Use first row as headers). Склеенные через точку с запятой категория, наименование и вид заменят стандартные Column1,2,3...
- Теперь выделим первых три столбца и, щёлкнув по заголовку любого из них, выберем команду Отменить свёртывание других столбцов (Unpivot other columns). Все столбцы кроме выделенных превратятся в новые строки, где название столбца и числовое значение попадут в разные ячейки:
- Осталось разделить слипшиеся категорию, наименование и вид в столбце Атрибут с помощью команды Главная - Разделить столбец - По разделителю (Home - Split column - By delimiter):
- Ну, и для пущей красоты переименуем получившиеся столбцы двойным щелчком мыши по заголовкам:
Шаг 3. Строим сводную
На Главной вкладке Power Query выберем команду Закрыть и загрузить - Закрыть и загрузить в... (Home - Close & Load - Close & Load to...):
В следующем окне можно пойти двумя путями:
Если у вас есть в этом окне опция Отчет сводной таблицы (Pivot Table Report) - смело выбирайте его и жмите на ОК. На новом листе будет создана сводная таблица по нашим данным из Power Query.
Если у вас старая версия Excel, то такой опции в этом окне может не быть. Тогда придется "идти в обход":
- Выбираем вариант Только создать подключение (Create connection only),
- После возвращения в Excel на вкладке Вставка жмём кнопку Сводная таблица (Insert - Pivot Table)
- В окне создания сводной выбираем опцию Использовать внешний источник данных (Use external data source).
- В открывшемся окне выбираем наше подключение из Power Query:
И в том, и в другом случае результатом будет сводная, построенная по нашей исходной красивой, но неудобной таблице:
Теперь наши данные можно крутить-вертеть-анализировать любым нужным образом легко и красиво :)
Ссылки по теме
- Редизайн кросс-таблицы в плоскую
- Сводная таблица по нескольким диапазонам данных
- Настройка вычислений в сводной таблице
Подскажите, пожалуйста, для каких надобностей Вы предполагаете использовать данные из столбца Общий Итог (План + Факт)?
Можно ли вместо него вывести в Сводной таблице вычисляемое поле доля Факта в Плане или Перевыполнение/Недовыполнение Плана?
Другой вариант - преобразовать "красивую" исходную таблицу в динамическую ("умную") с помощью сочетания клавиш Ctrl+T. В этом случае и новые добавленные впоследствии строки и столбцы автоматом подтянутся и в Power Query (с именованным диапазоном так не будет).
попытался воспользоваться в надстройке PLEX функцией Динамический диапазон, но Power Query его не видит.
Попробуйте использовать другой разделитель.
Не критики ради а пожелания для на будущее.
Считаю не стоит перегружать ролики.
Суть этого ролика в примере нормализации кривой таблицы в нормальную.
Нормализовали, вывели в таблицу на лист и на этом стоп.
Что делать с данными дальше и как работать вообще со сводными таблицами это уже совсем другая история...