La convalida dei dati in Excel

Oggi parliamo in modo approfondito di uno degli strumenti utili di Excel, ma troppo spesso trascurati e sconosciuti: la convalida dei dati. La convalida dei dati è uno strumento di controllo, o meglio uno strumento con cui realizzare e apporre controlli sui dati dei nostri fogli di lavoro. Ma non solo.

Ho aiutato un amico e ne è uscita una bella lezione sui metodi di controllo dei dati in Excel. Ne ho tratto questo post, dedicato all’utilizzo di uno strumento di Excel poco conosciuto.

Cos’è la convalida dei dati?

È uno strumento di Excel con cui è possibile creare controlli automatici, basati su regole precise che riferiscono a valori o formule, e installarli nei nostri fogli di lavoro nell’intervallo di celle che noi vogliamo.

Una volta installato il controllo, qualunque violazione al controllo, o meglio qualunque valore che proviamo a inserire nelle celle sotto controllo verrà bloccato e segnalato con un messaggio per l’utente.

Come funziona la convalida dei dati?

Lo strumento di convalida dei dati si trova nella scheda “Dati” della barra multifunzione, nel gruppo “strumenti dati”, comando “Convalida dei dati”. Cliccando sul pulsante si apre la finestra omonima con cui possiamo impostare criteri e parametri dei controlli. La finestra Convalida dati è composta da tre schede:

1. Impostazioni, attraverso cui si impostano tipo e parametri di convalida;
2. Messaggi di Input, con cui possiamo preparare un messaggio da far comparire alla selezione delle celle in cui abbiamo installato i controlli;
3. Messaggi di Errore, con cui possiamo preparare il messaggio che comparirà all’utente alla violazione del controllo.

Il primo passo per creare un controllo è impostare i criteri di convalida e il primo passo per impostare i criteri è definire la regola su cui poggia il controllo. Questo si fa scegliendo dal menu a tendina “Consenti” una tra le seguenti voci:

1. Numero intero, il controllo verificherà la presenza di numeri interi di valori compresi o non compresi tra due limiti, uguali o diversi da uno o più valori definiti, maggiori o minori di un valore preciso, e altro ancora;
2. Decimale, il controllo verificherà la presenza di numeri decimali con le stesse modalità espresse nel punto precedente;
3. Data, il controllo verificherà la presenza di date con le stesse modalità espresse nel primo punto;
4. Ora, il controllo verificherà la presenza di orari con le stesse modalità espresse nel primo punto;
5. Lunghezza testo, il controllo verificherà la presenza di testo con le stesse modalità espresse nel primo punto;
6. Elenco, il controllo verificherà la presenza di valori che corrispondano a uno dei valori di una lista predefinita;
7. Personalizzato, il controllo verificherà che il valore dia soluzione “vero” a una formula logica;
8. Qualsiasi valore, voce predefinita, quando non è ancora stato creato un controllo, è anche la scelta che elimina il controllo esistente.

Una volta selezionato il tipo di dato, compariranno nella finestra altri menu a tendina o caselle di testo con cui definire con precisione la regola. Per esempio, se vogliamo che una colonna di una tabella accetti solo valori numeri compresi tra 1000 e 9999, dobbiamo impostare prima Numero intero, poi “compreso tra”, poi inserire il valore minimo “1000” e quello massimo “9999”.

Inoltre la scheda presenta anche le voci: “Ignora celle vuote”, per consentire la presenza di celle vuote, e “Applica le modifiche a tutte le altre celle con le stesse impostazioni”, appunto per propagare le modifiche a tutte le altre celle che contengono il criterio originale di convalida dei dati.

Come si impostano i messaggi?

Lo strumento di formattazione permette di creare messaggi di errore personalizzati e anche messaggi di input. I messaggi sono opzionali, ma sono consigliati per garantire una corretta comunicazione con l’utente.

Il messaggio di errore si imposta nella scheda omonima della finestra di dialogo. La cosa importante è il testo, anzi il contenuto: sii preciso sui criteri del controllo per minimizzare ripetizioni dell’errore.

