Как быстро собрать отчёт из нескольких Excel-файлов с помощью Power Query

Если вы каждый месяц вручную объединяете десятки однотипных таблиц из разных файлов — например, отчёты по филиалам, данные из нескольких бухгалтерий или логи систем, — то эта инструкция сэкономит вам часы работы. Power Query в Excel умеет подхватывать все файлы из папки и склеивать их в одну таблицу за пару кликов. Разберём конкретный пример: в папке лежат десятки .xlsx-файлов с одинаковой структурой, и нужно собрать их в единый отчёт.

Что такое Power Query и где его найти

Power Query — это встроенный в Excel инструмент для загрузки и трансформации данных. Он находится на вкладке Данные → Получить данные → Из файла → Из папки. В старых версиях Excel (2010–2013) он может потребоваться как отдельная надстройка, но в Excel 2016 и новее, а также в Microsoft 365 уже встроен в ленту.

Главная идея: вы один раз описываете шаги обработки данных, а потом просто обновляете загрузку — и Excel сам проходит по всем файлам, вытаскивает нужное и собирает в итоговую таблицу. Никакого копипаста, макросов и ручного переноса.

Пошаговая инструкция: от папки с файлами до готового отчёта

Шаг 1. Подготовьте файлы

Все файлы, которые нужно объединить, должны лежать в одной папке. Желательно, чтобы они были действительно однотипными — одинаковые столбцы, одинаковый порядок, одинаковые листы. Если где-то названия столбцов отличаются хотя бы на пробел или регистр, Power Query может выдать ошибку или создать дополнительные столбцы с пустыми значениями.

Создайте отдельную папку только для этих файлов. Не кладите туда ничего лишнего — каждый файл из папки будет обработан.

Шаг 2. Подключите папку в Excel

1. Откройте новый или существующий Excel-файл, куда хотите загрузить итоговую таблицу.
2. Перейдите на вкладку Данные → Получить данные → Из файла → Из папки.
3. Укажите путь к папке с файлами и нажмите ОК.
4. Появится окно со списком файлов. Нажмите Объединить → Объединить и загрузить.
5. Power Query покажет пример содержимого одного файла. Выберите нужный лист или таблицу и подтвердите.

После этого Excel сам пройдёт по всем файлам, считает их и склеит строки в одну общую таблицу. Обычно это занимает от пары секунд до минут в зависимости от объёма данных.

Шаг 3. Настройте трансформации в редакторе Power Query

Если данные после загрузки выглядят не идеально — лишние заголовки, пустые строки, неверные типы данных — не беда. Откройте редактор: на вкладке Главная нажмите Расширенный редактор или дважды кликните по запросу в области Запросы и подключения. Здесь можно:

• Удалить первые строки с заголовками, которые повторяются в каждом файле.
• Переименовать столбцы для единообразия.
• Изменить тип данных (текст, дата, число) — это критично для корректных вычислений и сортировки.
• Отфильтровать ненужные строки или оставить только определённые столбцы.
• Добавить столбец с именем исходного файла, чтобы видеть, откуда пришла каждая строка (Power Query добавляет его автоматически при объединении).

Каждое действие в редакторе записывается как шаг. Шаги можно переставлять, удалять и менять — это не макрос, а последовательность команд, которую легко корректировать.

Шаг 4. Загрузите результат на лист

Когда данные приведены в порядок, нажмите Закрыть и загрузить → Закрыть и загрузить в…. Выберите, куда вывести таблицу: на новый лист, в существующий диапазон или только как подключение (без отображения на листе). Рекомендую загружать на лист — так результат сразу виден.

Когда структура файлов отличается

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

Столбцы в разном порядке

Power Query сопоставляет столбцы по именам, а не по позициям. Так что если в одном файле «Сумма» стоит третьей, а в другом — пятой, проблем не будет. Главное — чтобы названия совпадали.

Разные названия столбцов

Если в части файлов написано «Сумма», а в части «Сумма, руб.», лучше привести к единому формату. В редакторе Power Query можно переименовать столбцы вручную или написать правило замены, чтобы автоматически приводить похожие названия к одному виду.

