I fogli di calcolo Excel includono spesso i menu a discesa delle celle per semplificare e / o standardizzare l'inserimento dei dati. Questi elenchi a discesa vengono creati utilizzando la funzione di convalida dei dati per specificare un elenco di voci consentite.
Per impostare un semplice elenco a discesa, selezionare la cella in cui verranno inseriti i dati, quindi fare clic su Convalida dati (nella scheda Dati ), selezionare Convalida dati, selezionare Elenco (in Consenti :), quindi inserire gli elementi dell'elenco (separati da virgole ) nel campo Source : (vedi Figura 1).
In questo tipo di menu a discesa di base, l'elenco delle voci consentite è specificato all'interno della convalida dei dati stessi; pertanto, per apportare modifiche all'elenco, l'utente deve aprire e modificare la convalida dei dati. Questo può essere difficile, tuttavia, per utenti inesperti, o nei casi in cui l'elenco delle scelte è lungo.
Un'altra opzione è quella di posizionare l'elenco in un intervallo denominato all'interno del foglio di calcolo e quindi specificare il nome dell'intervallo (preceduto da un segno di uguale) nel campo Origine : della convalida dei dati (come mostrato nella Figura 2).
Questo secondo metodo semplifica la modifica delle scelte nell'elenco, ma l'aggiunta o la rimozione di elementi può essere problematica. Poiché l'intervallo denominato (FruitChoices, nel nostro esempio) fa riferimento a un intervallo di celle fisso ($ H $ 3: $ H $ 10 come mostrato), se vengono aggiunte ulteriori opzioni alle celle H11 o inferiori, non verranno visualizzate nel menu a discesa (poiché tali celle non fanno parte della gamma FruitChoices).
Allo stesso modo, se, ad esempio, le voci Pears e Strawberry vengono cancellate, non appariranno più nel menu a discesa, ma il menu a discesa includerà due scelte "vuote" poiché il menu a discesa fa ancora riferimento all'intero intervallo FruitChoices, incluse le celle vuote H9 e H10.
Per questi motivi, quando si utilizza un intervallo denominato normale come origine elenco per un menu a discesa, l'intervallo denominato deve essere modificato per includere più o meno celle se le voci vengono aggiunte o eliminate dall'elenco.
Una soluzione a questo problema è utilizzare un nome di intervallo dinamico come origine per le scelte del menu a discesa. Un nome di intervallo dinamico è uno che si espande automaticamente (o si contrae) esattamente per corrispondere alla dimensione di un blocco di dati quando le voci vengono aggiunte o rimosse. Per fare ciò, si usa una formula, piuttosto che un intervallo fisso di indirizzi di cella, per definire l'intervallo denominato.
Come impostare un intervallo dinamico in Excel
Un normale nome di intervallo (statico) si riferisce a un intervallo di celle specificato ($ H $ 3: $ H $ 10 nel nostro esempio, vedi sotto):
Ma un intervallo dinamico viene definito utilizzando una formula (vedi sotto, tratto da un foglio di calcolo separato che utilizza i nomi degli intervalli dinamici):
Prima di iniziare, assicurati di scaricare il nostro file di esempio Excel (le macro di ordinamento sono state disabilitate).
Esaminiamo questa formula in dettaglio. Le scelte per i frutti sono in un blocco di celle direttamente sotto un titolo ( FRUTTA ). A quell'intestazione viene anche assegnato un nome: FruitsHeading :
L'intera formula utilizzata per definire la gamma dinamica per le scelte di frutta è:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX (ISBLANK (OFFSET (FruitsHeading, 1, 0, 20, 1)), 0, 0), 0) -1, 20), 1)
FruitsHeading si riferisce all'intestazione che è una riga sopra la prima voce nell'elenco. Il numero 20 (usato due volte nella formula) è la dimensione massima (numero di righe) per la lista (questo può essere regolato come desiderato).
Nota che in questo esempio ci sono solo 8 voci nell'elenco, ma ci sono anche celle vuote sotto queste dove è possibile aggiungere ulteriori voci. Il numero 20 si riferisce all'intero blocco in cui è possibile inserire le voci, non al numero effettivo di voci.
Ora dividiamo la formula in pezzi (codifica a colori ogni pezzo) per capire come funziona:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDICE (ISBLANK ( OFFSET (FruitsHeading, 1, 0, 20, 1) ), 0, 0), 0) -1, 20), 1)
Il pezzo "più interno" è OFFSET (FruitsHeading, 1, 0, 20, 1) . Questo fa riferimento al blocco di 20 celle (sotto la cella FruitsHeading) in cui è possibile inserire le scelte. Questa funzione OFFSET dice fondamentalmente: Inizia nella cella FruitsHeading, vai su 1 riga e su 0 colonne, quindi seleziona un'area lunga 20 righe e larga 1 colonna. In modo che ci dà il blocco di 20 file in cui sono inserite le scelte di frutta.
Il prossimo pezzo della formula è la funzione ISBLANK :
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDICE ( ISBLANK (sopra), 0, 0), 0) -1, 20), 1)
Qui, la funzione OFFSET (spiegata sopra) è stata sostituita con "il precedente" (per rendere le cose più facili da leggere). Ma la funzione ISBLANK funziona su un intervallo di celle di 20 righe definito dalla funzione OFFSET.
ISBLANK crea quindi un set di 20 valori TRUE e FALSE, indicando se ciascuna delle singole celle nell'intervallo di 20 righe a cui fa riferimento la funzione OFFSET è vuota (vuota) o meno. In questo esempio, i primi 8 valori nel set saranno FALSE poiché le prime 8 celle non sono vuote e gli ultimi 12 valori saranno TRUE.
Il prossimo pezzo della formula è la funzione INDICE:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDICE (sopra, 0, 0), 0) -1, 20), 1)
Di nuovo, "il precedente" si riferisce alle funzioni ISBLANK e OFFSET descritte sopra. La funzione INDICE restituisce una matrice contenente i 20 valori VERO / FALSO creati dalla funzione ISBLANK.
L'INDICE viene normalmente utilizzato per selezionare un determinato valore (o intervallo di valori) da un blocco di dati, specificando una determinata riga e colonna (all'interno di quel blocco). Ma impostando gli input di riga e colonna su zero (come si fa qui), INDICE restituisce un array contenente l'intero blocco di dati.
Il prossimo pezzo della formula è la funzione MATCH:
= OFFSET (FruitsHeading, 1, 0, IFERROR ( MATCH (TRUE, sopra, 0) -1, 20), 1)
La funzione MATCH restituisce la posizione del primo valore TRUE, all'interno dell'array restituito dalla funzione INDICE. Poiché le prime 8 voci nell'elenco non sono vuote, i primi 8 valori nell'array saranno FALSE e il nono valore sarà TRUE (poiché la 9a riga nell'intervallo è vuota).
Quindi la funzione MATCH restituirà il valore di 9 . In questo caso, tuttavia, vogliamo veramente sapere quante voci sono presenti nell'elenco, quindi la formula sottrae 1 dal valore MATCH (che fornisce la posizione dell'ultima voce). Quindi alla fine, MATCH (TRUE, il precedente, 0) -1 restituisce il valore di 8 .
Il prossimo pezzo della formula è la funzione IFERROR:
= OFFSET (FruitsHeading, 1, 0, IFERROR (sopra, 20), 1)
La funzione IFERROR restituisce un valore alternativo, se il primo valore specificato provoca un errore. Questa funzione è inclusa poiché, se l'intero blocco di celle (tutte le 20 righe) è pieno di voci, la funzione MATCH restituirà un errore.
Questo perché stiamo dicendo alla funzione MATCH di cercare il primo valore TRUE (nella matrice di valori dalla funzione ISBLANK), ma se NESSUNO delle celle è vuoto, allora l'intero array sarà riempito con valori FALSE. Se MATCH non riesce a trovare il valore di destinazione (TRUE) nella matrice che sta cercando, restituisce un errore.
Quindi, se l'intera lista è piena (e quindi MATCH restituisce un errore), la funzione IFERROR restituirà invece il valore di 20 (sapendo che ci devono essere 20 voci nell'elenco).
Infine, OFFSET (FruitsHeading, 1, 0, il precedente, 1) restituisce l'intervallo che stiamo effettivamente cercando: Inizia dalla cella FruitsHeading, scendi di 1 riga e supera 0 colonne, quindi seleziona un'area che è comunque molte righe lunghe come ci sono voci nella lista (e 1 colonna di larghezza). Quindi l'intera formula insieme restituirà l'intervallo che contiene solo le voci effettive (fino alla prima cella vuota).
L'utilizzo di questa formula per definire l'intervallo che rappresenta l'origine dell'elenco a discesa significa che puoi modificare liberamente l'elenco (aggiungendo o rimuovendo voci, purché le voci rimanenti inizino dalla cella superiore e siano contigue) e il menu a discesa rifletterà sempre l'attuale lista (vedi Figura 6).
Il file di esempio (elenchi dinamici) che è stato utilizzato qui è incluso ed è scaricabile da questo sito Web. Le macro non funzionano, tuttavia, poiché a WordPress non piacciono i libri Excel con macro al loro interno.
In alternativa alla specifica del numero di righe nel blocco di lista, al blocco di elenco può essere assegnato il proprio nome di intervallo, che può quindi essere utilizzato in una formula modificata. Nel file di esempio, un secondo elenco (Nomi) utilizza questo metodo. Qui, all'intero blocco di lista (sotto l'intestazione "NAMES", 40 righe nel file di esempio) viene assegnato il nome dell'intervallo di NameBlock . La formula alternativa per la definizione di NamesList è quindi:
= OFFSET (NamesHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX (ISBLANK ( NamesBlock ), 0, 0), 0) -1, ROWS (NamesBlock) ), 1)
dove NamesBlock sostituisce OFFSET (FruitsHeading, 1, 0, 20, 1) e ROWS (NamesBlock) sostituisce il 20 (numero di righe) nella formula precedente.
Quindi, per gli elenchi a discesa che possono essere facilmente modificati (anche da altri utenti che potrebbero essere inesperti), prova a utilizzare i nomi degli intervalli dinamici! E si noti che, sebbene questo articolo sia stato incentrato sugli elenchi a discesa, i nomi di intervalli dinamici possono essere utilizzati ovunque sia necessario fare riferimento a un intervallo o elenco che può variare in termini di dimensioni. Godere!
