Comment simplifier les formules avec l’automatisation des plages nommées d’Excel

Aujourd’hui je vous propose un tutoriel dans lequel je vais vous montrer comment utiliser les plages nommées pour faciliter la création de formules sur Excel. Nous verrons ainsi comment les plages nommées permettent de facilité la création de formules, et ensuite comment automatiser l’affectation de nom sur l’ensemble des lignes et des colonnes d’un tableau.

Téléchargement

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

Tutoriel Vidéo

1. Présentation

Pour illustrer ce que nous allons voir dans ce chapitre, nous allons utiliser un petit tableau qui reprend les ventes annuelles réalisées par une chaîne de magasins :

Nous retrouvons ainsi les ventes réalisées par chaque magasin, chacun d’entre eux étant regroupé par région, ainsi que par trimestre.

À partir de ce tableau, nous allons souhaiter faire ressortir quelques informations clés telles que :

• Le montant des ventes réalisées par le magasin de Lille,
• Le pourcentage des ventes réalisées par les magasins de la région Sud pour le premier trimestre,
• La variation des ventes entre le premier et le second trimestre pour l’ensemble des magasins

Dans un premier temps, nous allons étudier la méthode classique.

Pour récupérer le montant des ventes réalisées par le magasin de Lille, la méthode est très simple, il suffit en effet de taper le symbole d’égalité pour préciser à Excel que nous souhaitons saisir une formule.

Ensuite nous cliquons sur la cellule correspondante pour créer une référence à cette même cellule afin qu’Excel reprenne la valeur de la cellule en tant que valeur :

 =F8 

Le pourcentage des ventes réalisées par le magasin de la région Sud par rapport au total des ventes sur le premier trimestre s’obtient de la même manière, mais en effectuant une division entre les deux cellules :

 =B17/B23 

Et pour finir, la variation entre deux périodes s’obtient en effectuant une différence entre les deux :

 =C23-B23 

2. Pourquoi nommer des cellules ?

Ces formules sont évidemment très simples, mais hors contexte, celles-ci sont absolument incompréhensibles :

 =F8
=B17/B23
=C23-B23 

Il est donc nécessaire de remonter jusqu’au cellules sources pour comprendre

Pour que la compréhension en soit simplifiée, Excel propose d’affecter un nom directement à une ou plusieurs cellules !

Pour cela, c’est très simple, il suffit de sélectionner les cellules en question, puis de saisir le nom correspondant dans la zone des noms, située en haut à gauche, juste à côté de la barre des formules (puis d’appuyer sur la touche [Entrée] pour valide) :

Nous pourrons alors reconstruire les formules en utilisant les noms correspondants :

 =_ventesTotalesLille
=_ventesT1RegionSud/_ventesT1Totales
=_ventesT2Totales-_ventesT1Totales 

Note 1 : Comme vous pouvez le constater j’ai pour habitude de toujours suffixer les noms de cellules avec un tiret « _ ».

Cette astuce permet de retrouver tous les noms du classeurs réunis les uns en dessous des autres au moment de la saisie de la formule.

Il suffit en effet de taper le tiret pour que la saisie assistée nous les présente :

Note 2 : Pour retrouver une liste des noms disponibles sur le classeur, rendez-vous dans le menu Formule, puis dans le groupe Noms définis de dérouler le menu Dans une formule :

De cette manière, lorsque nous cliquons sur un des noms disponibles, Excel va immédiatement l’insérer dans la formule en cours de saisie.

Bien entendu, ici nous n’avons renommé que des cellules uniques, mais il est également possible de renommer des plages constituées de plusieurs cellules :

En combinant des groupes de plages de cellules nommées avec la fonction SOMME(), nous pouvons ainsi utiliser une astuce que nous avons déjà eu l’occasion d’utiliser dans un tutoriel précédent : l’opérateur d’intersection :

 =somme(_CA _ventesLille) 

Ici, l’espace permet de récupérer la ou les cellules qui se trouvent à l’intersection des plages de cellules, que nous intégrons ensuite dans une fonction SOMME().

Le seul problème de fonctionner ainsi c’est qu’il sera alors nécessaire de nommer toutes les lignes et colonnes du tableau pour pouvoir créer toutes sortes de fonctions !

3. Nommer automatiquement les lignes et colonnes d’un tableau

Heureusement pour nous, Excel propose une fonctionnalité de nommage automatique des cellules qui va alors nous permettre de donner un nom spécifique à toutes les lignes et colonnes d’un tableau !

Pour cela, nous commençons par sélectionner les cellules du tableau :

• Soit directement à la souris s’il s’agit d’un petit tableau comme celui de notre exemple,
• Soit si le tableau contient davantage de cellules en sélectionnant l’un des cellules de ce tableau, puis en appuyant sur les touches [Ctrl]+[A] :

Puis, nous nous rendons dans le menu Formules afin de cliquer sur le bouton Depuis sélection, toujours dans le groupe Noms définis :

Excel nous présente alors une fenêtre afin de définir à quel endroit du tableau se trouve les en-têtes qui seront alors de référence pour la création des noms affectés aux plages de cellules :

Ici, s’agissant d’un tableau classique, nous utilisons la ligne du haut ainsi que la colonne de gauche pour créer les noms automatiquement.

Nous pouvons ensuite valider la création des noms en cliquant sur le bouton [OK].

Les noms sont maintenant créés, nous pouvons les retrouver :

• Soit en déroulant le menu d’affectation de nom ; ce qui permettra alors de sélectionner les cellules correspondantes :

• Soit en déroulant le menu [Dans une formule] ; ce qui permettra alors d’insérer le nom correspondant dans une formule en cours de rédaction

Nous pouvons maintenant utiliser ces noms pour construire des formules qui seront alors facilement compréhensible, notamment avec la fonction somme :

 =SOMME(CA Lille) 
=SOMME(CA_T1 Région_Sud)/SOMME(CA_T1 Total) 
=SOMME(CA_T2 Total)-SOMME(CA_T1 Total) 

4. Le tableau de données d’Excel

Bien entendu, si vous avez déjà eu l’occasion de suivre le cours dédié à la découverte des tableaux de données d’Excel vous devez vous demander quel est l’intérêt de cette fonctionnalité ?

En effet, ces deux options permettent d’attribuer automatiquement un nom aux cellules d’un tableau, mais les usages sont bien différents :

• Le tableau de données permet d’ajouter de nombres fonctionnalités au tableau (mise en forme élaborée, recopie de formule automatique sur toutes les cellules d’une colonne, …) et en plus de cela, les coordonnées du tableau vont s’adapter automatiquement lorsque nous allons ajouter de nouvelles lignes ou colonnes au tableau,
• L’affectation automatique des noms de lignes et colonnes permet quant à elle de créer une relation entre un nom et des cellules à un instant T, sans avoir le côté dynamique. En revanche l’avantage de cette solution vient du fait que même les lignes vont avoir un nom attribué, ce qui n’est pas possible avec les tableaux de données !

Mais le plus intéressant, c’est que ces deux fonctionnalités sont complémentaires et peuvent être utilisées simultanément afin de pouvoir tirer le meilleur des deux montres !