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

Быстрые ссылки

С момента своего дебюта в Excel Power Query изменил способ, которым специалисты управляют данными. Первоначально представленный как надстройка в 2010 году, Power Query стал незаменимой встроенной функцией Excel с 2016 года. Его основная функция? Оптимизация процесса очистки, импорта и реорганизации данных, чтобы сэкономить вам не только время, но и дополнительные разочарования. Сосредоточившись на эффективной подготовке данных, Power Query позволяет вам сосредоточиться на анализе и выводах.

Что такое PowerQuery?

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

Расположение инструментов Power Query на вкладке «Данные» в Excel

Пристегните ремни, ведь мы погрузимся в практическое использование Power Query и научим вас эффективно очищать, импортировать и организовывать данные.

Очистка данных Excel с помощью Power Query

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

Список адресов электронной почты, разделенных точкой с запятой в Excel

Для начала выполните следующие действия:

Откройте Power Query: щелкните вкладку «Данные», затем выберите «Из таблицы/диапазона». В этом окне вам будет предложено создать таблицу из выбранных данных.
Выберите данные: убедитесь, что выбран правильный диапазон, и, если у ваших данных нет заголовков, не устанавливайте флажок «Заголовки», прежде чем нажать «ОК».

После нажатия кнопки «ОК» откроется редактор Power Query, который подготовит почву для преобразования данных.

Ваша следующая задача? Разбить адреса электронной почты на отдельные строки:

Разделить столбец: щелкните правой кнопкой мыши заголовок столбца, наведите указатель мыши на «Разделить столбец» и выберите «По разделителю». Выберите «Точка с запятой» в качестве разделителя и выберите «Строки» в диалоговом окне «Дополнительные параметры».

После подтверждения вы увидите каждый адрес электронной почты, аккуратно организованный в отдельные строки.

Адреса электронной почты разделены на несколько строк в редакторе Power Query

Теперь пришло время извлечь имя и фамилию. Для этого воспользуйтесь функцией «Добавить столбец из примеров»:

Извлечение имен: дважды щелкните заголовок столбца и введите имя или должность, чтобы повторить шаблон. Power Query автоматически предложит оставшиеся записи!
Переименование столбцов: обязательно переименуйте столбцы соответствующим образом (например, «Имя», «Фамилия», «Адрес электронной почты»).
Последние штрихи: отрегулируйте порядок столбцов, перетаскивая их в нужные позиции.

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

Присвоение имени запросу в редакторе Power Query

Импорт и реорганизация данных с помощью Power Query

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

Для начала снова перейдите на вкладку «Данные»:

Инициировать импорт: нажмите «Получить данные» > «Из файла» > «Из книги Excel».
Выберите файл: найдите файл, из которого необходимо импортировать данные, и нажмите «Импорт», а затем «Преобразовать данные», чтобы получить доступ к редактору Power Query.

Окно навигатора, показывающее данные, доступные для импорта

В редакторе Power Query:

Сделать строку заголовком: нажмите «Использовать первую строку как заголовки», чтобы мгновенно назначить первую строку заголовком столбца.
Отменить свертывание данных: выберите столбцы, которые представляют ваши уникальные переменные, и щелкните правой кнопкой мыши, чтобы выбрать «Отменить свертывание других столбцов». Это преобразует ваш макет, представляя каждое уникальное значение в отдельной строке.

Несведенные данные, демонстрирующие аккуратную компоновку

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

Установка типа данных для столбца «Месяц» в редакторе Power Query

Завершив преобразования, нажмите «Закрыть и загрузить» > «Закрыть и загрузить в», чтобы определить, где в Excel будут отображаться ваши данные.

Сила Power Query заключается в его способности поддерживать динамические ссылки на свои источники данных. Например, если ваш исходный набор данных изменится, просто щелкните правой кнопкой мыши на новой таблице и выберите «Обновить», чтобы обновить ее без проблем.

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

Готовы ли вы вывести свои навыки управления данными на новый уровень? Изучите Power Query глубже, работая с таблицами из Интернета и испытывая его универсальные возможности импорта на собственном опыте. Вы обнаружите, что этот инструмент повышает вашу производительность и точность в кратчайшие сроки!

Дополнительные идеи

1. Какие типы источников данных я могу использовать с Power Query?

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

2. Можно ли автоматически обновлять данные, импортированные через Power Query?

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

3. Существует ли ограничение на объем данных, которые я могу импортировать с помощью Power Query?

Хотя Power Query может обрабатывать большие наборы данных, производительность может снизиться при слишком больших наборах, особенно если они превышают ограничение Excel на количество строк в 1 048 576. Рекомендуется фильтровать или суммировать данные перед импортом, чтобы повысить производительность обработки.

Источник и изображения