Découvrez le secret des plages relatives dynamique dans Excel

Dans ce tutoriel, nous allons découvrir une fonctionnalité méconnue mais extrêmement puissante d'Excel : les plages nommées relatives.

Cette technique peu connue va nous permettre de créer des formules intelligentes qui s'adaptent automatiquement à nos données, sans aucune programmation !

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 tutoriel, nous allons utiliser le tableau de suivi des ventes d'une chaîne de magasins de sport.

Mais avant d'aborder les plages nommées relatives, faisons un petit rappel sur les références dans Excel.

Dans Excel, nous travaillons principalement avec deux types de références :

• Les références absolues : elles utilisent le symbole $ pour "figer" une cellule ou une plage de cellules. Par exemple, en écrivant =$A$1 dans une formule, Excel conservera cette référence exacte, même si la formule est copiée ou déplacée. La cellule référencée restera toujours A1.
• Les références relatives : elles n'ont pas de $, ce qui permet à la référence de se "déplacer" en fonction de l’endroit où elle est copiée. Par exemple, si nous écrivons =A1 dans la cellule B1 et que nous copions cette formule en C1, la référence se décalera automatiquement de façon à pointer vers B1.

Le mélange de ces deux types de référence est une référence mixte, qui permet de ne figer que la colonne ou la ligne d’une cellule.

Ainsi, les plages nommées relatives fonctionnent de la même manière que les références relatives : elles adaptent leurs références en fonction de la position de la cellule qui les utilise.

Par exemple, une plage nommée relative peut être définie pour pointer vers la cellule située juste au-dessus de celle dans laquelle la formule est utilisée, ce qui permet d'automatiser les calculs dans un tableau sans avoir à réajuster manuellement chaque formule.

2. Créer notre première plage nommée relative

Maintenant voyons comment créer une plage nommée relative, qui permettra de simplifier nos calculs et d’automatiser certaines analyses dans notre tableau de suivi des ventes.

Une plage nommée relative s’ajuste en effet dynamiquement en fonction de la cellule active, offrant une flexibilité accrue pour travailler avec des données répétitives ou évolutives.

Pour commencer, nous sélectionnons la cellule qui va faire référence à la plage, c’est-à-dire celle dans laquelle nous allons insérer la formule (ici la cellule B14) :

Puis, pour définir le nom de la plage : nous nous rendons dans l’onglet Formules du ruban afin de sélectionner Gestionnaire de noms.

Pour aller plus vite, nous pouvons également utiliser le raccourci clavier [Ctrl]+[F3] pour ouvrir le gestionnaire de noms

Une fois dans le gestionnaire des noms, nous cliquons sur Nouveau.

Ensuite, nous pouvons spécifier un nom à donner à la référence relative, par exemple « ventesMensuelles »

Dans le champ Fait référence à, nous sélectionnons la plage des cellules que nous souhaitons affecter à la plage nommée (ici, les cellules B9 à B13, dans lesquelles nous retrouvons les montants des ventes réalisées au cours du mois de janvier).

Nous retrouvons donc bien la plage des cellules sélectionnées et nous pouvons constater que cette plage est sous la forme d’une référence absolue, avec des symboles dollar.

Pour convertir cette référence en référence relative, nous pouvons soit supprimer les dollars manuellement, soit appuyer sur la touche [F4] du clavier trois fois de suite, après avoir sélectionné la référence.

Cela garantira que la plage se déplace en fonction de la cellule dans laquelle la formule est utilisée.

Ensuite, nous cliquons sur [OK] pour enregistrer cette nouvelle plage.

Désormais, nous pourrons utiliser la plage nommée « ventesMensuelles » dans une formule pour faire appel automatiquement aux cellules sélectionnées.

Nous saisissons donc la formule suivante dans la cellule

 =SOMME(ventesMensuelles) 

Pour retrouver le nom, nous pouvons soit saisir les premières lettres de celui-ci, soit si nous avons oublié comment il s’appelle, nous pouvons appuyer sur [F3] pour afficher la liste des noms créés :

Maintenant, nous pouvons étendre cette formule sur les autres cellules de la ligne pour calculer le montant des ventes au mois le mois :

Comme nous pouvons le constater, le résultat va bien s’adapter aux plages de cellules, alors que la formule est identique dans chacune d’entre elles.

3. Utiliser une plage nommée mixte

En revanche, si nous effectuons un copier-coller de la ligne 14 en ligne 16, alors nous allons rencontrer un problème : les références de la plage nommée « ventesMensuelles » vont également s’adapter à cette nouvelle ligne.

Cela signifie que la plage va se décaler, se basant toujours sur la position relative de la cellule active, et ne fera donc plus référence aux cellules initiales de la ligne 14.

