Excel plage nommée – comment définir et utiliser des noms dans Excel

Le tutoriel explique ce qu’est un Excel nom et montre comment définir un nom pour une cellule, une plage, une constante ou une formule. Vous apprendrez également à modifier, filtrer et supprimer des noms définis dans Excel.

Les noms dans Excel sont une chose paradoxale: étant l’une des fonctionnalités les plus utiles, ils sont souvent considérés comme inutiles ou ringards. La raison en est que très peu d’utilisateurs comprennent l’essence des noms Excel., Ce tutoriel vous apprendra non seulement à créer une plage nommée dans Excel, mais montrera également comment tirer parti de cette fonctionnalité pour rendre vos formules beaucoup plus faciles à écrire, à lire et à réutiliser.,option

Que signifie le nom dans Excel?,

Dans la vie quotidienne noms sont largement utilisés pour désigner des personnes, des objets et des lieux géographiques. Par exemple, au lieu de dire « la ville située à la latitude 40.7128° N et à la longitude 74.0059° W, vous dites simplement « New York City ».

De même, dans Microsoft Excel, vous pouvez donner un nom lisible par l’homme à une seule cellule ou à une plage de cellules, et faire référence à ces cellules par nom plutôt que par référence.,

Par exemple, pour trouver le total des ventes (B2:B10) pour un article spécifique (E1), vous pouvez utiliser la formule suivante:

=SUMIF($A$2:$A$10, $E$1, $B$2:$B$10)

Ou, vous pouvez donner des noms significatifs aux plages et aux cellules individuelles et fournir ces noms à la formule:

=SUMIF(items_list, item, sales)

En regardant la capture d’écran ci-dessous, laquelle des deux formules est la plus facile à comprendre?,

Types de noms Excel

Dans Microsoft Excel, vous pouvez créer et utiliser deux types de noms:

Nom défini – un nom qui fait référence à une seule cellule, une plage de cellules, une valeur constante ou une formule. Par exemple, lorsque vous définissez un nom pour une plage de cellules, cela s’appelle une plage nommée ou une plage définie. Ces noms font l’objet du tutoriel d’aujourd’hui.

Nom du tableau – nom d’un tableau Excel créé automatiquement lorsque vous insérez un tableau dans une feuille de calcul (Ctrl + T). Pour plus d’informations sur les tableaux Excel, veuillez consulter Comment créer et utiliser un tableau dans Excel.,

Comment créer une plage nommée Excel

Dans l’ensemble, il existe 3 façons de définir un nom dans Excel: Zone de nom, bouton Définir le nom et Gestionnaire de noms Excel.

Tapez un nom dans la zone Nom

La zone Nom dans Excel est le moyen le plus rapide de créer une plage nommée:

1. Sélectionnez une cellule ou une plage de cellules que vous souhaitez nommer.
2. Saisissez un nom dans la zone Nom.
3. Appuyez sur la touche Entrée.

Voilà, une nouvelle plage nommée Excel est créée!,

Créez un nom en utilisant l’option Définir le nom

Une autre façon de créer une plage nommée dans Excel est la suivante:

1. Sélectionnez la ou les cellules.
2. Dans l’onglet Formules, dans le groupe Définir les noms, cliquez sur le bouton Définir le nom.
3. Dans la boîte de dialogue Nouveau Nom, préciser trois choses:
   • Dans la zone Nom, tapez le nom de la plage.
   • Dans la liste déroulante Scope, définissez le nom scope (Classeur par défaut).
   • Dans la case Se réfère à, cochez la référence et corrigez-la si nécessaire.
4. Cliquez sur OK pour enregistrer les modifications et fermer la boîte de dialogue.,

Par rapport à la méthode précédente, l’utilisation de Define Name dans Excel prend quelques clics supplémentaires, mais elle fournit également quelques options supplémentaires telles que la définition de la portée du nom et l’ajout d’un commentaire expliquant quelque chose sur le nom., De plus, la fonction Définir le nom d’Excel vous permet de créer un nom pour une constante ou une formule.

Créez une plage nommée à l’aide du gestionnaire de noms Excel

Habituellement, le gestionnaire de noms dans Excel est utilisé pour travailler avec des noms existants. Cependant, cela peut également vous aider à créer un nouveau nom. Voici comment:

