Lezione-online fornisce corsi di formazione e-learning a più di 60mila studenti.

Fb. Ig. In. Yt.

Excel: la convalida dei dati

Il foglio di Excel da sempre ti permette di inserire all’interno di ogni cella qualsiasi dato: puoi scrivere un testo oppure un numero senza alcun tipo di limitazione.

Non esistono caratteri proibiti anzi, nella scheda Inserisci trovi il pulsante Simbolo che ti permette di aggiungere anche caratteri speciali.

Ovviamente questa caratteristica ha lati positivi e negativi. Quando compili una tabella infatti puoi nelle diverse colonne scrivere quello che vuoi.

Puoi creare una tabella “Data emissione” e compilarla inserendo non delle date ma dei testi. Oppure puoi iniziare a digitare delle date e poi proseguire inserendo testi o numeri, senza che il programma ci mostri messaggi di avviso. Oppure ancora in una colonna denominata “Comune Residenza” puoi scrivere lo stesso dato in mille modi diversi.

È chiaro che se il file di Excel sul quale stai lavorando è un semplice elenco che devi solamente consultare ignorando questo genere di problema. Ma se devi eseguire operazioni di elaborazione come ordinamenti, filtri, calcoli, o tabelle pivot ti troverai di fronte ad uno scoglio apparentemente insormontabile: la disomogeneità dei dati.

COME PUOI AFFRONTARE QUESTO PROBLEMA?

L’attenzione alla digitazione dei dati e l’utilizzo dei suggerimenti di compilazione purtroppo non bastano. Potrai sempre digitare “480à” invece che 450, inserendo quindi un testo al posto di un numero.

L’unico strumento che Excel ti mette a disposizione per affrontare efficacemente questo problema è la Convalida dati. Si tratta di un pulsante presente all’interno del gruppo Strumenti dati della scheda Dati. Un volta cliccato apre la finestra che puoi vedere nell’immagine.

Le tre linguette a disposizione indicano che cosa puoi fare: Impostazioni, Messaggio di input e Messaggio di errore.

Tabella Excel con esempio di finestra della Convalida dati.
La finestra di Convalida dati.

Le regolazioni si effettuano da Impostazioni aprendo il menu a tendina di Consenti. Se l’opzione di default è Qualsiasi valore, le alternative sono tutte molto interessanti: Numero intero, Decimale, Elenco, Data, Ora, Lunghezza testo e Personalizzato (vedi immagine seguente).

Tabella Excel con esempio del menù di Consenti.
Il menù di Consenti.

Vediamole nel dettaglio:

  • Numero intero: obbliga ad inserire nella cella un numero senza decimali;
  • Decimale: identico al precedente, con la possibilità di inserire sia cifre decimali che numeri interi;
  • Elenco: permette di inserire i dati prendendoli da un elenco creato e definito da noi, composto da un gruppo di celle già compilate;
  • Data: costringe ad inserire nella cella una data;
  • Ora: fa inserire nella cella un orario;
  • Lunghezza testo: consente di inserire dei testi che possono avere una lunghezza minima, massima o entrambe. Anche gli spazi, i numeri ed i caratteri speciali vengono conteggiati;
  • Personalizzato: permette di creare dei criteri completamente personalizzati, basati sull’utilizzo di formule.

Quando scegli, ad esempio, criteri come Decimale o Numero intero scoprirai che in realtà la Convalida dati ti dà un’altra possibilità: definire dei limiti minimi e massimi.

Se devi inserire un prezzo ad esempio, l’importo non potrà mai essere inferiore o uguale a zero. Scegli quindi il criterio Maggiore di e nel campo Valore minimo digita 0, come pupi vedere nell’immagine successiva.

Tabella Excel con esempio di definizione di un criterio minimo.
La definizione di un criterio minimo.

EXCEL: QUALI SONO I PALETTI CHE PUOI SFRUTTARE?

Tra, Non compreso tra, Uguale a, Diverso da, Maggiore di, Maggiore o uguale a, Minore di, Minore o uguale a. Onestamente di più non potrai chiedere.

Gli stessi identici criteri si possono applicare anche alle convalide di Data e Ora. Se vuoi creare una regola che ti permetta di inserire nelle celle solamente date del 2020, andrai a usare il criterio Tra e inserirai come data di inizio 01/01/2020 e come Data di fine 31/12/2020 (vedi immagine).

Tabella Excel con esempio di creazione di una Convalida dati per inserire solo date del 2020.
Creare una Convalida dati per inserire solo date del 2020.

Quando usi la Convalida dati Elenco fai apparire nella cella il menu a tendina dal quale scegliere il dato corretto da inserire.

MA COME?

Basta semplicemente inserire nel campo Origine il riferimento alle celle che contengono i dati corretti che si possono inserire in una data cella, come illustrato nella prossima immagine.

Tabella Excel con esempio di Convalida dati Elenco.
La Convalida dati Elenco.

Dovrai quindi compilare prima delle celle, ad esempio con i nominativi dei clienti, e poi inserirli con una semplice selezione per generare l’origine della Convalida dati Elenco. Queste celle si possono trovare nello stesso foglio che vuoi compilare, oppure in un altro. Non è però possibile che si trovino in un altro file di Excel.

Fin qui hai esplorato le possibilità che ti mettono a disposizione le opzioni tradizionali. Se non hai trovato quello che cercavi allora dovrai avvalerti dell’opzione Personalizzato. Qui sostanzialmente puoi creare qualsiasi tipo di Convalida dati, sfruttando le formule di Excel.

FACCIAMO UN ESEMPIO:

Vuoi inserire solamente date dell’anno in corso. Selezioni il gruppo di celle che devono avere la regola di Convalida dati. Ipotizziamo che si tratti dell’intervallo C2:C50. Digiterai nel campo Formula della Convalida dati Personalizzato =ANNO(C2)=ANNO(OGGI()), come puoi vedere nell’immagine seguente.

Grafico Excel con esempio di convalida Dati basata su una formula.
Una convalida Dati basata su una formula.

COSA SIGNIFICA?

L’anno della data scritta nella cella C2 deve essere uguale all’anno della data odierna. Se nella cella C2 inserisci una data di un anno passato oppure un testo, la funzione ANNO fa sì che la Convalida Dati non ammetta l’immissione del dato.

Quando si passa dal 2020 al 2021 automaticamente vengono accettate solo date del 2021, e così via. La formula viene poi applicata automaticamente alle altre celle dell’intervallo selezionato aggiornando il riferimento.

Prima di concludere ti suggerisco qualche altra formula da sfruttare nella convalida dati, sempre in riferimento alla cella C2:

  • =GIORNO.SETTIMANA(C2;2)<6, serve per evitare di inserire date che cadono di sabato o domenica;
  • =CONTE.SE(C:C;C2);=1, evita che un dato possa essere inserito due volte nella colonna C. Impedisce quindi l’inserimento di dati doppi;
  • =C2>OGGI(), nella cella C2 si deve obbligatoriamente inserire una data futura;
  • VAL.TESTO(C2), impedisce di digitare qualcosa di diverso da un testo.

Tutte le formule che si inseriscono nel campo Formula vanno tassativamente digitate a mano.

DUE ULTIME ANNOTAZIONI SU EXCEL

Il cavaliere Messaggio di errore permette di personalizzare la finestra di errore che appare quando non rispettiamo la Convalida dati impostata.

Puoi scrivere quello che vuoi ma ti sconsiglio di modificare lo Stile: Interruzione (vedi la prossima immagine). Le alternative Avviso e Informazione consentono comunque di inserire un dato non corrispondente alla regola di validazione impostata.

Grafico Excel con esempio di personalizzazione del Messaggio di errore.
La personalizzazione del Messaggio di errore.

Il Messaggio di input, personalizzabile, genera invece una finestra in prossimità della cella nella quale puoi leggere i suggerimenti che hai impostato.

Per cancellare una Convalida dati sbagliata o inutile è sufficiente selezionare le celle, cliccare il pulsante Convalida dati e scegliere Consenti: Qualsiasi valore.

Commenti:
Condividi su:

Laureatosi nel 2003 in Scienze Politiche presso l'Università degli Studi di Milano, si occupa di insegnare materie informatiche dal 2004. La sua filosofia professionale lo spinge sempre ad ascoltare i reali bisogni degli allievi, per capire ciò che veramente vogliono imparare. Collabora tuttora con importanti società.

You don't have permission to register