I fogli di calcolo Excel spesso includono elenchi a discesa delle celle per semplificare e / o standardizzare l'immissione dei dati. Questi menu 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), seleziona Convalida dati, scegli Elenco(in Consenti :), quindi inserisci gli elementi dell'elenco (separati da virgole) in Fonte: campo (vedere la Figura 1).
In questo tipo di menu a discesa di base, viene specificato l'elenco delle voci consentite nell'ambito della validazione dei dati stessa; pertanto, per apportare modifiche all'elenco, l'utente deve aprire e modificare la convalida dei dati. Questo può essere difficile, tuttavia, per gli utenti inesperti o nei casi in cui l'elenco delle scelte è lungo.
Un'altra opzione è quella di posizionare l'elenco in un intervallo denominato nel foglio di calcolo, quindi specificare quel nome di intervallo (preceduto da un segno di uguale) nel campo Sorgente: 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) si riferisce a un intervallo fisso di celle ($ H $ 3: $ H $ 10 come mostrato), se vengono aggiunte più opzioni alle celle H11 o inferiori, non verranno visualizzate nel menu a discesa (poiché quelle celle non fanno parte dell'intervallo FruitChoices).
Allo stesso modo se, ad esempio, le voci Pere e Fragole vengono cancellate, non appariranno più nel menu a discesa, ma invece il menu a discesa includerà due Scelte "vuote" poiché il menu a discesa fa ancora riferimento all'intero intervallo FruitChoices, comprese le celle vuote H9 e H10.
Per questi motivi, quando si utilizza un intervallo denominato normale come origine dell'elenco per un menu a discesa, l'intervallo denominato stesso deve essere modificato per includere più o meno celle se le voci vengono aggiunte o eliminate dall'elenco.
Una soluzione a questo problema è utilizzare una dinamicanome intervallo come fonte per le scelte a discesa. Un nome di intervallo dinamico è quello che si espande (o si contrae) automaticamente per adattarsi esattamente alla dimensione di un blocco di dati quando le voci vengono aggiunte o rimosse. Per fare ciò, usi un formula, piuttosto che un intervallo fisso di indirizzi di cella, per definire l'intervallo denominato.
Come impostare una dinamica Intervallo in Excel
Un nome di intervallo normale (statico) si riferisce a un intervallo di celle specificato ($ H $ 3: $ H $ 10 nel nostro esempio, vedi sotto):
Ma un intervallo dinamico è definito usando una formula (vedi sotto, presa da un foglio di calcolo separato che utilizza nomi di intervallo dinamico):
Prima di iniziare, assicurati di scaricare i nostri File di esempio di Excel (le macro di ordinamento sono state disabilitate).
Esaminiamo questa formula in dettaglio. Le scelte per Frutta sono in un blocco di celle direttamente sotto un'intestazione (FRUTTA). A quell'intestazione viene anche assegnato un nome: FruitsHeading:
L'intera formula utilizzata per definire l'intervallo dinamico per il Le scelte di frutta sono:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
FruitsHeadingsi riferisce all'intestazione che è una riga sopra la prima voce dell'elenco. Il numero 20 (usato due volte nella formula) è la dimensione massima (numero di righe) per l'elenco (questo può essere regolato come desiderato).
Nota che in questo esempio ci sono solo 8 voci nell'elenco, ma sotto ci sono anche celle vuote in cui è possibile aggiungere ulteriori voci. Il numero 20 si riferisce all'intero blocco in cui è possibile creare voci, non al numero effettivo di voci.
Ora suddividiamo la formula in pezzi (codifica per colore di ogni pezzo), per capire come funziona :
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(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 immettere le opzioni. Questa funzione OFFSET in pratica dice: Inizia dalla cella FruitsHeading, scendi 1 riga e oltre 0 colonne, quindi seleziona un'area lunga 20 righe e larga 1 colonna. Questo ci dà il blocco di 20 righe in cui sono inserite le scelte di Frutta.
Il prossimo pezzo della formula è la funzione ISBLANK:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),1)
Qui, la funzione OFFSET (spiegata sopra) è stata sostituita da "quanto sopra" (per rendere le cose più facili da leggere). Ma la funzione ISBLANK opera nell'intervallo di 20 righe di celle definito dalla funzione OFFSET.
ISBLANK crea quindi un insieme di 20 valori TRUE e FALSE, indicando se ciascuna delle singole celle nei 20- l'intervallo di righe a cui fa riferimento la funzione OFFSET è vuoto (vuoto) oppure no. In questo esempio, i primi 8 valori nell'insieme saranno FALSE poiché le prime 8 celle non sono vuote e gli ultimi 12 valori saranno VERO.
Il prossimo pezzo della formula è la funzione INDICE:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,0,0),0)-1,20),1)
Ancora una volta, "quanto sopra" si riferisce alle funzioni ISBLANK e OFFSET descritte sopra. La funzione INDICE restituisce un array contenente i 20 valori VERO / FALSO creati dalla funzione ISBLANK.
INDICEviene 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 fatto qui), INDEX restituisce un array contenente l'intero blocco di dati.
Il prossimo pezzo della formula è la funzione MATCH:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)
La funzione MATCHrestituisce la posizione del primo valore VERO, all'interno della matrice restituita 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 riga 9 thnell'intervallo è vuota).
Quindi la funzione MATCH restituirà il valore di 9. In questo caso, tuttavia, vogliamo davvero sapere quante voci sono presenti nell'elenco, quindi la formula sottrae 1 dal valore MATCH (che indica la posizione dell'ultima voce). Quindi, in definitiva, MATCH (TRUE, quanto sopra, 0) -1 restituisce il valore di 8.
Il prossimo pezzo della formula è la funzione IFERROR:
=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)
La funzione IFERROR restituisce un valore alternativo, se il primo valore specificato genera 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 VERO (nell'array di valori dalla funzione ISBLANK), ma se NESSUNO delle celle è vuoto, l'intero array verrà riempito con i valori FALSE. Se MATCH non riesce a trovare il valore target (TRUE) nell'array che sta cercando, restituisce un errore.
Quindi, se l'intero elenco è pieno (e quindi MATCH restituisce un errore), la funzione IFERROR restituisce invece il valore 20 (sapendo che ci devono essere 20 voci nell'elenco).
Infine, OFFSET (FruitsHeading, 1,0, quanto sopra, 1)restituisce il intervallo che stiamo effettivamente cercando: inizia dalla cella FruitsHeading, scendi 1 riga e oltre 0 colonne, quindi seleziona un'area che è comunque lunga più righe in quanto vi sono voci nell'elenco (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'uso di questa formula per definire l'intervallo che è l'origine del menu a discesa significa che puoi modificare liberamente l'elenco (aggiunta o rimozione di voci, purché le voci rimanenti inizino nella cella superiore e siano contigue) e l'elenco a discesa rifletterà sempre l'elenco corrente (vedere la Figura 6).
file di esempio (Elenchi dinamici) che è stato utilizzato qui è incluso ed è scaricabile da questo sito Web. Le macro non funzionano, tuttavia, perché WordPress non gradisce i libri di Excel con macro al loro interno.
In alternativa alla specifica del numero di righe nel blocco elenco, al blocco elenco può essere assegnato il relativo proprio nome 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 elenco (sotto l'intestazione "NOMES", 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 NamesBlocksostituisce 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 non avere esperienza), prova a usare i nomi degli intervalli dinamici! E nota che, sebbene questo articolo sia stato incentrato su elenchi a discesa, i nomi di intervalli dinamici possono essere utilizzati ovunque sia necessario fare riferimento a un intervallo o a un elenco che può variare di dimensioni. Buon divertimento!