Raggruppamento fuzzy in Excel: come unire dati simili ma non identici

Hai mai dovuto raggruppare manualmente dati simili ma non identici in Excel? Scopri come Power Query offre una soluzione efficace attraverso il raggruppamento fuzzy, una funzionalità potente ma poco conosciuta che automatizza questo processo tedioso.

Lavorare con dati non standardizzati in Excel può trasformarsi in un’attività frustrante. Spesso ci troviamo di fronte a valori che dovrebbero essere considerati uguali ma presentano piccole differenze di battitura, formattazione o sintassi. Raggruppare manualmente questi elementi richiede tempo prezioso e aumenta il rischio di errori. Fortunatamente, Excel offre una soluzione efficace ma poco conosciuta: il raggruppamento fuzzy in Power Query, una tecnica avanzata di fuzzy matching che semplifica notevolmente questo processo.

Comprendere il raggruppamento fuzzy in Excel

Il raggruppamento fuzzy è una funzionalità avanzata di Power Query che consente di identificare e unire automaticamente elementi simili ma non identici. Ma prima di approfondire, chiariamo: power query cos’è? Si tratta di un potente strumento di preparazione dei dati integrato in Excel che permette di trasformare e pulire i dati in modo efficiente. A differenza delle funzioni standard di Excel che richiedono corrispondenze esatte, il raggruppamento fuzzy utilizza algoritmi di somiglianza per riconoscere varianti dello stesso dato. Questa funzionalità è particolarmente utile quando lavori con:

• Elenchi di nomi con varianti ortografiche (es. “Mario Rossi” e “Rossi Mario”)
• Dati inseriti da persone diverse con convenzioni differenti
• Informazioni provenienti da sistemi diversi con formattazioni non uniformi
• Risposte a domande aperte in sondaggi o questionari

Il raggruppamento fuzzy si basa su un “punteggio di somiglianza” configurabile che determina quanto due stringhe devono essere simili per essere considerate equivalenti. Inoltre, puoi creare tabelle di traduzione personalizzate per mappare termini specifici che desideri trattare come identici. Nei prossimi paragrafi, ti guiderò attraverso tutti i passaggi necessari per implementare questa potente funzionalità nei tuoi fogli di calcolo, inclusa la preparazione dei dati intelligente e l’operazione di aggregazione.

Preparare i dati per il raggruppamento fuzzy

Prima di utilizzare il raggruppamento fuzzy, è fondamentale organizzare correttamente i dati. La preparazione adeguata garantirà risultati ottimali e semplificherà l’intero processo. Il primo passo consiste nel convertire i tuoi dati in una tabella Excel. Questo è un requisito essenziale per utilizzare Power Query e accedere alle funzionalità di raggruppamento fuzzy.

Per creare una tabella:

1. Seleziona qualsiasi cella all’interno dei tuoi dati
2. Premi Ctrl+T oppure vai nella scheda “Inserisci” e clicca su “Tabella”
3. Verifica che l’opzione “La tabella ha intestazioni” sia selezionata se la prima riga contiene i nomi delle colonne
4. Conferma cliccando su “OK”

Una volta creata la tabella, è consigliabile assegnarle un nome significativo e conciso. Questo renderà più facile fare riferimento ad essa nelle formule e in Power Query. Per rinominare la tabella:

1. Seleziona qualsiasi cella nella tabella
2. Nella scheda “Progettazione tabella” che appare, modifica il nome nel campo “Nome tabella” nell’angolo superiore sinistro

È importante anche verificare la qualità dei dati prima di procedere. Controlla la presenza di celle vuote, errori di formattazione o caratteri speciali che potrebbero influenzare il processo di raggruppamento. Se necessario, esegui una pulizia dei dati rimuovendo spazi extra, uniformando maiuscole/minuscole o correggendo errori evidenti. Questa fase di pulizia dei dati è cruciale per garantire l’integrità del dataset e migliorare l’efficacia del raggruppamento fuzzy.

Infine, identifica quali colonne contengono i valori che desideri raggruppare. Il raggruppamento fuzzy funziona meglio quando applicato a una singola colonna alla volta, quindi potresti dover riorganizzare i tuoi dati di conseguenza. Questa preparazione dei dati intelligente ti aiuterà a ottenere risultati più accurati nelle fasi successive.

