Visai neseniai aptarėme FILTER.XML funkcijos naudojimą XML duomenims importuoti iš interneto – tai pagrindinė užduotis, kuriai ši funkcija iš tikrųjų yra skirta. Tačiau pakeliui pasirodė dar vienas netikėtas ir gražus šios funkcijos panaudojimas – greitam lipniam tekstui skaidyti į langelius.
Tarkime, kad turime tokį duomenų stulpelį:
Žinoma, patogumo dėlei norėčiau suskirstyti į atskirus stulpelius: įmonės pavadinimas, miestas, gatvė, namas. Tai galite padaryti įvairiais būdais:
- naudojimas Tekstas pagal stulpelius iš skirtuko Duomenys (Duomenys – tekstas į stulpelius) ir eik tris žingsnius Teksto analizatorius. Bet jei rytoj duomenys pasikeis, visą procesą teks kartoti dar kartą.
- Įkelkite šiuos duomenis į „Power Query“ ir padalykite juos ten, tada įkelkite atgal į lapą, o tada atnaujinkite užklausą, kai pasikeis duomenys (tai jau lengviau).
- Jei jums reikia atnaujinti skrydžio metu, galite parašyti labai sudėtingas formules, kad rastumėte kablelius ir ištrauktumėte tarp jų esantį tekstą.
Ir jūs galite tai padaryti elegantiškiau ir naudoti FILTER.XML funkciją, bet ką tai turi su tuo?
Funkcija FILTER.XML kaip pradinį argumentą gauna XML kodą – tekstą, pažymėtą specialiomis žymomis ir atributais, o tada analizuoja jį į savo komponentus, išskirdama mums reikalingus duomenų fragmentus. XML kodas paprastai atrodo maždaug taip:
XML kiekvienas duomenų elementas turi būti įtrauktas į žymas. Žyma yra tam tikras tekstas (aukščiau pateiktame pavyzdyje tai yra vadovas, vardas, pelnas), įterptas į kampinius skliaustus. Žymos visada būna poromis – atidaromos ir uždaromos (pradžioje pridedamas pasvirasis brūkšnys).
Funkcija FILTER.XML gali lengvai išgauti visų mums reikalingų žymų turinį, pavyzdžiui, visų vadybininkų vardus ir (svarbiausia) juos visus vienu metu rodyti viename sąraše. Taigi mūsų užduotis yra pridėti žymes prie šaltinio teksto, paverčiant jį XML kodu, tinkamu tolesniam FILTER.XML funkcijos analizei.
Jei kaip pavyzdį paimsime pirmąjį adresą iš sąrašo, turėsime jį paversti šia konstrukcija:
Iškviečiau visuotinę viso teksto atidarymo ir uždarymo žymą t, o kiekvieną elementą įrėminančios žymos yra s., bet galite naudoti bet kokius kitus pavadinimus – nesvarbu.
Jei iš šio kodo pašalinsime įtraukas ir eilučių lūžius – beje, visiškai neprivaloma ir pridėta tik aiškumo dėlei, visa tai pavirs į eilutę:
Ir jį jau galima palyginti nesunkiai gauti iš šaltinio adreso pakeitus jame kablelius keliomis žymomis naudojant funkciją PAKEITIMAS (PAKEIT.) ir klijavimas simboliu & pradžios ir pabaigos žymų pradžioje ir pabaigoje:
Norėdami išplėsti gautą diapazoną horizontaliai, naudojame standartinę funkciją PRANEŠIMAS (TRANSPONUOTI), įvyniodami į jį mūsų formulę:
Svarbi viso šio dizaino ypatybė yra ta, kad naujoje „Office 2021“ ir „Office 365“ versijoje su dinaminių masyvų palaikymu įvesti nereikia jokių specialių gestų – tiesiog įveskite ir spustelėkite įeiti – pati formulė užima reikiamą ląstelių skaičių ir viskas veikia su kaupu. Ankstesnėse versijose, kur dar nebuvo dinaminių masyvų, prieš įvesdami formulę turėsite pasirinkti pakankamai tuščių langelių (galite su parašte), o sukūrę formulę paspauskite spartųjį klavišą "Ctrl"+perėjimas+įeitinorėdami įvesti jį kaip masyvo formulę.
Panašus triukas gali būti naudojamas atskiriant tekstą, sulipusį į vieną langelį per eilutės pertrauką:
Vienintelis skirtumas nuo ankstesnio pavyzdžio yra tas, kad vietoj kablelio čia pakeičiame nematomą Alt + Enter eilutės pertraukos simbolį, kurį formulėje galima nurodyti naudojant CHAR funkciją su kodu 10.
- „Excel“ darbo su eilučių pertraukomis (Alt + Enter) subtilybės
- Padalinkite tekstą į stulpelius programoje „Excel“.
- Tekstas keičiamas į SUBSTITUTE