Découvrir les plages nommées d’Excel : utilisation et intérêt !

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

Dans ce tutoriel, nous allons découvrir un outil très puissant d’Excel : les plages nommées. Celles-ci vont en effet permettre de donner un nom symbolique à une ou plusieurs cellules. Nous pourrons ensuite utiliser ce nom exprimé dans un langage compréhensif pour faire référence aux cellules, que ce soit pour les sélectionner, ou encore pour les utiliser dans des formules.

 

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

Tutoriel Vidéo

 

Partie 1 : Appeler une cellule par son nom

 

Partie 2 : Les noms de cellules

 

1. Qu’est-ce qu’une cellule nommée ou une plage nommée ?

Dans Excel, pour pouvoir accéder rapidement aux données stockées dans les cellules, nous utilisons ce que nous appelons une référence.

Nous avons déjà eu l’occasion d’aborder cette notion et nous y avons notamment découvert qu’il existe des références relatives et des références absolues.

La différence entre l’un et l’autre, c’est que dans le cas d’une référence absolue, si nous étendons la formule sur d’autres cellules de la feuille de calcul, les coordonnées resteront les mêmes.

C’est ce que nous pouvons identifier grâce à la présence du dollar (« $ »).

Mais il faut également savoir qu’il est possible d’utiliser ce que l’on appelle une cellule nommée (ou plus simplement un « nom »).

Dans ce cas-là, nous allons donner un nom à une cellule, et c’est à partir de ce nom, que nous ferons appel à celle-ci.

Cela nous permettra donc d’obtenir une méthode claire et ludique pour faire référence aux cellules.

Évidemment, il va également être possible de créer non pas une simple cellule nommée, mais directement une plage nommée constituée d’un ensemble de cellules, que celle-ci soit adjacentes ou non.

Ainsi, imaginons que souhaitions utiliser le taux de marge enregistré dans la cellule C8 pour l’utiliser dans une formule.

Nous pourrons accéder à celle-ci de plusieurs manières :

  • Pour le premier cas, nous allons tout simplement utiliser les coordonnées de la cellule, c’est-à-dire C4
  • Pour le second cas, nous allons faire référence à celle-ci avec une référence absolue en insérant des $ : $C$4
  • Et enfin, pour le dernier cas - qui est justement celui qui nous intéresse ici - nous allons nommer la cellule.

Pour cela, nous allons tout simplement saisir le nom à lui donner dans la zone des noms, afin de créer la cellule nommée de manière très rapide : « _tauxMarge » :

Excel formation - les noms de cellules - 01

Et c’est ensuite en utilisant ce nom « _tauxMarge » que nous pourrons accéder aux informations de la cellule :

Excel formation - les noms de cellules - 02

Maintenant, si nous étendons les trois formules sur les cellules située en dessous, nous allons pouvoir faire un certain nombre de constatations :

  • Dans le premier cas, lorsque nous utilisons une référence relative, la formule ne sera plus correcte étant donné qu’Excel va déployer la formule en décalant également la référence de la cellule dans laquelle se trouve le taux de marge, le résultat sera donc erroné.
  • Dans le cas de la référence absolue, la formule restera correcte et le résultat correspondra effectivement à la marge de brut dégagée par l’entreprise.
  • Et enfin, dans le troisième et dernier cas, lorsque nous utilisons une cellule nommée, alors le résultat sera toujours correct, étant donné que celui-ci est égal à celui que nous avons juste avant. Une cellule nommée est donc une référence absolue.

Excel formation - les noms de cellules - 03

Mais en plus de ça, si nous regardons le détail de la formule, nous allons pouvoir retrouver immédiatement à quoi correspond la cellule que nous avons utilisé, c’est-à-dire au taux de marge.

Ici, le cas est très simple, mais nous pouvons imaginer avoir besoin de créer des applications Excel beaucoup plus complexes. Et dans ce cas-là, il sera bien plus intéressant de savoir à quoi font référence les différentes cellules utilisées dans les formules, sans avoir besoin d’aller retourner sur celle-ci, en sachant qu’il est possible qu’elle soit sur une autre feuille de calcul.

 

2. Création des cellules et des plages nommées

Comme nous venons de le voir dans la partie précédente, la création d’une cellule nommée est très simple.

Il suffit en effet de sélectionner une cellule, puis de saisir le nom dans la zone des noms, situé es en haut à gauche, juste à côté de la barre des formules.

Si nous voulons créer une plage nommé constituée de plusieurs cellules, c’est exactement pareil : nous commençons par sélectionner les cellules en question, en sachant que nous pouvons évidemment utiliser des cellules qui ne sont pas adjacentes (dans ce cas, là, nous maintiendrons la touche [Ctrl] du clavier enfoncée) :

