Més recentment, vam parlar de l'ús de la funció FILTER.XML per importar dades XML d'Internet, la tasca principal per a la qual, de fet, està destinada aquesta funció. Al llarg del camí, però, ha sorgit un altre ús inesperat i bonic d'aquesta funció: per dividir ràpidament el text enganxós en cel·les.
Suposem que tenim una columna de dades com aquesta:
Per descomptat, per comoditat, m'agradaria dividir-lo en columnes separades: nom de l'empresa, ciutat, carrer, casa. Podeu fer-ho de moltes maneres diferents:
- Ús Text per columnes de la pestanya dades (Dades: text a columnes) i fes tres passos Analitzador de text. Però si les dades canvien demà, hauràs de tornar a repetir tot el procés.
- Carregueu aquestes dades a Power Query i dividiu-les allà, i després torneu-les a carregar al full i, a continuació, actualitzeu la consulta quan canviïn les dades (que ja és més fàcil).
- Si necessiteu actualitzar sobre la marxa, podeu escriure algunes fórmules molt complexes per trobar comes i extreure el text entre elles.
I pots fer-ho de manera més elegant i utilitzar la funció FILTER.XML, però què hi té a veure?
La funció FILTER.XML rep com a argument inicial un codi XML: text marcat amb etiquetes i atributs especials, i després l'analitza en els seus components, extreint els fragments de dades que necessitem. El codi XML normalment té un aspecte com aquest:
En XML, cada element de dades ha d'estar inclòs en etiquetes. Una etiqueta és un text (a l'exemple anterior és gestor, nom, benefici) inclòs entre claudàtors angulars. Les etiquetes sempre vénen per parelles: s'obren i es tanquen (amb una barra inclinada afegida al principi).
La funció FILTER.XML pot extreure fàcilment el contingut de totes les etiquetes que necessitem, per exemple, els noms de tots els gestors, i (el més important) mostrar-les totes alhora en una llista. Per tant, la nostra tasca és afegir etiquetes al text font, convertint-lo en codi XML adequat per a l'anàlisi posterior mitjançant la funció FILTER.XML.
Si prenem com a exemple la primera adreça de la nostra llista, haurem de convertir-la en aquesta construcció:
Vaig trucar a l'etiqueta global d'obertura i tancament de tot el text t, i les etiquetes que emmarcan cada element són s., però podeu utilitzar qualsevol altra designació, no importa.
Si eliminem els sagnats i els salts de línia d'aquest codi, completament, per cert, opcional i afegit només per a més claredat, tot això es convertirà en una línia:
I ja es pot obtenir amb relativa facilitat des de l'adreça d'origen substituint-hi les comes per un parell d'etiquetes utilitzant la funció SUPLENT (SUBSTITUT) i enganxar amb el símbol & al principi i al final de les etiquetes d'obertura i tancament:
Per ampliar horitzontalment el rang resultant, utilitzem la funció estàndard TRANSP (TRANSPOSICIÓ), embolicant-hi la nostra fórmula:
Una característica important de tot aquest disseny és que a la nova versió d'Office 2021 i Office 365 amb suport per a matrius dinàmiques, no calen gestos especials per a l'entrada; només cal que introduïu i feu clic a Enter – la fórmula en si ocupa el nombre de cel·les que necessita i tot funciona amb una explosió. A les versions anteriors, on encara no hi havia matrius dinàmiques, primer haureu de seleccionar un nombre suficient de cel·les buides abans d'introduir la fórmula (ho podeu fer amb un marge) i després de crear la fórmula, premeu la drecera del teclat. Ctrl+Canviar+Enterper introduir-lo com a fórmula matricial.
Es pot utilitzar un truc similar per separar el text enganxat en una cel·la mitjançant un salt de línia:
L'única diferència amb l'exemple anterior és que en comptes d'una coma, aquí substituïm el caràcter invisible de salt de línia Alt + Enter, que es pot especificar a la fórmula mitjançant la funció CHAR amb el codi 10.
- Les subtileses de treballar amb salts de línia (Alt + Enter) a Excel
- Dividiu el text per columnes a Excel
- Substitució del text per SUBSTITUTE