Références absolues et mixtes
Un seul signe dollar décide si une formule recopiée continue de viser le taux de taxe en B1 ou glisse vers une cellule vide.
Pourquoi la colonne des taxes se dérègle après une seule recopie
Commence par une énigme. Le taux de taxe, 10 %, est en B1, et quatre prix sont en A4 à A7. Quelqu'un tape =A4*B1 en B4, obtient 2, ce qui est juste, et recopie vers le bas jusqu'en B7. Maintenant B5 affiche 0, B6 affiche #VALUE! et B7 affiche 25. Avant de lire la suite, essaie d'expliquer pourquoi chacune de ces trois cellules est fausse.
Clique sur B5 et lis sa formule : =A5*B2. La recopie vers le bas a décalé chaque référence ordinaire d'une ligne. La référence du prix est passée de A4 à A5, comme il faut, mais celle du taux est passée de B1 à B2 elle aussi, et B2 est vide. Une cellule vide compte pour 0 dans une multiplication, donc B5 affiche 0 sans rien dire. B6 multiplie maintenant par B3, qui contient l'en-tête Tax ; la page de Microsoft sur #VALUE! précise que les opérateurs comme + et * peuvent ne pas réussir à calculer des cellules qui contiennent du texte, d'où l'erreur. B7 multiplie par B4, le premier résultat de taxe, et obtient 12.5 × 2 = 25. Celle-là a l'air crédible, ce qui en fait la pire des trois. Une seule recopie, trois mauvaises réponses, et une seule d'entre elles est un message d'erreur.
La solution consiste à dire au tableur que B1 ne doit jamais bouger. Un signe dollar devant une partie d'une adresse bloque cette partie. $B$1 bloque à la fois la colonne et la ligne, ce qui en fait une référence absolue : elle désigne B1 où que la formule arrive. Écris la première taxe sous la forme =A4*$B$1, recopie vers le bas, et tu obtiens =A5*$B$1, =A6*$B$1 et ainsi de suite. L'exemple de Microsoft est =$B$4*$C$4, qui reste exactement identique quand on le copie de D4 vers D5. Le signe n'a rien à voir avec l'argent. C'est un repère.
Garder le taux dans sa propre cellule rapporte une seconde fois. Si le taux passe de 10 % à 12 %, tu modifies B1 et toutes les lignes se recalculent d'un coup. Si le taux avait été tapé dans chaque formule sous la forme *0.1, il faudrait traquer chaque copie, et celle que tu oublies laisse un chiffre faux qui a l'air parfaitement normal. Place donc tout ce qui peut changer (taux, limites, taux de change) dans des cellules étiquetées en haut de la feuille. Vise-les avec des références absolues.
Fais glisser pour tourner. Fais défiler ou pince pour zoomer.
Bloquer seulement la ligne, ou seulement la colonne
Tu peux bloquer une seule partie. Dans A$1, la ligne est bloquée et la colonne bouge ; dans $A1, c'est l'inverse. Le tableau de Microsoft copie chaque forme deux cellules plus bas et deux cellules plus à droite : $A$1 reste $A$1, A$1 devient C$1, $A1 devient $A3, et A1 devient C3. Tu taperas rarement les signes à la main. Pendant que tu modifies une formule, place le curseur sur une référence et appuie sur F4. Chaque appui passe à la forme suivante (A1, $A$1, A$1, $A1) puis revient à A1. La liste des raccourcis de Google indique aussi F4 pour Sheets, et Fn+F4 sur un clavier Mac.
| Référence | Colonne | Ligne | Copiée 2 vers le bas, 2 vers la droite |
|---|---|---|---|
| A1 | bouge | bouge | C3 |
| $A$1 | bloquée | bloquée | $A$1 |
| A$1 | bouge | bloquée | C$1 |
| $A1 | bloquée | bouge | $A3 |
Où les références mixtes sont-elles vraiment utiles ? Dans une grille avec une donnée en haut et une autre sur le côté, et la table de multiplication en est l'exemple classique. Mets 1 à 5 en B1:F1 et encore en A2:A6, puis écris =B$1*$A2 en B2. Recopie-la vers la droite jusqu'en F2 et vers le bas jusqu'en F6. Chacune des vingt-cinq copies lit toujours son nombre du haut dans la ligne 1 et son nombre du côté dans la colonne A. Une seule formule remplit correctement toute la grille.
Fais glisser pour tourner. Fais défiler ou pince pour zoomer.
- Recopier =A4*B1 vers le bas sans $. La référence du taux glisse vers B2, B3 et B4 en même temps que les prix, ce qui donne 0, #VALUE! et un 25 faux au lieu de trois taxes.
- Mettre le $ sur la mauvaise partie : =A4*B$1 se recopie bien vers le bas, =A4*$B1 non. $B1 bloque seulement la colonne, donc la ligne bouge encore. Décide dans quel sens la formule sera copiée, puis bloque la partie qui bougerait dans ce sens.
- Taper le taux dans chaque formule sous la forme *0.1. Ça marche jusqu'à ce que le taux change. Il faut alors retrouver et modifier chaque copie, et celle qu'on oublie a toujours l'air normale.
- Appuyer sur F4 dans Google Sheets en dehors d'une formule et attendre un $. F4 change le type de référence seulement pendant la saisie d'une formule. Ailleurs, c'est une des touches pour Rétablir.
- =A4*$B$1 est copiée une ligne plus bas. Que devient-elle ?
- Dans la barre de formule, le curseur est sur A1. Tu appuies une fois sur F4. Que devient A1 ?
- Et après un deuxième appui sur F4 ?
- =A$1 est copiée deux cellules plus bas et deux cellules plus à droite. Que devient-elle ?
- =$A1 est copiée deux cellules plus bas et deux cellules plus à droite. Que devient-elle ?
- =B$1*$A2 en B2 est copiée en C3. Que devient-elle ?
- Dans ce tableau, quelle valeur donne =E$1*$A5 ?
- B1 contient 10 % et B4 contient =A4*B1. Tu recopies B4 vers le bas jusqu'en B7. Qu'est-ce qui se passe mal ?
- La référence du taux quitte B1, donc B5 affiche 0 et les lignes suivantes sont fausses aussi
- Chaque ligne affiche #REF!
- Chaque ligne affiche le même prix
- Rien ne se passe mal
- Quelle référence garde la ligne fixe mais laisse bouger la colonne ?
- A$1
- $A1
- $A$1
- A1
- Quelle touche change le type de référence sur un Mac dans Google Sheets ?
- Fn+F4
- Ctrl+D
- Ctrl+~
- Shift+F2
- Le $ bloque la partie qui le suit : $A$1 les deux, A$1 la ligne, $A1 la colonne.
- Utilise $B$1 pour une cellule de donnée unique que toutes les lignes partagent.
- F4 fait défiler A1, $A$1, A$1, $A1 (Fn+F4 sur un Mac dans Google Sheets).
- Les références mixtes permettent à une seule formule de remplir toute une grille.
Sources de ce cours (19)
- Microsoft Support, "Switch between relative, absolute, and mixed references" (support.microsoft.com/en-us/office/dfec08cd-ae65-4f56-839e-5f0d8d0baca9: $ before column and row, F4 to switch, copying two down and two right: $A$1 stays, A$1 to C$1, $A1 to $A3, A1 to C3)
- Microsoft Support, "AVERAGE function" (support.microsoft.com/en-us/office/047bac88-d466-426c-a32b-8f33eb960cf6: text, logical values and empty cells in a reference ignored, zeros included; 10, 7, 9, 27, 2 gives 11, with 5 gives 10; mean 5, median 4, mode 3 for 2 3 3 5 7 10)
- Microsoft Support, "COUNT function" (support.microsoft.com/en-us/office/a59cd7fc-b623-4d93-87a4-d23bf411294c: only numbers counted, dates count, errors and text do not)
- Microsoft Support, "ROUND function" (support.microsoft.com/en-us/office/c018c5d8-40fb-4053-90b1-b3e7f61a213c: num_digits required; 2.15 to 2.2, -1.475 to -1.48, 626.3 to 1000, 1.98 to 0, -50.55 to -100)
- Microsoft Support, "IF function" (support.microsoft.com/en-us/office/69aed7c9-4e8a-4755-a9bc-aa8bbff73be2: value_if_false optional, text in quotes except TRUE and FALSE, 0 and #NAME? problems)
- Microsoft Support, "VLOOKUP function" (support.microsoft.com/en-us/office/0bbc8083-26fe-4963-8ab8-93a18ad188a1: range_lookup TRUE by default, sorted first column, #N/A, #REF!, #VALUE!, absolute table range, wildcards with FALSE)
- Microsoft Support, "XLOOKUP function" (support.microsoft.com/en-us/office/b7fd680e-6d10-43e6-84f9-88eae8bf5929: not in Excel 2016 and Excel 2019, exact match by default)
- Microsoft Support, "How to correct a #VALUE! error" (support.microsoft.com/en-us/office/15e1b616-fbf2-4147-9c0b-0a11a20e409e: + and * may not calculate cells with text, use SUM instead, IFERROR hides all errors)
- Microsoft Support, "Excel specifications and limits" (15 digits, 64 nested levels, 255 arguments)
- Google Docs Editors Help, "VLOOKUP" (support.google.com/docs/answer/3093318: is_sorted TRUE by default, FALSE recommended, search down to a larger value, " Apple" and "Apple " differ, wildcards, IFNA, absolute range)
- Google Docs Editors Help, "XLOOKUP" (support.google.com/docs/answer/12405947)
- Google Docs Editors Help, "ROUND" (support.google.com/docs/answer/3093440: places optional, 0 by default, rounding by magnitude)
- Google Docs Editors Help, "IF" (support.google.com/docs/answer/3093364: order of the answers is the most common problem, IFS)
- Google Docs Editors Help, "AVERAGE" (support.google.com/docs/answer/3093615: text ignored, any number of arguments)
- Google Docs Editors Help, "COUNTA" (support.google.com/docs/answer/3093991: zero-length strings and spaces count)
- Google Docs Editors Help, "Keyboard shortcuts for Google Sheets" (support.google.com/docs/answer/181110: F4 and Fn+F4 while entering a formula, F4 as Redo)
- Pryor Learning and Exceljet (F4 cycle A1, $A$1, A$1, $A1; = comparison not case-sensitive)
- Ablebits (IF without value_if_false returns FALSE)
- Spreadsheet Class (Sheets circular dependency shows #REF!)
Consulté le: 2026-10-19