Come estrarre tutti i valori inseriti in una serie di celle Microsoft Excel

In questa guida scopriamo come estrarre tutti i valori inseriti in una cella su Excel. Questa procedura è particolarmente utile quando hai a che fare con database che abbiano, nella stessa cella, diversi valori utili.

Un caso tipico è quello dell'e-commerce: un capo potrebbe avere diversi colori o diversi tag, per esempio. Mi è capitato spesso di vedere strutture del tipo: abitoX | rosso, giallo, verde | M | unisex. In questo caso, recuperare tutti i colori risulta particolarmente complicato, se non sappiamo come fare. In questa guida lo vediamo in due modi: prima vedremo come utilizzare le formule, successivamente come fare se lavoriamo con Power Query.

Il database di partenza in questo caso presenta dei film. Vogliamo sapere quanti siano i generi inseriti e per quanti film questi vengano segnalati. Quindi ci aspettiamo una lista del tipo: Action | Adventure | Crime...

Questo, come dicevamo, può essere complicato. Vediamo però come fare utilizzando le formule di Excel 365.

Come estrarre i valori inseriti in una serie di celle Microsoft Excel usando le formule

L'obiettivo è chiaro, dato che vogliamo estrarre tutti i valori inseriti una cella Excel con delle formule. Per una cella sola, ci basterebbe fare con DIVIDI.TESTO e probabilmente usando funzioni come A.COL e UNICI.

Dato che vogliamo considerare una serie di celle, verifichiamo la situazione iniziale. In questo caso ci torna utile vedere tutte le possibili combinazioni su 3 colonne, grazie alla funzione A.CAPO.RIGA:

=A.CAPO.RIGA(
DATI.ORDINA(UNICI(TBL_FILM[GENERI]));
3;"")

Questa formula prima prende tutti i valori univoci presenti nella tabella chiamata TBL_FILM nella colonna GENERI. A questo punto, li mette su diverse righe ogni 3 valori. A.CAPO.RIGA ci dice infatti che questo intervallo andrà a capo, per ogni riga, dopo avere 3 riferimenti. Se non ci fosse niente, apparirebbe il valore vuoto ("") che può tornare comodo se il totale non è esattamente divisibile per tre.

