Che cos'è la convalida dei dati in Excel? Guida completa per aggiungere, modificare e rimuovere regole di convalida dei dati in Excel

Capire e applicare i principi della convalida dei dati è essenziale sia per i junior data analyst sia per gli specialisti più esperti. In questo tutorial vedremo come eseguire la convalida dei dati in Excel. Copriremo le varie tecniche disponibili e mostreremo come aggiungere, modificare e rimuovere le regole di convalida.

Se vuoi ripassare le basi di Excel prima di iniziare, incluse le principali formule, gli operatori, le funzioni matematiche e altro ancora, esplora il nostro Foglio di formule di Excel.

Che cos'è la convalida dei dati in Excel?

La convalida dei dati in Excel è una funzionalità specifica che controlla i valori che possono essere inseriti in una cella. Offre una serie di tecniche che possono essere usate per verificare valori esistenti o per creare regole che si applicheranno a quelli nuovi.

Per trovare la funzione Convalida dati in Excel, segui questi passaggi:

1. Apri la scheda Dati.
2. Vai al gruppo Strumenti dati.
3. Fai clic sul pulsante Convalida dati.

Apertura della funzione Convalida dati

Compare quindi la seguente finestra pop-up sullo schermo.

Finestra pop-up Convalida dati

Più avanti nel tutorial vedremo nel dettaglio come configurare questa finestra, incluse le modifiche e la rimozione delle regole di convalida. Prima, però, approfondiamo che cos'è la convalida dei dati.

Perché la convalida dei dati è importante?

La convalida dei dati è il processo di verifica dell'accuratezza e della coerenza dei dati che sono stati o saranno inseriti in un database per l'archiviazione e l'elaborazione. Con la convalida ci assicuriamo che i dati rispettino criteri specifici e predefiniti. Questi criteri possono includere il tipo di dato corretto, valori all'interno di un intervallo specificato e elementi di dato con la lunghezza appropriata.

Una convalida solida è fondamentale per molti motivi. Protegge dagli errori di inserimento e garantisce che le informazioni usate nelle analisi siano affidabili e valide. Vediamo più da vicino le principali ragioni della convalida dei dati.

Qualità dei dati

La convalida impedisce che valori inesatti o incoerenti vengano memorizzati nel database. Questo riduce gli errori successivi, aumenta la qualità complessiva dei dati e rende più significativo l'esito dell'analisi.

L'importanza di avere dati di alta qualità è ben riassunta dal concetto GIGO, diffuso nell'informatica, che sta per garbage-in e garbage-out. In sostanza, analizzare dati difettosi porta a risultati errati e fuorvianti.

Vediamo alcuni esempi in cui le regole di convalida non sono state applicate e i nostri dati presentano valori strani.

• Registri universitari: Un'università non può avere un numero decimale di studenti; sono validi solo i numeri interi.
• Cartelle cliniche: La temperatura corporea massima di una persona non può superare i 50°C.
• Voci sull'età: Un campo età dovrebbe accettare solo valori realistici, tipicamente tra 0 e 120.
• Indirizzi email: Un campo email dovrebbe seguire un formato specifico, che include il simbolo "@".
• Conteggi d'inventario: Le quantità di prodotto in un sistema di inventario non dovrebbero essere negative.

Ricorda che, pur garantendo l'integrità dei dati, la convalida non assicura la correttezza assoluta. Per esempio, in una tabella su gatti e cani, una regola può consentire solo voci "gatto" o "cane", ma non può impedire che per errore un gatto venga etichettato come cane.

Integrità dei dati e conformità

La convalida è cruciale per mantenere l'integrità dei dati e garantire la conformità a regolamenti e standard di settore.

In sanità, il rispetto dell'HIPAA è obbligatorio per proteggere le informazioni dei pazienti. Allo stesso modo, le aziende che operano nell'UE devono aderire al GDPR, garantendo privacy e sicurezza dei dati per gli individui.

Pulizia e pre-elaborazione dei dati

