Excel Data cleaning | Pulizia dei dati in Excel

Ripulire un foglio Excel con il data cleaning

Autore: Andrea Pacchiarotti
Ultimo aggiornamento: 07 Marzo 2023
Categoria: Microsoft Office Excel Data cleaning | Pulizia dati

Data cleaning Excel, ne hai mai sentito parlare? Forse la conosci come pulizia dei dati Excel.
In questo articolo descriverò come pulire un database Excel con il data cleaning per renderlo più fruibile. A volte capita di dover lavorare su dati corretti, ma disposti o formattati in un modo tale che risulta difficile o impossibile estrarre informazioni utili. E quindi, dato che non sempre si ha il controllo completo del formato delle informazioni importate da una qualsiasi origine dati esterna, spesso, per poter analizzare i dati, è necessario ripulirli. Parole con errori di ortografia, formattazione differente, spazi finali, prefissi indesiderati, ecc. fanno parte di un più lungo elenco dei modi in cui i dati possono essere sporcati nei fogli di lavoro di Microsoft Excel. Excel include varie caratteristiche che permettono il data cleaning ovvero che consentono di fare l’agognata pulizia dei dati Excel.
Per ripulire periodicamente la stessa origine dati è anche possibile registrare una macro o scrivere codice VBA per automatizzare l'intero processo. Esistono inoltre software di terze parti adatti allo scopo (scaricabili da ablebits.com, add-ins.com, addintools.com, winpure.com), ma se seguirete gli step che ora spiegherò, sarete in grado di effettuare un'ottima pulizia dei dati in Excel ovvero il cosiddetto Excel Data cleaning che, dunque, è il processo di pulizia e trasformazione dei dati presenti in un foglio di calcolo Excel, al fine di renderli pronti per l'analisi. Questa fase critica nella preparazione delle informazioni per l'analisi, può aiutare a garantire che le conclusioni derivate dai dati siano corrette e affidabili.
Curiosi? Sicuramente sì! Leggi allora Excel Data cleaning | Pulizia dei dati in Excel

Sommario Excel Data cleaning | Pulizia dei dati in Excel

• Cancellare formattazione
• Controllare l'ortografia
• Rimuovere duplicati
• Eliminare celle vuote
• Sostituire testo
• Modificare maiuscole e minuscole
• Rimuovere spazi e caratteri non stampabili
• Convertire i numeri memorizzati come testo in numeri
• Correggere date
• Suddividere il contenuto di una cella di testo in colonne
• Unire colonne
• Ridisporre colonne e righe

Cancellare formattazione

Risulta utile per pulire i dati e applicare la formattazione desiderata. Per eliminare la formattazione selezionare i dati e cliccare, dalla scheda Home, l’icona Cancella e poi scegliere Cancella formati.
È anche possibile cancellare tutto oppure solo il contenuto delle celle, i commenti e le note, i collegamenti ipertestuali.

Controllare l'ortografia

Utile non solo per trovare termini ortograficamente sbagliati, ma anche quelli adoperati in maniera incoerente (nomi di prodotto, nomi aziendali, ecc.), aggiungendoli a un dizionario personalizzato per fare la correzione automatica. Quest'ultima funzionerà solo da quel momento in poi e quindi non correggerà eventuali errori pregressi; anche nel caso si pensasse di fare il copia e incolla sul foglio Excel da Word o da qualche altra fonte, la correzione non avverrà automaticamente perché bisognerà comunque entrare in ogni cella da correggere con il doppio clic e poi dare Invio.
La correzione ortografica si trova nella scheda Revisione > Controllo ortografia (ma si può avviare anche premendo il tasto funzione F7).
Per aggiungere invece termini a un dizionario personalizzato, i cui termini sono case-sensitive, bisogna andare su File > Opzioni > Strumenti di correzione > Opzioni correzione automatica… In Sostituisci: va messo il termine scorretto da cercare, mentre in Con: quello con cui correggerlo automaticamente.

Rimuovere duplicati

Se preventivamente di desidera evidenziare i valori duplicati, bisogna andare nella scheda Home > Formattazione condizionale > Regole evidenziazione celle > Valori duplicati

Per filtrare i valori univoci, fare cliccare la scheda Dati > Avanzate e quindi rimuovere i valori duplicati. Per:

• filtrare in posizione
  • selezionare l’opzione Filtra l'elenco sul posto
  • selezionare la casella di controllo Copia univoca dei record e cliccare OK
    
• copiare i dati filtrati in un’altra posizione:
  • selezionare l’opzione Copia in un'altra posizione
  • nella casella di testo Copia in: inserire un riferimento di cella
  • selezionare la casella di controllo Copia univoca dei record e cliccare OK

Per rimuovere i valori duplicati selezionare i dati e cliccare la scheda Dati > Rimuovi duplicati (se i dati hanno intestazioni, spunta la casella di controllo Dati con intestazioni).

Eliminare celle vuote

Selezionare l’intervallo e cliccare la scheda Home > Trova e seleziona > Vai a formato speciale… (o premere il tasto funzione F5). Selezionare a sinistra l’opzione Celle vuote e cliccare OK per selezionare tutte le celle vuote.
Per eliminarle fare tasto destro su una delle celle selezionate e scegliere Elimina…
Anziché eliminarle è anche possibile:

• valorizzarle tutte digitando un testo e premendo CTRL+Invio
• evidenziarle tutte cliccando, dalla scheda Home
• Colore riempimento oppure
• Formattazione condizionale > Nuova regola > Formatta solo le celle che contengono e, dal menu a tendina Formatta solo celle con:, selezionare Valore vuoto. Scegliere il formato desiderato e cliccare OK

Sostituire testo

Se fosse necessario rimuovere una stringa o sottostringa comune, è possibile andare sulla scheda Home > Trova e seleziona > Sostituisci...

Modificare maiuscole e minuscole

Per convertire il testo in maiuscole, in minuscole, o applicando l'iniziale maiuscola, sono necessarie le funzioni:

• MAIUSC() converte da minuscolo a maiuscolo
• MINUSC() converte da maiuscole a minuscole
• MAIUSC.INIZ() converte in maiuscolo la prima lettera di ogni termine e in minuscolo il resto

Supponendo di voler sostituire i termini dell'intervallo A1:A10, posizionarsi in una cella vuota e scrivere =MAIUSC(A1), trascinare per 10 celle: si otterranno le maiuscole. Selezionare il contenuto ottenuto e copiarlo. Selezionare il range A1:A10 e incollare come Valori, poi eliminare le celle dove si era calcolata la funzione.

Rimuovere spazi e caratteri non stampabili

A volte i valori di testo possono produrre risultati imprevisti negli ordinamenti, filtri (leggi i filtri in Excel) e funzioni se contengono:

• spazi non necessari
  risolvibile con la funzione ANNULLA.SPAZI() che permette di eliminare spazi iniziali, spazi aggiuntivi tra le parole (non gli spazi singoli), spazi finali
  Ad esempio volendo eliminare gli spazi dall'intervallo A1:A10, posizionarsi in una cella vuota e scrivere =ANNULLA.SPAZI(A1), trascinare per 10 celle: si otterranno gli stessi valori senza spazi. Selezionare il contenuto ottenuto e copiarlo. Selezionare il range A1:A10 e incollare come Valori, poi eliminare le celle dove si era calcolata la funzione
• caratteri non stampabili (ad esempio la &)
  risolvibile con la funzione LIBERA()
  Ad esempio si immagini di avere nel range A1:A10 una serie di formule del tipo =A1&" "&A2. Volendo eliminare i caratteri non stampabili (la & e le virgolette) dall'intervallo A1:A10, posizionarsi in una cella vuota e scrivere =LIBERA(A1), trascinare per 10 celle: si otterranno gli stessi valori senza caratteri non stampabili. Selezionare il contenuto ottenuto e copiarlo. Selezionare il range A1:A10 e incollare come Valori, poi eliminare le celle dove si era calcolata la funzione

Convertire i numeri memorizzati come testo in numeri

A volte si ha questa necessità nell’importazione dei dati da sorgenti esterne, che potrebbero successivamente creare problemi quando si desidera utilizzare queste celle nei calcoli.
Selezionare una cella vuota all’esterno dell’intervallo dati. Digitare il numero 1 e pigiare CTRL+Invio. Copiare con CTRL+C. Selezionare l’intervallo da convertire in numeri. Cliccare Incolla > Incolla speciale… selezionando a destra l’opzione Moltiplica (Excel moltiplica ogni cella per 1 convertendo il testo in numeri). Cliccare OK.

Correggere date