Данные на разных листах внутри файла

При подключении папки Power Query покажет список листов из первого файла. Выберите нужный лист, и он будет использоваться для всех файлов. Если листы называются по-разному, придётся либо переименовать их в файлах, либо настроить выбор через дополнительный шаг в редакторе — это чуть сложнее, но вполне реализуемо.

Сравнение подходов: что выбрать в зависимости от задачи

Подход	Когда подходит	Сложность настройки	Гибкость при изменении структуры
Power Query (Из папки)	Регулярные однотипные отчёты, одинаковая структура файлов	Низкая — 5–10 минут на первую настройку	Средняя — если добавляются новые столбцы, нужно обновить запрос
Сводная таблица с несколькими источниками	Разовые задачи, файлы с разной структурой	Средняя — нужно вручную указать диапазоны	Низкая — при изменении структуры всё ломается
Макрос VBA	Нужна нестандартная логика, обработка неструктурированных данных	Высокая — требует знания программирования	Высокая — можно заложить любую логику
Python / pandas	Большие объёмы данных (сотни тысяч строк), сложная трансформация	Высокая — нужен скрипт и окружение	Очень высокая — полный контроль над обработкой

Частые ошибки и как их избежать

1. Лишние файлы в папке

Если в папке лежит что-то помимо целевых отчётов — черновики, архивы, временные файлы — Power Query попытается обработать и их. Результат: ошибки, дубли или мусор в итоговой таблице. Решение: используйте отдельную папку только для конкретного объединения.

2. Повторяющиеся заголовки в файлах

Когда каждый файл содержит строку с названиями столбцов, и вы объединяете их как есть, в итоговой таблице появляются дублирующиеся заголовки. В редакторе Power Query удалите лишние строки через меню Удалить строки → Сверху (укажите количество строк с заголовком, которые нужно убрать в каждом файле).

3. Неверные типы данных

Числа, которые Excel считает текстом, не суммируются. Даты, распознанные как текст, не сортируются правильно. Всегда проверяйте типы столбцов после загрузки и при необходимости меняйте их через контекстное меню в редакторе Power Query.

4. Забывают про обновление

Power Query не обновляется автоматически при изменении исходных файлов. Чтобы пересобрать отчёт, нужно нажать Обновить всё на вкладке Данные. Если этого не делать, вы будете работать со старыми данными.

Практические рекомендации

• Сохраняйте файл с запросом отдельно. Это ваш шаблон. Исходные файлы могут обновляться, а шаблон — нет. Просто кладите новые файлы в папку и обновляйте запрос.
• Используйте столбец «Имя источника». Power Query добавляет его автоматически — там видно, из какого файла пришла строка. Это бесценно при поиске ошибок в исходных данных.
• Не обрабатывайте всё в одном запросе. Если данных много, разбейте на этапы: сначала объединение, потом фильтрация, потом группировка. Так проще отлаживать и быстрее работает.
• Задавайте понятные имена шагам в редакторе Power Query. Через месяц вы скажете себе спасибо, когда откроете запрос и сразу поймёте, что происходит.

Что делать, если Power Query не справляется

Power Query — мощный инструмент, но у него есть ограничения. Если файлы принципиально разные по структуре, если нужно обрабатывать неструктурированный текст или данные из сотен тысяч файлов — возможно, стоит посмотреть в сторону Python (библиотека pandas) или специализированных ETL-инструментов. Но для 80% типичных бизнес-задач — ежемесячные отчёты, выгрузки из систем, данные от контрагентов — Power Query более чем достаточно.

Итог

Power Query превращает рутину объединения Excel-файлов в пятиминутную задачу. Один раз настроили запрос — и потом только кладёте новые файлы в папку и жмёте «Обновить». Это не требует навыков программирования, работает в обычном Excel и реально экономит время. Если вы до сих пор копируете данные вручную — попробуйте этот подход прямо сегодня. Скорее всего, вы больше не вернётесь к старому способу.