La maggior parte degli errori in un foglio di calcolo non nasce dalle formule, ma dai dati inseriti a mano. Un agente scritto in tre modi diversi, una data digitata come testo, una quantità negativa per distrazione: ognuno di questi dettagli altera totali, ricerche e tabelle pivot. La convalida dei dati serve a impedire questi errori alla fonte, limitando ciò che si può scrivere in una cella.
In Excel si trova in Dati, Convalida dati; in LibreOffice Calc in Dati, Validità; in Google Sheets in Dati, Convalida dei dati.
Gli elenchi a discesa
È l'uso più comune e quello con il beneficio più immediato. Invece di scrivere a mano lo stato di una pratica, l'utente lo sceglie da un menu.
Elenco scritto nella regola
Per pochi valori stabili si possono inserire direttamente nella regola, separati dal punto e virgola (o dalla virgola, a seconda delle impostazioni internazionali): Aperta;In lavorazione;Chiusa. È rapido ma poco pratico da mantenere, perché per aggiungere una voce bisogna modificare la regola in ogni cella.
Elenco preso da un intervallo
La soluzione migliore è tenere i valori ammessi in un foglio separato, per esempio un foglio «Elenchi» con una colonna per ogni categoria, e indicare quell'intervallo come origine. Per aggiungere una voce basta scriverla nell'elenco.
In Excel conviene trasformare l'elenco di origine in una tabella e assegnargli un nome: in questo modo l'intervallo si allarga da solo quando aggiungi valori in fondo, e la regola di convalida non va mai ritoccata.
Elenchi dipendenti
Un caso classico: scegli una regione e il secondo menu mostra solo le province di quella regione. In Excel si ottiene creando un intervallo con nome per ogni regione (con lo stesso nome della regione, senza spazi) e usando come origine del secondo elenco =INDIRETTO(A2). Funziona, ma è fragile: se il nome della regione contiene spazi o apostrofi l'intervallo non viene trovato. Nelle versioni con matrici dinamiche si può usare invece una formula FILTRO in una zona di appoggio e puntare la convalida al suo risultato.
Limitare numeri, date e lunghezze
Oltre agli elenchi, la convalida permette di imporre limiti precisi:
| Tipo | Esempio di regola | Uso tipico |
|---|---|---|
| Numero intero | tra 1 e 999 | quantità ordinate |
| Decimale | maggiore o uguale a 0 | importi, pesi |
| Data | tra 01/01 e 31/12 dell'anno in corso | date di registrazione |
| Lunghezza testo | uguale a 16 | codici a lunghezza fissa |
Anche i limiti possono fare riferimento a celle: «data minore o uguale a =OGGI()» impedisce di registrare eventi nel futuro.
Regole personalizzate con formula
L'opzione «Personalizzato» accetta qualsiasi formula che restituisca VERO o FALSO. La formula viene scritta pensando alla prima cella dell'intervallo selezionato, con riferimenti relativi che si adattano alle altre celle.
Alcuni esempi utili, ipotizzando che la selezione parta da A2:
- niente duplicati in una colonna:
=CONTA.SE($A$2:$A$1000; A2)=1; - solo testo che inizia con un prefisso:
=SINISTRA(A2; 3)="PR-"; - coerenza tra due campi, per esempio data di fine non precedente alla data di inizio:
=C2>=B2; - nessuno spazio all'inizio o alla fine:
=A2=ANNULLA.SPAZI(A2).
La regola contro i duplicati è particolarmente preziosa per codici cliente, numeri di protocollo e qualsiasi identificativo che deve essere univoco.
Messaggi di input e di errore
Ogni regola può avere due messaggi:
- il messaggio di input, che compare quando selezioni la cella e spiega cosa inserire («Inserire la data nel formato gg/mm/aaaa»);
- il messaggio di errore, che compare quando il valore non rispetta la regola.
Per l'errore si può scegliere lo stile. «Interrompi» blocca l'inserimento; «Avviso» e «Informazione» mostrano il messaggio ma permettono di proseguire. Per i campi critici usa sempre il blocco: un avviso viene ignorato con un clic.
Il limite principale: copia e incolla
La convalida controlla ciò che viene digitato, ma in Excel e LibreOffice un valore incollato da un'altra cella può sovrascrivere la regola stessa, insieme alla formattazione. Inoltre le regole non controllano i dati che erano già presenti prima della loro creazione.
Per questo, dopo aver impostato le regole su un foglio esistente, conviene usare il comando che evidenzia i dati non validi: in Excel si trova nel menu della convalida come «Cerchia dati non validi», e disegna un cerchio rosso intorno a ogni cella che non rispetta la regola. Google Sheets segnala le celle non valide con un piccolo indicatore nell'angolo.
Nel mio lavoro, quando preparo un modello che altre persone compileranno, abbino la convalida alla protezione del foglio: le celle di inserimento restano sbloccate e convalidate, tutto il resto è protetto. È il modo più semplice per ricevere indietro dati utilizzabili.
In sintesi
- La convalida impedisce gli errori di inserimento prima che entrino nei calcoli.
- Gli elenchi a discesa vanno alimentati da un intervallo separato, meglio se con nome.
- Le regole personalizzate con formula coprono duplicati, prefissi e coerenza tra campi.
- Usa lo stile «Interrompi» per i campi critici e un messaggio di input chiaro.
- Incollare può aggirare le regole: verifica periodicamente i dati non validi.
