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