Как из оборотно-сальдовой ведомости сделать сводную таблицу при помощи Power Query

Если еще не работали с надстройкой PowerQuery и не знаете что это такое, то для начала лучше ознакомиться со статьей: Power Query - что такое и почему её необходимо использовать в работе?

Power Query – специальная надстройка для для Excel 2010 и выше. Начиная с версии Excel 2016 эта надстройка встроена в Excel и все команды расположены на вкладке Данные -группа Скачать и преобразовать. Для 2010 и 2013 после подключения надстройки появится новая вкладка - PowerQuery.

---

В качестве знакомства с некоторым функционалом и работой с Power Query возьмем печально известный многим отчет – Оборотно-Сальдовая ведомость. Выглядит он примерно так:

А нам надо все это привести в такой вид, чтобы можно было создать сводную таблицу и уже в этой сводной таблице крутить и вертеть данные в любом ракурсе. Т.е. на выходе надо сделать что-то вроде этого:

А из этого вот такая сводная таблица:

Проблемы видны сразу: многоуровневая шапка, несколько столбцов значений. Напрямую из такой таблицы не построить нормальную сводную, в которой можно нормально просматривать и сравнивать данные. Поэтому в любом случае необходимо эту таблицу преобразовывать таким образом, как показано на втором скрине "Результат - плоская таблица".
Если делать подобное вручную – то уйдет как минимум час, при этом время будет напрямую зависеть от кол-ва строк. Если же делать это через Power Query – это займет максимум полчаса независимо от количества строк. И то только в первый раз – познакомившись с надстройкой поближе и 10 минут будет за глаза для данной операции. Я постараюсь пошагово расписать весь процесс с иллюстрациями своих действий. Чтобы удобно было повторять действия из статьи можно скачать файл:

  Пример работы с Power Query (62,9 КиБ, 3 142 скачиваний)

В приложенном есть лист, еще не преобразованной ОСВ. Он называется "Не обработанная ОСВ". Так же есть лист с уже подготовленной ОСВ(лист "Оборотно-сальдовая ведомость"), а так же с готовым запросом Power Query, созданной на его основе умной таблицей(лист "Лист2") и сводной таблицей(лист "Лист3").

Преобразовать подобную таблицу в "нормальную" можно как минимум двумя вариантами(из тех, что можно через интерфейс PowerQuery наколдовать):

Вариант 1: попроще, но не универсальный

Вариант 2: чуть сложнее в освоении, но подобный принцип можно будет в дальнейшем применить к любой таблице с многоуровневой шапкой

Оба варианта рассмотрим на примере оборотно-сальдовой ведомости, поэтому в Варианте2 некоторые шаги по преобразованию исходной таблицы не описаны.

---

Вариант 1
Сначала нам надо подготовить саму оборотно-сальдовую ведомость(в дальнейшем я буду называть её кратко – ОСВ). Для этого выделяем ОСВ от заголовков и до конца, без строки итогов:

Переходим на вкладку Вставка и выбираем – Таблица. Снимаем галку с пункта "Таблица с заголовками" -Ок:

Сразу переходим на вкладку Данные(или на вкладку Power Query) -группа Загрузить и преобразовать -Из таблицы

Появится окно редактора запросов:

Теперь производим преобразования данных, для приведения этой таблицы в нормальную, так называемую "плоскую". Здесь есть два варианта - какой из них использовать выбирать вам:
Вариант 1 (проще в понимании логики, но менее универсальный для таблиц с добавляющимися столбцами)

1. Вкладка Главная –Использовать первую строку в качестве заголовков(на рисунке выше выделено красным). Запоминаем где у нас Сальдо на начало периода, где Обороты за период, где Сальдо на конец периода. Это еще пригодится. Так же это можно сделать с вкладки Преобразование -Таблица -Использовать первую строку в качестве заголовков
2. Еще раз повышаем заголовки(Главная –Использовать первую строку в качестве заголовков) и теперь у нас вместо Оборотов за период и Сальдо только Дебет и Кредит. Поэтому вспоминаем изначальную структуру и к каждому Дебет и Кредит дописываем через нижнее подчеркивание Тип операции: Сальдо на начало периода, Обороты за период и Сальдо на конец периода:
   