Creare una tabella di traduzione personalizzata

Una delle caratteristiche più potenti del raggruppamento fuzzy è la possibilità di utilizzare una tabella di traduzione personalizzata. Questa tabella, che funge da tabella di riferimento, permette di definire esplicitamente quali termini devono essere considerati equivalenti, indipendentemente dal loro punteggio di somiglianza. La tabella di traduzione deve avere una struttura specifica con due colonne:

• Da: contiene i valori originali che desideri mappare
• A: contiene i valori a cui desideri convertire i termini originali

Ad esempio, potresti voler considerare “email”, “e-mail” e “posta elettronica” come lo stesso concetto. In questo caso, la tabella di traduzione potrebbe apparire così:

Da	A
email	posta elettronica
e-mail	posta elettronica
mail	posta elettronica

Per creare questa tabella di trasformazione:

1. Inserisci le intestazioni “Da” e “A” in due celle adiacenti
2. Compila le righe con le coppie di valori da mappare
3. Seleziona l’intera area e converti in tabella (Ctrl+T)
4. Assegna un nome significativo alla tabella, ad esempio “Traduzione”

La tabella di traduzione è particolarmente utile per:

• Standardizzare terminologie specifiche del settore
• Unificare abbreviazioni e forme estese
• Gestire sinonimi o termini equivalenti in contesti diversi
• Correggere errori comuni di battitura o formattazione

Più completa e accurata sarà la tua tabella di traduzione, migliori saranno i risultati del raggruppamento fuzzy. Vale la pena dedicare tempo alla creazione di una tabella di traduzione completa, soprattutto se prevedi di eseguire frequentemente operazioni di unione su dati simili.

Importare i dati in Power Query

Una volta preparati i dati e creata la tabella di traduzione, è il momento di importare tutto in Power Query per iniziare il processo di raggruppamento fuzzy. Il caricamento dei dati in Power Query è un passaggio fondamentale che ti permetterà di applicare trasformazioni avanzate prima di caricare i risultati nuovamente in Excel.

Per importare la tabella principale:

1. Seleziona qualsiasi cella all’interno della tabella dei dati
2. Vai alla scheda “Dati” nella barra multifunzione
3. Clicca su “Da tabella/intervallo” nel gruppo “Recupera e trasforma dati”

Si aprirà l’Editor di Power Query con i tuoi dati. Questo ambiente ti permette di applicare trasformazioni avanzate prima di caricare i risultati nuovamente in Excel. Successivamente, devi importare anche la tabella di traduzione (se l’hai creata). Il processo è identico:

1. Torna a Excel senza chiudere l’Editor di Power Query
2. Seleziona una cella nella tabella di traduzione
3. Vai alla scheda “Dati” e clicca su “Da tabella/intervallo”

Ora avrai due query separate nell’Editor di Power Query, visibili nel pannello “Query” sulla sinistra. È importante che entrambe le query siano disponibili nell’ambiente Power Query prima di procedere con il raggruppamento fuzzy.

Prima di continuare, è consigliabile verificare che i tipi di dati siano corretti in entrambe le tabelle. Power Query assegna automaticamente tipi di dati in base al contenuto, ma a volte potrebbe essere necessario correggerli:

1. Seleziona la colonna da modificare
2. Fai clic destro e scegli “Cambia tipo”
3. Seleziona il tipo di dati appropriato (in genere “Testo” per i dati da raggruppare)

Con i dati correttamente importati e formattati, sei pronto per applicare il raggruppamento fuzzy. Se hai bisogno di caricare più file da una cartella, Power Query offre anche questa funzionalità, che può essere utile per progetti più complessi che coinvolgono multiple fonti di dati.

Applicare il raggruppamento base in Power Query

Prima di utilizzare il raggruppamento fuzzy, è utile comprendere come funziona il raggruppamento standard in Power Query. Questo ci fornirà la base per poi modificare la formula e implementare il raggruppamento fuzzy. Per applicare un raggruppamento standard:

1. Nell’Editor di Power Query, seleziona la colonna che contiene i valori da raggruppare
2. Vai alla scheda “Trasforma” nella barra multifunzione
3. Clicca sul pulsante “Raggruppa per”

Si aprirà la finestra di dialogo “Raggruppa per” con diverse opzioni:

