Pred kratkim smo razpravljali o uporabi funkcije FILTER.XML za uvoz XML podatkov iz interneta – glavni nalogi, ki ji je ta funkcija pravzaprav namenjena. Spotoma pa se je pojavila še ena nepričakovana in lepa uporaba te funkcije – za hitro razdelitev lepljivega besedila v celice.
Recimo, da imamo stolpec s podatki, kot je ta:
Seveda bi ga zaradi udobja rad razdelil v ločene stolpce: ime podjetja, mesto, ulica, hiša. To lahko storite na več različnih načinov:
- Uporaba Besedilo po stolpcih iz zavihka datum (Podatki — besedilo v stolpce) in naredi tri korake Razčlenjevalnik besedila. Če pa se podatki jutri spremenijo, boste morali celoten postopek znova ponoviti.
- Te podatke naložite v Power Query in jih tam razdelite ter nato naložite nazaj na list in nato posodobite poizvedbo, ko se podatki spremenijo (kar je že lažje).
- Če morate posodabljati sproti, potem lahko napišete nekaj zelo zapletenih formul za iskanje vejic in ekstrahiranje besedila med njimi.
In lahko to naredite bolj elegantno in uporabite funkcijo FILTER.XML, ampak kaj ima to opraviti s tem?
Funkcija FILTER.XML kot svoj začetni argument prejme kodo XML – besedilo, označeno s posebnimi oznakami in atributi, nato pa ga razčleni na njegove komponente in izloči fragmente podatkov, ki jih potrebujemo. Koda XML običajno izgleda nekako takole:
V XML mora biti vsak podatkovni element obdan z oznakami. Oznaka je besedilo (v zgornjem primeru je vodja, ime, dobiček), zaprto v oglatih oklepajih. Oznake so vedno v paru – začetna in zapiralna (s poševnico na začetku).
Funkcija FILTER.XML zlahka izlušči vsebino vseh oznak, ki jih potrebujemo, na primer imena vseh upraviteljev, in (kar je najpomembneje) prikaže vse naenkrat na enem seznamu. Naša naloga je torej dodati oznake izvornemu besedilu in ga spremeniti v kodo XML, primerno za kasnejšo analizo s funkcijo FILTER.XML.
Če za primer vzamemo prvi naslov s seznama, ga bomo morali pretvoriti v to konstrukcijo:
Poklical sem globalno oznako za odpiranje in zapiranje celotnega besedila t, in oznake, ki uokvirjajo vsak element, so s., lahko pa uporabite poljubne druge oznake – ni pomembno.
Če odstranimo zamike in prelome vrstic iz te kode – popolnoma, mimogrede, neobvezno in dodano samo zaradi jasnosti, potem se bo vse to spremenilo v vrstico:
Razmeroma preprosto ga je mogoče dobiti že iz izvornega naslova, tako da vejice v njem zamenjate z nekaj oznakami uporabo funkcije NAMESTITEV (ZAMENJAVA) in lepljenje s simbolom & na začetku in koncu začetne in končne oznake:
Za vodoravno razširitev nastalega obsega uporabimo standardno funkcijo TRANSP (PRENOS)in vanjo zavijemo našo formulo:
Pomembna značilnost celotne zasnove je, da v novi različici Officea 2021 in Office 365 s podporo za dinamična polja za vnos niso potrebne nobene posebne poteze – samo vnesite in kliknite Vnesite – sama formula zasede toliko celic, kot jih potrebuje, in vse deluje z udarcem. V prejšnjih različicah, kjer še ni bilo dinamičnih nizov, boste morali pred vnosom formule najprej izbrati zadostno število praznih celic (lahko z robom) in po ustvarjanju formule pritisniti bližnjico na tipkovnici Ctrl+Shift+Vnesiteda ga vnesete kot matrično formulo.
Podoben trik je mogoče uporabiti pri ločevanju besedila, zlepljenega v eno celico, prek preloma vrstice:
Edina razlika s prejšnjim primerom je, da namesto vejice tukaj zamenjamo nevidni znak za prelom vrstice Alt + Enter, ki ga lahko v formuli podamo s funkcijo CHAR s kodo 10.
- Tankosti dela s prelomi vrstic (Alt + Enter) v Excelu
- Razdelite besedilo po stolpcih v Excelu
- Zamenjava besedila z SUBSTITUTE