Tutoriel Excel : référence absolue et relative

Lors de l'écriture d'une formule Excel, le signe $ dans les références de cellule s’impose à de nombreux utilisateurs. Pourtant, son emploi est très important. Le signe dollar dans une référence de cellule Excel ne sert qu'à un seul but : il indique à Excel s'il faut modifier ou non la référence lorsque la formule est copiée dans d'autres cellules. Ce tutoriel fournit des détails complets sur cette fonctionnalité inédite.

Une feuille de calcul dans Excel est composée de cellules. Ainsi, ces dernières peuvent être référencées en spécifiant la valeur de ligne et la valeur de colonne. La puissance d'Excel réside dans le fait que vous pouvez utiliser ces références de cellule dans d'autres cellules lors de la création de formules.

Il existe trois types de références de cellule que vous pouvez utiliser dans Excel :

• Références relatives aux cellules.
• Références de cellules absolues.
• Références de cellules mixtes.

La compréhension de ces différents types de références de cellule vous aidera à travailler avec des formules et à gagner du temps (en particulier lors du copier-coller de formules).

Table des matières

Qu'est-ce qu'une référence de cellule Excel ?

Qu’est-ce qu’une référence de cellules relative dans Excel ?

Quand utiliser les références de cellules relatives dans Excel ?

Référence de cellule absolue (avec le signe $)

Quand utiliser des références de cellule absolues dans Excel ?

Quelles sont les références de cellules mixtes dans Excel ?

Comment référencer une colonne ou une ligne entière dans Excel ?

Une référence sur toute la colonne

Une référence sur toute la ligne

Utilisation de références de cellule avec plusieurs feuilles de calcul

Qu'est-ce qu'une référence de cellule Excel ?

Une référence ou une adresse de cellule est une combinaison d'une lettre de colonne et d'un numéro de ligne qui identifie une cellule dans une feuille de calcul.

Par exemple :

• A1 fait référence à la cellule à l'intersection de la colonne A et de la ligne 1;
• B2 fait référence à la deuxième cellule de la colonne B ;
• Et ainsi de suite.

Lorsqu'elles sont utilisées dans une formule, les références de cellule aident Excel à trouver les valeurs que la formule doit calculer.

Par exemple, pour extraire la valeur de A1 dans une autre cellule, vous utilisez cette formule simple =A1 :

Pour additionner les valeurs dans les cellules A1 et A2, vous utilisez cette formule =A1 + A2 :

Qu’est-ce qu’une référence de cellules relative dans Excel ?

Par défaut, toutes les références de cellule sont des références relatives. Lorsqu'elles sont copiées sur plusieurs cellules, elles changent en fonction de la position relative des lignes et des colonnes. Par exemple, si vous copiez la formule =A1 + B1 de la ligne 1 à la ligne 2, la formule deviendra =A2 + B2. Les références relatives sont particulièrement pratiques lorsque vous devez répéter le même calcul sur plusieurs lignes ou colonnes.

Prenons un petit exemple simple pour expliquer le concept de références de cellule relatives dans Excel.

Supposons que j'ai l’ensemble de données illustré ci-dessous :

Pour calculer le total de chaque produit, nous devons multiplier le prix unitaire de chaque produit par la quantité correspondante.

Pour le premier élément, la formule dans la cellule D2 serait =B2 * C2 (comme indiqué ci-dessous) :

Maintenant, au lieu de saisir la formule pour toutes les cellules une par une, vous pouvez simplement copier la cellule D2 et la coller dans toutes les autres cellules (D3: D9). Lorsque vous le faites, vous remarquerez que la référence de cellule s'ajuste automatiquement pour se référer à la ligne correspondante. Par exemple, la formule dans la cellule D3 devient =B3 * C3 et la formule dans D4 devient =B4 * C4.

Ces références de cellule qui s'ajustent lors de la copie de la cellule sont appelées références de cellule relatives dans Excel.

Quand utiliser les références de cellules relatives dans Excel ?

Les références de cellule relatives sont utiles lorsque vous devez créer une formule pour une plage de cellules et que la formule doit faire référence à une référence de cellule relative. Dans de tels cas, vous pouvez créer la formule pour une cellule et la copier-coller dans toutes les autres cellules.

Référence de cellule absolue (avec le signe $)

Il peut arriver que vous ne souhaitiez pas qu'une référence de cellule change lors du remplissage des cellules. Contrairement aux références relatives, les références absolues ne changent pas lorsqu'elles sont copiées ou remplies. Vous pouvez utiliser une référence absolue pour garder une ligne et / ou une colonne constante.

Une référence absolue est désignée dans une formule par l'ajout d'un signe dollar ($) avant la colonne et la ligne. Si celui-ci précède la colonne ou la ligne (mais pas les deux), il s'agit d'une référence mixte.

Le signe dollar fixe la référence à une cellule donnée, de sorte qu'elle reste inchangée, peu importe où la formule se déplace. En d'autres termes, l'utilisation de $ dans les références de cellule vous permet de copier la formule dans Excel sans modifier les références.

