Guida alla Convalida Dati

Questa guida vuole introdurre e svelare uno degli strumenti utili di Excel troppo spesso trascurato: la convalida dei dati.

Cos’è la convalida dati?

La convalida dati è uno strumento di controllo, o meglio uno strumento con cui realizzare e installare controlli automatici sull’inserimento dei dati nei nostri fogli di lavoro. E non solo. I controlli automatici creati con la convalida dati si basano sull’impostazione di criteri che definiscono con precisione quando il valore che si vuole inserire è valido o meno. Questi controlli possono essere equipaggiati con finestre personalizzate e possono essere configurati e installati con facilità su singole celle o intervalli.

Una volta installato il controllo, qualunque violazione, o meglio qualunque valore che proviamo inserire nelle celle sotto controllo verrà bloccato e segnalato con un messaggio per l’utente. Ma anche questo comportamento dello strumento può essere personalizzato come vedremo più avanti.

Oltre a creare il controllo automatico, la convalida dati permette di definire e personalizzare:

1. i messaggi di input, restituendo alla selezione della cella in cui è installato lo strumento una finestra apposita con cui comunicare istruzioni e regole, ma che possiamo usare anche in assenza dei controlli per scopi informativi diversi;
2. i messaggio di errore, le finestre che compaiono quando si cerca di inserire valori non validi e che offrono informazioni e indicazioni su come rispettare il controllo.

Come funziona la convalida dati?

Lo strumento di convalida 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 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.

Quali controlli possiamo installare?

Possiamo installare i controlli di base preimpostati attraverso selezionando dal menu a tendina “Consenti” della scheda “Impostazioni”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; questa opzione permette di installare buona parte dei controlli avanzati;
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 maggior precisione la regola che stiamo creando. 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”. In modo analogo Nello stesso modo possiamo impostare una regola che permetta l’inserimento di un numero maggiore di un valore preciso, oppure una data compresa tra due date definite, o un valore lungo meno di 8 caratteri. E così via.

Come si impostano i messaggi?

Come abbiamo anticipato, 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 e quindi un utilizzo corretto degli strumenti.

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 le 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, del tipo “Questo valore non corrisponde alle limitazioni di convalida…” eccetera, ma senza spiegare quali sono le limitazioni.

Attraverso questa scheda è possibile anche modificare il comportamento del controllo stesso, la sua tassatività, permettendo anche l’inserimento di valori non validi. Per farlo devi modificare l’impostazione del menu “Stile”:

1. Interruzione, è l’impostazione di default che blocca l’inserimento non valido comunicandolo all’operatore,
2. Avviso, permette l’inserimento dopo conferma tramite pulsante aggiuntivo della finestra di errore,
3. Informazione, invece inserisce il valore e dà solo comunicazione dell’invalidità tramite finestra di errore.

è consigliabile usare i controlli per bloccare gli inserimenti,

È consigliabile impostare anche il messaggio di input, soprattutto nel caso in cui il foglio di lavoro venga usato da altre persone. Il messaggi si imposta nella scheda “Messaggio di Input”, inserendo il testo con cui fornire istruzioni sull’utilizzo dei controlli e i relativi criteri.

Per minimizzare gli 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, soprattutto per l’input, che ti invito a mantenere breve e poco invasivo sulla pagina; due frasi al massimo per il messaggio di errore; l’ideale sarebbe 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, in cui spieghi i particolari del controllo.

Come si usa la convalida 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.

Cosa si può fare con la convalida dati?

Oltre a creare controlli che limitano l’inserimento di dati, come descritto in precedenza, con la convalida dei dati si può fare:

1. Creare elenchi

Uno degli utilizzi più comuni è la creazione di un elenco a discesa da cui scegliere il valore da inserire nella cella, uno strumento per 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 che contiene la lista e si inserisce quello come riferimento nella finestra “Convalida dati” (ed: =NomeIntervallo).

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 per creare controlli avanzati

