Lorsque vous faites référence à une cellule dans une formule dans Excel, le type de référence par défaut est une référence relative. Ces références changeront lorsque la formule sera copiée dans d'autres cellules en fonction de leur colonne et ligne relatives. Si vous souhaitez conserver une référence constante, quel que soit l'endroit où la formule est copiée, vous devez utiliser la référence absolue.

• Qu'est-ce qu'une référence absolue
• Comment faire des références absolues
• Utiliser une référence absolue avec des exemples
• Calculer le pourcentage du total
• Rechercher une valeur et revenir à la valeur de correspondance correspondante
• 2 clics pour rendre les références de cellule absolues avec Kutools
• Référence relative et référence mixte
• Choses dont il faut se rappeler

Téléchargez gratuitement le fichier d'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.

Par rapport à une référence relative qui changera en fonction de sa position relative lorsqu'une formule est copiée dans d'autres cellules, une référence absolue restera constante quel que soit l'endroit où la formule est copiée ou déplacée.

Une référence absolue est créée en ajoutant un signe dollar ($) avant les références de colonne et de ligne dans la formule. Par exemple, pour créer une référence absolue pour la cellule A1, vous devez la représenter sous la forme $A$1.

Les références absolues sont utiles lorsque vous souhaitez faire référence à une cellule fixe ou à une plage dans une formule qui sera copiée dans plusieurs cellules, mais que vous ne souhaitez pas que la référence change.

Par exemple, la plage A4:C7 contient les prix des produits et vous souhaitez obtenir la taxe à payer de chaque produit en fonction du taux de taxe de la cellule B2.

Si vous utilisez une référence relative dans la formule comme "=B5*B2", des résultats erronés sont renvoyés lorsque vous faites glisser la poignée de remplissage automatique vers le bas pour appliquer cette formule. Étant donné que la référence à la cellule B2 changera par rapport aux cellules de la formule. Maintenant, la formule dans la cellule C6 est "=B6*B3", et la formule dans la cellule C7 est "=B7*B4"

Mais si vous utilisez une référence absolue à la cellule B2 avec la formule "= B5 * $ B $ 2" garantira que le taux de taxe reste le même pour toutes les cellules lorsque la formule est déplacée vers le bas à l'aide de la poignée de remplissage automatique, les résultats sont corrects.

Utilisation de la référence relative		Utiliser la référence absolue

Comment faire des références absolues

Pour créer une référence absolue dans Excel, vous devez ajouter des signes dollar ($) avant les références de colonne et de ligne dans la formule. Il existe deux manières de créer une référence absolue :

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

Vous pouvez ajouter manuellement des signes dollar ($) avant les références de colonne et de ligne que vous souhaitez rendre absolues lors de la saisie de la formule dans une cellule.

Par exemple, si vous souhaitez ajouter les nombres dans les cellules A1 et B1 et les rendre absolus, tapez simplement la formule "=$A$1+$B$1". Cela garantira que les références de cellule restent constantes lorsque la formule est copiée ou déplacée vers d'autres cellules.

Ou si vous souhaitez modifier les références d'une formule existante dans une cellule en absolue, vous pouvez sélectionner la cellule, puis accéder à la barre de formule pour ajouter les signes dollar ($).

Utilisation du raccourci F4 pour convertir une référence relative en absolue

1. Double-cliquez sur la cellule avec la formule pour entrer en mode édition ;
2. Placez le curseur sur la référence de cellule que vous souhaitez rendre absolue ;
3. Presse F4 touche du clavier pour changer les types de référence jusqu'à ce que les signes dollar soient ajoutés avant les références de colonne et de ligne ;
4. Presse Entrer touche pour quitter le mode d'édition et appliquer les modifications.

La touche F4 peut basculer la référence entre la référence relative, la référence absolue et la référence mixte.

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

Si vous souhaitez que toutes les références soient absolues dans une formule, sélectionnez la formule entière dans la barre de formule, appuyez sur F4 pour basculer entre les types de référence jusqu'à ce que les signes dollar soient ajoutés avant les références de colonne et de ligne.

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

Utiliser une référence absolue avec des exemples

Cette partie fournit 2 exemples pour montrer quand et comment utiliser des références absolues dans une formule Excel.

Exemple 1 Calculer le pourcentage du total

Supposons que vous disposiez d'une plage de données (A3: B7) contenant les ventes de chaque fruit et que la cellule B8 contienne le montant total des ventes de ces fruits. Vous souhaitez maintenant calculer le pourcentage de chaque vente de fruits par rapport au total.

La formule générique pour calculer le pourcentage du total :

Percentage = Sale/Amount

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

=B4/B8

Lorsque vous faites glisser la poignée de remplissage automatique vers le bas pour calculer le pourcentage d'autres fruits, #DIV/0 ! les erreurs seront renvoyées.

Étant donné que lorsque vous faites glisser la poignée de remplissage automatique pour copier la formule dans les cellules ci-dessous, la référence relative B8 est automatiquement ajustée aux autres références de cellule (B9, B10, B11) en fonction de leurs positions relatives. Et la cellule B9, B10 et B11 sont vides (zéros), lorsque le diviseur est nul, la formule retourne à une erreur.

Pour corriger les erreurs, dans ce cas, vous devez rendre la référence de cellule B8 absolue ($B$8) dans la formule pour l'empêcher de changer lorsque vous déplacez ou copiez la formule n'importe où. Maintenant, la formule est mise à jour pour :

=B4/$B$8

Faites ensuite glisser la poignée de remplissage automatique vers le bas pour calculer le pourcentage d'autres fruits.

