Référence absolue dans Excel (comment la créer et l’utiliser)

Lorsque vous faites référence à une cellule dans une formule Excel, le type de référence par défaut est une référence relative. Ces références changent lorsque la formule est copiée vers d’autres cellules, en fonction de leur colonne et ligne relatives. Si vous souhaitez conserver une référence constante, quelle que soit la destination de la copie de la formule, vous devez utiliser une référence absolue.

• Qu’est-ce qu’une référence absolue
• Comment créer des références absolues
• Utiliser la référence absolue avec des exemples
• Calculer le pourcentage du total
• Rechercher une valeur et renvoyer la valeur correspondante
• 2 clics pour transformer en lot les références de cellules en absolues avec Kutools
• Référence relative et référence mixte
• Points à retenir

Téléchargez gratuitement le fichier exemple

Vidéo : Référence absolue

Qu’est-ce qu’une référence absolue

Une référence absolue est un type de référence de cellule dans Excel.

Contrairement à une référence relative, qui s’ajuste automatiquement en fonction de sa position lorsqu’une formule est copiée vers d’autres cellules, une référence absolue reste inchangée, quelle que soit la destination de la copie ou du déplacement de la formule.

Une référence absolue s’obtient en ajoutant un signe dollar ($) devant la colonne et la ligne dans la formule. Ainsi, pour créer une référence absolue à la cellule A1, il suffit de l’écrire sous la forme $A$1.

Les références absolues sont idéales lorsque vous souhaitez faire référence à une cellule ou une plage fixe dans une formule que vous comptez copier dans plusieurs cellules, sans que cette référence ne change.

Par exemple, la plage A4:C7 contient les prix des produits, et vous souhaitez calculer la taxe applicable à chacun d’eux en fonction du taux figurant dans la cellule B2.

Si vous utilisez une référence relative dans la formule, comme « =B5*B2 », des résultats incorrects apparaissent lorsque vous faites glisser la poignée de recopie vers le bas pour l’appliquer. En effet, la référence à la cellule B2 évolue relativement à la position des cellules contenant la formule : ainsi, en C6, la formule devient « =B6*B3 », et en C7, elle devient « =B7*B4 ».

En revanche, si vous utilisez une référence absolue vers la cellule B2 avec la formule « =B5*$B$2 », le taux de taxe reste identique pour toutes les cellules lorsque vous étirez la formule vers le bas à l’aide de la poignée de recopie, garantissant ainsi des résultats exacts.

Utilisation d’une référence relative		Utilisation d’une référence absolue

Comment créer des références absolues

Pour créer une référence absolue dans Excel, ajoutez des symboles dollar ($) devant les références de colonne et de ligne dans une formule. Deux méthodes s’offrent à vous pour créer une référence absolue :

Ajouter manuellement des signes dollar à la référence de cellule

Vous pouvez ajouter manuellement des symboles dollar ($) devant les références de colonne et de ligne que vous souhaitez verrouiller en tant que références absolues, directement lors de la saisie de votre formule dans une cellule.

Par exemple, si vous souhaitez additionner les nombres situés dans les cellules A1 et B1 en rendant les deux références absolues, saisissez simplement la formule sous la forme « =$A$1+$B$1 ». Cela garantit que les références de cellule restent inchangées lorsque la formule est copiée ou déplacée vers d’autres cellules.

Sinon, pour transformer les références d’une formule existante en références absolues, sélectionnez la cellule, puis ajoutez les symboles dollar ($) directement dans la barre de formule.

Utiliser le raccourci F4 pour convertir une référence relative en référence absolue

1. Double-cliquez sur la cellule contenant la formule pour entrer en mode édition ;
2. Placez le curseur sur la référence de cellule que vous souhaitez rendre absolue ;
3. Appuyez sur la touche « F4 » du clavier pour faire défiler les types de référence jusqu’à ce que des signes dollar apparaissent devant la colonne et la ligne ;
4. Appuyez sur la touche « Entrée » pour quitter le mode édition et enregistrer vos modifications.

La touche F4 permet de faire défiler les différents types de références : relative, absolue et mixte.

