Convalida dati

samuele agnolin
  • 18 febbraio 2016
  • livello: base/intermedio   3 minuti
convalida-dati

Il convalida dati di Excel consente di definire delle limitazioni su ciò che può essere inserito in una cella o in un intervallo di celle.

È possibile, ad esempio, consentire l’inserimento solo di numeri interi compresi tra due valori, di date comprese in un intervallo temporale, di contenuti lunghi esattamente un numero predefinito di caratteri.

La funzionalità è accessibile nella scheda Dati di Excel, voce “Convalida dati…”.

Il comando si presenta come visualizzato nell’immagine sottostante:

finestra convalida dati

La prima scheda “Impostazioni” è la più importante perché in essa si definiscono le regole di inserimento.

Il valore predefinito è “Qualsiasi valore”, in linea con il fatto che solitamente in una cella di Excel è possibile inserire qualsiasi tipo di contenuto: testi, numeri, date ecc.

convalida dati elenco opzioniCliccando sulla voce “Qualsiasi valore” compare un elenco che visualizza le varie tipologie di regole applicabili.
Vediamo subito un paio di esempi di applicazione del convalida dati e poi torniamo a parlare nel dettaglio di tutte le opzioni, dai messaggi di input ed errore ai criteri di convalida utili per creare un convalida dati personalizzato.




2 esempi di base

Ecco due esempi veloci. Nella colonna Età andiamo ad inserire una restrizione indicando che è possibile inserire solo numeri interi tra 18 e 65. Nella colonna Genere andiamo ad inserire un elenco predefinito contenente F ed M. Se hai bisogno di sapere nel dettaglio come funziona l’elenco predefinito puoi leggere il tutorial su come creare un menu a tendina in Excel.

convalida dati esempi

Ora che abbiamo intuito che cosa possiamo fare con il convalida dati proviamo ad entrare nel dettaglio delle voci che troviamo quando utilizziamo il comando.

Il messaggio di input e il messaggio di errore

messaggio input convalida dati excelNella scheda “Messaggio di input” è possibile definire un messaggio che possa aiutare l’inserimento dei contenuti.

Quando nel convalida dati di Excel si eliminano eventuali regole dalla scheda “Impostazioni” è necessario eliminare anche eventuali messaggi inseriti nella scheda “Messaggio di input”, altrimenti il messaggio continuerà ad essere visibile pur non essendo presenti criteri di convalida.

 

convalida dati scheda messaggio di erroreNella scheda “Messaggio di errore” è possibile scegliere se, in caso di inserimento di contenuto non coerente con la regola definita, permettere o meno l’inserimento.

Lo stile “Interruzione” avviserà della presenza di una regola di convalida e bloccherà l’inserimento, lo stile “Avviso” mostrerà l’avviso ma consentirà di scegliere se inserire il contenuto non rispondente alla regola, lo stile “Informazione” avviserà della regola e inserirà il contenuto anche se non rispondente.

 

Questo il messaggio dello stile “Interruzione” nel caso di inserimento di valori non consentiti.

messaggio errore convalida dat

 

Nel caso non siano stati compilati i campi “Titolo:” e “Messaggio di errore:” il messaggio del convalida dati di Excel si presenterà cosìmessaggio errore non specificato

 

Di seguito le finestre di definizione di parametri e quelle di avviso legate allo stile “Avviso”.

definizione parametri convalida avviso

messaggio errore avviso convalida

 

Di seguito le finestre di definizione di parametri e quelle di avviso legate allo stile “Informazione”.

parametri-informazione-convalida

messaggio errore informazione convalida

 

I criteri di convalida nel dettaglio

Quali tipologie possono essere definite tramite la scheda “Impostazioni”? Vediamo tutte le voci con cui possiamo creare un convalida dati personalizzato.

Numero intero: consentito l’inserimento di soli numeri interi compresi in un intervallo o maggiori/minori/diversi/uguali a quanto specificato. Per questa e per le altre tipologie di regole, è possibile far riferimento anche a numeri scritti nelle celle del foglio di lavoro o di altri fogli.

Decimale: consentito l’inserimento di numeri (anche con decimali, a differenza del punto precedente)