Excel formation - les noms de cellules - 04

Au moment où nous allons saisir le nom de la cellule ou de la plage, nous allons devoir respecter un certain nombre de conventions :

  • Tout d’abord, il ne doit pas y avoir d’espace dans le nom.
  • Ensuite, le premier caractère doit obligatoirement être une lettre, un tiret de soulignement (« _ ») ou un antislash (« \ »). À partir du second caractère, il sera également possible d’utiliser des chiffres.
  • Bien entendu, un nom de cellule ne peut pas correspondre à une référence de cellule (« A1 », « EE56 »…)
  • Les noms « r » et « c » sont interdits, car ils désignent une ligne (« row » en anglais) et une colonne (« column »)
  • L’utilisation des accents est autorisée, mais par convention, il vaut mieux les éviter lors de la création des noms de cellule.
  • Afin, sachez que la casse n’est pas importante, donc « _tauxMarge » et « _TAUXMARGE » sont reconnus de la même manière, Excel procédera automatiquement à la correction.

Le fait de créer une cellule ou une plage nommée comme nous venons de le faire permet de créer rapidement un nom sur une cellule, mais la méthode conventionnelle consiste à utiliser le menu Formule et de cliquer sur le bouton « Définir un nom » :

Excel formation - les noms de cellules - 05

Excel affiche alors une boîte de dialogue dans lequel nous allons tout d’abord pouvoir saisir le nom à donner à la plage de cellules, en respectant les conventions que nous en vues juste avant :

Excel formation - les noms de cellules - 06

Ensuite, nous allons pouvoir définir la zone de fonctionnement du nom.

En effet, le nom peut être reconnu, soit au niveau du classeur, soit au niveau de la feuille de calcul.

L’intérêt d’utiliser une zone étendue au niveau du classeur, c’est que nous allons pouvoir utiliser la cellule nommée à n’importe quel endroit de celui-ci.

En revanche, étant donné que nous ne pouvons utiliser que des noms uniques, nous ne pourrons donc pas retrouver le même nom à plusieurs endroits du classeur.

C’est pourquoi nous pouvons choisir de limiter le champ de reconnaissance du nom uniquement à la feuille de calcul active, et donc dans ce cas-là nous pourrons utiliser un même nom sur différentes feuilles.

Ensuite, si nous le souhaitons, nous pouvons ajouter un commentaire facultatif qui nous permettra de donner des informations supplémentaires quant au nom que nous affectons à cellule ou à la plage de cellules.

Pour finir, nous allons voir, sélectionner la où les cellules que nous voulons affecter au nom, en sachant qu’Excel insère directement dans le champ correspondant, les références aux cellules qui étaient préalablement sélectionnées.

 

3. Comment modifier une plage nommée

Une fois que nous avons créé notre plage nommée, il est possible que nous ayons besoin de modifier celle-ci.

Cela nous permettra par exemple de changer le nom correspondant, ou alors de changer la référence aux cellules liées.

Pour cela nous allons nous rendre le menu « Formules » et cliquer sur le bouton « Gestionnaire de noms » afin de retrouver tous les noms disponibles sur le classeur :

Excel formation - les noms de cellules - 07

Nous pouvons également afficher cette boîte de dialogue en utilisant le raccourci clavier [Ctrl]+[F3].

Ensuite, nous sélectionnons le nom afin de pouvoir effectuer les modifications voulues.

Une fois que celle-ci sont terminées, nous appuyons sur la coche verte afin de valider la modification des paramètres.

 

4. Comment créer des plages automatiquement en fonction des en-têtes de tableau

Dans un tutoriel précédent, que vous pouvez consulter en cliquant ici, nous avions vu qu’il existe une méthode très simple qui permet de créer automatiquement les noms des plages de cellule correspondant à un tableau en fonction des en-têtes de ces derniers.

Pour cela, nous commençons par sélectionner les cellules du tableau, puis nous nous rendons dans le menu Formule et nous cliquons sur le bouton « Depuis sélection » :

Excel formation - les noms de cellules - 08

Excel nous présente alors une boîte de dialogue qui nous demande de paramétrer où se trouvent les noms qui vont permettre de définir les plages nommées du tableau.

Nous sélectionnons ici la « Ligne du haut » et la « Colonne de gauche » :

Excel formation - les noms de cellules - 09

Maintenant, nous allons pouvoir faire référence aux différentes données du tableau en utilisant les noms.

Pour cela, il suffit de sélectionner une ligne ou une colonne pour retrouver le nom qui correspond dans la zone des noms :

Excel formation - les noms de cellules - 10

Nous allons maintenant voir dans la partie suivante, comment exploiter ses données.

 

5. Comment sélectionner une plage nommée

