Références relatives, absolues et mixtes dans Excel
Recopier une formule vers le bas et découvrir que tous les résultats sont faux : c'est l'erreur la plus fréquente sur tableur, et elle coûte cher à l'épreuve de l'UE 8 du DCG. Tout se joue sur un détail typographique : le symbole dollar. Savoir distinguer une référence absolue, une référence relative et une référence mixte dans Excel, c'est garantir que vos modèles de calcul restent justes quelle que soit la direction de la recopie.
Référence relative, absolue ou mixte : le rôle du dollar
Dans Excel (ou LibreOffice Calc), une formule fait référence à d'autres cellules : =A1+B1, =B2*$B$1, etc. Le comportement de ces références lors d'une recopie (copier-coller ou poignée de recopie) dépend de la présence du symbole $ :
- Référence relative (
A1) : la référence se décale avec la formule ;
- Référence absolue (
$A$1) : la référence ne bouge jamais ;
- Référence mixte (
$A1 ou A$1) : seule la colonne ou seule la ligne est figée.
C'est ce mécanisme qui permet d'écrire une formule une seule fois et de la recopier sur des centaines de lignes sans la réécrire.
La référence relative : la formule qui se déplace
Par défaut, toute référence est relative. Si la cellule C1 contient =A1+B1 et que vous la recopiez une ligne plus bas, en C2, la formule devient automatiquement =A2+B2. Excel ne mémorise pas « A1 » mais « la cellule située deux colonnes à gauche, sur la même ligne ». La recopie conserve ce décalage.
C'est exactement ce que l'on veut pour calculer un montant ligne par ligne : quantité multipliée par prix unitaire, recopiée sur tout un tableau de ventes.
La référence absolue : le dollar qui fige tout
Le symbole $ placé devant la colonne ET devant la ligne fige complètement la référence : $B$1 pointe toujours vers la cellule B1, que la formule soit recopiée vers le bas, vers la droite ou en diagonale.
L'usage typique : référencer un paramètre fixe du modèle, comme un taux de TVA, un taux de remise ou un seuil. Si B1 contient le taux de TVA (20 %), la formule du prix TTC en C3 s'écrit =B3*(1+$B$1). Recopiée en C4, elle devient =B4*(1+$B$1) : le prix HT glisse, le taux reste accroché à B1.
Les références mixtes : figer la ligne ou la colonne
La référence mixte ne fige qu'une des deux coordonnées :
$A1 fige la colonne : recopiée vers la droite, la référence reste en colonne A, mais la ligne s'ajuste si l'on recopie vers le bas ;A$1 fige la ligne : recopiée vers le bas, la référence reste sur la ligne 1, mais la colonne s'ajuste vers la droite.
Les références mixtes deviennent indispensables dès qu'une même formule doit être recopiée à la fois vers le bas et vers la droite, comme dans une grille tarifaire ou une table de simulation.
| Référence |
Comportement lors de la recopie |
A1 (relative) |
Colonne et ligne s'ajustent |
$A$1 (absolue) |
Rien ne bouge |
$A1 (colonne figée) |
La colonne ne bouge pas, la ligne s'ajuste |
A$1 (ligne figée) |
La ligne ne bouge pas, la colonne s'ajuste |
La touche F4 : basculer entre les quatre modes
Inutile de taper les dollars à la main. Dans la barre de formule, placez le curseur sur la référence et appuyez sur F4 : Excel fait défiler les quatre modes dans l'ordre A1 → $A$1 → A$1 → $A1 → A1. C'est un réflexe à acquérir avant l'examen : il fait gagner du temps et évite les fautes de frappe.
Un exemple chiffré : la grille tarifaire de la société Verdier
Prenons le cas de la société Verdier, négociant en fournitures de bureau, qui veut construire une grille de prix : en ligne 1 (cellules B1 à E1), les quantités commandées (10, 50, 100, 500) ; en colonne A (cellules A2 à A8), les prix unitaires de ses sept articles. Chaque cellule du tableau doit afficher le montant total : prix unitaire multiplié par quantité.
La formule saisie en B2 doit pouvoir être recopiée sur toute la grille. Analysons :
- le prix unitaire est toujours en colonne A : il faut figer la colonne, donc
$A2 ;
- la quantité est toujours en ligne 1 : il faut figer la ligne, donc
B$1.
La formule en B2 s'écrit donc =$A2*B$1. Recopiée en E8, elle devient =$A8*E$1 : chaque cellule croise le bon prix et la bonne quantité. Avec une formule entièrement relative (=A2*B1), la grille aurait été fausse dès la deuxième colonne ; avec une formule entièrement absolue, toutes les cellules auraient affiché le même montant.
Ajoutons un paramètre : la remise de 5 % stockée en H1. Le montant net s'écrit =$A2*B$1*(1-$H$1). Ici, $H$1 est en absolu complet car la remise est unique pour toute la grille.
Les erreurs fréquentes
- Oublier le dollar sur un paramètre : la recopie fait glisser la référence vers des cellules vides et la formule renvoie 0 ou un résultat faux, sans message d'erreur.
- Tout mettre en absolu par réflexe : la formule recopiée affiche partout le même résultat ; les références qui doivent suivre les lignes doivent rester relatives.
- Confondre
$A1 et A$1 : retenir que le dollar fige ce qui le suit immédiatement (colonne ou ligne).
- Écrire le taux en dur dans la formule (
=B3*1,2 au lieu de =B3*(1+$B$1)) : le modèle devient impossible à mettre à jour quand le taux change.
- Ne pas vérifier après recopie : cliquez sur quelques cellules recopiées et lisez la formule dans la barre de formule pour contrôler le déplacement des références.
FAQ
Quelle est la différence entre $A1 et A$1 ?
$A1 fige la colonne A : en recopiant vers la droite, la référence reste en colonne A, mais elle suit le déplacement vers le bas. A$1 fige la ligne 1 : en recopiant vers le bas, la référence reste sur la ligne 1, mais elle suit le déplacement vers la droite. Le dollar bloque uniquement l'élément qu'il précède.
À quoi sert la touche F4 dans Excel ?
Dans la barre de formule, F4 fait basculer la référence sélectionnée entre les quatre modes : relative, absolue, ligne figée, colonne figée (A1 → $A$1 → A$1 → $A1). C'est le moyen le plus rapide et le plus sûr de poser les dollars au bon endroit.
Pourquoi ma formule renvoie-t-elle un résultat faux après recopie ?
Dans la grande majorité des cas, une référence qui aurait dû être absolue est restée relative : elle a glissé pendant la recopie et pointe désormais vers une cellule vide ou erronée. Vérifiez chaque référence de la formule d'origine et figez avec $ celles qui désignent des paramètres fixes.
Entraînez-vous
La cellule C3 contient la formule =A1*$B$1. Indiquez ce que devient cette formule si on la recopie : 1) en C4 (une ligne en dessous) ; 2) en D3 (une colonne à droite) ; 3) en D4 (en diagonale). 4) Expliquez ensuite quelle formule saisir en B2 pour une grille où les quantités sont en ligne 1 et les prix unitaires en colonne A, afin de pouvoir la recopier sur toute la grille.
Afficher le corrigé
En C4 : =A2*$B$1. La référence relative A1 glisse d'une ligne (A1 devient A2) ; $B$1 est absolue et ne bouge pas.
En D3 : =B1*$B$1. La référence relative glisse d'une colonne (A1 devient B1) ; $B$1 reste figée.
En D4 : =B2*$B$1. Déplacement d'une ligne et d'une colonne : A1 devient B2 ; la référence absolue ne bouge toujours pas.
Grille : en B2, saisir =$A2*B$1. Le prix unitaire est toujours en colonne A, on fige donc la colonne ($A2) ; la quantité est toujours en ligne 1, on fige donc la ligne (B$1). La formule peut alors être recopiée vers le bas et vers la droite : chaque cellule croise automatiquement le bon prix et la bonne quantité.