I fogli di calcolo di Excel^(Excel) spesso includono menu 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, seleziona la cella in cui verranno inseriti i dati, quindi fai clic su Convalida dati^{(Data Validation)} (nella scheda Dati ), seleziona ^(Data)Convalida dati^{(Data Validation)} , scegli Elenco^(List) (sotto Consenti^(Allow) :), quindi inserisci gli elementi dell'elenco (separati da virgole ) nel campo Source : (vedere la figura 1).

In questo tipo di menu a tendina di base, l'elenco delle voci consentite è specificato all'interno della convalida dei dati stessa; pertanto, per apportare modifiche alla lista, 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 consiste nel posizionare l'elenco in un intervallo denominato all'interno del foglio di calcolo^{(named range within the spreadsheet)} , quindi specificare il nome dell'intervallo (preceduto da un segno di uguale) nel campo Source : della convalida dei dati (come mostrato nella Figura 2^{(Figure 2)} ).

Questo secondo metodo semplifica la modifica delle scelte nell'elenco, ma l'aggiunta o la rimozione di elementi può essere problematico. 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ù scelte alle celle H11 o inferiori, non verranno visualizzate nel menu a discesa (poiché quelle celle non fanno parte della gamma FruitChoices ).

Allo stesso modo se, ad esempio, le voci Pere^(Pears) e Fragole^{(Strawberries)} 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 normale intervallo denominato come origine dell'elenco per un elenco a discesa, l'intervallo denominato stesso deve essere modificato in modo da includere più o meno celle se le voci vengono aggiunte o eliminate dall'elenco.

Una soluzione a questo problema consiste nell'utilizzare un nome di intervallo dinamico^(dynamic) come origine per le scelte a discesa. Un nome di intervallo dinamico è un nome che si espande (o si contrae) automaticamente in modo che corrisponda esattamente alla dimensione di un blocco di dati quando le voci vengono aggiunte o rimosse. A tale scopo, si utilizza una formula , anziché un intervallo fisso di indirizzi di cella, per definire l'intervallo denominato.

Come impostare un intervallo dinamico^{(Dynamic Range)} 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 viene definito utilizzando una formula (vedi sotto, presa 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 Frutta si trovano in un blocco di celle direttamente sotto un'intestazione ( FRUTTA^(FRUITS) ). A tale intestazione viene assegnato anche un nome: FruitsHeading :

L'intera formula utilizzata per definire il range dinamico per le scelte Fruits è:^(Fruits)

=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 l'elenco (può essere modificato a piacere).

Si noti che in questo esempio ci sono solo 8 voci nell'elenco, ma ci sono anche celle vuote al di sotto di queste in cui è possibile aggiungere voci aggiuntive. Il numero 20 si riferisce all'intero blocco in cui è possibile effettuare inserimenti, non al numero effettivo di inserimenti.

Ora scomponiamo la formula in pezzi (codificando a colori 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 inserire le scelte. Questa funzione OFFSET fondamentalmente dice: inizia dalla cella FruitsHeading , scendi di 1 riga e oltre 0 colonne, quindi seleziona un'area lunga 20 righe e larga 1 colonna. Quindi questo ci dà il blocco di 20 righe in cui vengono inserite le scelte Fruits .

Il prossimo pezzo della formula è la funzione ISLANK^(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 con "quanto sopra" (per rendere le cose più facili da leggere). Ma la funzione ISBLANK opera sull'intervallo di 20 righe di celle che definisce la funzione OFFSET .

ISBLANK crea quindi un insieme di 20 valori VERO^(TRUE) e FALSO^(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 del 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^(INDEX) :

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,0,0),0)-1,20),1)

Anche in questo caso, "quanto sopra" si riferisce alle funzioni ISLANK^(ISBLANK) e OFFSET sopra descritte. La funzione INDICE^(INDEX) restituisce un array contenente i 20 valori TRUE / FALSE creati dalla funzione ISBLANK .

INDEX 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 l'impostazione degli input di riga e colonna su zero (come viene fatto qui) fa sì che INDEX restituisca un array contenente l'intero blocco di dati.

Il prossimo pezzo della formula è la funzione CONFRONTA^(MATCH) :

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)

La funzione CONFRONTA^(MATCH) restituisce la posizione del primo valore VERO^(TRUE) , all'interno della matrice restituita dalla funzione INDICE^(INDEX) . 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 ^nona riga nell'intervallo è vuota).