• Raggruppa per: seleziona la colonna da utilizzare per il raggruppamento
• Nuova colonna: inserisci un nome per la colonna che conterrà i risultati del raggruppamento
• Operazione: scegli “Tutte le righe” per mantenere tutti i dati originali

Dopo aver configurato queste impostazioni, clicca su “OK” per applicare il raggruppamento standard. Power Query genererà una formula M che utilizza la funzione Table.Group(). Questa formula apparirà nella barra della formula nella parte superiore dell’editor. Il risultato sarà una nuova tabella con due colonne:

• La colonna con i valori unici trovati nel campo selezionato
• Una colonna contenente tabelle nidificate con tutte le righe corrispondenti a ciascun valore unico

Questo raggruppamento standard, tuttavia, funziona solo con corrispondenze esatte. Per ottenere un raggruppamento basato sulla somiglianza, dobbiamo modificare la formula generata e trasformarla in un raggruppamento fuzzy, implementando così una corrispondenza fuzzy più flessibile.

Modificare la formula per il raggruppamento fuzzy

Il passaggio cruciale per implementare il raggruppamento fuzzy consiste nel modificare manualmente la formula generata dal raggruppamento standard. Questo è necessario perché l’interfaccia utente di Power Query non offre un pulsante diretto per il raggruppamento fuzzy. Dopo aver applicato il raggruppamento standard, osserva la barra della formula nella parte superiore dell’editor. Vedrai una formula simile a questa:

