Accueil | Ce site | CV | Excel | Livre d’or | Macros XL4 | Modèles | VBA

Références absolues et relatives

Á l’intérieur des formules, on se réfère souvent à une ou plusieurs cellules. Afin d’éviter de répéter régulièrement l'expression « une cellule (ou plusieurs) », nous emploierons dans la suite de cette page le terme générique de “plage”.

Selon les cas, on utilise des références relatives (), absolues () ou mixtes ( ou ).

Références relatives

Ce sont les plus fréquentes. Le critère privilégié ici est la position par rapport à la cellule contenant la formule, de la plage à laquelle la formule fait référence. Le tableau ci-dessous, un extrait d’une facture est un exemple typique de recours à des références relatives


Fig. 1 - Extrait d’une facture (affichage des valeurs)

  


Fig. 2 - Extrait d’une facture (affichage des formules)

Le montant est calculé de manière équivalente dans les cellules D2 à D5 : le prix unitaire est multiplié par la quantité. Cependant, chacune des formules diffère : en D2, on calcule le produit de B2 par C2, tandis que la formule en D3 utilise B3 et C3.

En fait, dans les 4 cellules D2 à D5, ce n’est pas la formule, qui est invariante, mais un schéma : on multiplie, la cellule située sur la même ligne, 2 colonnes avant (le prix unitaire) par la cellule située également sur la même ligne, une colonne avant (la quantité).

Références absolues

La figure 3 représente une variante du tableau précédent, avec une colonne supplémentaire rappelant les montants en Francs


Fig. 3 - Le tableau de la Fig. 1, complété par une colonne affichant les montants en FF.

Les montants en Francs sont obtenus en multipliant le montant en € (même ligne, colonne précédente) par la valeur de l’€ exprimée en francs français.

La notion de référence relative convient pour le montant en €, mais pas pour le cours de l’€ : la formule en E4 doit se référer à la cellule B1, tout comme la formule en E7.


Fig. 4 - Formules correspondant à la fig. 3. Dans les cellules E4 à E7,
on se réfère à la valeur de l’€ en désignant la cellule B1 par $B$1.

Pour convertir des francs français en €, c’est la référence B1 qui doit rester invariante, quelque soit le numéro de ligne ou de colonne de la cellule dans laquelle la formule est entrée. L’expression « référence absolue » est donc parfaitement justifiée. C’est le caractère “$” qui permet de préciser ce que l’on veut : “$B$1” indique une référence absolue tandis que “B1” correspond à une référence relative.

Personnellement, plutôt que d’employer des références absolues, je préconise de nommer les cellules correspondantes,


Fig. 5 - Définition du nom “Cours.Euro”

et d’utiliser les noms dans les formules, qui sont ainsi plus lisibles :


Fig. 6 - Formules correspondant à la fig. 3. Par rapport à la fig. 4, la différence consiste
à utiliser un nom, “Cours.Euro”, plutôt qu’une référence absolue pour désigner la cellule B1

Une fois défini le nom “Cours.Euro”, il est aisé de mettre à jour les formules reproduites en figure 4, afin d’aboutir à la figure 6 : il suffit de faire appel à “Appliquer…, commande trop souvent méconnue, accessible par le sous-menu “Nom” du menu “Insertion” (figure 7).


Fig 7 - Accès au dialogue permettant d’appliquer un nom.

Cette commande affiche un dialogue (figure 8) indiquant les plages nommées, ce qui permet de choisir un ou plusieurs noms dans la liste afin de les utiliser dans des formules, a posteriori.


Figure 8 - Le dialogue listant les noms attribués à des plages.

Dans le dialogue de la figure 8, il suffit de sélectionner le nom “Cours.Euro” dans la liste et valider, pour modifier les cellules E4 à E7 : dans ces 4 cellules, les formules reproduites en figure 4 sont remplacées par celles de la figure 6 (figure 9).


Figure 9 - Effet de la validation du dialogue
de la figure 8 les formules des cellules E4 à E7

Références mixtes

Comme l’indique le titre, il s’agit d’une référence intermédiaire. Contrairement aux références absolues, seul un caractère “$” est employé :

- $B1 signifie que la colonne est absolue (“$”), et la ligne relative (pas de “$”).
- B$1 signifie que la colonne est relative (pas de “$”), et la ligne absolue (“$”).