A1 → $A$1 → A$1 → $A1 → A1

Si vous souhaitez rendre toutes les références absolues dans une formule, sélectionnez l’intégralité de la formule dans la Barre de formule, appuyez sur la touche « F4 » pour faire défiler les types de références jusqu’à ce que des signes dollar soient ajoutés devant les références de colonne et de ligne.

A1+B1 → $A$1++$B$1 → A$1++B$1 → $A1+$B1 → A1+B1

Utiliser la référence absolue avec des exemples

Cette section présente deux exemples concrets montrant quand et comment utiliser les références absolues dans une formule Excel.

Exemple 1 : Calculer le pourcentage d’un total

Supposons que vous disposiez d’une plage de données (A3:B7) indiquant les ventes de chaque fruit, et que la cellule B8 contienne le montant total des ventes. Vous souhaitez désormais calculer le pourcentage que représentent les ventes de chaque fruit par rapport à ce total.

Formule générique pour calculer le pourcentage d’un total :

Percentage = Sale/Amount

Utilisez une référence relative dans la formule pour obtenir le pourcentage du premier fruit, comme suit :

=B4/B8

Lorsque vous faites glisser la poignée de recopie vers le bas pour calculer le pourcentage des autres fruits, des erreurs #DIV/0! s’affichent.

En effet, lorsque vous étirez la poignée de recopie pour appliquer la formule aux cellules situées en dessous, la référence relative B8 s’ajuste automatiquement en fonction de la position relative de chaque cellule (devenant B9, B10, B11, etc.). Or, les cellules B9, B10 et B11 sont vides — ce qui équivaut à zéro — et comme le diviseur devient nul, la formule génère une erreur.

Pour corriger ces erreurs, vous devez transformer la référence de la cellule B8 en référence absolue ($B$8) dans la formule, afin qu’elle reste inchangée lors du déplacement ou de la copie de celle-ci. La formule est alors mise à jour comme suit :

=B4/$B$8

Ensuite, faites glisser la poignée de recopie vers le bas pour calculer automatiquement le pourcentage des autres fruits.

Exemple 2 : Rechercher une valeur et renvoyer la valeur correspondante

Supposons que vous souhaitiez rechercher les noms figurant dans la plage D4:D5 et obtenir leurs salaires correspondants à partir de la liste du personnel et des salaires annuels fournis dans la plage A4:B8.

Formule générique pour effectuer une recherche :

=VLOOKUP(lookup_value, table_range, column_index, logical)

Si vous utilisez une référence relative dans la formule pour rechercher une valeur et renvoyer la valeur correspondante, comme suit :

=VLOOKUP(D4,A4:B8,2,FALSE)

Puis, lorsque vous faites glisser la poignée de recopie vers le bas pour rechercher la valeur suivante, une erreur s’affiche.

Lorsque vous étirez la poignée de recopie vers le bas pour copier la formule dans la cellule située en dessous, les références s’ajustent automatiquement d’une ligne : la plage A4:B8 devient ainsi A5:B9. Or, Lisa ne figurant pas dans cette nouvelle plage, la formule renvoie une erreur.

Pour éviter ces erreurs, utilisez la référence absolue $A$4:$B$8 à la place de la référence relative A4:B8 dans la formule :

=VLOOKUP(D4,$A$4:$B$8,2,FALSE)

Ensuite, faites glisser la poignée de recopie vers le bas pour obtenir le salaire de Lisa.

2 clics pour transformer en lot les références de cellule en références absolues avec Kutools

Que vous choisissiez de taper manuellement ou d’utiliser le raccourci F4, vous ne pouvez modifier qu’une seule formule à la fois dans Excel. Si vous souhaitez transformer en références absolues les références de cellule dans des centaines de formules, l’outil « Convertir les références de cellules » de « Kutools pour Excel » vous permet d’accomplir cette tâche en 2 clics.

Pour transformer les références de cellule en références absolues dans plusieurs formules, sélectionnez les cellules contenant ces formules, puis cliquez sur « Kutools » > « Plus » > « Convertir les références de cellules ». Choisissez l’option « En absolu », puis cliquez sur « OK » ou « Appliquer ». Toutes les références de cellule des formules sélectionnées sont désormais converties en références absolues.

