Come pulire i dati in Excel
Pulizia dati in Excel. Scopri come pulire i dati in Excel con tecniche pratiche, esempi prima e dopo e flussi di lavoro ripetibili e scalabili.
Un export dal CRM arriva poco prima del primo incontro. Le righe sembrano familiari, ma gli spazi finali impediscono alle ricerche di trovare corrispondenze, le date arrivano in formati diversi, le celle unite mandano in tilt i filtri e una seconda riga di intestazione compare a metà del foglio. Le formule continuano comunque a calcolare, e questo rende il file più pericoloso, non meno.
Una pulizia dei dati in Excel affidabile non significa semplicemente rendere ordinato un foglio di lavoro. Significa preservare l’input originale, applicare trasformazioni che altri possano verificare e produrre valori che formule, tabelle pivot, grafici e modelli a valle possano usare in sicurezza. La ricerca sui fogli di calcolo considera da tempo la pulizia un’attività ad alto rischio: una revisione importante riporta che il 94% dei fogli di calcolo contiene errori, con un tasso medio di errore per cella del 5,2% negli studi storici presi in esame (revisione della letteratura sugli errori nei fogli di calcolo).
L’approccio pratico è un flusso di lavoro governato. Vedrai dove funzioni come TRIM, CLEAN, SUBSTITUTE, VALUE e DATEVALUE fanno risparmiare tempo, dove gli strumenti strutturali risolvono problemi che le formule non riescono a raggiungere e quando Power Query diventa la sostituzione sensata delle modifiche manuali ripetute. Se anche il tuo processo di reportistica più ampio dipende da input affidabili, dati aziendali affidabili con Streamkap offre un contesto utile sulla qualità dei dati oltre il singolo foglio di lavoro.
Indice dei contenuti
- Quando un foglio di calcolo in disordine ti ruba la mattinata
- Preparare un ambiente di lavoro sicuro per la pulizia
- Pulire testo e valori con le funzioni integrate
- Correggere struttura, date e formattazioni miste
- Quando la pulizia manuale non scala più
- Un flusso di lavoro di pulizia ripetibile e riusabile
- Abitudini che tengono i dati puliti anche domani
Quando un foglio di calcolo in disordine ti ruba la mattinata
Il primo istinto è spesso quello di mettersi a correggere i problemi visibili. Eliminare l’intestazione fuori posto, rimuovere le righe vuote, lanciare Trova e sostituisci e incollare i risultati sopra l’originale. Sembra un lavoro produttivo, finché l’export successivo non cambia forma e le formule posizionate con cura puntano alle colonne sbagliate.
Uno spazio finale in Customer Name può far fallire una ricerca esatta. Una data memorizzata come testo può sparire da un calcolo. Una categoria inserita come Retail, retail e RETAIL può comparire come etichette separate in una tabella pivot, mentre una cella unita apparentemente innocua può compromettere l’ordinamento o il filtro. Le funzioni VLOOKUP possono restituire corrispondenze mancanti senza rendere evidente la causa sottostante, e le formule possono finire per contare i record sbagliati.
Regola pratica: se un’azione di pulizia non può essere spiegata e ripetuta, considerala una riparazione temporanea, non un flusso di lavoro completo.
Un passaggio governato produce un risultato diverso nella stessa sessione di lavoro. Le stringhe diventano coerenti, le date diventano interpretabili, le righe duplicate vengono valutate rispetto a chiavi definite e l’output pulito resta collegato alla fonte. Un collega che riapre il file in un secondo momento dovrebbe riuscire a capire cosa è cambiato, perché è cambiato e da dove arriva il valore originale.
La differenza conta, perché gli errori possono propagarsi in formule, riepiloghi, grafici e decisioni. La letteratura di ricerca sottolinea che un rilevamento affidabile richiede più di una formattazione visiva. L’ispezione a livello di cella, il controllo delle formule e le verifiche di coerenza hanno tutti un ruolo, ed è per questo che un processo affidabile batte una collezione di trucchi brillanti pensati per l’occasione.
Preparare un ambiente di lavoro sicuro per la pulizia
Un ambiente di lavoro sicuro nasce prima della prima modifica. Salva un backup datato, lascia invariato il file ricevuto e lavora su una copia separata. Blocca la riga superiore così le intestazioni restano visibili e conserva un punto di ripristino prima di eliminare, incollare o sostituire valori. Questi passaggi rendono recuperabili le modifiche accidentali agli intervalli, senza dover ricostruire la fonte.
Converti l’intervallo di origine in una tabella di Excel. Intestazioni stabili, compilazione automatica delle formule e riferimenti strutturati sono più facili da verificare rispetto alle coordinate fisse delle celle. Usa la tabella per ispezionare i dati e produrre output in modo controllato, mantenendo i valori importati separati da qualsiasi logica di trasformazione. La guida di Microsoft sul profiling con Power Query illustra come profilare i dati ed esaminare vuoti, errori e duplicati nell’ambito di un processo di pulizia ripetibile.
Separare logica di origine e output
Usa tre livelli con nomi chiari:
- Foglio grezzo: l’importazione invariata, con intestazioni e valori originali.
- Foglio di pulizia: colonne di appoggio, formule, mappature e controlli di validazione.
- Foglio di output: la tabella pronta per l’analisi, l’origine della pivot o il risultato del report.
Mantieni un campo per colonna. Usa intestazioni descrittive, includi le unità di misura dove serve ed elimina le celle unite dal blocco di dati. Evita di collocare tabelle non correlate nello stesso foglio di lavoro o di tenere versioni concorrenti sparse tra le schede. Intestazioni coerenti e valori null gestiti in modo uniforme rendono più semplici i controlli successivi, soprattutto quando un altro analista eredita il file.
Per modifiche di grandi dimensioni, il calcolo manuale può ridurre i rallentamenti, ma ricalcola e verifica prima di salvare. Disattiva l’AutoCorrezione dove il testo esatto conta, come per identificatori e codici importati. Aggiungi un Cleaning Log con il nome del file di origine, la data, l’operatore e una traccia sintetica di ogni trasformazione.
Questa struttura protegge la tracciabilità. Il foglio grezzo mostra il valore di partenza, la logica di appoggio mostra come è cambiato e il log registra la decisione. La guida di Microsoft sulla pulizia dei dati in Excel consiglia comunque di conservare i dati grezzi e di usare colonne di appoggio prima di sostituire i campi di origine.
Pulire testo e valori con le funzioni integrate
La pulizia basata su formule funziona meglio a livello di singola cella. Mantieni il valore grezzo in Raw!A2, poi colloca la trasformazione in una colonna di appoggio invece di sovrascrivere l’input. In questo modo preservi la tracciabilità e puoi confrontare i valori prima e dopo affiancati.
TRIM rimuove gli spazi iniziali e finali e comprime gli spazi interni ripetuti. Se A2 contiene North Region , =TRIM(A2) restituisce North Region. È un primo passaggio utile per nomi, località e categorie che non superano la corrispondenza esatta a causa di spazi normali.
CLEAN rimuove i caratteri non stampabili. I dati copiati da sistemi datati o da pagine web possono contenere caratteri invisibili nella griglia. Per gli spazi più ostinati, inclusi quelli unificatori, combina più funzioni:
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))
La formula sostituisce lo spazio unificatore con uno spazio normale, rimuove i caratteri non stampabili e poi normalizza il risultato.
Convertire i valori in modo consapevole
SUBSTITUTE è utile prima di convertire testo in numeri. Se A2 contiene $1,250, una formula come =VALUE(SUBSTITUTE(SUBSTITUTE(A2,"$",""),",","")) rimuove il simbolo di valuta e la virgola prima della conversione. VALUE trasforma poi il testo rimanente in un numero che SUM e AVERAGE possono usare.
Per i formati regionali, NUMBERVALUE offre un controllo esplicito sui separatori decimali e delle migliaia. Questo conta quando un export usa la virgola come separatore decimale mentre il foglio di lavoro si aspetta il punto. Anche i separatori delle formule possono variare in base alle impostazioni locali di Excel, quindi verifica la sintassi nell’ambiente di destinazione invece di copiare una formula alla cieca.
Le date richiedono la stessa disciplina. DATEVALUE converte un testo di data riconoscibile nel numero seriale di data di Excel, mentre TIMEVALUE gestisce i testi di orario. TEXT controlla il risultato visualizzato, per esempio =TEXT(B2,"yyyy-mm-dd"), ma per i calcoli conviene conservare un vero valore data e usare TEXT solo per la presentazione o la formattazione in export.
Per altri esempi e schemi di formule, la guida alla creazione di formule in Excel aiuta a tradurre la trasformazione desiderata in un’espressione funzionante.
| Funzione | Input prima | Output dopo | Caso d’uso |
|---|---|---|---|
TRIM | Acme Ltd | Acme Ltd | Normalizzare gli spazi normali |
CLEAN | Testo con caratteri di controllo nascosti | Testo stampabile | Riparare testo importato o estratto dal web |
SUBSTITUTE | $1,250 | 1250 prima della conversione | Rimuovere simboli o sostituire caratteri |
VALUE | "1250" | 1250 come numero | Rendere calcolabile un testo numerico |
DATEVALUE | "12/03/2024" | Valore data di Excel | Convertire testo di data riconoscibile |
TEXT | Un valore data valido | Visualizzazione 2024-03-12 | Presentare le date in modo coerente |
La pulizia con formule mostra i suoi limiti quando una sola espressione cerca di risolvere ogni input possibile. Una formula annidata può essere potente, ma diventa difficile da verificare quando contiene molte eccezioni, ipotesi sulle impostazioni locali e regole di sostituzione. Quando conta la possibilità di revisionare il lavoro, tieni ogni trasformazione significativa nella sua colonna di appoggio.
Correggere struttura, date e formattazioni miste
Alcuni problemi dei fogli di calcolo non sono problemi di valori di cella. Una formula può pulire il testo dentro una cella, ma non può decidere in sicurezza come suddividere una colonna che contiene Smith, Jordan o riparare un foglio in cui intestazioni unite interrompono l’area dei dati.
Usa Testo in colonne quando un campo contiene un delimitatore coerente. Nomi separati da virgole, codici e frammenti di indirizzo possono essere suddivisi in campi separati, ma controlla prima l’anteprima del risultato. La procedura guidata può sovrascrivere le colonne adiacenti se non hai inserito abbastanza spazio, quindi lavora su una copia o su un’area di appoggio.
Per l’operazione inversa, & gestisce le unioni semplici, come =A2&" "&B2, mentre TEXTJOIN gestisce intervalli e delimitatori in modo più pulito. La Compilazione rapida (Flash Fill) è comoda per le attività basate su schemi, come estrarre il dominio da un indirizzo email o trasformare un nome da Last, First a First Last. Da sola, però, non è una trasformazione governata. Controlla lo schema generato, soprattutto quando compaiono eccezioni a metà dei dati.
La formattazione può creare falsa sicurezza. Usa Copia formato solo quando conosci bene l’intervallo di destinazione e usa Incolla speciale valori quando devi rimuovere le dipendenze dalle formule da un output finale. I caratteri nascosti possono sopravvivere alla pulizia visiva, quindi un valore che sembra identico va comunque sottoposto a una funzione o a un test di confronto.
Rendere le date univoche
12/03/2024 può rappresentare date diverse a seconda della convenzione regionale. Non normalizzarla limitandoti a cambiare il formato della cella. Stabilisci prima se la fonte intende giorno-mese-anno o mese-giorno-anno, poi costruisci una vera data con DATE, YEAR, MONTH e DAY, oppure usa l’analisi con impostazioni locali di Power Query quando la convenzione della fonte è esplicita.
Numeri seriali, date rilevate automaticamente e date in formato testo dovrebbero confluire in un’unica colonna dedicata alle date, con un tipo di dato coerente. Visualizza quel valore in uno stile ISO quando utenti o sistemi hanno bisogno di una rappresentazione univoca.
Un numero memorizzato come testo spesso mostra un triangolo verde di avviso, è allineato a sinistra o viene ignorato da SUM. Seleziona le celle interessate e usa il menu di avviso per convertirle, moltiplica per uno in una formula di appoggio oppure applica VALUE o NUMBERVALUE quando serve una gestione esplicita. La scelta giusta dipende dalla presenza di separatori, simboli e regole locali.
Quando la pulizia manuale non scala più
La pulizia con formule funziona bene per una correzione puntuale e controllata. I limiti emergono quando gli export crescono, arrivano con regolarità o devono essere verificati da qualcun altro. TRIM e SUBSTITUTE possono rallentare su intervalli molto grandi, la Compilazione rapida può dedurre uno schema diverso dopo che la fonte è cambiata e le colonne di appoggio possono spargere la logica di trasformazione per tutto il file. Incollare il risultato sopra la fonte fa risparmiare tempo sul momento, ma elimina il collegamento visibile tra input e trasformazione.
Power Query si adatta alla pulizia ricorrente perché il processo può essere salvato e aggiornato. Supporta azioni come profilare i dati, mantenere o rimuovere i duplicati, rimuovere i valori vuoti, rimuovere gli errori e sostituire gli errori. Ogni azione viene registrata nel riquadro Passaggi applicati, quindi aggiornando la query la stessa sequenza viene eseguita su una nuova fonte, senza ricostruzioni manuali.
Il compromesso è la manutenzione. Power Query richiede tempo per essere appreso e gli stakeholder abituati ai vecchi flussi di lavoro basati sui fogli di calcolo possono trovare un file basato su query meno familiare. Il ritorno è un processo documentato, ispezionabile, aggiornabile e trasferibile a un altro analista. In genere offre una tracciabilità più solida rispetto a una lunga catena di formule da ricostruire dopo ogni export.
Scegliere il metodo in base al carico di lavoro
Gli intervalli indicati qui sotto sono indicazioni operative, non limiti tecnici di Excel. Segnalano quando il costo di gestione del lavoro manuale supera in genere la sua comodità.
| Approccio | Intervallo di righe ideale | Tracciabilità | Riproducibilità |
|---|---|---|---|
| Pulizia manuale | Meno di 1.000 righe | Bassa, salvo un log accurato | Bassa |
| Formule e colonne di appoggio | Da 1.000 a 50.000 righe | Media, se i livelli di origine e di appoggio restano separati | Media |
| Power Query o script | Oltre 50.000 righe | Alta, grazie ai passaggi registrati o al codice | Alta, tramite aggiornamento o riesecuzione |
Power Query è una scelta solida quando la stessa fonte arriva più volte, le colonne possono spostarsi o più analisti devono verificare lo stesso risultato. Per i file ricchi di formule, la pulizia dei fogli di calcolo assistita dall’AI può aiutare a predisporre le trasformazioni. Considera le formule generate come punto di partenza, poi verificale sui valori grezzi e sulle regole di business documentate.
Il numero di righe da solo non dovrebbe decidere il metodo. Un report piccolo che richiede tracciabilità rigorosa può giustificare Power Query, mentre una lista personale da sistemare una volta può essere più veloce da pulire a mano. Chiediti se il lavoro si ripete, se un altro analista deve verificarlo e se la struttura dell’input cambia nel tempo. Sono queste risposte a stabilire se una correzione rapida con una formula resta pratica o diventa un processo non documentato che manda in errore le formule a valle.
Un flusso di lavoro di pulizia ripetibile e riusabile
Considera il file come una piccola pipeline di dati con cinque fasi. L’ordine ti protegge dal validare un risultato prodotto a partire da una fonte già compromessa.
Fasi uno e due: stabilire il controllo
Prima viene il backup. Salva una copia datata, conserva l’input originale e proteggi la scheda di origine. Se un’operazione distruttiva sfugge al controllo, puoi ripristinare il punto di partenza invece di tirare a indovinare cosa è stato modificato.
Profila prima di trasformare. Usa Ctrl+Down per ispezionare l’estensione reale di ogni colonna, applica la formattazione condizionale per far emergere vuoti e duplicati e usa LEN per trovare valori insolitamente corti o lunghi. La profilazione ti dà una mappa dei problemi e ti evita di scambiare i sintomi di formattazione per record duplicati.
Fasi tre, quattro e cinque: creare le evidenze
Trasforma in una sequenza stabile. Applica le funzioni di testo nelle colonne di appoggio, ripara la struttura, normalizza date e tipi numerici e solo allora prepara l’output finale. La guida di Microsoft consiglia di inserire una colonna di appoggio, copiare la formula di trasformazione verso il basso, incollare i valori ed eliminare l’originale solo dopo aver verificato il risultato (guida di Microsoft sulla pulizia in Excel).
Valida rispetto alla fonte. Confronta i totali, usa COUNTIF per confermare le categorie o gli stati attesi ed esamina le eccezioni invece di fidarti di una griglia dall’aspetto pulito. Esegui Rimuovi duplicati dopo aver normalizzato i dati, così incoerenze di spaziatura e maiuscole non mascherano il numero reale di duplicati.
Documenta il risultato. Un foglio Note dovrebbe registrare il nome del file di origine, le trasformazioni applicate, i controlli di validazione, la data e ogni ipotesi su date, valori mancanti o mappature di categoria. Se lavori anche su Google Sheets, collegare Google Sheets a ChatGPT può supportare i flussi di analisi, ma lo stesso standard di documentazione resta valido.
La sequenza conta quanto le singole azioni. Il backup rende possibile il ripristino, la profilazione rivela l’entità dei problemi, la trasformazione modifica i valori, la validazione mette alla prova il risultato e la documentazione rende il processo comprensibile a chi verrà dopo.
Abitudini che tengono i dati puliti anche domani
Una pulizia affidabile inizia prima che l’export arrivi. Standardizza i formati di data e numero al momento dell’inserimento, usa menu a tendina con convalida dati per le categorie controllate e mantieni le intestazioni stabili lavorando dentro una tabella di Excel. Queste scelte riducono il numero di riparazioni necessarie in seguito.
Archivia ogni file pulito con un nome datato e un log delle modifiche di una riga. Non sovrascrivere mai la scheda di origine sul posto e non affidarti a cicli fragili di Trova e sostituisci che possono alterare etichette, formule o identificatori fuori dall’intervallo previsto.
Per gli export ricorrenti semi-strutturati, la pulizia assistita dall’AI in Google Sheets o Excel può aiutare a classificare i valori, suggerire formule, standardizzare i campi e segnalare anomalie. GPT Workspace offre funzionalità dedicate ai fogli di calcolo per analisi e pulizia dei dati all’interno di Google Workspace, ma lo strumento non sostituisce la conservazione della fonte, la validazione o un log chiaro.
Prima che arrivi il prossimo file in disordine, ricorda: conserva i dati grezzi, profila prima di modificare, trasforma nelle colonne di appoggio, valida rispetto alla fonte e documenta il risultato.
GPT Workspace porta l’assistenza dell’AI in Gmail, Docs, Sheets, Slides, Drive e Forms, tra cui la generazione di formule per fogli di calcolo, l’analisi e i flussi di pulizia dei dati. Se vuoi ridurre la preparazione ripetitiva dei fogli di calcolo mantenendo le trasformazioni verificabili, visita GPT Workspace e scopri come si adatta ai tuoi file esistenti.