Come applicare più convalide dei dati a una singola cella in un foglio Excel?
In un foglio Excel è abbastanza comune applicare una singola regola di convalida dati a una cella. Ciò aiuta a mantenere coerenza e accuratezza dei dati. Tuttavia, esistono scenari in cui potrebbe essere necessario applicare diversi criteri di convalida nella stessa cella, ad esempio consentendo sia un numero valido sia un valore proveniente da un elenco specifico, oppure combinando requisiti specifici sul testo con valori ammessi Intervallo di date. Gestire questi requisiti più complessi di convalida dati in Excel consente di controllare meglio i processi di inserimento dati, prevenire errori e migliorare la qualità dei dati.
Di seguito, questo articolo presenta diversi esempi pratici per applicare più regole di convalida dati a una singola cella di Excel. Ogni esempio affronta uno scenario specifico, permettendoti di scegliere l’approccio più adatto alle tue esigenze. Vengono inoltre illustrati metodi alternativi, come l’utilizzo di VBA, per gestire casi che richiedono maggiore flessibilità o logiche avanzate.
Applicare più convalide dei dati in una cella (Esempio 1)
Applicare più convalide dei dati in una cella (Esempio 2)
Applicare più convalide dei dati in una cella (Esempio 3)
Applicare più convalide dei dati tramite VBA (Avanzato)
Applicare più convalide dei dati in una cella (Esempio 1)
Immagina di voler configurare una cella in modo che accetti solo valori che soddisfino uno dei due criteri seguenti:
- Se il valore inserito è un numero, deve essere inferiore a 100.
- Se si tratta di testo, deve essere presente in un elenco specifico (ad esempio, l’intervallo D2:D7).
Questa situazione si verifica spesso quando è necessario raccogliere nello stesso campo sia codici quantitativi sia risposte categoriali predefinite. Combinando regole di convalida, si evita di dover utilizzare campi separati per numeri e testo, migliorando chiarezza ed efficienza.
1. Seleziona la cella o l’intervallo a cui vuoi applicare più criteri di convalida dati. Quindi, nella scheda Dati, fai clic su Convalida dati > Convalida dati dalla Barra multifunzione, come mostrato di seguito:
2. Nella finestra di dialogo Convalida dati, passare alla scheda Impostazioni e configurare come segue:
- (1.) Dal menu a discesa Consenti, seleziona Personalizzato.
- (2.) Nel campo Formula, immettere la seguente formula:=OR(A2<$C$2,COUNTIF($D$2:$D$7,A2)=1)
Nota: In questa formula, A2 è l’indirizzo della cella da convalidare, C2 contiene il valore massimo consentito e D2:D7 elenca le voci di testo ammesse. Aggiorna questi riferimenti in base al tuo foglio di lavoro.
3. Fare clic su OK per applicare. Ora le celle selezionate accetteranno solo valori che siano numeri inferiori a 100 o stringhe di testo presenti nell’intervallo D2:D7. Se un utente tenta di immettere un valore che non soddisfa nessuna delle due condizioni, Excel visualizzerà immediatamente un avviso per segnalare l’inserimento non valido.
Questo metodo è perfetto per scenari semplici con regole dai confini ben definiti. Tuttavia, quando i requisiti diventano più articolati o interattivi — come nel caso di suggerimenti condizionali o logiche multistep — la convalida basata su formule potrebbe rivelarsi limitata. In questi casi, il metodo VBA illustrato di seguito offre una flessibilità decisamente superiore.
Applicare più convalide dei dati in una cella (Esempio 2)
In questo scenario, si potrebbe voler consentire l’inserimento di dati solo se soddisfano una delle due condizioni seguenti:
- Il valore immesso è esattamente il testo «Kutools per Excel»
- Il valore immesso è una data compresa tra 12/1/2017 e 12/31/2017
Questo tipo di convalida multipla è ideale quando un modulo o un questionario richiede o un codice di conferma (una stringa di testo esatta) o una data compresa nell’intervallo del progetto.
1. Apri la finestra di dialogo Convalida dati per la/le cella/e di destinazione. Nella finestra di dialogo, segui questi passaggi:
- (1.) Passa alla scheda Impostazioni.
- (2.) Scegliere Personalizzato dal menu a discesa Consenti elenco a discesa.
- (3.) Digitare questa formula nell'area Formula:=OR(A2=$C$2,AND(A2>=DATE(2017,12,1), A2<=DATE(2017,12,31)))
Nota: Qui, A2 indica la cella da convalidare, C2deve contenere il testo «Kutools per Excel» e l’intervallo di date è definito mediante DATA(2017,12,1) e DATA(2017,12,31). Adatta i riferimenti in base alla configurazione del tuo foglio.
2. Conferma facendo clic su OK. La o le celle accetteranno ora solo il testo specificato o una data compresa nell’intervallo definito. Qualsiasi altro tipo di input o testo al di fuori di tali limiti verrà bloccato, con un feedback immediato come mostrato qui:
Questo approccio funziona bene in scenari con requisiti di input rigorosi, dove sono accettabili solo corrispondenze esatte o date fisse. Tuttavia, se la convalida comporta dipendenze complesse, calcoli o interazioni con l’utente, si consiglia di valutare una soluzione basata su VBA per un controllo più accurato.
Applicare più convalide dei dati in una cella (Esempio 3)
Per il terzo esempio, consideriamo una situazione in cui la cella deve accettare solo voci con un testo iniziale specifico e un numero preciso di caratteri:
- La cella deve iniziare con «KTE» ed essere esattamente di 6 caratteri
- Oppure iniziare con «www» ed essere esattamente di 10 caratteri
Questi criteri sono comunemente utilizzati per applicare standard di formattazione a codici o URL. Impostare controlli su lunghezza e prefisso riduce significativamente gli errori di inserimento.
Per gestire questo caso, impostare la convalida dati con la seguente formula:
1. Apri la finestra di dialogo Convalida dati. Nelle impostazioni, completa i seguenti passaggi:
- (1.) Scegli la scheda Impostazioni.
- (2.) Selezionare Personalizzato dal menu a discesa Consenti.
- (3.) Nel campo Formula, immettere:=OR(AND(LEFT(A2,3)=«KTE»,LEN(A2)=6),AND(LEFT(A2,3)="www",LEN(A2)=10))
Nota: Sostituire A2con il riferimento effettivo della cella, se necessario. È possibile personalizzare anche «KTE», «www» e il numero di caratteri in base al proprio contesto specifico.
2. Fare clic su OK. La cella ora accetta solo valori conformi alle regole di prefisso e lunghezza definite. L’inserimento di un valore che viola una qualsiasi delle due condizioni attiverà un errore di convalida, come mostrato:
Suggerimento: Se hai in mente altri tipi di convalida con criteri multipli, puoi creare formule personalizzate utilizzando le funzioni integrate di Excel per soddisfare le tue esigenze.
Un limite della convalida basata su formule è che può risultare difficile da gestire o modificare quando le regole si complicano o diventano interattive, ad esempio se si desidera mostrare messaggi di errore personalizzati o adattarsi a criteri in continua evoluzione. In questi casi, ricorrere a una soluzione VBA può aumentare notevolmente la flessibilità.
Applicare più convalide dei dati tramite VBA (Avanzato)
Nei casi in cui la convalida dati basata su formule non sia sufficiente—ad esempio quando serve una convalida che combina più fattori, l’applicazione di regole dinamiche legate ai valori di altre celle o la visualizzazione in tempo reale di messaggi personalizzati—è possibile ricorrere alle macro VBA (Visual Basic for Applications) per implementare regole di convalida avanzate o dinamiche direttamente all’interno di una cella.
Gli scenari tipici includono:
- Convalida dell'input basata su più di due condizioni simultanee
- Consentire l'interazione dell'utente, ad esempio messaggi pop-up con indicazioni dettagliate
- Ripristino automatico dei dati non validi e fornitura di istruzioni personalizzate
Di seguito è riportato un esempio di soluzione VBA in cui i dati inseriti in B2devono soddisfare uno dei seguenti criteri:
- Essere un numero intero compreso tra 1 e 50
- OPPURE essere una delle parole consentite nell’intervallo D2:D5
Nota: Puoi personalizzare i criteri, gli intervalli di destinazione o la logica di convalida nel codice in base alle tue esigenze.
1. Premere Alt+F11 per aprire l’editor di Visual Basic for Applications. Nell’editor VBA, fare doppio clic sul foglio di lavoro in cui si desidera aggiungere più convalide dati nel riquadro Progetto. Quindi, copiare la seguente macro nella finestra del codice relativa a tale foglio:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ValidList As Range
Dim InputValue As Variant
Dim IsValid As Boolean
Dim xTitleId As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
' Only validate B2 (you can set this to your desired cell or range)
If Not Intersect(Target, Range("B2")) Is Nothing Then
InputValue = Target.Value
Set ValidList = Range("D2:D5") ' Change as needed
IsValid = False
' Check for whole number between 1 and 50
If IsNumeric(InputValue) And InputValue = Int(InputValue) Then
If InputValue >= 1 And InputValue <= 50 Then
IsValid = True
End If
End If
' Check if input matches allowed list
If WorksheetFunction.CountIf(ValidList, InputValue) > 0 Then
IsValid = True
End If
If Not IsValid Then
MsgBox "Entry must be an integer between 1 and 50 OR one of the values listed in D2:D5.", vbExclamation, xTitleId
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
End If
End Sub2. Prova a inserire valori nella cella B2. Se digiti un numero intero compreso tra 1 e 50 oppure una parola presente nell’intervallo D2:D5, l’inserimento viene accettato. In caso contrario, appare un messaggio di avviso e il valore non valido viene rimosso all’istante. Puoi personalizzare la/e cella/e di destinazione e l’intervallo dei valori ammessi direttamente nel codice VBA in base alle tue esigenze.
Suggerimenti e risoluzione dei problemi:
- Salva sempre la cartella di lavoro prima di eseguire codice VBA: un errore nel codice potrebbe causare la perdita dei tuoi dati.
- Se il foglio di lavoro contiene più celle con convalida, puoi modificare il codice per convalidare qualsiasi intervallo, non solo B2.
- Se il codice non viene eseguito, assicurati che le macro siano abilitate e che il codice sia posizionato nel foglio di lavoro corretto.
- È possibile migliorare il codice per fornire messaggi personalizzati o registrare le voci non valide, in base alle proprie esigenze.
Le soluzioni basate su VBA offrono un’estrema flessibilità e sono ideali per esigenze avanzate di convalida. Tuttavia, tieni presente che le macro richiedono agli utenti di abilitare VBA nel proprio ambiente Excel e potrebbero non essere disponibili in tutte le configurazioni di sicurezza.
In sintesi, quando si gestiscono regole di convalida multiple per una cella in Excel, è possibile optare per metodi basati su formule nei casi più semplici oppure ricorrere a VBA per implementare flussi di convalida dati più dinamici e sofisticati. Valuta con attenzione l’approccio più adatto ai tuoi obiettivi, alla complessità dei dati e all’ambiente degli utenti per ottenere i migliori risultati.
Migliori Strumenti per la Produttività in Office
Potenzia le tue competenze in Excel con Kutools per Excel e sperimenta un’efficienza mai vista prima.Kutools per Excel offre oltre 300 funzionalità avanzate per aumentare la produttività e Risparmia tempo.Clicca qui per ottenere la funzionalità di cui hai più bisogno...
Office Tab Porta l'interfaccia a schede in Office e rende il tuo lavoro molto più semplice
- Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede all’interno della stessa finestra, invece che in finestre separate.
- Aumenta la tua produttività del 50 % e risparmia centinaia di clic del mouse ogni giorno!
Tutti i componenti aggiuntivi di Kutools in un unico programma di installazione.
Kutools for Office è la suite che include componenti aggiuntivi per Excel, Word, Outlook e PowerPoint, oltre a Office Tab Pro: la soluzione ideale per i team che lavorano su diverse app di Office.
- Suite completa— componenti aggiuntivi per Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Un unico programma di installazione, una sola licenza— configurazione in pochi minuti (pronto per MSI)
- Funziona meglio insieme— produttività ottimizzata tra le app di Office
- Prova gratuita di 30 giorni con tutte le funzionalità— nessuna registrazione, nessuna carta di credito
- Miglior rapporto qualità-prezzo— risparmia rispetto all’acquisto dei singoli componenti aggiuntivi