Référence relative et référence mixte

Outre la référence absolue, on distingue deux autres types de références : la référence relative et la référence mixte.

La référence relative est le type de référence par défaut dans Excel : elle ne comporte aucun signe dollar ($) devant les références de ligne et de colonne. Lorsqu’une formule contenant des références relatives est copiée ou déplacée vers d’autres cellules, ces références s’ajustent automatiquement en fonction de leur position relative.

Par exemple, lorsque vous saisissez une formule telle que « =A1+1 » dans une cellule, puis que vous faites glisser la poignée de recopie vers le bas pour remplir la cellule suivante, la formule s’adapte automatiquement en « =A2+1 ».

Référence mixte combine à la fois une référence absolue et une référence relative. Autrement dit, elle utilise le signe dollar ($) pour figer soit la ligne, soit la colonne lorsqu’une formule est copiée ou recopiée.

Prenons l’exemple d’une table de multiplication dont les lignes et les colonnes contiennent les chiffres de 1 à 9, que vous allez multiplier entre eux.

Pour commencer, saisissez la formule « =B3*C2 » dans la cellule C3 afin de multiplier la valeur située en B3 par le nombre (1) de la première colonne. Toutefois, en faisant glisser la poignée de recopie vers la droite pour remplir les autres cellules, vous constatez que tous les résultats sont incorrects, à l’exception du premier.

Cela s’explique par le fait que, lors de la copie de la formule vers la droite, la ligne reste inchangée tandis que la colonne évolue : B3 devient C3, puis D3, etc. Ainsi, les formules dans les cellules situées à droite (D3, E3, etc.) prennent la forme « =C3*D2 », « =D3*E2 », etc., alors que vous souhaitez obtenir « =B3*D2 », « =B3*E2 », etc.

Dans ce cas, vous devez ajouter un signe dollar ($) pour verrouiller la référence de colonne « B3 ». Utilisez la formule suivante :

=$B3*C2

Désormais, lorsque vous étirez la formule vers la droite, les résultats s’affichent correctement.

Ensuite, multipliez le chiffre 1 situé en C2 par les chiffres des lignes situées en dessous.

Lorsque vous copiez la formule vers le bas, la colonne de la cellule C2 reste inchangée, mais la ligne s’ajuste automatiquement : C2 devient C3, puis C4, etc. Ainsi, les formules dans les cellules situées en dessous se transforment en « =$B4*C3 », « =$B5*C4 », etc., générant des résultats incorrects.

Pour résoudre ce problème, remplacez « C2 » par « C$2 » afin de verrouiller la référence de ligne lorsque vous faites glisser la poignée de recopie vers le bas pour étendre les formules.

=$B3*C$2

Vous pouvez désormais faire glisser la poignée de recopie vers la droite ou vers le bas pour obtenir l’ensemble des résultats.

Points à retenir

• Résumé des références de cellules

  Type	Exemple	Résumé
  Référence absolue	$A$1	Ne change jamais lorsqu’une formule est copiée dans d’autres cellules
  Référence relative	A1	La référence de ligne et de colonne change toutes deux en fonction de la position relative lorsqu’une formule est copiée dans d’autres cellules
  Référence mixte	$A1/A$1	La référence de ligne change lorsqu’une formule est copiée dans d’autres cellules, mais la référence de colonne reste fixe / La référence de colonne change lorsqu’une formule est copiée dans d’autres cellules, mais la référence de ligne reste fixe ;

• En général, les références absolues ne changent jamais lorsqu’une formule est déplacée. Toutefois, elles s’ajustent automatiquement lorsqu’une ligne ou une colonne est insérée ou supprimée au-dessus ou à gauche de la cellule concernée dans la feuille de calcul. Par exemple, si vous insérez une ligne en haut de la feuille, la formule « =$A$1+1 » devient automatiquement « =$A$2+1 ».

  

• La touche « F4 » permet de faire basculer une référence entre les modes relatif, absolu et mixte.