Реклама
Этот сайт использует Яндекс.Метрику для анализа посещаемости. Подробнее
Очистка данных и Power Query

Очистка данных в Excel с помощью Power Query: полное руководство

forest_paw 04.03.2026 512

Очистка данных в Excel с помощью Power Query: полное руководство

Одним из ключевых навыков для работы с большими объёмами информации является очистка данных в Excel. Power Query позволяет получать, преобразовывать и загружать данные. В Excel для Windows он встроен начиная с версии 2016, включая 2019, 2021, 2024 и Microsoft 365. В Microsoft 365 для Mac и Excel для Интернета набор источников и команд отличается; ниже описан интерфейс Windows.

Если вы хотите ускорить анализ данных в Excel, навести порядок в источниках и подключить Power Query там, где это действительно уместно, этот разбор будет полезен.

Power Query сохраняет последовательность преобразований. После первоначальной настройки достаточно обновить запрос, если структура источника остаётся совместимой с его шагами. Время настройки и обновления зависит от данных и сложности запроса.

В группе "Получить и преобразовать данные" на вкладке "Данные" ленты большинство элементов — это Power Query. Выбор "Из рисунка" не относится к Power Query. Команды «Получить данные», «Из текста/CSV», «Из Интернета» и «Из таблицы/диапазона» используются для импорта в Power Query. «Существующие подключения» также могут относиться к другим подключениям Excel.
В группе "Получить и преобразовать данные" на вкладке "Данные" ленты большинство элементов — это Power Query. Выбор "Из рисунка" не относится к Power Query. Команды «Получить данные», «Из текста/CSV», «Из Интернета» и «Из таблицы/диапазона» используются для импорта в Power Query. «Существующие подключения» также могут относиться к другим подключениям Excel.

Примечание: Команда «Из таблицы/диапазона» позволяет получить данные с листа. Для обычного диапазона Excel предложит создать таблицу; её также можно заранее создать сочетанием Ctrl+T. Именованные диапазоны доступны как источники текущей книги. Возможность обращения к динамическим массивам зависит от версии Excel и способа задания источника.

Первичная очистка данных в Power Query

Для примера рассмотрим типичную задачу: ежедневно вы получаете файл, где названия продуктов в столбце A указаны только в первой строке каждой группы, а кварталы расположены по горизонтали, а не по вертикали.

  1. Сохраните исходную книгу на жесткий диск в предсказуемом месте с постоянным именем.
  2. В Excel выберите: Данные → Получить данные → Из файла → Из книги.
Продукты указаны в столбце A, но в структурированном виде, где "Apple" отображается в A2, за которым следуют несколько пустых ячеек. "Banana" указана в A8, за которой следуют другие пустые ячейки. Клиенты указаны в столбце B. Кварталы расположены горизонтально на листе в столбцах C, D, E и F.
Продукты указаны в столбце A, но в структурированном виде, где "Apple" отображается в A2, за которым следуют несколько пустых ячеек. "Banana" указана в A8, за которой следуют другие пустые ячейки. Клиенты указаны в столбце B. Кварталы расположены горизонтально на листе в столбцах C, D, E и F.
  1. Перейдите к файлу. В области предварительного просмотра щелкните на Лист1. Вместо кнопки «Загрузить» нажмите Преобразовать данные. Вы увидите книгу в сетке редактора Power Query.
  2. Чтобы заполнить пустые ячейки в столбце A, выделите его, перейдите на вкладку Преобразование и выберите Заполнить → Вниз. Значения null ниже заполненной ячейки заменятся последним предшествующим значением. Начальные null без значения выше останутся пустыми; пустые строки текста при необходимости сначала замените на null.
Пустые ячейки в столбце A теперь отображаются как "null" в редакторе Power Query.
Пустые ячейки в столбце A теперь отображаются как "null" в редакторе Power Query.
  1. Для преобразования структуры данных (чтобы кварталы шли по вертикали) выделите первые два столбца (не кварталы). На вкладке Преобразование откройте меню Отменить свёртывание столбцов (Unpivot Columns) и выберите Отменить свёртывание других столбцов (Unpivot Other Columns).
  2. Щелкните правой кнопкой мыши на новом столбце «Attribute» и переименуйте его в Quarter.
Если все четыре квартальных значения заполнены, строк станет в четыре раза больше. Значения null при отмене свёртывания не создают строк. Столбцы A и B выглядят так же (за исключением того, что теперь четыре строки для каждой предыдущей одной строки). Кварталы, которые шли по горизонтали в столбцах C, D, E и F, теперь идут по вертикали в столбце C. Выручка из набора данных теперь находится в столбце D.
Если все четыре квартальных значения заполнены, строк станет в четыре раза больше. Значения null при отмене свёртывания не создают строк. Столбцы A и B выглядят так же (за исключением того, что теперь четыре строки для каждой предыдущей одной строки). Кварталы, которые шли по горизонтали в столбцах C, D, E и F, теперь идут по вертикали в столбце C. Выручка из набора данных теперь находится в столбце D.
  1. По завершении очистки нажмите Закрыть и загрузить на вкладке «Главная».