Il messaggio di errore comparirà dopo il tentativo di immissione di un valore non valido. Se il messaggio di errore non viene impostato Excel produrrà il suo messaggio standard, tipo “Questo valore non corrisponde alle limitazioni di convalida…” eccetera, ma senza spiegare quali sono le limitazioni.

È buona norma impostare anche il messaggio di input, soprattutto nel caso in cui il foglio di lavoro venga usato da altre persone. Il messaggio di input si imposta nella scheda omonima della finestra di dialogo “Convalida dati”, inserendo il testo con cui comunichi la presenza del controllo e se possibile i suoi criteri.

Per ottenere il miglior risultato, cioè minimizzare inserimenti errati, ti consiglio di:

1. impostare il messaggio di input, almeno per avvertire della presenza del controllo;
2. impostare il messaggio di errore, con chiara indicazione dei criteri;
3. inserire messaggi brevi e diretti, una frase per l’input, che ti invito a mantenere poco invasivo sulla pagina, due frasi al massimo per il messaggio di errore, ma in modo che in entrambi i casi i messaggi si leggano con un colpo d’occhio;

Infine, dove possibile, aggiungi una nota esterna alla convalida dati, direttamente sulla pagina o nella casella di testo delle istruzioni, se presente, in cui spieghi i particolari del controllo.

Come si usa la convalida dei dati?

Il modo più veloce per impostare un controllo con la convalida dei dati è il seguente:

1. seleziona la cella o l’intervallo di celle in cui vuoi installare il controllo,
2. vai alla barra multifunzione, scheda “Dati” e clicca sul comando “Convalida dati” per visualizzare la finestra omonima, che dovrebbe aprirsi sulla scheda Impostazioni,
3. seleziona l’opzione voluta dal menu a tendina “Consenti”, i controlli della finestra cambieranno a seconda della scelta,
4. specifica le condizioni impiegando i successivi controlli visualizzati, se necessario inserisci i valori limite, i riferimenti, o la formula di controllo;
5. clicca su OK, per creare il controllo e installarlo nelle celle selezionate.

Non è difficile, ma per realizzare controlli efficaci è opportuno conoscere caratteristiche e limiti dello strumento di convalida.

Cosa si può fare con la convalida dei dati?

Oltre a creare controlli che limitano l’inserimento di dati a un tipo di dato (numerico, decimale, data, ora) di valore o lunghezza limitati, come descritto in precedenza, con la convalida dei dati si può fare:

1. Creare elenchi

L’utilizzo più comune della convalida dei dati è la creazione di un elenco a discesa da cui scegliere il valore da inserire nella cella per velocizzare e facilitare l’inserimento.

Per creare un elenco in una cella si procede in questo modo:

1. prepara i valori da far comparire nell’elenco, come una lista verticale e consecutiva di celle con i valori relativi, che puoi disporre anche in altre pagine,
2. seleziona la cella o l’intervallo di celle in cui vuoi far comparire l’elenco, che sia un campo intero di una tabella di dati o un controllo sulla pagina,
3. vai sulla barra multifunzione, alla scheda “Dati” e clicca sul comando “Convalida dati” per aprire la finestra omonima,
4. nella scheda Impostazioni seleziona l’opzione “Elenco” dal menu a discesa,
5. inserisci nella casella “Origine” il riferimento all’intervallo di celle che contiene l’elenco predisposto al punto 1,
6. se necessario seleziona la casella “Elenco nella cella”,
7. clicca su OK.

La cella o le celle in cui hai installato il controllo, quando vengono selezionate mostreranno il controllo con il triangolo che identifica un elenco a discesa. Per accedere all’elenco puoi cliccare sul controllo, o premere Alt+Giù e poi ancora Giù per scorrere le voci.

L’elenco oltre che sulla pagina di Excel può essere inserito direttamente nel controllo “Origine” della finestra “Convalida dati”, separando ogni voce con il punto e virgola. Naturalmente è una soluzione consigliabile solo per elenchi brevi.

In alternativa al tipico riferimento all’intervallo di celle (es: =$P$3:$P$5), l’elenco può essere inserito come nome di un intervallo celle (es: =Clienti), cioè si assegna un nome all’intervallo di celle con la lista e si inserisce quello come riferimento nella finestra “Convalida dati”.

