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