Nedavno smo raspravljali o korištenju funkcije FILTER.XML za uvoz XML podataka s interneta – glavnom zadatku za koji je ova funkcija, zapravo, i namijenjena. Usput se, međutim, pojavila još jedna neočekivana i lijepa upotreba ove funkcije – za brzo dijeljenje ljepljivog teksta u ćelije.
Recimo da imamo stupac podataka poput ovog:
Naravno, radi praktičnosti, želio bih ga podijeliti u zasebne stupce: naziv tvrtke, grad, ulica, kuća. To možete učiniti na mnogo različitih načina:
- Koristiti Tekst po stupcima s kartice Datum (Podaci — tekst u stupce) i idi tri koraka Parser teksta. Ali ako se podaci sutra promijene, morat ćete ponovno ponoviti cijeli postupak.
- Učitajte te podatke u Power Query i tamo ih podijelite, a zatim ih prenesite natrag na list, a zatim ažurirajte upit kada se podaci promijene (što je već lakše).
- Ako trebate ažurirati u hodu, možete napisati neke vrlo složene formule za pronalaženje zareza i izdvajanje teksta između njih.
A možete to napraviti i elegantnije i koristiti funkciju FILTER.XML, ali kakve ona veze ima s tim?
Funkcija FILTER.XML prima kao početni argument XML kod — tekst označen posebnim oznakama i atributima, a zatim ga raščlanjuje na njegove komponente, izdvajajući fragmente podataka koji su nam potrebni. XML kod obično izgleda ovako:
U XML-u, svaki element podataka mora biti okružen oznakama. Oznaka je neki tekst (u gornjem primjeru to je menadžer, ime, dobit) u uglastim zagradama. Oznake uvijek dolaze u paru – početna i završna (s kosom crtom na početku).
Funkcija FILTER.XML može lako izdvojiti sadržaj svih oznaka koje su nam potrebne, na primjer, imena svih menadžera, i (što je najvažnije) prikazati ih sve odjednom na jednom popisu. Stoga je naš zadatak dodati oznake izvornom tekstu, pretvarajući ga u XML kod prikladan za naknadnu analizu pomoću funkcije FILTER.XML.
Ako uzmemo prvu adresu s našeg popisa kao primjer, morat ćemo je pretvoriti u ovu konstrukciju:
Pozvao sam globalnu oznaku za otvaranje i zatvaranje svih tekstova t, a oznake koje uokviruju svaki element su s., ali možete koristiti bilo koje druge oznake – nije bitno.
Ako iz ovog koda uklonimo uvlake i prijelome redaka – potpuno, uzgred, neobavezno i dodano samo radi jasnoće, tada će se sve ovo pretvoriti u redak:
I već se relativno lako može dobiti iz adrese izvora tako da zareze u njoj zamijenite s nekoliko oznaka koristeći funkciju ZAMJENA (ZAMJENA) i lijepljenje sa simbolom & na početku i kraju početnih i završnih oznaka:
Za vodoravno proširenje rezultirajućeg raspona koristimo standardnu funkciju TRANSP (TRANSPONIRAJ), omatajući našu formulu u to:
Važna značajka cijelog ovog dizajna je da u novoj verziji sustava Office 2021 i Office 365 s podrškom za dinamičke nizove nisu potrebne posebne geste za unos – samo unesite i kliknite na ući – sama formula zauzima broj ćelija koje treba i sve radi s praskom. U prijašnjim verzijama, gdje još nije bilo dinamičkih nizova, morat ćete prvo odabrati dovoljan broj praznih ćelija prije unosa formule (možete s marginom), a nakon izrade formule pritisnite tipkovni prečac Ctrl+smjena+ućida ga unesete kao formulu polja.
Sličan trik može se upotrijebiti kada se tekst zalijepljen u jednu ćeliju odvaja prijelomom retka:
Jedina razlika u odnosu na prethodni primjer je u tome što umjesto zareza ovdje zamjenjujemo nevidljivi znak za prijelom retka Alt + Enter, koji se može navesti u formuli pomoću funkcije CHAR s kodom 10.
- Suptilnosti rada s prijelomima redaka (Alt + Enter) u Excelu
- Podijelite tekst po stupcima u Excelu
- Zamjena teksta sa ZAMJENA