È possibile riscontrare problemi copiando e incollando date tra file con diversi sistemi di data. Excel per Mac ed Excel per Windows supportano infatti i sistemi di data 1900 e 1904, ma il sistema di data predefinito di Windows è 1900 (1 gennaio 1900 valore seriale 1) e quello per Mac è 1904 (1 gennaio 1904 valore seriale 1).
Ad esempio, copiando la data 5 luglio 2007 da un file che usa il sistema 1900 e incollandola in un file con il sistema 1904, la data diventa 6 luglio 2011 (1.462 giorni più tardi). Copiando la data 5 luglio 2007 da un file che usa il sistema 1904 e incollandola in un file che usa il sistema 1900, la data diventa 4 luglio 2003 (1.462 giorni prima). Ecco come correggere il problema:

• In una cella vuota scrivere 1462
• Selezionare la cella e copiarla
• Selezionare le celle con le date scorrette
• Cliccare Incolla > Incolla speciale…, selezionare l’opzione Valori e, nella sezione Operazione, per impostare la data come quattro anni e un giorno:
• in un secondo momento, cliccare Aggiungi
• prima, cliccare Sottrai

In Windows si può modificare l’impostazione del sistema data da File > Opzioni > Impostazioni avanzate

Suddividere il contenuto di una cella di testo in colonne

Importare i dati da un file di testo a volte comporta che più dati vengano inseriti in una cella e divisi da un qualche separatore (virgola, spazio, ecc.). Necessita dunque dividere il contenuto in colonne separate.
Selezionare le celle con il testo da dividere e cliccare scheda Dati > Testo in colonne
Compare la finestra di conversione guidata, passaggio 1 di 3, dove va selezionato il tipo di dati tramite l’opzione Delimitato o Larghezza fissa: la prima opzione è utile quando si hanno i dati divisi da un qualsiasi separatore, mentre la seconda quando si vuole ad esempio separare stringhe della stessa lunghezza come codici fiscali, IBAN e similari.
Se i dati sono separati da tabulazione, punto e virgola, virgola, spazio, selezionare Delimitato. Cliccare Avanti e selezionare il delimitatore ovvero il carattere che separa i dati (se il carattere non è presente usare Altro).
Nell’ultima schermata, passaggio 3 di 3, selezionare il formato dei dati e specificare la cella di destinazione. Se quest’ultima non viene selezionata, la cella contenente i dati verrà sovrascritta.

Unire colonne

Un'attività comune dopo l'importazione di dati sta nell'unione dei dati contenuti in due o più colonne, ad esempio si desidera unire due colonne Nome e Cognome in una colonna che comprenda entrambi separati da uno spazio. In tal caso è possibile usare l'operatore & o la funzione CONCATENA. Volendo unire in C1 il contenuto della cella A1 e B1, separandole da spazio, basta scrivere in C1 la formula A1&" "&B1 o la funzione =CONCATENA(A1;" ";B1)

Ridisporre colonne e righe

A volte si desidera trasformare le righe in colonne e le colonne in righe, per farlo è possibile usare la funzione MATR.TRASPOSTA. Ad esempio, nell'immagine seguente la formula =MATR.TRASPOSTA(A1:B4) dispone le celle da A1 a B4 in orizzontale.

Procedere nel modo seguente

1. Selezionare lo stesso numero di celle dei dati da trasporre, ma in senso opposto. Ad esempio, l'immagine precedente mostra 8 celle disposte in verticale (A1:B4), quindi bisognerà selezionarne 8 in orizzontale (A6:D7)
2. Con le celle vuote ancora selezionate, digitare =MATR.TRASPOSTA(A1:B4), ma non premere INVIO, bensì Ctrl+Shift+Invio in quanto la funzione in questione viene usata nelle formule di matrice e questo è il modo in cui si termina una funzione di matrice (ovvero una funzione che viene applicata a più celle).

Esiste anche un altro modo probabilmente più veloce, eccolo qui:

1. Selezionare le celle da trasporre (A1:B4)
2. Copiare
3. Selezionare lo stesso numero di celle dei dati da trasporre (A6:D7)
4. Incollare scegliendo la modalità Trasponi

Se Excel Data cleaning | Pulizia dei dati in Excel ti è piaciuto, condividilo!

Per saperne di più su Microsoft Excel potrebbero interessarti i video corsi Excel o questi libri: