Pourquoi vous devriez utiliser des gammes nommées dans Excel

Les gammes nommées sont une fonction utile, mais souvent sous-utilisée, de Microsoft Excel.  Les gammes nommées peuvent faciliter les formules plus faciles à comprendre (et à déboguer), simplifier la création de feuilles de calcul complexes et simplifier vos macros.

Une gamme nommée n'est qu'une gamme (soit une seule cellule, soit une gamme de cellules) à laquelle vous attribuez un nom.  Vous pouvez ensuite utiliser ce nom à la place des références de cellules normales dans des formules, dans des macros et pour définir la source de graphiques ou de validation des données.

Utilisation d'un nom de gamme, comme le nombre de taxes, à la place d'une référence de cellule standard, comme la feuille2!$ C 11 $, peut rendre une feuille de calcul plus facile à comprendre et à déboguer / audit.

En utilisant des gammes nommées dans Excel

Par exemple, regardons un bon bon de commande.  Notre fichier comprend un bon de commande à remplir avec une liste déroulante pour sélectionner la méthode d'expédition, plus une deuxième feuille avec un tableau des frais d'expédition, et le taux d'imposition.

La version 1 (sans gammes nommées) utilise des références de cellules de style A1 normales dans ses formules (illustrées dans la barre de formule ci-dessous).

La version 2 utilise des gammes nommées, ce qui rend ses formules beaucoup plus faciles à comprendre.  Les gammes nommées facilitent également la saisie des formules, car Excel affichera une liste de noms, y compris les noms de fonction, que vous pouvez choisir, chaque fois que vous commencez à taper un nom dans une formule.  Double-cliquez sur le nom dans la liste de choix pour l'ajouter à votre formule.

Ouvrant le Gestionnaire de noms fenêtre depuis le Formules L'onglet affiche une liste des noms de plage et des gammes de cellules qu'ils font référence.

Mais les gammes nommées ont également d'autres avantages.  Dans notre exemple de fichiers, la méthode d'expédition est sélectionnée à l'aide d'une liste déroulante (validation des données) dans la cellule B13 sur la feuille1.  La méthode sélectionnée est ensuite utilisée pour rechercher les frais d'expédition sur la feuille2.

Sans gammes nommées, les choix déroulants doivent être saisis manuellement car la validation des données ne vous permettra pas de sélectionner une liste source sur une autre feuille.  Ainsi, tous les choix doivent être entrés deux fois: une fois dans la liste déroulante, et à nouveau sur la feuille2.  De plus, les deux listes doivent correspondre.

Si une erreur est commise dans l'une des entrées dans l'une ou l'autre liste, la formule des coûts d'expédition générera une erreur # N / A lorsque le choix erroné est sélectionné.  Nommer la liste sur la feuille2 comme Méthodes de livraison élimine les deux problèmes.

Vous pouvez référencer une plage nommée lors de la définition de la validation des données pour une liste déroulante en entrant simplement = ShippingMethods Dans le champ source, par exemple.  Cela vous permet d'utiliser une liste de choix qui figurent sur une autre feuille.

Et si la liste déroulante fait référence aux cellules réelles utilisées dans la recherche (pour la formule des coûts d'expédition), les choix déroulants correspondent toujours à la liste de recherche, en évitant les erreurs n / a.

Créer une gamme nommée dans Excel

Pour créer une plage nommée, sélectionnez simplement la cellule ou la gamme de cellules que vous souhaitez nommer, puis cliquez dans le Boîte de nom (Lorsque l'adresse de cellule sélectionnée est normalement affichée, juste à gauche de la barre de formule), saisissez le nom que vous souhaitez utiliser et appuyez sur Entrer.

Vous pouvez également créer une plage nommée en cliquant sur le Nouveau bouton dans la fenêtre du gestionnaire de noms.  Cela ouvre un Nouveau nom fenêtre où vous pouvez entrer le nouveau nom.

Par défaut, la plage à nommer est définie sur n'importe quelle plage sélectionnée lorsque vous cliquez sur le nouveau bouton, mais vous pouvez modifier cette plage avant ou après l'enregistrement du nouveau nom.

Notez que les noms de plage ne peuvent pas inclure des espaces, bien qu'ils puissent inclure des soulignements et des périodes.  Généralement, les noms doivent commencer par une lettre et ne contenir que des lettres, des chiffres, des règles ou des soulignements.

Les noms ne sont pas sensibles à la casse, mais l'utilisation d'une chaîne de mots capitalisés, tels que Taxrate ou décembre 2018Sales, rend les noms plus faciles à lire et à reconnaître.  Vous ne pouvez pas utiliser un nom de plage qui imite une référence de cellule valide, comme Dog26.

Vous pouvez modifier vos noms de plage ou modifier les plages auxquelles ils se réfèrent à l'aide de la fenêtre du gestionnaire de noms.

Notez également que chaque gamme nommée a une portée définie.  Normalement, la portée sera par défaut Cahier de travail, ce qui signifie que le nom de la plage peut être référencé de n'importe où dans le classeur.  Cependant, il est également possible d'avoir deux gammes ou plus avec le même nom sur des feuilles séparées, mais dans le même classeur.

Par exemple, vous pourriez avoir un fichier de données de vente avec des feuilles séparées pour janvier, février, mars, etc.  Chaque feuille pourrait avoir une cellule (gamme nommée) appelée MonthlySales, mais normalement la portée de chacun de ces noms ne serait que la feuille qui le contenait.

Ainsi, la formule = Round (mensuels, 0) donnerait des ventes de février, arrondi au dollar entier le plus proche, si la formule est sur la feuille de février, mais les ventes de mars si sur la feuille de mars, etc.

Pour éviter la confusion dans les classeurs ayant plusieurs gammes sur des feuilles séparées avec le même nom ou simplement des classeurs complexes avec des dizaines ou des centaines de gammes nommées, il peut être utile d'inclure le nom de la feuille dans le cadre de chaque nom de gamme.

Cela rend également chaque nom de plage unique, afin que tous les noms puissent avoir une portée de classeur.  Par exemple, janvier_monthlysales, février_monthlysales, budget_date, order_date, etc.

Deux mises en garde concernant la portée des gammes nommées: (1) Vous ne pouvez pas modifier la portée d'une plage nommée après sa création, et (2) vous ne pouvez spécifier que la portée d'une nouvelle gamme nommée si vous le créez en utilisant le Nouveau bouton dans le Gestionnaire de noms fenêtre.

Si vous créez un nouveau nom de plage en le tapant dans la zone de nom, la portée sera par défaut soit un classeur (si aucune autre plage avec le même nom n'existe), soit la feuille où le nom est créé.  Par conséquent, pour créer une nouvelle gamme nommée dont la portée est limitée à une feuille particulière, utilisez le bouton «nouveau» du gestionnaire de noms.

Enfin, pour ceux qui écrivent des macros, les noms de plage peuvent être facilement référencés dans le code VBA en plaçant simplement le nom de la plage entre crochets.  Par exemple, au lieu de ce livre de travail.Feuilles (1).Cellules (2,3) Vous pouvez simplement utiliser [Salestotal] si ce nom fait référence à cette cellule.

Commencez à utiliser des gammes nommées dans vos feuilles de calcul Excel et vous serez rapidement apprécié les avantages! Apprécier!