Elenco: consente di inserire nella cella un elenco a discesa. I valori da mostrare devono essere definiti attraverso il parametro “Origine:” possono essere scritti manualmente separando le voci con un ‘punto e virgola’, ad esempio Prodotto X;Prodotto Y;Prodotto Z oppure possono essere letti da un intervallo specificato (anche di un altro foglio), ad esempio =$D$10:$D$12. Il tutto è illustrato nel dettaglio nel tutorial su come creare un elenco in una cella.

Data: consente l’inserimento di date, specificando una date un intervallo di date di riferimento, ad esempio maggiore di 01/01/2016. È possibile far riferimento anche a date scritte nelle celle del foglio di lavoro o di altri fogli.

Ora: consente l’inserimento di orari, specificando un orario o un intervallo di orari di riferimento, ad esempio tra 12:00 e 14:00

Lunghezza testo: consente di specificare il numero di caratteri (stringhe/numeri) richiesti nella cella

Personalizzato: nel parametro “Formula:” è possibile scrivere una espressione che dia come risultato Vero o Falso. Su quanto viene scritto nella cella viene applicata l’espressione specificata. Se l’espressione   restituisce come risultato Vero allora verrà consentita l’immissione del contenuto altrimenti verrà segnalato che quanto scritto non risponde alla regola.

Nell’esempio seguente l’espressione =SINISTRA(F5;1)=”B” limita l’immissione nella cella ai testi che iniziano con la lettera “B”. La funzione SINISTRA(F5;1) restituisce infatti la prima lettera di quanto è stato immesso. Se ciò è uguale a “B” l’espressione risulterà vera e quindi sarà consentito l’inserimento.

Dopo aver selezionato un intervallo di celle, ad esempio B6:B10, è possibile scrivere nel parametro “Formula:” l’espressione =B6>B5 per fare in modo che le celle dell’intervallo selezionato accettino valori maggiori rispetto a quello della cella precedente. Se numeri o date dovranno essere più grandi di quello precedente, se testo superiori alfabeticamente.

È possibile fare riferimento anche ad una cella presente in un altro foglio del file in cui si sta lavorando.

Scrivendo nella cella C5 l’espressione =C5>C$3 si impone che il contenuto della cella C5 debba essere superiore a ciò che è scritto in C3.

Il riferimento misto (C$3) è necessario perché, quando si seleziona un intervallo di celle e poi si definisce la regola personalizzata facendo riferimento a celle del foglio, i riferimenti relativi (senza dollaro) vengono riferiti alla prima cella dell’intervallo su cui si sta definendo la regola, per tutte le altre celle la regola viene adattata, quindi i riferimenti vengono aggiornati incrementando la riga (o la colonna nel caso di intervallo orizzontale).

Se nell’esempio la formula fosse =C5>C3 invece che =C5>C$3 succederebbe che nella seconda cella dell’intervallo selezionato l’espressione valutata diventerebbe =C6>C4 invece che =C6>C$3.

 

L’espressione =CONTA.SE(D$5:D$9;D5)=1 consente in D5:D9 solo valori non duplicati.

L’espressione =VAL.TESTO(E5) consente di inserire solo valori testuali.

L’espressione =SOMMA(G$5:G$9)<=$G$3 verifica che la somma dei valori in G$5:G$9 non superi il valore presente in G$3.

 

L’espressione =E(H5>H4;H5>C$3) consente di inserire due condizioni attraverso l’utilizzo della funzione logica E.

 

Ignore celle vuote e altri dettagli

  • La voce “Ignora celle vuote” della scheda “Impostazioni” permette, se selezionata, di non applicare le regole di convalida sulle celle senza contenuto
  • La voce “Applica le modifiche a tutte le altre celle con le stesse impostazioni”, presente nella scheda “Impostazioni” si utilizza quando si sta modificando la regola di una cella e si desidera che le modifiche vengano applicate a tutte le celle del foglio corrente in cui è stata definita inizialmente (o tramite copia/incolla della cella) la regola
  • La voce “Elenco nella cella” visibile nel caso di tipologia regola “Elenco” definisce se visualizzare o meno la tendina dei valori nella cella
  • Il pulsante “Cancella tutto” elimina tutto ciò che è stato definito nelle varie schede, tornando ad una situazione iniziale in cui nella cella non vi è nessuna limitazione nell’inserimento.
  • Copiando il formato di una cella che ha una regola di convalida e incollandola in una senza regole, quest’ultima non acquisisce la regola della cella di partenza
  • Il copia/incolla (anche mediante trascinamento) da una cella contenente una regola verso una cella senza regole incollerà anche la regola di convalida, andando ad aggiornare eventuali riferimenti di cella
  • Nella situazione opposta a quella del punto precedente, quando si copia/incolla (anche mediante trascinamento) da una cella senza regole verso una con regole si perderà la regola nella cella di destinazione
  • I comandi “Cerchia dati non validi” (scheda Dati, “Convalida dati”) e “Rimuovi tutti i cerchi” permettono di evidenziare o meno con degli ovali rossi le celle che non rispondono alle regole di convalida

