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.
Parleremo di:
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.
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.
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.
È 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)
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
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; " ";""))
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.
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:
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:
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.
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!