1. Allez dans l’onglet Formules > Groupe de noms définis, cliquez sur Gestionnaire de noms. Ou, appuyez simplement sur Ctrl + F3 (ma façon préférée).,
2. Dans le coin supérieur gauche de la fenêtre de dialogue du Gestionnaire de noms, cliquez sur le bouton Nouveau button:
   
3. Cela ouvrira la boîte de dialogue Nouveau nom dans laquelle vous configurez un nom comme indiqué dans la section précédente.

Comment créer un nom Excel pour une constante

En plus des plages nommées, Microsoft Excel vous permet de définir un nom sans référence de cellule qui fonctionnera comme une constante nommée. Pour créer un tel nom, utilisez la fonction Excel Define Name ou Name Manager comme expliqué ci-dessus.

Et maintenant, vous pouvez utiliser ce nom n’importe où dans vos formules pour convertir USD en EUR:

Dès que le taux de change change, vous mettez à jour la valeur uniquement dans un emplacement central, et toutes vos formules seront recalculées en une seule étape!,

Comment définir un nom pour une formule

De la même manière, vous pouvez donner un nom à une formule Excel, par exemple, celle qui renvoie le nombre de cellules non vides dans la colonne A, à l’exclusion de la ligne d’en-tête (-1):

=COUNTA(Sheet5!$A:$A)-1

Maintenant, chaque fois que vous voulez savoir combien d’éléments il y a dans la colonne A sur la feuille 5, sans inclure l’en-tête de colonne, tapez simplement le signe d’égalité suivi du nom de votre formule dans n’importe quelle cellule, comme ceci: =Items_count

