Как включить и пользоваться Power Query в Excel: пошаговая инструкция

Что такое Power Query в Excel

Power Query — надстройка Microsoft Excel для автоматизации ETL-процесса: инструмент извлекает данные из внешних источников (Extract), преобразует их в нужный формат (Transform) и загружает в рабочую книгу или Power Pivot (Load). Надстройка бесплатная, разработчик — Microsoft. Начиная с Excel 2016 она встроена по умолчанию под названием Get & Transform («Скачать и преобразовать»).

Пять ключевых возможностей Power Query:

• Подключение к десяткам форматов источников: XLS, CSV, JSON, PDF, XML, SQL-базы данных.
• Очистка данных — удаление дубликатов, лишних пробелов и пустых строк.
• Объединение нескольких таблиц по общему ключевому столбцу.
• Автоматическое обновление при изменении исходных данных.
• Экспорт на лист Excel, в сводную таблицу или Power Pivot.

Главное отличие от встроенных средств Excel — отсутствие ограничений по количеству строк. Стандартный лист вмещает максимум 1 048 576 строк, Power Query обрабатывает любые объёмы.

Power Query, обычный Excel и Power Pivot: в чём разница

Три инструмента решают разные задачи.

• Excel без надстроек: ручная обработка в ячейках, лимит строк, ВПР-формула (вертикальный поиск) — удобно для небольших таблиц, но неповоротливо при тысячах записей.
• Power Query: ETL-конвейер без ограничения строк, поддерживает больше форматов и функций редактирования — оптимален для загрузки и трансформации данных.
• Power Pivot: инструмент моделирования данных и анализа через DAX-формулы (Data Analysis Expressions — язык аналитических выражений Microsoft), принимает обработанные данные из Power Query.

Типичная рабочая схема выглядит так: Power Query загружает и очищает исходные данные → передаёт в Power Pivot → аналитик строит отчёт. Инструмент заменяет сотни ВПР-формул и ручное копирование между листами.

Как включить Power Query в Excel

Способ включения зависит от версии Excel. В Excel 2016 и новее надстройка встроена — дополнительная установка не нужна. Для версий 2010 и 2013 Power Query скачивается как MSI-файл (установочный пакет Microsoft) с официального сайта.

Excel 2010 и 2013: установка надстройки

В Excel 2010 и 2013 Power Query не входит в стандартную поставку — нужна ручная установка. Надстройка скачивается с официального сайта Microsoft бесплатно, это единственный способ добавить её в эти версии.

Шаг 1. Проверьте разрядность системы: Файл → Справка → раздел «Сведения о продукте». Здесь указано 32 или 64-bit — для каждой разрядности существует отдельный установочный файл.

Шаг 2. Скачайте нужный MSI-файл с сайта Microsoft (поиск: «Power Query для Excel»). Выберите версию, строго соответствующую вашей разрядности.

Шаг 3. Запустите установщик и следуйте инструкциям мастера. После завершения перезапустите Excel — в верхней панели появится отдельная вкладка «Power Query».

⚠️ Если скачать файл неверной разрядности, надстройка не установится и при запуске выдаст ошибку. Перед загрузкой обязательно сверьтесь со сведениями о продукте.

Excel 2016, 2019 и 365: встроенная функция

В Excel 2016 и новее Power Query встроена и дополнительной установки не требует.

• Excel 2016: вкладка «Данные» → раздел «Скачать и преобразовать» → кнопка «Создать запрос».
• Excel 2019 и 365: вкладка «Данные» → раздел «Получить и преобразовать данные» → кнопка «Получить данные».

Альтернативный способ: дважды кликните по запросу в боковой панели «Запросы и подключения» — редактор Power Query откроется сразу на нужном запросе.

Если кнопка «Получить данные» не видна, убедитесь, что вкладка «Данные» активна. В корпоративных версиях Microsoft 365 раздел может быть скрыт системным администратором.

Как пользоваться Power Query в Excel: пошаговая инструкция

Power Query в Excel работает как последовательный ETL-конвейер: источник данных → редактор → типы данных → очистка → объединение → экспорт. Каждое действие записывается автоматически в виде шага и может быть изменено или отменено в любой момент без потери исходных данных. Разберём каждый шаг подробно.