2. Riferirsi a valori esterni

La convalida dei dati permette di usare riferimenti non solo nel caso degli elenchi, ma sempre, quindi per esempio invece di inserire direttamente il valore massimo è possibile inserire il riferimento a una cella contenente il valore di riferimento. Questo dà la possibilità di modificare le condizioni del controllo agendo sulla cella invece che modificare il controllo ritornando alla finestra “Convalida dati”. Nella cella di riferimento possiamo inserire non solo valori, ma formule e funzioni, ampliando in questo modo la potenzialità dei controlli.

Riassumendo per la definizione delle condizioni dei controlli di dati possiamo usare:

1. valori,
2. formule che calcolano valori, o formule logiche,
3. riferimenti a celle che contengono valori o formule.

3. Usare formule

Con lo strumento di convalida possiamo realizzare controlli che hanno condizioni riferite a formule logiche, cioè formule che danno come risultato “vero” o “falso”. Se il risultato della formula è Vero, allora il controllo permette l’inserimento del valore nella cella, altrimenti visualizza il messaggio di errore.

Per creare questo tipo di controlli si procede in questo modo:

1. seleziona la cella o l’intervallo di celle in cui vuoi installare il controllo,
2. vai sulla barra multifunzione, alla scheda “Dati” e clicca sul comando “Convalida dati” per aprire la finestra omonima,
3. nella scheda Impostazioni seleziona l’opzione “Personalizzato” dal menu a discesa,
4. inserisci nella casella “Formula” la formula logica su cui si basa il controllo,
5. clicca su OK.

Un esempio classico dell’utilizzo delle formule è l’impiego di funzioni informative (es: Val.Testo(), Val.Numero(), Val.Vuoto(), ecc) indirizzate sulla cella di inserimento per controllare che vengano inseriti i tipi giusti di dati, per esempio se aggiungiamo al controllo =Val.Numero(riferimento cella controllo) darà errore se proveremo a inserire dati diversi da numeri.

Nella realtà possiamo usare formule e funzioni in tutti i campi delle condizioni della convalida dei dati. Un esempio classico sono i controlli dell’inserimento della data quando devono essere riferite al giorno dell’inserimento o a una data di un intervallo di giorni calcolato sul presente, es: minimo 30 giorni prima, massimo 3 giorni dopo oggi. In questo caso si usa la funzione OGGI() e formule tipo =OGGI()–30, =OGGI()+3.

E’ importante sottolineare che le formule possono essere inserite direttamente nei controlli della finestra “Convalida dati” come condizione, ma anche in celle sulla pagina per poi aggiungere come condizione nella finestra di convalida dati solo il riferimento di cella, o se preferisci il nome che hai assegnato alla cella.

4. Menu dinamici

Oltre a controlli sull’inserimento dei dati, gli elenchi possono essere usati per realizzare strumenti di controllo su pagina, come menù da cui selezionare valori di riferimento a cui agganciare formule, altri controlli o strumenti, e automatismi.

Per realizzarli si seguono le stesse indicazioni, ma si posizionano in maschere apposite con titoli, note ed eventuali istruzioni sul funzionamento. Per esempio si può usare un menù realizzato con la convalida dei dati per controllare la visualizzazione di una tabella excel realizzata con formule e funzioni che si riferiscono alla cella del menù e di conseguenza anche del grafico relativo.

Se vogliamo realizzare velocemente un menu dinamico, a cui per esempio possiamo aggiungere nuovi valori senza modificare il controllo, è sufficiente trasformare la lista dei valori in una tabella Excel (premi Ctrl+T) che ad ogni valore aggiunto si amplierà in automatico. Oppure possiamo assegnare un nome all’intervallo di celle con l’elenco e inserire nel controllo il riferimento al nome (es: =NOME).

Infine sfruttando le caratteristiche della convalida dei dati viste finora possiamo realizzare anche menu dinamici codipendenti, cioè un secondo menù che, a seconda del valore che abbiamo scelte nel primo menù, presenza un elenco diverso. L’esempio classico è quello dei comuni delle province, cioè a seconda della provincia scelta nel primo menu/lista, il secondo menu mostra la lista dei comuni corrispondenti.