Con lo strumento di convalida possiamo realizzare controlli avanzati che hanno criteri basati su 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, ma invece di restituire vero o falso, queste dovranno restituire i valori necessari per definire i limiti dei criteri. Un esempio classico sono i controlli dell’inserimento della data quando devono essere riferite al giorno dell’inserimento o a 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 come queste: =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 menu da cui selezionare valori di riferimento a cui agganciare formule, altri controlli, strumenti o 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.

Come si aggira la convalida 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ì, come già detto, 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.

Controlli avanzati con la convalida dati

Se la convalida si fermasse ai controlli preimpostati, quelli elementari, sarebbe uno strumento limitato poco utile. Invece come abbiamo già detto la convalida accetta l’inserimento di:

1. formule e funzioni nei campi della definizione delle regole al posto dei valori,
2. riferimenti a celle delle pagine di Excel, che possono contenere valori, o formule e funzioni,
3. riferimenti a nomi definiti in Excel, da noi o da strumenti avanzati, che possono indicare valori, intervalli di celle o elenchi.

Questo permette di usare strumenti potenti e rodati come funzioni e gestione dei nomi per realizzare controlli più evoluti. Elenco qui di seguito una serie di controlli più o meno avanzati realizzati con la convalida dei dati e gli altri strumenti di Excel:

1. Inserire un testo

Se invece di imporre un controllo che permetta l’inserimento di numeri o date, volessimo limitare l’inserimento a stringhe di testo? Si può realizzare usando la funzione VAL.TESTO() impostata direttamente nel campo formula dell’opzione “Personalizzato” del menu “Consenti” della scheda “impostazioni”.

È sufficiente aggiungere: =VAL.TESTO(CellaInserimconControllo)

La funzione verifica appunto se il valore è una stringa di testo e in caso positivo restituisce un valore Vero. CellaInserimconControllo è l’indicazione della cella in cui vuoi installare il controllo (es: A4) che puoi scrivere direttamente, o selezionare con il puntatore del mouse, o indicare scrivendo un nome impostato in precedenza con la gestione dei nomi o da strumenti come tabelle Excel.

Se non conosci la gestione dei nomi o vuoi approfondire l’argomento ti invito a leggere la guida relativa.

Controlli analoghi possono essere realizzati usando le altre funzioni “informative”, come VAL.NUMERO(), VAL.ERRORE(), VAL.VUOTO(), eccetera che verificano la tipologia di valore e restituiscono valore vero o falso a seconda della corrispondenza.

2. Controlli sui testi

È possibile controllare che un testo inizi o finisca con un certo carattere o stringa. Per esempio se inserisci la formula =SINISTRA( CellaInserimconControllo; 7) = “Stringa”, puoi imporre l’inserimento di valori che iniziano con “Stringa”. Sinistra() è la funzione che estrae una porzione di stringa a partire da sinistra.

In modo analogo usando le altre funzioni di Excel per la gestione dei testi, come SINISTRA(), DESTRA(), LUNGHEZZA(), STRINGA.ESTRAI(), TROVA() è possibile impostare controlli che verifichino porzioni del testo, perfino i singoli caratteri, o la presenza di stringhe specifiche, non solo all’inizio o alla fine, ma in qualunque posizione.

Per esempio, con =SINISTRA(DESTRA(CellaInserimconControllo); 3); 1)= “F” possiamo verificare se il terzultimo carattere del testo è “F”. Le possibilità sono molteplici e permettono di controllare codici aziendali alfanumerici anche carattere per carattere, se necessario.

3. Controlli multipli

Possiamo inserire come formula del controllo “Personalizzato” espressioni anche molto grandi e complesse che devono restituire un valore vero e falso. Se l’espressione è vera il controllo riconoscerà valido il valore che si vuole inserire, altrimenti bloccherà l’inserimento.

