Passer au contenu principal

Référence absolue Excel (comment faire et utiliser)

Auteur : Soleil Dernière modification: 2023-06-08

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.

Téléchargez gratuitement le fichier d'exemple exemple de doc


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
doc référence absolue 3 1   doc référence absolue 4 1

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

référence absolue f4 bascule 1

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

référence absolue f4 bascule 2


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.

Notes:

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.

doc référence absolue 15 1

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.

doc référence absolue 16 1

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.

doc référence absolue 17 1

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.

doc référence absolue 18 1

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

doc référence absolue 19 1

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

doc référence absolue 20 1


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".

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

Meilleurs outils de productivité bureautique

🤖 Aide à l'IA Kutools: Révolutionner l'analyse des données en s'appuyant sur : Exécution intelligente   |  Générer du code  |  Créer des formules personnalisées  |  Analyser les données et générer des graphiques  |  Invoquer les fonctions Kutools...
Caractéristiques populaires: Rechercher, mettre en évidence ou identifier les doublons   |  Supprimer les lignes vides   |  Combinez des colonnes ou des cellules sans perdre de données   |   Tour sans formule 
Super recherche: VSearchup à critères multiples    VSearch à valeurs multiples  |   Recherche virtuelle sur plusieurs feuilles   |   Recherche floue ....
Liste déroulante avancée: Créez rapidement une liste déroulante   |  Liste déroulante dépendante   |  Liste déroulante à sélection multiple ....
Gestionnaire de colonnes: Ajouter un nombre spécifique de colonnes  |  Déplacer les colonnes  |  Basculer l'état de visibilité des colonnes masquées  |  Comparer les plages et les colonnes 
Caractéristiques en vedette: Mise au point de la grille   |  Voir Design   |   Grande barre de formule    Gestionnaire de classeurs et de feuilles   |  Ressources (Texte automatique)   |  Sélecteur de date   |  Combiner des feuilles de travail   |  Crypter/déchiffrer les cellules    Envoyer des e-mails par liste   |  Super filtre   |   Filtre spécial (filtre gras/italique/barré...) ...
Les 15 meilleurs ensembles d'outils12 Texte Outils (Ajouter du texte, Supprimer les caractères, ...)   |   50+ Graphique Types (Diagramme de Gantt, ...)   |   40+ Pratique Formules (Calculer l'âge en fonction de l'anniversaire, ...)   |   19 Insertion Outils (Insérer le code QR, Insérer une image à partir du chemin, ...)   |   12 Conversion Outils (Nombres en mots, Conversion des devises, ...)   |   7 Fusionner et fractionner Outils (Lignes de combinaison avancées, Cellules divisés, ...)   |   ... et plus

Améliorez vos compétences Excel avec Kutools for Excel et faites l'expérience d'une efficacité comme jamais auparavant. Kutools for Excel offre plus de 300 fonctionnalités avancées pour augmenter la productivité et gagner du temps.  Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...

Description


Office Tab apporte une interface à onglets à Office et facilite grandement votre travail

  • Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit des centaines de clics de souris chaque jour!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations