Открыть сервисСервис

Power Query в Excel

Power Query — это встроенное в Microsoft Excel средство для импорта, преобразования и объединения данных из различных внешних источников. Инструмент относится к классу ETL (Extract, Transform, Load) и позволяет автоматизировать подготовку данных без написания сложных формул и макросов на VBA. Power Query доступен в Excel начиная с версии 2016 в составе редакции «ProPlus» и входит в состав Microsoft 365; для более ранних версий Excel 2010 и 2013 доступен отдельный бесплатный надстройочный модуль.

Где найти Power Query в Excel

Расположение инструмента зависит от версии Excel и языка интерфейса. В русскоязычной версии Excel 2016 и Microsoft 365 Power Query находится на вкладке «Данные» в группе «Получение и преобразование данных». Основные команды:

  • «Получить данные» (в Excel 2016 — «Из файла», «Из базы данных», «Из онлайн-сервисов») — запуск мастера подключения к источнику.
  • «Из таблицы/диапазона» — запуск редактора Power Query для текущего листа.
  • «Из файла» — подключение к файлам Excel, CSV, XML, JSON, текстовым файлам.
  • «Из базы данных» — подключение к SQL Server, Access, Oracle и другим СУБД.
  • «Из онлайн-сервисов» — подключение к SharePoint, OData, веб-страницам.
  • «Запросы и подключения» — панель со списком всех созданных запросов.
  • «Изменить источник», «Изменить», «Обновить» — управление существующими запросами.

В Excel 2016 отдельной вкладки «Power Query» нет — инструмент интегрирован во вкладку «Данные». В Excel 2010 и 2013 после установки надстройки Power Query появляется отдельная вкладка «Power Query» на ленте.

Редактор запросов Power Query

При запуске Power Query открывается редактор запросов (Query Editor) — отдельное окно с четырьмя основными областями:

  1. Область запросов (слева) — список всех запросов текущей книги.
  2. Область предварительного просмотра (центр) — таблица с предпросмотром результата преобразований.
  3. Область шагов (справа, «Применённые шаги») — журнал всех выполненных операций, каждая из которых может быть отредактирована или удалена.
  4. Лента команд — инструменты преобразования: удаление столбцов, изменение типов данных, объединение таблиц, группировка, фильтрация, добавление вычисляемых столбцов.

Внутри редактора используется язык M (M Language) — функциональный язык запросов, разработанный Microsoft специально для Power Query. Каждый шаг преобразования записывается на языке M и отображается в столбце «Применённые шаги». Пользователь может редактировать M-код напрямую через кнопку «Дополнительно» в диалоговом окне параметров запроса.

Основные возможности

Power Query поддерживает:

  • Импорт данных из более чем 100 источников: файлы Excel, CSV, XML, JSON, папки, веб-страницы, базы данных SQL, SharePoint, OData, Google Таблицы (через ODBC), файлы PDF.
  • Очистку данных: удаление пустых строк и столбцов, замена значений, изменение регистра, удаление дубликатов, извлечение подстрок.
  • Преобразование структуры: транспонирование таблиц, объединение и разбиение столбцов, группировку и свёртку, иерархические структуры.
  • Объединение источников: соединение таблиц по ключевым полям (аналог SQL JOIN), объединение файлов из папки в единую таблицу.
  • Фильтрацию и сортировку данных на этапе загрузки.
  • Обновление данных: повторный запуск запроса с обновлением результата одной кнопкой «Обновить» (клавиша F9 или правый клик по запросу).

Загрузка результатов

После завершения преобразований результат запроса загружается в Excel одним из двух способов:

  • В таблицу — данные помещаются на отдельный лист в виде таблицы Excel, связанной с запросом.
  • В модель данных — данные загружаются в внутреннюю модель Power Pivot (Power BI), что позволяет строить сводные таблицы и отчёты на больших объёмах данных.

Кнопка «Закрыть и загрузить» на вкладке «Главная» редактора выполняет оба действия: закрывает редактор и помещает результат на лист или в модель.

Пример типичного сценария

Допустим, необходимо объединить данные из 12 ежемесячных CSV-файлов с продажами в единую таблицу. Без Power Query потребовалось бы копирование и ручное объединение. С Power Query:

  1. Вкладка «Данные» → «Получить данные» → «Из файла» → «Из папки».
  2. Указание пути к папке с файлами.
  3. В редакторе — объединение всех файлов одной кнопкой.
  4. Удаление лишних столбцов, изменение типов данных, фильтрация.
  5. «Закрыть и загрузить» — итоговая таблица готова.
  6. При появлении новых файлов в папке — «Обновить» обновит результат автоматически.

Системные требования и ограничения

Power Query встроен в 64-разрядные версии Excel; в 32-разрядных версиях работает с ограничениями по объёму памяти. Максимальный объём данных, загружаемых в модель, ограничен доступной оперативной памятью. Для работы с очень большими наборами данных (свыше 1 ГБ) рекомендуется использовать Power BI или Power Query в составе Power BI Desktop.

Источники:

  • Microsoft Docs — «Power Query для Excel» (документация)
  • Microsoft Support — «Начало работы с Power Query в Excel»
  • «Excel 2016. Полный курс» — учебное пособие (Бином, 2017)
  • Сборник статей журнала «Компьютерра» о работе с данными в Excel
Заметили ошибку или не согласны с информацией в статье? Напишите нам support@bfometr.ru