Äskettäin keskustelimme FILTER.XML-funktion käytöstä XML-tietojen tuomiseen Internetistä – päätehtävästä, johon tämä toiminto itse asiassa on tarkoitettu. Matkan varrella on kuitenkin noussut esiin toinen odottamaton ja kaunis käyttötarkoitus tälle toiminnolle – tahmean tekstin nopeaan jakamiseen soluihin.
Oletetaan, että meillä on tällainen tietosarake:
Tietenkin mukavuuden vuoksi haluaisin jakaa sen erillisiin sarakkeisiin: yrityksen nimi, kaupunki, katu, talo. Voit tehdä tämän monilla eri tavoilla:
- Käyttää Teksti sarakkeittain välilehdeltä Päiväys (Data – Teksti sarakkeisiin) ja mennä kolme askelta Tekstin jäsentäjä. Mutta jos tiedot muuttuvat huomenna, sinun on toistettava koko prosessi uudelleen.
- Lataa nämä tiedot Power Queryyn ja jaa se siellä ja lataa se sitten takaisin taulukkoon ja päivitä kysely tietojen muuttuessa (mikä on jo helpompaa).
- Jos sinun on päivitettävä lennossa, voit kirjoittaa erittäin monimutkaisia kaavoja löytääksesi pilkkuja ja poimiaksesi niiden välisen tekstin.
Ja voit tehdä sen tyylikkäämmin ja käyttää FILTER.XML-toimintoa, mutta mitä tekemistä sillä on sen kanssa?
FILTER.XML-funktio vastaanottaa alkuperäisenä argumenttinaan XML-koodin – tekstin, joka on merkitty erityisillä tunnisteilla ja attribuutteilla, ja jäsentää sen sitten komponentteikseen poimien tarvitsemamme datafragmentit. XML-koodi näyttää yleensä tältä:
XML:ssä jokainen tietoelementti on suljettava tunnisteisiin. Tunniste on tekstiä (yllä olevassa esimerkissä se on manageri, nimi, voitto), joka on suljettu kulmasuluissa. Tunnisteet tulevat aina pareittain – avautuvat ja sulkeutuvat (alkuun lisätään vinoviiva).
FILTER.XML-toiminto voi helposti poimia kaikkien tarvitsemiemme tunnisteiden sisällön, esimerkiksi kaikkien johtajien nimet, ja (mikä tärkeintä) näyttää ne kaikki kerralla yhdessä luettelossa. Tehtävämme on siis lisätä lähdetekstiin tunnisteita ja muuttaa se XML-koodiksi, joka soveltuu myöhempään analyysiin FILTER.XML-funktiolla.
Jos otamme esimerkkinä luettelon ensimmäisen osoitteen, meidän on muutettava se tähän rakenteeseen:
Soitin globaalille avaus- ja sulkemistekstitunnisteen t, ja kunkin elementin kehystävät tagit ovat s., mutta voit käyttää mitä tahansa muita nimityksiä – sillä ei ole väliä.
Jos poistamme sisennykset ja rivinvaihdot tästä koodista - muuten täysin valinnaisia ja lisätty vain selvyyden vuoksi, kaikki tämä muuttuu riviksi:
Ja sen saa jo suhteellisen helposti lähdeosoitteesta korvaamalla siinä olevat pilkut parilla tagilla funktiota käyttämällä SUBSTITUTE (KORVAA) ja liimaamalla symbolilla & alku- ja lopputunnisteiden alussa ja lopussa:
Laajentaaksemme tuloksena olevaa aluetta vaakasuunnassa, käytämme vakiotoimintoa TRANSP (TRANSPOSI), kääri kaavamme siihen:
Tärkeä piirre tässä koko suunnittelussa on, että Office 2021:n ja Office 365:n uudessa versiossa, joka tukee dynaamisia taulukoita, syöttämiseen ei tarvita erityisiä eleitä – syötä vain ja napsauta enter – itse kaava täyttää tarvitsemansa määrän soluja ja kaikki toimii räjähdysmäisesti. Aiemmissa versioissa, joissa ei vielä ollut dynaamisia taulukoita, sinun on ensin valittava riittävä määrä tyhjiä soluja ennen kaavan syöttämistä (voit tehdä marginaalilla) ja painaa kaavan luomisen jälkeen pikanäppäintä Ctrl+siirtyminen+entersyöttääksesi sen taulukkokaavana.
Samanlaista temppua voidaan käyttää erotettaessa yhteen soluun tarttunutta tekstiä rivinvaihdolla:
Ainoa ero edelliseen esimerkkiin on, että pilkun sijaan tässä korvataan näkymätön Alt + Enter rivinvaihtomerkki, joka voidaan määrittää kaavassa CHAR-funktiolla koodilla 10.
- Rivinvaihtojen (Alt + Enter) työskentelyn hienoudet Excelissä
- Jaa teksti sarakkeiksi Excelissä
- Teksti korvataan tekstillä SUBSTITUTE