Время прочтения: 4 мин.
Для безошибочного построения сводного отчета необходимо учесть следующее:
- все выгружаемые файлы должны храниться в одной папке и иметь аналогичную структуру таблиц (при этом количество столбцов может отличаться);
- название листа, который содержит необходимые нам данные в виде таблицы, должно быть одинаково во всех файлах (например, как в моем случае, Лист 1).
Если мы регулярно осуществляем выгрузки из какой-то базы или АС, то файлы по умолчанию содержат идентичные названия листов, поэтому сложностей с этим возникнуть не должно.
Итак, открываем excel, на вкладке «Данные» нажимаем «Создать запрос – Из файла – Из книги»
Находим расположение выгруженных файлов и импортируем любую из них. В появившемся окне нажимаем «Изменить».
Открывается окно редактора Power Query.
Чтобы импортировать данные из всех остальных файлов необходимо создать функцию. Для этого через кнопку «Изменить» переходим в «Просмотр – Расширенный редактор».
В появившемся окне необходимо внести изменения в запрос.
В начале запроса и в скобках, вместо ранее указанного пути расположения файлов, добавляем команду (filename) и нажимаем «готово».
Возвращаемся на главную вкладку, нажимаем «закрыть и загрузить» (параметры запроса в появившемся поле вводить не обязательно).
После сохранения мы снова оказываемся в нашем изначально открытом файле excel, из которого мы делали запрос, и здесь, справа мы уже можем увидеть созданную нами функцию (я ее для удобства назвала «SvodTab»).
Теперь снова создаем запрос, но уже к папке (не к книге)
Подгружается список всех файлов из папки.
Нажимаем «изменить» и снова попадаем в окно редактора запроса Power Query.
Добавляем пользовательский столбец и прописываем формулу:
=SvodTab([Folder Path]&{Name])
Обратите внимание, что в скобках указаны названия столбцов, обозначающие путь к вашим таблицам. Эти данные в формуле постоянны. Измениться может только наименование созданной нами ранее функции, которое указывается после знака =.
После выполнения запроса появляется еще один пользовательский столбец.
Раскрываем его содержимое и получаем данные из всех таблиц, находящихся в папке.
Теперь, при необходимости, можно переименовать, а также удалить ненужные столбцы, оставив только ту информацию, которая будет необходима для отражения в сводном отчете.
На вкладке Данные нажимаем «закрыть и загрузить» и получаем сводную таблицу в формате excel.
Обязательно сохраните данный сводный файл отчет, потому что в дальнейшем, при последующих выгрузках отчетных данных и сохранении их в той же папке, достаточно будет открыть этот сводный файл и на вкладке «Данные» нажать «Обновить все».
Все данные будут автоматически обновляться и выстраиваться в ваш сводный отчет, что существенно сэкономит ваше время и убережет от возможных ошибок.