Power Query: основы работы в Excel
Ключевые тезисы
- Power Query — бесплатный встроенный инструмент Excel для автоматизации обработки данных.
- Основные функции: извлечение, преобразование и загрузка данных из различных источников.
- Работает без сложных формул, записывая все действия в виде последовательных шагов.
- Позволяет работать с большими массивами данных, не перегружая файл отчёта.
Основное содержание
Что такое Power Query?
Power Query — это инструмент, встроенный в Excel начиная с версии 2016 (вкладка Данные). Его главная задача — обработка и приведение данных к нужному виду, а не выполнение сложных расчётов или прямое редактирование ячеек.
Основные функции:
- Извлечение данных: Импорт из книг Excel, текстовых файлов, папок, баз данных, веб-сервисов и других источников без ограничения на количество строк.
- Преобразование данных: Очистка и трансформация (разделение столбцов, извлечение чисел из текста, замена значений, объединение и т.д.) с помощью визуальных инструментов.
- Загрузка данных: Возврат обработанных данных в Excel в виде умной таблицы, сводной таблицы или в модель данных.
Подготовка к работе
- Требуемая версия: Excel 365, 2016 или новее. Максимальный функционал доступен в Excel 365.
- Необходимые базовые навыки: работа с основными функциями (СУММ, ЕСЛИ, ВПР), умными и сводными таблицами.
Правильная структура набора данных
Для эффективной работы Power Query ожидает данные в «правильном» формате:
- Первая строка содержит уникальные заголовки столбцов.
- Все записи идут последовательно без пропусков строк.
- Каждый столбец имеет единый тип данных (дата, число, текст).
- Отсутствуют промежуточные итоги внутри таблицы.
- Нет полностью пустых столбцов.
Пример проблемы: В исходных данных цена товара записана с точкой (текстовый формат), а для отчёта нужна общая сумма заказа. Решение через формулы в Excel увеличивает размер файла и снижает производительность.
Рабочий процесс в Power Query
Импорт данных
- Создание запроса: Во вкладке Данные → Получить и преобразовать данные → Из файла → Из книги.
- Навигатор: Выбор нужного листа и нажатие Преобразовать данные для открытия редактора Power Query.
Интерфейс редактора Power Query
- Правая панель «Параметры запроса»: Имя запроса и история «Примененные шаги». Каждый шаг можно редактировать.
- Заголовки столбцов: Значки указывают на тип данных (ABC123 — смешанный, 123 — число, календарь — дата).
- Лента: Основные вкладки — Главная, Преобразование, Добавление столбца, Вид.
- Важно: В редакторе нет кнопки отмены (Ctrl+Z). Для отмены действия нужно удалить соответствующий шаг в истории.
Преобразование данных (на примере)
- Удаление лишних столбцов: Выделение столбцов и нажатие Delete или использование инструмента Выбор столбцов.
- Разделение столбца: Например, разделение номера счета (InvoiceNumber) по разделителю «-» для извлечения ID заказчика.
- Изменение типа данных: Замена точки на запятую в столбце «Цена» и смена типа с текста на Десятичное число или Валюта.
- Добавление столбца: Выделение столбцов «Количество» и «Цена» → вкладка Добавление столбца → Умножить. Новый столбец переименовывается (например, в «SalesAmount»).
Загрузка данных в Excel
После преобразований используется Закрыть и загрузить. Можно загрузить:
- В виде таблицы (умной таблицы).
- В виде сводной таблицы (напрямую, если версия Excel поддерживает).
- Только создать подключение (данные не загружаются на лист, что удобно для сохранения незавершённой работы).
Ключевые особенности и нюансы
Разница вкладок «Преобразование» и «Добавление столбца»
- «Преобразование»: Изменяет данные в выбранном столбце.
- «Добавление столбца: Создаёт новый столбец с преобразованными данными, оставляя исходный нетронутым.
Полезные опции на вкладке «Вид»
- Моноширинный шрифт: Помогает видеть пробелы и разделители в тексте.
- Качество столбца / Профиль столбца: Показывает распределение типов данных и ошибки в столбце (по умолчанию анализирует 1000 строк для скорости).
Язык M
Все преобразования записываются на языке M. Работать можно без его знания, используя визуальные инструменты. Однако редактирование кода в Расширенном редакторе (вкладка Главная) позволяет гибко править сложные шаги.
Обновление данных
При изменении исходного файла отчёт обновляется одним из способов:
- Обновление сводной таблицы через контекстное меню.
- Кнопка Обновить все на вкладке Данные.
- Значок обновления в области Запросы и подключения.
Объединение запросов (аналог ВПР)
- Создание второго запроса к источнику с дополнительными данными (например, CSV-файл с именами заказчиков).
- В основном запросе: Главная → Объединить запросы. Выбор второго запроса и столбца для связи (например, CustomerID).
- Тип соединения: «Первое» (соответствует работе ВПР).
- В появившемся столбце с типом «Table» нажать на значок разворота и выбрать нужные столбцы для добавления.
Выводы
Power Query — мощный инструмент для ETL (Extract, Transform, Load). Он кардинально упрощает и ускоряет подготовку данных для анализа по сравнению с ручными формулами, особенно при работе с большими или неструктурированными массивами данных из разных источников.