Possiamo inserire controlli multipli usando formule, grazie alle funzioni logiche E(), O(), NON(), eccetera, che permettono di valutare e connettere più espressioni. Per esempio se proviamo a inserire la formula: =O(CellaInserimconControllo =”SI”; CellaInserimconControllo =”NO”), il controllo risultante permetterà solo l’inserimento di SI o NO.

La più utilizzata di queste funzioni logiche è in realtà E(), usata appunto per impostare controlli multipli nella stessa espressione da inserire come formula nel controllo “Personalizzato”. La sintassi è: =E( Espressione1 ; Espression2 ; …. ; EspressionN ), restituirà un valore vero se tutte le espressioni sono a loro volta vere.

Con espressione intendo una formula con funzione che restituisce di base un valore logico vero o falso, o una comparazione tra formule o formule e valori, che può essere vera o falsa. Se vuoi far restituire a una formula vero o falso comparala con qualcosa (=, <, >, ecc), oppure inseriscila come argomento in funzioni che restituiscono vero o falso (es: val.errore()) .

4. Il valore deve contenere un testo preciso

Talvolta è opportuno installare un controllo che vada a cercare la presenza di singoli caratteri o stringhe all’interno di un testo o un alfanumerico, come per esempio la @ e un punto (.) per verificare che il valore inserito sia probabilmente un indirizzo email.

Per farlo possiamo utilizzare le funzioni TROVA() e VAL.NUMERO(), la prima individua il carattere o la stringa e il secondo trasforma il numero restituito in vero o falso. Per esempio =VAL.NUMERO(TROVA(“@”; CellaInserimconControllo)) darà risultato vero, quindi accetterà inserimenti di testi che contengono la @.

Spesso questo controllo rimanda a una cella esterna per il carattere o la stringa da cercare, In questo modo =VAL.NUMERO( TROVA(CellaconValore;CellaInserimconControllo)). Naturalmente la formula va inserita sempre nel campo “Formula” del controllo “Personalizzato” del menu “Consenti” della scheda “impostazioni”.

5. Non deve contenere un testo preciso

Se invece vogliamo che il nostro testo non contenga un carattere o una stringa specifica, allora possiamo utilizzare la stessa coppia di formule, ma insieme alla funzione NON(), funziona logica che inverte il risultato vero e falso.

Per esempio =NON(VAL.NUMERO(TROVA(“@”; CellaInserimconControllo))) non permette di inserire testi che contengono il carattere @. Questo può essere utile in molti modi, anche per escludere l’utilizzo di punteggiatura e di caratteri speciali.

Naturalmente per bloccare l’impiego di più stringhe o caratteri dobbiamo replicare la formula usando la funzione E(). Per esempio =E(NON(VAL.NUMERO(TROVA(“@”; CellaInserimconControllo))); NON(VAL.NUMERO(TROVA(“.”; CellaInserimconControllo)))) esclude sia punti che @ dal testo inserito.

Questo sistema funziona per alcuni caratteri o stringhe, ma se abbiamo liste intere di “esclusioni” allora è opportuno passare a strumenti diversi.

6. Deve o non deve contenere uno dei valori della lista

Quando abbiamo una “lista di esclusione” o viceversa una “lista di inclusione” le soluzioni più comode sono due: la funzione MATR.SOMMA.PRODOTTO() oppure le formule matriciali.

La prima soluzione, la funzione MATR.SOMMA.PRODOTTO() è concepita per restituire somme di prodotti numerici in matrici o intervalli di dati, ma in generale è uno strumento utile in situazioni in cui abbiamo liste e matrici di numeri.

Per esempio, possiamo usarla con le funzioni TROVA() e VAL.NUMERO() per elaborare i dati numeri restituiti da TROVA() se appunto trova uno o più stringhe della lista indicata. In questo modo: =MATR.SOMMA.PRODOTTO( –VAL.NUMERO( TROVA( listacaratteridaescludere; CellaInserimconControllo )))=0 generiamo un controllo che esclude la presenza di caratteri e stringhe nella lista indicata (“listacaratteridaescludere”).

