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

32 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 2 anni 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 2 anni 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 1 anno 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 1 anno 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 1 anno 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 1 anno 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 1 anno ago Reply


        • In effetti Michele potrebbe essere come dici.

          samuele 1 anno ago Reply


          • come faccio a risolvere allora??

            michele 1 anno 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 1 anno 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 1 anno ago Reply


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

        Andrea 1 anno 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 1 anno 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 1 anno 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 1 anno 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 1 anno 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 1 anno 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 1 anno 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 12 mesi ago Reply


  • Ciao ragazzi,

    ho l’esigenza di creare una colonna con menu a tendina con gli orari di inizio e una colonna con menu a tendina con l’orario di fine.
    Ora vorrei riuscire a inibire:
    che l’orario della cella di fine sia minore o uguale a quella di inizio
    che l’orario della cella di fine abbia sia maggiore di almeno 30 min di quella di inizio
    che l’orario della cella di inizio della riga successiva controlli l’orario di fine della riga precedente
    che l’orario della cella di inizio della riga successiva abbia un intervallo minimo di 30 min rispetto alla cella di fine della riga precedente.

    Come posso fare?

    Grazie

    Daniele 9 mesi ago Reply


    • Ciao Daniele,
      mi pare che il convalida dati non sia sufficiente… bisognerebbe utilizzare del codice VBA

      samuele 8 mesi ago Reply


  • Ragazzi invece se io volessi avere un tasto che mi metta tutte le convalida dati a un valore predefinito? come posso fare. mi serve per azzerare tutta una tabella.
    grazie

    Carmine Francesco 8 mesi ago Reply


    • Ciao Carmine. Io proverei a selezionare tutte le celle, poi scriverei il valore predefinito sulla prima e con CTRL+INVIO dovresti riuscire a incollarlo su tutte le celle selezionate…

      samuele 8 mesi ago Reply


  • Ciao Samuele,
    io ho la limitazione dei caratteri sulle mie celle. Funziona, cioè se provo a scrivere più caratteri di quelli imposti, ricevo l’errore, tuttavia se copio e incollo un valore più lungo, me lo incolla tranquillamente senza seguire la regola. Anche se provo a fare solo incolla valore o incolla funzione!
    Come posso fare? Grazie

    Mauro 8 mesi ago Reply


  • Salve Samuele, non è un commento, non trovo per contatti quindi mi adopero: la mia richiesta è, convalida dati in colonna B2:B7000ho delle date e vorrei che all’apertura della richiesta in cella avere nell’elenco solo le righe della prima del mese. Come aiuto in colonna A2:A7000 esempio 1, 2, 3, ecc che riguarda giorno 1, 2, 3 eccetera fino ad un massimo di 14 come ultimo numero che si riferisce al mese, Mese appresso ricomincia con 1, 2, 3 ecc Come si può risolvere questa situazione. Grazie NB.Nella cella B1 la cella della colonna B e non A

    Giorgio Gatti 4 mesi ago Reply


    • Ciao Giorgio. Sinceramente ci ho capito poco. Se vuoi mandami (samuele@amicoexcel.it) un file sintetico con due dati dentro e con il risultato che vorresti ottenere.

      samuele 4 mesi ago Reply


  • Ciao a tutti, ho un problema e non so se si risolve con la funzione CONVALIDA ve lo sottopongo:
    in un file ho una tabella “Anagrafica” dove ci sono 3 campi: Cod. Cliente, cod. commessa e descrizione commessa in cui lo stesso cliente potrebbe avere più records con commesse diverse;
    in un’altra tabella “Gestione” dello stesso file c’è la colonna Cliente dove con il Convalida vedo l’elenco dei clienti presenti dalla tabella precedente, poi a fianco c’è un’altro campo commessa dove dovrei immettere una delle commesse del cliente pre-selezionato.
    Con il CONVALIDA “personalizzato” non ce la faccio e penso che non si possa fare.
    Allora ho pensato di creare un campo Chiave Ricerca nella tabella Anagrafica che concatena Cliente&Cod.Commessa&Des.Commessa) così facendo il Convalida su questo campo vedo tutti i dati insieme e riporto il valore nella cella della tabella di Gestione; poi però da questa stringa dovrei estrarre il Cliente nel campo Cliente che si trova a SX del campo chiave (ce l’ho fatta con questa formula:
    =SINISTRA([RICERCA];TROVA(” – “;$D2)-1)
    ma poi non riesco a recuperare il codice commessa (8 char) che è immediatamente successivo al cod .cliente separato da “-“. NB non devo riportare però la descrizione che segue al cod. commessa.
    Qualcuno sa suggerire la soluzione?
    PS: se volete potete scrivermi all’indirizzo mail lciani60@gmail.com
    Grazie anticipate
    Luigi

    Luigi Ciani 2 mesi ago Reply


  • Buongiorno Samuele;
    Se io volessi semplicemente creare un avviso, che in un foglio alcune celle non sono state compilate, a prescindere che siano date, nomi o altro? È possibile?
    Grazie in anticipo!

    Alessandro Ferrari 4 settimane ago Reply


  • Buongiorno Samuele,

    Ho un problema con i menù a discesa creati con convalida dati; Ho una tabella di origine dei dati dove alcuni valori sono espressi in numeri con un formato cella personalizzato che mi indica se sono KG o L. Ho dovuto usare il formato cella personalizzato perchè essendo delle quantità che poi vengono calcolate con formule, devono essere per forza essere dei numeri altrimenti le formule non funzionano, perchè giustamente, se io nella cella di origine ci scrivessi “10 L” o “10 Kg”, verrebbe considerato come testo.

    Ho solamente bisogno che quando seleziono dal menù a discesa la quantità mi venga riportata anche l’unità di misura. Invece questo non accade perchè la quantità prende il formato della cella di destinazione, anzichè della cella di origine. E’ un rompicapo perchè nel menù a discesa le unità di misura ci sono scritte! è solo quando seleziono che spariscono.

    Ho provato a cercare ovunque ma non trovato nulla.
    Grazie in Anticipo per il tuo tempo!

    Matteo 2 settimane ago Reply


    • Ciao Matteo. Chiaro perché utilizzi il formato personalizzato nelle celle che poi usi come “origine” della tua tendina. Sinceramente non mi è mai capitato di gestire una cosa del genere. Immagino tu non abbia trovato nulla, dubito che si possa risolvere con gli strumenti classici. Mi sa che bisognerebbe orientarsi sul vba.

      samuele 5 giorni ago Reply


Inserisci un commento