Une fois que nous avons créé nos plages nommées sur notre feuille de calcul, nous allons pouvoir les sélectionner de manière très rapide.

En effet, comme nous venons de le voir, il suffit pour cela de dérouler la liste de la zone des noms afin de retrouver tous les noms disponibles sur la feuille de calculs active, c’est-à-dire soit les noms ayant pour zone la feuille de calcul active, soit le classeur en entier.

Lorsque nous cliquons sur l’un des noms, Excel va alors sélectionner les cellules liées.

Nous pouvons également retrouver l’ensemble des noms qui ont été créés en nous rendant dans le menu Formule et en cliquant sur « Gestionnaire des noms ».

Ici, nous pouvons également retrouver les zones sur lesquelles les différents noms vont être connus.

Une autre option pour retrouver rapidement des cellules affectées à un nom consiste à utiliser la boîte de dialogue « Atteindre », que nous retrouvons dans le menu Accueil > Rechercher et sélectionner. Nous pouvons également l’afficher à l’écran en appuyant sur la touche [F5] :

Excel formation - les noms de cellules - 11

Cette boîte de dialogue nous présente l’ensemble des noms disponibles sur tous les classeurs ouverts, il suffit de sélectionner celui-ci qui nous intéresse, puis de cliquer sur [OK] pour sélectionner les cellules liées.

Évidemment l’intérêt d’utiliser les noms de cellules ne se limite pas uniquement à la sélectionner des cellules. Cet outil va en effet nous permettre de construire des formules élaborées et facilement compréhensibles. Ce sujet tutoriel complet dédié que vous retrouverez ici, très bien bientôt.

6. Comment insérer des noms dans les formules

Comme vous pouvez vous en douter, le fait de créer des noms ne va pas uniquement nous permettre de sélectionner les cellules très rapidement.

Cela va également nous permettre d'utiliser ces plages nommées directement dans des formules.

Pour cela, il suffira de saisir les noms correspondant à la place des coordonnées des cellules que nous voulons utiliser dans les formules.

Pour cet exemple, nous allons souhaiter calculer le montant total des ventes réalisées au cours de l’année :

Excel formation - les noms de cellules - 01

Ensuite, pour obtenir le montant qui correspond, nous allons appeler la fonction SOMME(), et en tant qu’argument, nous allons saisir le nom que nous venons de créer, à savoir « _ventes » :

Excel formation - les noms de cellules - 02

 =SOMME(Ventes_2020) 

Ici, le fait d’avoir fait commencer le nom par un « _ » va nous permet de retrouver très rapidement la liste de tous les noms en tapant tout simplement ce signe.

consisteUne autre astuce qui permet de retrouver rapidement un nom lorsque nous sommes en train de rédiger une formule à utiliser le menu Formule et de dérouler la liste « Dans un formule ». Nous pouvons ensuite sélectionner directement le nom à insérer :

Excel formation - les noms de cellules - 03

Il est également possible d’appuyer sur la touche [F3] en cours de saisie pour afficher une boîte de dialogue dans laquelle nous allons retrouver tous les noms créés et disponibles sur la feuille de calcul :

Excel formation - les noms de cellules - 04

Il suffit alors de double cliquer sur le nom qui nous intéresse pour qu’Excel l’ajoute dans la formule.

Ensuite, nous pouvons valider la formule en appuyant sur le la touche [Entrée] du clavier.

Si nous reprenons les noms que nous avions créés automatiquement sur le tableau de suivi des ventes, nous allons maintenant pouvoir utiliser une fonctionnalité peu connue d’Excel qui consiste à récupérer les valeurs situées à l’intersection de plage de cellules.

Ici, imaginons que vous souhaitiez en retrouver les ventes qui correspondent au mois de février 2021, il suffira de saisir la formule suivante : « Février Vente_2021 » :

 =Février Ventes_2021 

Excel formation - les noms de cellules - 05

Si nous saisissons le nom du mois que nous souhaitons extraire dans une cellule, nous pourrons appeler ce mois en utilisant la fonction INDIRECT(), qui va alors se charger de convertir un texte en référence vers la plage de cellules :

Excel formation - les noms de cellules - 06

 =INDIRECT(A24) Ventes_2021 

 

7. Plages nommées et formules matricielles

Lorsqu’un nom fait référence à une cellule seule, alors nous récupérer sa valeur simplement dans une autre cellule en utilisant le signe égal.

En revanche, si le nom est lié à un ensemble de plusieurs cellules, alors cette méthode retournera une erreur #VALEUR!, étant donné qu’une cellule ne peut pas afficher le résultat de plusieurs autres cellules directement.

En revanche, nous pouvons utiliser une formule matricielle qui permet justement de retourner un résultat sur plusieurs cellules.

