Les feuilles de calcul Excel^(Excel) incluent souvent des listes déroulantes de cellules pour simplifier et/ou standardiser la saisie de données. Ces listes déroulantes sont créées à l'aide de la fonction de validation des données pour spécifier une liste d'entrées autorisées.

Pour configurer une liste déroulante simple, sélectionnez la cellule dans laquelle les données seront saisies, puis cliquez sur Validation des données^{(Data Validation)} (sur l' onglet Données ), sélectionnez ^(Data)Validation^{(Data Validation)} des données , choisissez Liste^(List) (sous Autoriser^(Allow) :), puis saisissez les éléments de la liste (séparés par des virgules ) dans le champ Source : (voir Figure 1).

Dans ce type de liste déroulante de base, la liste des entrées autorisées est spécifiée dans la validation des données elle-même ; par conséquent, pour apporter des modifications à la liste, l'utilisateur doit ouvrir et modifier la validation des données. Cela peut cependant être difficile pour les utilisateurs inexpérimentés ou dans les cas où la liste de choix est longue.

Une autre option consiste à placer la liste dans une plage nommée dans la feuille de calcul^{(named range within the spreadsheet)} , puis à spécifier ce nom de plage (préfacé par un signe égal) dans le champ Source : de la validation des données (comme illustré à la figure 2 ).

Cette deuxième méthode facilite la modification des choix dans la liste, mais l'ajout ou la suppression d'éléments peut être problématique. Étant donné que la plage nommée ( FruitChoices , dans notre exemple) fait référence à une plage fixe de cellules ($H$3:$H$10 comme indiqué), si d'autres choix sont ajoutés aux cellules H11 ou ci-dessous, ils n'apparaîtront pas dans la liste déroulante. (puisque ces cellules ne font pas partie de la gamme FruitChoices ).

De même, si, par exemple, les entrées Poires^(Pears) et Fraises^{(Strawberries)} sont effacées, elles n'apparaîtront plus dans la liste déroulante, mais à la place, la liste déroulante comprendra deux choix "vides" puisque la liste déroulante fait toujours référence à toute la gamme FruitChoices, y compris les cellules vides H9 et H10 .

Pour ces raisons, lorsque vous utilisez une plage nommée normale comme source de liste pour une liste déroulante, la plage nommée elle-même doit être modifiée pour inclure plus ou moins de cellules si des entrées sont ajoutées ou supprimées de la liste.

Une solution à ce problème consiste à utiliser un nom de plage dynamique^(dynamic) comme source pour les choix de la liste déroulante. Un nom de plage dynamique est un nom qui se développe (ou se contracte) automatiquement pour correspondre exactement à la taille d'un bloc de données lorsque des entrées sont ajoutées ou supprimées. Pour ce faire, vous utilisez une formule^(formula) , plutôt qu'une plage fixe d'adresses de cellule, pour définir la plage nommée.

Comment configurer une plage dynamique^{(Dynamic Range)} dans Excel

Un nom de plage normal (statique) fait référence à une plage de cellules spécifiée ($H$3 :$H$10 dans notre exemple, voir ci-dessous) :

Mais une plage dynamique est définie à l'aide d'une formule (voir ci-dessous, tirée d'une feuille de calcul séparée qui utilise des noms de plage dynamique) :

Avant de commencer, assurez-vous de télécharger notre fichier d'exemple Excel  (les macros de tri ont été désactivées).

Examinons cette formule en détail. Les choix pour Fruits se trouvent dans un bloc de cellules directement sous un en-tête ( FRUITS ). Cette rubrique se voit également attribuer un nom : FruitsHeading :

La formule complète utilisée pour définir la plage dynamique des choix Fruits est la suivante :

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

FruitsHeading fait référence à l'en-tête qui se trouve une ligne au-dessus de la première entrée de la liste. Le nombre 20 (utilisé deux fois dans la formule) est la taille maximale (nombre de lignes) de la liste (elle peut être ajustée à volonté).

Notez que dans cet exemple, il n'y a que 8 entrées dans la liste, mais il y a aussi des cellules vides en dessous de celles-ci où des entrées supplémentaires pourraient être ajoutées. Le nombre 20 fait référence au bloc entier où les entrées peuvent être faites, pas au nombre réel d'entrées.

Décomposons maintenant la formule en morceaux (code couleur de chaque morceau), pour comprendre comment cela fonctionne :

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

La pièce "la plus interne" est OFFSET(FruitsHeading,1,0,20,1) . Cela fait référence au bloc de 20 cellules (sous la cellule FruitsHeading ) où les choix peuvent être saisis. Cette fonction OFFSET dit essentiellement : commencez par la cellule FruitsHeading , descendez d'une ligne et sur 0 colonne, puis sélectionnez une zone de 20 lignes de long et 1 colonne de large. Cela nous donne donc le bloc de 20 lignes où les choix de fruits sont entrés.

L'élément suivant de la formule est la fonction ISBLANK :

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

Ici, la fonction OFFSET (expliquée ci-dessus) a été remplacée par "ce qui précède" (pour faciliter la lecture). Mais la fonction ISBLANK fonctionne sur la plage de cellules de 20 lignes définie par la fonction OFFSET .

ISBLANK crée ensuite un ensemble de 20 valeurs TRUE et FALSE , indiquant si chacune des cellules individuelles de la plage de 20 lignes référencée par la fonction OFFSET est vide (vide) ou non. Dans cet exemple, les 8 premières valeurs de l'ensemble seront FALSE puisque les 8 premières cellules ne sont pas vides et les 12 dernières valeurs seront TRUE .

Le prochain élément de la formule est la fonction INDEX :

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

Encore une fois, "ce qui précède" fait référence aux fonctions ISBLANK et OFFSET décrites ci-dessus. La fonction INDEX renvoie un tableau contenant les 20 valeurs TRUE / FALSE créées par la fonction ISBLANK .

INDEX est normalement utilisé pour sélectionner une certaine valeur (ou plage de valeurs) dans un bloc de données, en spécifiant une certaine ligne et colonne (dans ce bloc). Mais la définition des entrées de ligne et de colonne à zéro (comme cela est fait ici) oblige INDEX à renvoyer un tableau contenant le bloc de données complet.

L'élément suivant de la formule est la fonction MATCH :

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

La fonction MATCH renvoie la position de la première valeur TRUE , dans le tableau renvoyé par la fonction INDEX . Étant donné que les 8 premières entrées de la liste ne sont pas vides, les 8 premières valeurs du tableau seront FALSE et la neuvième valeur sera TRUE (puisque la 9 ^ème ligne de la plage est vide).

Ainsi, la fonction MATCH renverra la valeur de 9 . Dans ce cas, cependant, nous voulons vraiment savoir combien d'entrées se trouvent dans la liste, donc la formule soustrait 1 de la valeur MATCH (qui donne la position de la dernière entrée). Donc, finalement, MATCH ( TRUE ,ce qui précède,0)-1 renvoie la valeur de 8 .

Le prochain élément de la formule est la fonction SIERREUR^(IFERROR) :

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

La fonction SIERREUR^(IFERROR) renvoie une autre valeur si la première valeur spécifiée entraîne une erreur. Cette fonction est incluse car, si le bloc entier de cellules (les 20 lignes) est rempli d'entrées, la fonction MATCH renverra une erreur.

C'est parce que nous disons à la fonction MATCH de rechercher la première valeur TRUE (dans le tableau de valeurs de la fonction ISBLANK ), mais si AUCUNE^(NONE) des cellules n'est vide, alors le tableau entier sera rempli de valeurs FALSE . Si MATCH ne trouve pas la valeur cible ( TRUE ) dans le tableau qu'il recherche, il renvoie une erreur.

Ainsi, si toute la liste est pleine (et donc, MATCH renvoie une erreur), la fonction SIERREUR renverra^(IFERROR) à la place la valeur de 20 (sachant qu'il doit y avoir 20 entrées dans la liste).

Enfin, OFFSET(FruitsHeading,1,0,the above,1) renvoie la plage que nous recherchons réellement : commencez par la cellule FruitsHeading , descendez d'une ligne et sur 0 colonne, puis sélectionnez une zone qui compte le nombre de lignes le plus long possible. il y a des entrées dans la liste (et 1 colonne de large). Ainsi, la formule entière renverra ensemble la plage qui ne contient que les entrées réelles (jusqu'à la première cellule vide).

L'utilisation de cette formule pour définir la plage qui est la source de la liste déroulante signifie que vous pouvez modifier librement la liste (ajouter ou supprimer des entrées, tant que les entrées restantes commencent à la cellule supérieure et sont contiguës) et la liste déroulante reflétera toujours le courant liste (voir Figure 6 ).

Le fichier d'exemple (listes dynamiques) qui a été utilisé ici est inclus et peut être téléchargé à partir de ce site Web. Cependant, les macros ne fonctionnent pas, car WordPress n'aime pas les livres Excel contenant des macros.^(Excel)

Au lieu de spécifier le nombre de lignes dans le bloc de liste, le bloc de liste peut se voir attribuer son propre nom de plage, qui peut ensuite être utilisé dans une formule modifiée. Dans le fichier d'exemple, une seconde liste ( Names ) utilise cette méthode. Ici, le bloc de liste entier (sous l'en-tête "NAMES", 40 lignes dans le fichier d'exemple) se voit attribuer le nom de plage de NameBlock . La formule alternative pour définir la NamesList est alors :

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

où NamesBlock remplace OFFSET ( FruitsHeading,1,0,20,1 ) et ROWS(NamesBlock) remplace le 20 (nombre de lignes) dans la formule précédente.

Donc, pour les listes déroulantes qui peuvent être facilement modifiées (y compris par d'autres utilisateurs qui peuvent être inexpérimentés), essayez d'utiliser des noms de plage dynamique ! Et notez que, bien que cet article se soit concentré sur les listes déroulantes, les noms de plage dynamique peuvent être utilisés partout où vous avez besoin de référencer une plage ou une liste dont la taille peut varier. Profitez!

Use Dynamic Range Names in Excel for Flexible Dropdowns

Εxcel spreadsheets often include cell dropdowns to simplify and/or standardizе data entry.  These dropdowns are created using the data validation feature to specify a list of allowable 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

• Comment supprimer des lignes vides dans Excel

• Comment utiliser la fonctionnalité Speak Cells d'Excel

• Comment insérer une feuille de calcul Excel dans un document Word

• Comment utiliser l'analyse What-If d'Excel

• Comment réparer une ligne dans Excel

• Comment écrire une formule/instruction IF dans Excel

• Un guide VBA avancé pour MS Excel

• Comment créer plusieurs listes déroulantes liées dans Excel

• 3 façons de diviser une cellule dans Excel

• Pourquoi devriez-vous utiliser des plages nommées dans Excel

• Utilisation de l'outil de recherche d'objectifs d'analyse What-If d'Excel

• Comment effectuer une recherche dans Excel

• 4 façons d'utiliser une coche dans Excel

• Comment calculer le score Z dans Excel

• Google Sheets vs Microsoft Excel – Quelles sont les différences ?

• Comment fusionner des cellules, des colonnes et des lignes dans Excel

• Centrez vos données de feuille de calcul dans Excel pour l'impression

• Qu'est-ce qu'un tableau VBA dans Excel et comment en programmer un

• 4 façons de convertir Excel en Google Sheets

• Comment utiliser les références absolues dans Excel