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 liste déroulante simple, 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, puis Liste(sous Autoriser :), puis entrez les éléments de la liste (séparés par des virgules) dans la zone Source: champ (voir la 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 s'avérer 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 un plage nommée dans la feuille de calcul, puis à spécifier ce nom de plage (précédé du signe égal) dans le champ Source: de la validation des données (comme indiqué dans 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 davantage de choix sont ajoutés aux cellules H11 ou inférieures, elles n'apparaîtront pas dans la liste déroulante. (étant donné que 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. Les choix "vides" car la liste déroulante fait toujours référence à toute la plage FruitChoices, y compris les cellules H9 et H10 vides.
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 dynamiquenom de plage en tant que source des 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.
Procédure de configuration d'un fichier dynamique Plage 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):
Cependant, 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 de fruits se trouvent dans un bloc de cellules directement sous un en-tête (FRUITS). Un nom est également attribué à cette rubrique: FruitsHeading:
La formule complète utilisée pour définir la plage dynamique de la Le choix de fruits est:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
FruitsHeadingfait 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 (elle peut être ajustée à votre guise).
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 dans lequel les entrées peuvent être effectuées et non au nombre réel d'entrées.
Découpons maintenant la formule en plusieurs éléments (en codant par couleur chaque élément) pour comprendre son fonctionnement. :
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
L'élément "le plus à l'intérieur" 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 longue de 20 rangées et 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(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),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 de la 20 La plage de 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.
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.
INDEXest normalement utilisé pour sélectionner une certaine valeur (ou plage de valeurs) parmi un bloc de données, en spécifiant une ligne et une colonne spécifiques (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.
Le prochain élément de la formule est la fonction MATCH:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)
La fonction MATCHrenvoie 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 FAUX et la neuvième valeur sera VRAI (puisque la ligne 9 thde la plage est vide).
La fonction MATCH renvoie donc la valeur 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.
Le prochain élément 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 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 AUCUNE des cellules n'est vide, le tableau entier sera rempli de valeurs FAUX. Si MATCH ne trouve pas 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 retourne plutôt la valeur 20 (sachant qu'il doit y avoir 20 entrées dans la liste).
Enfin, OFFSET (FruitsHeading, 1,0, la ci-dessus, 1)renvoie le La plage que nous recherchons actuellement: commencez à la cellule FruitsHeading, descendez d’une rangée et de plus de 0 colonnes, puis sélectionnez une zone de plusieurs rangées car il ya des entrées dans la liste (et une colonne de large). Ainsi, toute la formule retournera la plage contenant uniquement les entrées réelles (jusqu'à la première cellule vide).
Utiliser cette formule pour définir la plage qui est la source du menu déroulant vous permet de modifier librement la liste (ajout ou suppression d'entrées, tant que les entrées restantes commencent dans la cellule du haut et sont contiguës) et que la liste déroulante correspondra toujours à la liste actuelle (voir la figure 6).
Le 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 lui affecter le bloc de liste. nom de plage propre, qui peut ensuite être utilisé dans une formule modifiée. Dans le fichier exemple, une deuxième liste (Noms) utilise cette méthode. Dans ce cas, le nom de la plage NameBlockest attribué à l'ensemble du bloc de liste (sous l'en-tête "NOMS", 40 lignes dans le fichier exemple). La formule alternative pour définir la liste de noms est alors:
=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)
où NamesBlockremplace OFFSET (FruitsHeading, 1,0,20,1) et ROWS (NamesBlock)remplace les 20 (nombre de lignes) de la formule précédente.
Donc, pour les listes déroulantes qui peuvent être facilement modifiées (y compris par d'autres utilisateurs inexpérimentés), essayez d'utiliser des noms de plage dynamique! 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. Profitez!