Utilizzare la convalida dati in Excel

Convalida Dati: creare un menù a tendina per l'immissione dei dati

Excel non è prioritariamente un programma per la creazione di database, anche se viene utilizzato da molti utenti per questo scopo.
Nella famiglia di prodotti Office, Microsoft Access rappresenta il programma più adatto per creare database complessi e vincolati a regole ferree di validazione dei dati.
Che piaccia o meno, l’immissione dei dati in una cella Excel può essere sottoposta ad errate digitazioni che riducono l’efficacia del database stesso.
Si osservi l’esempio dell’immagine. In questa tabella di iscritti ai corsi di informatica di una ipotetica società di formazione, le denominazioni dei corsi sono a volte complesse e può capitare che un corso venga scritto in modo errato.
Inoltre, spesso viene digitata la barra spaziatrice anche quando un valore è scritto correttamente, ma lo spazio è un carattere come qualsiasi altro, per cui la parola risulta comunque digitata in modo sbagliato.
Scrivere in modo corretto un valore non è puro esercizio di stile ma influenza il buon funzionamento di un file Excel.
Si guardi la tabella pivot a fianco dell’elenco dati: non informa che gli iscritti ai corsi Sharepoint sono 7 ma 1 per ogni dicitura diversa ed anche quando i valori sembrano scritti allo stesso modo (celle A5 ed A6) in realtà sono diversi, perché in uno dei casi c’è lo spazio vuoto dopo l’ultima lettera e l’errore si trascina sulla pivot.




Pur non avendo la potenza dei comandi similari di Access, esiste una funzionalità in Excel che può svolgere un ruolo importante e che andrebbe sempre presa in considerazione durante l’operazione di immissione dati: la convalida dati di tipo elenco.
Sarebbe a dire: l’utente può digitare soltanto le voci che fanno parte di un menu a tendina o scegliere direttamente tali voci all’interno del menu.
La prima domanda da porsi è: quali sono le voci su cui si basa l’elenco? La soluzione più comoda è digitare in un intervallo di celle qualsiasi del foglio le denominazioni corrette, che fungeranno da base per l’elenco. Detto fatto: in una selezione di celle sono state immesse le voci così come dovranno apparire nel menu a tendina, compresa la voce Sharepoint che tanti problemi aveva creato… Per la comodità della dispensa si trovano nello stesso foglio, ma nella realtà è consigliato disporle in un foglio di lavoro a parte. Ora si pone il quesito: come creare il menu? La procedura è descritta a seguire.

1. La prima cosa da fare è selezionare tutte le celle che dovranno essere sottoposte alla convalida.Si può selezionare anche tutta la colonna, se necessario. Poi sulla scheda Dati nel gruppo di comandi Strumenti dati si clicca sul comando Convalida dati e si agisce dalla finestra omonima.

2. La linguetta Impostazioni è attiva. Nella sezione Consenti, dove appare la scritta “Qualsiasi valore” bisogna cliccare e poi scegliere Elenco come tipo di criterio di convalida.

3. Si valorizza la sezione Origine, in cui vanno specificate le voci che appariranno come scelte nel menu a tendina. Siccome le voci sono presenti nel foglio di lavoro in un intervallo celle, si seleziona tale intervallo (nello specifico J2:J21).

4. Si può già concludere qui e confermare con Ok, ma è consigliabile anche usare le altre due linguette per immettere un messaggio di input e/o un messaggio di errore. Il primo appare ogni volta che l’utente seleziona una cella sottoposta a regole di convalida e serve per suggerire all’utente l’operazione da fare. Il secondo interviene soltanto quando l’utente ha commesso un errore immettendo un dato che non rispetta le regole e suggerisce cosa occorre fare. In realtà un messaggio di errore predefinito e generico appare sempre quando si sbaglia, ma è altamente consigliabile prevedere un messaggio che guidi in modo più chiaro l’utente all’operazione da effettuare.