Un principio noto afferma che i data analyst trascorrono circa l'80% del loro tempo a preparare i dati e solo il 20% a fare analisi. Poiché spesso dedicano una parte sproporzionata del tempo alla pulizia e alla pre-elaborazione, applicare regole di convalida già nelle fasi iniziali del flusso di lavoro può far risparmiare molto tempo.

Processo decisionale

Analizzare dati convalidati porta a risultati affidabili e utili che, a loro volta, aiutano a prendere decisioni più informate e guidate dai dati. Nel lungo periodo, la convalida fa risparmiare tempo e denaro all'azienda e contribuisce a un business più sano, efficiente e redditizio.

Diverse tecniche di convalida dei dati in Excel

Excel offre diversi approcci di convalida per esigenze differenti. Per praticità, dividiamoli in gruppi e caratterizziamoli.

Limitare tipo di dato e intervallo/lunghezza

In Excel è possibile scegliere tra i seguenti tipi di dati: Whole number, Decimal, Date, Time e Text.

Creiamo ora una tabella per valutare l'applicabilità di alcune condizioni logiche ai diversi tipi di dati. Le condizioni logiche includono: between, not between, equal to, not equal to, greater than, less than, greater than or equal to e less than or equal to.

• Between e Not Between: Queste condizioni sono in genere usate con valori numerici, date e orari. Quando applicate al text, between e not between si riferiscono alla lunghezza, ovvero al numero di caratteri in una cella.

• Equal to e Not Equal to: Possono applicarsi a tutti i tipi di dati. Per il text, verificano le corrispondenze esatte.

• Greater Than, Less Than, Greater Than or Equal To e Less Than or Equal To: Queste condizioni sono adatte ai tipi whole number, date e time, ma non si applicano direttamente al text a meno di considerare la lunghezza del testo o, in alcuni casi specifici, l'ordine alfabetico.

Creare elenchi a discesa

A volte può essere necessario limitare le opzioni di alcune celle di Excel a un insieme predefinito di valori. Riprendiamo l'esempio in cui nella tabella sono validi solo gatti e cani. Il modo migliore per creare una regola di convalida che impedisca l'inserimento di coccodrilli e altri animali è creare un elenco a discesa per ogni cella pertinente. Vediamo i passaggi nel dettaglio.

1. Inserisci gli elementi che vuoi compaiano nell'elenco a discesa. Puoi anche ordinarli selezionando l'elenco completo, facendo clic con il tasto destro, selezionando Ordina e scegliendo un metodo.

Inserimento degli elementi per un elenco a discesa

2. Converti l'elenco in una tabella di Excel. Seleziona una cella nell'intervallo e premi Ctrl+T. Questo passaggio è importante perché, se aggiungi o rimuovi elementi dall'elenco, tutti gli elenchi a discesa dipendenti si aggiorneranno automaticamente.

Conversione dell'elenco in tabella

3. Seleziona la cella o le celle del foglio di lavoro in cui vuoi aggiungere l'elenco a discesa.
4. Apri la finestra pop-up Convalida dati. Vai alla scheda Dati. Nel gruppo Strumenti dati fai clic sul pulsante Convalida dati.
5. Nella scheda Impostazioni, nel campo Consenti, seleziona Elenco. Lascia selezionata la casella Menu a discesa nella cella. Lascia selezionata la casella Ignora celle vuote se la cella o le celle in cui vuoi aggiungere l'elenco possono rimanere vuote; in caso contrario deselezionala:

Impostazione di un elenco a discesa come metodo di convalida

6. Posiziona il cursore nella casella Origine, seleziona l'intervallo dell'elenco e premi OK. Devi escludere la riga di intestazione per non averla tra le opzioni di selezione.

Selezione dell'origine per un elenco a discesa

7. Seleziona la cella o le celle per cui hai creato questa convalida. Ora vedrai un triangolino a discesa a destra della cella. Dopo averci fatto clic, potrai scegliere il valore per quella cella.

Apertura di un elenco a discesa in una cella

Applicare formule personalizzate

• range: Indica l'intervallo di celle di Excel a cui vogliamo applicare la convalida basata su formula personalizzata.
• uppermost_cell: Indica la prima cella, ossia quella più in alto, del nostro intervallo.

Solo numeri o solo testo

Le due formule seguenti servono a verificare se il contenuto di una specifica cella è un numero oppure un testo. Sono utili quando devi distinguere tra valori numerici e non numerici o, rispettivamente, tra testo e non testo.

=ISNUMBER(uppermost_cell)Offerto da

=ISTEXT(uppermost_cell)Offerto da

Solo testo maiuscolo o minuscolo

=AND(EXACT(uppermost_cell, UPPER(uppermost_cell)), ISTEXT(uppermost_cell))Offerto da

=AND(EXACT(uppermost_cell, LOWER(uppermost_cell)), ISTEXT(uppermost_cell))Offerto da

Nessun duplicato

=COUNTIF(range, uppermost_cell)<=1Offerto da

Solo valori che iniziano con una specifica sottostringa

La seguente formula verifica se i primi n caratteri di una stringa corrispondono esattamente. Restituisce un valore TRUE o FALSE.

=EXACT(LEFT(uppermost_cell, n), substring)Offerto da

Solo giorni feriali o weekend

=WEEKDAY(uppermost_cell, 2)<=5Offerto da

=WEEKDAY(uppermost_cell, 2)>5Offerto da

Solo date passate

=uppermost_cell<TODAY()Offerto da

Nessuno spazio iniziale o finale

=uppermost_cell=TRIM(uppermost_cell)Offerto da

Nessuno spazio

=uppermost_cell=SUBSTITUTE(uppermost_cell, " ", "")Offerto da

Non superare il totale

=SUM(range)<=total_cellOfferto da

Per migliorare rapidamente la tua padronanza delle formule in Excel, valuta di seguire un corso completo e pratico di Data Analysis in Excel.

Come usare la convalida dei dati in Excel

Per chiarezza, ho diviso questa sezione in due sottosezioni. La prima mostra come configurare la cartella di lavoro per incorporare le regole di convalida. La seconda spiega come applicare queste regole a celle che contengono già dei dati. Vediamo.

Come configurare una cartella di lavoro di Excel per le regole di convalida

1. Seleziona la cella o le celle in cui vuoi applicare la convalida.
2. Apri la finestra pop-up Convalida dati. Apri la scheda Dati. Vai al gruppo Strumenti dati e fai clic sul pulsante Convalida dati.
3. Nella scheda Impostazioni, nel campo Consenti, seleziona il metodo necessario, che per impostazione predefinita è Qualsiasi valore (ossia nessuna convalida).

Selezione del metodo necessario

4. Lascia selezionata la casella Ignora celle vuote se la cella o le celle in cui vuoi applicare la convalida possono rimanere vuote; altrimenti deselezionala.

Gestione delle celle vuote

5. Se hai selezionato il metodo Elenco, scegli una regola predefinita pertinente al tuo tipo di dato. Se hai selezionato il metodo Personalizzato, inserisci una formula personalizzata.

Selezione di una condizione logica per limitare intervallo/lunghezza dei dati

Inserimento dei limiti inferiore e superiore per limitare intervallo/lunghezza

Applicazione di una formula personalizzata

6. Nella scheda Messaggio di input puoi impostare un Titolo e un Messaggio di input. Per impostazione predefinita non c'è alcun messaggio.

Impostazione di un messaggio per suggerire i valori corretti

Verifica dell'aspetto del messaggio di input

7. Nella scheda Avviso di errore puoi impostare lo Stile (Arresto, Avviso o Informazioni), il Titolo e il Messaggio di errore. Per impostazione predefinita, un messaggio di errore è di stile Arresto.

Impostazione di un messaggio di errore

Dopo aver fatto clic su OK, puoi testare il funzionamento selezionando una qualsiasi cella vuota a cui si applica questa convalida e inserendo un valore non valido. Per confronto, ecco un esempio di messaggio di errore per ciascuno stile.

Messaggio di errore in stile Arresto all'inserimento di un valore non valido

