Con l’ultimo articolo abbiamo iniziato ad esplorare le funzionalità di convalida dei dati di Excel e imparato come creare un semplice menu a discesa sulla base di un elenco separato da virgole, di un intervallo di celle o di un intervallo denominato.
Oggi, ci accingiamo a studiare questa funzione in modo più approfondito e impareremo a creare elenchi a discesa concatenati che consentono di visualizzare le scelte a seconda del valore selezionato in un menu a discesa precedente.
Come creare un semplice menu a tendina concatenato in Excel
Creare dei semplici elenchi a discesa dipendenti in Excel è facile. Tutto ciò che vi serve è un paio di intervalli denominati e la funzione INDIRETTO. Questo metodo funziona con tutte le versioni di Excel: 2007, 2010, 2013 e 2016.
Inserite gli elementi dei menu a discesa
Prima di tutto, digitate le voci che desiderate visualizzare negli elenchi a discesa, ciascuna lista in una colonna separata. Ad esempio, sto creando un menu a discesa dei principali produttori di alcuni frutti: nella colonna A ho inserito l’elenco dei frutti e nelle colonne successive l’elenco dei vari paesi produttori, una colonna per frutto.
Create gli intervalli denominati
Ora è necessario creare i nomi per l’elenco principale e per ciascuna delle liste dipendenti. È possibile eseguire questa operazione con l’aggiunta di un nuovo nome nella finestra Gestione nomi (scheda FORMULE > Gestione nomi > Nuovo…) oppure digitando il nome direttamente nella Casella del nome.
Per istruzioni dettagliate passo-passo, vedete Creare un intervallo denominato.
Cose da ricordare:
- Gli elementi che compariranno nel primo elenco a discesa dovranno essere voci di una sola parola, ad esempio mele, ananas, arance. Se avete elementi composti da due, tre o più parole, vedete Come creare un menu a discesa a cascata con voci composte da più parole.
- I nomi delle liste dipendenti devono essere esattamente gli stessi della voce corrispondente nella lista principale. Ad esempio, l’elenco dipendente da visualizzare quando si seleziona “Mele” dal primo elenco a discesa dovrebbe essere denominato Mele
Una volta fatto, vi consiglio di premere Ctrl + F3 per aprire la finestra Gestione Nomi e verificare se tutte le liste hanno nomi e riferimenti corretti.
Creare il primo (principale) elenco a discesa
- Nello stesso foglio o in un altro, selezionate una o più celle in cui volete visualizzare l’elenco a discesa principale (supponiamo di scegliere A2)
- Andate alla scheda DATI, scegliete Convalida dati e create un elenco a discesa basato su un intervallo denominato, selezionando Elenco in Consenti e inserendo il nome dell’intervallo nella casella Origine
Per la procedura dettagliata, vedete Creare un elenco a discesa in base a un intervallo denominato.
Creare i menu a tendina dipendenti
Selezionate una cella dove inserire il menu a discesa dipendente e applicate di nuovo la Convalida dati di Excel come descritto nel passaggio precedente. Ma questa volta, al posto del nome di intervallo, immettete la seguente formula nel campo Origine:
=INDIRETTO(A2)
Dove A2 è la cella in cui abbiamo inserito il primo (principale) elenco a discesa.
Se la cella A2 è vuota, si ottiene il messaggio di errore “L’origine restituisce attualmente un errore. Continuare?”.
Fate clic su Sì, e non appena selezionate un elemento dal primo menu a discesa, potrete vedere le voci corrispondenti nel secondo menu a discesa.
Aggiungere un terzo elenco a discesa dipendente (opzionale)
Se necessario, è possibile aggiungere un 3° elenco a discesa che dipenda dalla selezione nel 2° menu a discesa o dalle selezioni nei primi due menù a discesa.
Impostare un 3° menu a discesa che dipenda dal 2° elenco
È possibile creare un elenco a discesa di questo tipo nello stesso modo in cui abbiamo appena fatto. Basta ricordare le 2 cose importanti di cui sopra, che sono essenziali per il corretto funzionamento dei vostri elenchi a discesa.
Per esempio, se volete visualizzare un elenco delle regioni in colonna C a seconda di quale paese venga selezionato nella colonna B, create l’elenco delle regioni per ciascun paese e denominatelo con il nome del paese, esattamente come appare nel secondo elenco a discesa. Per esempio, l’elenco delle regioni indiane deve essere denominato “India”, l’elenco delle regioni cinesi “Cina”, e così via. Dopo di che, selezionate una cella per il 3° menu a discesa (C2 nel nostro caso) e applicate i dati di convalida di Excel con la seguente formula (B2 è la cella con il secondo menu a discesa che contiene l’elenco dei paesi):
=INDIRETTO(B2)
Creare un terzo menu a discesa dipendente dai primi due elenchi
Se aveste la necessità di creare un menu a discesa che dipenda dalle selezioni effettuate sia nel primo che nel secondo elenco a discesa, occorre procedere in questo modo:
- Create degli ulteriori set di intervalli denominati e assegnate loro un nome composto dalla combinazione delle parole presenti nei primi due menù a discesa. Ad esempio, avete Mele, Kiwi, ecc nel 1° elenco e Cina, Italia, ecc nel 2°. Quindi create degli intervalli denominati MeleCina, MeleItalia, KiwiCina, KiwiItalia, ecc. Questi nomi non devono contenere sottolineature o altri caratteri aggiuntivi
- Applicate la Convalida dati di Excel con la formula INDIRETTO/SOSTITUISCI che concatena i nomi delle voci nelle prime due colonne, e rimuove gli spazi dai nomi. Ad esempio, nella cella C2, la formula di convalida dei dati potrebbe essere: =INDIRETTO(SOSTITUISCI(A2&B2; ” “; “”))
Dove A2 e B2 contengono rispettivamente il primo e il secondo menu a discesa. Come risultato, il 3 ° elenco a discesa visualizzerà le regioni corrispondenti alla frutta e al paese selezionati nei primi 2 elenchi a discesa.
Questo è il modo più semplice per creare un menu a discesa in Excel. Tuttavia, questo metodo ha diverse limitazioni.
Limiti di questo approccio:
- Le voci dell’elenco a discesa primario devono essere composte di una sola parola. Approfondite qui come creare elenchi a discesa con voci composte da più parole
- Questo metodo non funziona se le voci nell’elenco a discesa principale contengono caratteri non consentiti nei nomi di intervallo, come ad esempio il trattino (-), la e commerciale (&), ecc. La soluzione è quella di creare un menu a discesa dinamico che non abbia questa restrizione
- I menu a discesa creati in questo modo non vengono aggiornati automaticamente, cioè dovrete cambiare i riferimenti degli intervalli denominati ogni volta che aggiungerete o rimuoverete elementi dagli elenchi di origine. Per superare questa limitazione, provate a creare un elenco a discesa dinamico
Come creare menu a tendina con voci composte di più parole
Le formule INDIRETTO, come quella che abbiamo usato nel precedente esempio, sono in grado di gestire solo elementi di una sola parola. Ad esempio, la formula =INDIRETTO(A2) fa riferimento indirettamente alla cella A2 e visualizza l’intervallo denominato esattamente con lo stesso nome con il quale è presente nella cella di riferimento. Tuttavia, gli spazi non sono consentiti nei nomi di Excel, questo è il motivo per cui questa formula non funziona con nomi composti da più parole.
La soluzione è quella di utilizzare la funzione INDIRETTO in combinazione con SOSTITUISCI come abbiamo fatto durante la creazione del 3° menu a discesa.
Supponiamo di avere Fico d’India tra i prodotti. In questo caso, il nome dell’elenco dei produttori di fico d’India dovrà essere denominato con una sola parola senza spazi – FicodIndia.
Poi, per il secondo elenco a discesa, applicate la convalida dei dati di Excel con la seguente formula che rimuove gli spazi dal nome nella cella A2:
=INDIRETTO(SOSTITUISCI(A2; " ";""))
Come prevenire modifiche al menu a tendina principale
Immaginate il seguente scenario. Un utente ha fatto le selezioni in tutti gli elenchi a discesa, poi ha cambiato idea, è andato di nuovo al primo menu, e ha scelto un altro elemento. Come risultato, le selezioni del 1° e del 2° elenco non corrispondono. Per evitare che ciò accada, consigliamo di bloccare eventuali modifiche al primo elenco a discesa non appena viene effettuata una selezione nel secondo elenco.
Per fare questo, quando creiamo il primo elenco a discesa, utilizziamo una formula speciale che verifica se una voce viene selezionata nel secondo menu a tendina:
=SE(B2=""; Frutta; INDIRETTO("FintaLista"))
Dove B2 contiene il secondo menu a discesa, “Frutta” è il nome della lista che compare nel primo menu a discesa, e “FintaLista” è un qualsiasi nome falso che non esiste.
Ora, se un qualsiasi elemento viene selezionato nel 2° elenco a discesa, nessuna scelta sarà disponibile quando l’utente fa clic sulla freccia accanto al primo menu.
Creare elenchi a discesa dinamici dipendenti in Excel
Il vantaggio principale di un elenco a discesa dinamico dipendente è che sarete liberi di modificare gli elenchi di origine e i vostri menu a discesa saranno aggiornati in tempo reale. Naturalmente, la creazione di menu a discesa dinamici richiede un po’ più di tempo e formule più complesse, ma credo che questo sia un investimento meritevole perché una volta impostati, tali menu a discesa saranno veramente piacevoli da usare.
Come per quasi tutto in Excel, è possibile ottenere lo stesso risultato in diversi modi. In particolare, è possibile creare un menu a discesa dinamico utilizzando una combinazione delle funzioni SCARTO, INDIRETTO e CONTA.VALORI oppure una più flessibile INDICE/CONFRONTA. Quest’ultimo è il mio metodo preferito perché fornisce numerosi vantaggi, i più essenziali dei quali sono:
- È necessario creare solo 3 intervalli denominati, non importa quante voci ci siano nell’elenco principale e nei dipendenti
- Gli elenchi possono contenere elementi composti da più parole ed eventuali caratteri speciali
- Il numero di voci può variare in ogni colonna
- L’ordinamento delle voci non è importante
- Infine, è molto facile da manutenere e modificare gli elenchi di origine.
Va bene, basta con la teoria, iniziamo con la pratica.
Organizzare i dati di origine in una tabella
Come al solito, la prima cosa da fare è quella di scrivere tutte le scelte per i vostri elenchi a discesa in un foglio di lavoro. Questa volta, dovrete impiegare le tabelle di Excel per memorizzare i dati di origine.
Vi ricordo che le tabelle sono stati introdotte in Excel 2007, potranno quindi essere utilizzate in tutte le versioni moderne di Excel: 2016, 2013, 2010 e 2007.
Una volta inseriti i dati, selezionate tutte le voci e fate clic su INSERISCI > Tabella. Poi passate alla scheda PROGETTAZIONE e digitate un nome nella casella Nome tabella.
L’approccio più pratico e intuitivo è quello di memorizzare gli elementi per l’elenco principale come intestazioni di tabella, e le voci del menu a discesa dipendente come dati della tabella. La figura seguente illustra la struttura della mia tabella, chiamata produttori – i nomi di frutta sono intestazioni di tabella e viene aggiunto un elenco dei paesi produttori sotto il nome di frutta corrispondente.
Creare nomi di Excel
Ora che i dati originari sono pronti, è il momento di impostare gli intervalli denominati che dinamicamente recupereranno l’elenco corretto dalla vostra tabella.
Aggiungere un nome alla riga di intestazione della tabella (menu principale)
Per creare un nuovo nome che faccia riferimento all’intestazione della tabella, selezionarla e quindi fare clic su FORMULE > Gestione nomi > Nuovo o premere Ctrl + F3.
Microsoft Excel utilizzerà il sistema di riferimento tabella incorporato per creare il nome nel formato nome_tabella[#Intestazioni].
Dategli un nome significativo e facile da ricordare, ad esempio, frutta, e fate clic su OK.
Creare un nome per la cella che conterrà il primo elenco a discesa
So che ancora non avete alcun elenco a discesa :), ma dovete scegliere la cella che dovrà ospitare il primo menu ed assegnargli un nome ora perché è necessario includere questo nome nel riferimento del terzo nome.
Per esempio, la mia prima casella a discesa sarà nella cella B1 su Foglio 7, quindi le ho attribuito un nome, qualcosa di semplice e intuitivo come frutto:
Creare un nome per recuperare le voci del menu dipendente
Invece di creare nomi univoci per ciascuna delle liste dipendenti come abbiamo fatto nel precedente esempio, creeremo un nome sotto forma di formula che non verrà assegnato a una cella particolare o ad un intervallo di celle. Recupererà l’elenco corretto di voci per il secondo menu a seconda della scelta effettuate nel primo elenco a discesa. Il vantaggio principale di utilizzare questa formula è che non sarà necessario creare nuovi nomi quando si aggiungeranno nuove voci al primo elenco a discesa – una formula le coprirà tutte.
Create un nuovo nome di Excel nel solito modo (FORMULE > Gestione nomi > Nuovo) con questa formula:
=INDICE(produttori; ; CONFRONTA(frutto; frutta; 0))
Dove:
- produttori è il nome della tabella (creata nel primo step)
- frutto è il nome della cella contenente il menu a discesa principale
- frutta è il nome che fa riferimento alle intestazioni di tabella
Gli ho dato il nome di elenco_produttori, come vedete dall’immagine seguente:
Se siete curiosi di approfondire le funzioni INDICE e CONFRONTA, leggete pure questo tutorial: Come usare INDICE / CONFRONTA: un CERCA.VERT potenziato.
Bene, avete già fatto la maggior parte del lavoro! Prima di arrivare alla fase finale, può essere una buona idea aprire il gestore dei nomi (Ctrl + F3) e verificare i nomi e riferimenti:
Impostare la convalida dati di Excel
Questa è in realtà la parte più facile. Con le due formule denominate già impostate, sistemiamo la convalida dati nel modo consueto (scheda DATI > Convalida dati).
- Per il primo elenco a discesa, nella casella Origine, digitate =frutta
- Per l’elenco a discesa dipendente, digitate =elenco_produttori
Fatto! Il menu a discesa dinamico è finito e si aggiornerà automaticamente riflettendo le modifiche apportate alla tabella di origine.
Questo elenco a discesa dinamico, perfetto in tutti gli altri aspetti, ha un difetto – se le colonne della tabella di origine contengono un numero diverso di elementi, appariranno delle righe vuote nel menu, come in questo caso:
Escludere le righe vuote dal menu a discesa dinamico dipendente
Se volete pulire tutte le righe vuote dai vostri menu a discesa, dovrete fare un ulteriore passo avanti e migliorare la formula INDICE / CONFRONTA utilizzato per creare l’elenco a discesa dinamico dipendente.
L’idea è quella di utilizzare 2 funzioni INDICE, dove la prima ottiene la cella in alto a sinistra e la seconda restituisce la cella in basso a destra del campo, oppure la funzione SCARTO con INDICE nidificato e CONTA.VALORI. I passaggi dettagliati sono qui sotto:
Create due nomi aggiuntivi
Per non appesantire troppo la formula, prima create un paio di nomi di supporto con le seguenti semplici formule:
- Un nome chiamato col_num per fare riferimento al numero di colonna selezionato: =CONFRONTA(frutto; frutta; 0)
- Un nome chiamato col_intera per fare riferimento alla colonna selezionata (non il numero della colonna, ma l’intera colonna): =INDICE(produttori; ; col_num)
Nelle formule di cui sopra, produttori è il nome della tabella di origine, frutto è il nome della cella contenente il primo elenco a discesa, e frutta è il nome che fa riferimento alla riga di intestazione della tabella.
Creare il riferimento denominato per il menu a discesa dipendente
Successivamente, utilizzate una delle due formule di seguito per creare un nuovo nome (chiamiamolo elenco_produttori2) da utilizzare con l’elenco a discesa dipendente:
=INDICE(produttori; 1; col_num):INDICE(produttori; CONTA.VALORI(col_intera); col_num) =SCARTO(INDICE(produttori; 1; col_num); 0; 0; CONTA.VALORI(col_intera))
Applicare la convalida dati
Infine, selezionate la cella che contiene il menu a discesa dipendente e applicate convalida dei dati inserendo =elenco_produttori2 (il nome creato nel passaggio precedente) nella casella Origine.
E tutte le righe vuote se ne sono andate!