Nelle celle selezionate appare il menu a tendina che rende impossibile digitare la voce in modo errato e garantisce una efficacia maggiore nella reportistica.
Il problema che si pone è casomai un altro: cosa accade se nel futuro la società di formazione decide di aggiungere nuovi corsi? In altre parole: il menu a tendina includerà o meno le celle eventualmente aggiunte all’origine dati dell’elenco? La risposta è negativa: ciò che verrà aggiunto alla base dell’elenco a partire dalla cella J22 non viene visto dal menu a tendina e l’utente è costretto a rientrare nel comando di convalida per estendere l’origine dati.
Né rappresenta una soluzione pragmatica la selezione preventiva di più celle rispetto a quelle già compilate, poiché nel menu a tendina si vedranno prima tutte le celle vuote e la scelta sarà più scomoda. Le soluzioni possibili sono due.

FORMATTA COME TABELLA

Rappresenta la soluzione più semplice. L’intervallo celle con l’origine elenco va formattato come tabella dal comando opportuno (Formatta come tabella) che si trova nella scheda Home.
Il dinamismo è garantito dalla estensione automatica della tabella fino ad includere nuove celle che vengono subito viste dal menu a tendina.
Volendo, è anche possibile selezionare tutte le celle (nel caso specifico J2:J21) ed assegnarle un nome dalla Casella Nome (ad esempio Anagrafica Corsi, senza spazi) ed usare il nome AnagraficaCorsi come origine dell’elenco.
In tale caso si evita persino l’incombenza di dover selezionare le celle mentre si crea la convalida.

SCARTO

La seconda soluzione è più complessa ma può affascinare qualche utente più esperto o più interessato ad entrare nei meandri di Excel.
Prevede l’uso dei nomi e delle funzioni Scarto e Conta.Valori.
Questa newsletter non approfondisce la funzione Scarto che merita maggiore approfondimento.
Viene, quindi, descritto ciò che è utile sapere per adoperarla come origine dell’elenco di convalida.
La funzione Scarto prevede che si indichi una cella di partenza e si specifichi di quante righe e/o colonne si debba spostare il cursore del mouse dalla cella di partenza.
Ad esempio, scrivendo =SCARTO(J1;1;0) il puntatore del mouse parte dalla cella J1 e sposta il punto di inizio una riga sotto e nessuna colonna a fianco ovvero in J2.
Inoltre, bisogna indicare quante celle devono essere incluse in altezza ed in larghezza a partire dal punto di partenza.
Nel nostro caso la colonna dell’origine elenco è solo una mentre in altezza devono essere incluse tutte le celle contenenti dati ovvero tutte le celle piene.
E la funzione che conta le celle piene è Conta.Valori, da applicare includendo tutta la colonna J.
Siccome con il Conta.Valori viene compresa anche una cella vuota bisogna specificare di escluderla dal computo per cui la funzione finale è =SCARTO(J1;1;0;CONTA.VALORI(J:J)-1;1).
Traduzione: parti dalla cella J1, spostati in J2 (una riga e nessuna colonna) ed includi in verticale tutte le celle piene della colonna J meno una ed una sola colonna in orizzontale.
Siccome come origine dell’elenco la convalida dati accetta solo intervalli di celle o nomi e non funzioni, bisogna assegnare un nome alla funzione. Come? Si clicca sul comando Definisci nome nella scheda Formule.
Si assegna un nome nella sezione Nome della finestra Nuovo Nome mentre nella sezione Riferito a si digita la funzione, avendo cura di bloccare i vari riferimenti di cella con il riferimento assoluto =SCARTO($J$1;1;0;CONTA.VALORI($J:$J)-1;1).
Il gioco è fatto: il nome assegnato alla funzione viene usato come origine dell’elenco ed ogni nuova voce aggiunta viene vista dalla convalida per merito della funzione Conta.Valori che include tutte le nuove celle piene. Abbastanza ingegnoso, vero?

Se l'articolo è stato utile, seguimi sui Social networks cliccando i pulsanti in alto a destra di questa pagina.