Деление слипшегося текста функцией ФИЛЬТР.XML

Совсем недавно мы разбирали применение функции ФИЛЬТР.XML для импорта XML-данных из интернета - основной задачи, для которой эта функция, собственно, и предназначена. Попутно, однако, всплыло ещё одно неожиданное и красивое применение этой функции - для быстрого деления слипшегося текста по ячейкам.

Предположим, что у нас имеется вот такой столбец с данными:

Исходный слипшийся текст

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

  • Использовать Текст по столбцам с вкладки Данные (Data - Text to columns) и пройти три шага Мастера разбора текстов. Но если завтра данные поменяются, то придётся повторить весь процесс заново.
  • Загрузить эти данные в Power Query и поделить там, а затем выгрузить обратно на лист, а потом при изменении данных обновлять запрос (что уже проще).
  • Если нужно обновление "на лету", то можно написать несколько весьма сложных формул для поиска запятых и извлечения текста между ними.

А можно поступить изящнее и использовать функцию ФИЛЬТР.XML, но причем тут она?

Функция ФИЛЬТР.XML получает в качестве исходного аргумента XML-код - текст, размеченный специальными тегами и атрибутами, и затем разбирает его на составляющие, извлекая нужные нам фрагменты данных. Обычно XML-код выглядит как-то так:

Пример XML кода

В XML каждый элемент данных должен быть заключен в теги. Тег - это некий текст (в примере выше это manager, name, profit), заключенный в угловые скобки. Теги всегда идут в паре - открывающий и закрывающий (с добавленным в начало слэшем).

Функция ФИЛЬТР.XML может легко извлечь содержимое всех нужных нам тегов, например, имена всех менеджеров и (самое главное) выдать их сразу всех одним списком. Так что наша задача сводится к тому, чтобы добавить в исходный текст теги, превратив его в XML-код, пригодный для последующего анализа функцией ФИЛЬТР.XML.

Если взять для примера первый адрес из нашего списка, то превратить его нужно будет вот в такую конструкцию:

Адрес с добавленными тегами

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

Если убрать из этого кода отступы и переносы строк - совершенно, кстати, необязательные и добавленные только для наглядности, то всё это превратится в строку:

XML код в одну строку

А её уже можно относительно легко получить из исходного адреса, заменив в нём запятые на пару тегов </s><s> с помощью функции ПОДСТАВИТЬ (SUBSTITUTE) и приклеив с помощью символа & в начало и конец открывающие и закрывающие теги:

Добавляем функцию ФИЛЬТР.XML

Чтобы развернуть полученный диапазон горизонтально, используем стандартную функцию ТРАНСП (TRANSPOSE), завернув в неё нашу формулу:

Готовая формула

Важной особенностью всей этой конструкции является то, что в новой версии Office 2021 и Office 365 с поддержкой динамических массивов никаких специальных телодвижений для ввода не требуется - просто вводим и жмём на Enter - формула сама занимает нужное ей количество ячеек и всё работает "на ура". В предыдущих же версиях, где динамических массивов ещё не было, потребуется перед вводом формулы сначала выделить достаточное количество пустых ячеек (можно с запасом), а после создания формулы - нажать на сочетание клавиш Ctrl+Shift+Enter, чтобы ввести её как формулу массива.

Похожий трюк можно использовать и при разделении текста слипшегося в одну ячейку через перенос строки:

Разделение текста через перенос строки

Разница с предыдущим примером только в том, что вместо запятой мы заменяем здесь невидимый символ переноса строки Alt+Enter, который в формуле можно задать с помощью функции СИМВОЛ (CHAR) с кодом 10.

Ссылки по теме




19.09.2021 09:57:20
Добрый день.
Давно искал относительно простое решение для разделения текста. Спасибо вам.
Но у меня вопрос.
Например, я выделил 5 пустых ячеек для массива, но разделяемая ячейка содержит данные только для 3-х. После применения формулы, в 2-е оставшиеся ячейки выводится #Н/Д. Как сделать чтобы лишние ячейки оставались пустыми?
20.09.2021 08:48:44
Оберните всю формулу в =ЕСЛИОШИБКА(Ваша формула; что показывать вместо #Н/Д например "")
24.09.2021 15:50:30
А можно сделать так, чтобы выделяемые xml-фильтром значения еще обрабатывались сразу (например ВПРом)?
07.10.2021 07:05:58
Доброго времени суток! Николай больше спасибо за огромный и столь нужный труд по всем видео в части работы в Excel! Пробуя практиковаться на основе этого видео у меня возникли несколько проблем (установлен Office 2016 professional+): первая -функция фильтр.xml отрабатывает только первое восхождение, но если ставлю в функции ПОДСТАВИТЬ необязательный последний аргумент, то отрабатывает всю строку, НО фильтр не работает, то есть всё значение ячейки также помещает в одну ячейку, с учетом формулы массива повторяет значение во всех выделенных ячейках...
Наверх