Convalida dati Excel

Limitare l’inserimento dati tramite convalida dati

Autore: Andrea Pacchiarotti
Ultimo aggiornamento: 14 Marzo 2023
Categoria: Microsoft Office Excel Convalida Dati

La convalida dei dati Excel permette di limitare l’inserimento dei valori inseriti in una cella a specifici dati (un po' come fanno il valido se e il messaggio di errore presenti nelle proprietà delle tabelle Access). In questo modo, si garantisce che i dati inseriti siano corretti e conformi a determinati criteri. Tramite Convalida dati si può, per esempio, permettere l’immissione solo di numeri, date o testi compresi tra due valori ma anche, come vedremo, di fare molto di più. La convalida dati è spesso utilizzata per creare un elenco a discesa. Curioso? Inizia la lettura di convalida dei dati Excel.

Sommario Excel Convalida dati

• Impostare una convalida dati in Excel
• Altre voci o pratiche Excel sulle regole di convalida
• Esempi regole di convalida Excel
• Aggirare le regole di convalida Excel
• Rimuovere le regole di convalida Excel

Impostare una convalida dati in Excel

Per impostare una convalida dati in Excel

1. Seleziona le celle su cui desideri creare una regola di convalida
2. Seleziona la scheda Dati e poi l’icona Convalida dati.
3. Nella scheda Impostazioni scegli una voce in Consenti:
• Qualsiasi valore: è il default e consente di inserire qualsiasi tipo di contenuto
• Numero intero: permette l'immissione di soli numeri interi (maggiore, minore, diverso, uguale, tra i valori specificati)
• Decimale: permette l'immissione di soli numeri decimali (maggiore, minore, diverso, uguale, tra i valori specificati)
• Elenco: permette di selezionare dati da un elenco a discesa
• Data: permette l'immissione di sole date (maggiore, minore, diverso, uguale, tra i valori specificati)
• Ora: permette l'immissione di sole ore (maggiore, minore, diverso, uguale, tra i valori specificati)
• Lunghezza testo: permette l'immissione di soli testi di una data lunghezza (maggiore, minore, diverso, uguale, tra i valori specificati)
• Personalizzato: permette di inserire formule personalizzate
4. In Dati, quando consentito, seleziona una condizione e imposta gli altri valori necessari
5. Seleziona la scheda Messaggio di input e personalizza un titolo e un messaggio da mostrare agli utenti che immettono i dati (seleziona la casella di controllo Mostra messaggio di input quando viene selezionata la cella)
6. Seleziona la scheda Messaggio di errore per personalizzare un titolo e un messaggio di errore in caso di inserimento di contenuto non coerente con la regola definita e scegliere uno Stile:
• Interruzione: avvisa della presenza di una regola di convalida e blocca l’inserimento
• Avviso: avvisa ma consente di scegliere se inserire il contenuto non rispondente alla regola
• Informazione: avvisa e inserisce il contenuto anche se non rispondente
7. Seleziona OK

È possibile far riferimento a valori scritti nelle celle del foglio di lavoro o di altri fogli: leggi cosa sono i Riferimenti relativi, misti e assoluti

Altre voci o pratiche Excel sulle regole di convalida

• Ignora celle vuote della scheda Impostazioni consente, se selezionata, di non applicare le regole di convalida sulle celle vuote
• Applica le modifiche a tutte le altre celle con le stesse impostazioni della scheda Impostazioni si usa quando si sta modificando la regola di una cella e si vuole applicare la modifica a tutte le celle in cui è stata definita inizialmente
• Elenco nella cella visibile quando si sceglie Elenco in Consenti definisce se visualizzare o meno la tendina nella cella
• Il pulsante Cancella tutto elimina le regole di convalida
• Copia formato non copia le regole di convalida
• Copia e incolla (anche tramite trascinamento) da una cella con regole di convalida incolla la regola medesima (vale anche il contrario da una cella senza regole verso una con regole, in tal caso si perde la regola)
• Cerchia dati non validi e Rimuovi tutti i cerchi presenti nell’icona Convalida dati cerchiano o meno con ovali rossi le celle che non rispettano le regole di convalida
• Messaggi di input anche se non crei alcun controllo, è possibile usare i messaggi di input della Convalida dei dati per creare messaggi sulla cella quando questa viene selezionata, quasi come se avessi inserito un commento

Esempi regole di convalida Excel

• Inserire solo date anteriori a quella odierna
  Consenti: Data
  Dati: minore o uguale a
  Origine: =OGGI()
• Inserire solo una data entro 30 giorni da oggi
  Consenti: Personalizzato
  Origine: =E(RiferimentoRelativo>OGGI();RiferimentoRelativo<=(OGGI()+Valore))
  Ad esempio =E(C1>OGGI();C1<=(OGGI()+30))
• Creare un elenco a discesa con M e F
  Consenti: Elenco
  Origine: M;F
• Inserire solo numeri
  Consenti: Personalizzato
  Origine: =VAL.NUMERO(RiferimentoRelativo)
  Ad esempio =VAL.NUMERO(A2)
• Inserire solo stringhe
  Consenti: Personalizzato
  Origine: =VAL.TESTO(RiferimentoRelativo)
  Ad esempio =VAL.TESTO(A2)
• Inserire solo valori maiuscoli
  Consenti: Personalizzato
  Origine: =IDENTICO(RiferimentoRelativo;MAIUSC(RiferimentoRelativo))
  Ad esempio =IDENTICO(F1;MAIUSC(F1))
• Inserire solo giorni feriali
  Consenti: Personalizzato
  Origine: =GIORNO.SETTIMANA(RiferimentoRelativo;2)<=5
  Ad esempio =GIORNO.SETTIMANA(A1;2)<=5
  il 2 specifica che la settimana inizia di lunedì (numero seriale 1) e termina di domenica (numero seriale 7)
  In questo esempio, scrivendo nelle celle interessate una data del tipo 31/12/2022, limiteremo l’inserimento ai giorni che vanno dal lunedì al venerdì (<=5 esclude sabato e domenica)
• Inserire una parola di 5 caratteri che inizia con A
  Consenti: Personalizzato
  Origine: =E(SINISTRA(RiferimentoRelativo)="A";LUNGHEZZA(RiferimentoRelativo)=5)
  Ad esempio =E(SINISTRA(B1)="A";LUNGHEZZA(B1)=5)
• Inserire una stringa specifica, ad esempio una @ Consenti: Personalizzato
  Origine: =VAL.NUMERO(TROVA("@";RiferimentoRelativo))
  Ad esempio =VAL.NUMERO(TROVA("@";D1))
  La funzione TROVA è sensibile alle maiuscole e alle minuscole
• Inserire solo Maschi o Femmina
  Consenti: Personalizzato
  Origine: =O(RiferimentoRelativo=”SI”;RiferimentoRelativo=”NO”)
  Ad esempio =O(E1="Maschio";E1="Femmina")
• Inserire solo stringhe che iniziano con A
  Consenti: Personalizzato
  Origine: =SINISTRA(RiferimentoRelativo;1)=”A”
  Ad esempio =SINISTRA(F1;1)=”A”
• Inserire valori univoci
  Consenti: Personalizzato
  Origine: =CONTA.SE(RiferimentoMisto:RiferimentoRelativo;RiferimentoRelativo)=1
  Ad esempio =CONTA.SE(D$5:D9;D5)=1
• Inserire totali non superiori al valore immesso in una cella
  Consenti: Personalizzato
  Origine: =SOMMA(RiferimentoMisto:RiferimentoRelativo)<=RiferimentoMisto
  Ad esempio =SOMMA(A$1:A5)<=H$1
• Inserire valori compresi tra due intervalli
  Consenti: Personalizzato
  Origine: =E(RiferimentoRelativo>= Valore;RiferimentoRelativo<=Valore)
  Ad esempio =E(A1>=10;A1<=100)

Aggirare le regole di convalida Excel

Questo è il tallone d’Achille della Convalida dati in Excel: purtroppo è infatti possibile inserire un valore che non dovrebbe essere invece accettato dal controllo imposto dalla convalida. Come?
Attraverso il Copia e Incolla o il Trascinamento.
In questo caso Excel, dalla scheda Dati, offre il comando Cerchia dati che evidenzia con un ovale rosso i dati non validi secondo i controlli impostati. Il cerchio scompare alla correzione del dato, o cliccando Rimuovi i cerchi presente nello stesso menu.

Per risolvere il problema del Copia e Incolla basta un piccolo codice VBA da inserire nel foglio dov'è presente la convalida dati. Immaginando che essa sia impostata sul range A1:A5, basterà scrivere:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
Application.CutCopyMode = False
End If
End Sub

Se si desidera bloccare il copia incolla su tutto il foglio:
Private Sub Workbook_Activate()
Application.CutCopyMode = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
End Sub

Private Sub Workbook_Deactivate()
Application.CellDragAndDrop = True
Application.OnKey "^c"
Application.CutCopyMode = False
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Application.CutCopyMode = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Application.CellDragAndDrop = True
Application.OnKey "^c"
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Application.CutCopyMode = False
End Sub

Per disabilitare il trascinamento inserire il seguente codice sempre nel foglio dov'è presente la convalida dati:
Private Sub Worksheet_Activate()
Application.CellDragAndDrop = False
End Sub
Per riattivarlo basta cambiare False con True.
Se, dopo aver bloccato il trascinamento con il VBA, non riuscite più ad attivarlo anche in altri file, è sufficiente una semplice procedura.
Per attivare (o disattivare) il trascinamento in Excel:

1. Cliccare la scheda File
2. Cliccare la voce Opzioni
3. Nella categoria Impostazioni Avanzate, in Opzioni di modifica, selezionare (o deselezionare) la casella di controllo Attiva quadratino di riempimento e trascinamento celle.

Rimuovere la convalida dati

Per rimuovere le regole di convalida dati:

1. seleziona l’intervallo con i controlli da eliminare
2. clicca l’icona Convalida dati
3. clicca il Cancella tutto

Se convalida dei dati Excel ti è piaciuto, condividilo!

Per saperne di più su Microsoft Excel potrebbero interessarti i video corsi Excel o questi libri: