Pulire Dati Excel: Testo, Numeri e Power Query

Trasformare fogli di calcolo caotici in informazioni utili. Funzioni indispensabili di Excel e automazione Power Query per la normalizzazione dei dati

Quando si lavora con dati provenienti da fonti diverse, la prima e spesso la più lunga fase non è l’analisi, ma la pulizia. Molti file che importiamo, magari estratti da vecchi gestionali o da report online, contengono errori di formattazione, spazi indesiderati, maiuscole e minuscole casuali o valori numerici confusi con il testo. Affrontare questa "sporcizia" cella per cella consuma ore che un analista o un professionista non può permettersi di sprecare.

Per questo motivo, l'efficacia in Excel non si misura più dalla conoscenza di formule basiche, ma dalla capacità di automatizzare la data cleansing. Qui di seguito, scopriamo come farlo con strumenti integrati, dalle formule dinamiche alle potenti funzionalità di Power Query.

Pulizia Rapida e Consistente del Testo

Il testo è l'elemento più ostico da trattare. Una singola riga può contenere un nome scritto in tre modi diversi, o indirizzi pieni di spazi che rendono impossibile l'uso di filtri e funzioni di ricerca. L’obiettivo principale in questo caso è ottenere l’uniformità del dato.

Standardizzazione di Maiuscole e Spazi

Due funzioni, spesso sottovalutate da chi usa Excel solo occasionalmente, permettono di sistemare quasi subito le anomalie più comuni e assicurano che il dato sia pronto per l'analisi o la ricerca con le funzioni di confronto.

Rimuovere Spazi Eccessivi con ANNULLA.SPAZI: Questa funzione è fondamentale perché rimuove tutti gli spazi iniziali e finali in una stringa, e riduce a un solo spazio tutti gli spazi multipli tra le parole. Se un testo è importato con formattazione disordinata, come ad esempio " Nome Cognome ", l'applicazione di questa formula lo trasforma in "Nome Cognome", risolvendo di colpo i problemi di allineamento e filtraggio.
Standardizzare la Capitalizzazione con MAIUSC.INIZ: Per rendere i nomi o i titoli coerenti, questa formula mette in maiuscola la prima lettera di ogni parola e in minuscolo tutte le altre, come si farebbe per i nomi propri. Ad esempio, =MAIUSC.INIZ("milano, italy") diventerà “Milano, Italy”. Le sue sorelle, MAIUSC e MINUSC, sono utili quando si deve imporre un formato interamente maiuscolo (ad esempio per i codici alfanumerici) o minuscolo (ad esempio per gli indirizzi email).

Estrazione e Separazione Intelligente del Testo

L’estrazione di una porzione specifica di testo (ad esempio un codice prodotto di cinque cifre inserito alla fine di una descrizione) richiede spesso la combinazione di più formule o l'uso di strumenti automatizzati.

Si usano in sequenza le funzioni RICERCA (SEARCH), TROVA (FIND) e ESTRAI.TESTO (MID) per individuare la posizione di un carattere delimitatore e prelevare i caratteri desiderati. La funzione RICERCA trova la posizione di un carattere all'interno di una stringa, e il suo risultato viene passato alla funzione ESTRAI.TESTO per sapere da dove iniziare l'estrazione e quanti caratteri includere. Questa tecnica è molto più precisa dell'uso della semplice funzione Da testo a colonne quando il delimitatore non è sempre lo stesso o se il testo da estrarre ha una lunghezza variabile.

