in

Levez Enfin Le Mystère Sur La Référence Absolue Dans Excel

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)

refer2

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 :

 

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

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.

refer3

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 :

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 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.

formation excel

Ce qui donnera le résultat suivant:

référence absolue

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




Rédigé par 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 !

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

La Mise En Forme Complète D’un Classeur Excel -Wikiclic

enlever un élément d'une photo

Enlever Un élément D’Une Photo Très Facilement