Exemple 2 Rechercher une valeur et revenir à la valeur de correspondance correspondante

En supposant que vous souhaitiez rechercher la liste des noms dans D4: D5 et renvoyer leurs salaires correspondants en fonction des noms du personnel et du salaire annuel correspondant fournis dans la plage (A4: B8).

La formule générique à rechercher est :

=VLOOKUP(lookup_value, table_range, column_index, logical)

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

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

Faites ensuite glisser la poignée de remplissage automatique vers le bas pour rechercher la valeur ci-dessous, une erreur sera renvoyée.

Lorsque vous faites glisser la poignée de remplissage vers le bas pour copier la formule dans la cellule ci-dessous, les références dans la formule s'ajustent automatiquement d'une ligne. Par conséquent, la référence à la plage du tableau, A4:B8, devient A5:B9. Étant donné que "Lisa : est introuvable dans la plage A5:B9, la formule renvoie une erreur.

Pour éviter les erreurs, utilisez la référence absolue $A$4:$B$8 au lieu de la référence relative A4:B8 dans la formule :

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

Faites ensuite glisser la poignée de remplissage automatique vers le bas pour obtenir le salaire de Lisa.

2 clics pour rendre les références de cellule 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 rendre absolues les références de cellule dans des centaines de formules dans Excel, la Convertir les références outil de Kutools for Excel peut vous aider à gérer le travail en 2 clics.

Sélectionnez les cellules de formule pour lesquelles vous souhaitez rendre les références de cellule absolues, cliquez sur Kutools > Plus (effets) > Convertir les références. Puis choisissez le À l'absolu Option et cliquez sur Ok or Appliquer. Maintenant, toutes les références de cellule des formules sélectionnées ont été converties en absolu.

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

Outre la référence absolue, il existe deux autres types de référence : la référence relative et la référence mixte.

Référence relative est le type de référence par défaut dans Excel, qui est sans signe dollar ($) avant les références de ligne et de colonne. Et lorsqu'une formule avec des références relatives est copiée ou déplacée vers d'autres cellules, les références changent automatiquement en fonction de leur position relative.

Par exemple, lorsque vous tapez une formule dans une cellule telle que "=A1+1", puis faites glisser la poignée de remplissage automatique vers le bas pour remplir cette formule dans la cellule suivante, la formule passera automatiquement à "=A2+1".

Référence mixte est composé à la fois d'une référence absolue et d'une référence relative. En d'autres termes, la référence mixte utilise le signe dollar ($) pour fixer la ligne ou la colonne lorsqu'une formule est copiée ou remplie.

Prenez une table de multiplication comme exemple, les lignes et les colonnes listent les nombres de 1 à 9, que vous multiplierez entre eux.

Pour commencer, vous pouvez utiliser la formule "=B3*C2" dans la cellule C3 pour multiplier 1 dans la cellule B3 par le nombre (1) dans la première colonne. Cependant, lorsque vous faites glisser la poignée de remplissage automatique vers la droite pour remplir les autres cellules, vous remarquerez que tous les résultats sont incorrects, à l'exception du premier.

En effet, lorsque vous copiez la formule vers la droite, la position de la ligne ne change pas, mais la position de la colonne passe de B3 à C3, D3, etc. Par conséquent, les formules dans les cellules de droite (D3, E3, etc.) changez pour "=C3*D2", "=D3*E2", et ainsi de suite, quand vous voulez réellement qu'ils soient "=B3*D2", "=B3*E2", et ainsi de suite.

Dans ce cas, vous devez ajouter un signe dollar ($) pour verrouiller la référence de colonne "B3". Utilisez la formule ci-dessous :

=$B3*C2

Maintenant, lorsque vous faites glisser la formule vers la droite, les résultats sont corrects.

Ensuite, vous devez multiplier le nombre 1 dans la cellule C2 par les nombres dans les lignes ci-dessous.

Lorsque vous copiez la formule vers le bas, la position de la colonne de la cellule C2 ne change pas, mais la position de la ligne passe de C2 à C3, C4, etc. Par conséquent, les formules dans les cellules ci-dessous deviennent "=$B4C3", "=$B5C4", etc. qui produira des résultats incorrects.

Pour résoudre ce problème, remplacez "C2" par "C$2" pour empêcher la référence de ligne de changer lorsque vous faites glisser la poignée de remplissage automatique vers le bas pour remplir les formules.

=$B3*C$2

Vous pouvez maintenant faire glisser la poignée de remplissage automatique vers la droite ou vers le bas pour obtenir tous les résultats.

Choses dont il faut se rappeler

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

  Type	Exemple	Résumé
  Référence absolue	1 $ AU	Ne jamais changer lorsque la formule est copiée dans d'autres cellules
  Référence relative	A1	La référence de ligne et de colonne change en fonction de la position relative lorsque la formule est copiée dans d'autres cellules
  Référence mixte	1 $A/1 $A	La référence de ligne change lorsque la formule est copiée dans d'autres cellules mais que la référence de colonne est fixe/La référence de colonne change lorsque la formule est copiée dans d'autres cellules mais que la référence de ligne est fixe ;

• Généralement, les références absolues ne changent jamais lorsqu'une formule est déplacée. Cependant, les références absolues s'ajusteront automatiquement lorsqu'une ligne ou une colonne est ajoutée ou supprimée en haut ou à gauche dans la feuille de calcul. Par exemple, dans une formule "=$A$1+1", lorsque vous insérez une ligne en haut de la feuille, la formule passera automatiquement à "=$A$2+1".

  

• Les F4 La touche peut basculer entre référence relative, référence absolue et référence mixte.