Une feuille de calcul dans Excel est composée de cellules. Ces cellules peuvent être référencées en spécifiant la valeur de ligne et la valeur de colonne.
Par exemple, A1 ferait référence à la première ligne (spécifiée par 1) et à la première colonne (spécifiée par A). De même, B3 serait la troisième ligne et la deuxième colonne.
La puissance d'Excel réside dans le fait que vous pouvez utiliser ces références de cellules dans d'autres cellules lors de la création de formules.
Il existe maintenant trois types de références de cellules que vous pouvez utiliser dans Excel :
- Références de cellules relatives
- Références de cellules absolues
- Références de cellules mixtes
Comprendre ces différents types de références de cellules vous aidera à travailler avec des formules et à gagner du temps (en particulier lors du copier-coller de formules).
Que sont les références de cellules relatives dans Excel ?
Permettez-moi de prendre un exemple simple pour expliquer le concept de références de cellules relatives dans Excel.
Supposons que j'ai un ensemble de données ci-dessous :
Pour calculer le total de chaque article, nous devons multiplier le prix de chaque article par la quantité de cet article.
Pour le premier élément, la formule dans la cellule D2 serait B2* C2 (comme indiqué ci-dessous) :
Maintenant, au lieu d'entrer la formule pour toutes les cellules une par une, vous pouvez simplement copier la cellule D2 et la coller dans toutes les autres cellules (D3:D8). Lorsque vous le faites, vous remarquerez que la référence de cellule s'ajuste automatiquement pour faire référence à la ligne correspondante. Par exemple, la formule dans la cellule D3 devient B3*C3 et la formule dans D4 devient B4*C4.
Ces références de cellule qui s'ajustent lorsque la cellule est copiée sont appelées références de cellules relatives dans Excel.
Quand utiliser des références de cellules relatives dans Excel ?
Les références de cellules relatives sont utiles lorsque vous devez créer une formule pour une plage de cellules et que la formule doit faire référence à une référence de cellule relative.
Dans de tels cas, vous pouvez créer la formule pour une cellule et la copier-coller dans toutes les cellules.
Que sont les références de cellules absolues dans Excel ?
Contrairement aux références de cellules relatives, les références de cellules absolues ne changent pas lorsque vous copiez la formule dans d'autres cellules.
Par exemple, supposons que vous ayez l'ensemble de données comme indiqué ci-dessous où vous devez calculer la commission pour les ventes totales de chaque article.
La commission est de 20% et est répertoriée dans la cellule G1.
Pour obtenir le montant de la commission pour chaque vente d'article, utilisez la formule suivante dans la cellule E2 et copiez pour toutes les cellules :
=D2*$G$1
Notez qu'il y a deux signes dollar ($) dans la référence de cellule qui a la commission - $g$2.
A quoi sert le signe dollar ($) ?
Un symbole dollar, lorsqu'il est ajouté devant le numéro de ligne et de colonne, le rend absolu (c'est-à-dire qu'il empêche le numéro de ligne et de colonne de changer lorsqu'il est copié dans d'autres cellules).
Par exemple, dans le cas ci-dessus, lorsque je copie la formule de la cellule E2 à E3, elle passe de =D2*$G$1 à =D3*$G$1.
Notez que même si D2 devient D3, $G$1 ne change pas.
Étant donné que nous avons ajouté un symbole dollar devant « G » et « 1 » dans G1, cela ne laisserait pas la référence de cellule changer lorsqu'elle est copiée.
Par conséquent, cela rend la référence de cellule absolue.
Quand utiliser des références de cellules absolues dans Excel ?
Les références de cellule absolues sont utiles lorsque vous ne souhaitez pas que la référence de cellule change lorsque vous copiez des formules. Cela peut être le cas lorsque vous avez une valeur fixe que vous devez utiliser dans la formule (comme le taux d'imposition, le taux de commission, le nombre de mois, etc.)
Bien que vous puissiez également coder en dur cette valeur dans la formule (c.
Par exemple, si votre structure de commission change et que vous payez maintenant 25 % au lieu de 20 %, vous pouvez simplement modifier la valeur dans la cellule G2, et toutes les formules seront automatiquement mises à jour.
Que sont les références de cellules mixtes dans Excel ?
Les références de cellules mixtes sont un peu plus délicates que les références de cellules absolues et relatives.
Il peut y avoir deux types de références de cellules mixtes :
- La ligne est verrouillée tandis que la colonne change lorsque la formule est copiée.
- La colonne est verrouillée tandis que la ligne change lorsque la formule est copiée.
Voyons comment cela fonctionne à l'aide d'un exemple.
Vous trouverez ci-dessous un ensemble de données dans lequel vous devez calculer les trois niveaux de commission en fonction de la valeur en pourcentage dans les cellules E2, F2 et G2.
Vous pouvez désormais utiliser la puissance de la référence mixte pour calculer toutes ces commissions avec une seule formule.
Entrez la formule ci-dessous dans la cellule E4 et copiez pour toutes les cellules.
=$B4*$C4*E$2
La formule ci-dessus utilise les deux types de références de cellules mixtes (une où la ligne est verrouillée et une où la colonne est verrouillée).
Analysons chaque référence de cellule et comprenons son fonctionnement :
- $B4 (et $C4) - Dans cette référence, le signe dollar est juste avant la notation de colonne mais pas avant le numéro de ligne. Cela signifie que lorsque vous copiez la formule dans les cellules de droite, la référence restera la même que la colonne est fixe. Par exemple, si vous copiez la formule de E4 vers F4, cette référence ne changera pas. Cependant, lorsque vous le copiez, le numéro de ligne change car il n'est pas verrouillé.
- 2 € - Dans cette référence, le signe dollar est juste avant le numéro de ligne et la notation Colonne n'a pas de signe dollar. Cela signifie que lorsque vous copiez la formule dans les cellules, la référence ne changera pas car le numéro de ligne est verrouillé. Cependant, si vous copiez la formule à droite, l'alphabet de la colonne changera car il n'est pas verrouillé.
Comment changer la référence de relative à absolue (ou mixte) ?
Pour changer la référence de relative à absolue, vous devez ajouter le signe dollar avant la notation de colonne et le numéro de ligne.
Par exemple, A1 est une référence de cellule relative, et elle deviendrait absolue lorsque vous en ferez $A$1.
Si vous n'avez que quelques références à modifier, vous trouverez peut-être facile de modifier ces références manuellement. Vous pouvez donc accéder à la barre de formule et modifier la formule (ou sélectionner la cellule, appuyer sur F2, puis la modifier).
Cependant, un moyen plus rapide de le faire consiste à utiliser le raccourci clavier - F4.
Lorsque vous sélectionnez une référence de cellule (dans la barre de formule ou dans la cellule en mode édition) et appuyez sur F4, la référence change.
Supposons que vous ayez la référence =A1 dans une cellule.
Voici ce qui se passe lorsque vous sélectionnez la référence et appuyez sur la touche F4.
- Appuyez une fois sur la touche F4 : La référence de la cellule passe de A1 à $A$1 (devient « absolue » à partir de « relative »).
- Appuyez deux fois sur la touche F4 : La référence de cellule passe de A1 à A$1 (passe à une référence mixte où la ligne est verrouillée).
- Appuyez trois fois sur la touche F4 : La référence de cellule passe de A1 à $A1 (passe à une référence mixte où la colonne est verrouillée).
- Appuyez quatre fois sur la touche F4 : La référence de cellule redevient A1.
