Cours Excel 14: Références de cellules relatives et absolues

C’est le cours Excel numéro 14 dans la série cours Excel Miscrosoft Office 365.

cours gratuit Excel / cours Excel 2019 /cours Microsoft Office 2019/ cours et tutoriel word 2019 /cours en ligne Excel /tuto en ligne excel /formation en ligne Excel

Les types de références de cellule sont relatives et absolues. Les références relatives et absolues se comportent différemment lorsqu’elles sont copiées et remplies dans d’autres cellules. Quand une formule est copiée dans une autre cellule, les références relatives changent. Les références absolues, en revanche, restent constantes, quel que soit l’endroit d’où elles sont copiées.

Références relatives

Toutes les références de cellule sont par défaut des références relatives. Lorsqu’elles sont copiées dans plusieurs cellules, elles changent en fonction de la position relative des lignes et des colonnes. Par exemple, si vous copiez la formule = A1 + B1 de la ligne 1 à la ligne 2, la formule deviendra = A2 + B2. Les références deviennent utiles quand l’utilisateur du tableur Excel veut répéter le même calcul sur plusieurs lignes ou colonnes.

Pour créer et copier une formule à l’aide de références relatives :

Dans l’exemple suivant, nous voulons créer une formule qui multipliera le prix de chaque article par la quantité. Au lieu de créer une nouvelle formule pour chaque ligne, nous pouvons créer une seule formule dans la cellule D4, puis la copier dans les autres lignes. Nous utiliserons des références relatives afin que la formule calcule correctement le total pour chaque élément.

• Sélectionnez la cellule qui contiendra la formule. Dans notre exemple, nous sélectionnerons la cellule D4.

Entrez la formule pour calculer la valeur souhaitée. Dans notre exemple, nous allons taper = B4 * C4.

Entrez la formule pour calculer la valeur souhaitée. Dans notre exemple, nous allons taper = B4 * C4.

• Appuyez sur Entrée sur votre clavier. La formule sera calculée et le résultat sera affiché dans la cellule.
• Localisez la case de remplissage dans le coin inférieur droit de la cellule souhaitée. Dans notre exemple, nous localiserons la poignée de recopie pour la cellule D4.

Cliquez et faites glisser la poignée de remplissage sur les cellules que vous souhaitez remplir. Dans notre exemple, nous sélectionnerons les cellules D5 : D13.

Relâchez la souris. La formule sera copiée dans les cellules sélectionnées avec leurs références, affichant le résultat dans chaque cellule.

Vous pouvez double-cliquer sur les cellules remplies pour vérifier l’exactitude de leurs formules. Les références de cellule relatives doivent être différentes pour chaque cellule, en fonction de leurs lignes.

Références absolues

Il peut arriver que vous ne vouliez pas qu’une référence de cellule change lorsqu’elle est copiée dans d’autres cellules. Contrairement aux références relatives, les références absolues ne changent pas lorsqu’elles sont copiées ou remplies. Vous pouvez utiliser une référence absolue pour conserver une ligne et/ou une colonne constante.

Une référence absolue est désignée dans une formule en ajoutant un signe dollar ($). Il peut précéder la référence de colonne, la référence de ligne ou les deux.

Vous utiliserez généralement le format $ A $ 2 lors de la création de formules contenant des références absolues. Les deux autres formats sont beaucoup moins utilisés.

Lors de l’écriture d’une formule, vous pouvez appuyer sur la touche F4 de votre clavier pour basculer entre les références de cellules relatives et absolues, comme indiqué dans la vidéo ci-dessous. C’est un moyen facile d’insérer rapidement une référence absolue.

Entrez la formule pour calculer la valeur souhaitée. Dans notre exemple, nous allons taper = (B4 * C4) * $ E $ 2, faisant de $ E $ 2 une référence absolue.

Cliquez et faites glisser la poignée de remplissage sur les cellules que vous souhaitez remplir (cellules D5 : D13 dans notre exemple).

Relâchez la souris. La formule sera copiée dans les cellules sélectionnées avec une référence absolue et les valeurs seront calculées dans chaque cellule.

Vous pouvez double-cliquer sur les cellules remplies pour vérifier l’exactitude de leurs formules. La référence absolue doit être la même pour chaque cellule, tandis que les autres références sont relatives à la ligne de cellule.

Comment utiliser des références de cellule avec plusieurs feuilles de calcul Excel?

Excel nous permet de référencer n’importe quelle cellule sur n’importe quelle feuille de calcul Excel. Ceci s’avère utile si nous souhaitions référencer une valeur spécifique d’une feuille de calcul à une autre. De ce fait, nous devons initier la référence de cellule par le nom de la feuille de calcul suivi d’un point d’exclamation (!) (si la cellule à référencer est A1 sur Sheet1, la référence de cellule serait Sheet1 ! A1.)

Il convient ici de noter que si un nom de feuille de calcul contient un espace, L’utilisateur du tableur Excel doit contourner le nom par des guillemets simples (“”). (si A1 est la cellule à référencer dans une feuille de calcul nommée prévision de juillet, la référence de cellule serait « prévision de juillet » ! A1.

Faire référence à des cellules dans des feuilles de calcul :

Dans notre exemple ci-dessous, nous montrons un cas d’usage concrêt : une cellule avec une valeur calculée entre deux feuilles de calcul.

utiliser exactement la même valeur sur deux feuilles de calcul différentes sans réécrire la formule ni copier les données.

Trouvez la cellule à laquelle vous voulez vous référer

Notez sa feuille de calcul. Ici, nous voulons faire référence à la cellule E14 dans la feuille de calcul Menu Order.

Accédez à la feuille de calcul. Ici, il s’agit d’unefeuille de calcul Facture de restauration.

Tapez le signe égal (=), le nom de la feuille suivi d’un point d’exclamation (!) et l’adresse de la cellule. Dans notre exemple, nous allons taper = ‘Menu Order’ ! E14.

Appuyez sur Entrée sur votre clavier. La valeur de la cellule de référence apparaîtra. Désormais, si la valeur de la cellule E14 change dans la feuille de calcul Commande de menu, elle sera automatiquement mise à jour dans la feuille de calcul Facture de restauration.

Ultérieurement, si l’utilisateur renomme la feuille de calcul, la référence de cellule reflète automatiquement la nouvelle nomination.

le #REF! l’erreur apparaîtra si l’utilisateur entre un nom de feuille de calcul incorrect.