Зусім нядаўна мы абмяркоўвалі выкарыстанне функцыі 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
- Замена тэксту на SUBSTITUTE