Pourquoi une formule recopiée se décale

C'est le point du tableur où presque tout le monde décroche, et c'est presque toujours la même scène. Vous écrivez une formule juste. Vous la recopiez vers le bas. La première ligne est correcte, la deuxième donne n'importe quoi, la troisième affiche une erreur. Vous concluez que vous n'êtes pas fait pour ça.

Vous avez en fait rencontré un mécanisme, et un seul. Une fois compris, il explique à lui seul la majorité des comportements de tableur qui paraissent incompréhensibles.

En clair

Une formule ne retient pas les adresses que vous avez tapées. Elle retient le chemin entre sa propre cellule et celles qu'elle utilise. Quand on la recopie ailleurs, elle refait le même chemin depuis son nouvel emplacement : elle ne parle donc plus des mêmes cellules.

Le mécanisme, en une image

Vous êtes en C2 et vous écrivez =A2*B2.

Le tableur ne mémorise pas « A2 et B2 ». Il mémorise : « la cellule deux colonnes à ma gauche, multipliée par la cellule une colonne à ma gauche, sur ma propre ligne ».

Recopiez cette formule en C3. Elle refait ce chemin depuis C3, et devient donc =A3*B3. Recopiez-la en C10 : elle devient =A10*B10.

C'est ce qu'on appelle une référence relative : relative à la position de la formule.

flowchart TD
    A["En C2 j'écris =A2*B2"] --> B["Ce qui est mémorisé :<br/>deux cases à gauche fois une case à gauche"]
    B --> C["Recopiée en C3<br/>elle devient =A3*B3"]
    B --> D["Recopiée en C10<br/>elle devient =A10*B10"]

Ce mécanisme est une bonne nouvelle

Avant d'y voir un défaut, mesurez ce qu'il vous évite.

Vous avez un tableau de cent lignes avec un nombre d'heures et un taux horaire par ligne. Vous écrivez une formule sur la première ligne, vous double-cliquez sur le petit carré en bas à droite, et les cent lignes se calculent, chacune avec ses propres valeurs.

Sans ce décalage automatique, il faudrait écrire cent formules à la main. Le décalage n'est pas un bug à contourner : c'est la fonctionnalité principale du tableur, et elle marche toute seule tant que chaque ligne utilise ses propres données.

Le cas où ça casse : la valeur unique

Le problème apparaît dès qu'une formule doit utiliser une cellule qui, elle, ne doit pas bouger.

Reprenons les heures de bénévolat, avec cette fois un taux de remboursement de frais unique pour toute l'association, rangé une seule fois en B1 :

En C3, vous écrivez =B3*B1, et vous obtenez 96. C'est juste.

Vous recopiez vers le bas, et le mécanisme s'applique fidèlement : en C4, la formule devient =B4*B2. Elle multiplie donc les heures de Léa par le mot « Heures », ce qui donne une erreur. En C5, ce sera =B5*B3, qui donne un nombre, ce qui est bien pire, parce que ce nombre est faux et qu'il ne ressemble pas à une erreur.

La formule a fait exactement ce qu'on lui a demandé. Ce qui manquait, c'est de dire que B1 ne devait pas suivre le mouvement.

Figer une référence : le rôle du dollar

Pour dire « cette adresse ne bouge pas quand je recopie », on place un signe dollar devant la partie à figer.

Le dollar est une punaise. Il ne veut pas dire « dollar », il n'a aucun rapport avec une monnaie, et cette confusion trouble beaucoup de débutants. Lisez-le comme : « la partie qui suit est épinglée ».

La bonne formule devient donc, en C3 :

=B3*$B$1

Recopiée en C4, elle devient =B4*$B$1. En C5, =B5*$B$1. Les heures suivent la ligne, le taux reste en place. Les trois lignes sont justes.

Dans la pratique, les deux cas de loin les plus fréquents sont tout relatif et tout figé. Les formes mixtes servent surtout aux tableaux à double entrée, et vous les rencontrerez bien plus tard.

Le raccourci : plutôt que de taper les dollars, placez le curseur sur l'adresse dans la formule et appuyez sur une touche qui bascule entre les quatre écritures. C'est F4 dans Excel et dans Google Sheets, Maj+F4 dans LibreOffice Calc. Si le raccourci ne donne rien sur votre clavier, tapez simplement les dollars à la main : le résultat est identique.

Cas concret

Vous calculez les remboursements de frais de douze bénévoles. Le taux, 12 euros de l'heure, est en B1 parce qu'il changera peut-être l'an prochain et que vous ne voulez pas le retaper douze fois.

1. En C3, écrivez =B3*$B$1.
2. Double-cliquez sur le petit carré en bas à droite de C3 : les douze lignes se remplissent d'un coup.
3. Contrôle indispensable avant de transmettre quoi que ce soit : cliquez sur la dernière cellule calculée, C14, et lisez la barre de formule. Elle doit afficher =B14*$B$1. Si elle affiche =B14*B12, le dollar manque et tout le bas de la colonne est faux.
4. L'an prochain, le taux passe à 13 euros. Vous modifiez la seule cellule B1, et les douze remboursements se recalculent.

Ce contrôle de la dernière ligne vaut d'être pris comme une habitude : c'est la vérification la plus rentable de tout le cours.

Erreurs fréquentes

• Ne vérifier que la première ligne. C'est celle qui est toujours juste, puisque c'est celle où la formule a été écrite. L'erreur se voit sur la dernière.
• Croire que le dollar a un rapport avec une monnaie. C'est une punaise qui empêche une adresse de se décaler, rien d'autre.
• Figer toutes les adresses pour être tranquille. Une formule entièrement figée donne la même valeur sur toutes les lignes, et c'est faux aussi. On fige ce qui est unique, jamais ce qui est propre à la ligne.

À retenir

• Une formule mémorise un chemin, pas des adresses. Recopiée ailleurs, elle refait ce chemin depuis sa nouvelle position.
• Ce décalage est ce qui permet d'écrire une formule pour cent lignes : il n'est un problème que face à une valeur unique.
• Le dollar épingle : $B$1 pointera toujours sur B1, où que la formule soit recopiée.
• Après toute recopie, cliquez sur la dernière cellule et lisez la barre de formule.

Mini-exercice

Saisissez 12 en B1, puis trois nombres d'heures en B3, B4 et B5.

1. En C3, écrivez =B3*B1 sans dollar, et recopiez vers le bas jusqu'en C5. Regardez le résultat, puis cliquez sur C5 et lisez la formule dans la barre : vous voyez le mécanisme à l'oeuvre.
2. Corrigez C3 en =B3*$B$1 et recopiez à nouveau. Relisez la formule en C5.
3. Changez la valeur de B1 : les trois lignes se recalculent ensemble. Vous venez de comprendre le point qui bloque le plus de monde.