Et si on parlait $$ ?

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 !

Vous avez déjà entendu parler de références relatives et des références absolues dans Excel ? 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. 
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’adopter et l’appliquer dès la fin de l’article à vous fichiers. 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.

Comprendre les bases des références dans Excel

Avant de plonger dans le vif du sujet, il est essentiel de comprendre à quoi correspondent les références dans Excel. Excel est un tableau composé de cellules (jusque-là, j’espère ne rien vous apprendre de nouveau), 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 ?

Une référence de cellule est l’adresse d’une cellule dans une feuille de calcul Excel. Cette référence est utilisée dans les formules pour indiquer à Excel où trouver les données nécessaires.

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.

Références relatives vs. Références absolues

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 allons-y doucement).

Les références relatives – Flexibilité et automatisation

Les références relatives se révèlent essentielles pour effectuer des calculs dynamiques. Elles sont particulièrement utiles lorsque vous travaillez avec des données qui doivent être copiées ou déplacées. 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.

Avantages des références absolues

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

Importance de la syntaxe des références absolues

C’est maintenant qu’intervient la notion de “$$” (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, en 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’aux Shorts, 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 grâce à l’utilisation des “$$”.

Quand utiliser quoi ?

Maintenant que vous comprenez la différence entre les références relatives et absolues, il est temps de savoir quand utiliser l’une ou l’autre.

– Utilisez des références relatives lorsque vous souhaitez que vos formules s’adaptent dynamiquement aux changements de position.

– Optez pour des références absolues lorsque vous voulez verrouiller une valeur spécifique dans une formule, comme une constante.

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

En partant de notre exemple précédent, imaginons maintenant que vous souhaitez calculer vos remises pour les soldes. Vous avez prévu 3 démarques, avec à chaque tour 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 (regardez par vou-même) :

L’utilisation des références mixtes est indispensable ici. Pour pouvoir étirer votre formule sur tout le tableau, vous devez écrire la formule “=$B3*C$2”. En écrivant $B3, vous figer la cellule de prix horizontalement en colonne B, et pour E$2 c’est l’inverse, le “$” étant situé devant le chiffre de la ligne, vous figez la position verticale en ligne 2. Ainsi : 

1. Quand vous étirez votre formule vers la droite, la cellule de prix reste B3, mais votre cellule de taux de remise suit les colonnes avec la formule
2. Quand ensuite vous retirez l’ensemble des formules vers le bas, la cellule de prix suit le mouvement vertical avec la formule, mais le taux de remise lui reste en ligne 2

Conseils et astuces pour exceller avec les références

Pour devenir un véritable expert en Excel, voici quelques conseils et astuces supplémentaires à garder à l’esprit :

• Utilisez des noms de cellules : Donnez des noms significatifs à vos cellules pour rendre vos formules plus lisibles.
• Résolution de problèmes courants : Apprenez à résoudre les problèmes courants liés aux références, tels que les erreurs de formules.
• 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 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)

Exercices de mise en pratique

Télécharger le fichier d’entrainement ici.

Conclusion : 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. Les références n’ont désormais plus de secret pour vous !

Bonne mise en pratique, d’Excel !

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.

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

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

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

Ces questions fréquentes devraient répondre à vos interrogations sur les références relatives et absolues dans Excel. Si vous en avez d’autres, n’hésitez pas à les poser dans les commentaires.