Qu’Est Ce Qu’Une Référence ?

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 absolue et relative.

Avant de commencer, créez le classeur suivant pour poursuivre la suite de ce tutoriel : (Vous pouvez aussi le télécharger grâce au lien en dessous)

Télécharger le classeur  reference .

La Différence Entre La Référence Relative Et La Référence Absolue

Le tableau ci-dessous définit brièvement la notion de référence ainsi que la différence entre les deux :

La Référence Relative

Nous allons commencer par calculer les valeurs de la colonne « Prix unitaire » (la dernière colonne) grâce à la formule :

Prix unitaire TTC = Prix unitaire HT * 2

• Il suffit de sélectionner la première cellule de la colonne « Prix unitaire TTC » puis d’appuyer sur la touche « = » du clavier.

• Cliquez ensuite sur la première cellule de la colonne « Prix unitaire HT » pour faire apparaître D2 dans la cellule « E » juste après le signe « =« 

• Saisissez ensuite le reste de la formule, soit « *2« 

• Pour terminer, cliquez sur le bouton « Entrée » de votre clavier et la valeur sera calculée.

• Il ne reste plus qu’à appliquer cette formule sur le reste de la colonne en glissant le curseur à partir du coin de la cellule déjà calculée.

En appliquant 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

Le fait que les valeurs sont automatiquement calculées grâce à la formule appliquée est possible car les références des cellules changent automatiquement pour s’adapter à la formule : c’est la référence relative.

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 :

on aura un problème de calcul de valeur :

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é :

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 solution serait de dire à Excel que la valeur D10 doit rester fixe : c’est le principe de la référence absolue.

Soit, permettre 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.

Ce qui donnera le résultat suivant:

La formule de la dernière ligne étant :

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. (A télécharger)

[divider style= »7″]