Ce qu'il faut savoir
- Pour calculer plusieurs éléments basés sur des cellules ailleurs et conserver la ligne ou la colonne constante, utilisez une référence de cellule absolue.
- Dans cette équation, la référence de cellule absolue est A$12:=+B2+B2+A$12.
- Le symbole $ "maintient" la ligne ou la colonne constante même lors de la copie ou du remplissage d'une colonne ou d'une ligne avec la même formule.
Cet article explique comment utiliser une référence de cellule absolue dans Excel 2010 et versions ultérieures pour empêcher les formules de s'ajuster automatiquement en fonction du nombre de lignes ou de colonnes que vous avez remplies.
Qu'est-ce qu'une référence de cellule absolue dans Excel ?
Il existe deux types de façons de référencer une cellule dans une formule dans Excel. Vous pouvez utiliser une référence de cellule relative ou une référence de cellule absolue.
- Référence de cellule relative: Une adresse de cellule qui ne contient pas le symbole $ devant les coordonnées de la ligne ou de la colonne. Cette référence mettra automatiquement à jour la colonne ou la cellule par rapport à la cellule d'origine lorsque vous remplissez vers le bas ou vers le bas.
- Référence de cellule absolue: une adresse de cellule qui contient le symbole $ devant les coordonnées de la ligne ou de la colonne. Cela "maintient" la ligne ou la colonne de référence constante même lors du remplissage d'une colonne ou d'une ligne avec la même formule.
Le référencement cellulaire peut sembler être un concept abstrait pour les novices.
Comment utiliser la référence de cellule absolue dans Excel
Il existe plusieurs façons d'utiliser une référence absolue dans Excel. La méthode que vous utilisez dépend de la partie de la référence que vous souhaitez conserver constante: la colonne ou la ligne.
-
Par exemple, prenons une feuille de calcul dans laquelle vous vouliez calculer la taxe de vente réelle pour plusieurs articles, en fonction de cellules de référence ailleurs dans la feuille.
Image -
Dans la première cellule, vous entrez la formule en utilisant des références de cellules relatives standard pour les prix des articles. Mais pour intégrer la taxe de vente correcte dans le calcul, vous utiliseriez une référence absolue pour la ligne, mais pas pour la cellule.
Image -
Cette formule fait référence à deux cellules de différentes manières. B2 est la cellule à gauche de la première cellule de taxe de vente, par rapport à la position de cette cellule. La référence A$12 pointe vers le numéro de taxe de vente réel indiqué dans A12. Le symbole $ gardera la référence à la ligne 12 constante, quelle que soit la direction dans laquelle vous remplissez les cellules adjacentes. Remplissez toutes les cellules sous cette première pour voir cela en action.
Image -
Vous pouvez voir que lorsque vous remplissez la colonne, la cellule de la deuxième colonne utilise un référencement relatif des cellules pour les valeurs de coût dans la colonne B, en incrémentant la colonne pour correspondre à la colonne de taxe de vente. Cependant, la référence à A12 reste constante en raison du symbole $, qui conserve la même référence de ligne. Remplissez maintenant la colonne D à partir de la cellule C2.
Image -
Maintenant, vous pouvez voir que le remplissage de la ligne à droite utilise la bonne taxe de vente de l'État (Indiana) car la référence de colonne est relative (elle se décale vers la droite, tout comme la cellule que vous remplissez). Cependant, la référence relative pour le coût s'est également déplacée vers la droite, ce qui est incorrect. Vous pouvez résoudre ce problème en faisant en sorte que la formule d'origine dans C2 référence la colonne B avec une référence absolue à la place.
Image -
En plaçant un symbole $ devant "B", vous avez créé une référence absolue pour la colonne. Maintenant, lorsque vous remplissez à droite, vous verrez que la référence à la colonne B pour le coût reste la même.
Image -
Maintenant, lorsque vous remplissez la colonne D, vous verrez que toutes les références de cellules relatives et absolues fonctionnent exactement comme vous le souhaitiez.
Image -
Les références de cellules absolues dans Excel sont essentielles pour contrôler quelles cellules sont référencées lorsque vous remplissez des colonnes ou des lignes. Utilisez le symbole $ mais vous en avez besoin dans la référence de cellule pour conserver la référence de ligne ou de colonne constante, afin de référencer les données correctes dans la cellule correcte. Lorsque vous combinez des références de cellule relatives et absolues comme celle-ci, cela s'appelle une référence de cellule mixte.
Si vous utilisez une référence mixte, il est important d'aligner la colonne ou la ligne des données source avec la colonne ou la ligne où vous saisissez la formule. Si vous avez rendu la référence de ligne relative, gardez à l'esprit que lorsque vous remplissez sur le côté, le numéro de colonne des données source s'incrémentera avec la colonne de la cellule de formule. C'est l'une des choses qui compliquent l'adressage relatif et absolu pour les nouveaux utilisateurs, mais une fois que vous l'aurez appris, vous aurez beaucoup plus de contrôle sur l'extraction des cellules de données source dans vos références de cellule de formule.
Utilisation de références de cellules absolues pour épingler une référence de cellule
Une autre approche pour utiliser le référencement absolu des cellules consiste à l'appliquer à la fois à la colonne et à la ligne pour "épingler" essentiellement la formule afin d'utiliser une seule cellule, peu importe où elle se trouve.
En utilisant cette approche, vous pouvez remplir sur le côté ou vers le bas et la référence de cellule restera toujours la même.
L'utilisation d'une référence de cellule absolue sur la colonne et la ligne n'est efficace que si vous ne faites référence qu'à une seule cellule dans toutes les cellules que vous remplissez.
-
En utilisant le même exemple de feuille de calcul ci-dessus, vous pouvez référencer uniquement le taux d'imposition d'un seul État en ajoutant le symbole $ à la colonne et à la ligne de référence.
Image -
Cela fait que la colonne "A" et la ligne "12" restent constantes, quelle que soit la direction dans laquelle vous remplissez les cellules. Pour voir cela en action, remplissez toute la colonne pour la taxe de vente ME après avoir mis à jour la formule avec la référence absolue de la colonne et de la ligne. Vous pouvez voir que chaque cellule remplie utilise toujours la référence absolue $A$12. Ni la colonne ni la ligne ne changent.
Image -
Utilisez la même formule pour la colonne de la taxe de vente de l'Indiana, mais cette fois, utilisez la référence absolue pour la colonne et la ligne. Dans ce cas, c'est $B$12.
Image -
Remplissez cette colonne, et encore une fois, vous verrez que la référence à B12 ne change pas dans chaque cellule, grâce à la référence absolue pour la colonne et la ligne.
Image -
Comme vous pouvez le voir, il existe plusieurs façons d'utiliser des références de cellules absolues dans Excel pour accomplir des tâches similaires. Ce que les références absolues vous offrent, c'est la flexibilité de conserver des références de cellule constantes même lorsque vous remplissez un grand nombre de colonnes ou de lignes.
Vous pouvez faire défiler les références de cellule relatives ou absolues en mettant en surbrillance la référence, puis en appuyant sur F4 Chaque fois que vous appuyez sur F4, la référence absolue sera appliquée soit à la colonne, soit au rangée, à la fois la colonne et la cellule, ou aucune des deux. C'est un moyen simple de modifier votre formule sans avoir à taper le symbole $.
Quand utiliser des références de cellules absolues
Dans presque tous les secteurs et domaines, il existe de nombreuses raisons pour lesquelles vous voudrez peut-être utiliser des références de cellules absolues dans Excel.
- Utiliser des multiplicateurs fixes (comme le prix unitaire) dans une longue liste d'articles.
- Appliquez un pourcentage unique pour chaque année lorsque vous projetez des objectifs de bénéfices annuels.
- Lors de la création de factures, utilisez des références absolues pour faire référence au même taux de taxe pour tous les articles.
- Utilisez des références de cellules absolues dans la gestion de projet pour faire référence à des taux de disponibilité fixes pour des ressources individuelles.
- Utilisez des références de colonne relatives et des références de ligne absolues pour faire correspondre les calculs de colonne dans vos cellules référencées aux valeurs de colonne d'un autre tableau.
Si vous utilisez une combinaison de références relatives et absolues pour les colonnes ou les lignes, vous devez simplement vous assurer que la position des colonnes ou des lignes de données source correspond à la colonne ou à la ligne des cellules de destination (où vous retaper la formule).
