Comment les références s’ajustent lors d’une copie

Lorsque vous écrivez =A2 dans une formule, le tableur ne mémorise pas réellement « la cellule A2 ». Il mémorise une direction et une distance, comme « la cellule située deux colonnes à ma gauche ».

On appelle cela une référence relative. Comme la référence est décrite par rapport à la position propre de la formule, elle s'ajuste chaque fois que vous copiez la formule ailleurs. C'est ce comportement qui explique pourquoi la recopie fonctionne si bien.

Configurez deux colonnes de nombres :

• A2 = 100, B2 = 50
• A3 = 200, B3 = 80

Dans C2, saisissez =A2+B2. La cellule affiche 150. Copiez maintenant C2 vers le bas, jusqu'à C3. Le résultat est 280, car la formule est devenue =A3+B3.

Vous n'avez rien modifié. Les références ont suivi la formule d'une ligne vers le bas.

Lorsque vous copiez une formule vers le bas, seuls les numéros de ligne changent. Les lettres des colonnes restent en place.

À partir de =A2+B2 :

• Une ligne vers le bas donne =A3+B3
• Deux lignes vers le bas donnent =A4+B4
• Trois lignes vers le bas donnent =A5+B5

Les colonnes A et B ne bougent jamais, car vous ne vous déplacez pas sur le côté.

Copier une formule sur le côté modifie les lettres des colonnes et laisse les numéros de ligne inchangés.

Supposons que B2 contienne =B1*2. Copiez-la vers la droite, dans C2 puis D2 :

• C2 devient =C1*2
• D2 devient =D1*2

La ligne reste la ligne 1, car vous vous êtes déplacé uniquement sur le côté, et non vers le bas. Déplacez-vous dans les deux directions à la fois et les deux parties se décalent ensemble.

Voici un modèle mental utile : une référence relative mémorise un décalage depuis la cellule contenant la formule.

Si C2 contient =A2, le décalage est « deux colonnes à gauche, même ligne ». Copiez cette formule dans C5 et le décalage est réappliqué depuis son nouvel emplacement : deux colonnes à gauche de C5, sur la même ligne, ce qui donne A5.

La flèche entre la formule et sa cible conserve toujours la même longueur et la même direction.

Imaginez une facture de 1 000 lignes. Chaque ligne nécessite de multiplier la quantité par le prix. Sans références relatives, vous devriez écrire 1 000 formules différentes à la main.

Avec elles, vous écrivez une seule fois =A2*B2, puis vous recopiez vers le bas. Chaque ligne pointe automatiquement vers sa propre quantité et son propre prix. Une formule, mille résultats corrects, aucune faute de frappe.

Les références relatives transforment une tâche fastidieuse en un simple double-clic.

Vous pouvez le vérifier vous-même. Après la recopie, cliquez sur l'une des cellules copiées et regardez la barre de formule en haut.

Copiez =A2+B2 vers le bas sur cinq lignes, puis cliquez sur la dernière cellule. La barre de formule affiche =A7+B7, et non =A2+B2. Chaque cellule contient sa propre version ajustée.

Parcourir la colonne en cliquant sur les cellules et lire la barre de formule est le meilleur moyen de comprendre intuitivement le fonctionnement de la copie.

L'ajustement relatif correspond généralement à ce que vous recherchez, mais pas toujours. Supposons que chaque ligne doive être multipliée par un taux de taxe unique situé dans la cellule E1.

Si vous écrivez =C2*E1 et recopiez vers le bas, la partie E1 dérive vers E2, E3, E4, et ainsi de suite, pour arriver sur des cellules vides. Le résultat est incorrect.

Vous voulez que C2 descende, mais que E1 reste verrouillée. Pour cela, vous avez besoin d'une référence absolue, qui sera présentée dans le prochain cours.

Voici un aperçu de la solution. Placer un signe dollar devant une partie d'une référence la verrouille, afin qu'elle ne puisse pas se décaler.

Écrivez =C2*$E$1 et recopiez vers le bas. C2 devient alors C3, C4, et ainsi de suite, tandis que $E$1 reste à chaque fois fixé sur le taux de taxe.

Vous maîtriserez bientôt cette astuce du signe dollar. Pour l'instant, retenez simplement qu'elle existe.

Tout ce qui est présenté ici fonctionne de manière identique dans Microsoft Excel et Google Sheets. Les deux applications utilisent les références relatives par défaut, décalent les lignes lors d'une recopie vers le bas et décalent les colonnes lors d'une recopie sur le côté.

La syntaxe de verrouillage avec le signe dollar est également la même dans les deux applications. Les compétences que vous développez en copiant des formules dans l'une se transfèrent directement à l'autre.

Si une colonne recopiée affiche des nombres incorrects, la cause est presque toujours une référence qui s'est décalée alors qu'elle aurait dû rester en place.

Cliquez sur une cellule qui se comporte mal et lisez la barre de formule. Demandez-vous : pointe-t-elle vers l'emplacement attendu ? Si une référence qui devrait être fixe a dérivé, vous avez trouvé votre erreur, et le signe dollar est la solution.

Vérifiez votre compréhension du décalage des références.

Vous comprenez maintenant le mécanisme qui se trouve derrière la recopie :

• Les références sont relatives par défaut et mémorisées comme un décalage depuis la cellule contenant la formule.
• La recopie vers le bas décale les numéros de ligne ; la recopie sur le côté décale les lettres des colonnes.
• Cet ajustement automatique permet à une seule formule de servir pour des milliers de lignes.
• Lorsqu'une référence doit rester en place, un signe dollar la verrouille ; vous apprendrez cela dans le prochain cours.

Lisez la barre de formule chaque fois qu'une colonne recopiée semble incorrecte.