Автоматизация работы с данными в Excel через Power Query

Power Query в Excel позволяет импортировать данные из файлов, баз данных и веб-источников, автоматически очищать их от ошибок и объединять несколько таблиц в одну без использования макросов VBA. Инструмент встроен в Excel 2016 и новее (вкладка «Данные» → «Получить и преобразовать данные») и сохраняет все шаги обработки, которые можно обновлять одной кнопкой при изменении исходных файлов.

Это руководство описывает полный цикл работы: от подключения источника до выгрузки готового отчета.

Что такое Power Query и когда его использовать

Power Query (в интерфейсе Excel — «Получить и преобразовать данные») — это ETL-инструмент (Extract, Transform, Load), который заменяет ручное копирование и формулы типа ВПР/VLOOKUP при работе с большими массивами информации.

Основные сценарии применения:

• Сбор данных из множества файлов: например, соединение 12 ежемесячных отчетов из разных Excel-файлов или CSV в одну сводную таблицу.
• Очистка «грязных» выгрузок: удаление лишних строк с итогами, исправление форматов дат, разделение ФИО по столбцам.
• Объединение справочников: присоединение названий товаров или имен клиентов к таблице продаж по уникальному ID.

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

Шаг 1: Импорт данных из файлов и папок

Процесс начинается с подключения к источнику. Интерфейс находится на вкладке Данные.

Импорт одного файла

1. Нажмите Данные → Получить данные → Из файла → Из книги Excel (или «Из текста/CSV»).
2. Выберите файл. В окне предварительного просмотра убедитесь, что данные отображаются корректно.
3. Нажмите кнопку Преобразовать данные (не «Загрузить», если планируете очистку). Откроется редактор Power Query.

Импорт из папки (массовая загрузка)

Если у вас есть папка с однотипными файлами (например, Отчет_Январь.xlsx, Отчет_Февраль.xlsx):

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

Шаг 2: Очистка и подготовка данных

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

Базовая гигиена данных

1. Проверка заголовков: Если первая строка содержит данные, а не названия столбцов, нажмите Главная → Использовать первую строку как заголовки.
2. Удаление лишнего:
   • Выделите ненужные столбцы → Правая кнопка мыши → Удалить другие столбцы (безопаснее, чем просто «Удалить», так как при появлении новых столбцов в источнике они не сломают логику).
   • Для удаления пустых строк: Главная → Удалить строки → Удалить пустые строки.
3. Типы данных: Обязательно задайте правильный тип для каждого столбца, кликнув на иконку слева от названия (ABC — текст, 123 — число, календарь — дата). Неправильный тип (например, число как текст) сделает невозможным математические операции и сводные таблицы.

Работа с текстом и дублями

• Лишние пробелы: Выделите текстовые столбцы → Преобразование → Формат → Очистка (удаляет непечатные символы) или Усечение (удаляет пробелы по краям).
• Удаление дубликатов: Выделите столбцы, определяющие уникальность строки (например, «Номер заказа») → Главная → Удалить строки → Удалить дубликаты.

Шаг 3: Объединение таблиц (Append и Merge)

В Power Query есть два принципиально разных способа соединения таблиц.

Append (Добавить/Конкатенация)

Используется, когда нужно поставить таблицы друг под друга (увеличить количество строк). Структура столбцов должна быть схожей.

1. На вкладке Главная нажмите Объединить запросы → Добавить запросы (Append Queries).
2. Выберите режим:
   • Две таблицы: для простого сложения.
   • Три и более таблиц: если нужно собрать данные из множества источников.
3. Переместите нужные таблицы в правую часть окна и нажмите ОК.

Merge (Объединение/JOIN)

Используется, когда нужно «подтянуть» данные из другой таблицы по совпадению в одном столбце (аналог ВПР/XLOOKUP).

1. Нажмите Главная → Объединить запросы → Объединить запросы (Merge Queries).
2. В первом окне выберите основную таблицу, во втором — справочник.
3. Выделите мышью столбец-ключ в первой таблице и соответствующий столбец во второй (они подсветятся серым).
4. Выберите Тип соединения:
   • Левое внешнее (Left Outer): Все строки из первой таблицы + совпадения из второй (самый частый вариант).
   • Внутреннее (Inner): Только строки, где есть совпадение в обеих таблицах.
5. После нажатия ОК появится новый столбец Table. Нажмите на значок развертывания (две стрелочки) в заголовке этого столбца и выберите только нужные поля (снимите галочку «Использовать исходное имя столбца как префикс», чтобы имена были чище).

Выгрузка результата и настройка автообновления

Когда данные очищены и объединены:

1. Нажмите Главная → Закрыть и загрузить.
2. Данные выгрузятся на новый лист Excel в виде «Умной таблицы».

Как обновлять данные

При изменении исходных файлов (например, вы положили новый отчет в папку или исправили данные в источнике):

1. Откройте файл Excel с запросом.
2. Нажмите правой кнопкой мыши на любую ячейку полученной таблицы → Обновить.
3. Или используйте вкладку Данные → Обновить все.

Excel заново пройдет по всем записанным шагам и актуализирует результат.

Частые ошибки при работе с Power Query

FAQ: Ответы на популярные вопросы

Где найти Power Query в Excel 2013? В Excel 2013 и 2010 это отдельная надстройка, которую нужно скачать с сайта Microsoft. В Excel 2016, 2019, 2021 и Microsoft 365 инструмент встроен по умолчанию на вкладке «Данные».

Можно ли редактировать формулы Power Query? Да. В редакторе есть «Строка формул» (вкладка «Вид» → галочка «Строка формул»). Код пишется на языке M. Однако для большинства задач достаточно использования кнопок интерфейса.

Как изменить источник данных (путь к файлу)? В редакторе Power Query справа в панели «Запросы» найдите шаг «Источник» (Source). Нажмите на шестеренку рядом с ним или отредактируйте формулу, указав новый путь к файлу или папке.

Сохраняются ли шаги Power Query при отправке файла коллеге? Да, все шаги сохраняются внутри файла .xlsx или .xlsm. Коллеге не нужно ничего настраивать, но у него должен быть доступ к исходным данным (если они лежат на сетевом диске, путь должен быть доступен и ему).