Utilisez des noms de plage dynamique dans Excel pour les listes déroulantes flexibles

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

Pour configurer une liste déroulante simple, sélectionnez la cellule où les données seront entrées, puis cliquez sur La validation des données (sur le Données onglet), sélectionnez la validation des données, choisissez Liste (sous Autoriser :), puis entrez les éléments de la liste (séparés par des virgules) dans le Source: champ (voir figure 1).

Cette deuxième méthode facilite la modification des choix dans la liste, mais l'ajout ou la suppression des éléments peut être problématique.  Étant donné que la gamme nommée (fruichoices, dans notre exemple) fait référence à une gamme fixe de cellules ($ H 3: $ H 10 $ comme indiqué), si plus de choix sont ajoutés aux cellules H11 ou moins, 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 de poires et de fraises sont effacées, elles n'apparaîtront plus dans la liste déroulante, mais à la place, la liste déroulante comprendra deux choix «vides» car la liste déroulante fait toujours référence à l'ensemble de la gamme FrUitchoices, y compris les cellules vides H9 et H10.

Pour ces raisons, lors de l'utilisation d'une gamme 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 gamme comme source des choix déroulants.  Un nom de plage dynamique est celui qui se développe automatiquement (ou les contrats) pour correspondre exactement à la taille d'un bloc de données à mesure que les entrées sont ajoutées ou supprimées.  Pour ce faire, vous utilisez un formule, plutôt qu'une gamme fixe d'adresses cellulaires, pour définir la plage nommée.

Comment configurer une plage dynamique dans Excel

Un nom de gamme normal (statique) fait référence à une gamme spécifiée de cellules ($ 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 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és).

Examinons cette formule en détail.  Les choix pour les fruits sont dans un bloc de cellules directement en dessous d'une rubrique (DES FRUITS).  Ce titre se voit également attribuer un nom: Tête de fruit:

La formule entière utilisée pour définir la plage dynamique des choix des fruits est:

= Offset (Fruitading, 1,0, IFERROR (Match (True, Index (Isblank (Offset (Fruitading, 1,0,20,1)), 0,0), 0) -1,20), 1)

Tête de fruit fait référence à l'en-tête qui est une ligne au-dessus de la première entrée de la liste.  Le numéro 20 (utilisé deux fois dans la formule) est la taille maximale (nombre de lignes) pour la liste (cela peut être ajusté comme souhaité).

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 où des entrées supplémentaires pourraient être ajoutées.  Le numéro 20 fait référence à l'ensemble du bloc où les entrées peuvent être faites, et non au nombre réel d'entrées.

Maintenant, décomposons la formule en morceaux (codant en couleur chaque pièce), pour comprendre comment cela fonctionne:

= Décalage (chef de fruit, 1,0, iFerror (match (vrai, index (Isblank (Offset (Tree Fruit, 1,0,20,1)), 0,0), 0) -1,20), 1)

La pièce «la plus intérieure» est Offset (Tree Fruit, 1,0,20,1).  Cela fait référence au bloc de 20 cellules (sous la cellule de tête de fil à fruit) où les choix peuvent être entrés.  Cette fonction de décalage dit essentiellement: Commencez par Tête de fruit cellule, descendez 1 ligne et supérieure à 0 colonnes, puis sélectionnez une zone de 20 lignes de long et 1 colonne de large.  Cela nous donne donc le bloc de 20 rangées où les choix de fruits sont entrés.

Le prochain morceau de la formule est le Isblank fonction:

= Décalage (chef de fruit, 1,0, iFerror (match (vrai, index (Isblank (ce qui précède),0,0), 0) -1,20), 1)

Ici, la fonction de décalage (expliqué ci-dessus) a été remplacée par «ce qui précède» (pour rendre les choses plus faciles à lire).  Mais la fonction Isblank fonctionne sur la gamme de cellules à 20 rangées que la fonction de décalage définit.

