Références (adresses) absolues et relatives dans Excel [tutoriel]

La notion de "cellule" constitue le fondement des tableurs tels que Microsoft Excel. L'un des atouts majeurs d'un tableur est la capacité de référencer une cellule spécifique (A) dans une autre cellule (B). Cette référence peut servir à afficher la même valeur que la cellule (A) dans la cellule (B) ou, plus fréquemment, être utilisée dans des formules.

Dans une formule Excel, il existe quatre manières distinctes de mentionner une autre cellule. Par exemple : B2, $B$2, $B2, B$2.

Le concept peut sembler quelque peu abstrait, mais les exemples ci-après vous permettront de mieux comprendre.

Exemple d'utilisation des adresses de cellule dans une formule Excel.

Les adresses de cellules utilisées dans une formule peuvent être relatives, absolues ou mixtes. De nombreux utilisateurs préfèrent éviter la complexité et se contentent d'utiliser des adresses relatives (telles que "A2"). Cependant, l'importance des adresses absolues (et mixtes) ne doit pas être sous-estimée, en particulier lorsqu'on travaille avec de vastes plages de formules ou lorsqu'on copie fréquemment des formules d'une cellule à l'autre.

Les adresses relatives de cellules dans Excel

Les adresses relatives ne contiennent pas de signe "$" (comme C21). Lorsque vous copiez une formule contenant une telle adresse, elle s'adapte automatiquement. Par exemple, si vous copiez la formule =A1+B1 de la cellule C1 dans la cellule C2, la formule s'adaptera en =A2+B2. Les colonnes s'adaptent également automatiquement. Ainsi, si vous copiez la formule =A1+B1 de la cellule C1 dans la cellule D2, la formule se transformera en =B2+C2.

Les adresses absolues de cellules dans Excel

Les adresses absolues comportent le signe"$" devant les lettres et les chiffres (comme $A$1). Lorsque vous copiez une formule contenant une telle adresse, elle ne s'adapte pas. Par exemple, si vous copiez la formule =A1+$B$1 de la cellule C1 dans la cellule C2, la formule restera =A2+$B$1. Les colonnes ne s'adaptent pas non plus. Ainsi, si vous copiez la formule =A1+$B$1 de la cellule C1 dans la cellule D2, la formule se transformera en =B2+$B$1.

Les adresses mixtes de cellules dans Excel

Les adresses mixtes comportent le signe "$" devant la lettre ($A1) ou le chiffre (A$1). Lorsque vous copiez une formule contenant une adresse mixte, seule la partie absolue ne s'adapte pas. Par exemple, si vous copiez la formule =A1+$B1 de la cellule C1 dans la cellule C2, la formule s'adaptera en =A2+$B2. Si vous copiez la formule =A1+B$1 de la cellule C1 dans la cellule D2, la formule se transformera en =B2+C$1.

