Power Query это встроенный инструмент Excel для автоматического импорта, очистки и преобразования данных из любых источников, включая CSV, базы данных, веб-страницы и другие файлы Excel, который позволяет обновлять отчёты одной кнопкой вместо ежедневного ручного копирования.
Зачем нужен Power Query, если есть обычный импорт
Разница огромна. Обычный импорт это разовая операция. Скопировал данные, вставил, забыл. Завтра повторяешь всё заново.
Power Query запоминает все шаги. Каждое преобразование записывается. Когда приходят новые данные, вы нажимаете «Обновить», и всё происходит автоматически. Без ручной работы.
Короче, представьте: вы каждый понедельник получаете выгрузку из CRM в формате CSV. В ней 15 столбцов, а вам нужны 5. Даты в американском формате. Названия клиентов с лишними пробелами. Каждый раз вы тратите 30 минут на очистку и форматирование.
Power Query делает это за 3 секунды. Один раз настроили, дальше работает само.
Мой коллега Артём (аналитик данных, рабочая станция на Ryzen 5 7600 с 16 ГБ ОЗУ) каждый день импортировал данные из пяти разных источников. Полтора часа ручной работы. После настройки Power Query это занимает 2 минуты, включая время на заваривание кофе.
Где найти Power Query в Excel
Power Query встроен в Excel начиная с версии 2016. В Excel 2010 и 2013 его можно установить как бесплатную надстройку.
Находится на вкладке «Данные». Кнопка «Получить данные» (или «Получить и преобразовать данные» в некоторых версиях). Это и есть Power Query.
Кстати, в Excel для Microsoft 365 Power Query постоянно обновляется и получает новые возможности. Это одно из преимуществ подписной модели: инструмент становится лучше каждый месяц.
Первый запрос: импорт CSV файла
Начнём с самого простого. У вас есть CSV файл с данными. Нужно его импортировать и привести в порядок.
Вкладка «Данные», «Получить данные», «Из файла», «Из текстового/CSV файла». Выбираете файл. Появляется окно предварительного просмотра.
На самом деле, можно сразу нажать «Загрузить», и данные появятся в таблице. Но вся магия Power Query в кнопке «Преобразовать данные». Нажмите её.
Откроется редактор Power Query. Отдельное окно с вашими данными и панелью инструментов. Здесь вы будете колдовать.
Вот что можно сделать прямо сейчас:
- Удалить ненужные столбцы (правый клик по заголовку, «Удалить»)
- Изменить тип данных (клик по иконке типа в заголовке столбца)
- Переименовать столбцы (двойной клик по заголовку)
- Отфильтровать строки (стрелка в заголовке, как в обычном Excel)
- Заменить значения (правый клик, «Заменить значения»)
Знаете что? Каждое ваше действие записывается как «шаг» в панели справа («Применённые шаги»). Если ошиблись, просто удалите последний шаг. Это как бесконечный Ctrl+Z, но лучше.
Очистка данных: грязные данные в чистые
Данные из реального мира всегда грязные. Лишние пробелы, дубликаты, пустые строки, непонятные символы. Power Query справляется со всем этим.
Удаление пробелов: выделите столбец, вкладка «Преобразование», «Формат», «Усечь». Это уберёт пробелы в начале и конце текста.
Удаление дубликатов: выделите столбцы, по которым определяете уникальность, правый клик, «Удалить дубликаты».
Тут такое дело: Power Query удаляет дубликаты гораздо надёжнее, чем встроенная функция Excel. Он работает до загрузки данных в таблицу, поэтому вы видите результат до применения.
Разделение столбца: если в одном столбце имя и фамилия через пробел, правый клик, «Разделить столбец», «По разделителю». Выбираете пробел. Получаете два столбца.
Объединение столбцов: обратная операция. Выделяете несколько столбцов, правый клик, «Объединить столбцы». Выбираете разделитель.
Импорт из разных источников
CSV это только начало. Power Query работает с десятками источников данных.
По факту, вот самые полезные для повседневной работы:
- Файлы Excel (.xlsx, .xls) с выбором конкретного листа
- CSV и текстовые файлы с любой кодировкой
- Папка с файлами (автоматическое объединение всех файлов в папке)
- Веб-страницы (парсинг таблиц с сайтов)
- Базы данных (SQL Server, Access, MySQL через ODBC)
- JSON и XML файлы
Импорт из папки это невероятно мощная функция. Допустим, вы каждый месяц получаете файл отчёта. За год их 12. Power Query может автоматически объединить все файлы из папки в одну таблицу. Добавляете новый файл в папку, нажимаете «Обновить», он появляется в общей таблице.
Моя знакомая Светлана (финансовый контролёр, ноутбук Lenovo IdeaPad на i5-1340P) объединяет ежемесячные отчёты из 15 филиалов. Каждый филиал присылает свой файл Excel. Раньше она вручную копировала данные из каждого файла. С Power Query все 15 файлов объединяются автоматически.
Формулы M: язык Power Query
За визуальным интерфейсом Power Query скрывается язык M (Power Query Formula Language). Каждый шаг, который вы делаете мышкой, генерирует код на M.
Если честно, знать M не обязательно. 90% задач решаются через визуальный интерфейс. Но иногда формулы M позволяют сделать то, чего нет в меню.
Чтобы увидеть код, в редакторе Power Query нажмите «Расширенный редактор» на вкладке «Главная». Вы увидите что-то вроде:
let
Source = Csv.Document(File.Contents("C:data.csv")),
#"Promoted Headers" = Table.PromoteHeaders(Source),
#"Changed Type" = Table.TransformColumnTypes(...)
in
#"Changed Type"Каждый шаг это переменная, которая ссылается на предыдущую. Логика простая и линейная.
Обновление данных: автоматика и расписание
Настроили запрос? Теперь при каждом открытии файла или по нажатию кнопки «Обновить всё» Power Query заново выполнит все шаги и подставит свежие данные.
Грубо говоря, ваш отчёт обновляется сам. Вы только открываете файл.
Можно настроить автоматическое обновление при открытии файла: правый клик по таблице, «Свойства таблицы», галочка «Обновлять данные при открытии файла».
А ещё можно настроить обновление по расписанию (каждые 5, 10, 30 минут), если источник данных постоянно меняется. Но это актуально в основном для подключений к базам данных.
Кстати, если вы используете Power Query в связке с Power BI (это отдельный продукт Microsoft для визуализации), то обновление по расписанию работает через облако и не требует открытого компьютера.
Типичные сценарии использования
Вот конкретные задачи, которые Power Query решает лучше всего:
Консолидация ежемесячных отчётов. 12 файлов за год, одинаковая структура. Power Query объединяет их в одну таблицу, добавляя столбец с именем файла (чтобы видеть, откуда какие данные).
Очистка выгрузок из 1С. Все знают, что выгрузки из 1С содержат лишние строки, объединённые ячейки и странное форматирование. Power Query приводит это в нормальный табличный вид.
Парсинг данных с сайтов. Нужны курсы валют, цены конкурентов, статистика? Power Query может загрузить таблицу прямо с веб-страницы.
Ну и объединение данных из разных источников (JOIN). Есть таблица продаж и справочник клиентов? Power Query объединит их по общему ключу, как в базе данных. Функция «Объединить запросы» на вкладке «Главная».
Ошибки новичков и как их избежать
Не сохраняйте промежуточные данные. Power Query хранит только запрос (алгоритм), а не данные. Данные загружаются при обновлении. Если вы удалите исходный файл, запрос перестанет работать.
На самом деле, самая частая ошибка это абсолютные пути к файлам. Если вы переместите исходный файл, запрос сломается. Используйте параметры для указания пути к папке с данными. Так при смене расположения нужно будет изменить только один параметр.
Не загружайте все данные, если не нужны. Фильтруйте в Power Query до загрузки. Если в исходном файле миллион строк, а вам нужны данные за текущий месяц, отфильтруйте их в запросе. Excel будет работать быстрее.
Power Query и Power Pivot: связка для аналитики
Power Query импортирует и чистит данные. А Power Pivot строит модели для анализа.
Вместе они превращают Excel в мини-BI-платформу. Power Query загружает данные из нескольких источников, а Power Pivot объединяет их в модель данных, где можно строить сводные таблицы, DAX-формулы и дашборды. Грубо говоря, это то, что раньше требовало отдельного BI-инструмента, а теперь доступно прямо в Excel.
Мой коллега Антон, финансовый директор на рабочей станции HP EliteDesk с i7-13700 и 32 ГБ ОЗУ, построил целую систему отчётности для компании на связке Power Query + Power Pivot. Данные из 1С, CRM и Google Analytics загружаются через Power Query, объединяются в Power Pivot, и руководство каждое утро видит актуальный дашборд. Если честно, раньше для этого нанимали аналитика на полную ставку.
Где взять Excel с Power Query
Power Query встроен во все современные версии Excel: Office 2016, 2019, 2021, 2024 и Microsoft 365. Лицензионные ключики для всех этих версий доступны в keytrust24.store с моментальной доставкой на email. Если вы хотите получать регулярные обновления Power Query с новыми функциями, рекомендую Microsoft 365, где инструмент постоянно развивается.
