Mai recent, am discutat despre utilizarea funcției FILTER.XML pentru a importa date XML de pe Internet – sarcina principală pentru care este destinată, de fapt, această funcție. Pe parcurs, însă, a apărut o altă utilizare neașteptată și frumoasă a acestei funcții – pentru a împărți rapid textul lipicios în celule.
Să presupunem că avem o coloană de date ca aceasta:
Desigur, pentru comoditate, aș dori să-l împart în coloane separate: numele companiei, orașul, strada, casa. Puteți face acest lucru într-o mulțime de moduri diferite:
- Utilizare Text pe coloane din filă Date (Date — Text în coloane) și mergi trei pași Analizator de text. Dar dacă datele se schimbă mâine, va trebui să repeți din nou întregul proces.
- Încărcați aceste date în Power Query și împărțiți-le acolo, apoi încărcați-le înapoi în foaie, apoi actualizați interogarea când datele se modifică (ceea ce este deja mai ușor).
- Dacă trebuie să actualizați din mers, atunci puteți scrie câteva formule foarte complexe pentru a găsi virgule și a extrage textul dintre ele.
Și poți să o faci mai elegant și să folosești funcția FILTER.XML, dar ce legătură are ea?
Funcția FILTER.XML primește ca argument inițial un cod XML - text marcat cu etichete și atribute speciale, apoi îl parsează în componentele sale, extragând fragmentele de date de care avem nevoie. Codul XML arată de obicei cam așa:
În XML, fiecare element de date trebuie să fie inclus în etichete. O etichetă este un text (în exemplul de mai sus este manager, nume, profit) cuprins între paranteze unghiulare. Etichetele vin întotdeauna în perechi – de deschidere și de închidere (cu o bară oblică adăugată la început).
Funcția FILTER.XML poate extrage cu ușurință conținutul tuturor etichetelor de care avem nevoie, de exemplu, numele tuturor managerilor și (cel mai important) le poate afișa pe toate odată într-o singură listă. Deci sarcina noastră este să adăugăm etichete textului sursă, transformându-l în cod XML potrivit pentru analiza ulterioară prin funcția FILTER.XML.
Dacă luăm ca exemplu prima adresă din lista noastră, atunci va trebui să o transformăm în această construcție:
Am apelat eticheta globală de deschidere și închidere a tuturor textului t, iar etichetele care încadrează fiecare element sunt s., dar puteți folosi orice alte denumiri - nu contează.
Dacă eliminăm indentările și rupturile de linie din acest cod - complet, apropo, opțional și adăugat doar pentru claritate, atunci toate acestea se vor transforma într-o linie:
Și poate fi deja obținut relativ ușor de la adresa sursă prin înlocuirea virgulelor în ea cu câteva etichete folosind funcția SUBSTITUI (SUBSTITUI) și lipirea cu simbolul & la începutul și la sfârșitul etichetelor de deschidere și de închidere:
Pentru a extinde intervalul rezultat pe orizontală, folosim funcția standard TRANSP (TRANSPUNE), învelind formula noastră în ea:
O caracteristică importantă a întregului design este că, în noua versiune de Office 2021 și Office 365 cu suport pentru matrice dinamice, nu sunt necesare gesturi speciale pentru introducere - trebuie doar să introduceți și să faceți clic pe Intrați – formula în sine ocupă numărul de celule de care are nevoie și totul funcționează cu un bang. În versiunile anterioare, unde nu existau încă matrice dinamice, va trebui să selectați mai întâi un număr suficient de celule goale înainte de a introduce formula (puteți cu o marjă), iar după crearea formulei, apăsați comanda rapidă de la tastatură Ctrl+Schimba+Intrațipentru a o introduce ca formulă matrice.
Un truc similar poate fi folosit atunci când separă textul lipit într-o singură celulă printr-o întrerupere de linie:
Singura diferență cu exemplul anterior este că, în loc de virgulă, aici înlocuim caracterul invizibil Alt + Enter, care poate fi specificat în formulă folosind funcția CHAR cu codul 10.
- Subtilitățile lucrului cu rupturi de linie (Alt + Enter) în Excel
- Împărțiți textul pe coloane în Excel
- Înlocuirea textului cu SUBSTITUTE