• la formule =$A1+$B1 copiée de "C1" en "C2" donnera: "=$A2+$B2" (les lignes sont adaptées)
• la formule =A$1+B$1 copiée de "C1" en "C2" donnera: "=A$1+B$1" (les lignes ne s'adaptent pas)
• la formule =$A1+$B1 copiée de "C1" en "D2" donnera: "=$A2+$B2" (les lignes sont adaptées, les colonnes restent les mêmes)
• la formule =A$1+B$1 copiée de "C1" en "D2" donnera: "=B$1+C$1" (les lignes ne s'adaptent pas, les colonnes s'adaptent)

Les adresses de cellule dans Excel: exemple en ligne

Après la théorie, voici un exemple en ligne où vous pouvez tester vous-mêmes les différentes possibilités. Pour voir la barre de formule, téléchargez le fichier et ouvrez-le dans l'Excel sur votre ordinateur. Prenez la cellule verte et copiez la vers les cellules en jaune. Vous verrez alors la différence de comportement entre les références relatives et absolues.

 Astuce: pour ne pas devoir vous "amuser" en tapant les "$", il y a un raccourci clavier pour changer le type de l'adresse: la touche "F4". Dans la Barre de formule, vous cliquez sur l'adresse que vous voulez modifier et vous appuyez une ou plusieurs fois sur la touche F4. Cette touche fait la boucle entre les 4 possibilités. Pour voir d'autres raccourcis clavier utiles dans Excel…

Exemples pratiques d'utilisation des adresses absolues, relatives et mixtes dans la vie quotidienne

Voici quelques exemples pour illustrer la théorie que nous venons de voir.

• Calcul de la TVA: Supposons que vous ayez une liste de prix et que vous souhaitiez ajouter une colonne avec les "prix TVAC". Dans ce cas, vous pourriez utiliser une adresse mixte pour référencer le taux de TVA. Par exemple, si la cellule B1 contient le taux de TVA et que les prix sont listés dans la colonne A (de A2 à A10), vous pourriez écrire la formule =A2*(1+$B$1) dans la cellule B2, puis la copier jusqu'à B10. Les adresses mixtes permettront de garder le taux de TVA fixe tout en adaptant les références aux prix.
• Conversion de devises: Lorsque vous avez besoin de convertir des montants dans différentes devises, les adresses absolues peuvent être utiles. Supposons que vous ayez un tableau avec des montants en dollars (colonne A) et que vous souhaitiez les convertir en euros (colonne B) en utilisant le taux de change dans la cellule C1. Vous pourriez écrire la formule =A2*$C$1 dans la cellule B2, puis la copier jusqu'à B10. Les adresses absolues permettront de garder le taux de change fixe tout en adaptant les références aux montants en dollars.
• Calcul de pourcentages: Imaginez que vous ayez un tableau avec des scores de tests pour plusieurs élèves (colonnes B à E) et que vous souhaitiez calculer le pourcentage de réussite pour chaque élève (colonne F). Les adresses mixtes peuvent être utiles ici. Si les scores maximaux pour chaque test sont respectivement dans les cellules B1, C1, D1 et E1, vous pourriez écrire la formule =(B2/$B$1+C2/$C$1+D2/$D$1+E2/$E$1)/4 dans la cellule F2, puis la copier jusqu'à F10. Les adresses mixtes permettront de garder les scores maximaux fixes tout en adaptant les références aux scores des élèves.
• Création d'un tableau croisé: Parfois, vous devez créer un tableau croisé où chaque cellule est le produit des valeurs des cellules de la même ligne et de la même colonne. Par exemple, si vous avez des unités vendues en colonne A (de A2 à A5) et des prix unitaires en ligne 1 (de B1 à E1), vous pourriez utiliser des adresses mixtes pour calculer le revenu pour chaque combinaison. Dans la cellule B2, vous écririez la formule =$A2*B$1, puis la copieriez jusqu'à E5. Les adresses mixtes permettront d'adapter les références aux unités vendues et aux prix unitaires.

Pour aller plus loin en Excel…

Maintenant que vous maîtrisez la notion des adresses relatives et absolues dans Excel, place à la pratique. Et par où commencer? Je vous propose la liste de toutes les fonctions disponibles dans Excel. Vous devriez y trouver de l'inspiration…

Vous pouvez également consulter les articles et tutoriels suivants:

• Tout sur le Tableau croisé dynamique Excel
• Tutoriels sur les principales fonctions Excel: SI, NB.SI, SOMME.SI, RECHERCHEV ou la nouvelle RECHERCHEX
• Comment convertir Excel en PDF
• Calculer l'âge en Excel – tutoriel
• Excel Online Gratuit – pourquoi ne pas en profiter!
• la page Téléchargement où vous trouverez nombreux outils Excel gratuits
• la page Ressources où se trouve la liste des meilleurs livres et manuels sur Excel

Et pour finir, en ce qui concerne l'utilisation des adresses de cellules dans Excel, voici la page internet de Microsoft dédiée à ce sujet.