Questo passaggio ci segnala che i generi non sono tantissimi (a colpo d'occhio), quindi possiamo pensare di lavorare con le formule senza particolari problemi.

Visto che creeremo una funzione LAMBDA, se il concetto non ti è chiarissimo ti consiglio di dare un'occhiata questa guida che spiega, passo passo, come creare una funzione personalizzata da zero.

Vediamo ora come pescare questi valori in un colpo solo. Potremmo pensare di utilizzare la funzione DIVIDI.TESTO, ma purtroppo DIVIDI.TESTO non funziona bene per gli intervalli. Ciò significa che, se utilizziamo DIVIDI.TESTO, questa formula prenderà tutti i valori inseriti in una cella (indicando il separatore), ma solo per una cella. Se invece usiamo un array, come punto di partenza, questa formula ci darà, per tutti i valori, unicamente il primo risultato. Questo, chiaramente, non ci è sufficiente.

Dobbiamo pensare a un trucco di questo tipo, per cavarcela:

• Prendiamo tutti i valori e li mettiamo tutti insieme
• Li dividiamo, per ottenere tutti i valori singoli
• Prendiamo solo i valori univoci e poi li ordiniamo

Come logica, sembra funzionare. La formula sarà però un po' complessa, come vediamo:

Questa funzione è già impostata come LAMBDA, come vediamo nello specifico:

=LAMBDA(intervallo;separatore;
LET(dati_uniti;TESTO.UNISCI(separatore;;intervallo);
dati_divisi;DIVIDI.TESTO(dati_uniti;;separatore);
dati_unici_ordinati; DATI.ORDINA(UNICI(dati_divisi));
dati_unici_ordinati))(TBL_FILM[GENERI];", ")

Che cosa ci dice questa LAMBDA? In sostanza, l'utente dovrà inserire esclusivamente l'intervallo e il separatore, per farla funzionare. Vediamo infatti che, dopo la formula, ci sono (TBL_FILM[GENERI];", "). Questi saranno, rispettivamente, il nostro intervallo e il suo separatore, che vengono utilizzati per testare la LAMBDA ed essere sicuri che funzioni correttamente.

La formula segue questi passaggi, identificati tramite la funzione LET, che ci permette di dichiarare un parametro:

• Dati_uniti è una cella con tantissimi valori testuali, che racchiude, con TESTO.UNISCI, tutti i valori che abbiamo trovato nella colonna dei generi della tabella iniziale, intervallati dal nostro separatore ", ". Immaginati una lista tipo questa: Drama, Crime, Drama, Action, Crime, Drama, Crime, Drama, Crime, Drama, Biography, Drama, Thriller che però si trova in una sola cella
• Dati_divisi prende la cella enorme di Dati_uniti e la divide utilizzando il separatore per singola riga con ", ". Avremo quindi una lista di valori piuttosto ampia, dato che la formula prenderà, con DIVIDI.TESTO, tutte le possibili combinazioni precedenti. Avremo quindi una lunga lista di valori duplicati, tutti consecutivi, simili a questa progressione: Drama | Crime | Drama | Action| Crime. I dati però, ora, sono in righe diverse e quindi sono utilizzabili
• Dati_unici_ordinati prende quanto troviamo con Dati_divisi, ma elimina i duplicati e li mette in ordine alfabetico tramite DATI.ORDINA e UNICI. In questo modo, avremo una lista chiara e definita di tutti i nostri dati

Sì, è un po' complessa, ma ridurre in un solo passaggio tutti questi calcoli rende queste formule piuttosto complicate da creare e seguire.

Voglio ringraziare Diarmuid Early che mi ha dato degli spunti utili per ridurre all'osso la formula e ottimizzarla, tramite il suo intervento nel gruppo Excel Lambda Discussions su Linkedin - se ti interessa l'argomento LAMBDA, ti consiglio di iscriverti (LINK 1). Qui (LINK 2) trovi, nello specifico, la discussione che ha dato origine a questa guida, ci sono tantissimi suggerimenti e soluzioni diverse dalla mia!

A questo punto, potrebbe venirci la curiosità di contare quanti film abbiano un certo genere:

=CONTA.PIÙ.SE(TBL_FILM[GENERI];"*"&I4#&"*")

In questo caso stiamo dicendo che l'intervallo interessato è sempre la colonna GENERI della TBL_FILM. L'indicazione "*"&I4#&"*" significa che vogliamo avere un riscontro quando qualsiasi valore ("*") è inserito prima o dopo quanto abbiamo inserito nel nostro array in I4#, cioè tutti i generi possibili. CONTA.PIÙ.SE è piuttosto flessibile, ma dobbiamo ricordarci di come inserire la sintassi corretta, in casi particolari come questo.

Volendo, possiamo anche vedere quanti siano i generi con un semplice CONTA.VALORI:

=SE.ERRORE(CONTA.VALORI(I4#);"")

Ho inserito il SE.ERRORE solo perchè potremmo anche non avere risultati, ma comunque CONTA.VALORI ci dice che ci sono ben 18 generi diversi.

A questo punto potremmo creare la nostra funzione personalizzata, che ho chiamato ESPLOSIONE_CELLA. Ci basterebbe seguire questa procedura:

• Copia la funzione fino alla fine, escludendo la sezione con (TBL_FILM[GENERI];", "). Questo è necessario per rendere la formula il più universale possibile
• Crea un nuovo nome definito da Formule > Definisci nome e copia la formula nella sezione chiamata Riferito a:
• Dai il nome che preferisci e, se vuoi, metti una breve descrizione della funzione

In questo modo, potrai riutilizzare la funzione ovunque sul tuo file, evitando di essere bloccati a un intervallo o a un separatore specifico.

Come estrarre i valori inseriti, e relativo conteggio, in una serie di celle Microsoft Excel usando le formule

Vediamo ora come fare se volessimo estrarre i valori univoci e aggiungere, in automatico, anche il conteggio finale di quante volte questi valori siano inseriti nel nostro intervallo di partenza su Excel.

Recuperemo buona parte della LAMBDA precedente, aggiungendo però un pezzo fondamentale alla fine:

=LAMBDA(intervallo;separatore;
LET(dati_uniti;TESTO.UNISCI(separatore;;intervallo);
dati_divisi;DIVIDI.TESTO(dati_uniti;;separatore);
dati_univoci_ordinati; DATI.ORDINA(UNICI(dati_divisi));
conteggio_dati;CONTA.SE(intervallo;"*"&dati_univoci_ordinati&"*");
recap_finale;DATI.ORDINA(
STACK.ORIZ(dati_univoci_ordinati;conteggio_dati);
2;-1);
recap_finale))(TBL_FILM[GENERI];", ")

Vediamo che cosa succede. Rispetto al caso precedente, vogliamo modificare leggermente la parte finale:

• Conteggio_dati recupera i Dati_univoci_ordinati e ci restituisce, per ognuno di loro, il numero di volte nei quali questo genere sia inserito in una cella specifica nella tabella di partenza
• Il Recap_finale diventa quindi un'unione di due diversi intervalli. Il primo saranno i nostri Dati_univoci_ordinati, mentre il secondo sarà il nostro Conteggio_dati. Possiamo quindi lavorare con la funzione STACK.ORIZ per unirli orizzontalmente su due diverse colonne
• A questo punto, diamo priorità a quelli più presenti ordinando i dati con DATI.ORDINA. L'inserimento di 2;-1 significa che stiamo dando priorità alla seconda colonna del nostro stack (il Conteggio_dati) in ordine decrescente (-1)

Il vantaggio, in questo caso, è di avere tutto in un colpo solo, sia la lista di generi sia il conteggio complessivo. Un dettaglio da evidenziare, anche se è rimasto nella formula finale, è che chiaramente il DATI.ORDINA(UNICI al centro della formula ha un ordinamento ininfluente, poichè quello che conta sarà l'ordinamento finale dello stack.

In questi casi, il suggerimento sarebbe sempre quello di creare poi un grafico, anche semplice, per visualizzare velocemente la distinzione fra i valori. In questo caso, notiamo come Drama sia decisamente il genere più rappresentato, seguito a grande distanza da Adventure e Crime.

Qui trovi una guida su come rendere questo grafico completamente dinamico, se ti interessa.

Questa combinazione può anche essere utilizzata per creare una nuova LAMBDA specifica, chiamata ESPLOSIONE_CELLA_CONTEGGIO. Queste funzioni non richiederanno all'utente di capire esattamente che cosa succeda, ma così almeno potrà utilizzare qualsiasi intervallo del foglio di lavoro, con qualunque separatore, per ottenere velocemente un elenco già ordinato e con tutti i conteggi.

Come estrarre i valori inseriti in una serie di celle Microsoft Excel usando le formule, se i dati di partenza sono numerosi

Un possibile problema, sempre per estrarre i valori inseriti in una serie di celle Excel, può essere la quantità di risultati che abbiamo. C'è infatti un limite al numero di caratteri gestiti da TESTO.SUCCESSIVO e questo potrebbe dare dei problemi, non avresti nessun risultato in questo caso. Se ti capita una situazione simile, ti consiglierei di guardare la soluzione in Power Query che trovi poco più avanti in questa guida.

Questo mi è stato segnalato nello specifico da JvdV, che segnalava giustamente come questa formula non funzionerebbe con più di 32767 caratteri nella prima cella (chiamata Dati_uniti). La sua soluzione, che ti segnalo, è decisamente interessante, oltrechè complessa:

=LAMBDA(range;divider;DATI.ORDINA(UNICI(A.COL(DIVIDI.TESTO(TESTO.SUCCESSIVO(divider&divider;SEQUENZA(;MAX((LUNGHEZZA(range)-LUNGHEZZA(SOSTITUISCI(range;divider;)))/LUNGHEZZA(divider))+1));divider);3))))(TBL_FILM[GENERI];", ")

Questa funzione riesce a superare i limiti di quella segnalata in precedenza, ma come noti richiede uno sforzo importante per essere compresa dato che effettua diverse trasformazioni una dopo l'altra. Visto che non abbiamo molti valori, il risultato di questa formula e di quella che trovi in precedenza è esattamente lo stesso, ma trovo sia utile indicarti una strada alternativa nel caso avessi dei problemi.

Come estrarre i valori inseriti in una serie di celle Microsoft Excel usando Power Query

Vediamo ora come estrarre i valori univoci inseriti in una serie di celle Excel utilizzando Power Query. Ci sono vari passaggi da effettuare che vedremo singolarmente, ma il punto principale è che pure in questo caso dovremo valutare quale sia la strada più veloce e meno complessa.

A livello logico, i passaggi sono questi:

• Caricare i dati in Power Query, in questo caso prendendo direttamente la tabella TBL_FILM. Possiamo quindi selezionare TBL_FILM e poi cliccare su Recupera Dati > Da altre origini > Da tabella/intervallo
• Sfruttare le funzionalità di Power Query per modificare i dati in modo da ottenere esclusivamente una colonna con i singoli generi che ci interessano
• Caricare questa nuova tabella, dove i film non sono compresi, sul nostro foglio di lavoro

Più nello specifico:

Carichiamo, da Dati > Recupera Dati > Da altre origini > Da tabella/intervallo, i dati della nostra TBL_FILM.

Dividiamo poi la colonna del genere selezionando il divisore (", ") e spiegando che vogliamo che si crei una colonna nuova a ogni nuova occorrenza del delimitatore. In questo modo Crime, Action, Drama diventerà Crime | Action | Drama.

Vediamo come il database stia diventando inutilizzabile, ma è tutto parte del processo. Abbiamo ora 4 diverse colonne e useremo la prima, TITOLO, come bussola per poi lavorare sui nostri GENERI.

A questo punto, selezioniamo TITOLO per poi cliccare su Trasforma > Trasforma colonne tramite UnPivot > Trasforma altre colonne tramite UnPivot. Vediamo come si creino ora tre diverse colonne: le prime due sono i film e le intestazioni precedenti. Quella che ci interessa è però l'ultima, Genere.

Rimuoviamo, cliccando su Home > Rimuovi colonne, le colonne che non ci interessano (TITOLO e Attributo).

Ci rimane una sola colonna, dalla quale possiamo rimuovere i duplicati cliccando su Home > Rimuovi righe > Rimuovi duplicati. Vediamo già che i generi si sono notevolmente ridotti.

A questo punto, possiamo mettere in ordine i nostri valori semplicemente cliccando sulla intestazione e ordinando alfabeticamente. Possiamo caricare i dati sul nostro Excel in una nuova tabella.

Il risultato finale sarà assolutamente analogo a quello visto con le formule, ma ci sono vari vantaggi. Il processo è infatti facile da leggere e seguire, avendo utilizzando Power Query, nonchè completamente scalabile. Dovremo solo stare attenti nell'utilizzare queste informazioni qualora dovesse cambiare il separatore, perchè la nostra procedura non funzionerebbe correttamente. Modificando i dati di origine, possiamo poi rivedere anche gli inserimenti nei vari step di Power Query e modificare il tutto di conseguenza.

In formato più compatto, il codice è questo:

let
Origine = Excel.CurrentWorkbook(){[Name="TBL_FILM"]}[Content],
#"Suddividi colonna in base al delimitatore" = Table.SplitColumn(Origine, "GENERI", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), {"Generi.1", "Generi.2", "Generi.3"}),
#"Trasformate altre colonne tramite UnPivot" = Table.UnpivotOtherColumns(#"Suddividi colonna in base al delimitatore", {"TITOLO"}, "Attributo", "Genere"),
#"Rimosse colonne" = Table.RemoveColumns(#"Trasformate altre colonne tramite UnPivot",{"TITOLO", "Attributo"}),
#"Rimossi duplicati" = Table.Distinct(#"Rimosse colonne"),
#"Ordinate righe" = Table.Sort(#"Rimossi duplicati",{{"Genere", Order.Ascending}})
in
#"Ordinate righe"

Rivediamo il processo più nel dettaglio:

Ecco una spiegazione passo dopo passo:

1. Origine: Carica i dati da una tabella denominata "TBL_FILM" nel workbook corrente di Excel. La tabella viene assegnata alla variabile Origine. Questo passaggio seleziona semplicemente la tabella per le operazioni successive.
2. Suddividi colonna in base al delimitatore: Utilizza Table.SplitColumn per dividere i valori della colonna "GENERI", che contiene generi di film separati da ", ". Ogni genere viene separato in una nuova colonna (es. "Generi.1", "Generi.2", "Generi.3"), usando il delimitatore ", ". Questo passaggio è utile quando una cella contiene più valori che si desidera trattare separatamente.
3. Trasformate altre colonne tramite UnPivot: Esegue un "unpivot" delle colonne tranne "TITOLO", trasformando le colonne dei generi in righe. Dopo questo passaggio, avremo una colonna "Attributo" che indica il nome originale della colonna dei generi, e una colonna "Genere" che contiene il valore del genere. Ciò trasforma la tabella da un formato "wide" (più colonne) a un formato "long" (più righe), rendendo ogni genere di film una riga separata.
4. Rimosse colonne: Rimuove le colonne "TITOLO" e "Attributo" dalla tabella risultante, lasciando solo la colonna "Genere". Questo perché l'obiettivo è lavorare solo con i generi, indipendentemente dai titoli dei film o dall'attributo originale.
5. Rimossi duplicati: Utilizza Table.Distinct per rimuovere eventuali righe duplicate basate sulla colonna "Genere". Questo assicura che ogni genere sia elencato una sola volta, indipendentemente da quante volte appaia attraverso vari film.
6. Ordinate righe: Ordina le righe della tabella in base alla colonna "Genere" in ordine ascendente, utilizzando Table.Sort. Questo facilita la ricerca e l'analisi dei generi di film, presentandoli in un ordine alfabetico.

In sintesi, il codice trasforma una colonna con valori multipli separati da virgole in una lista di valori univoci, rimuove le duplicazioni e ordina i risultati in modo che ogni genere di film appaia una sola volta in ordine alfabetico.

Conclusioni

Come abbiamo visto, estrarre i valori univoci da una lista di celle può essere complesso, sia utilizzando le formule di Excel sia lavorando con Power Query. Tuttavia, queste procedure possono essere salvifiche quando lavoriamo con database complessi e spero vivamente che ti torneranno utili nel tuo lavoro di tutti i giorni!