Recommandé, 2024

Choix De L'Éditeur

Utiliser des noms de plages dynamiques dans Excel pour des 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 fonctionnalité de validation des données afin de spécifier une liste d'entrées autorisées.

Pour configurer une simple liste déroulante, sélectionnez la cellule où les données seront saisies, puis cliquez sur Validation des données (sous l'onglet Données ), 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 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 éditer la validation des données. Cela peut toutefois être difficile pour les utilisateurs inexpérimentés ou dans les cas où la liste des choix est longue.

Une autre option consiste à placer la liste dans une plage nommée de la feuille de calcul, puis à spécifier le nom de cette plage (précédé du 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 davantage de choix sont ajoutés aux cellules H11 ou inférieures, elles 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 et Fraises sont effacées, elles n'apparaîtront plus dans la liste déroulante. Au lieu de cela, la liste déroulante comprendra deux choix «vides», car la liste déroulante fait toujours référence à toute la plage FruitChoices, y compris les cellules vides H9 et H10.

Pour ces raisons, lorsque vous utilisez une plage nommée normale en tant que source de liste pour une liste déroulante, vous devez la modifier 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 comme source pour les choix de 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, 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 utilisant des noms de plage dynamique):

Avant de commencer, assurez-vous de télécharger notre exemple de fichier 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 une rubrique ( FRUITS ). Un nom est également attribué à cette rubrique: 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 (VRAI, INDEX (ISBLANK, OFFSET (FruitsHeading, 1, 0, 20, 1)), 0, 0), 0) -1, 20), 1) 

FruitsHeading fait référence à l'en-tête situé 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 (celle-ci 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 dans lesquelles des entrées supplémentaires pourraient être ajoutées. Le nombre 20 désigne l'ensemble du bloc dans lequel des entrées peuvent être effectuées et non le nombre réel d'entrées.

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

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

La pièce «la plus intérieure» est OFFSET (FruitsHeading, 1, 0, 20, 1) . Ceci fait référence au bloc de 20 cellules (sous la cellule FruitsHeading) où des choix peuvent être entrés. Cette fonction OFFSET indique essentiellement: Commencez par la cellule FruitsHeading, descendez d’une rangée et de plus de 0 colonnes, puis sélectionnez une zone de 20 rangées de long sur une colonne de large. Cela nous donne donc le bloc de 20 lignes dans lequel les choix de fruits sont entrés.

La partie suivante de la formule est la fonction ISBLANK :

 = OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (VRAI, INDEX ( ISBLANK (ci-dessus), 0, 0), 0), 1), 1) 

Ici, la fonction OFFSET (expliquée ci-dessus) a été remplacée par «ci-dessus» (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 VRAI et FAUX, indiquant si chacune des cellules individuelles dans 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 FAUX, car les 8 premières cellules ne sont pas vides et les 12 dernières valeurs sont VRAIES.

La pièce suivante de la formule est la fonction INDEX:

 = OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (VRAI, INDEX (supérieur, 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 une certaine colonne (au sein de ce bloc). Toutefois, si vous définissez les entrées de ligne et de colonne sur zéro (comme c'est le cas ici), INDEX renvoie un tableau contenant l'intégralité du bloc de données.

La partie suivante de la formule est la fonction MATCH:

 = OFFSET (FruitsHeading, 1, 0, IFERROR ( MATCH (VRAI, ci-dessus, 0) -1, 20), 1) 

La fonction MATCH renvoie la position de la première valeur TRUE, dans le tableau renvoyé par la fonction INDEX. Comme 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 VRAI (la 9ème ligne de la plage étant vide).

Donc, la fonction MATCH retournera la valeur de 9 . Dans ce cas, cependant, nous voulons vraiment savoir combien d’entrées figurent dans la liste. La formule soustrait donc 1 de la valeur MATCH (qui donne la position de la dernière entrée). Donc, finalement, MATCH (TRUE, la valeur ci-dessus, 0) -1 renvoie la valeur de 8 .

La prochaine partie de la formule est la fonction IFERROR:

 = OFFSET (FruitsHeading, 1, 0, IFERROR (ci-dessus, 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 tout le bloc de cellules (les 20 lignes) est rempli d'entrées, la fonction MATCH renvoie une erreur.

En effet, nous demandons à la fonction MATCH de rechercher la première valeur VRAI (dans le tableau de valeurs de la fonction ISBLANK), mais si NONE des cellules est vide, le tableau entier est rempli de valeurs FALSE. Si MATCH ne parvient pas à trouver la valeur cible (TRUE) dans le tableau recherché, il renvoie une erreur.

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

Enfin, OFFSET (FruitsHeading, 1, 0, le ci-dessus, 1) renvoie la plage que nous recherchons: Commencez par la cellule FruitsHeading, baissez 1 ligne et plus de 0 colonnes, puis sélectionnez une zone composée de plusieurs lignes il y a des entrées dans la liste (et 1 colonne de large). Ainsi, l'ensemble de la formule renverra la plage contenant uniquement les entrées réelles (jusqu'à la première cellule vide).

L'utilisation de cette formule pour définir la plage qui constitue la source du menu déroulant signifie que vous pouvez modifier librement la liste (ajouter ou supprimer des entrées, à condition que les entrées restantes commencent dans la cellule du haut et soient contiguës) et que le menu déroulant reflète toujours le texte actuel. liste (voir figure 6).

L'exemple de fichier (Listes dynamiques) 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.

Au lieu de spécifier le nombre de lignes dans le bloc de liste, vous pouvez attribuer à ce bloc de liste son propre nom de plage, qui peut ensuite être utilisé dans une formule modifiée. Dans le fichier exemple, une deuxième liste (Noms) utilise cette méthode. Ici, l'ensemble du bloc de liste (sous l'en-tête “NOMS”, 40 lignes dans le fichier exemple) se voit attribuer le nom de la plage de NameBlock . La formule alternative pour définir la liste de noms est alors:

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

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

Donc, pour les listes déroulantes qui peuvent être facilement éditées (y compris par d'autres utilisateurs inexpérimentés), essayez d'utiliser des noms de plages dynamiques! 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 faire référence à une plage ou à une liste dont la taille peut varier. Prendre plaisir!

Top