Деление слипшегося текста функцией ФИЛЬТР.XML
Совсем недавно мы разбирали применение функции ФИЛЬТР.XML для импорта XML-данных из интернета - основной задачи, для которой эта функция, собственно, и предназначена. Попутно, однако, всплыло ещё одно неожиданное и красивое применение этой функции - для быстрого деления слипшегося текста по ячейкам.
Предположим, что у нас имеется вот такой столбец с данными:
Само-собой, для удобства хотелось бы разделить его на отдельные колонки: название компании, город, улица, дом. Сделать это можно кучей разных способов:
- Использовать Текст по столбцам с вкладки Данные (Data - Text to columns) и пройти три шага Мастера разбора текстов. Но если завтра данные поменяются, то придётся повторить весь процесс заново.
- Загрузить эти данные в Power Query и поделить там, а затем выгрузить обратно на лист, а потом при изменении данных обновлять запрос (что уже проще).
- Если нужно обновление "на лету", то можно написать несколько весьма сложных формул для поиска запятых и извлечения текста между ними.
А можно поступить изящнее и использовать функцию ФИЛЬТР.XML, но причем тут она?
Функция ФИЛЬТР.XML получает в качестве исходного аргумента XML-код - текст, размеченный специальными тегами и атрибутами, и затем разбирает его на составляющие, извлекая нужные нам фрагменты данных. Обычно XML-код выглядит как-то так:
В XML каждый элемент данных должен быть заключен в теги. Тег - это некий текст (в примере выше это manager, name, profit), заключенный в угловые скобки. Теги всегда идут в паре - открывающий и закрывающий (с добавленным в начало слэшем).
Функция ФИЛЬТР.XML может легко извлечь содержимое всех нужных нам тегов, например, имена всех менеджеров и (самое главное) выдать их сразу всех одним списком. Так что наша задача сводится к тому, чтобы добавить в исходный текст теги, превратив его в XML-код, пригодный для последующего анализа функцией ФИЛЬТР.XML.
Если взять для примера первый адрес из нашего списка, то превратить его нужно будет вот в такую конструкцию:
Глобальный открывающий и закрывающий весь текст тег я назвал t, а теги обрамляющие каждый элемент - s., но можно использовать и любые другие обозначения - это не принципиально.
Если убрать из этого кода отступы и переносы строк - совершенно, кстати, необязательные и добавленные только для наглядности, то всё это превратится в строку:
А её уже можно относительно легко получить из исходного адреса, заменив в нём запятые на пару тегов </s><s> с помощью функции ПОДСТАВИТЬ (SUBSTITUTE) и приклеив с помощью символа & в начало и конец открывающие и закрывающие теги:
Чтобы развернуть полученный диапазон горизонтально, используем стандартную функцию ТРАНСП (TRANSPOSE), завернув в неё нашу формулу:
Важной особенностью всей этой конструкции является то, что в новой версии Office 2021 и Office 365 с поддержкой динамических массивов никаких специальных телодвижений для ввода не требуется - просто вводим и жмём на Enter - формула сама занимает нужное ей количество ячеек и всё работает "на ура". В предыдущих же версиях, где динамических массивов ещё не было, потребуется перед вводом формулы сначала выделить достаточное количество пустых ячеек (можно с запасом), а после создания формулы - нажать на сочетание клавиш Ctrl+Shift+Enter, чтобы ввести её как формулу массива.
Похожий трюк можно использовать и при разделении текста слипшегося в одну ячейку через перенос строки:
Разница с предыдущим примером только в том, что вместо запятой мы заменяем здесь невидимый символ переноса строки Alt+Enter, который в формуле можно задать с помощью функции СИМВОЛ (CHAR) с кодом 10.
Ссылки по теме
- Тонкости работы с переносами строк (Alt+Enter) в Excel
- Деление текста по столбцам в Excel
- Замена текста функцией ПОДСТАВИТЬ
Давно искал относительно простое решение для разделения текста. Спасибо вам.
Но у меня вопрос.
Например, я выделил 5 пустых ячеек для массива, но разделяемая ячейка содержит данные только для 3-х. После применения формулы, в 2-е оставшиеся ячейки выводится #Н/Д. Как сделать чтобы лишние ячейки оставались пустыми?
В результате выполнения формулы почему-то выводится только первое слово до запятой. С чем может быть связано?
П.С. если выполнять формулу в файле с примером - точно так же, только первое слово до запятой
Обычно для таких вещей использую Данные/"Текст по столбцам"
вот есть ответ из банка:
<Currency Id="431" fromDate="01/01/2023" toDate="01/12/2023">
<Record Date="01/01/2023">
<Rate>2.7364</Rate>
</Record></Currency>
раньше работала команда -- ={ФИЛЬТР.XML(D$4;"//Rate";)} -- давала этот курс. Сейчас она же выдает -- 1995718.
Что это за миллионы ? Где в статье синтаксис этого запроса ?
вообще странно. Понятно, что эта команда -- массив. И его середину нельзя изменить. И через 10 значений действительно выдает правильный курс. Потом опять ошибки-миллионы. Не может же банк выдавать эти ошибки.