Совет: Лист Excel ограничен 1 048 576 строками, включая заголовок загружаемой таблицы. Для более крупных наборов в поддерживающем модель данных Excel для Windows выберите «Закрыть и загрузить в…», «Только создать подключение» и «Добавить эти данные в модель данных». Документированный предел таблицы модели — 1 999 999 997 строк, но достижимый объём существенно зависит от памяти, разрядности Excel и сжатия данных.

Мощь Applied Steps: Power Query запоминает всё

В правой части редактора находится список Применённые шаги (Applied Steps). Можно просмотреть результат каждого шага и, если рядом есть шестерёнка, изменить его параметры. Последующие шаги будут вычислены заново, но после изменения структуры данных могут потребовать исправления.

В правой части редактора Power Query список Applied Steps. Для этого примера у вас есть Source, Navigation, Promoted Headers, Changed Type, Filled Down, Unpivoted Other Columns, Renamed Columns.
В правой части редактора Power Query список Applied Steps. Для этого примера у вас есть Source, Navigation, Promoted Headers, Changed Type, Filled Down, Unpivoted Other Columns, Renamed Columns.

Результат: очистка данных на 400-й день одним кликом

Power Query позволяет повторять подготовленные преобразования при обновлении источника. В Excel для Windows откройте панель Данные → Запросы и подключения, найдите запрос и выберите Обновить.

Панель "Запросы и подключения" содержит один запрос с названием "Sheet1" с загруженными 68 строками. Если расширить панель и навести курсор на "Sheet1", появится значок обновления.
Панель "Запросы и подключения" содержит один запрос с названием "Sheet1" с загруженными 68 строками. Если расширить панель и навести курсор на "Sheet1", появится значок обновления.

Чтобы продемонстрировать работу на 400-й день, полностью измените исходный файл: добавьте новые продукты, клиентов, измените числа, увеличьте количество строк. Сохраните новую версию файла по тому же пути и с тем же именем.

После сохранения нового исходного файла обновите запрос. Power Query выполнит сохранённые шаги над новыми данными — например, загрузит 92 строки вместо 68. Если изменились названия листов, столбцов или типы значений, может понадобиться исправить запрос; скорость обработки зависит от источника и объёма данных.

Нажмите значок "Обновить" на панели "Запросы и подключения", и он сообщит, что у вас загружено 92 строки.
Нажмите значок "Обновить" на панели "Запросы и подключения", и он сообщит, что у вас загружено 92 строки.

Бонусные советы по работе с Power Query

Управление последовательностью обновления

Если сводная таблица использует результат запроса, сначала обновите запрос, дождитесь завершения загрузки, затем обновите сводную таблицу. В свойствах поддерживающего это подключения можно снять флажок Разрешить фоновое обновление, чтобы Excel ожидал завершения запроса. Этот флажок сам по себе не заменяет проверку обновления зависимой сводной таблицы.

В панели управления обновлением доступны следующие параметры: Разрешить фоновое обновление. Обновлять каждые NN минут. Обновлять данные при открытии файла. Удалять данные из внешнего диапазона данных. Обновлять это подключение при обновлении всех.
В панели управления обновлением доступны следующие параметры: Разрешить фоновое обновление. Обновлять каждые NN минут. Обновлять данные при открытии файла. Удалять данные из внешнего диапазона данных. Обновлять это подключение при обновлении всех.

Профилирование данных в Power Query

В поддерживающих профилирование версиях редактора откройте вкладку Вид и включите Качество столбца, Распределение столбца или Профиль столбца. По умолчанию анализируются первые 1000 строк. Для полной проверки переключите профилирование на весь набор данных в строке состояния редактора.

Вкладка "Вид" в Power Query теперь предлагает группу "Предварительный просмотр данных" с пятью флажками: Моноширинный, Показывать пробелы, Качество столбца, Распределение столбца и Профиль столбца.
Вкладка "Вид" в Power Query теперь предлагает группу "Предварительный просмотр данных" с пятью флажками: Моноширинный, Показывать пробелы, Качество столбца, Распределение столбца и Профиль столбца.

Профиль столбца показывает статистику и частоты значений. Если в столбце представлены все дни недели, различных значений будет 7. В терминологии профилирования distinct — число различных значений, а unique — число значений, встречающихся ровно один раз; это разные показатели.

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

Качество столбца показывает процент допустимых, ошибочных и пустых ячеек в каждом столбце, что помогает быстро оценить целостность данных.

"Качество столбца" показывает процент значений в этом столбце, которые являются Допустимыми, Ошибочными или Пустыми.
"Качество столбца" показывает процент значений в этом столбце, которые являются Допустимыми, Ошибочными или Пустыми.

Заключение

Power Query — это революционный инструмент для очистки данных в Excel, который превращает рутинную работу в автоматизированный процесс. Освоив его базовые функции и продвинутые возможности, такие как профилирование данных и управление обновлениями, вы сможете значительно повысить свою продуктивность и сосредоточиться на анализе, а не на подготовке информации.

Обложка книги "Master Your Data with Excel and Power BI — Leveraging Power Query to Get & Transform Your Task Flow".
Обложка книги "Master Your Data with Excel and Power BI — Leveraging Power Query to Get & Transform Your Task Flow".
Поделиться:

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