Par exemple, si vous avez la valeur 10 dans la cellule A1 et que vous utilisez une référence de cellule absolue ($A$1), la formule =$A$1 + 5 renvoie toujours 15, quelles que soient les autres cellules dans lesquelles la formule est copiée.

En revanche, si vous écrivez la même formule avec une référence de cellule relative (A1), puis la copiez vers les autres cellules de la colonne, une valeur différente sera calculée pour chaque ligne. L'image suivante montre la différence :

Remarque : Bien que nous ayons dit qu'une référence absolue dans Excel ne change jamais, en fait, elle change lorsque vous ajoutez ou supprimez des lignes et / ou des colonnes dans votre feuille de calcul, et cela modifie l'emplacement de la cellule référencée. Dans l'exemple ci-dessus, si nous insérons une nouvelle ligne en haut de la feuille de calcul, Excel est suffisamment intelligent pour ajuster la formule afin de refléter ce changement :

Quand utiliser des références de cellule absolues dans Excel ?

Les références de cellule absolues sont utiles lorsque vous ne souhaitez pas que la référence de cellule change lorsque vous copiez des formules. Cela pourrait être le cas lorsque vous avez une valeur fixe que vous devez utiliser dans la formule (comme le taux de taxe, le taux de commission, le nombre de mois, etc.)

Dans les vraies feuilles de calcul, il est très rare que vous n'utilisiez que des références absolues dans votre formule Excel. Cependant, de nombreuses tâches nécessitent l'utilisation de références absolues et relatives, comme expliqué dans la section suivante.

Quelles sont les références de cellules mixtes dans Excel ?

Les références de cellules mixtes sont un peu plus délicates que les références de cellules absolues et relatives.

Il peut y avoir deux types de références de cellules mixtes :

• La ligne est verrouillée tandis que la colonne change lorsque la formule est copiée.
• La colonne est verrouillée tandis que la ligne change lorsque la formule est copiée.

Voyons comment cela fonctionne à l'aide d'un exemple.

Vous trouverez ci-dessous un ensemble de données dans lequel vous devez calculer les trois niveaux de commission en fonction du pourcentage dans les cellules E2, F2 et G2.

Vous pouvez maintenant utiliser la puissance de la référence mixte pour calculer toutes ces commissions avec une seule formule.

Entrez la formule ci-dessous dans la cellule E3 et appliquez la sur toutes les cellules comme illustré dans la capture ci-dessous :

=$B3 * $C3 * E$2

La formule ci-dessus utilise les deux types de références de cellules mixtes (une où la ligne est verrouillée et une où la colonne est verrouillée).

Analysons chaque référence de cellule et comprenons comment elle fonctionne :

• $B3 (et $C3) - Dans cette référence, le signe dollar est juste avant la notation Colonne mais pas avant le numéro de Ligne. Cela signifie que lorsque vous copiez la formule dans les cellules de droite, la référence reste la même puisque la colonne est fixe. Par exemple, si vous copiez la formule de la cellule E3 vers F3, cette référence ne changera pas. Cependant, lorsque vous la copiez, le numéro de ligne change car il n'est pas verrouillé.
• E$2 - Dans cette référence, le signe dollar est juste avant le numéro de ligne et la notation Colonne n'a pas de signe dollar. Cela signifie que lorsque vous copiez la formule dans les cellules, la référence ne change pas car le numéro de ligne est verrouillé. Cependant, si vous copiez la formule vers la droite, l'alphabet des colonnes changera car il n'est pas verrouillé.

Remarque : Lorsque vous écrivez une formule dans Microsoft Excel, vous pouvez appuyer sur la touche F4 de votre clavier pour basculer entre les références de cellule relatives, absolues et mixtes. C'est un moyen simple d'insérer rapidement une référence absolue. Supposons que vous ayez la référence =A1 dans une cellule. Voici ce qui se passe lorsque vous sélectionnez la référence et appuyez sur la touche F4.

• Appuyer une fois sur la touche F4: la référence de cellule passe de A1 à $A$1 (elle change de «relative» à «absolue»).
• Appuyer deux fois sur la touche F4: la référence de cellule passe de A1 à A$1 (change en référence mixte où la ligne est verrouillée).
• Appuyer trois fois sur la touche F4: la référence de cellule passe de A1 à $A1 (change en référence mixte où la colonne est verrouillée).
• Appuyer quatre fois sur la touche F4: la référence de cellule redevient A1.

Comment référencer une colonne ou une ligne entière dans Excel ?

Lorsque vous travaillez avec une feuille de calcul Excel qui a un nombre variable de lignes, vous souhaiterez peut-être faire référence à toutes les cellules d'une colonne spécifique. Pour référencer toute la colonne, tapez simplement une lettre de colonne deux fois avec deux points au milieu, par exemple A:A.

Une référence sur toute la colonne

En plus des références de cellule, une référence de colonne entière peut être absolue et relative, par exemple :

• Référence de colonne absolue, comme $A:$A.
• Référence de colonne relative, comme A:A.

