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

15 сентября 2023 г.

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

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

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

Перейти к:

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

Почему вам следует использовать 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.

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

Рисунок А

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

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

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

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

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

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

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

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

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

Рисунок Б

Существует несколько вариантов извлечения.

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

Рисунок С

Введите символ дефиса в качестве разделителя.

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

Рисунок D

Эта опция возвращает только один символ для каждой строки.

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

Рисунок Е

Просто выберите его и нажмите «Удалить».

СМОТРИТЕ: Как найти дубликаты с помощью Microsoft Power Query.

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

Рисунок F

Эта опция возвращает все символы после первого символа дефиса.

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

Рисунок G

Введите начальный и конечный разделитель.

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

Рисунок Н

Последняя опция возвращает символы между двумя символами дефиса.

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

Как извлечь строки с разделителями, используя разделенный столбец в Power Query

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

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

Рисунок I

Выберите вариант, в котором используются все символы-разделители.

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

Рисунок J

Эта опция разделяет три раздела на три столбца.

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

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

---

Оригинал