Se viene trovato almeno uno degli elementi della lista nel testo che stiamo inserendo, allora il risultato di MATR.SOMMA.PRODOTTO() sarà diverso da 0. Come puoi vedere abbiamo usato una comparazione per restituire un valore vero o falso.

Possiamo usare lo stesso metodo per includere uno o più caratteri di una lista, in questo modo: =MATR.SOMMA.PRODOTTO( – – VAL.NUMERO( TROVA( listacaratteridaescludere; CellaInserimconControllo )))<>0. È importante sottolineare che questo controllo garantisce la presenza di almeno uno dei caratteri, non di tutti, neppure di un numero preciso.

La lista dei valori o caratteri da escludere o includere deve essere inserita nel foglio, magari in una pagina a parte e nominata in modo chiaro per facilitare l’inserimento, oppure indicata con un riferimento classico (es: A5:A25).

7. Esclude i caratteri della lista

La soluzione più efficace per escludere liste di caratteri, stringhe o valori sono le formule matriciali, cioè formule che eseguono calcoli su più elementi di una matrice contemporaneamente per restituire uno o più valori contemporaneamente. Non spiegherò qui le formule matriciali, ti rimando ai post dedicati, ma soprattutto perché quando le inserisci nei controlli non è necessario farlo con Ctrl+Maius+Invio in quanto la convalida gestisce autonomamente la matrice.

Per esempio questa formula: =CONTA.NUMERI( TROVA( listacaratteridaescludere; CellaInserimconControllo))=0, utilizza le funzioni CONTA.NUMERI() e TROVA() per cercare i caratteri o le stringhe della lista e contare quante volte vengono individuati. La comparazione a 0 fa restituire vero o falso e quindi imposta il controllo per escludere gli elementi della lista.

Come sempre devi inserirlo nel campo “Formula” del controllo “Personalizzato” del menu “Consenti” della scheda “impostazioni”. Se invece vuoi installare la formula in una cella della pagina e inserire nel campo “Formula” del controllo solo il riferimento o il nome che hai assegnato, dovrai ricordarti di farlo con Ctrl+Maius+Invio o non funzionerà correttamente.

Un esempio classico di controllo con Conta.Numeri() e Trova() è questo: =E(VAL.TESTO(CellaInserimconControllo); CONTA.NUMERI(TROVA(listacaratterispecialidaescludere; CellaInserimconControllo))=0), che verifica che l’inserimento sia un testo esente da caratteri presenti in una lista di esclusione.

Naturalmente lo stesso metodo può essere utilizzato per includere uno o più valori della lista, in questo modo, per esempio: =CONTA.NUMERI( TROVA( listacaratteridaescludere;  CellaInserimconControllo))>0.

8. Testo senza caratteri numerici

I controlli con liste di esclusione/inclusione possono essere usati in molti modi diversi, con limiti che dipendono più dalla nostra fantasia che dallo strumento. Per esempio la formula che segue imposta un controllo che verifica che il valore che stiamo inserendo è testo e allo stesso tempo non contenga caratteri numerici.

In questo modo: =E( VAL.TESTO( CellaInserimconControllo); CONTA.NUMERI( TROVA( numerida0a9; CellaInserimconControllo))=0). La prima espressione verifica se il valore è un testo, la seconda verifica se sono presenti caratteri numerici. Questi sono stati posizionati su pagina in una lista in un intervallo di celle, poi nominato “numerida0a9”.

Se la formula non viene inserita nel campo “Formula” del controllo “Personalizzato” del menu “Consenti” della scheda “impostazioni”, ma su pagina, allora è necessario ricordare di inserirla come una formula matriciale con Ctrl+Maius+Invio.

9. Controlli multipli complessi

