I. Introduction▲
L'utilisation des références absolues et relatives dans un tableur Excel fait partie des connaissances de base qu'il faut maîtriser pour travailler avec Excel.
Je rencontre pourtant, sur les forums comme durant les formations que je donne, des personnes utilisant Excel depuis un certain temps, et qui ne sont pourtant pas encore à l'aise avec ces notions et l'utilisation des références absolues et relatives.
Ce petit cours n'a d'autre but que de schématiser l'utilisation de ces références. À sa lecture, vous comprendrez quand utiliser ou pas le signe $ dans vos références.
I-A. Prérequis▲
Être familiarisé avec l'environnement d'Excel.
I-B. Niveau▲
Ce cours s'adresse a priori aux débutants.
I-C. Objectifs du cours▲
Les objectifs de ce cours sont :
- comprendre le concept de références absolues et relatives ;
- apprendre à utiliser les références absolues et relatives ;
- apprendre à créer moins de formules.
II. Références relatives et absolues▲
II-A. Premier tableau : Formule avec des références relatives▲
Créons un petit tableau reprenant les salaires à payer pour quelques employés sur le premier trimestre.
Ce tableau est un tableau tout simple, avec la saisie du montant à payer.
II-A-1. Calculer la somme d'une plage de cellules▲
Nous allons ajouter une somme à ce tableau, de façon à totaliser les salaires par mois.
Commençons par ajouter la somme pour le mois de janvier
Il existe une façon plus simple d'insérer une somme sur l'ensemble d'un tableau à double entrée. Nous ne la verrons pas ici.
En tirant cette formule vers la cellule de droite, nous constatons que la formule a été modifiée par Excel, pour nous donner la somme des cellules C2:C6
II-A-2. Comprendre ce qui a été demandé à Excel▲
Nous avons l'impression qu'Excel a adapté notre formule, pour la rendre conforme à ce que nous attendions intuitivement, à savoir copier la formule de la colonne B vers la colonne C pour obtenir le total de février.
Excel n'a rien adapté du tout! Il a accompli ce que nous lui avons demandé, ni plus ni moins.
C'est donc que notre demande initiale, à savoir la formule
=SOMME(B2:B6)n'est pas, en français, calculer la somme des cellules B2:B6, et donc que ce que nous voyons dans la formule n'est pas ce qui est réellement demandé à Excel.
Avec Excel, il faut constamment garder à l'esprit que ce qui est n'est pas toujours ce que l'on voit!
C'est vrai pour des formules, mais aussi pour des formats de cellules qui affichent parfois autre chose que la valeur de la cellule utilisée par Excel.
Nous avons saisi une formule en B7, et cette formule fait référence aux cellules B2:B6.
Relativement à B7, où se trouvent les cellules saisies dans la formule?
B2 se trouve 5 lignes plus haut dans la même colonne, et B6 se trouve 1 ligne plus haut dans la même colonne.
Notre vraie demande à Excel est donc :
calculer la somme de la plage de cellules qui commence 5 lignes plus haut dans la même colonne (B2) et qui se termine 1 ligne plus haut dans la même colonne (B6).
Lorsque nous recopions cette formule en C7, Excel traite notre demande:
Calculer la somme de la plage de cellules qui commence 5 lignes plus haut dans la même colonne (C2) et qui se termine 1 ligne plus haut dans la même colonne (C6).
Donc, Excel n'est pas magique et, peut-être sans le savoir, notre demande initiale « =SOMMME(B2:B6) » était bien formulée, mais peut-être que nous l'interprétions mal.
Nous pouvons recopier cette formule pour Mars et Excel l'adapte à notre demande.
Pour bien fixer les choses, nous allons suivre le même raisonnement pour la création de la formule qui totalisera le trimestre par employé.
En E2, nous saisissons la formule =SOMME(B2:D2), ce qui signifie:
Calculer la somme de la plage de cellules qui commence sur la même ligne, 3 colonnes à gauche (B2) et qui se termine sur la même ligne, 1 colonne à gauche (D2).
Nous pouvons donc recopier cette formule vers le bas pour les autres employés, ainsi, pourquoi pas, que pour les totaux par mois.
II-A-3. Un autre système de références▲
Il y a bien longtemps, les premiers tableurs utilisaient un autre système de références. Les colonnes n'étaient pas repérées par des lettres, mais par des chiffres. Pour des raisons de compatibilité, Excel a gardé la possibilité d'utiliser ce système de références, appelé L1C1 (R1C1 en anglais). Au passage, nous noterons que ce système inverse l'ordre des axes puisque A1 devient L1C1, D25 devient L25C4.
Via les options d'Excel (options de calcul), nous pouvons basculer dans ce système de références. Excel modifie l'affichage des entêtes de colonnes et des formules en conséquence.
La formule =SOMME(B2:B6) devient alors =SOMME(L(-5)C:L(-1)C. On remarquera qu'en D7, la formule est identique. Excel n'a donc pas adapté la formule, c'est bien notre formule qui a été recopiée de B7 en D7, sans aucune modification. Excel a simplement adapté l'affichage en fonction du système de références utilisé.
On remarquera que, même si le système L1C1 peut sembler plus compréhensible, il amène vite à des formules illisibles, sans même parler de la saisie manuelle de formules un peu complexes. C'est pourquoi le système de références A1 lui est largement préféré. J'ai basculé en mode L1C1 pour aider à la compréhension de notre formule, mais j'utiliserai évidemment le système A1 pour la suite du cours.
II-B. Deuxième tableau : Des références relatives et une référence mixte▲
Pour la clarté de l'exposé, les différents tableaux sont placés sur la même feuille. Normalement, dans le cadre d'un travail propre avec Excel, chaque tableau sera placé sur une feuille (un onglet) séparée, notamment pour faciliter la maintenance et l'évolution de notre classeur.
Les tableaux ne sont bien entendu que rarement aussi simples à créer.
Imaginons que le salaire à payer soit dépendant de prestations réalisées et d'un taux horaire variable par employé.
Nous devrions alors nous baser sur les tableaux suivants pour créer notre formule
II-B-1. Création de la formule de base▲
Dans ce tableau, nous comprenons que le salaire est constitué du produit des heures effectuées par le taux horaire de l'employé.
Pour Pierre en janvier, nous aurons donc la formule illustrée ci-dessous.
Un copier-coller de cette formule jusqu'en D6 nous donne un résultat inattendu.
Si nous éditons la formule en D6, nous constatons qu'Excel plonge « dans le vide » pour aller chercher le taux horaire de Paul.
II-B-2. Comprendre notre demande et la reformuler à Excel▲
En B2, nous avons demandé à Excel d'aller chercher le taux horaire de Pierre dans la même colonne, 17 lignes plus bas.
Transposée en D6, cette demande fait donc pointer Excel dans la même colonne, 17 lignes plus bas, soit en D23.
Excel a donc interprété correctement notre formule !
Le numéro de ligne pour le taux horaire est correct, mais il fallait dire à Excel de rester dans la colonne B, puisque nous voulons bien entendu éviter de devoir recopier le taux horaire pour tous les mois pris en compte dans le tableau.
À défaut de pouvoir formuler cette demande, nous serions contraints de devoir créer une formule pour chaque mois du tableau…
Nous allons donc demander à Excel de « geler » la colonne B pour les taux horaires, et ce « gel » de la colonne va être demandé en faisant précéder la référence de la colonne par le signe $.
Si je reprends la formule en B2, j'aurai donc ceci
Recopiée en B2:D6, la formule est correcte, et l'édition de cette formule en D6 met en évidence le gel de la colonne B, grâce au signe $devant la lettre de la colonne.
Lorsque l'on saisit une référence dans une cellule, le raccourci F4 permet de boucler sur les références utilisables d'une cellule ou d'une plage.
Ainsi, si on saisit =C1 dans une cellule puis que l'on presse sur F4, on obtiendra $C$1, puis C$1, puis $C1, puis on reviendra sur C1 pour boucler.
II-C. Troisième tableau : Des références relatives et deux références mixtes▲
Complexifions un peu notre tableau par l'ajout d'une donnée. Le patron de la boîte étant généreux, il propose d'indexer mensuellement le salaire de 0.5%.
Nous devons bien entendu adapter notre tableau en y ajoutant cette donnée, et adapter notre formule pour qu'elle devienne :
Heures effectuées * Taux horaire * Index mensuel
À nouveau, remanions notre formule en B2 pour tenter de créer une seule formule exploitable sur tout le tableau des salaires.
Puisque nous avons compris le principe, nous comprenons que, cette fois, c'est la ligne qu'il faut geler dans la référence de l'index. De cette manière, lorsque nous recopierons la formule sur B2:D6, la ligne 26 sera bien gelée, comme le montre le tableau suivant.
II-D. Quatrième tableau : Des références relatives, deux références mixtes et une référence absolue▲
Nous arrivons tout doucement à la fin de ce petit exposé. Notre patron étant vraiment généreux, il a décidé de l'octroi d'une prime inconditionnelle de 100 $ par mois, pour chaque employé.
Nous pourrions être tentés d'utiliser la formule suivante
et donc de mettre, « en dur », le montant de la prime.
La modification future des valeurs, chaines de caractères ou dates saisies dans les formules peut vite s'avérer être un véritable casse-tête, ainsi qu'une source d'oublis ou d'erreurs. Il est donc préférable de saisir les valeurs dans des cellules et de faire référence à ces cellules dans les formules d'Excel.
Puisqu'il ne faut pas utiliser de valeur dans une cellule, nous allons utiliser une cellule pour y stocker le montant de la prime. Le bas de notre feuille ressemblera donc à ceci
Nous pouvons alors modifier notre formule en B2 pour qu'elle utilise cette valeur stockée en B28
Comme la prime se trouve dans une cellule unique, il nous faudra geler la ligne ET la colonne lorsque nous ferons référence à cette cellule dans une formule.
Pour geler la ligne (tableau d'index), nous avons mis un $ devant le numéro de la ligne. Pour geler la colonne (tableau des taux horaires), nous avons mis un $ devant la lettre de la colonne.
Pour geler la ligne et la colonne, nous mettrons donc un $ devant la ligne et devant la colonne. La formule utilisée sera donc
Lorsqu'elle est recopiée en B2:D6, la formule est correctement interprétée par Excel, comme le montre l'édition de la formule en D6
II-E. Schéma▲
On peut donc retenir le schéma suivant.
Lorsque l'on va chercher des données dans un tableau de mêmes dimensions que le tableau de résultat, on utilise une référence relative comme B11(flèche bleue).
Lorsque l'on va chercher des données dans un tableau ne contenant qu'une colonne, on verrouille la colonne et on utilise une référence mixte en signalant le verrou sur la colonne par un $ avant la lettre de colonne comme $B19 (flèche verte).
Lorsque l'on va chercher des données dans un tableau ne contenant qu'une ligne, on verrouille la ligne et on utilise une référence mixte en signalant le verrou sur la ligne par un $ avant le numéro de ligne comme B$26 (flèche mauve).
Lorsque l'on va chercher une donnée dans une cellule, donc dans un tableau ne contenant qu'une ligne et qu'une colonne, on verrouille la ligne et la colonne en faisant précéder chaque référence de $, comme $B$28 (rond brun).
Dans certains cas, il est possible (et préférable…) d'utiliser des plages nommées. L'utilisation de plages nommées peut dispenser de l'utilisation des références absolues et relatives. L'acquisition de ces notions ne faisant pas partie de ce cours, je mentionne cette possibilité uniquement pour mémoire.
Comme il n'est toutefois pas possible de toujours utiliser des plages nommées et de se passer des références absolues, mixtes ou relatives, il est utile de bien en comprendre le fonctionnement.
III. Conclusions▲
Nous venons de voir comment utiliser les quatre types de référencement ou d'adressage de cellules dans Excel.
Sans cette possibilité de verrouiller des lignes ou des colonnes, nous n'aurions pas eu d'autre choix que de créer une formule par cellule du tableau de résultat.
Si cela est éventuellement réalisable sur un petit tableau de 5 lignes et 3 colonnes, on comprend aisément que sur des tableaux reprenant des prestations par semaine (52 colonnes) et pour 2000 employés, il n'est pas envisageable de devoir créer 52 x 2000 formules !
L'utilisation des références absolues, mixtes et relatives permet ici de ne créer qu'une seule formule utilisable sur tout le tableau.
J'espère que ce petit tutoriel vous aura permis de progresser. Je suis à votre disposition pour toute remarque ou tout complément d'information. Bonne continuation avec Excel. Bonne continuation sur www.developpez.com.
III-A. Résumé des connaissances acquises▲
- Compréhension du fonctionnement d'Excel en matière de références de cellules.
- Utilisation de références relatives.
- Utilisation de références mixtes.
- Utilisation de références absolues.
- Utilisation de cellules dans des formules en remplacement de valeurs « en dur » (prime unique).
IV. Remerciements▲
Merci à Philippe (Philippe Jochmans) et à Anthony (olsimare) pour la relecture.
Merci à Jean (Jeannot45) pour les conseils d'amélioration