Pour cela, nous commençons par sélectionner un nombre de cellules correspondant à la taille de la plage à afficher, puis nous saisissons la formule « =Année_2020 », et lorsque nous validons, nous appuyons en même temps sur les touches [Ctrl]+[Maj]+[Entrée] :

Excel formation - les noms de cellules - 07

Excel encadre alors la formule entre des crochets, symbolisant une formule matricielle, et les résultats sont bien repris dans les cellules correspondantes.

 {=Ventes_2020} 

Nous noterons au passage que si nous avions sélectionné uniquement une cellule,

Excel nous aurait bien retourné le résultat de la cellule de la matrice de cellules liée au nom « Année_2020 », mais uniquement celle-ci.

En effet, si nous étendons la formule matricielle sur toutes les cellules de la colonne, alors celles-ci afficheront toutes les mêmes résultats. Excel dupliquera en effet uniquement le premier élément de chaque matrice :

Excel formation - les noms de cellules - 08

Pour retrouver le résultat souhaité, il est en effet nécessaire de valider la formule matricielle sur toutes les cellules de destination.

Maintenant que nous avons bien compris le principe, nous pouvons créer des formules plus utiles, comme calculer le montant des CA mensuels moyens : {=( Année_2020+Année_2021+Année_2022)/3}

Excel formation - les noms de cellules - 09

 {=(Ventes_2020+Ventes_2021)/2} 

 

8. Lier une constante à un nom

L’utilisation des plages nommées que nous venons de voir juste avant correspond à l’usage le plus courant que nous pouvons faire de cet outil, c’est-à-dire faire référence à une ou plusieurs cellules.

Mais nous pouvons également affecter une constante derrière un nom.

Pour cela, rendons-nous dans le menu Formules > Définir un nom.

Ici, nous saisissons les paramètres de manière classique, comme nous l’avons vu lors des exemples précédents, mais dans le champ « Fait référence à », nous allons saisir la valeur de la constante que nous souhaitons affecter au nom « =20% » :

Excel formation - les noms de cellules - 10

 

9. Lier une formule à un nom

Pour finir, sachez qu’il est encore possible d’insérer une formule derrière un nom de cellule.

Ici, nous allons souhaiter obtenir le montant TTC correspondant au prix HT.

Pour cela, nous commençons par sélectionner la cellule située juste à droite de la cellule C10 dans laquelle se trouve le prix HT de la première ligne du tableau.

Ensuite, rendons-nous dans le menu Formules > Définir un nom.

Nous créons le nom « _montantTTC » et dans le champ « Fait référence à », nous saisissons la formule de calcul de la TVA « =C10*(1+20%) » :

Excel formation - les noms de cellules - 11

Attention, si ici nous saisissons la référence à la cellule C10 sous la forme d’une référence absolue (par exemple en cliquant sur la cellule), alors le nom fera toujours référence à cette cellule.

C’est pourquoi, si nous étendons la formule sur toutes les cellules de la colonne, les montants TTC retournés seront tous identiques.

Pour régler ce problème, nous allons devoir revenir sur le paramétrage du nom en revenant dans le menu Formule > Gestionnaire de nom.

Ensuite, nous allons modifier le nom depuis le menu « Formules » en cliquant sur le bouton « Gestionnaire de noms » comme nous l’avons vu dans les parties précédentes.

Nous pouvons également afficher cette boîte de dialogue en utilisant le raccourci clavier [Ctrl]+[F3].

Après avoir identifié le nom « TTC », nous transformons la référence absolue en référence relative en supprimant les symboles « $ ».

Cela étant fait, nous appuyons sur la coche verte afin de valider la modification de la formule.

Les résultats sont maintenant corrects.

 

 



Articles qui pourraient vous intéresser

Découvrir les plages nommées d’Excel : utilisation et intérêt !
Comment corriger les fautes d’orthographe sur Excel ?
Comment créer un commentaire cliquable avec un lien hypertexte sur Excel ?
Comment déterminer très rapidement des valeurs futures d’une série de données sur Excel ?
Comment créer de superbes graphiques visuellement impactant pour vos infographies avec Excel ?
Comment réaliser une somme des cellules en fonction de leur couleur sur Excel ? La fonction SOMMESICOULEUR()
Ne fusionnez jamais vos cellules ! Découvrez comment centrer le contenu sur plusieurs cellules sans fusionner les cellules
Comment se protéger des macros infectées par un virus sur Excel ?
Comment obtenir le résultat d’une cellule située sur un autre classeur Excel ? Découvrons les références externes
Comment établir le classement des équipes de la Ligue 1 sur Excel ?
Comment faire une pause en VBA sur Excel ?
Comment savoir si un fichier existe dans un dossier en VBA avec Excel ?

Contact - Plan du site - À propos - Contact

© Excelformation.fr 2018-2022 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.