Comment créer des plages nommées dans Excel (un guide étape par étape)

Qu'est-ce qu'il y a dans le nom ?

Si vous travaillez avec des feuilles de calcul Excel, cela peut signifier beaucoup de gain de temps et d'efficacité.

Dans ce didacticiel, vous apprendrez à créer des plages nommées dans Excel et à l'utiliser pour gagner du temps.

Plages nommées dans Excel - Une introduction

Si quelqu'un doit m'appeler ou se référer à moi, il utilisera mon nom (au lieu de dire qu'un homme reste dans tel endroit avec telle taille et tel poids).

Droite?

De même, dans Excel, vous pouvez donner un nom à une cellule ou à une plage de cellules.

Maintenant, au lieu d'utiliser la référence de cellule (telle que A1 ou A1:A10), vous pouvez simplement utiliser le nom que vous lui avez attribué.

Par exemple, supposons que vous ayez un ensemble de données comme indiqué ci-dessous :

Dans cet ensemble de données, si vous devez vous référer à la plage qui a la date, vous devrez utiliser A2:A11 dans les formules. De même, pour Sales Rep et Sales, vous devrez utiliser B2:B11 et C2:C11.

Bien que cela ne pose pas de problème lorsque vous n'avez que quelques points de données, mais dans le cas où vous auriez d'énormes ensembles de données complexes, l'utilisation de références de cellules pour faire référence aux données peut prendre beaucoup de temps.

Les plages nommées Excel facilitent la référence aux ensembles de données dans Excel.

Vous pouvez créer une plage nommée dans Excel pour chaque catégorie de données, puis utiliser ce nom à la place des références de cellule. Par exemple, les dates peuvent être nommées « Date », les données du représentant des ventes peuvent être nommées « Rep des ventes » et les données des ventes peuvent être nommées « Ventes ».

Vous pouvez également créer un nom pour une seule cellule. Par exemple, si vous avez le pourcentage de commission de vente dans une cellule, vous pouvez nommer cette cellule comme « Commission ».

Avantages de la création de plages nommées dans Excel

Voici les avantages de l'utilisation de plages nommées dans Excel.

Utiliser des noms au lieu de références de cellules

Lorsque vous créez des plages nommées dans Excel, vous pouvez utiliser ces noms au lieu des références de cellule.

Par exemple, vous pouvez utiliser =SUM(SALES) au lieu de =SUM(C2:C11) pour l'ensemble de données ci-dessus.

Jetez un œil aux formules énumérées ci-dessous. Au lieu d'utiliser des références de cellules, j'ai utilisé les plages nommées.

• Nombre de ventes d'une valeur supérieure à 500 : =COUNTIF(Sales,”>500″)
• Somme de toutes les ventes réalisées par Tom : =SUMIF(SalesRep,"Tom",Sales)
• Commission gagnée par Joe (ventes de Joe multipliées par le pourcentage de commission) :
  =SUMIF(Rep des ventes,"Joe",Ventes)*Commission

Vous conviendrez que ces formules sont faciles à créer et à comprendre (surtout lorsque vous les partagez avec quelqu'un d'autre ou que vous les revisitez vous-même.

Pas besoin de revenir à l'ensemble de données pour sélectionner des cellules

Un autre avantage important de l'utilisation des plages nommées dans Excel est que vous n'avez pas besoin de revenir en arrière et de sélectionner les plages de cellules.

Vous pouvez simplement taper quelques alphabets de cette plage nommée et Excel affichera les plages nommées correspondantes (comme indiqué ci-dessous):

Les plages nommées rendent les formules dynamiques

En utilisant des plages nommées dans Excel, vous pouvez rendre les formules Excel dynamiques.

Par exemple, dans le cas d'une commission de vente, au lieu d'utiliser la valeur 2,5%, vous pouvez utiliser la plage nommée.

Désormais, si votre entreprise décide plus tard d'augmenter la commission à 3%, vous pouvez simplement mettre à jour la plage nommée, et tout le calcul sera automatiquement mis à jour pour refléter la nouvelle commission.

Comment créer des plages nommées dans Excel

Voici trois façons de créer des plages nommées dans Excel :

Méthode #1 - Utilisation de Définir le nom

Voici les étapes pour créer des plages nommées dans Excel à l'aide de Define Name :

• Sélectionnez la plage pour laquelle vous souhaitez créer une plage nommée dans Excel.
• Allez dans Formules -> Définir le nom.
• Dans la boîte de dialogue Nouveau nom, saisissez le nom que vous souhaitez attribuer à la plage de données sélectionnée. Vous pouvez spécifier la portée comme le classeur entier ou une feuille de calcul spécifique. Si vous sélectionnez une feuille particulière, le nom ne serait pas disponible sur d'autres feuilles.
• Cliquez sur OK.

Cela créera une plage nommée SALESREP.

Méthode #2 : Utilisation de la zone de nom

• Sélectionnez la plage pour laquelle vous souhaitez créer un nom (ne sélectionnez pas les en-têtes).
• Allez dans la zone de nom à gauche de la barre de formule et tapez le nom du avec lequel vous souhaitez créer la plage nommée.
• Notez que le nom créé ici sera disponible pour l'ensemble du classeur. Si vous souhaitez le restreindre à une feuille de calcul, utilisez la méthode 1.

Méthode n°3 : Utilisation de l'option Créer à partir de la sélection

C'est la méthode recommandée lorsque vous avez des données sous forme de tableau et que vous souhaitez créer une plage nommée pour chaque colonne/ligne.

Par exemple, dans l'ensemble de données ci-dessous, si vous souhaitez créer rapidement trois plages nommées (Date, Sales_Rep et Sales), vous pouvez utiliser la méthode indiquée ci-dessous.

Voici les étapes pour créer rapidement des plages nommées à partir d'un ensemble de données :

• Sélectionnez l'ensemble des données (y compris les en-têtes).
• Allez dans Formules -> Créer à partir de la sélection (Raccourci clavier - Ctrl + Maj + F3). Cela ouvrira la boîte de dialogue « Créer des noms à partir de la sélection ».
• Dans la boîte de dialogue Créer des noms à partir de la sélection, cochez les options où vous avez les en-têtes. Dans ce cas, nous sélectionnons la ligne du haut uniquement car l'en-tête se trouve dans la ligne du haut. Si vous avez des en-têtes à la fois dans la rangée supérieure et dans la colonne de gauche, vous pouvez choisir les deux. De même, si vos données sont organisées lorsque les en-têtes se trouvent uniquement dans la colonne de gauche, vous ne cochez que l'option Colonne de gauche.

Cela créera trois plages nommées - Date, Sales_Rep et Sales.

Notez qu'il récupère automatiquement les noms dans les en-têtes. S'il y a des espaces entre les mots, il insère un trait de soulignement (car vous ne pouvez pas avoir d'espaces dans les plages nommées).

Convention de dénomination pour les plages nommées dans Excel

Vous devez connaître certaines règles de nommage lors de la création de plages nommées dans Excel :

• Le premier caractère d'une plage nommée doit être une lettre et un caractère de soulignement (_) ou une barre oblique inverse (\). Si c'est autre chose, il affichera une erreur. Les caractères restants peuvent être des lettres, des chiffres, des caractères spéciaux, un point ou un trait de soulignement.
• Vous ne pouvez pas utiliser de noms qui représentent également des références de cellules dans Excel. Par exemple, vous ne pouvez pas utiliser AB1 car il s'agit également d'une référence de cellule.
• Vous ne pouvez pas utiliser d'espaces lors de la création de plages nommées. Par exemple, vous ne pouvez pas avoir un représentant commercial comme plage nommée. Si vous souhaitez combiner deux mots et créer une plage nommée, utilisez un trait de soulignement, un point ou des caractères majuscules pour le créer. Par exemple, vous pouvez avoir Sales_Rep, SalesRep ou SalesRep.
  • Lors de la création de plages nommées, Excel traite les majuscules et les minuscules de la même manière. Par exemple, si vous créez une plage nommée VENTES, vous ne pourrez pas créer une autre plage nommée telle que « ventes » ou « Ventes ».
• Une plage nommée peut comporter jusqu'à 255 caractères.

Trop de plages nommées dans Excel ? Ne t'inquiète pas

Parfois, dans les grands ensembles de données et les modèles complexes, vous pouvez finir par créer de nombreuses plages nommées dans Excel.

Que faire si vous ne vous souvenez pas du nom de la plage nommée que vous avez créée ?

Ne t'inquiète pas - voici quelques conseils utiles.

Obtenir les noms de toutes les plages nommées

Voici les étapes pour obtenir une liste de toutes les plages nommées que vous avez créées :

• Allez dans l'onglet Formules.
• Dans le groupe Défini nommé, cliquez sur Utiliser dans la formule.
• Cliquez sur "Coller les noms".

Cela vous donnera une liste de toutes les plages nommées dans ce classeur. Pour utiliser une plage nommée (dans des formules ou une cellule), double-cliquez dessus.

Affichage des plages nommées correspondantes

• Si vous avez une idée du nom, tapez quelques caractères initiaux et Excel affichera une liste déroulante des noms correspondants.

Comment modifier des plages nommées dans Excel

Si vous avez déjà créé une plage nommée, vous pouvez la modifier en procédant comme suit :

• Allez dans l'onglet Formules et cliquez sur Gestionnaire de noms.
• La boîte de dialogue Gestionnaire de noms répertorie toutes les plages nommées dans ce classeur. Double-cliquez sur la plage nommée que vous souhaitez modifier.
• Dans la boîte de dialogue Modifier le nom, apportez les modifications.
• Cliquez sur OK.
• Fermez la boîte de dialogue Gestionnaire de noms.

Raccourcis de plage nommés utiles (la puissance de F3)

Voici quelques raccourcis clavier utiles qui vous seront utiles lorsque vous travaillerez avec des plages nommées dans Excel :

• Pour obtenir une liste de toutes les plages nommées et la coller dans la formule : F3
• Pour créer un nouveau nom à l'aide de la boîte de dialogue du gestionnaire de noms : Contrôle + F3
• Pour créer des plages nommées à partir de la sélection : Contrôle + Maj + F3

Création de plages nommées dynamiques dans Excel

Jusqu'à présent dans ce didacticiel, nous avons créé des plages nommées statiques.

Cela signifie que ces plages nommées feraient toujours référence au même ensemble de données.

Par exemple, si A1:A10 a été nommé « Ventes », il fera toujours référence à A1:A10.

Si vous ajoutez plus de données de vente, vous devrez alors mettre à jour manuellement la référence dans la plage nommée.

Dans le monde des ensembles de données en constante expansion, cela peut finir par vous prendre beaucoup de temps. Chaque fois que vous obtenez de nouvelles données, vous devrez peut-être mettre à jour les plages nommées dans Excel.

Pour résoudre ce problème, nous pouvons créer des plages nommées dynamiques dans Excel qui prendraient automatiquement en compte les données supplémentaires et les incluraient dans la plage nommée existante.

Par exemple, par exemple, si j'ajoute deux points de données de vente supplémentaires, une plage nommée dynamique ferait automatiquement référence à A1:A12.

Ce type de plage nommée dynamique peut être créé à l'aide de la fonction Excel INDEX. Au lieu de spécifier les références de cellule lors de la création de la plage nommée, nous spécifions la formule. La formule est automatiquement mise à jour lorsque les données sont ajoutées ou supprimées.

Voyons comment créer des plages nommées dynamiques dans Excel.

Supposons que nous ayons les données de vente dans la cellule A2:A11.

Voici les étapes pour créer des plages nommées dynamiques dans Excel :

1. 1. Allez dans l'onglet Formule et cliquez sur Définir le nom.
   2. Dans la boîte de dialogue Nouveau nom, saisissez ce qui suit :
      • Nom : Ventes
      • Portée : Cahier d'exercices
      • Fait référence à : =$A$2 :INDEX($A$2 :$A$100,COUNTIF($A$2 :$A$100,""&""))
   3. Cliquez sur OK.

Terminé!

Vous avez maintenant une plage nommée dynamique avec le nom « Ventes ». Cela se mettrait automatiquement à jour chaque fois que vous y ajouteriez des données ou en supprimeriez des données.

Comment fonctionnent les plages nommées dynamiques ?

Pour expliquer comment cela fonctionne, vous devez en savoir un peu plus sur la fonction Excel INDEX.

La plupart des gens utilisent INDEX pour renvoyer une valeur à partir d'une liste basée sur le numéro de ligne et de colonne.

Mais la fonction INDEX a aussi un autre côté.

Il peut être utilisé pour retourner une référence de cellule lorsqu'il est utilisé comme partie d'une référence de cellule.

Par exemple, voici la formule que nous avons utilisée pour créer une plage nommée dynamique :

=$A$2:INDEX($A$2:$A$100,COUNTIF($A$2:$A$100,""&""))

INDEX($A$2:$A$100,COUNTIF($A$2:$A$100,""&"") -> Cette partie de la formule devrait renvoyer une valeur (qui serait la 10ème valeur de la liste, considérant qu'il y a dix éléments).

Cependant, lorsqu'il est utilisé devant une référence (=$A$2 :INDEX($A$2:$A$100,COUNTIF($A$2:$A$100,""&""))) il renvoie la référence à la cellule au lieu de la valeur.

Par conséquent, ici, il renvoie =$A$2:$A$11

Si nous ajoutons deux valeurs supplémentaires à la colonne des ventes, cela renverrait alors =$A$2:$A$13

Lorsque vous ajoutez de nouvelles données à la liste, la fonction Excel COUNTIF renvoie le nombre de cellules non vides dans les données. Ce numéro est utilisé par la fonction INDEX pour récupérer la référence de cellule du dernier élément de la liste.

Noter:

• Cela ne fonctionnerait que s'il n'y a pas de cellules vides dans les données.
• Dans l'exemple pris ci-dessus, j'ai affecté un grand nombre de cellules (A2:A100) à la formule de plage nommée. Vous pouvez ajuster cela en fonction de votre ensemble de données.

Vous pouvez également utiliser la fonction OFFSET pour créer des plages nommées dynamiques dans Excel. Cependant, comme la fonction OFFSET est volatile, elle peut entraîner un classeur Excel lent. INDEX, en revanche, est semi-volatile, ce qui en fait un meilleur choix pour créer des plages nommées dynamiques dans Excel.

Vous aimerez peut-être également les ressources Excel suivantes :

• Modèles Excel gratuits.
• Formation Excel en ligne gratuite (cours vidéo en ligne en 7 parties).
• Exemples de code de macro Excel utiles.
• 10 exemples avancés de RECHERCHEV Excel.
• Création d'une liste déroulante dans Excel.
• Création d'une plage nommée dans Google Sheets.
• Comment référencer une autre feuille ou un classeur dans Excel