Possiamo usare le formule viste finora con la funzione E() per creare controlli multipli complessi, come per esempio =E( SINISTRA( CellaInserimconControllo;2)=”AC”; LUNGHEZZA( CellaInserimconControllo)>=9; CONTA.SE( numerida0a9; DESTRA(CellaInserimconControllo;1))=1); VAL.NUMERO(TROVA(“ST”; CellaInserimconControllo)). Questa espressione determina un controllo che accetta inserimenti di valori che iniziano con “AC”, lunghi almeno 9 caratteri, che finiscono con un numero e contengono la stringa “ST”. Il nome “numerida0a9” è riferito a un intervallo di dieci celle che contengono le singole cifre numeriche di base.

Oppure per esempio, =E( VAL.TESTO( CellaInserimconControllo); LUNGHEZZA(CellaInserimconControllo)>9; VAL.NUMERO( TROVA(“@”;CellaInserimconControllo)); VAL.NUMERO( TROVA(“.”;CellaInserimconControllo)); O( DESTRA( CellaInserimconControllo; 2)=”it”; DESTRA( CellaInserimconControllo; 3)=”com”)). Questa espressione genera un controllo che permette l’inserimento di una stringa di testo di lunghezza maggiore di 9 caratteri, che contiene @ e punto e che termina con “it” o “com”.

Questi vogliono essere esempi di quanto i controlli creati con formule e funzioni possono essere precisi e complessi nella definizione di un controllo multiplo che garantisca il corretto inserimento da parte dell’operatore.

10. Controlli particolari sulle date

Nella maggior parte dei casi i controlli su date sono soddisfatti dalle opzioni di base, che prevengono prevalentemente errori di distrazione, rese dinamiche da funzioni relative alle date come, per esempio, OGGI().

Talvolta è però utile andare oltre, per esempio limitando l’immissione delle date ai giorni feriali, come in questo modo: =GIORNO.SETTIMANA(CellaInserimconControllo;2)<6. Non è l’unico modo, ma forse è il più semplice grazie alla funzione GIORNO.SETTIMANA().

Oppure a controlli che si appoggiano anche ad altre celle di inserimento o altri valori, per esempio in questo modo: =O( E( DESTRA(CellaDiversa;3)=”STD”; CellaInserimconControllo>     OGGI+40; CellaInserimconControllo< OGGI+60); E( DESTRA(CellaDiversa;3)=”TP2”; CellaInserimconControllo> OGGI+20; CellaInserimconControllo< OGGI+40) ).

Questa espressione imposta un doppio controllo multiplo basato sul valore di una cella differente da quella del controllo, di solito un altro campo del record che si sta inserendo, andando di conseguenza ad accettare intervalli di dati diversi.

11. Controlli che si appoggiano ai dati già inseriti

L’esempio precedente dimostra tra l’altro che i controlli si possono appoggiare senza difficoltà anche su altri valori oltre a quello oggetto del controllo, come altri valori del record che si sta inserendo, valori dello stesso campo inseriti in precedenza o altri valori predisposti specificatamente (costanti, valori di configurazione, valori elaborati dalla base dati, eccetera) e di solito inseriti sulla pagina o in pagina apposita a nominati per richiamarli in modo semplice.

Per esempio, =SOMMA(Tabella[Giacenza])+ CellaInserimconControllo <=Limite imposta un controllo che valuta i dati precedenti inseriti fissando un limite (es: 100mila pezzi, capacità massima di un magazzino). Questo limite può essere inserito direttamente nella formula o inserito a parte sulla pagina, come costante o valore calcolato con formule e funzioni.

Oppure = CellaInserimconControllo >=MAX(Tabella [DataInserim]) imposta un controllo che verifica che la data che stiamo inserendo sia sempre maggior o uguale dell’ultima data inserita.

