Più recentemente, abbiamo discusso dell'uso della funzione FILTER.XML per importare dati XML da Internet, il compito principale a cui questa funzione, infatti, è destinata. Lungo la strada, tuttavia, è emerso un altro uso inaspettato e bellissimo di questa funzione, per dividere rapidamente il testo appiccicoso in celle.
Supponiamo di avere una colonna di dati come questa:
Ovviamente, per comodità, vorrei dividerlo in colonne separate: nome dell'azienda, città, via, casa. Puoi farlo in molti modi diversi:
- Usa il Testo per colonne dalla scheda Dati (Dati — Testo in colonne) e fai tre passi Analizzatore di testo. Ma se i dati cambiano domani, dovrai ripetere di nuovo l'intero processo.
- Carica questi dati in Power Query e dividili lì, quindi caricali di nuovo nel foglio, quindi aggiorna la query quando i dati cambiano (il che è già più semplice).
- Se hai bisogno di aggiornare al volo, puoi scrivere alcune formule molto complesse per trovare le virgole ed estrarre il testo tra di loro.
E puoi farlo in modo più elegante e utilizzare la funzione FILTER.XML, ma cosa c'entra?
La funzione FILTER.XML riceve come argomento iniziale un codice XML, un testo contrassegnato con tag e attributi speciali, quindi lo analizza nei suoi componenti, estraendo i frammenti di dati di cui abbiamo bisogno. Il codice XML di solito assomiglia a questo:
In XML, ogni elemento di dati deve essere racchiuso tra tag. Un tag è un testo (nell'esempio sopra è manager, nome, profitto) racchiuso tra parentesi angolari. I tag sono sempre in coppia: apertura e chiusura (con una barra aggiunta all'inizio).
La funzione FILTER.XML può estrarre facilmente il contenuto di tutti i tag di cui abbiamo bisogno, ad esempio i nomi di tutti i gestori, e (soprattutto) visualizzarli tutti in una volta in un elenco. Quindi il nostro compito è quello di aggiungere dei tag al testo sorgente, trasformandolo in codice XML adatto ad una successiva analisi da parte della funzione FILTER.XML.
Se prendiamo come esempio il primo indirizzo dalla nostra lista, allora dovremo trasformarlo in questa costruzione:
Ho chiamato l'apertura globale e la chiusura di tutti i tag di testo te i tag che incorniciano ogni elemento sono s., ma puoi usare qualsiasi altra designazione – non importa.
Se rimuoviamo i rientri e le interruzioni di riga da questo codice, completamente, tra l'altro, facoltativo e aggiunto solo per chiarezza, tutto questo si trasformerà in una riga:
E può già essere ottenuto in modo relativamente semplice dall'indirizzo di origine sostituendo le virgole con un paio di tag utilizzando la funzione SOSTITUIRE (SOSTITUTO) e incollare con il simbolo & all'inizio e alla fine dei tag di apertura e chiusura:
Per espandere l'intervallo risultante in orizzontale, utilizziamo la funzione standard TRASPORTO (TRASPORRE), racchiudendo in esso la nostra formula:
Una caratteristica importante di tutto questo progetto è che nella nuova versione di Office 2021 e Office 365 con supporto per array dinamici, non sono richiesti gesti speciali per l'input: basta entrare e fare clic su entrare – la formula stessa occupa il numero di celle di cui ha bisogno e tutto funziona con il botto. Nelle versioni precedenti, dove non c'erano ancora array dinamici, dovrai prima selezionare un numero sufficiente di celle vuote prima di inserire la formula (puoi con un margine) e dopo aver creato la formula, premere la scorciatoia da tastiera Ctrl+Shift+entrareper inserirlo come una formula di matrice.
Un trucco simile può essere utilizzato quando si separa il testo bloccato insieme in una cella tramite un'interruzione di riga:
L'unica differenza con l'esempio precedente è che invece di una virgola, qui sostituiamo il carattere di interruzione di riga invisibile Alt + Invio, che può essere specificato nella formula usando la funzione CHAR con il codice 10.
- Le sottigliezze del lavoro con le interruzioni di riga (Alt + Invio) in Excel
- Dividi il testo per colonne in Excel
- Sostituzione del testo con SOSTITUZIONE