Шаг 1. Подключитесь к источнику данных

Путь для Excel 2019/365: вкладка «Данные» → «Получить данные» → выберите категорию источника. Power Query поддерживает пять категорий:

• Файл: XLS, XLSX, CSV, TXT, JSON, PDF, XML, HTML.
• База данных: SQL Server, Access, Oracle, MySQL.
• Веб-службы: SharePoint, OData-каналы.
• Облако: Azure, Dynamics 365.
• Прочие: ODBC (Open Database Connectivity — универсальный интерфейс к базам данных), OLEDB — через них подключаются к 1С.

Для работы с российскими сервисами доступно подключение к Яндекс Метрике и 1С через ODBC или файловый экспорт в CSV. Google Analytics подключается через веб-коннектор.

После выбора файла открывается Навигатор: слева — список листов или таблиц, справа — предпросмотр данных. Выберите нужный лист и нажмите «Преобразовать данные» — откроется редактор Power Query.

Шаг 2. Изучите редактор Power Query

[IMAGE: image_5]

Редактор запросов Power Query состоит из шести разделов:

1. Панель вкладок (Главная / Преобразование / Добавление столбца) — все операции с данными.
2. Строка формул — отображает M-формулу для каждого выбранного шага.
3. Список запросов — все открытые запросы текущей книги.
4. Предпросмотр данных — таблица с результатами на текущем шаге преобразований.
5. Свойства — имя запроса и описание.
6. Применённые шаги — история всех изменений запроса.

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

Шаг 3. Установите правильные типы данных

Power Query поддерживает 13 типов данных. При загрузке из файла типы определяются автоматически, но нередко с ошибками — например, числовой ID с ведущими нулями превращается в число и теряет их.

Как изменить тип: кликните иконку слева от названия столбца → выберите нужный тип из выпадающего списка. Шаг «Изменённый тип» автоматически появится в «Применённых шагах».

Шаг 4. Очистите данные от ошибок

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

Четыре основные операции очистки:

• Удалить дубликаты: выделите столбец → правая кнопка мыши → «Удалить дубликаты».
• Убрать пробелы: вкладка «Преобразование» → «Формат» → «Усечь» — удаляет пробелы в начале и конце строки.
• Исключить пустые строки: кликните значок фильтра в заголовке столбца → снимите галочку с «null».
• Изменить регистр: «Преобразование» → «Формат» → нижний, верхний или каждое слово с прописной буквы.

Каждое действие добавляется в «Применённые шаги» — его можно удалить и вернуть прежний вид данных в любой момент.

Шаг 5. Объедините таблицы

Power Query предлагает два метода объединения таблиц:

• Слияние (Merge) — соединяет две таблицы по общему ключевому столбцу, аналог ВПР. Путь: «Главная» → «Объединить запросы» → «Слияние запросов».
• Добавление (Append) — склеивает строки нескольких таблиц с одинаковой структурой. Путь: «Главная» → «Объединить запросы» → «Добавить запросы».

При слиянии доступно шесть типов соединения:

Как импортировать данные из Power Query в Excel

После всех преобразований нажмите «Закрыть и загрузить в…» — откроется диалог с пятью вариантами выгрузки:

• Таблица — умная таблица на новом или существующем листе Excel.
• Отчёт сводной таблицы — данные сразу поступают в сводную таблицу для анализа.
• Сводная диаграмма — диаграмма с интерактивной фильтрацией по полям.
• Только создать подключение — запрос сохраняется, данные не выгружаются на лист.
• Добавить в модель данных — данные передаются в Power Pivot (ThisWorkbookDataModel) для DAX-анализа.

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

Язык M и функция Excel.Workbook()

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

Вручную язык M применяется для сложных условий и пользовательских функций. Наиболее частый пример — подключение к другой книге Excel через функцию Excel.Workbook():

Источник = Excel.Workbook(File.Contents(«C:\data\отчёт.xlsx»), null, true)

