Деление слипшегося текста функцией ФИЛЬТР.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 отрабатывает только первое восхождение, но если ставлю в функции ПОДСТАВИТЬ необязательный последний аргумент, то отрабатывает всю строку, НО фильтр не работает, то есть всё значение ячейки также помещает в одну ячейку, с учетом формулы массива повторяет значение во всех выделенных ячейках...
07.11.2021 21:03:34
Добрый день!
В результате выполнения формулы почему-то выводится только первое слово до запятой. С чем может быть связано?
П.С. если выполнять формулу в файле с примером - точно так же, только первое слово до запятой
04.02.2023 22:09:15
Привет! Тоже долго не мог понять, стояла вроде как последняя версия Excel, думал что в статье о чем-то забыли упомянуть, но потом стало ясно. Главное в правильной последовательности сделать то, что описано в статье. 1. Перед вставкой формулы выделить ячейки куда будут заноситься данные после разделения(Именно в этом причина того, почему выводилось только до первой запятой). 2. В строку вставить формулу. 3.Сочетание клавиш для массива из статьи.
20.12.2021 22:29:51
Добрый день!

Обычно для таких вещей использую Данные/"Текст по столбцам"
12.01.2023 05:35:03
как то все сложно в статье. Заменять одно на другое ...
вот есть ответ из банка:
<?xml version="1.0" encoding="utf-8"?>
<Currency Id="431" fromDate="01/01/2023" toDate="01/12/2023">
<Record Date="01/01/2023">
<Rate>2.7364</Rate>
</Record></Currency>
мне нужно получить цифру -- 2.7364
раньше работала команда -- ={ФИЛЬТР.XML(D$4;"//Rate";)}  -- давала этот курс.  Сейчас она же выдает -- 1995718.
Что это за миллионы ?  Где в статье синтаксис этого запроса ?


вообще странно. Понятно, что эта команда -- массив. И его середину нельзя изменить. И через 10 значений действительно выдает правильный курс. Потом опять ошибки-миллионы. Не может же банк выдавать эти ошибки.
05.12.2023 12:55:16
ну это миллионы дней, прошедших с января 1900 года... попробуйте так (тонкий намёк на то, что происходит):
--ТЕКСТ(ФИЛЬТР.XML(A1;"//Rate");"М,ГГГГ")
04.06.2024 16:29:57
Не могу понять как в Варианте 2 разбить текст не в столбик внизу, а в ячеки слева.

Нашел, понял.
Наверх