In sintesi per farlo è necessario:

1. predisporre su pagina tutte le liste necessarie,
2. dare un nome agli intervalli di cella di ogni lista, i nomi assegnati corrispondono ai valori della lista delle province,
3. realizzare il primo menu su pagina come visto sopra,
4. poi realizzare il secondo menu inserendo come formula la funzione Indiretto() indirizzata alla cella del primo menù (es: Indiretto(b5)).

5. Messaggi sulla cella

Vale la pena citare la possibilità di usare i messaggi di input della Convalida dei dati per creare istruzioni e messaggi di supporto sulla cella. Anche se non creiamo alcun controllo, se impostiamo il messaggio di input e premiamo ok, questo comparirà ogni volta che selezioniamo la cella o l’intervallo di celle in cui lo abbiamo installato.

Questo è piuttosto utile quando dobbiamo inserire note ed istruzioni e non vogliamo metterli direttamente nella pagina o in una casella di testo. D’altra parte i messaggi di input non sono marcati come i commenti, ma compaiono solo alla selezione delle celle.

Alcuni esempi di convalida

Questi sono alcuni esempi di controlli di dati realizzati con lo strumento Convalida dati:

1. Chiavi alfanumeriche

In modo analogo è possibile usare una formula per controllare che la stringa inserita inizi con una lettera o stringa (es: =SINISTRA(riferimento cella sotto controllo, 2)=”ID”), abbia una lunghezza precisa (es: =LUNGHEZZA(riferimento cella sotto controllo)=7), oppure che contengano stringhe definite (es: =TROVA(“stringa”; riferimento cella sotto controllo)>0). E così via.

2. No duplicati

E’ possibile creare un controllo che non permetta di inserire duplicati dei valori già inseriti, usando un controllo “Personalizzato” con una formula che sfrutti funzioni “condizionate” (es: =CONTA.SE(riferimento intervallo di celle con valori preesistenti; riferimento cella sotto controllo)=1 ).

3. Solo testo

Per realizzare un controllo che costringa a inserire solo valori di testo, è necessario usare convalida dati per inserire un controllo “Personalizzato” con la formula =VAL.TESTO(riferimento cella sotto controllo).

4. Il totale dei valori inseriti non deve superare il limite

Si può usare un controllo per evitare che la somma del valore che si sta inserendo e di quelli precedenti non superi un limite definito, per esempio il numero massimo di partecipanti, il budget, 100%, eccetera (es: =SOMMA(riferimento range di celle che comprenda la cella del controllo e i valori precedenti)<=riferimento cella con il limite impostato) ).

Se ti interessano altri esempi di controlli avanzati realizzati con la Convalida dei dati ti invito a leggere il post “20 Controlli avanzati con la convalida dei dati”.

Come si aggira la convalida dei dati

Si possono aggirare i controlli della convalida dei dati?

No, se hai impostato il controllo in modo preciso e questo si riferisce a valori definiti, allora l’unico modo per aggirare un controllo è rimuoverlo. Se al contrario il controllo è mal definito, o si basa su formule e funzioni, allora il controllo assumerà limiti e lacune delle formule/funzioni, per esempio la possibilità che queste generino errori (vedi guida agli errori di Excel).

In generale consiglio sempre di testare i controlli in modo adeguato prima di distribuire il foglio di lavoro ad altri.

E’ possibile inserire comunque un valore non accettato dal controllo creato dalla convalida dei dati?

Sì, lo strumento permette di modificare il comportamento del controllo, permettendo anche l’inserimento di valori non validi. Puoi farlo modificando lo “Stile” nella scheda “Messaggio di errore” della finestra di convalida.

Oltre all’interruzione che è il valore di default c’è la possibilità di impostare la finestra di errore con lo stile “Avviso” che permette comunque l’inserimento del valore con un pulsante “Ok” e lo stile “Informazione” che si limita solo a informare che il valore non è accettato dal controllo.

Naturalmente il mio consiglio è mantenere i controlli in modalità interruzione per evitare di inserire valori non accettabili. Questo naturalmente ha le dovute eccezioni: per esempio con gli elenchi predefiniti potrebbe essere utile permettere l’inserimento di nuovi valori, se l’elenco di riferimento è reso dinamico, per esempio con formule e funzioni, in modo da aggiornarsi autonomamente con nuovi valori.