= Table.Group(#"Tipo modificato", {"NomeColonna"}, {{"Dati", each _, type table [NomeColonna=nullable text]}})

Per convertirla in un raggruppamento fuzzy, devi:

1. Cambiare Table.Group in Table.FuzzyGroup
2. Aggiungere un quarto parametro che definisce le opzioni del raggruppamento fuzzy

La formula modificata dovrebbe apparire così:

= Table.FuzzyGroup(#"Tipo modificato", {"NomeColonna"}, {{"Dati", each _, type table [NomeColonna=nullable text]}}, [IgnoreCase=true, IgnoreSpace=true, Threshold=0.8, TransformationTable=Traduzione])

Le opzioni nel quarto parametro controllano il comportamento del raggruppamento fuzzy:

• IgnoreCase: quando impostato su true, il raggruppamento ignora le differenze tra maiuscole e minuscole
• IgnoreSpace: quando impostato su true, gli spazi vengono ignorati durante il confronto
• Threshold: un valore tra 0 e 1 che determina quanto due stringhe devono essere simili per essere raggruppate (0.8 è un buon punto di partenza)
• TransformationTable: il nome della query che contiene la tabella di traduzione

Dopo aver modificato la formula, premi Invio o clicca sul segno di spunta accanto alla barra della formula per applicare la modifica. Power Query eseguirà il raggruppamento fuzzy secondo i parametri specificati.

È importante notare che il valore di Threshold richiede sperimentazione. Un valore troppo alto (vicino a 1) richiederà una somiglianza quasi perfetta, mentre un valore troppo basso (vicino a 0) potrebbe raggruppare elementi che non dovrebbero essere considerati simili. Questa operazione di aggregazione basata sulla somiglianza delle stringhe è il cuore del raggruppamento fuzzy.

Configurare le opzioni di somiglianza

Il successo del raggruppamento fuzzy dipende in gran parte dalla corretta configurazione delle opzioni di somiglianza. Queste opzioni determinano quali elementi verranno considerati simili e quindi raggruppati insieme. L’opzione Threshold (soglia) è particolarmente importante. Rappresenta il punteggio minimo di somiglianza (da 0 a 1) necessario affinché due stringhe vengano considerate equivalenti:

• Un valore di 1.0 richiede una corrispondenza esatta (equivalente al raggruppamento standard)
• Un valore di 0.0 raggrupperebbe tutti gli elementi insieme (raramente utile)
• Valori tra 0.7 e 0.9 sono generalmente più efficaci per la maggior parte delle applicazioni

La scelta del valore ottimale dipende dalla natura dei tuoi dati:

• Per dati con piccole variazioni ortografiche: prova con 0.8-0.9
• Per variazioni più significative nella formulazione: prova con 0.6-0.8
• Per concetti correlati ma espressi diversamente: prova con 0.5-0.7

Le opzioni IgnoreCase e IgnoreSpace sono più semplici da configurare:

• IgnoreCase=true: utile nella maggior parte dei casi, poiché le differenze tra maiuscole e minuscole raramente indicano significati diversi
• IgnoreSpace=true: utile quando gli spazi sono incoerenti (es. “data base” vs “database”)

È consigliabile iniziare con impostazioni conservative (threshold alto) e gradualmente ridurre il valore se necessario. Dopo ogni modifica, esamina attentamente i risultati per verificare che il raggruppamento sia logico e coerente con le tue aspettative.

Ricorda che puoi sempre tornare indietro e modificare queste impostazioni se i risultati non sono soddisfacenti. Il processo di ottimizzazione delle opzioni di somiglianza è spesso iterativo e richiede sperimentazione. Alcuni algoritmi di somiglianza, come l’algoritmo di somiglianza Jaccard, possono essere particolarmente efficaci per certi tipi di dati, quindi vale la pena esplorare diverse opzioni.

Espandere i risultati del raggruppamento

Dopo aver applicato il raggruppamento fuzzy, otterrai una tabella con due colonne: la colonna dei valori raggruppati e una colonna contenente tabelle nidificate con tutti i dati originali. Per rendere questi risultati più utilizzabili, devi espandere le tabelle nidificate.

Per espandere i risultati:

1. Nella colonna contenente le tabelle nidificate, clicca sull’icona di espansione (due frecce divergenti) nell’intestazione della colonna
2. Nella finestra di dialogo che appare, seleziona le colonne che desideri includere nei risultati espansi
3. Scegli se mantenere o rimuovere il prefisso del nome della colonna originale
4. Clicca su “OK” per applicare l’espansione

Questo processo di espansione tabella trasformerà la struttura nidificata in una tabella piatta con tutti i dati originali, ma ora organizzati secondo il raggruppamento fuzzy applicato. Ogni riga mostrerà il valore raggruppato insieme ai dati originali corrispondenti.

Se la tabella originale conteneva molte colonne, potresti voler selezionare solo quelle più rilevanti durante l’espansione per mantenere i risultati gestibili. Puoi sempre modificare questa selezione in seguito se necessario. In alcuni casi, potrebbe essere utile anche considerare la rimozione colonne non necessarie per semplificare ulteriormente il dataset.

L’espansione dei risultati è particolarmente utile quando desideri:

• Vedere tutti i valori originali che sono stati raggruppati insieme
• Verificare l’accuratezza del raggruppamento fuzzy
• Eseguire ulteriori analisi sui dati raggruppati

Preparare i dati per la visualizzazione o la reportistica

Dopo l’espansione, è consigliabile riordinare le colonne in modo logico per facilitare l’interpretazione dei risultati. Puoi farlo trascinando le intestazioni delle colonne nella posizione desiderata o utilizzando l’opzione “Sposta” nel menu contestuale delle colonne. Questo passaggio è importante per creare una serie ben disposta e ordinata di dati che sarà più facile da analizzare e presentare.

In questa fase, potresti anche voler considerare la standardizzazione dei valori in alcune colonne per garantire coerenza nei tuoi report. Ad esempio, potresti voler uniformare il formato dei campi data o assicurarti che tutti i nomi siano in un formato coerente (es. “Cognome, Nome”). Queste operazioni di pulizia finale contribuiranno a migliorare la qualità complessiva del tuo dataset.

Caricare i risultati in Excel

Una volta completato il raggruppamento fuzzy e configurato l’output come desiderato, è il momento di caricare i risultati nuovamente in Excel per l’analisi finale o la presentazione. Per caricare i risultati:

1. Nell’Editor di Power Query, vai alla scheda “Home” nella barra multifunzione
2. Clicca sul pulsante “Chiudi e carica” per inviare i dati direttamente a Excel
3. In alternativa, clicca sulla freccia sotto “Chiudi e carica” e seleziona “Chiudi e carica in…” per più opzioni

Nella finestra di dialogo “Importa dati”, puoi scegliere:

• Tabella: carica i dati come tabella Excel formattata (opzione consigliata)
• Tabella pivot: crea direttamente una tabella pivot dai dati raggruppati
• Solo connessione: crea solo una connessione ai dati senza caricarli in un foglio
• Aggiungi questi dati al modello di dati: utile per analisi più complesse o per l’uso con Power Pivot

Seleziona anche la posizione in cui desideri caricare i dati:

• Foglio di lavoro esistente: specifica una cella in un foglio esistente
• Nuovo foglio di lavoro: crea un nuovo foglio per i risultati

Dopo aver confermato le tue scelte, Excel caricherà i dati raggruppati nella posizione specificata. I dati manterranno un collegamento dinamico con la query di Power Query, il che significa che potrai aggiornare i risultati se i dati di origine cambiano.

Per aggiornare i dati in futuro:

1. Seleziona qualsiasi cella nella tabella dei risultati
2. Vai alla scheda “Dati” nella barra multifunzione
3. Clicca su “Aggiorna tutto” o “Aggiorna” nel gruppo “Query e connessioni”

Questo rieseguirà la query di Power Query, applicando nuovamente il raggruppamento fuzzy a eventuali dati aggiornati. Questa funzionalità di aggiornamento automatico è particolarmente utile quando lavori con dati che cambiano frequentemente o quando desideri eseguire un merge di query da fonti diverse.

Verificare e perfezionare i risultati

Dopo aver caricato i risultati in Excel, è fondamentale verificare l’accuratezza del raggruppamento fuzzy e apportare eventuali perfezionamenti necessari. Anche con le migliori impostazioni, il raggruppamento automatico potrebbe non essere perfetto al primo tentativo. Ecco alcune strategie per verificare e migliorare i risultati:

1. Esamina i gruppi creati: ordina i dati per il valore raggruppato e verifica che tutti gli elementi in ciascun gruppo siano effettivamente correlati. Cerca anomalie o elementi che sembrano fuori posto.
2. Identifica falsi positivi: elementi diversi che sono stati erroneamente raggruppati insieme. Questi indicano che la soglia di somiglianza potrebbe essere troppo bassa.
3. Cerca falsi negativi: elementi simili che non sono stati raggruppati insieme come previsto. Questi suggeriscono che la soglia potrebbe essere troppo alta.
4. Aggiorna la tabella di traduzione: se trovi errori ricorrenti, aggiungi nuove mappature alla tabella di traduzione per correggerli esplicitamente.
5. Modifica le impostazioni di somiglianza: torna all’Editor di Power Query e modifica il valore di Threshold o le altre opzioni di somiglianza per migliorare i risultati.

Per modificare la query e perfezionare il raggruppamento:

1. Seleziona qualsiasi cella nella tabella dei risultati
2. Vai alla scheda “Query” o “Dati” nella barra multifunzione
3. Clicca su “Modifica” per riaprire l’Editor di Power Query
4. Modifica la formula di raggruppamento fuzzy o la tabella di traduzione
5. Chiudi e carica nuovamente per aggiornare i risultati

Il perfezionamento del raggruppamento fuzzy è spesso un processo iterativo che richiede diversi tentativi per ottenere i risultati ottimali. Non esitare a sperimentare con diverse impostazioni fino a trovare la combinazione che funziona meglio per i tuoi dati specifici. Questo processo di affinamento contribuirà a garantire l’integrità del dataset e la qualità dei tuoi risultati finali.

Casi d’uso pratici del raggruppamento fuzzy

Il raggruppamento fuzzy in Excel è uno strumento versatile con numerose applicazioni pratiche in vari settori. Ecco alcuni casi d’uso comuni dove questa funzionalità può fare la differenza:

• Pulizia dei database clienti: Identificare duplicati con piccole variazioni nei nomi (es. “Rossi S.p.A.” e “Rossi SpA”)
  • Standardizzare nomi di aziende acquisite o con marchi diversi
  • Unificare record clienti provenienti da sistemi diversi
• Analisi di feedback e sondaggi: Raggruppare risposte a domande aperte con significato simile
  • Identificare temi comuni in recensioni o commenti dei clienti
  • Categorizzare suggerimenti o reclami per prioritizzazione
• Gestione dell’inventario: Standardizzare nomi di prodotti inseriti manualmente
  • Identificare prodotti simili o equivalenti da fornitori diversi
  • Consolidare categorie di prodotti con nomenclature leggermente diverse
• Analisi finanziaria: Raggruppare voci di spesa simili ma registrate con nomi diversi
  • Standardizzare descrizioni di transazioni bancarie
  • Consolidare categorie di costo per reporting più accurato
• Ricerca e analisi di mercato: Raggruppare nomi di concorrenti con diverse varianti ortografiche
  • Standardizzare nomi di località o regioni geografiche
  • Unificare termini di settore o gergo tecnico

Per ciascuno di questi casi d’uso, il raggruppamento fuzzy offre un significativo risparmio di tempo rispetto alla categorizzazione manuale, riducendo al contempo il rischio di errori umani. La chiave per il successo è adattare le impostazioni di somiglianza e la tabella di traduzione alle specifiche esigenze del tuo scenario.

Limitazioni e alternative al raggruppamento fuzzy

Nonostante la sua potenza, il raggruppamento fuzzy in Power Query presenta alcune limitazioni che è importante conoscere:

Limitazioni principali:

• Funziona meglio con testi relativamente brevi; le frasi lunghe possono dare risultati imprevedibili
• Richiede Power Query, che potrebbe non essere disponibile in tutte le versioni di Excel
• Le prestazioni possono degradarsi con set di dati molto grandi (decine di migliaia di righe)
• L’algoritmo di somiglianza non è completamente trasparente o personalizzabile
• Non gestisce bene confronti multilingua o caratteri speciali

Per situazioni in cui il raggruppamento fuzzy non è adatto, considera queste alternative:

• Funzioni di ricerca approssimativa: CERCA.VERT combinato con funzioni come SIMILE o DISTANZA.TESTO
• Formule array complesse per identificare corrispondenze approssimative
• Add-in di terze parti specializzati in corrispondenze fuzzy
• Approcci esterni a Excel: Software specializzati per la deduplicazione dei dati
• Strumenti ETL (Extract, Transform, Load) con funzionalità di corrispondenza fuzzy
• Soluzioni di database con capacità di ricerca fuzzy
• Linguaggi di programmazione come Python o R con librerie per il matching fuzzy
• Metodi ibridi: Pre-elaborazione dei dati per standardizzare formati comuni
• • Raggruppamento iniziale basato su parti del testo (es. prime lettere)
  • Combinazione di raggruppamento automatico e revisione manuale

Se il raggruppamento fuzzy in Power Query non soddisfa le tue esigenze, valuta se una di queste alternative potrebbe essere più adatta al tuo caso specifico. In molti scenari, un approccio combinato che utilizza diverse tecniche può offrire i risultati migliori.

Conclusioni e best practice

Il raggruppamento fuzzy in Power Query rappresenta uno strumento potente ma spesso sottovalutato nell’arsenale di Excel. Permette di automatizzare un processo che altrimenti richiederebbe ore di lavoro manuale e soggetto a errori. Per ottenere i migliori risultati con il raggruppamento fuzzy, considera queste best practice:

1. Prepara adeguatamente i dati: pulisci i dati prima del raggruppamento, rimuovendo formattazioni incoerenti o caratteri speciali non necessari.
2. Investi nella tabella di traduzione: una tabella di traduzione ben costruita può migliorare significativamente i risultati, soprattutto per termini specifici del settore o abbreviazioni comuni.
3. Itera e perfeziona: non aspettarti risultati perfetti al primo tentativo. Sii pronto a sperimentare con diverse impostazioni di somiglianza e a perfezionare il processo.
4. Verifica i risultati: controlla sempre manualmente un campione dei risultati per assicurarti che il raggruppamento sia logico e coerente con le tue aspettative.
5. Documenta il processo: prendi nota delle impostazioni utilizzate e delle decisioni prese, soprattutto se prevedi di ripetere il processo in futuro.
6. Considera il contesto: adatta le impostazioni di somiglianza al contesto specifico dei tuoi dati e al livello di precisione richiesto.
7. Mantieni i dati originali: conserva sempre una copia dei dati originali non raggruppati per riferimento futuro o per iterazioni alternative.

Il raggruppamento fuzzy è particolarmente prezioso in un’epoca di crescente volume e varietà di dati. Padroneggiare questa tecnica ti permetterà di trasformare dati disordinati e incoerenti in informazioni strutturate e utilizzabili, migliorando significativamente la qualità delle tue analisi e dei tuoi report in Excel.

Pubblicato in Excel