Messaggio di errore in stile Avviso all'inserimento di un valore non valido

Messaggio di errore in stile Informazioni all'inserimento di un valore non valido

Il messaggio di errore predefinito all'inserimento di un valore non valido

Nota: I messaggi di errore in stile Avviso o Informazioni non impediscono l'inserimento di dati non validi, mentre quelli in stile Arresto sì.

Come usare la convalida su celle già compilate

E se alcune delle celle in cui vuoi applicare la convalida sono già compilate? La funzione Convalida dati consente sia la convalida in inserimento sia a posteriori.

1. Seleziona le celle in cui vuoi applicare la convalida. Alcune di queste celle conterranno già dei valori.

Selezione delle celle per la convalida, incluse quelle già compilate

2. Apri la finestra pop-up Convalida dati. Apri la scheda Dati. Vai al gruppo Strumenti dati e apri il pulsante Convalida dati.
3. Nella scheda Impostazioni, nel campo Consenti, seleziona il metodo necessario, che per impostazione predefinita è Qualsiasi valore.

Impostazione della convalida per le celle selezionate

3. Seleziona una qualsiasi cella tra quelle a cui hai applicato questa convalida, apri la scheda Dati, fai clic sulla freccia a discesa a destra del pulsante Convalida dati del gruppo Strumenti dati e seleziona Cerchia dati non validi.

Cerchiare i dati non validi in Excel

Dati non validi cerchiati in Excel

4. Ora puoi modificare ogni valore non valido per farlo rispettare i criteri imposti. Dopo la modifica, il cerchio attorno al valore scomparirà automaticamente. È importante ricordare che se ti limiti a eliminare un valore non valido, il cerchio rimarrà.
5. Per rimuovere i cerchi di convalida, seleziona una qualsiasi cella tra quelle a cui hai applicato questa convalida, apri la scheda Dati, vai al gruppo Strumenti dati, apri il pulsante Convalida dati e quindi seleziona Rimuovi cerchi di convalida.

Rimozione dei cerchi di convalida dai dati non validi

Come modificare la convalida dei dati in Excel

1. Seleziona una cella che ha già una convalida applicata.
2. Apri la finestra pop-up Convalida dati. Apri la scheda Dati. Vai al gruppo Strumenti dati e fai clic sul pulsante Convalida dati.
3. Apporta le modifiche necessarie in una qualsiasi scheda della finestra Convalida dati. Se necessario, rivedi le istruzioni della sezione Come usare la convalida dei dati in Excel.
4. Vai alla scheda Impostazioni e seleziona Applica queste modifiche a tutte le altre celle con le stesse impostazioni.
5. Premi OK.

Applicare le modifiche di convalida a tutte le altre celle con le stesse impostazioni

Come rimuovere la convalida dei dati in Excel

1. Seleziona una cella che ha già una convalida applicata.
2. Apri la finestra pop-up Convalida dati. Apri la scheda Dati. Vai al gruppo Strumenti dati e fai clic sul pulsante Convalida dati.
3. Vai alla scheda Impostazioni e seleziona Applica queste modifiche a tutte le altre celle con le stesse impostazioni.
4. Fai clic sul pulsante Cancella tutto.
5. Premi OK.

Rimozione della convalida dei dati

Conclusione

In questo tutorial abbiamo visto che cos'è la convalida dei dati, i suoi benefici, l'implementazione in Excel, le varie tecniche disponibili e come usare, modificare e rimuovere la convalida in Excel.

Se vuoi sbloccare tutta la potenza di Excel e imparare a preparare, visualizzare e analizzare i dati con esercizi pratici, oltre a scoprire funzionalità avanzate, prova un percorso completo e adatto ai principianti come Excel Fundamentals. Valuta anche corsi specializzati come Data Preparation in Excel o Financial Modeling in Excel, che offrono formazione mirata per approfondire le tue competenze. Queste risorse ti aiuteranno a passare dalla semplice convalida dei dati alla padronanza delle funzionalità avanzate di Excel.