Nylig diskuterte vi bruken av FILTER.XML-funksjonen for å importere XML-data fra Internett – hovedoppgaven som denne funksjonen faktisk er ment for. Underveis har imidlertid en annen uventet og vakker bruk av denne funksjonen dukket opp – for raskt å dele opp klebrig tekst i celler.
La oss si at vi har en datakolonne som denne:
Selvfølgelig, for enkelhets skyld, vil jeg dele det inn i separate kolonner: firmanavn, by, gate, hus. Du kan gjøre dette på en rekke forskjellige måter:
- Bruk Tekst etter kolonner fra fanen Data (Data – tekst til kolonner) og gå tre trinn Tekstfortolker. Men hvis dataene endres i morgen, må du gjenta hele prosessen på nytt.
- Last disse dataene inn i Power Query og del dem der, og last dem deretter opp tilbake til arket, og oppdater deretter spørringen når dataene endres (noe som allerede er enklere).
- Hvis du trenger å oppdatere med en gang, kan du skrive noen veldig komplekse formler for å finne kommaer og trekke ut teksten mellom dem.
Og du kan gjøre det mer elegant og bruke FILTER.XML-funksjonen, men hva har det med det å gjøre?
FILTER.XML-funksjonen mottar som sitt første argument en XML-kode – tekst merket med spesielle tagger og attributter, og deretter analyserer den inn i komponentene, og trekker ut datafragmentene vi trenger. XML-koden ser vanligvis omtrent slik ut:
I XML må hvert dataelement være omsluttet av tagger. En tag er tekst (i eksemplet ovenfor er det manager, navn, profitt) omsluttet av vinkelparenteser. Etiketter kommer alltid i par – åpning og lukking (med en skråstrek lagt til i begynnelsen).
FILTER.XML-funksjonen kan enkelt trekke ut innholdet i alle taggene vi trenger, for eksempel navnene på alle ledere, og (viktigst) vise dem alle samtidig i én liste. Så vår oppgave er å legge til tagger til kildeteksten, gjøre den om til XML-kode som er egnet for påfølgende analyse med FILTER.XML-funksjonen.
Hvis vi tar den første adressen fra listen vår som et eksempel, må vi gjøre den om til denne konstruksjonen:
Jeg ringte den globale åpningen og lukkingen av all tekst-tag t, og kodene som rammer inn hvert element er s., men du kan bruke alle andre betegnelser - det spiller ingen rolle.
Hvis vi fjerner innrykk og linjeskift fra denne koden – helt, forresten, valgfri og bare lagt til for klarhetens skyld, så blir alt dette til en linje:
Og det kan allerede relativt enkelt fås fra kildeadressen ved å erstatte komma i den med et par tagger ved hjelp av funksjonen ERSTATNING (ERSTATNING) og liming med symbolet & på begynnelsen og slutten av åpnings- og avslutningstaggene:
For å utvide det resulterende området horisontalt bruker vi standardfunksjonen TRANSP (TRANSPOSER)pakker inn formelen vår:
En viktig funksjon i hele denne designen er at i den nye versjonen av Office 2021 og Office 365 med støtte for dynamiske arrays, kreves det ingen spesielle bevegelser for inndata – bare skriv inn og klikk på Enter – selve formelen opptar antall celler den trenger, og alt fungerer med et smell. I tidligere versjoner, hvor det ikke var noen dynamiske matriser ennå, må du først velge et tilstrekkelig antall tomme celler før du skriver inn formelen (du kan med en margin), og etter å ha opprettet formelen, trykk på hurtigtasten Ctrl+Skift+Enterfor å angi den som en matriseformel.
Et lignende triks kan brukes når du skiller tekst som sitter fast i én celle gjennom et linjeskift:
Den eneste forskjellen med det forrige eksemplet er at i stedet for et komma, erstatter vi her det usynlige Alt + Enter linjeskifttegnet, som kan spesifiseres i formelen ved hjelp av CHAR-funksjonen med kode 10.
- Finessene ved å jobbe med linjeskift (Alt + Enter) i Excel
- Del tekst etter kolonner i Excel
- Erstatter tekst med ERSTATT