Строка открывает внешний XLSX-файл как источник данных. Параметр null загружает все листы, true — использует заголовки первой строки как имена столбцов.

Пример пользовательской функции для расчёта НДС:

(цена) => цена * 0.2

Функцию применяют через «Добавление столбца» → «Вызвать пользовательскую функцию» — Power Query добавит результат как новый столбец таблицы.

Хотите системно освоить Power Query, сводные таблицы и анализ данных в Excel? В ProfiFuture можно пройти курс «Excel для работы с данными» примерно за 1 месяц с господдержкой — 75% стоимости компенсируется образовательной квотой. Онлайн, в удобное время, от 14 900 ₽.

Часто задаваемые вопросы

Что такое Power Query в Excel?

Power Query — бесплатная надстройка Microsoft Excel, которая автоматизирует ETL-процесс: извлекает данные из внешних источников, преобразует их и загружает в Excel или Power Pivot. Встроена с Excel 2016; для версий 2010 и 2013 скачивается с официального сайта Microsoft. Не имеет ограничений по количеству строк — в отличие от стандартного листа с лимитом 1 048 576 строк.

Где находится Power Query в Excel 2016 и новее?

В Excel 2016 — вкладка «Данные» → раздел «Скачать и преобразовать» → кнопка «Создать запрос». В Excel 2019 и 365 путь изменился: «Данные» → «Получить и преобразовать данные» → «Получить данные». В обоих случаях надстройка встроена — дополнительная установка не нужна.

Как включить Power Query в Excel 2013?

Определите разрядность системы: Файл → Справка → «Сведения о продукте» — 32 или 64-bit. Скачайте соответствующий MSI-файл с официального сайта Microsoft. Запустите установщик и следуйте инструкциям. После установки в Excel появится вкладка «Power Query». Выбор неверной разрядности приводит к ошибке при установке.

Сколько строк поддерживает Power Query?

Power Query не ограничивает количество строк — в отличие от стандартного листа Excel, где максимум 1 048 576 строк. Инструмент обрабатывает любые объёмы данных без снижения производительности, что делает его незаменимым при работе с большими выгрузками из баз данных и CRM-систем.

Чем Power Query отличается от Power Pivot?

Power Query — ETL-инструмент: загружает, очищает и преобразует данные из множества форматов источников. Power Pivot — инструмент моделирования данных и аналитики через DAX-формулы. Стандартный рабочий процесс: данные обрабатываются в Power Query, затем передаются в Power Pivot для построения аналитических отчётов.

Нужно ли платить за Power Query?

Power Query полностью бесплатна. В Excel 2016 и новее встроена по умолчанию. Для Excel 2010 и 2013 скачивается с сайта Microsoft без оплаты — нужны совместимая версия Windows и правильная разрядность системы.

Что такое «Применённые шаги» в Power Query?

«Применённые шаги» — панель в правой части редактора, куда автоматически записывается каждое преобразование: смена типа данных, фильтр, удаление столбца. Каждый шаг отображается в строке формул как M-формула. Любой шаг можно удалить — данные вернутся к состоянию предыдущего шага без потерь.

Как научиться работать с Power Query с нуля?

Начните с официальной документации Microsoft — она доступна на русском языке и содержит пошаговые руководства по каждой функции. Для системного изучения Power Query с практикой подойдут структурированные онлайн-программы. Например, курс «Excel для работы с данными» в ProfiFuture рассчитан примерно на 1 месяц, включает практические задания и доступен от 14 900 ₽ с господдержкой.

Можно ли подключить Power Query к Яндекс Метрике или 1С?

Да. Power Query поддерживает подключение к Яндекс Метрике, 1С (через ODBC или файловый экспорт в CSV), Google Analytics и другим российским сервисам. Это делает инструмент особенно удобным для аналитиков, работающих с отечественными системами учёта и маркетинговой аналитики.

Что такое язык M в Power Query?

Язык M (Power Query Formula Language) — функциональный язык, на котором записываются все шаги преобразований. При работе через интерфейс редактора M-формулы генерируются автоматически. Вручную язык M нужен для сложных условий, вложенных запросов, пользовательских функций и работы с функцией Excel.Workbook().