Excel/PowerQuery/VBA, Анализ данных

Быстро и просто готовим качественный отчет

Время прочтения: 4 мин.

Для безошибочного построения сводного отчета необходимо учесть следующее:

  • все выгружаемые файлы должны храниться в одной папке и иметь аналогичную структуру таблиц (при этом количество столбцов может отличаться);
  • название листа, который содержит необходимые нам данные в виде таблицы, должно быть одинаково во всех файлах (например, как в моем случае, Лист 1).

Если мы регулярно осуществляем выгрузки из какой-то базы или АС, то файлы по умолчанию содержат идентичные названия листов, поэтому сложностей с этим возникнуть не должно.

Итак, открываем excel, на вкладке «Данные» нажимаем «Создать запрос – Из файла – Из книги»

Находим расположение выгруженных файлов и импортируем любую из них. В появившемся окне нажимаем «Изменить».

Открывается окно редактора Power Query.

Чтобы импортировать данные из всех остальных файлов необходимо создать функцию. Для этого через кнопку «Изменить» переходим в «Просмотр – Расширенный редактор».

В появившемся окне необходимо внести изменения в запрос.

В начале запроса и в скобках, вместо ранее указанного пути расположения файлов, добавляем команду (filename) и нажимаем «готово».

Возвращаемся на главную вкладку, нажимаем «закрыть и загрузить» (параметры запроса в появившемся поле вводить не обязательно).

После сохранения мы снова оказываемся в нашем изначально открытом файле excel, из которого мы делали запрос, и здесь, справа мы уже можем увидеть созданную нами функцию (я ее для удобства назвала «SvodTab»).

Теперь снова создаем запрос, но уже к папке (не к книге)

Подгружается список всех файлов из папки.

Нажимаем «изменить» и снова попадаем в окно редактора запроса Power Query.

Добавляем пользовательский столбец и прописываем формулу:

=SvodTab([Folder Path]&{Name])

Обратите внимание, что в скобках указаны названия столбцов, обозначающие путь к вашим таблицам. Эти данные в формуле постоянны. Измениться может только наименование созданной нами ранее функции, которое указывается после знака =.

После выполнения запроса появляется еще один пользовательский столбец.

Раскрываем его содержимое и получаем данные из всех таблиц, находящихся в папке.

Теперь, при необходимости, можно переименовать, а также удалить ненужные столбцы, оставив только ту информацию, которая будет необходима для отражения в сводном отчете.

На вкладке Данные нажимаем «закрыть и загрузить» и получаем сводную таблицу в формате excel.

Обязательно сохраните данный сводный файл отчет, потому что в дальнейшем, при последующих выгрузках отчетных данных и сохранении их в той же папке, достаточно будет открыть этот сводный файл и на вкладке «Данные» нажать «Обновить все».

Все данные будут автоматически обновляться и выстраиваться в ваш сводный отчет, что существенно сэкономит ваше время и убережет от возможных ошибок.

Советуем почитать