Utiliser les noms de plage dynamique dans Excel pour les listes déroulantes flexibles
Les feuilles de calcul Excel incluent souvent des listes déroulantes de cellules pour simplifier et / ou normaliser 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 La validation des données (sur le Les données onglet), sélectionnez Validation des données, choisissez liste (sous Autoriser :), puis entrez les éléments de la liste (séparés par des virgules) dans le La source: champ (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 être difficile, cependant, 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, puis à spécifier ce nom de plage (précédé d’un signe égal) dans le La source: champ de la validation des données (comme indiqué sur 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. Puisque la plage nommée (FruitChoices, dans notre exemple) fait référence à une plage fixe de cellules ($ H $ 3: $ H $ 10 comme indiqué), si plus de 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 Pears et Strawberries sont effacées, elles n’apparaîtront plus dans la liste déroulante, mais à la place la liste déroulante inclura 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 dynamique nom de la plage comme source des choix de liste déroulante. Un nom de plage dynamique est un nom qui se développe automatiquement (ou se contracte) 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 un formule, plutôt qu’une plage fixe d’adresses de cellules, pour définir la plage nommée.
Comment configurer une plage dynamique 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, extraite d’une feuille de calcul distincte 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 les fruits sont dans un bloc de cellules directement sous un en-tête (DES FRUITS). Cette rubrique reçoit également un nom: FruitsTête:
La formule entière utilisée pour définir la plage dynamique des choix Fruits est:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
FruitsTête 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 (cela peut être ajusté à 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ù des entrées peuvent être effectuées, et non au nombre réel d’entrées.
Décomposons maintenant la formule en morceaux (code couleur de chaque pièce), 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 intime» 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 à FruitsTête cellule, descendez 1 ligne et plus de 0 colonnes, puis sélectionnez une zone de 20 lignes de long et 1 colonne de large. Cela nous donne le bloc de 20 lignes où les choix Fruits sont entrés.
L’élément suivant de la formule est le ISBLANK fonction:
=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 car les 8 premières cellules ne sont pas vides et les 12 dernières valeurs seront TRUE.
L’élément suivant de la formule est la fonction INDEX:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,0,0),0)-1,20),1)
Là encore, «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.
INDICE est normalement utilisé pour choisir 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 en définissant les entrées de ligne et de colonne sur zéro (comme cela est fait ici), INDEX renvoie un tableau contenant tout le bloc de données.
L’élément suivant de la formule est la fonction MATCH:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)
le RENCONTRE La fonction renvoie la position de la première valeur TRUE, dans le tableau retourné 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 les 9e ligne de la plage est vide).
Ainsi, la fonction MATCH retournera la valeur de 9. Dans ce cas, cependant, nous voulons vraiment savoir combien d’entrées sont 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.
L’élément suivant de la formule est la fonction IFERROR:
=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)
La fonction 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 des cellules n’est vide, alors le tableau entier sera rempli avec des valeurs FALSE. Si MATCH ne peut pas trouver la valeur cible (TRUE) dans le tableau qu’il recherche, il renvoie une erreur.
Donc, si la liste entière est pleine (et par conséquent, MATCH renvoie une erreur), la fonction IFERROR renverra à la place la valeur de 20 (sachant qu’il doit y avoir 20 entrées dans la liste).
Finalement, OFFSET (FruitsHeading, 1,0, ci-dessus, 1) renvoie la plage que nous recherchons réellement: Commencez par la cellule FruitsHeading, descendez d’une ligne et sur 0 colonnes, puis sélectionnez une zone qui compte le nombre de lignes qu’il y a des entrées dans la liste (et 1 colonne de large). Ainsi, l’ensemble de la formule retournera la plage qui contient uniquement 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 librement modifier 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 l’actuelle liste (voir Figure 6).
Le fichier d’exemple (Dynamic Lists) qui a été utilisé ici est inclus et est téléchargeable à partir de ce site Web. Les macros ne fonctionnent pas, cependant, car WordPress n’aime pas les livres Excel contenant des macros.
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 deuxième liste (Noms) utilise cette méthode. Ici, le bloc de liste entier (sous l’en-tête “NAMES”, 40 lignes dans le fichier d’exemple) reçoit le nom de plage de NomBlock. 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ù NomsBlock 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 édité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. Prendre plaisir!