Les résultats retournés sont donc erronés.

Pour corriger cela, nous allons devoir modifier les références des plages nommées pour utiliser des références mixtes, qui comme nous l’avons vu un peu plus tôt permettent de ne « figer » que la colonne ou que la ligne, tout en gardant l’autre variable.

Dans notre cas, si nous souhaitons que les lignes de la plage restent toujours les lignes 9 à 13.

Nous allons modifier la plage nommée « ventesMensuelles » en remplaçant les références par des références mixtes.

Pour ouvrir le gestionnaire de noms, nous utilisons à nouveau le raccourci [Ctrl] + [F3] et dans le champ Fait référence à, nous ajoutons des dollars devant les numéros de ligne de plage source :

Puis nous validons la modification en appuyant sur la coche verte.

De cette manière, les résultats des lignes 14 et 16 sont maintenant identiques :

4. Gestion des nouvelles données

L'un des grands avantages des plages nommées relatives est leur capacité à s'adapter automatiquement aux nouvelles données.

Pour illustrer ce principe, imaginons que nous souhaitions déterminer automatiquement le montant total des ventes par rayon.

Pour cela, comme nous venons de le voir, nous commençons par nous placer dans la cellule G9 afin de créer la plage nommée qui permet d’obtenir le montant des ventes du premier rayon pour les mois de janvier à mai, en utilisant la référence des cellules mixtes « $B9:$F9 », afin de figer les colonnes, mais pas les lignes

 =Excelformation.fr!$B9:$F9 

Puis, pour obtenir le montant total des ventes par rayon sur les mois de janvier à mai, nous utilisons la formule suivante dans G9

 =SOMME(ventesParRayon) 

Maintenant que la plage nommée mixte est créée, imaginons que nous souhaitions ajouter les ventes du mois de 06.

Nous allons donc sélectionner les cellules G8 à G14 pour les glisser sur la colonne de droite :

Puis ajouter les ventes du mois de juin dans l’espace libéré :

Seulement comme vous pouvez le constater, le résultat ne tient pas compte de ces nouvelles valeurs.

Pour rendre la plage nommée mixte dynamique, nous allons devoir rendre la référence plus complexe, en utilisant la formule suivante :

 =DECALER($B9;0;0;1;COLONNE()-COLONNE($B9)) 

Cette formule repose sur l’utilisation de la fonction DECALER, qui permet de créer une plage dynamique en se basant sur une cellule de référence initiale.

La puissance de DECALER réside dans sa capacité à ajuster automatiquement la plage définie, suivant l’ajout ou la suppression de données dans le tableau.

Les arguments sont les suivants :

Référence : il s’agit de la cellule ou de la plage de départ à partir de laquelle le décalage s'opère. Par exemple, en utilisant =DECALER($B$9; …), la cellule B9 est définie comme point de référence. Toutes les manipulations de décalage (lignes, colonnes, hauteur, et largeur) seront calculées à partir de cette cellule. Dans le cas d’un tableau de ventes mensuelles, la cellule B9 pourrait représenter le début de la ligne des ventes pour le premier rayon.

Lignes et Colonnes : ces arguments déterminent le déplacement en lignes et en colonnes à partir de la cellule de référence. Dans notre exemple, nous avons attribué une valeur de 0 à chacun de ces paramètres, ce qui signifie qu’il n’y a pas de décalage initial. La plage commence donc exactement là où se trouve la cellule de référence, sans bouger vers une autre ligne ou colonne. Si nous avions entré une valeur de 1 pour Lignes, par exemple, la plage commencerait une ligne en dessous de B9. Cette flexibilité permet d’adapter les calculs en fonction de la position des nouvelles données dans le tableau.

Hauteur : ce paramètre définit le nombre de lignes que couvrira la plage résultante. Dans notre exemple, Hauteur est fixée à 1 car nous ne voulons couvrir qu'une seule ligne, correspondant aux ventes d’un seul rayon. Cette valeur reste fixe pour éviter que la plage ne se propage sur plusieurs lignes, ce qui maintient chaque ligne de vente (chaque rayon) indépendante dans le calcul.

Largeur : cet argument est défini par COLONNE()-COLONNE($B$9) + 1, une formule dynamique qui permet à la largeur de la plage de s’adapter automatiquement au nombre de colonnes remplies depuis la colonne de départ (B9) jusqu’à la colonne actuelle dans laquelle la formule est insérée. Voici comment cela fonctionne. Ici « COLONNE() » retourne le numéro de la colonne actuelle où la formule est utilisée et « COLONNE($B$9) » donne le numéro de colonne de la cellule B9, qui est ici le point de départ.

Les montants par rayon tient maintenant compte des ventes du mois de juin, et nous pouvons ajouter autant de mois que nous le souhaitons :