Как извлечь данные с разделителями с помощью Excel Power Query

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

Вы можете получить внешние данные в виде связанных вместе символов, которые необходимо импортировать в Microsoft Excel. Обычно вы импортируете весь набор данных, даже если вам нужна только часть строки.

Например, вы можете получить список номеров транзакций, который частично содержит идентификационный номер клиента. Вам нужны только части клиентов в Excel, чтобы создать связь между этим клиентом и таблицей, содержащей имена клиентов. Таким образом, при составлении отчетов зрители будут видеть имена клиентов, а не бессмысленный номер.

В этом случае вы можете использовать функции извлечения и разделения столбцов Power Query для извлечения строк с разделителями в их компоненты. Я использую Microsoft 365 Desktop и Power Query в Microsoft Excel. Power Query доступен в более старых версиях Excel 10. Вы можете скачать демонстрационный файл Microsoft Excel для этого урока.

Перейти к:

Почему вам следует использовать Power Query в Excel

Вы можете использовать строковые функции Excel, «Текст в столбцы» или «Быстрая заливка», но есть причины, по которым вы не можете этого делать:

• Текст в столбцы записывается поверх исходных данных.
• Ваши данные могут находиться не в Excel, и хотя Power Query доступен в Excel, Power Query может импортировать данные из множества источников, а не только из Excel.
• Исходные данные содержат больше строк, которые можно импортировать в Excel.
• Возможно, вам придется использовать Power Query для чего-то гораздо более сложного, и извлечение подмножества исходной записи — это лишь первый шаг.

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

Как получить данные в Power Query

Мы будем работать с простым листом Excel с несколькими строками-разделителями в таблице с именем TableCustomerID. Вам не нужно заменять имя таблицы по умолчанию, но со значимыми именами легче работать, если у вас несколько таблиц. Однако данные должны быть отформатированы как объект Table. Если это не так, Power Query предложит вам преобразовать диапазон данных.

СМОТРИТЕ: Вот как создать и заполнить таблицу в Power Query Microsoft Excel.

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

Рисунок А

Первым шагом является загрузка данных в Power Query следующим образом:

1. Щелкните в любом месте таблицы.

2. Откройте вкладку Данные.

3. В группе «Получить и преобразовать данные» нажмите «Из таблицы/диапазона».

Вот и все. Простая таблица, показанная на Рисунок А теперь находится в Power Query.

Имея данные в Power Query, вы можете начать извлекать разделы.

Как извлечь строки с разделителями с помощью параметров извлечения в Power Query

На самом деле существует два способа извлечения данных в Power Query. Мы начнем с использования параметров Extract, которые возвращают подмножество исходного значения. Начать:

1. Откройте вкладку «Преобразование».
2. Нажмите раскрывающийся список «Извлечь» в группе «Текстовый столбец». Как вы можете видеть в Рисунок Б, есть несколько вариантов, и все они по большей части говорят сами за себя. Мы рассмотрим параметры разделителей, чтобы вы могли увидеть, что делает каждый из них.

Рисунок Б

3. Щелкнув заголовок поля «Идентификатор клиента» для выбора столбца, при необходимости перейдите на вкладку «Преобразование».
4. Нажмите «Извлечь» в группе текстовых столбцов.
5. Выберите параметр «Текст перед разделителем». В появившемся диалоговом окне введите символ дефиса (Рисунок С) и нажмите «ОК».

Рисунок С

Как вы можете видеть в Рисунок Dэта опция возвращает только первый символ(ы) перед разделителем.

Рисунок D

Чтобы вернуть исходные данные, удалите шаг «Извлеченный текст перед разделителем» на панели «Примененные шаги», показанной на рис. Цифры Е щелкнув элемент правой кнопкой мыши и выбрав «Удалить» в контекстном меню.

Цифры Е

ВИДЕТЬ: Как найти дубликаты с помощью Microsoft Power Query.

Теперь давайте сделаем то же самое со следующей опцией «Текст после разделителя». При появлении запроса введите символ дефиса и нажмите «ОК», чтобы увидеть результаты, показанные на рисунке. Рисунок F. На этот раз Power Query удаляет первые два символа: первое число и первый дефис.

Рисунок F

Еще раз восстановите исходные данные, удалив шаг извлечения и выбрав параметр «Текст между разделителями». На этот раз Power Query запрашивает два разделителя. В данном случае это оба символа дефиса (Рисунок G).

Рисунок G

Нажмите «ОК», чтобы увидеть результаты, показанные на Рисунок Н.

Рисунок Н

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

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

1. После выбора столбца перейдите на вкладку «Главная».
2. В группе «Преобразование» нажмите «Разделить столбец».
3. Выберите первый вариант «По разделителю».
4. В появившемся диалоговом окне вам не нужно ничего делать, поскольку Power Query хорошо распознает ваши потребности. Убедитесь, что Power Query выбирает каждое вхождение разделителя в разделе «Разделить на» (Рисунок I).

Рисунок I

5. Нажмите «ОК», чтобы увидеть результаты в Рисунок J.

Рисунок J

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

Как видите, функции «Извлечение» и «Разделить столбец» помогают быстро разделить данные. Вы, вероятно, столкнетесь с применением обоих.