Références absolues et références relatives dans Excel, ça vous parle ?
J’imagine que oui, mais ce concept n’évoque sûrement pas grand-chose pour vous. Pas de panique, nous allons éclaircir le sujet ensemble.
Et si on parlait $ $ ?
Et non, je ne vous parle pas d’argent ! Mais bel et bien de cellules d’un tableau Excel.
Vous ne voyez pas le rapport ? Restez avec moi, vous allez vite comprendre !
Que vous soyez un total débutant ou un utilisateur expérimenté d’Excel, cet article vous guidera pas à pas à travers le monde fascinant des références relatives et absolues. Nous allons explorer ce concept de manière simple et accessible pour que vous puissiez l’appliquer à vos fichier de suite.
Préparez vous à (re)découvrir comment les références absolues et relatives peuvent travailler au service de votre carrière professionnelle en vous aidant à utiliser Excel plus efficacement, sans avoir besoin de maîtriser les macros ni le VBA.
1 – Comprendre les bases des références absolues et relatives dans Excel
Excel est un tableur composé de cellules [jusque-là, j’espère ne rien vous apprendre de nouveau.]
Mais avant d’aller plus loin et plonger dans le vif du sujet, il est essentiel de comprendre que chaque cellule peut être identifiée par une référence, généralement sous la combinaison de lettres et de chiffres.
Qu’est-ce qu’une référence de cellule ?
La référence d’une cellule est l’adresse de celle-ci dans une feuille de calcul Excel. Cette adresse est l’association du nom de la colonne et du numéro de ligne où elle se site.
Dans l’exemple ci-dessous, la référence de la cellule sélectionnée est “B2”, car elle fait référence à la cellule se trouvant à l’intersection de la colonne B et de la ligne 2.
Le sujet de cet article est un concept légèrement plus poussé, mais tout aussi fondamental sur Excel. Ces références peuvent être “absolues” ou “relatives” (ou une combinaison des deux, mais commençons par le début).
2 – Les références relatives Excel : flexibilité et automatisation
Une référence relative dans une formule Excel s’ajuste automatiquement lorsque vous copiez ou déplacez la formule vers une nouvelle cellule. Cela signifie que la formule “comprend” le déplacement et ajuste les références en conséquence.
Exemple concret
Supposons que vous ayez une liste de vêtements (colonne A) avec un prix (colonne B) et un montant de remise (colonne C) pour chaque article. En colonne D vous souhaitez calculer le montant de la remise.
Pour le premier article, vous pouvez utiliser la barre de formule avec “=B2*C2” (multiplie le prix du produit par le taux de remise).
Si vous étendez la formule avec la poignée de recopie (petit carré vert à droite) jusqu’à la cellule D6, vous remarquez que la formule à décalé la référence de prix (B6) et le taux de remise (C6) pour chaque ligne.
3 – Les références absolues Excel : stabilité et contrôle
Les références absolues sont l’un des piliers fondamentaux d’Excel, et sont le contraire des références relatives. Elles restent constantes, peu importe où vous copiez ou déplacez la formule.
Importance de la syntaxe des références absolues
C’est maintenant qu’intervient la notion de “$$” évoqué dans l’introduction [comme promis, on y revient.]
La syntaxe pour fixer une référence absolue dans une formule est simple : il vous suffit de placer un signe “$” devant la lettre de la colonne et le numéro de la ligne de la cellule que vous souhaitez bloquer pour figer celle-ci.
Par exemple, si vous voulez fixer la cellule “B2”, celle-ci devient “$B$2“, une référence absolue.
ASTUCE : Vous pouvez passer d’une référence relative à une référence absolue en appuyant sur la touche “F4” de votre clavier.
Exemple concret
Reprenons notre exemple précédent et imaginons que vous souhaitez appliquer une remise constante, contenu de la cellule C3, à tous les vêtements. Dans ce cas, la formule pour les T-shirts devient “=B6*$C$2”.
De cette manière, en étirant votre formule jusqu’en ligne 6, vous remarquez que le prix du vêtement suit bien le déplacement de la formule (D6), mais le taux de remise lui reste fixe (C2) grâce à l’utilisation des “$$”.
4 – Quoi choisir : références absolues ou relative ?
Maintenant que vous comprenez la différence entre les références relatives et absolues, il est temps de savoir pourquoi et quand utiliser l’une ou l’autre.
Avantages des références relatives
Les références relatives simplifient les tâches répétitives. Vous n’avez pas besoin de réécrire des formules à chaque fois que vous copiez une formule vers une nouvelle cellule. Excel s’occupe automatiquement de l’ajustement des références pour vous.
Elles offrent une flexibilité essentielle lors de la création de feuilles de calcul dynamiques.
Avantages des références absolues Excel
Les références absolues sont essentielles lorsque vous avez besoin que certaines cellules restent “figées”, quel que soit le déplacement de votre formule. Elles offrent une stabilité et un contrôle précis sur votre modèle Excel.
Elles sont généralement utilisées pour fixer des valeurs spécifiques dont vous souhaitez garder la valeur.
5 – Technique avancée : les références mixtes
Je vous entends d’ici : “mais qu’est-ce que c’est encore que cette référence ?”
C’est simple, vous allez voir.
Dans de nombreux cas, vous utiliserez une combinaison des deux, appelée référence mixte. Une référence mixte verrouille une partie de la référence tout en permettant à l’autre partie de s’ajuster. C’est aussi utile que les références absolues et ça pourra vous sauver pour beaucoup scénarios variés.
Syntaxe des références mixtes
Pour une référence mixte nous allons aussi utiliser le “$”, mais uniquement pour figer la colonne ou la ligne.
Si vous faites référence à “$B2”, c’est une référence mixte dont la position est figée en colonne B, mais qui peut se déplacer verticalement de ligne en ligne. À l’inverse, “B$2” est une référence mixte dont la position est figée en ligne 2, mais qui peut se déplacer horizontalement de colonne en colonne.
Exemple concret
Imaginons maintenant que vous souhaitez calculer vos remises pour les soldes. Vous avez prévu 3 démarques, avec à chaque fois un taux de remise différente.
Pour calculer le montant de la première remise en C2 vous faites “=B3*C2”.
Mais cette formule utilise des références relatives, si vous l’étendez sur tout le tableau, vous trouverez des résultats faux :
L’utilisation des références mixtes est indispensable ici. Voyons comment les utiliser dans ce cas :
- Pour pouvoir étirer votre formule sur tout le tableau, vous devez écrire la formule “=$B3*C$2”. En écrivant $B3, vous figez la cellule de prix horizontalement en colonne B, et pour C$2 c’est l’inverse, le “$” étant situé devant le chiffre de la ligne, vous figez la position verticale en ligne 2 :
- Quand vous étirez votre formule horizontalement, vers la droite, la cellule de prix reste $B3, mais votre cellule de taux de remise suit les colonnes avec la formule jusqu’en E$2
- Maintenant, si vous étendez l’ensemble des formules vers le bas, la cellule de prix suit le mouvement vertical avec la formule pour atteindre $B7, mais le taux de remise lui reste en ligne 2
6 – Conseils et astuces pour exceller avec les références absolues et relatives
Pour devenir un véritable expert en Excel, voici quelques conseils et astuces supplémentaires à garder à l’esprit :
- Utilisez la touche de fonction F4 comme raccourcis : en sélectionnant une cellule (B2), si vous appuyez une fois sur F4 elle devient absolue ($B$2), si vous appuyez une seconde fois vous figez uniquement la ligne (B$2), et si vous appuyez une 3ème fois sur F4, vous ne figez que la colonne ($B2).
- Définissez des noms de cellules : Donnez des noms significatifs à vos cellules pour rendre vos formules plus lisibles.
- Expérimentez et pratiquez : La maîtrise des références absolues et relatives demande de la pratique. Plus vous les utilisez, plus vous deviendrez compétent.
- Utilisez les références de cellules absolues et mixtes dès que possible : vous pouvez le intégrer dans :
- différentes feuilles d’un même classuer Excel
- d’autres formules Excel (RECHERCHEV, SI, SOMME, CONCATENER…)
- d’autres fonctionnalités d’Excel (mise en forme conditionnelle, liste déroulante, filtres…)
7 – Ce qu’il faut retenir
Vous voilà maintenant armés pour maîtriser les références relatives et absolues dans Excel.
En comprenant quand et comment les utiliser correctement, vous pouvez automatiser des tâches, simplifier des calculs et améliorer considérablement votre efficacité. Que vous soyez en train de gérer des finances personnelles, de créer des rapports professionnels ou de réaliser des analyses de données complexes, ces compétences vous seront précieuses.
Bonne mise en pratique sur Excel !
8 – Les 5 questions les plus fréquentes
- Q1. Pourquoi devrais-je utiliser des références absolues ou relatives ?
Les références absolues sont utiles lorsque vous avez besoin que certaines cellules restent fixes, tandis que les références relatives sont idéales pour les tâches répétitives ou les calculs qui changent de position.
- Q2. Les références mixtes sont-elles couramment utilisées ?
Oui, les références mixtes sont couramment utilisées pour des tâches spécifiques où vous avez besoin de la flexibilité des références relatives et de la stabilité des références absolues.
- Q3. Existe-t-il des raccourcis clavier pour travailler avec des références ?
Oui, Excel offre des raccourcis clavier pratiques pour basculer entre les types de références.
- Q4. Comment puis-je éviter les erreurs courantes liées aux références dans Excel ?
En comprenant bien les concepts des références absolues, relatives et mixtes, et en les pratiquant régulièrement, vous minimisez les erreurs possibles.
- Q5. Comment créer une plage de données dynamique ?
Utilisez des références relatives pour créer des plages de données dynamiques qui s’ajustent automatiquement à de nouvelles entrées.
En comprenant bien les concepts des références absolues, relatives et mixtes, et en les pratiquant régulièrement, vous minimisez les erreurs possibles.
9 – Ressources supplémentaires
Pour compléter vos compétences Excel, vous pouvez aussi lire les articles :