Une référence absolue garde une cellule fixe quand vous copiez une formule. Dans =B2*$E$1, les dollars verrouillent E1 : recopiez la formule vers le bas et chaque ligne multiplie toujours par E1, alors que B2 devient B3, B4 et ainsi de suite. Pour ajouter les dollars, cliquez sur la référence dans la formule et appuyez sur F4 (sur Mac, Cmd+T).
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Commission | Rate | 5% |
| 2 | Ana | $12,000 | $600 | ||
| 3 | Ben | $8,500 | $425 | ||
| 4 | Chen | $15,200 | $760 | ||
| 5 | Dina | $9,800 | $490 | ||
| 6 | Eli | $11,000 | $550 |
=B2*$E$1C2 a été tapée une fois puis recopiée vers le bas. Cliquez sur C4 : sa formule est =B4*$E$1. La cellule des ventes est passée à la ligne 4, le taux est resté en E1. Passez le taux de E1 à 8 % et toutes les commissions se mettent à jour.
Références relatives et absolues
| Référence | Nom | Copiée une ligne plus bas et une colonne à droite |
|---|---|---|
A1 | relative | B2 |
$A$1 | absolue | $A$1 |
A$1 | mixte : ligne verrouillée | B$1 |
$A1 | mixte : colonne verrouillée | $A2 |
Une référence simple comme B2 est relative : Excel l'enregistre comme "la cellule à cette position par rapport à moi", donc une copie une ligne plus bas pointe une ligne plus bas. C'est exactement ce qu'il faut pour des données ligne par ligne, et c'est le comportement par défaut. Le $ devant une lettre de colonne ou un numéro de ligne verrouille cette partie.
L'erreur classique : recopier vers le bas sans $
Voici de nouveau le tableau des commissions, avec =B2*E1 en C2 et sans dollars. La première ligne est juste. Les autres valent 0.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Commission | Rate | 5% |
| 2 | Ana | $12,000 | $600 | ||
| 3 | Ben | $8,500 | $0 | ||
| 4 | Chen | $15,200 | $0 | ||
| 5 | Dina | $9,800 | $0 | ||
| 6 | Eli | $11,000 | $0 |
=B3*E2C3 contient =B3*E2 : la référence au taux est descendue en E2, qui est vide, et une cellule vide compte pour 0. Corrigez-le ici : cliquez sur C2, remplacez la formule par =B2*$E$1 et appuyez sur Entrée. Toute la colonne suit, car C3:C6 sont des copies de C2. Quand la cellule fixe est un diviseur, comme dans =B2/B7 pour une part d'un total, la même erreur affiche #DIV/0! au lieu de 0 (le pourcentage d'un total en est le cas typique).
Appuyez sur F4 pour ajouter les dollars
Pendant que vous tapez ou modifiez une formule, placez le curseur dans une référence (ou juste après) et appuyez sur F4. Chaque appui passe à la forme suivante :
E1 -> $E$1 -> E$1 -> $E1 -> E1
Sur beaucoup de portables, F4 règle l'écran ou le son : appuyez alors sur Fn+F4. Sur Mac, utilisez Cmd+T, ou Fn+F4. Vous pouvez aussi taper les $ vous-même.
Références mixtes : verrouiller seulement la ligne ou la colonne
Une référence mixte a un seul dollar. $A2 lit toujours la colonne A mais laisse la ligne bouger ; B$1 lit toujours la ligne 1 mais laisse la colonne bouger. Avec les deux dans une formule, une seule formule recopiée sur une grille construit une table de multiplication :
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | x | 1 | 2 | 3 | 4 | 5 |
| 2 | 1 | 1 | 2 | 3 | 4 | 5 |
| 3 | 2 | 2 | 4 | 6 | 8 | 10 |
| 4 | 3 | 3 | 6 | 9 | 12 | 15 |
| 5 | 4 | 4 | 8 | 12 | 16 | 20 |
| 6 | 5 | 5 | 10 | 15 | 20 | 25 |
=$A2*B$1B2 contient =$A2*B$1. Cliquez sur F6 : elle contient =$A6*F$1, le numéro de ligne de la colonne A fois le numéro de colonne de la ligne 1, et affiche donc 25. Retirez un dollar en B2 et la table s'écroule, parce que les copies se mettent à multiplier des cellules voisines au lieu des en-têtes.
Le même principe calcule une liste de prix à plusieurs remises : =$A2*(1-B$1) avec les prix dans la colonne A et les taux de remise sur la ligne 1.
Total cumulé avec une plage à moitié verrouillée
Une plage peut n'être verrouillée qu'à une extrémité. =SOMME($B$2:B2) commence toujours en B2, alors que sa fin descend à mesure que la formule est recopiée : chaque ligne additionne tout jusqu'à elle-même. Le tableau affiche la formule en anglais (=SUM($B$2:B2)), mais vous pouvez aussi taper les formules en français, avec des points-virgules.
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | Total so far |
| 2 | Jan | 420 | 420 |
| 3 | Feb | 380 | 800 |
| 4 | Mar | 510 | 1310 |
| 5 | Apr | 450 | 1760 |
| 6 | May | 470 | 2230 |
=SOMME($B$2:B2)C6 contient =SUM($B$2:B6) et affiche 2230, le total des cinq mois. La même plage à moitié verrouillée permet à =NB.SI($A$2:A2;A2) (NB.SI s'appelle COUNTIF en anglais) de compter combien de fois une valeur est déjà apparue, ce qui sert à repérer les doublons après la première occurrence.
Exercice : une formule pour tout le tableau
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Price | 10% | 20% | 30% |
| 2 | $40.00 | |||
| 3 | $25.00 | |||
| 4 | $60.00 | |||
| 5 | $18.00 |
À vous : En B2, écrivez le prix du premier produit après la remise de B1. Utilisez $ pour que la même formule, recopiée vers la droite et vers le bas jusqu'à D5, donne tous les prix du tableau.
Le tableau copie votre formule dans chaque cellule de B2:D5, comme le ferait la poignée de recopie, et la vérification lit les douze résultats. Sans les bons dollars, les copies de la ligne 3 ou de la colonne C lisent le mauvais prix ou la mauvaise remise.
Références absolues vers une autre feuille ou une table de recherche
Les dollars fonctionnent de la même façon avec un nom de feuille : =B2*Settings!$B$1. Ils comptent surtout dans les recherches, où la table doit rester en place pendant que la valeur cherchée se déplace : =RECHERCHEV(A2;$E$2:$F$10;2;FAUX) recopiée vers le bas cherche toujours dans E2:F10 (RECHERCHEV est VLOOKUP en anglais), alors que =RECHERCHEV(A2;E2:F10;2;FAUX) fait glisser la table d'une ligne à chaque copie et finit par manquer les premières lignes (RECHERCHEV). Si une cellule fixe sert dans de nombreuses formules, vous pouvez aussi lui donner un nom avec Formules > Définir un nom et écrire =B2*Rate ; un nom défini ainsi pointe vers la même cellule depuis toutes les formules, comme $E$1.
Questions fréquentes
Que signifie le signe $ dans une formule Excel ?
Il verrouille la partie de la référence qui le suit. Dans $E$1, la colonne E et la ligne 1 sont toutes deux verrouillées, donc la référence reste E1 partout où la formule est copiée. E$1 ne verrouille que la ligne et $E1 que la colonne.
Quel est le raccourci pour une référence absolue dans Excel ?
Cliquez dans la référence pendant que vous modifiez la formule et appuyez sur F4 (Fn+F4 sur beaucoup de portables). Chaque appui passe par $A$1, A$1, $A1 et A1. Sur Mac, appuyez sur Cmd+T, ou Fn+F4.
Quelle est la différence entre une référence relative et une référence absolue ?
Une référence relative comme B2 se déplace quand vous copiez la formule : une ligne plus bas, elle devient B3. Une référence absolue comme $B$2 reste $B$2. Utilisez une référence absolue pour une cellule unique dont chaque ligne a besoin, comme un taux ou un total.
Qu'est-ce qu'une référence mixte dans Excel ?
Une référence avec un seul dollar : $A2 garde la colonne et laisse la ligne bouger, B$1 garde la ligne et laisse la colonne bouger. =$A2*B$1 recopié vers la droite et vers le bas sur une grille construit une table de multiplication.
Pourquoi ma formule affiche-t-elle 0 ou #DIV/0! après l'avoir recopiée vers le bas ?
Une référence qui devait rester fixe s'est déplacée avec la copie. Si la ligne 2 contient =B2/B7, la ligne 3 reçoit =B3/B8, et B8 est vide. Verrouillez le total avec =B2/$B$7 et recopiez de nouveau.
