Guarda il video - Creazione di un elenco a discesa dipendente in Excel
Un elenco a discesa di Excel è una funzionalità utile quando si creano moduli di immissione dati o dashboard di Excel.
Mostra un elenco di elementi come un menu a discesa in una cella e l'utente può effettuare una selezione dal menu a discesa. Questo potrebbe essere utile quando hai un elenco di nomi, prodotti o regioni che devi inserire spesso in un insieme di celle.
Di seguito è riportato un esempio di un elenco a discesa di Excel:
Nell'esempio sopra, ho utilizzato gli elementi in A2: A6 per creare un menu a discesa in C3.
Leggi: Ecco una guida dettagliata su come creare un elenco a discesa di Excel.
A volte, tuttavia, potresti voler utilizzare più di un elenco a discesa in Excel in modo tale che gli elementi disponibili in un secondo elenco a discesa dipendano dalla selezione effettuata nel primo elenco a discesa.
Questi sono chiamati elenchi a discesa dipendenti in Excel.
Di seguito è riportato un esempio di cosa intendo per elenco a discesa dipendente in Excel:
Puoi vedere che le opzioni nel menu a discesa 2 dipendono dalla selezione effettuata nel menu a discesa 1. Se seleziono "Frutta" nel menu a discesa 1, mi vengono mostrati i nomi dei frutti, ma se seleziono Verdura nel menu a discesa 1, allora vengono mostrati i nomi delle verdure in Drop Down 2.
Questo è chiamato un elenco a discesa condizionale o dipendente in Excel.
Creazione di un elenco a discesa dipendente in Excel
Ecco i passaggi per creare un elenco a discesa dipendente in Excel:
- Seleziona la cella in cui desideri visualizzare il primo elenco a discesa (principale).
- Vai su Dati -> Convalida dati. Questo aprirà la finestra di dialogo di convalida dei dati.
- Nella finestra di dialogo di convalida dei dati, all'interno della scheda delle impostazioni, selezionare Elenco.
- Nel campo Origine, specificare l'intervallo che contiene gli elementi che devono essere visualizzati nel primo elenco a discesa.
- Fare clic su OK. Questo creerà il menu a discesa 1.
- Selezionare l'intero set di dati (A1:B6 in questo esempio).
- Vai su Formule -> Nomi definiti -> Crea da selezione (oppure puoi usare la scorciatoia da tastiera Ctrl + Maiusc + F3).
- Nella finestra di dialogo "Crea nome dalla selezione", seleziona l'opzione Riga superiore e deseleziona tutte le altre. In questo modo vengono creati 2 intervalli di nomi ("Frutta" e "Verdura"). L'intervallo denominato Frutta si riferisce a tutti i frutti nell'elenco e L'intervallo denominato Verdura si riferisce a tutte le verdure nell'elenco.
- Fare clic su OK.
- Selezionare la cella in cui si desidera l'elenco a discesa Dipendente/Condizionale (E3 in questo esempio).
- Vai su Dati -> Convalida dati.
- Nella finestra di dialogo Convalida dati, all'interno della scheda Impostazioni, assicurati che Elenco sia selezionato.
- Nel campo Origine, inserisci la formula =INDIRETTO(D3). Qui, D3 è la cella che contiene il menu a discesa principale.
- Fare clic su OK.
Ora, quando effettui la selezione nel menu a discesa 1, le opzioni elencate nell'elenco a discesa 2 si aggiorneranno automaticamente.
Scarica il file di esempio
Come funziona? - L'elenco a discesa condizionale (nella cella E3) fa riferimento a =INDIRECT(D3). Ciò significa che quando selezioni "Frutti" nella cella D3, l'elenco a discesa in E3 fa riferimento all'intervallo denominato "Frutti" (tramite la funzione INDIRETTO) e quindi elenca tutti gli elementi in quella categoria.
Nota importante: Se la categoria principale è più di una parola (ad esempio, 'Frutta di stagione' invece di 'Frutta'), è necessario utilizzare la formula =INDIRETTO(SOSTITUTO(D3,” “,”_”)), invece della semplice funzione INDIRETTA mostrata sopra.
- Il motivo è che Excel non consente spazi negli intervalli denominati. Pertanto, quando crei un intervallo denominato utilizzando più di una parola, Excel inserisce automaticamente un carattere di sottolineatura tra le parole. Ad esempio, quando crei un intervallo denominato con "Frutti di stagione", verrà chiamato Season_Fruits nel back-end. L'utilizzo della funzione SOSTITUISCI all'interno della funzione INDIRETTO assicura che gli spazi sono convertito in caratteri di sottolineatura.
Ripristina/cancella automaticamente il contenuto dell'elenco a discesa dipendente
Dopo aver effettuato la selezione e quindi modificato il menu a discesa padre, l'elenco a discesa dipendente non cambierebbe e, pertanto, sarebbe una voce errata.
Ad esempio, se si seleziona "Frutta" come categoria e quindi si seleziona Apple come elemento, quindi si torna indietro e si modifica la categoria in "Verdura", il menu a discesa dipendente continuerà a mostrare Apple come elemento.
È possibile utilizzare VBA per assicurarsi che il contenuto dell'elenco a discesa dipendente venga ripristinato ogni volta che viene modificato l'elenco a discesa principale.
Ecco il codice VBA per cancellare il contenuto di un elenco a discesa dipendente:
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Target.Column = 4 Then If Target.Validation.Type = 3 Then Application.EnableEvents = False Target.Offset(0, 1).ClearContents End If End If exitHandler: Application.EnableEvents = True Exit Sub End Sub
Il merito di questo codice va a questo tutorial di Debra sulla cancellazione degli elenchi a discesa dipendenti in Excel quando la selezione viene modificata.
Ecco come far funzionare questo codice:
- Copia il codice VBA.
- Nella cartella di lavoro di Excel in cui è presente l'elenco a discesa dipendente, vai alla scheda Sviluppatore e, all'interno del gruppo "Codice", fai clic su Visual Basic (puoi anche utilizzare la scorciatoia da tastiera - ALT + F11).
- Nella finestra dell'editor VB, a sinistra nell'esploratore del progetto, vedresti tutti i nomi dei fogli di lavoro. Fare doppio clic su quello che ha l'elenco a discesa.
- Incolla il codice nella finestra del codice a destra.
- Chiudi l'editor VB.
Ora, ogni volta che si modifica l'elenco a discesa principale, il codice VBA verrebbe attivato e cancellerebbe il contenuto dell'elenco a discesa dipendente (come mostrato di seguito).
Se non sei un fan di VBA, puoi anche utilizzare un semplice trucco di formattazione condizionale che evidenzierà la cella ogni volta che c'è una mancata corrispondenza. Questo può aiutarti a vedere e correggere visivamente la mancata corrispondenza (come mostrato di seguito).
Ecco i passaggi per evidenziare le discrepanze negli elenchi a discesa dipendenti:
- Seleziona la cella con gli elenchi a discesa dipendenti.
- Vai a Home -> Formattazione condizionale -> Nuova regola.
- Nella finestra di dialogo Nuova regola di formattazione, seleziona "Utilizza una formula per determinare quali celle formattare".
- Nel campo della formula, inserisci la seguente formula: =VAL.ERRORE(CERCA.VERT(E3,INDICE($A$2:$B$6,,CONFRONTA(D3,$A$1:$B$1)),1,0))
- Imposta il formato.
- Fare clic su OK.
La formula utilizza la funzione CERCA.VERT per verificare se l'elemento nell'elenco a discesa dipendente è quello della categoria principale o meno. In caso contrario, la formula restituisce un errore. Viene utilizzato dalla funzione ISERROR per restituire TRUE che indica alla formattazione condizionale di evidenziare la cella.
Potrebbero piacerti anche i seguenti tutorial di Excel:
- Estrai dati in base a una selezione di elenchi a discesa.
- Creazione di un elenco a discesa con suggerimenti di ricerca.
- Seleziona più elementi da un elenco a discesa.
- Crea più elenchi a discesa senza ripetizione.
- Risparmia tempo con i moduli di immissione dati in Excel.
