Очистка данных в Excel с помощью Power Query: полное руководство
Одним из ключевых навыков для работы с большими объёмами информации является очистка данных в Excel. Power Query позволяет получать, преобразовывать и загружать данные. В Excel для Windows он встроен начиная с версии 2016, включая 2019, 2021, 2024 и Microsoft 365. В Microsoft 365 для Mac и Excel для Интернета набор источников и команд отличается; ниже описан интерфейс Windows.
Если вы хотите ускорить анализ данных в Excel, навести порядок в источниках и подключить Power Query там, где это действительно уместно, этот разбор будет полезен.
Power Query сохраняет последовательность преобразований. После первоначальной настройки достаточно обновить запрос, если структура источника остаётся совместимой с его шагами. Время настройки и обновления зависит от данных и сложности запроса.
Примечание: Команда «Из таблицы/диапазона» позволяет получить данные с листа. Для обычного диапазона Excel предложит создать таблицу; её также можно заранее создать сочетанием Ctrl+T. Именованные диапазоны доступны как источники текущей книги. Возможность обращения к динамическим массивам зависит от версии Excel и способа задания источника.
Первичная очистка данных в Power Query
Для примера рассмотрим типичную задачу: ежедневно вы получаете файл, где названия продуктов в столбце A указаны только в первой строке каждой группы, а кварталы расположены по горизонтали, а не по вертикали.
- Сохраните исходную книгу на жесткий диск в предсказуемом месте с постоянным именем.
- В Excel выберите: Данные → Получить данные → Из файла → Из книги.
- Перейдите к файлу. В области предварительного просмотра щелкните на Лист1. Вместо кнопки «Загрузить» нажмите Преобразовать данные. Вы увидите книгу в сетке редактора Power Query.
- Чтобы заполнить пустые ячейки в столбце A, выделите его, перейдите на вкладку Преобразование и выберите Заполнить → Вниз. Значения
nullниже заполненной ячейки заменятся последним предшествующим значением. Начальныеnullбез значения выше останутся пустыми; пустые строки текста при необходимости сначала замените наnull.
- Для преобразования структуры данных (чтобы кварталы шли по вертикали) выделите первые два столбца (не кварталы). На вкладке Преобразование откройте меню Отменить свёртывание столбцов (Unpivot Columns) и выберите Отменить свёртывание других столбцов (Unpivot Other Columns).
- Щелкните правой кнопкой мыши на новом столбце «Attribute» и переименуйте его в Quarter.
- По завершении очистки нажмите Закрыть и загрузить на вкладке «Главная».
Совет: Лист Excel ограничен 1 048 576 строками, включая заголовок загружаемой таблицы. Для более крупных наборов в поддерживающем модель данных Excel для Windows выберите «Закрыть и загрузить в…», «Только создать подключение» и «Добавить эти данные в модель данных». Документированный предел таблицы модели — 1 999 999 997 строк, но достижимый объём существенно зависит от памяти, разрядности Excel и сжатия данных.
Мощь Applied Steps: Power Query запоминает всё
В правой части редактора находится список Применённые шаги (Applied Steps). Можно просмотреть результат каждого шага и, если рядом есть шестерёнка, изменить его параметры. Последующие шаги будут вычислены заново, но после изменения структуры данных могут потребовать исправления.
Результат: очистка данных на 400-й день одним кликом
Power Query позволяет повторять подготовленные преобразования при обновлении источника. В Excel для Windows откройте панель Данные → Запросы и подключения, найдите запрос и выберите Обновить.
Чтобы продемонстрировать работу на 400-й день, полностью измените исходный файл: добавьте новые продукты, клиентов, измените числа, увеличьте количество строк. Сохраните новую версию файла по тому же пути и с тем же именем.
После сохранения нового исходного файла обновите запрос. Power Query выполнит сохранённые шаги над новыми данными — например, загрузит 92 строки вместо 68. Если изменились названия листов, столбцов или типы значений, может понадобиться исправить запрос; скорость обработки зависит от источника и объёма данных.
Бонусные советы по работе с Power Query
Управление последовательностью обновления
Если сводная таблица использует результат запроса, сначала обновите запрос, дождитесь завершения загрузки, затем обновите сводную таблицу. В свойствах поддерживающего это подключения можно снять флажок Разрешить фоновое обновление, чтобы Excel ожидал завершения запроса. Этот флажок сам по себе не заменяет проверку обновления зависимой сводной таблицы.
Профилирование данных в Power Query
В поддерживающих профилирование версиях редактора откройте вкладку Вид и включите Качество столбца, Распределение столбца или Профиль столбца. По умолчанию анализируются первые 1000 строк. Для полной проверки переключите профилирование на весь набор данных в строке состояния редактора.
Профиль столбца показывает статистику и частоты значений. Если в столбце представлены все дни недели, различных значений будет 7. В терминологии профилирования distinct — число различных значений, а unique — число значений, встречающихся ровно один раз; это разные показатели.
Качество столбца показывает процент допустимых, ошибочных и пустых ячеек в каждом столбце, что помогает быстро оценить целостность данных.
Заключение
Power Query — это революционный инструмент для очистки данных в Excel, который превращает рутинную работу в автоматизированный процесс. Освоив его базовые функции и продвинутые возможности, такие как профилирование данных и управление обновлениями, вы сможете значительно повысить свою продуктивность и сосредоточиться на анализе, а не на подготовке информации.