You are currently viewing Références relatives & absolues dans Excel

Références relatives & absolues dans Excel

  • Post category:Excel / Excel illustré / Google Sheets
  • Commentaires de la publication :1 commentaire
  • Temps de lecture :9 min de lecture
  • Dernière modification de la publication :3 janvier 2024

Cet article est un extrait de l’e-book gratuit que vous pouvez retrouver au téléchargement à la rubrique « Mes livres » de ce site. Pour découvrir cet ebook et son contenu, cliquez simplement sur ce lien et téléchargez Le petit Excel illustré. Cliquez sur ce lien pour afficher tous les articles du petit Excel illustré.
Kitcreanet Petit Excel Illustré

RÉFÉRENCES RELATIVES

Une formule standard utilise des références Relatives. Pour expliquer cette notion prenons l’exemple suivant : la cellule D3 contient une formule de calcul. Cette formule de calcul contient deux références :

  • La référence à B3, une cellule qui est à 2 colonnes sur la gauche de D3,
  • La référence à C3, une cellule qui est à 1 colonne sur la gauche de D3.
Kitcreanet Excel Refrelatives Absolues

Copie la cellule D3 , et colle-la en cellule D4. Tu peux constater que Excel, a automatiquement adapté la nouvelle formule.

Comme la première référence (B3) faisait référence à une cellule située à deux colonnes sur la gauche, il a appliqué la même règle à la première référence de la formule que tu as collée. 2 colonnes sur la gauche par rapport à D4, c’est la cellule B4.

Et il a fait pareil pour la seconde référence C3. C3 est une référence à une cellule qui se trouve à une colonne à gauche par rapport à D3, alors la nouvelle référence relative à D4, une colonne sur la gauche, c’est C4.

Kitcreanet Excel Refrelatives Absolues 02

Mais que se passe t-il si tu colles à nouveau ta formule en D5 ? La même logique est appliquée. Mais cette fois, les références relatives pointent sur des cellules qui sont vides. Notre résultat est donc de 0.

Kitcreanet Excel Refrelatives Absolues 03

RÉFÉRENCES ABSOLUES

Pour expliquer les références absolues, prenons un autre exemple. J’ai un tableau de note pour mes élèves et je veux multiplier toutes les notes par deux.

Dans la cellule D3, j’ai une formule qui multiplie la note par le coefficient qui se trouve en B2. Ce coefficient est le même pour toute les notes, il est donc inutile et même interdit de recopier bêtement ce coefficient dans toutes les cellules du dessous. L’idéal est de placer ce coefficient à un seul endroit.

Si je sélectionne la poignée de recopie et que je tire vers le bas, je vais avoir le même problème que dans la page précédente. Les formules vont être réécrites avec des références relatives et mon calcul ne trouveras plus la bonne valeur.

Kitcreanet Excel Refrelatives Absolues 04

Mais on peut empêcher Excel de modifier la référence d’une cellule lorsque l’on fait une copie ou qu’on utilise la poignée de recopie. Pour ça il suffit de placer le signe DOLLAR ($) devant les coordonnées de ligne et de colonne de la cellule. Ainsi B3 devra être écrit $B$3. En faisant ça tu viens de créer une référence absolue. Cette référence dans ta formule de calcul ne changera plus, même si tu copies la cellule ou que tu utilises la poignée de recopie.

Kitcreanet Excel Refrelatives Absolues 05

Pour placer le signe DOLLAR ($) et transformer une référence relative en référence absolue tu peux les écrire manuellement ou utiliser le raccourci clavier. Place ton curseur sur la référence dans la barre de formule puis :

  • Sur Excel pour Windows, appuie sur la touche F4,
  • Sur Excel pour Mac, utilise le raccourci clavier cmd – T.

RÉFÉRENCES MIXTES

Il est possible de mettre le signe $ seulement sur la référence de colonne ou seulement sur la référence de ligne. C’est alors une référence mixte. Super, mais à quoi est-ce que ça sert me diras tu ?

VOICI UN EXEMPLE

Pour illustrer ça, on va retourner à l’école pour faire une magnifique table de multiplications ! Je vais te montrer comment la créer en moins de 10 secondes !

Tu te souviens, nous avons vu qu’en utilisant la poignée de recopie, les formules s’adaptent de façon relative à leur nouvelle position et modifient le nom des lignes et des colonnes. Mais parfois, on veut empêcher ça, comme dans l’exemple ci- dessous.

Regarde attentivement la formule qui se trouve en B2 : =$A2*B$1.

Pour faire la table de multiplications, il suffit de sélectionner la cellule B2 et d’utiliser la poignée de recopie vers la droite jusqu’en J2.

Kitcreanet Excel Refrelatives Absolues 06

Ensuite, il suffit de faire descendre la poignée de recopie jusqu’à J10.

Magique !

Kitcreanet Excel Refrelatives Absolues 07

OUI MAIS…. Ça fonctionne seulement parce-que j’ai utilisé des références mixtes !

Si ma formule était =A2*B1, en tirant la poignée de recopie vers la droite, la formule changerait pour devenir B2*C1, C2*D1, etc. Ce qui fausserait mes résultats. (Essaye de ton côté pour bien comprendre).

Comment corriger ça ?

Dans le premier terme de mon opération, la colonne ne doit jamais changer. Je dois toujours multiplier par les chiffres blancs sur fond vert de la colonne A. Je vais donc bloquer cette colonne dans ma formule en plaçant un dollar ($) devant le nom de la colonne.

Dans le second terme de mon opération, la ligne ne doit jamais changer. Je dois toujours multiplier par les chiffres blancs sur fond vert de la ligne 1. Je vais donc bloquer cette ligne dans ma formule en plaçant un dollar ($) devant le nom de la ligne.

Kitcreanet Excel Refrelatives Absolues 08

Et la magie opère !

Cette publication a un commentaire

Laisser un commentaire