Quindi la funzione MATCH restituirà il valore di 9 . In questo caso, però, vogliamo davvero sapere quante voci ci sono nella lista, quindi la formula sottrae 1 dal valore MATCH (che dà la posizione dell'ultima voce). Quindi, alla fine, MATCH ( TRUE ,the above,0)-1 restituisce il valore di 8 .

Il prossimo pezzo della formula è la funzione SE ERRORE^(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 CONFRONTA^(MATCH) restituirà un errore.

Questo perché stiamo dicendo alla funzione MATCH di cercare il primo valore VERO^(TRUE) (nell'array di valori della funzione ISBLANK ), ma se NESSUNA^(NONE) delle celle è vuota, l'intero array verrà riempito con valori FALSE . Se MATCH non riesce a trovare il valore di destinazione ( TRUE ) nell'array che sta cercando, restituisce un errore.

Quindi, se l'intero elenco è pieno (e quindi CONFRONTA^(IFERROR) restituisce un errore), la funzione IFERROR^(MATCH) restituirà invece il valore di 20 (sapendo che devono esserci 20 voci nell'elenco).

Infine, OFFSET(FruitsHeading,1,0,the above,1) restituisce l'intervallo che stiamo effettivamente cercando: inizia dalla cella FruitsHeading , scendi di 1 riga e oltre 0 colonne, quindi seleziona un'area che sia lunga quante righe ci sono voci nell'elenco (e larghe 1 colonna). 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 (aggiungendo o rimuovendo voci, purché le voci rimanenti inizino nella cella in alto e siano contigue) e il menu a discesa rifletterà sempre l'attuale elenco (vedere la figura 6^{(Figure 6)} ).

Il file di esempio (Elenchi Dinamici) che è stato utilizzato qui è incluso ed è scaricabile da questo sito Web. Le macro non funzionano, tuttavia, perché a WordPress non piacciono i libri di Excel con le macro al loro interno.^(Excel)

In alternativa alla specifica del numero di righe nel blocco elenco, è possibile assegnare al blocco elenco un proprio nome di intervallo, che può quindi essere utilizzato in una formula modificata. Nel file di esempio, un secondo elenco ( Names ) utilizza questo metodo. Qui, all'intero blocco dell'elenco (sotto l'intestazione "NAMES", 40 righe nel file di esempio) viene assegnato il nome dell'intervallo di NameBlock . La formula alternativa per definire la 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 tieni presente che, sebbene questo articolo sia stato incentrato sugli elenchi a discesa, i nomi degli intervalli dinamici possono essere utilizzati ovunque sia necessario fare riferimento a un intervallo o un elenco di dimensioni variabili. Divertiti!

Use Dynamic Range Names in Excel for Flexible Dropdowns

Excel spreadsheets often include cell dropdowns to simplify and/or standardize datа entry.  These dropdowns are created using the data validation feature to specify a list of аllowable entries.

To set up a simple dropdown list, select the cell where data will be entered, then click Data Validation (on the Data tab), select Data Validation, choose List (under Allow:), and then enter the list items (separated by commas) in the Source: field (see Figure 1).

In this type of basic dropdown, the list of allowable entries is specified within the data validation itself; therefore, to make changes to the list, the user must open and edit the data validation.  This may be difficult, however, for inexperienced users, or in cases where the list of choices is lengthy.

Another option is to place the list in a named range within the spreadsheet, and then specify that range name (prefaced with an equal sign) in the Source: field of the data validation (as shown in Figure 2).

This second method makes it easier to edit the choices in the list, but adding or removing items can be problematic.  Since the named range (FruitChoices, in our example) refers to a fixed range of cells ($H$3:$H$10 as shown), if more choices are added to the cells H11 or below, they will not show up in the dropdown (since those cells are not part of the FruitChoices range).

Likewise if, for example, the Pears and Strawberries entries are erased, they will no longer appear in the dropdown, but instead the dropdown will include two “empty” choices since the dropdown still references the entire FruitChoices range, including the empty cells H9 and H10.

For these reasons, when using a normal named range as the list source for a dropdown, the named range itself must be edited to include more or fewer cells if entries are added or deleted from the list.

A solution to this problem is to use a dynamic range name as the source for the dropdown choices.  A dynamic range name is one that automatically expands (or contracts) to exactly match the size of a block of data as entries are added or removed.  To do this, you use a formula, rather than a fixed range of cell addresses, to define the named range.

How to Setup a Dynamic Range in Excel

A normal (static) range name refers to a specified range of cells ($H$3:$H$10 in our example, see below):

But a dynamic range is defined using a formula (see below, taken from a separate spreadsheet which uses dynamic range names):

Before we get started, make sure you download our Excel example file (sort macros have been disabled).

Let’s examine this formula in detail.  The choices for Fruits are in a block of cells directly below a heading (FRUITS).  That heading is also assigned a name: FruitsHeading:

The entire formula used to define the dynamic range for the Fruits choices is:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)

FruitsHeading refers to the heading that is one row above the first entry in the list.  The number 20 (used two times in the formula) is the maximum size (number of rows) for the list (this can be adjusted as desired).

Note that in this example, there are only 8 entries in the list, but there are also empty cells below these where additional entries could be added.  The number 20 refers to the entire block where entries can be made, not to the actual number of entries.

Now let’s break down the formula into pieces (color-coding each piece), to understand how it works:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)

The “innermost” piece is OFFSET(FruitsHeading,1,0,20,1).  This references the block of 20 cells (underneath the FruitsHeading cell) where choices may be entered.  This OFFSET function basically says: Start at the FruitsHeading cell, go down 1 row and over 0 columns, then select an area that is 20 rows long and 1 column wide.  So that gives us the 20-row block where the Fruits choices are entered.

The next piece of the formula is the ISBLANK function:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),1)

Here, the OFFSET function (explained above) has been replaced with “the above” (to make things easier to read).  But the ISBLANK function is operating on the 20-row range of cells that the OFFSET function defines.

ISBLANK then creates a set of 20 TRUE and FALSE values, indicating whether each of the individual cells in the 20-row range referenced by the OFFSET function is blank (empty) or not.  In this example, the first 8 values in the set will be FALSE since the first 8 cells are not empty and the last 12 values will be TRUE.

The next piece of the formula is the INDEX function:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,0,0),0)-1,20),1)

Again, “the above” refers to the ISBLANK and OFFSET functions described above.  The INDEX function returns an array containing the 20 TRUE / FALSE values created by the ISBLANK function.

INDEX is normally used to pick a certain value (or range of values) out of a block of data, by specifying a certain row and column (within that block).  But setting the row and column inputs to zero (as is done here) causes INDEX to return an array containing the entire block of data.

The next piece of the formula is the MATCH function:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)

The MATCH function returns the position of the first TRUE value, within the array that’s returned by the INDEX function.  Since the first 8 entries in the list are not blank, the first 8 values in the array will be FALSE, and the ninth value will be TRUE (since the 9^th row in the range is empty).

So the MATCH function will return the value of 9.  In this case, however, we really want to know how many entries are in the list, so the formula subtracts 1 from the MATCH value (which gives the position of the last entry).  So ultimately, MATCH(TRUE,the above,0)-1 returns the value of 8.

The next piece of the formula is the IFERROR function:

=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)

The IFERROR function returns an alternate value, if the first value specified results in an error.  This function is included since, if the entire block of cells (all 20 rows) are filled with entries, the MATCH function will return an error.

This is because we’re telling the MATCH function to look for the first TRUE value (in the array of values from the ISBLANK function), but if NONE of the cells are empty, then the entire array will be filled with FALSE values.  If MATCH cannot find the target value (TRUE) in array it is searching, it returns an error.

So, if the entire list is full (and therefore, MATCH returns an error), the IFERROR function will instead return the value of 20 (knowing that there must be 20 entries in the list).

Finally, OFFSET(FruitsHeading,1,0,the above,1) returns the range we are actually looking for:  Start at the FruitsHeading cell, go down 1 row and over 0 columns, then select an area that is however many rows long as there are entries in the list (and 1 column wide).  So the entire formula together will return the range that contains only the actual entries (down to the first empty cell).

Using this formula to define the range that is the source for the dropdown means you can freely edit the list (adding or removing entries, as long as the remaining entries start at the top cell and are contiguous) and the dropdown will always reflect the current list (see Figure 6).

The example file (Dynamic Lists) that’s been used here is included and is downloadable from this website. The macros don’t work, however, because WordPress doesn’t like Excel books with macros in them.

As an alternative to specifying the number of rows in the list block, the list block can be assigned its own range name, which can then be used in a modified formula.  In the example file, a second list (Names) uses this method.  Here, the entire list block (underneath the “NAMES” heading, 40 rows in the example file) is assigned the range name of NameBlock.  The alternate formula for defining the NamesList is then:

=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)

where NamesBlock replaces OFFSET(FruitsHeading,1,0,20,1) and ROWS(NamesBlock) replaces the 20 (number of rows) in the earlier formula.

So, for dropdown lists which can be easily edited (including by other users who may be inexperienced), try using dynamic range names!  And note that, although this article has been focused on dropdown lists, dynamic range names can be used anywhere you need to reference a range or list that can vary in size. Enjoy!

Related posts

• Come eliminare le righe vuote in Excel

• Come utilizzare la funzione Speak Cells di Excel

• Come inserire un foglio di lavoro Excel in un documento di Word

• Come utilizzare l'analisi What-If di Excel

• Come correggere una riga in Excel

• Perché dovresti usare intervalli denominati in Excel

• Differenze tra Microsoft Excel Online ed Excel per desktop

• Come correggere gli errori #N/D in formule di Excel come CERCA.VERT

• 4 modi per utilizzare un segno di spunta in Excel

• Centrare i dati del foglio di lavoro in Excel per la stampa

• Come ordinare per data in Excel

• Come creare più elenchi a discesa collegati in Excel

• Come calcolare il punteggio Z in Excel

• 2 modi per utilizzare la funzione di trasposizione di Excel

• Fogli Google vs Microsoft Excel: quali sono le differenze?

• 5 modi per convertire il testo in numeri in Excel

• Come utilizzare la funzione PMT in Excel

• Come creare un grafico a torta in Excel

• Come unire celle, colonne e righe in Excel

• Come creare una tabella pivot semplice in Excel