Référence absolue Excel (comment créer et 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 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, quelle que soit l'emplacement où la formule est copiée, 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 une référence absolue avec des exemples
• Calculer le pourcentage du total
• Rechercher une valeur et retourner la valeur correspondante
• Rendre les références de cellules absolues en lot en 2 clics avec Kutools
• Référence relative et référence mixte
• Points à retenir

Télécharger 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.

Contrairement à 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 peu importe 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 comme $A$1.

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

Par exemple, la plage A4:C7 contient les prix des produits, et vous souhaitez obtenir la taxe payable de chaque produit basée sur le taux de taxe dans la cellule B2.

Si vous utilisez une référence relative dans la formule comme "=B5*B2", des résultats incorrects seront retourné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 relativement aux cellules dans 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", cela garantira que le taux de taxe reste le même pour toutes les cellules lorsque la formule est tirée vers le bas en utilisant la poignée de remplissage automatique, les résultats sont corrects.

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

Comment créer des références absolues

Pour créer une référence absolue dans Excel, ajoutez des signes dollar ($) avant les références de colonne et de ligne dans une formule. Il existe deux façons 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 tout en tapant la formule dans une cellule.

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

Sinon, si vous souhaitez modifier les références dans une formule existante pour les rendre absolues, vous pouvez sélectionner la cellule, puis aller dans la barre de formule pour ajouter les signes dollar ($).

Utiliser le raccourci F4 pour convertir une référence relative en 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 basculer entre les types de références jusqu'à ce que les signes dollar soient ajoutés avant les références de colonne et de ligne ;
4. Appuyez sur la touche "Entrée" pour quitter le mode édition et appliquer les modifications.

La touche F4 bascule les références entre les références relatives, absolues et mixtes.

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

Si vous souhaitez rendre toutes les références absolues dans une formule, sélectionnez toute la formule dans la barre de formule, appuyez sur la touche "F4" pour basculer les types de références jusqu'à ce que des 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 ayez 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, maintenant vous souhaitez calculer le pourcentage de chaque vente de fruit 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 des autres fruits, des erreurs #DIV/0! seront retournées.

Étant donné que lorsque vous faites glisser la poignée de remplissage automatique pour copier la formule dans les cellules en dessous, la référence relative B8 est automatiquement ajustée à d'autres références de cellules (B9, B10, B11) en fonction de leurs positions relatives. Et les cellules B9, B10 et B11 sont vides (zéros), lorsque le diviseur est zéro, 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 comme suit :

=B4/$B$8

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

Exemple 2 Rechercher une valeur et retourner la valeur correspondante

Supposons que vous souhaitiez rechercher la liste des noms dans D4:D5 et retourner leurs salaires correspondants en fonction des noms du personnel et des salaires annuels correspondants fournis dans la plage (A4:B8).

La formule générique pour la recherche 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 retourner la valeur correspondante comme ceci :

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

Ensuite, faites glisser la poignée de remplissage automatique vers le bas pour rechercher la valeur ci-dessous, une erreur sera retourné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 vers le bas. En conséquence, la référence à la plage du tableau, A4:B8, devient A5:B9. Comme Lisa ne peut pas être trouvée dans la plage A5:B9, la formule retourne 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)

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

Rendre les références de cellules absolues en lot en 2 clics 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 les références de cellules absolues dans des centaines de formules dans Excel, l'outil "Convert Refers" de "Kutools for Excel" peut vous aider à gérer cette tâche en 2 clics.

Pour rendre les références de cellules absolues dans plusieurs formules, sélectionnez les cellules de formule et cliquez sur "Kutools" > "Plus" > "Convert Refers". Ensuite, choisissez l'option "En Absolue" et cliquez sur "Ok" ou "Appliquer". Maintenant, toutes les références de cellules des formules sélectionnées ont été converties en absolues.

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

Outre la référence absolue, il existe 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, sans signes dollar ($) avant les références de ligne et de colonne. Et lorsque une formule avec des références relatives est copiée ou déplacée vers d'autres cellules, les références changeront 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 changera automatiquement en "=A2+1".

La référence mixte est composée à 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 soit la ligne, soit la colonne lorsque la formule est copiée ou remplie.

Prenons une table de multiplication comme exemple, les lignes et colonnes listent les nombres de 1 à 9, que vous allez multiplier 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, sauf le premier.

Cela est dû au fait que lorsque vous copiez la formule vers la droite, la position de la ligne ne change pas, mais la position de la colonne change de B3 à C3, D3, etc. En conséquence, les formules dans les cellules à droite (D3, E3, etc.) changent en "=C3*D2", "=D3*E2", et ainsi de suite, alors que vous souhaitez en réalité qu'elles 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 de “B3”. Utilisez la formule comme suit :

=$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 change de C2 à C3, C4, etc. En conséquence, les formules dans les cellules ci-dessous changent en "=$B4C3", "=$B5C4", etc., ce qui produira des résultats incorrects.

Pour résoudre ce problème, changez “C2” en “C$2” pour maintenir la référence de ligne constante lorsque vous faites glisser la poignée de remplissage automatique vers le bas pour remplir les formules.

=$B3*C$2

Maintenant, vous pouvez faire glisser la poignée de remplissage automatique vers la droite ou vers le bas pour obtenir tous les 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 lorsque la formule est copiée dans d'autres cellules
  Référence relative	A1	Les références de ligne et de colonne changent en fonction de la position relative lorsque la formule est copiée dans d'autres cellules
  Référence mixte	$A1/A$1	La référence de ligne change lorsque la formule est copiée dans d'autres cellules, mais 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 la référence de ligne est fixe ;

• Généralement, les références absolues ne changent jamais lorsque la 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 depuis le haut ou la 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 changera automatiquement en "=$A$2+1".

  

• La touche "F4" peut basculer entre les références relatives, absolues et mixtes.