O per esempio, =E( CellaInserimconControllo>= MEDIA(Tabella[MisCampione])– 2*DEV.STD.P(Tabella[Misura]); CellaInserimconControllo<= MEDIA(Tabella[MisCampione])+ 2*DEV.STD.P(Tabella[Misura])) genera un controllo che verifica se il valore che stiamo inserendo è entro l’intervallo di + o – 2 volte la deviazione standard.

I controlli di questo tipo che si appoggiano alle funzioni di elaborazione di dati numerici e non, possono diventare molto complessi e precisi. Per esempio puoi andare a predisporre controlli che si appoggiano su porzioni dei dati di uno o più campi della base dati, valutati tramite funzioni condizionali (es: Somma.Più.SE()), funzioni di database o ricerca, o funzioni matriciali.

12. Solo valori univoci

Un classico controllo che si appoggia sui dati già inseriti, che prima o poi tutti utilizziamo, è quello che verifica che il valore sia univoco, cioè che non sia già presente tra i valori del campo specifico di tutti i record della tabella.

Per esempio: =CONTA.SE( Tabella[COD]; CellaInserimconControllo)=0 controlla che il valore che vuoi inserire nella cella (CellaInserimconControllo) non sia già presente nel campo “COD” della tabella Excel di nome “Tabella”.

La funzione CONTA.SE() conta i valori e restituisce un numero, ma comparando il risultato con 0 verifichiamo se il valore indicato è presente o meno. Anche in questo caso la formula va inserita nel campo “Formula” del controllo “Personalizzato” del menu “Consenti” della scheda “impostazioni”.

Un esempio di controllo multiplo che richiede l’univocità è questo: =E( VAL.NUMERO(CellaInserimconControllo); CONTA.SE( Tabella[Indice]; CellaInserimconControllo)=0) che verifica che il valore inserito sia un numero e sia unico in relazione ai valori precedenti inseriti.

Conta.Se() potrebbe essere utilizzato anche per limitare l’inserimento a una lista precisa di valori o stringhe, in questo modo =CONTA.SE(ListaValori;B4)>0, ma non fa altro che ricalcare l’opzione “Elenco” senza offrire la lista delle alternative delle cella.

13. Deve essere inserito un valore

Per costringere a inserire un valore, con qualunque controllo abbiamo impostato, è sufficiente togliere la spunta alla voce “ignora celle vuote” della scheda “impostazioni” della finestra “Convalida dati”. In modo analogo possiamo inserire in ogni controllo la necessità di inserire un valore con questa formula: =NON(VAL.VUOTO(CellaInserimconControllo)).

La cito, ma in entrambi i casi questa possibilità non serve a molto, soprattutto perché non viene percepita dall’operatore se non quando entra in modalità inserimento (digitando o premendo F2) e non cerca di uscirne con invio.

Di solito se un operatore non vuole inserire nulla in un campo non lo seleziona, né lo attiva, e se vuole uscirne senza inserire nulla di solito utilizza Esc, che non permette l’attivazione del controllo. Quindi se hai bisogno di far inserire tassativamente un valore, o utilizzi una macro in Vba, o aggiungi note che evidenziano la tua necessità.

14. Elenchi dinamici

Gli elenchi della convalida sono utilizzati non solo per facilitare l’inserimento di valori, ma anche per creare facilmente controlli su pagina, senza dover utilizzare i più performanti ma complicati controlli ActiveX (caselle di riepilogo e caselle combinate), per esempio per controllare dashboard, report o altro ancora.

Una volta che iniziamo a usare i controlli anche in questo modo, in breve tempo sentiamo la necessità di rendere gli elenchi dinamici. Ma “dinamici” assume significati diversi a seconda della situazione.

Per esempio dinamici può significare:

posso aggiungere elementi alla lista senza dover reimpostare il controllo?

