Finora in questa serie di convalida dei dati, abbiamo imparato a creare un normale elenco a discesa e un elenco a discesa dinamico utilizzando diverse tecniche con la convalida dei dati in Excel.
E, oggi, in questo capitolo, ti mostreremo come creare un elenco a discesa dipendente in Microsoft Excel, utilizzando metodi diversi.
L'elenco a discesa dipendente è anche noto come convalida dei dati a cascata e limita le scelte in un elenco a discesa, a seconda del valore selezionato nell'altra cella che contiene la convalida dei dati. In altre parole, dipende dal valore selezionato nel primo elenco a discesa che determina i valori da visualizzare nel secondo elenco a discesa.
Questo è uno scenario molto comune in cui si lavora con dati di grandi dimensioni o alcuni report dinamici, in cui si desidera avere 2ns la cella visualizza un elenco che dipende dall'elemento dell'elenco scelto nel primo menu a discesa.
Come sappiamo in Excel, ci sono molti modi per svolgere un'attività particolare e, allo stesso modo, ci sono molti modi per creare la convalida dei dati dipendenti in Excel. E, oggi, dimostreremo 5 diverse tecniche per creare un elenco di convalida dei dati dipendenti.
I dati grezzi possono essere in qualsiasi ordine o formato e ogni volta non puoi modificare i dati o il formato per ottenere ciò che stai cercando.
Quindi, abbiamo preso un set di dati ma in 3 formati diversi per ottenere l'elenco a discesa dipendente. E, come puoi vedere, i nostri dati si trovano sul lato sinistro, ovvero dalla colonna A alla colonna E, e avremo il nostro output previsto sul lato destro che si trova nella colonna J e K. La colonna J avrà la convalida primaria list mentre la colonna K sarà dipendente e visualizzerà i valori in base al valore selezionato nella colonna J.
1ns Esempio:-
2ns Esempio:-
3rd Esempio:-
1ns Esempio:-
Abbiamo un elenco di prodotti per ogni codice prodotto dalla colonna A8 a E13. E, vogliamo selezionare il codice del prodotto in J10, quindi, a seconda del codice del prodotto selezionato, un nome del prodotto nella cella K10.
Primo metodo:-
Il primo metodo è molto semplice e breve e richiede solo 3 passaggi per ottenere l'elenco a discesa dipendente. Tuttavia, funziona correttamente solo finché non apporti modifiche al tuo intervallo. Una volta modificati i dati, sarà necessario modificare prima l'intervallo denominato per ottenere la convalida dei dati a cascata aggiornati.
Segui i passaggi indicati di seguito:-
- Seleziona l'intera tabella da A8 a E13
- Quindi vai alla scheda "Formule", quindi nella categoria "Nomi definiti", fai clic su "Crea dalla selezione"
- Puoi anche usare la scorciatoia da tastiera CTRL+MAIUSC+F3
- Apparirà la finestra di dialogo Crea nomi da selezioni
- Chiede di confermare quali righe e colonne utilizzare per creare i nomi per altre righe e colonne. Confermiamo di utilizzare la "riga superiore" per creare i nomi e deselezionare il 2ns opzione e poi clicchiamo su OK
Nota: - Gli spazi e altri caratteri speciali eccetto il carattere di sottolineatura e il punto non sono consentiti come nomi. Per impostazione predefinita, verrà convertito in carattere di sottolineatura. Quindi, usa il carattere di sottolineatura e il punto per separare le parole. Inoltre, la prima lettera non può essere un numero; deve essere una lettera, un carattere di sottolineatura o una barra rovesciata.
- Ora, per confermare che ogni intervallo ha un nome, andiamo su "Name Manager" (premi CTRL + F3)
- Lì possiamo vedere tutti e 5 gli intervalli denominati disponibili
- E possiamo anche vedere che ogni nome di intervallo ha un carattere di sottolineatura anziché uno spazio vuoto nel mezzo della stringa
Ora creeremo un elenco a discesa:-
- Seleziona la cella J10 e premi ALT++ D+L per aprire la finestra di dialogo Convalida dati
- Seleziona Elenco> quindi inserisci l'intervallo A8: E8 nella scheda Sorgente
- Fare clic su OK
- Ora creeremo un elenco dipendente nella cella K10
- Aprire la finestra di dialogo Convalida dati premendo il tasto ALT+D+L
- Seleziona Elenco, in origine inserisci questa funzione:- =INDIRETTO(SOSTITUTO($J$10," ","_"))
Nella convalida dei dati, per creare l'elenco dipendente, abbiamo utilizzato la funzione INDIRETTO per restituire il valore in base all'elenco di convalida dei dati primari. E, per sostituire il carattere di sottolineatura con lo spazio, utilizzeremo la funzione SOSTITUTO all'interno della funzione INDIRETTO.
- Fare clic su OK
Quando selezioniamo qualsiasi codice prodotto nella cella J10, l'elenco dei prodotti del codice prodotto selezionato apparirà nella cella K10. Ad esempio: - Abbiamo selezionato ETV 501, ora puoi vedere che l'elenco dei prodotti dipendenti viene visualizzato nella cella K10
Nota: - Ogni volta che aggiungerai il nome del prodotto e il codice del prodotto che non compariranno nell'elenco.
Per esempio: - Abbiamo aggiunto il prodotto 26 sotto il codice del prodotto ETV 505, ma quando selezioniamo il prodotto ETV 505 il prodotto aggiunto non viene visualizzato nell'elenco a discesa.
Quindi, è così che puoi creare un elenco a discesa dipendente usando una tecnica semplice in soli 3 semplici passaggi.
2ns Esempio:-
In questo esempio, vedremo come ottenere un elenco a discesa dipendente quando hai i tuoi dati come mostrato in questa tabella verticale.
Useremo due metodi diversi per creare un elenco a discesa dipendente. Entrambe sono tecniche quasi simili. Tuttavia, uno è senza l'intervallo denominato e l'altro avrà l'intervallo denominato.
1ns Metodo:-
Per fare lo stesso, utilizzeremo le funzioni OFFSET, MATCH e COUNTIF insieme.
Poiché sappiamo che la funzione OFFSET viene utilizzata per creare l'intervallo dinamico, quindi, per creare l'elenco "Convalida dati dinamici", utilizziamo la funzione OFFSET per restituire l'intervallo dinamico.
CONFRONTA viene utilizzato per restituire la posizione relativa di un elemento in un elenco in Excel. E qui ci aiuterà ad abbinare la categoria selezionata nell'elenco a discesa principale nel nostro intervallo sul foglio e restituirà un numero.
E CONTA.SE viene utilizzato per ottenere il conteggio delle celle che corrispondono ai criteri. E qui lo useremo per contare il numero di righe da mostrare usando la funzione CONTA.SE.
Segui i passaggi indicati di seguito:-
- Seleziona la cella J21, in cui creeremo il nostro elenco di convalida dei dati primari
- Premi il tasto ALT+D+L per aprire la finestra di dialogo Convalida dati
- Seleziona l'elenco dalla categoria Consenti
- Fare clic nella scheda Sorgente e selezionare l'intervallo da B20: B24
- E fai clic su OK
- Vai alla cella K21 e apri nuovamente la finestra di dialogo di convalida dei dati
- Quindi selezioniamo List e, in source, inseriamo sotto la funzione:
- =OFFSET($E$19,CONFRONTA($J$21,$D$20:$D$32,0),0,CONTA.SE($D$20:$D$32,$J$21))
- Fare clic su OK
- Nella cella K21, possiamo vedere tutti i valori corrispondenti del codice prodotto selezionato:-
Quindi, è così che puoi ottenere l'elenco dipendente prendendo i riferimenti di cella nella funzione.
2ns Metodo:-
Nel prossimo metodo, useremo l'intervallo denominato nella stessa funzione per ottenere la convalida dei dati a cascata. Per prima cosa dobbiamo creare l'elenco dinamico per il codice prodotto. In caso di nuovi prodotti aggiunti ai dati, il menu a discesa dovrebbe essere aggiornato per visualizzare lo stesso.
Per fare lo stesso, segui i passaggi indicati di seguito:-
- Seleziona B19, quindi premi CTRL + F3 per aprire la finestra "Gestione nomi"
- Ora, facciamo clic su "Nuovo" e viene visualizzata la finestra di dialogo "Definisci nome"
- Possiamo vedere, il nome appare già nella casella del nome, perché abbiamo selezionato B9 prima di aprire la finestra "Gestione nomi". E, poiché B19 contiene del testo, se lo desideriamo, possiamo cambiarlo con un altro nome.
- Inserisci sotto la formula di menzione: -
=OFFSET('ListaDropDownDipendente'!$B$20,0,0,COUNTA('ListaDropDownDipendente'!$B$20:$B$32))
- Fare clic su OK
Poiché abbiamo creato un elenco dinamico per prodotti univoci, ora creeremo un intervallo dinamico per l'intervallo di codici prodotto che si trova nella colonna D.
Segui gli stessi passaggi che abbiamo seguito per il prodotto unico: -
- Seleziona la cella D19, apri la finestra di dialogo Definisci nome
- Scoprirai che il nome è già lì
- In riferimento, inserisci la seguente formula: -
=OFFSET('Elenco a discesa dipendente'!$D$20,0,0,COUNTA('Elenco a discesa dipendente'!$D$20:$D$35))
- Fare clic su OK
- Ora entrambe le gamme dinamiche sono pronte. Quindi, andiamo su J22 e premiamo "ALT + D + L" e selezioniamo "Elenco"
- In sorgente, avremo l'intervallo denominato che abbiamo definito per "Codice prodotto unico", quindi premiamo F3 per vedere tutti gli intervalli denominati disponibili
- Possiamo vedere l'intervallo denominato "Codice prodotto unico", quindi facciamo clic su di esso, quindi facciamo clic su OK e premiamo invio
- Nel momento in cui premiamo invio, otteniamo la freccia a discesa nella cella J22, che contiene l'elenco dei codici prodotto univoci
- Seleziona la cella K22 e apri la finestra di dialogo "Convalida dati"
- Useremo la stessa funzione che abbiamo usato nell'ultimo metodo ma con range denominato
- Seleziona l'elenco, quindi in origine, inserisci la formula seguente: -
=OFFSET($E$19,CONFRONTA($J$22,Codice_Prodotto,0),0,CONTA.SE(Codice_Prodotto,J22))
- Fare clic su OK
- Ora abbiamo il menu a discesa principale e l'elenco dei prodotti secondari
- Seleziona il prodotto "ETV-101" da J22 e in K22 possiamo vedere solo i nomi che rientrano in questo prodotto "ETV-101". E, quando cambiamo qualsiasi prodotto ("ETV-103) in J22, K22 visualizza i valori corrispondenti per quel codice
Ora vedremo cosa succede quando aggiungiamo un nuovo codice prodotto alla lista? Questi elenchi a discesa verranno aggiornati?
Aggiungiamo nuovo prodotto nell'elenco; Segui i passaggi indicati di seguito: -
- Aggiungi il codice prodotto nell'elenco di Unique_Prod_Code
- Aggiungi anche il codice_prodotto e il nome_prodotto nei dati:-
- Ora controlla l'elenco a discesa: il codice e il nome del prodotto aggiunti vengono visualizzati
3rd Esempio:-
Abbiamo le intestazioni dinamiche direttamente dalla tabella, e aggiungeremo nuovi prodotti alla gamma. La tabella è nello stesso formato che abbiamo usato per 1ns metodo.
4ns Metodo:-
Segui i passaggi indicati di seguito:-
- Seleziona l'intestazione A40:E40
- Crea prima l'intervallo dinamico per le intestazioni, apri la finestra di dialogo "Definisci nome"
- Scrivi "Titolo" nel luogo del nome, e poi in "si riferisce a", inserisci la formula sotto indicata: -
- Inserisci la seguente funzione: -
- =OFFSET('Elenco a discesa dipendente'!$A$40,,,,COUNTA('Elenco a discesa dipendente'!$40:$40))
- Fare clic su OK
- La gamma dinamica "Rotta" è pronta ora
E ora creeremo l'intervallo denominato per ogni intestazione, segui i passaggi indicati di seguito: -
- Seleziona la tabella da A40 a E50
- CTRL + MAIUSC + scorciatoia da tastiera F3
- Deselezionamo il 2ns opzione
- E, prima di fare clic su OK, assicurati che 1ns l'opzione "Riga superiore" è selezionata
- Ora siamo pronti con entrambe le gamme
Ora, prepareremo l'elenco a discesa dei genitori
- Seleziona la cella J42
- Apri la finestra di dialogo Convalida dati
- Quindi, dopo aver selezionato "Elenco", premiamo F3 in sorgente per ottenere l'intervallo denominato per le intestazioni. Facciamo clic su "Intestazione", quindi facciamo clic su OK e premiamo invio. Abbiamo l'elenco dei genitori in J42 ora
- Per creare la lista di Dettaglio Articolo, seleziona la cellaK42
- Aprire la finestra di dialogo Convalida dati premendo il tasto ALT+D+L
- Seleziona Elenco, quindi inserisci la funzione di seguito nella scheda Origine: -
- =OFFSET(INDIRETTO(SOSTITUTO($J$42," ","_")),,,CONTA.VALORI(INDIRETTO(SOSTITUTO($J$42," ","_")))))
- Fare clic su OK
Ora seleziona qualche elemento nel J42, diciamo che selezioniamo "Articolo 01" e guarda l'elenco a discesa K42. E, come i precedenti 3 metodi, anche qui abbiamo un elenco dipendente.
Allora che c'è di nuovo? Nel primo esempio, non è stato possibile aggiungere alcun prodotto all'elenco, ma qui è possibile aggiungere qualsiasi nuovo prodotto. Quindi, diciamo che aggiungiamo un nuovo prodotto a questo articolo. Andiamo su A45 e digitiamo "ETV-501 Prod 05" e poi torniamo a K42 ed ecco qui. Puoi vedere, il nuovo prodotto è stato aggiunto.
- Ora aggiungi alcuni prodotti sotto il nuovo articolo
Quando selezioniamo "Articolo 06", andiamo a K42 e facciamo clic sull'elenco a discesa. Sorprendentemente, non accade nulla quando si fa clic sulla freccia a discesa. Questo perché abbiamo creato tutto ciò che è dinamico e ci siamo dimenticati di creare un intervallo dinamico per la tabella, motivo per cui i prodotti non vengono visualizzati nell'elenco figlio.
Per farlo, dobbiamo utilizzare tecniche diverse. Ci sono due metodi per farlo. Puoi creare la tabella o semplicemente utilizzare solo la funzione OFFSET. E, nel prossimo metodo, useremo la funzione OFFSET e vedremo il trucco per espandere anche l'intervallo della tabella.
- Quindi, prima andiamo su J43 e premiamo "ALT + D + L"
- Selezioniamo “Elenco” e, quindi in sorgente, premiamo F3 e selezioniamo “Titolo” e clicchiamo su OK e poi premiamo invio
- Ora andiamo su K43 e, dopo aver selezionato "Elenco", andiamo su "Sorgente" e inseriamo la funzione indicata di seguito
=OFFSET($A$40,1,CONFRONTA($J$43,$40:$40,0)-1,CONTA.VALORI($A$40,1,CONFRONTA($J$43,$40:$40,0)-1,1000 ,1)))
- Fare clic su OK
Ora torniamo indietro e selezioniamo "Elemento 06 nella cella J43 e torniamo a K43 e facciamo clic sulla freccia a discesa. Ma questa lista temporale mostra i prodotti che abbiamo aggiunto per un nuovo articolo. E selezioniamo il primo prodotto "ETV-506 Prod 01".
In questo modo è possibile creare l'elenco a discesa dipendente utilizzando metodi diversi su qualsiasi tipo di formato di dati.
Video: come creare un elenco a discesa dipendente (a cascata) in Excel utilizzando 5 diverse tecniche in Microsoft Excel
Fare clic sul collegamento del video per un rapido riferimento all'uso di esso. Iscriviti al nostro nuovo canale e continua ad imparare con noi!
Se ti è piaciuto il nostro blog, condividilo con i tuoi amici su Facebook. E puoi anche seguirci su Twitter e Facebook.
Ci piacerebbe avere tue notizie, facci sapere come possiamo migliorare, completare o innovare il nostro lavoro e renderlo migliore per te. Scrivici al sito di posta elettronica