3. Теперь выделяем первые 2 столбца(Номер и Наименование), переходим на вкладку Преобразование -Отменить свертывание других столбцов:
   
   Здесь не мешает пояснить, что делает вообще означает данная команда. Несмотря на странное название, делает она очень нужную вещь: она транспонирует все столбцы, отличные от выделенных, преобразуя их в два столбца: в одном будут записаны имена заголовков, в другом значение каждой строки для этого заголовка:
   
4. Теперь осталось только отделить Типы от Дебета и Кредита. Для этого выделяем столбец Атрибут -правая кнопка мыши -Разделить столбец -По разделителю. В появившемся окне в выпадающем списке "Выберите или введите разделитель" выбираем Пользовательский и записываем в появившееся поле нижнее подчеркивание:
   
5. Переименовываем столбцы, чтобы получилось что-то вроде этого(главное, чтобы было понятно что в каком столбце):
   
6. Теперь выделяем столбец с суммой, переходим на вкладку Преобразование и находим там пункт Тип данных. Указываем тип Десятичное число:
   

Примечание: если на каком-то этапе что-то сделали неправильно – не надо все переделывать и не спешите закрывать окно. В правой части окна запросов есть история проделанных изменений – Примененные шаги. В этом окне можно удалить один из шагов:

Только удалять надо тоже аккуратно – если пока еще не очень хорошо ориентируетесь в Power Query – лучше удалять только последний шаг, который повлек ошибку. Если удалить шаг из середины – это может повлечь ошибки в дальнейших шагах, поэтому я не рекомендую удалять шаги из середины истории.

---

Вариант 2(чуть сложнее в понимании, чем Вариант 1, но этот принцип подойдет для преобразования любых таблиц и будет корректно работать даже если мы добавим столбцы в исходную таблицу)
я тут уже покажу минимум скринов, т.к. большинство уже описано в первом варианте
После того как загрузили таблицу в модель:

1. Вкладка Преобразование -Таблица -Транспонировать
   
2. Выделяем первый столбец – вкладка Преобразование -Заполнить- Вниз
   
3. Выделяем два первых столбца -правая кнопка мыши -Объединить столбцы. Разделитель – точка с запятой
   
4. Транспонируем таблицу обратно: вкладка Преобразование -Таблица -Транспонировать
5. вкладка Преобразование -Таблица -Использовать первую строку в качестве заголовков
6. Выделяем первые 2 столбца -вкладка Преобразование -Отменить свертывание других столбцов
7. Выделяем столбец Атрибут(в котором у нас "Сальдо на начало периода;Дебет" и пр.) -правая кнопка мыши -Разделить столбец -По разделителю -Точка с запятой
   
8. Переименовываем столбцы в нормальные названия, т.к. в некоторых у нас Атрибут.1, Атрибут.2 и Значение
9. Меняем тип данных для столбца с Суммой: вкладка Преобразование -Тип данных -Десятичное число

На этом преобразования завершены, переходим на вкладку Главная -Закрыть и загрузить. В исходной книге будет создан новый лист, на котором будет создана умная таблица из преобразованных данных. Теперь на основании этой умной таблицы мы можем создать сводную таблицу(выделяем любую ячейку внутри таблицы -вкладка Вставка -Сводная таблица) или производить другие действия для анализа данных.
При этом очень важный нюанс: не придется делать подобные преобразования каждый раз при изменении данных – если мы внесем изменения в исходную таблицу ОСВ, то для получения актуальных данных и преобразования достаточно будет обновить результирующую таблицу(выделять любую ячейку результирующей умной таблицы -вкладка Конструктор -Обновить). Даже если добавить еще пару столбцов с Дебетом и Кредитом(здесь это не уместно, но в других отчетах с подобной структурой это вполне вероятно) и обновить - то эти столбцы так же будут обработаны и включены в итоговую таблицу.
Если же надо применить все тоже самое для другой таблицы – то выделяем любую ячейку результирующей умной таблицы -вкладка Запрос -Изменить. Находим самый первый шаг в Примененных шагах(как правило он называется Источник – выделяем и в строке формул меняем имя таблицы на имя таблицы нужной таблицы):

---

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

 

Также см.:
Относительный путь к данным PowerQuery
План-фактный анализ в Excel при помощи Power Query