Come abbiamo già detto, si può fare facilmente usando nel controllo riferimenti o nomi relativi a intervalli più ampi, o meglio ancora trasformando l’elenco in tabella Excel (Ctrl+T), uno strumento dinamico che si adatta agli inserimenti successivi. Di conseguenza quando andrai ad aggiungere un altro valore la tabella lo integrerà e se utilizzerai la notazione delle tabelle Excel (NomeTabella[NomeCampo]) questa indicherà la lista via via sempre aggiornata. Non è l’unico modo, ma è quello più comodo e semplice.

Naturalmente questo richiede di aggiungere manualmente i valori nuovi alla lista prima dell’inserimento.

Posso aggiungere in automatico i valori alla lista, via via che li inserisco nella tabella o nella maschera di inserimento?

Certo, si può fare utilizzando le funzioni SCARTO() e CONTA.VALORI(), la gestione dei nomi di Excel e le tabelle Excel, riferendoci alla tabella dei dati invece che a un elenco a parte, e modificando l’impostazione del controllo. In questo modo:

1. premi Ctrl+Alt+F3 per richiamare la finestra “Nuovo Nome”,
2. inserisci nella finestra il nome che preferisci,
3. al posto del riferimento, inserisci la formula =SCARTO(Cellacon1oRecord;0;0;CONTA.VALORI(Tabella[CampoElenco]);0); Cellacon1oRecord è il campo relativo del primo record della tabella Excel con i dati che inserisci, “Tabella” è il nome della tabella Excel, “CampoElenco” è il campo della tabella Excel da cui vogliamo estrarre l’elenco dei valori univoci;
4. premi OK
5. seleziona la cella o l’intervallo in cui vuoi inserire il controllo
6. clicca sul comando “Convalida dati” della scheda “Dati” della barra multifunzione,
7. seleziona “Elenco” dal menu “Consenti” della scheda “Impostazioni”,
8. inserisci il nome esatto che hai creato, in questo modo: =NuovoNome,
9. se vuoi vai alla scheda “Messaggio di input” e imposta un messaggio personalizzato;
10. vai alla scheda “Messaggio di Errore” e imposta il tuo messaggio,
11. poi seleziona “Avviso” dal menù “Stile”,
12. premi OK e hai finito.

Come vedi è un poco complicato, ma in sostanza:

1. il nome che hai assegnato si riferirà all’elenco univoco dei valori che hai inserito finora,
2. il controllo mostrerà il menu a tendina con l’elenco,
3. se dovrai inserire un nuovo nome la finestra di blocco ti permetterà di farlo,
4. e in automatico l’elenco si aggiornerà con il nome aggiunto.

E se invece con “dinamico” intendi altro? Per esempio:

Posso creare elenchi concatenati che mostrano valori diversi a seconda del valore di un’altra cella o campo?

La risposta è sì, certo, lo abbiamo già visto nel post precedente. Possiamo realizzare anche menu dinamici codipendenti o concatenati.

In sintesi per farlo devi:

1. preparare in una pagina apposita le liste necessarie, cioè la lista principale i cui valori devono essere i titoli delle altre liste secondarie,
2. dare un nome agli intervalli di cella di ogni lista, i nomi assegnati alle liste secondarie devono corrispondere ai titoli delle liste e quindi ai valori della lista primaria,
3. realizzare il primo controllo/menu su pagina come già visto, indicando il nome dell’elenco principale (es: =Nome1aLista),
4. poi realizzare il secondo controllo/menu come già visto, inserendo non un nome, ma la formula =INDIRETTO(RifCella1oMenu); RifCella1oMenu è il riferimento della cella del primo controllo/menu (es: Indiretto(b5)).

Un esempio classico di impiego è quello di maschere di inserimento o controllo in cui abbiamo un elenco regioni e poi un elenco provincie e poi un elenco comuni, cap, eccetera. Naturalmente puoi replicare la concatenazione più volte a seconda delle necessità

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.

Note aggiuntive

Penso sia utile ricordare questi aspetti della convalida dei dati che ne facilitano l’utilizzo:

1. puoi replicare il controllo copiando la cella che lo contiene in qualunque modo tu lo faccia (es: trascinamento, da comando menu contestuale, ecc); copiare solo formati non copierà i controlli;
2. puoi cancellare/rimuovere un controllo tramite la finestra relativa, o usando il pulsante “Cancella tutto”, o impostando la scelta “qualsiasi valore” nel menu “Consenti”; puoi farlo anche usando il comando “cancella tutto” della scheda “Home” della barra multifunzione, oppure incollando una cella senza controllo su quella esistente; attenzione copiare solo i formati non cancellerà i controlli;
3. la voce “Ignora celle vuote” della scheda “Impostazioni” non applica i controlli alle celle senza contenuto;
4. la voce “Applica le modifiche a tutte le altre celle con le stesse impostazioni” della scheda “Impostazioni” si utilizza per propagare la modifica a tutte le celle che contengono lo stesso controllo;
5. il comando “Cerchia dati non validi” del menu “Convalida dati” usa i controlli applicati a dati già inseriti nelle celle per evidenziare con cerchi rossi i valori non validi; utile per eseguire i controlli su dati preesistenti; il comando “Rimuovi tutti i cerchi” rimuove l’evidenziazione precedente;
6. sebbene è consigliabile usare i controlli per bloccare gli inserimenti, puoi modificare la tassatività del controllo per permettere l’inserimento dalla scheda “Messaggio di errore”, menu “Stile”; la voce “Avviso” permetterà l’inserimento dopo conferma tramite pulsante della finestra di errore, mentre la voce “Informazione” lo inserirà subito dando comunicazione dell’invalidità tramite finestra di errore.

Conclusioni

La convalida è uno strumento classico, ma potente e flessibile che può essere impiegato per il controllo degli inserimenti, per il data cleaning, per la creazione di controlli interattivi su pagina, per la creazione di messaggistica e altro ancora.

Come hai visto possiamo realizzare controlli per la riduzione degli errori di inserimento sulla pagina, ma anche creare controlli avanzati e dinamici che verificano dati preesistenti o loro elaborazioni, come possiamo creare menu dinamici, strumenti sulla pagina con cui controllare tabelle e grafici, messaggi sulla cella e altro ancora.

La serie di esempi di controlli avanzati inserita in questa guida vuol essere solo un assaggio delle possibilità che la convalida dei dati offre quando viene utilizzata insieme a formule e funzioni e a strumenti come nomi e tabelle Excel.

I limiti di tutto ciò, in primo luogo dei controlli automatici di convalida dipendono dai limiti degli strumenti, sì, ma soprattutto dalle nostre competenze. Per questo ti invito a studiare e approfondire l’uso della convalida dei dati che in molte situazioni può rivelarsi efficace e comoda.

Per comodità tua e mia puoi scaricare il PDF della guida tramite Gumroad

Devi solo cliccare sul pulsante sottostante e seguire le istruzioni

Scarica la Guida

NB: non devi pagare, non è necessario lasciare la tua mail

Iscriviti a ExcelProfessionale per scoprire tutto quello che Excel può fare per te

Excel può fare per te più di quello che immagini.
Se vuoi scoprire e apprendere cosa puoi fare realmente con Excel, iscriviti a Excel Professionale usando la maschera seguente.

Riceverai la newsletter bimestrale, accederai ai contenuti gratuiti condivisi (fogli dimostrativi, macro, eccetera), potrai iscriverti gratuitamente ai video corsi introduttivi e ai webinar formativi, riceverai offerte dedicate ai corsi avanzati e molto altro.

Se questa guida ti è piaciuta o ti è stato utile, condividila con gli amici sui social. Grazie.
Puoi farlo attraverso il pulsanti qui di seguito.
Grazie!

PS: Qui puoi trovare le altre guide di Excel Professionale