Et encore une fois, vous utilisez le signe dollar ($) dans une référence de colonne absolue pour la verrouiller sur une certaine colonne, afin que la référence de colonne entière ne change pas lorsque vous copiez une formule dans d'autres cellules.

Une référence de colonne relative change lorsque la formule est copiée ou déplacée vers d'autres colonnes et reste intacte lorsque vous copiez la formule dans d'autres cellules de la même colonne.

Supposons que vous ayez des chiffres dans la colonne B et que vous souhaitiez connaître leur total et leur moyenne. Le problème est que de nouvelles lignes sont ajoutées au tableau chaque semaine, donc écrire une formule SOMME ou MOYENNE habituelle pour une plage fixe de cellules n'est pas la solution. Au lieu de cela, vous pouvez référencer l'intégralité de la colonne B:

=SOMME($B:$B) - utilisez le signe dollar ($) pour créer une référence de colonne entière absolue qui verrouille la formule sur la colonne B.

=SOMME(B:B) - écrivez la formule sans $ pour créer une référence relative à toute la colonne qui sera modifiée lorsque vous copiez la formule dans d'autres colonnes.

De la même manière, vous pouvez écrire une formule pour calculer le prix moyen dans toute la colonne B:

=MOYENNE(B:B)

Dans cet exemple, j’ai utilisé une référence de colonne entière relative, donc la formule est ajustée correctement lorsque je l’ai copié dans d'autres colonnes:

Une référence sur toute la ligne

Pour faire référence à la ligne entière, vous utilisez la même approche, sauf que vous tapez des numéros de ligne au lieu de lettres de colonne :

• Référence de ligne absolue, comme $1:$1
• Référence de ligne relative, comme 1:1

Si les données de votre feuille Excel sont organisées en lignes plutôt qu'en colonnes, vous pouvez référencer une ligne entière dans votre formule. Par exemple, voici comment vous pouvez calculer un prix moyen à la ligne 2 :

=MOYENNE($2:$2) - une référence de ligne entière absolue est verrouillée sur une ligne spécifique à l'aide du signe dollar ($).

=MOYENNE(2:2) - une référence relative à une ligne entière change lorsque la formule est copiée dans d'autres lignes.

Dans cet exemple, j’ai besoin d'une référence relative sur toute la ligne car j’ai 2 lignes de données et je veux calculer une moyenne dans chaque ligne en copiant la même formule :

Utilisation de références de cellule avec plusieurs feuilles de calcul

Excel vous permet de faire référence à n'importe quelle cellule de n'importe quelle feuille de calcul, ce qui peut être particulièrement utile si vous souhaitez référencer une valeur spécifique d'une feuille de calcul à une autre. Pour ce faire, il vous suffit de commencer la référence de cellule par le nom de la feuille de calcul suivi d'un point d'exclamation (!). Par exemple, si vous souhaitez référencer la cellule A1 sur la feuille « Feuil1 », sa référence de cellule serait Feuil1! A1.

Notez que si un nom de feuille de calcul contient un espace, vous devrez inclure des guillemets simples ('') autour du nom. Par exemple, si vous souhaitez référencer la cellule A1 dans une feuille de calcul nommée « Budget de juin », sa référence de cellule est ‘Budget de juin’! A1.

Pour référencer des cellules dans des feuilles de calcul :

Dans l’exemple ci-dessous, nous ferons référence à une cellule avec une valeur calculée entre deux feuilles de calcul. Cela permettra d'utiliser la même valeur exacte sur deux feuilles de calcul différentes sans réécrire la formule ni copier les données.

1. Recherchez la cellule que vous souhaitez référencer et notez sa feuille de calcul. Dans cet exemple, je veux référencer la cellule D10dans la feuille de calcul « Ventes Mai » :

2. Accédez à la feuille de calcul souhaitée. Dans cet exemple, je sélectionnerai la feuille de calcul « Total des ventes » :

3. Recherchez et sélectionnez la cellule dans laquelle vous souhaitez que la valeur apparaisse. Dans mon exemple, je vais sélectionner la cellule B3 :

4. Tapez le signe égal (=), le nom de la feuille suivi d'un point d'exclamation (!) Et l'adresse de la cellule. Dans mon exemple, je vais taper ='Ventes Mai'!D10 :

5. Appuyez sur « Entrée» de votre clavier.

La valeur de la cellule référencée apparaîtra. Désormais, si la valeur de la cellule D10 change dans la feuille de calcul « Ventes Mai », elle sera mise à jour automatiquement dans la feuille de calcul « Total des ventes ».

Si vous renommez votre feuille de calcul ultérieurement, la référence de cellule sera mise à jour automatiquement pour refléter le nouveau nom de la feuille de calcul.

Si vous entrez un nom de feuille de calcul de manière incorrecte, l'erreur « #REF! » apparaîtra dans la cellule. Dans l’exemple ci-dessous, j’ai fait exprès de saisir incorrectement le nom de la feuille de calcul. Pour modifier, ignorer ou rechercher l'erreur, cliquez sur le bouton « Erreur » à côté de la cellule et choisissez une option dans le menu.

Télécharger le fichier excel de ce tutoriel