Isblank crée ensuite un ensemble de 20 valeurs vraies et fausses, indiquant si chacune des cellules individuelles de la plage de 20 rangées référencées par la fonction de décalage est vide (vide) ou non.  Dans cet exemple, les 8 premières valeurs de l'ensemble seront fausses car les 8 premières cellules ne sont pas vides et les 12 dernières valeurs seront vraies.

La prochaine pièce de la formule est la fonction d'index:

= Offset (Fruitading, 1,0, IFERROR (Match (True,Index (ci-dessus, 0,0),0) -1,20), 1)

Encore une fois, «The ci-dessus» fait référence aux fonctions ISBLANK et à décalage décrites ci-dessus.  La fonction d'index renvoie un tableau contenant les 20 valeurs vraies / fausses créées par la fonction ISBLANK.

INDICE est normalement utilisé pour choisir une certaine valeur (ou plage de valeurs) à partir d'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) fait renvoyer l'index un tableau contenant le bloc de données entier.

La prochaine pièce de la formule est la fonction de match:

= Décalage (chef de fruit, 1,0, idirror (Match (vrai, ce qui précède, 0) -1,20), 1)

Le CORRESPONDRE La fonction renvoie la position de la première valeur vraie, 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 fausses et la neuvième valeur sera vraie (puisque le 9^e La ligne dans la gamme est vide).

Ainsi, la fonction de correspondance renvoie la valeur de 9.  Dans ce cas, cependant, nous voulons vraiment savoir combien de participations sont dans la liste, donc la formule soustrait 1 de la valeur de correspondance (qui donne la position de la dernière entrée).  Donc, finalement, Match (vrai, ce qui précède, 0) -1 renvoie la valeur de 8.

Le prochain morceau de la formule est la fonction IFERROR:

= Décalage (prise en fruit, 1,0,IIrror (ce qui précède, 20),1)

La fonction IFERROR renvoie une valeur alternative, si la première valeur spécifiée se traduit par une erreur.  Cette fonction est incluse car, si le bloc entier des cellules (les 20 lignes) est rempli d'entrées, la fonction de correspondance renvoie une erreur.

En effet.  Si la correspondance ne peut pas trouver la valeur cible (true) dans le tableau qu'il recherche, il renvoie une erreur.

Ainsi, si la liste entière est complète (et par conséquent, la correspondance 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).

Enfin, Décalage (chef de fruit, 1,0, ce qui précède, 1) Renvoie la plage que nous recherchons réellement: commencez à la cellule de vol de fruit, descendez 1 rangée et plus de 0 colonnes, puis sélectionnez une zone qui est aussi longtemps que des entrées dans la liste (et 1 colonne de large).  Ainsi, toute la formule ensemble renvoie 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 gamme qui est la source de la liste déroulante signifie que vous pouvez librement modifier la liste (en ajoutant ou en supprimant les entrées, tant que les entrées restantes commencent à la cellule supérieure et sont contiguës) et que la liste déroulante reflètera toujours le courant actuel liste (voir figure 6).

L'exemple de fichier (listes dynamiques) qui a été utilisée ici est incluse et est téléchargeable à partir de ce site Web. Les macros ne fonctionnent cependant pas, car WordPress n'aime pas les livres Excel avec des macros.

Comme alternative à la spécification du 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 l'exemple de fichier, 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 l'exemple de fichier) se voient attribuer le nom de la plage de Berceau.  La formule alternative pour définir la liste des noms est alors:

= Offset (NameSheading, 1,0, iFerror (Match (true, index (Isblank (Boucle de noms), 0,0), 0) -1,Lignes (namesblock)),1)

où Boucle de noms remplace le décalage (prise en fruit, 1,0,20,1) et Lignes (namesblock) remplace le 20 (nombre de lignes) dans la formule précédente.

Ainsi, 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 plages dynamiques!  Et notez que, bien que cet article se soit concentré sur les listes déroulantes, les noms de plages dynamiques peuvent être utilisés partout où vous devez référencer une plage ou une liste qui peut varier en taille. Apprécier!