Comment nommer les colonnes dans Excel (noms créez rapidement des noms pour chaque colonne et/ou ligne en fonction de leurs étiquettes:

1. Sélectionnez la table entière, y compris les en-têtes de colonne et de ligne.,
2. Accédez à l’onglet Formules > Définissez le groupe de noms, puis cliquez sur le bouton Créer à partir de la sélection. Ou, appuyez sur le raccourci clavier Ctrl + Maj + F3.
3. Quoi qu’il en soit, la boîte de dialogue Créer des noms à partir de la sélection s’ouvrira. Sélectionnez la colonne ou la ligne avec des en-têtes, ou les deux, puis cliquez sur OK.,

Dans cet exemple, nous avons des en-têtes dans la ligne supérieure et la colonne de gauche, nous sélectionnons donc ces deux options:

En conséquence, Excel créera 7 plages nommées, reprenant automatiquement les noms des en-têtes:

• Pommes, Bananes, Citrons et Oranges pour les lignes, et
• Jan, Feb et Mar pour

Excel dynamic named range

Dans tous les exemples précédents, nous avons eu affaire à des plages nommées statiques qui font toujours référence aux mêmes cellules, ce qui signifie que vous devrez mettre à jour la référence de plage manuellement chaque fois que vous souhaitez ajouter de nouvelles données à la plage nommée.

Si vous travaillez avec des ensembles de données extensibles, il est logique de créer une plage nommée dynamique qui accueille automatiquement les données nouvellement ajoutées.,

Les conseils détaillés sur la façon de créer une plage nommée dynamique dans Excel peuvent être trouvés ici:

• Formule de décalage Excel pour créer une plage dynamique
• Formule d’index pour créer une plage dynamique

Règles de nommage Excel

Lors de la création d’un nom dans Excel, il y a quelques règles à retenir:

niveau, dans lequel le nom est reconnu., Il peut s’agir de:

• Feuille de calcul spécifique – le niveau local de la feuille de calcul
• Classeur – le niveau global du classeur

Noms des niveaux de feuille de calcul

Un nom de niveau de feuille de calcul est reconnu dans la feuille de calcul où il se trouve. Par exemple, si vous créez une plage nommée et définissez sa portée sur Sheet1, elle ne sera reconnue que dans Sheet1.

Pour pouvoir utiliser un nom au niveau de la feuille de calcul dans une autre feuille de calcul, vous devez préfixer le nom de la feuille de calcul suivi du point d’exclamation (!), comme ceci:

Sheet1!,items_list

Pour référencer un nom au niveau de la feuille de calcul dans un autre classeur, vous devez également inclure le nom du classeur entre crochets:

Sheet1!items_list

Si le nom de la feuille ou le nom du classeur contient des espaces, ils doivent être placés entre guillemets simples:

‘Sheet1’!items_list

Noms de niveau de classeur

Un nom de niveau de classeur est reconnu dans l’ensemble du classeur, et vous pouvez vous y référer simplement par son nom à partir de n’importe quelle feuille du même classeur.,

A Utiliser un nom au niveau du classeur dans un autre classeur, précéder le nom du classeur (y compris l’extension) suivi du point d’exclamation:

Book1.xlsx!items_list

Priorité de la portée

Un nom défini doit être unique dans sa portée. Vous pouvez utiliser le même nom dans différentes étendues, mais cela peut créer un conflit de noms. Pour éviter cela, par défaut, le niveau de la feuille de calcul a priorité sur le niveau du classeur.,

S’il existe quelques plages de noms identiques avec des étendues différentes, et que vous souhaitez utiliser le nom du niveau du classeur, préfixez le nom avec le nom du classeur comme si vous faisiez référence à un nom dans un autre classeur, par exemple: Book1.xlsx!données. De cette façon, le conflit de noms peut être remplacé pour toutes les feuilles de calcul à l’exception de la première feuille, qui utilise toujours le nom de niveau de feuille de calcul local.,

Excel Name Manager – un moyen rapide de modifier, supprimer et filtrer les noms

Comme son nom l’indique, le gestionnaire de noms Excel est spécialement conçu pour gérer les noms: modifier, filtrer ou supprimer des noms existants ainsi que d’en créer de nouveaux.

Il existe deux façons d’accéder au Gestionnaire de noms dans Excel:

• Dans l’onglet Formules, dans le groupe Définir les noms, cliquez sur le Gestionnaire de noms
  
• Appuyez sur le raccourci Ctrl + F3.

Quoi qu’il en soit, la fenêtre de dialogue Gestionnaire de noms s’ouvrira, vous permettant de voir tous les noms dans le classeur actuel en un coup d’œil., Maintenant, vous pouvez sélectionner le nom avec lequel vous souhaitez travailler et cliquer sur l’un des 3 boutons en haut de la fenêtre pour effectuer l’action correspondante: modifier, supprimer ou filtrer.

Comment faire pour modifier une plage nommée dans Excel

Pour changer un Excel nom, ouvrez le Gestionnaire de noms, sélectionnez le nom, puis cliquez sur le bouton Modifier… Cela ouvrira la boîte de dialogue Modifier le nom où vous pouvez modifier le nom et la référence. La portée du nom ne peut pas être modifiée.

Astuce. Pour modifier une référence de nom, vous n’avez pas besoin d’ouvrir la boîte de dialogue Modifier le nom., Sélectionnez simplement le nom d’intérêt dans le Gestionnaire de noms Excel et tapez une nouvelle référence directement dans la zone Se réfère à, ou cliquez sur le bouton à droite et sélectionnez la plage souhaitée sur la feuille. Après avoir cliqué sur le bouton Fermer, Excel vous demandera si vous souhaitez enregistrer les modifications, et vous cliquez sur Oui.

Comment filtrer les noms dans Excel

Si vous avez beaucoup de noms dans un certain classeur, cliquez sur le bouton Filtrer dans le coin supérieur droit de la fenêtre du Gestionnaire de noms Excel pour afficher uniquement les noms pertinents à un moment donné., Les filtres suivants sont disponibles:

• Noms avec ou sans erreurs
• Noms définis ou noms de table

Comment supprimer une plage nommée dans Excel

Pour supprimer une plage nommée, sélectionnez-la dans le Gestionnaire de noms et cliquez sur le bouton Supprimer en haut.

Pour supprimer plusieurs noms, cliquez sur le prénom, puis appuyez sur la touche Ctrl et maintenez-la enfoncée tout en cliquant sur les autres noms que vous souhaitez supprimer. Cliquez ensuite sur le bouton Supprimer et tous les noms sélectionnés seront supprimés en une seule fois.,

Pour supprimer tous les noms définis dans un classeur, sélectionnez le prénom dans la liste, maintenez la touche Maj enfoncée, puis cliquez sur le nom de famille. Relâchez la touche Maj et cliquez sur Supprimer.

Comment supprimer des noms définis avec des erreurs

Si vous avez un certain nombre de noms non valides avec des erreurs de référence, cliquez sur le bouton Filtrer> Noms avec des erreurs pour les filtrer:

Après cela, sélectionnez tous les noms filtrés comme expliqué ci-dessus (en utilisant la touche Maj), puis cliquez sur le bouton Supprimer.

Top 5 avantages de l’utilisation des noms dans Excel

Jusqu’à présent, dans ce tutoriel, nous nous sommes principalement concentrés sur les choses pratiques qui couvrent différents aspects de la création et de l’utilisation de plages nommées dans Excel. Mais vous pouvez être curieux de savoir ce qui est si spécial à propos des noms Excel qui en vaut la peine? Les cinq principaux avantages de l’utilisation de noms définis dans Excel suivent ci-dessous.,

Les noms Excel facilitent la création et la lecture de formules

Vous n’avez pas besoin de taper des références complexes ou de faire des allers-retours en sélectionnant des plages sur la feuille. Commencez simplement à taper le nom que vous souhaitez utiliser dans la formule, et Excel affichera une liste de noms correspondants parmi lesquels vous pourrez choisir., Double-cliquez sur le nom souhaité et Excel l’insérera immédiatement dans la formule:

Les noms Excel permettent de créer des formules extensibles

En utilisant des plages nommées dynamiques, vous pouvez créer une formule » dynamique  » qui inclut automatiquement de nouvelles données dans les calculs sans que vous ayez à mettre

Les noms Excel facilitent la réutilisation des formules

Les noms Excel facilitent la copie d’une formule sur une autre feuille ou le portage d’une formule dans un classeur différent., Tout ce que vous avez à faire est de créer les mêmes noms dans le classeur de destination, de copier/coller la formule telle quelle et vous la ferez fonctionner immédiatement.

Les plages nommées simplifient la navigation

Pour accéder rapidement à une plage nommée spécifique, il suffit de cliquer sur son nom dans la zone Nom. Si une plage nommée réside sur une autre feuille, Excel vous mènera automatiquement à cette feuille.

Les plages nommées permettent de créer des listes déroulantes dynamiques

Pour créer une liste déroulante extensible et actualisable, créez d’abord une plage nommée dynamique, puis créez une liste de validation de données basée sur cette plage. Les instructions détaillées étape par étape peuvent être trouvées ici: Comment créer une liste déroulante dynamique dans Excel.,

Excel plage nommée – trucs et astuces

Maintenant que vous connaissez les bases de la création et l’utilisation des noms dans Excel, permettez-moi de partager quelques conseils qui peuvent s’avérer utiles dans votre travail.

Comment obtenir une liste de tous les noms dans le classeur

Pour obtenir plus concrètement la liste de tous les noms dans un classeur en cours, procédez de la manière suivante:

1. Sélectionnez la première cellule de la plage où vous souhaitez que les noms apparaissent.
2. Allez dans l’onglet Formules > Définissez un groupe de noms, cliquez sur Utiliser dans les formules, puis cliquez sur Coller les noms… Ou appuyez simplement sur la touche F3.,
3. Dans la boîte de dialogue Coller les noms, cliquez sur Coller la liste.

Cela insérera tous les noms Excel avec leurs références dans la feuille de calcul actuelle, en commençant par la cellule sélectionnée.

Noms Excel absolus vs noms Excel relatifs

Par défaut, les noms Excel se comportent comme des références absolues – verrouillées à des cellules spécifiques. Cependant, il est possible de faire une plage nommée par rapport à la position de la cellule active au moment où le nom est défini., Les noms relatifs se comportent comme des références relatives – sont modifiés lorsque la formule est déplacée ou copiée dans une autre cellule.

En fait, je ne peux penser à aucune raison pour laquelle on voudrait faire une plage nommée relative, sauf peut-être quand une plage se compose d’une seule cellule. Par exemple, créons un nom relatif qui fait référence à une cellule une colonne à gauche de la cellule actuelle, dans la même ligne:

1. Sélectionnez la cellule B1.
2. Appuyez sur Ctrl + F3 pour ouvrir le Gestionnaire de noms Excel, puis cliquez sur le Nouveau…
3. Dans la zone Nom, tapez le nom souhaité, par exemple, item_left.,
4. Dans la zone Se réfère à, tapez =A1.
5. Cliquez sur OK.

Maintenant, nous allons voir ce qui se passe lorsque nous utilisons le item_left nom dans une formule, par exemple:

Où items_list fait référence à $A$2:$A$10 et les ventes se réfère à $B$2:$B$10 dans le tableau ci-dessous.,

Lorsque vous entrez la formule dans la cellule E2, puis la copiez dans la colonne, elle calculera le total des ventes pour chaque produit individuellement car item_left est un nom relatif et sa référence s’ajuste en fonction de la position relative de la colonne et de la ligne où la formule est copiée:

Comment appliquer des noms Excel vos formules, Excel ne changera pas automatiquement les références aux noms appropriés., Cependant, au lieu de remplacer les références par des noms à la main, vous pouvez demander à Excel de faire le travail pour vous. Voici comment:

1. Sélectionnez une ou plusieurs cellules de formule que vous souhaitez mettre à jour.
2. Allez à l’onglet Formules > Définir des Noms de groupe, et cliquez sur Définir le Nom de > Appliquer les Noms…
   
3. Dans l’Appliquer Noms de boîte de dialogue, cliquez sur les noms que vous souhaitez appliquer, puis cliquez sur OK., Si Excel est capable de faire correspondre l’un des noms existants avec les références utilisées dans vos formules, les noms seront automatiquement sélectionnés pour vous:
   

De plus, deux autres options sont disponibles (sélectionnées par défaut):

• Ignorer Relatif/Absolu – cochez cette case si vous souhaitez qu’Excel n’applique que les noms ayant le même type de référence: remplacer les références relatives par des noms relatifs et les références absolues par des noms absolus.,
• Utiliser les noms de ligne et de colonne – si cette option est sélectionnée, Excel renommera toutes les références de cellule pouvant être identifiées comme l’intersection d’une ligne et d’une colonne nommées. Pour plus de choix, cliquez sur les options

Raccourcis de noms Excel

Comme c’est souvent le cas dans Excel, les fonctionnalités les plus populaires sont accessibles de plusieurs façons: via le ruban, le menu contextuel et les raccourcis clavier. Les plages nommées Excel ne font pas exception. Voici trois raccourcis utiles pour travailler avec des noms dans Excel:

• Ctrl + F3 pour ouvrir le Gestionnaire de noms Excel.,
• Ctrl + Maj + F3 pour créer des plages nommées à partir de la sélection.
• F3 pour obtenir une liste de tous les noms Excel dans un classeur.

Erreurs de nom Excel (#REF et #NAME)

Par défaut, Microsoft Excel fait de son mieux pour garder vos noms définis cohérents et valides en ajustant automatiquement les références de plage lorsque vous insérez ou supprimez des cellules dans une plage nommée existante. Par exemple, si vous avez créé une plage nommée pour les cellules A1:A10, puis que vous insérez une nouvelle ligne n’importe où entre les lignes 1 et 10, la référence de plage deviendra A1:A11., De même, si vous supprimez des cellules entre A1 et A10, votre plage nommée se contractera en conséquence.

Cependant, si vous supprimez toutes les cellules qui composent une plage nommée Excel, le nom devient invalide et affiche un #REF! erreur dans le Gestionnaire de noms. La même erreur apparaîtra dans une formule faisant référence à ce nom:

Si une formule fait référence à un nom non existant (mal typé ou supprimé), le #NAME? erreur s’affiche. Dans les deux cas, ouvrez le Gestionnaire de noms Excel et vérifiez la validité de vos noms définis (le moyen le plus rapide est de filtrer les noms avec des erreurs).,

Voici comment vous créez et utilisez des noms dans Excel. Je vous remercie d’avoir lu et j’espère vous voir sur notre blog la semaine prochaine!

• Comment faire pour créer et utiliser la dynamique d’une plage nommée dans Excel
• Base de formules Excel & fonctions avec des exemples
• Microsoft Excel formules avec des exemples