21 Comments

You can post comments in this post.


  • Samuele, avrei la necessità di vedere nella stessa tendina i valori presenti in due colonne.
    La guida excel dice che non si può, c’è un metodo alternativo?

    Angelo Ferri 10 mesi ago Reply


    • Ciao Angelo.

      E’ vero. Il convalida dati non permette di visualizzare i valori presenti in due colonne.

      Ti propongo alcune soluzioni. Secondo me dipende molto dal tipo di utilizzo che ne devi fare.

      Non è detto che non si possa scegliere una strada alternativa ad una tendina. Poco tempo, ad esempio, avevo suggerito ad un lettore che mi aveva scritto in privato di utilizzare, per il suo problema legato all’imputazione dati, non la classica tendina ma i messaggi opzionali, sempre del convalida.

      Di seguito le mie idee. Se vuoi inviarmi a samuele@amicoexcel.it un file simile alla situazione che devi gestire vedo volentieri.

      1) metodo artigianale: concateni i valori delle due colonne in un’unica colonna che vai ad usare per popolare il convalida (dipende poi da cosa devi farne… devi usare la scelta della tendina in un CERCA.VERT? si può vedere di personalizzare il cerca.vert)

      2) usare un controllo ActiveX per creare un elemento Casella Combinata (dalla scheda sviluppo)… se ti interessa questa soluzione te la posso descrivere nel dettaglio

      Samuele 10 mesi ago Reply


  • Ciao Samuele,
    io con la convalida dei dati ho un problema diverso: nella formula della selezione di un elenco ho posto diverse(molte) condizioni per appunto andare a scegliere l’elenco giusto in base a valori contenuti in altre celle; ma ad un certo punto non mi fa proseguire oltre, ovvero la formula del convalida dati ha una lunghezza massima!?
    Potresti aiutarmi?
    Grazie

    Fabio 6 mesi ago Reply


    • Ciao Fabio. Che messaggio ottieni? E che funzione stai utilizzando? Perché se stai usando la funzione E questa ha un limite attuale di 256 condizioni. Rispetto alla lunghezza di ciò che scrivi all’interno della formula della formattazione condizionale mi pare che ci fosse un limite di 256 caratteri ora, in teoria, superato.

      samuele 6 mesi ago Reply


  • Salve, ho un problema con il messaggio di input che non si adatta al contenuto, “tagliando”, ovvero lasciando fuori parte del testo. credo non vada oltre le 6 righe. è un mio problema o è proprio così???

    michele 6 mesi ago Reply


    • Ciao Michele, è più una questione di numero di caratteri. Nel messaggio di input del convalida dati il massimo è 255, con eventuale variabilità legata alla larghezza ed altezza predefinite del box di visualizzazione.

      samuele 6 mesi ago Reply


      • il messaggio contiene meno di 255 caratteri. forse è un problema di risoluzione dello schermo?
        in effetti su un altro pc veniva visualizzato correttamente.
        su questo che ho adesso che è un fullhd si presenta questo problema..

        Michele 6 mesi ago Reply


        • In effetti Michele potrebbe essere come dici.

          samuele 6 mesi ago Reply


          • come faccio a risolvere allora??

            michele 4 mesi ago


  • Ciao Samuele,
    io ho una grande quantità di dati “ora”: ho applicato la convalida tra 00:00:01 e 23:59:59.
    Come faccio ad eliminare tutti i dati che non hanno passato la convalida?
    I dati sono molti (più di 10.000) e non posso pensare di doverli eliminare 1 per 1…
    Grazie mille per la tua disponibilità.
    Andrea

    Andrea 6 mesi ago Reply


    • Ciao Andrea. Sinceramente non conosco una funzionalità che faccia quel che chiedi. Io proverei un’altra strada: formattazione condizionale più filtro. Con la formattazione condizionale colorerei il carattere o lo sfondo delle celle che non rispettano la tua regola. Applicherei poi il filtro basandomi sul colore applicato con la formattazione condizionale.

      La formula nella formattazione condizionale potrebbe essere qualcosa di questo genere:E(A1>ORARIO.VALORE(“00:00:01”);A1

      samuele 6 mesi ago Reply


      • Ciao Samuele,
        mi pare un’ottima alternativa! La provo subito!
        Grazie mille e complimenti per il sito!
        Andrea

        Andrea 6 mesi ago Reply


        • Grazie Andrea! Bene, fammi sapere.

          La mia risposta precedente non veniva visualizzata per intero…

          Ti dicevo che “la formula nella formattazione condizionale potrebbe essere qualcosa di questo genere: dentro la funzione
          le due condizioni da verificare…. =E(A1> ORARIO.VALORE(“00:00:01”); A1< ORARIO.VALORE("23:59:59"))Ma le celle che devono essere eliminate di che tipo sono? Perché se fossero testuali o altro nella formattazione condizionale si potrebbe utilizzare anche formule diverse. Se hai bisogno mandami uno stralcio del tuo file"

          samuele 6 mesi ago Reply


  • Salve,ho un problema con la convalida dei dati.
    Ho scelto per la convalida dei dati l’opzione – elenco nel quale se’ disponibile scegliete tra l’ora (6:00, 7:00, ecc.) e descrizioni come per esempio GL e GD.
    Tutto funzione, quando scelgo sulla lista. Pero quando scrivo manualmente non mi accetta tutte le ore, ad esempio 6:00 accetta pero 7:00 non acceta.
    Sa forse dirmi quale potrebee essere il problema? Le celle sono impostate come ore (hh:mm).

    grazie per la risposta!

    Cordiali saluti

    Lea 3 mesi ago Reply


    • Ciao io controllerei l’elenco inserito nel convalida: gli orari sono tutti scritti nello stesso modo? Se sono scritti come 06:00 e non 6:00 dovrebbe esserti consentito anche l’inserimento manuale.

      samuele 3 mesi ago Reply


  • Ciao Samuele, chiarissimo e completo. Complimenti. Ho creato una tabella e con convalida dati faccio in modo che venga compilata secondo vari elenchi sistemati in un altro foglio. Faccio comparire i vari messaggi se le condizioni non vengono rispettate, Io però ho anche l’esigenza che TUTTE le celle vengano compilate e che non vi siano celle vuote. In altre parole se passi oltre e vai alla cella successiva dovrebbe comparire un messaggio di errore. Ho disattivato “Ignora celle vuote” ma….si va avanti tranquillamente. Per me è INDISPENSABILE che siano compilate tutte le celle. Che fare ??? Inserire qualche formula? Grazie grazie grazie

    Enrico 3 mesi ago Reply


  • Salve,
    è possibile fare in modo che, in una cella i cui valori sono vincolati ad un elenco, ci sia digitazione assistita?

    Esempio: inserisco “Fast” e excel mi suggerisce Fastweb (che deve fare parte dell’elenco ovviamente)

    Grazie

    Francesco Bergamaschi 2 mesi ago Reply


    • Ciao Francesco, io tenterei con del codice VBA all’interno di una macro. Mi hai dato uno spunto buono da tenere in considerazione quando inserirò degli esempi pratici nel tutorial sulle macro. Grazie.

      samuele 2 mesi ago Reply


  • Ciao Samuele,
    una domanda. Devo fare in modo che se nel menu a tendina della cella G3 scelgo SI, allora nella cella H3 compaiono le date; se invece appare NO, nella cella H3 appare lo slash “/”. Ma se nella cella H3 c’è la convalida dati come faccio a inserirci anche la formula?
    Grazie mille,
    Luca

    Luca 2 settimane ago Reply


Inserisci un commento