Hiljuti arutasime funktsiooni FILTER.XML kasutamist XML-andmete importimiseks Internetist - see on peamine ülesanne, mille jaoks see funktsioon tegelikult on mõeldud. Teel on aga esile kerkinud veel üks ootamatu ja ilus selle funktsiooni kasutus – kleepuva teksti kiireks lahtriteks jagamiseks.
Oletame, et meil on selline andmeveerg:
Muidugi tahaksin mugavuse huvides selle jagada eraldi veergudeks: ettevõtte nimi, linn, tänav, maja. Saate seda teha mitmel erineval viisil:
- Kasutama Tekst veergude kaupa vahekaardilt kuupäev (Andmed – tekst veergudesse) ja astu kolm sammu Teksti parser. Kuid kui andmed homme muutuvad, peate kogu protsessi uuesti kordama.
- Laadige need andmed Power Querysse ja jagage need seal ning seejärel laadige need lehele tagasi ja seejärel värskendage päringut, kui andmed muutuvad (mis on juba lihtsam).
- Kui teil on vaja lennult värskendada, võite kirjutada väga keerulised valemid, et leida komasid ja eraldada nendevaheline tekst.
Ja saate seda teha elegantsemalt ja kasutada funktsiooni FILTER.XML, kuid mis on sellel pistmist?
Funktsioon FILTER.XML saab oma esialgse argumendina XML-koodi – teksti, mis on tähistatud spetsiaalsete siltide ja atribuutidega, ning seejärel analüüsib selle oma komponentideks, eraldades meile vajalikud andmefragmendid. XML-kood näeb tavaliselt välja umbes selline:
XML-is peab iga andmeelement olema märgendites. Silt on tekst (ülaltoodud näites on see juht, nimi, kasum), mis on suletud nurksulgudesse. Sildid tulevad alati paarikaupa – avavad ja sulgevad (algusesse lisatud kaldkriips).
Funktsioon FILTER.XML saab hõlpsasti eraldada kõigi meile vajalike siltide sisu, näiteks kõigi haldurite nimed, ja (mis kõige tähtsam) kuvada need kõik korraga ühes loendis. Seega on meie ülesanne lisada lähtetekstile sildid, muutes selle XML-koodiks, mis sobib järgnevaks analüüsiks funktsiooni FILTER.XML abil.
Kui võtame näitena oma loendi esimese aadressi, peame selle muutma selliseks konstruktsiooniks:
Helistasin globaalseks avamise ja sulgemise kogu tekstisildiks t, ja iga elementi raamivad sildid on s., kuid võite kasutada ka muid nimetusi – see pole oluline.
Kui eemaldame sellest koodist taanded ja reavahetused – täiesti, muide, valikulised ja lisame ainult selguse huvides, muutub see kõik reaks:
Ja selle saab juba suhteliselt lihtsalt kätte lähteaadressilt, kui asendada selles komad paari märgendiga funktsiooni kasutades ASENDA (ASENDAJA) ja sümboliga liimimine & algus- ja sulgemärgendi alguses ja lõpus:
Saadud vahemiku horisontaalseks laiendamiseks kasutame standardfunktsiooni ÜLEKANDMINE (TRANSPOSEERIDA), mähkides sellesse meie valemi:
Kogu selle disaini oluline omadus on see, et Office 2021 ja Office 365 uues versioonis, mis toetab dünaamilisi massiive, pole sisestamiseks vaja erilisi žeste – lihtsalt sisestage ja klõpsake sisene – valem ise hõivab vajaliku arvu lahtreid ja kõik töötab pauguga. Varasemates versioonides, kus dünaamilisi massiive veel polnud, peate enne valemi sisestamist valima piisava arvu tühje lahtreid (saate veerisega) ja pärast valemi loomist vajutage kiirklahvi Ctrl+nihe+siseneet sisestada see massiivivalemina.
Sarnast nippi saab kasutada reavahetuse kaudu ühte lahtrisse kokkukleepunud teksti eraldamisel:
Ainus erinevus eelmise näitega on see, et koma asemel asendame siin nähtamatu Alt + Enter reavahetuse märgi, mille saab määrata valemis funktsiooni CHAR abil koodiga 10.
- Reavahetustega (Alt + Enter) töötamise peensused Excelis
- Jagage tekst Excelis veergude kaupa
- Teksti asendamine tekstiga SUBSTITUTE