Совсем недавно мы обсуждали использование функции FILTER.XML для импорта XML-данных из Интернета – основная задача, для которой, собственно, и предназначена эта функция. Однако попутно всплыло еще одно неожиданное и красивое применение этой функции — для быстрого разделения закреплённого текста на ячейки.
Допустим, у нас есть такой столбец данных:
Конечно, для удобства хотелось бы разбить его на отдельные столбцы: название компании, город, улица, дом. Вы можете сделать это разными способами:
- Используйте Текст по столбцам на вкладке Данные (Данные — текст в столбцы) и пройди три шага Анализатор текста. Но если завтра данные изменятся, вам придется повторить весь процесс заново.
- Загрузите эти данные в Power Query и разделите их там, а затем загрузите обратно на лист, а затем обновите запрос при изменении данных (что уже проще).
- Если вам нужно обновиться на лету, то вы можете написать несколько очень сложных формул для поиска запятых и извлечения текста между ними.
А можно сделать более изящно и использовать функцию FILTER.XML, но причем тут она?
Функция FILTER.XML получает в качестве начального аргумента XML-код — текст, размеченный специальными тегами и атрибутами, а затем разбирает его на составляющие, извлекая нужные нам фрагменты данных. XML-код обычно выглядит примерно так:
В XML каждый элемент данных должен быть заключен в теги. Тег — это некоторый текст (в примере выше это менеджер, имя, прибыль), заключенный в угловые скобки. Теги всегда идут парами – открывающие и закрывающие (с косой чертой в начале).
Функция FILTER.XML легко может извлечь содержимое всех нужных нам тегов, например, имена всех менеджеров, и (самое главное) отобразить их все сразу в одном списке. Итак, наша задача — добавить теги к исходному тексту, превратив его в XML-код, пригодный для последующего анализа функцией FILTER.XML.
Если в качестве примера взять первый адрес из нашего списка, то нам нужно будет превратить его в такую конструкцию:
Я назвал глобальное открытие и закрытие всего текстового тега t, а теги, обрамляющие каждый элемент, s., но вы можете использовать любые другие обозначения – не имеет значения.
Если убрать из этого кода отступы и переносы строк — полностью, кстати, необязательно и добавлено лишь для наглядности, то всё это превратится в строку:
И его уже сравнительно легко можно получить из адреса источника, заменив в нем запятые на пару тегов используя функцию ЗАМЕНА (ЗАМЕНЯТЬ) и приклеиваем символ & в начале и конце открывающего и закрывающего тегов:
Чтобы расширить полученный диапазон по горизонтали, воспользуемся стандартной функцией ТРАНСПОРТ (ТРАНСПОН), завернув в него нашу формулу:
Важной особенностью всей этой конструкции является то, что в новой версии Office 2021 и Office 365 с поддержкой динамических массивов для ввода не требуется никаких специальных жестов — просто введите и нажмите Enter — сама формула занимает необходимое ей количество ячеек и все работает на ура. В предыдущих версиях, где еще не было динамических массивов, перед вводом формулы нужно будет сначала выделить достаточное количество пустых ячеек (можно с запасом), а после создания формулы нажать сочетание клавиш Ctrl+Shift+Enterчтобы ввести его как формулу массива.
Подобный прием можно использовать и при разделении склеенного в одну ячейку текста через перенос строки:
Единственное отличие от предыдущего примера состоит в том, что здесь вместо запятой мы заменяем невидимый символ переноса строки Alt+Enter, который можно указать в формуле с помощью функции CHAR с кодом 10.
- Тонкости работы с разрывами строк (Alt+Enter) в Excel
- Разделить текст по столбцам в Excel
- Замена текста на ЗАМЕНИТЬ