Références relatives, absolues et mixtes dans Excel
Une formule ne contient pas des valeurs mais des adresses de cellules. Quand on la recopie, Excel décale ces adresses, sauf celles protégées par un $. Comprendre ce mécanisme évite la majorité des erreurs de calcul. Ce tutoriel couvre les références relatives, absolues et mixtes, les noms de cellules et les liaisons entre feuilles et classeurs.
L’adressage relatif
Par défaut, une référence est relative : Excel la mémorise comme une position par rapport à la cellule de la formule. Dans D2, =B2*C2 signifie « la cellule deux colonnes à gauche × la cellule juste à gauche ». Recopiée en D3, elle devient =B3*C3. C’est ce qui permet de calculer toute une colonne avec une seule formule.
L’adressage absolu
Quand une formule doit toujours pointer vers la même cellule (un taux de TVA, un coefficient, un prix unitaire), on la rend absolue avec deux $ : $G$1. Dans la démo ci-dessus, choisissez « TTC sans $ » puis « TTC avec $G$1 » : sans le $, la référence au taux glisse vers G2, G3… qui sont vides.
Pendant la saisie d’une formule, placez le curseur sur une référence et appuyez sur F4 : Excel fait tourner les quatre formes.
| Recopiée… | devient |
|---|---|
| 3 lignes plus bas | |
| 2 colonnes à droite | |
$ ne sert qu’à la recopie. Une formule qui n’est jamais recopiée donne le même résultat avec ou sans $.$ au clavier.L’adressage semi-relatif (références mixtes)
Une référence mixte ne fige qu’une partie : $A3 bloque la colonne A, B$2 bloque la ligne 2. Elle est indispensable pour remplir un tableau à double entrée avec une seule formule. Exemple : prix remisés, avec les prix en colonne A et les remises en ligne 2. Choisissez une formule saisie en B3 puis recopiée jusqu’en E7, et cliquez sur une cellule pour voir sa formule.
| Forme | Recopie vers le bas | Recopie vers la droite | Usage type |
|---|---|---|---|
A1 | La ligne change | La colonne change | Calcul ligne à ligne |
$A$1 | Fixe | Fixe | Taux, constante, paramètre |
A$1 | Fixe | La colonne change | En-têtes de colonnes d’un tableau à double entrée |
$A1 | La ligne change | Fixe | En-têtes de lignes d’un tableau à double entrée |
$ se place devant ce qui ne doit pas bouger. « Je reste dans la colonne A » : $A. « Je reste sur la ligne 2 » : $2.=$A2*B$1 en B2, recopiée sur tout le tableau. Faire le cas pratique.L’adressage par nom
Un nom remplace une adresse absolue et rend la formule lisible. Si G1 s’appelle Taux_TVA, la formule =B2*C2*(1+Taux_TVA) se recopie sans $ : un nom est toujours absolu.
| Étape | Méthode |
|---|---|
| Nommer une cellule | Sélectionner G1, taper Taux_TVA dans la zone Nom (à gauche de la barre de formule), Entrée |
| Nommer avec options | Formules › Définir un nom : portée (classeur ou feuille), commentaire |
| Nommer des colonnes d’un coup | Sélectionner le tableau, Formules › Depuis sélection › Ligne du haut |
| Utiliser un nom | Le taper dans la formule, ou F3 pour la liste des noms |
| Modifier, supprimer | Formules › Gestionnaire de noms (Ctrl + F3) |
| Avec adresses | Avec noms |
|---|---|
=B2*C2*(1+$G$1) | =B2*C2*(1+Taux_TVA) |
=SOMME($D$2:$D$6) | =SOMME(Total_HT) |
=RECHERCHEX(A2;Tarifs!$A$2:$A$50;Tarifs!$C$2:$C$50) | =RECHERCHEX(A2;Codes;Prix) |
TVA2026 est refusé (c’est une cellule !), TVA_2026 est accepté.=Taux_TVA dans une autre feuille évite d’écrire =Paramètres!$G$1.Liaisons entre feuilles et classeurs
Pour utiliser une cellule d’une autre feuille, tapez =, cliquez sur l’onglet, cliquez sur la cellule et validez par Entrée. Excel écrit la référence avec le nom de la feuille suivi d’un point d’exclamation. Modifiez les chiffres des trois feuilles : la synthèse se recalcule.
| Cas | Syntaxe |
|---|---|
| Autre feuille | =Janvier!B2 |
| Nom de feuille avec espace | ='Ventes 2026'!B2 (apostrophes obligatoires) |
| Même cellule sur plusieurs feuilles (référence 3D) | =SOMME(Janvier:Mars!B2) : toutes les feuilles placées entre Janvier et Mars |
| Autre classeur ouvert | =[Budget.xlsx]Feuil1!$B$2 |
| Autre classeur fermé | ='C:\Formations\[Budget.xlsx]Feuil1'!$B$2 |
$B$2). Retirez les $ avec F4 si vous devez recopier la formule.Exercice de synthèse
Un classeur contient trois feuilles mensuelles (Janvier, Février, Mars) avec les heures de formation par stagiaire en B2:B10, et une feuille Paramètres avec le taux horaire en B1. Créez une feuille Trimestre qui calcule pour chaque stagiaire le total des heures et le montant facturé.
- Nommez la cellule Paramètres!B1
Taux_horaire(zone Nom). - En Trimestre!B2 :
=SOMME(Janvier:Mars!B2), puis recopiez jusqu’en B10 (référence 3D relative). - En Trimestre!C2 :
=B2*Taux_horaire, recopiez jusqu’en C10 : le nom reste fixe. - Variante sans nom :
=B2*Paramètres!$B$1. Sans les$, la recopie lirait B2, B3… de Paramètres.