Per le estrazioni più semplici, in cui il dato da separare segue uno schema prevedibile e uniforme (ad esempio estrarre solo l'iniziale del nome e il cognome), esiste l'opzione Riempimento Veloce (Flash Fill), disponibile dalla versione 2013 in poi. Basta digitare il risultato atteso nella prima cella della colonna vuota. Excel riconosce il pattern e, premendo la combinazione di tasti Ctrl+E o selezionando l'opzione dalla scheda Dati, riempie automaticamente le celle sottostanti. È il metodo più rapido per le manipolazioni di testo quando l'operazione non deve essere ripetuta regolarmente.

Sostituzioni Complesse (Annidate)

Quando si deve pulire una stringa da più simboli indesiderati (es. rimuovere il punto, la virgola e il simbolo di valuta) per renderla un numero, la funzione SOSTITUISCI (SUBSTITUTE) può essere annidata più volte. Poiché SOSTITUISCI può agire su un solo carattere alla volta, si incapsula il risultato della prima pulizia come input per la seconda, e così via.

Ad esempio, per trasformare la stringa "€ 1.200,50" in un numero puro, si usa una formula che esegue le sostituzioni in sequenza, per rimuovere i simboli che il formato regionale non riconosce, come segue:

=VALORE(SOSTITUISCI(SOSTITUISCI(A1; "€ "; ""); "."; "")) * 1

Questa formula rimuove il simbolo della valuta, poi rimuove i punti separatori delle migliaia e infine usa la funzione VALORE e la moltiplicazione per 1 per forzare la conversione del testo rimanente in un numero. È una soluzione essenziale quando si gestiscono dati con formati numerici misti.

Normalizzazione dei Numeri e Arrotondamento

I problemi con i numeri non riguardano solo la pulizia dei simboli, ma anche la corretta gestione degli arrotondamenti e la gestione delle liste di valori univoci per analisi corrette.

Arrotondamento al Multiplo Desiderato

Le classiche funzioni ARROTONDA o ARROTONDA.PER.DIF permettono l'arrotondamento alle cifre decimali, ma la funzione ARROTONDA.MULTIPLO (MROUND) offre un controllo maggiore. Questa funzione arrotonda un numero al multiplo più vicino specificato, il che è utile in scenari finanziari o di pianificazione.

Per arrotondare i prezzi dei prodotti a scaglioni di 50 (es. 1234 a 1250), si usa: =ARROTONDA.MULTIPLO(A1; 50).
Per arrotondare un valore orario a intervalli di 15 minuti, si usa un'espressione come: =ARROTONDA.MULTIPLO(A1; "0:15").

Identificazione Rapida dei Duplicati con Funzioni Dinamiche

Nelle versioni moderne di Excel (Microsoft 365 e 2021), la funzione a matrice dinamica UNICI (UNIQUE) è l'opzione più semplice. Prima dell'introduzione di questa funzione, per estrarre un elenco di valori senza ripetizioni si doveva ricorrere a complesse formule matriciali o all'uso di filtri avanzati. Oggi, la formula è estremamente diretta: =UNICI(A:A).

Questa singola formula restituisce in automatico e con un solo inserimento una lista pulita di tutti i valori presenti nella colonna A. Il risultato si "espande" nelle celle sottostanti (Spill), aggiornandosi dinamicamente al variare dei dati di origine. Si tratta di un'evoluzione che accelera di molto la creazione di report e liste di controllo pulite.

Power Query: La Soluzione Professionale per l'Automazione

Se la manipolazione riguarda migliaia di righe o se si tratta di un'operazione da ripetere con dati che arrivano regolarmente da file esterni, Power Query è l’unico strumento che garantisce un flusso di lavoro efficiente. Power Query non è una formula, ma uno strumento ETL (Extract, Transform, Load) integrato in Excel (scheda Dati > sezione Recupera e trasforma dati).

La sua forza è la ripetibilità. A differenza delle formule, dove ogni pulizia crea una nuova colonna, Power Query registra l’intera sequenza di pulizia (rimozione di spazi, cambio di formattazione, unione di tabelle) in un passaggio riutilizzabile. Ogni volta che si aggiorna la fonte dati, l'intera catena di trasformazioni viene applicata con un solo clic.

Tra le sue funzionalità più apprezzate per la pulizia vi è la possibilità di Raggruppa per con la funzione Fuzzy Matching (corrispondenza fuzzy), risolvendo quei problemi di battitura che le formule classiche non riescono a gestire se non con una lunga lista di condizioni. Permette, ad esempio, di accorpare in automatico "Roma" e "Roma " se ritiene che siano la stessa entità.

Altro che le persone chiedono e cercano sull'argomento

Di fronte alla complessità dei dati grezzi, gli utenti cercano spesso anche soluzioni a problemi specifici legati agli errori o all'uso di strumenti esterni.

Rimozione e Segnalazione Errori #N/A: Un problema frequente è gestire le formule di ricerca (come CERCA.VERT o XLOOKUP) che restituiscono errori quando il valore non viene trovato. Per evitare che il report diventi illeggibile, si usa la funzione SE.ERRORE (IFERROR) per sostituire l'errore con un valore vuoto ("") o un testo personalizzato, come ad esempio: =SE.ERRORE(XLOOKUP(…); "Valore non presente").
Strumenti per la Pulizia Esterna: Per la manipolazione di set di dati molto grandi e complessi, specialmente quelli con forti incoerenze testuali, molti professionisti utilizzano applicazioni open source. Il programma OpenRefine, installabile in locale, eccelle nella pulizia di dati testuali e nell'identificazione di valori simili da accorpare, offrendo funzionalità avanzate per la normalizzazione dei nomi e delle categorie.
Automazione della Divisione del Testo: Se il testo in una colonna è delimitato da una virgola, uno spazio o un altro carattere, lo strumento Testo in Colonne sulla scheda Dati offre la maniera più veloce. Se la divisione è un'operazione che si ripete su report settimanali o mensili, la funzione Dividi Colonna per Delimitatore all'interno di Power Query è da preferire, perché automatizza il processo per i futuri aggiornamenti.

In un'altra guida vi abbiamo parlato dei Migliori modelli Excel da scaricare gratis per operazioni finanziarie e commerciali e tanto altro.
Se non abbiamo ancora Excel sul nostro PC possiamo rimediare e scaricare Excel gratis.