Formation excel : La référence relative et la référence absolue

Commentaire

Formation excel : La référence relative et la référence absolue :

Dans une feuille de calcul, chaque donnée est enregistrée dans une cellule qui est référencée par une adresse (une référence). Dans une formule, les données provenant de feuilles de calcul sont représentées par des adresses de cellules, appelées aussi références de cellules.
Ces références sont de 3 types : relatives, absolues et mixtes.
Dans ce tutoriel, on va s’intéresser aux types de référence relative et absolue. Le fichier Excel adopté sera le suivant :

refer2

Télécharger le classeur  reference   pour commencer.

Le tableau ci-dessous définit brièvement la notion de référence :

Type   Signification Exemple 
 Relative Identifie la position d’une cellule par rapport à une
autre cellule.
 D10
 Absolue Identifie la position d’une cellule par rapport à son
emplacement exact dans la feuille de calcul.
 $D$10

La référence relative

La référence D10 fait en réalité référence au contenu de la cellule qui peut être modifié depuis n’importe quelle autre cellule. Par exemple, si
on veut calculer le double du prix HT proposé, la formule sera : D2*2

refer3

Si on applique la formule à toute la colonne , la référence changera automatiquement : la formule pour la cellule suivante sera D3*2 jusqu’à la dernière cellule qui sera D7*2

La référence absolue

Pour comprendre ce concept, nous allons poser la contrainte suivante : Comment calculer le prix unitaire TTC avec la formule : Prix unitaire HT+ (Prix unitaire HT*TVA) sans changer la valeur de la TVA à chaque ligne ?

Si on procède normalement, c’est à dire appliquer la formule de la première ligne à toutes les lignes de la même colonne :

refer5

on aura un problème de calcul de valeur :

refer7

Vous remarquerez que les valeurs des cellules des colonnes Prix unitaire HT et Prix unitaire TTC  qui sont colorées en rouge ont les mêmes valeurs, ce qui n’est pas le résultat souhaité. Cette erreur est le résultat du changement de la référence de la cellule D10 dont la valeur doit rester inchangée pour toutes les formules de calcul utilisées pour le Prix unitaire TTC. Voila en détail ce qui s’est passé :

refer8

Logiquement la formule devrait avoir la valeur D10 fixe et inchangée pour pouvoir calculer 12% à chaque fois, mais Excel l’a adapté à chaque ligne. La référence absolue permet de garder la valeur de la référence d’une cellule fixe afin de l’utiliser dans une formule de calcul.

Pour cela nous allons utiliser le symbole « $ » (sans les guillemets) pour passer en mode référence absolue.

refer9

Ce qui donnera le résultat suivant:

refer10

La formule de la dernière ligne étant :

refer11

Il existe 3 façons d’utiliser la référence absolue :

Mettre « $ » devant la référence de la cellule (lettre de la colonne) ne modifie pas la référence de la colonne si la fonction est appliquée d’une colonne à une autre. Exemple : $D10

Mettre « $ » devant le numéro de la ligne ne modifie pas le numéro de la ligne si la fonction est adaptée vers une autre ligne. Exemple : D$10

Mettre « $ » devant la référence de la colonne et le numéro de la ligne ne modifie ni la référence de la cellule ni son numéro si elle est adaptée dans n’importe quel endroit de la feuille. Exemple : $D$10

Voici le résultat final.

Auteur

Tanguy Bianay

Je suis devenu un mordu de technologie et un passionné de l’informatique dès mes 12 ans. J’ai commencé à bidouiller et à vouloir comprendre ce vaste univers en évoluant très rapidement. Et en seulement peu de temps, on me considérait comme l’expert à appeler en cas de pépin. Et pour couronner le tout, je me suis spécialisé en programmation et en développement Web !

Lisez Ça Aussi

Articles Similaires

Vous Pensez Quoi De Cet Article?