Controlli su valori già impostati

I controlli creati e installati dalla “Convalida dati” si attivano all’inserimento di nuovi valori nelle celle o alla modifica di valori preesistenti, ma li possiamo installare in celle contenenti già dei valori, anche valori non validi secondo il controllo che abbiamo creato.

In questo caso Excel offre uno strumento accessorio, “Cerchia dati” che ci permette di individuare la presenza di valori non validi preesistenti. Lo puoi trovare nella barra multifunzione, scheda “Dati”, come voce del menu sotto al pulsante “Convalida dati”.

Il comando evidenzia con un cerchio rosso i dati non validi secondo i controlli impostati. Il cerchio scompare alla correzione del dato, oppure disattivando lo strumento con il comando “Rimuovi i cerchi” presente nello stesso menù di “Convalida dati”.

Come si rimuovono i controlli?

I controlli creati con la convalida dei dati si rimuovono in questo modo:

1. seleziona la cella o l’intervallo con i controlli che vuoi eliminare,
2. clicca sul comando “Convalida dati” per aprire la finestra omonima,
3. clicca sul pulsante “Cancella tutto” per rimuovere ogni controllo.

Un’alternativa è quella di copiare una cella senza convalida, selezionare le celle da ripulire e premere Ctrl+Alt+V per richiamare la finestra Incolla speciale, in cui selezionare la voce “Convalida” e infine premere ok.

Come si dovrebbero creare i controlli

Il modo più efficace di usare lo strumento di convalida, parte da un’attenta riflessione preliminare prima di iniziare a posizionare controlli sulla pagina.

1. Il primo passo è definire con precisione qual è il nostro obiettivo: perché vogliamo mettere un controllo sulla pagina? Per ridurre gli errori di inserimento? Individuare errori preesistenti nei dati? Per creare controlli per altri scopi? Chi lo userà? Quando e come verrà usato? È veramente necessaria o utile?
2. Il passo successivo è definire il controllo sulla carta, valutare e definire con precisione:
3. dove posizionarlo, in quali pagine e celle,
4. il tipo di controllo, verificare il tipo di dato, il valore del dato, la dimensione del dato, la struttura del dato, la coerenza, l’univocità, eccetera,
5. le condizioni del controllo, tramite valore diretto, lista di valori, formula diretta, valore su pagina, formula su pagina, funzioni condizionate, eccetera.
6. Il terzo passo è creare il controllo, o un prototipo del controllo se è molto complesso. Prima predisporre sulla pagina gli elementi necessari, es: liste di valori, nomi di intervalli, o formule di riferimento, e poi impostare il controllo attraverso lo strumento di Convalida dati.
7. Una volta realizzato è necessario testare il controllo provando a inserire valori non validi. Soprattutto in presenza di controlli complessi con formule e funzioni logiche che sappiamo essere soggette a condizioni di errore, dobbiamo verificare che funzionino come abbiamo progettato.
8. Se il controllo non funziona come previsto, dobbiamo revisionarlo e testarlo nuovamente. Altrimenti se necessario dobbiamo installarlo dove abbiamo previsto, es: pagina dati, dashboard, eccetera.

Conclusioni

Lo strumento “Convalida dati” è un vecchio, ma potente strumento di creazione di controlli dei dati sulla pagina di Excel. Possiamo realizzare controlli per la riduzione degli errori di inserimento sulla pagina, ma possiamo anche creare controlli avanzati e dinamici che verificano dati preesistenti o loro elaborazioni, possiamo creare menu dinamici, strumenti sulla pagina con cui controllare tabelle e grafici, messaggi sulla cella e altro ancora.

Ti invito a imparare l’uso di questo strumento che può rivelarsi comodo, efficiente e utile in molte situazioni, talvolta anche in situazioni aldilà dei limiti di strumenti più avanzati.

PS: Se questo post ti è piaciuto o ti è stato utile, condividilo con gli amici sui social.
Puoi farlo attraverso i pulsanti qui di seguito. Grazie.