Meer onlangs het ons die gebruik van die FILTER.XML-funksie bespreek om XML-data vanaf die internet in te voer – die hooftaak waarvoor hierdie funksie eintlik bedoel is. Langs die pad het 'n ander onverwagte en pragtige gebruik van hierdie funksie egter opgeduik - om taai teks vinnig in selle te verdeel.
Kom ons sê ons het 'n datakolom soos hierdie:
Natuurlik wil ek dit gerieflikheidshalwe in aparte kolomme verdeel: maatskappynaam, stad, straat, huis. Jy kan dit op 'n klomp verskillende maniere doen:
- Gebruik Teks volgens kolomme vanaf die blad data (Data – teks na kolomme) en gaan drie treë Teks ontleder. Maar as die data môre verander, sal jy die hele proses weer moet herhaal.
- Laai hierdie data in Power Query en verdeel dit daar, en laai dit dan terug na die blad, en werk dan die navraag op wanneer die data verander (wat reeds makliker is).
- As jy dadelik moet opdateer, kan jy 'n paar baie komplekse formules skryf om kommas te vind en die teks tussen hulle te onttrek.
En jy kan dit meer elegant doen en die FILTER.XML-funksie gebruik, maar wat het dit daarmee te doen?
Die FILTER.XML-funksie ontvang as sy aanvanklike argument 'n XML-kode - teks gemerk met spesiale merkers en eienskappe, en ontleed dit dan in sy komponente, en onttrek die datafragmente wat ons benodig. Die XML-kode lyk gewoonlik so:
In XML moet elke data-element in etikette ingesluit word. 'n Merker is 'n teks (in die voorbeeld hierbo is dit bestuurder, naam, wins) wat tussen hakies ingesluit is. Merkers kom altyd in pare - oop en toe (met 'n skuinsstreep bygevoeg aan die begin).
Die FILTER.XML-funksie kan maklik die inhoud van al die etikette wat ons nodig het, onttrek, byvoorbeeld die name van alle bestuurders, en (belangriker nog) hulle almal gelyktydig in een lys vertoon. Ons taak is dus om etikette by die bronteks te voeg en dit in XML-kode te verander wat geskik is vir daaropvolgende ontleding deur die FILTER.XML-funksie.
As ons die eerste adres uit ons lys as voorbeeld neem, sal ons dit in hierdie konstruksie moet verander:
Ek het die globale opening en sluiting van alle teksmerker gebel t, en die etikette wat elke element omraam is s., maar jy kan enige ander benamings gebruik – dit maak nie saak nie.
As ons inkepings en reëlbreuke uit hierdie kode verwyder - heeltemal, terloops, opsioneel en slegs bygevoeg vir duidelikheid, dan sal dit alles in 'n reël verander:
En dit kan reeds relatief maklik van die bronadres verkry word deur kommas daarin te vervang met 'n paar etikette die funksie te gebruik PLAASVERVANGER (PLAASVERVANG) en plak met die simbool & aan die begin en einde van die openings- en sluitingsetikette:
Om die gevolglike reeks horisontaal uit te brei, gebruik ons die standaardfunksie OORDRAG (TRANSPONEER), verpak ons formule daarin:
'n Belangrike kenmerk van hierdie hele ontwerp is dat in die nuwe weergawe van Office 2021 en Office 365 met ondersteuning vir dinamiese skikkings, geen spesiale gebare vir invoer nodig is nie - voer net in en klik op Tik – die formule self beslaan die aantal selle wat dit nodig het en alles werk met 'n knal. In vorige weergawes, waar daar nog geen dinamiese skikkings was nie, moet u eers 'n voldoende aantal leë selle kies voordat u die formule invoer (jy kan met 'n kantlyn), en nadat u die formule geskep het, druk die sleutelbordkortpad Ctrl+Skuif+Tikom dit as 'n skikkingsformule in te voer.
'n Soortgelyke truuk kan gebruik word wanneer teks wat in een sel vasgeplak is, geskei word deur 'n reëlbreuk:
Die enigste verskil met die vorige voorbeeld is dat in plaas van 'n komma, vervang ons hier die onsigbare Alt + Enter lynbreuk-karakter, wat in die formule gespesifiseer kan word deur die CHAR-funksie met kode 10 te gebruik.
- Die subtiliteite om met lynbreuke (Alt + Enter) in Excel te werk
- Verdeel teks deur kolomme in Excel
- Vervang tans teks met SUBSTITUT