Excel 2019 (Partie 14) : Références de cellules relatives et absolues

Dans Excel , il existe deux types de références de cellule : 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. Les références relatives changent lorsqu'une formule est copiée dans une autre cellule. En revanche, les références absolues restent inchangées, quel que soit l'endroit où elles sont copiées.

Référence relative

Par défaut, toutes les références de cellules sont des références relatives. Lorsqu'elles sont copiées sur plusieurs cellules, elles changeront 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 devient =A2+B2. Les références relatives sont particulièrement pratiques lorsque vous devez répéter le même calcul sur plusieurs lignes ou colonnes.

Comment créer et copier des formules à l'aide de références relatives

L'exemple suivant souhaite 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, vous pouvez créer une seule formule dans la cellule D4 , puis la copier dans les autres lignes. L'exemple utilisera des références relatives afin que la formule calcule le total correct pour chaque élément.

1. Sélectionnez la cellule qui contiendra la formule. Par exemple, nous sélectionnerons la cellule D4.

2. Entrez la formule pour calculer la valeur souhaitée. Par exemple, saisissez =B4*C4.

3. Appuyez sur Entrée sur le clavier. La formule sera calculée et les résultats affichés dans la cellule.

4. Recherchez la poignée de remplissage dans le coin inférieur droit de la cellule souhaitée. L'exemple déterminera la position de la poignée de remplissage pour la cellule D4.

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

6. Relâchez la souris. La formule sera copiée dans les cellules sélectionnées avec des références relatives, affichant les résultats dans chaque cellule.

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

Référence absolue

Il peut arriver que vous ne souhaitiez 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 spécifiée dans une formule en ajoutant un signe dollar ( $ ). Ce symbole peut précéder une référence de colonne, une référence de ligne, ou les deux.

En général, vous utiliserez le format $A$2 lors de la création de formules contenant des références absolues. Les deux formats restants sont beaucoup moins utilisés.

Lorsque vous écrivez une formule, vous pouvez appuyer sur la touche F4 de votre clavier pour basculer entre les références de cellule relatives et absolues, comme le montre la vidéo ci-dessous. C'est un moyen simple d'insérer rapidement une référence absolue.

Comment créer et copier des formules à l'aide de références absolues

L'exemple ci-dessous utilisera la cellule E2 (qui contient le taux de taxe de 7,5 %) pour calculer la taxe de vente pour chaque article de la colonne D. Pour garantir que la référence au taux d'imposition reste la même - même si la formule est copiée et remplie dans d'autres cellules - la cellule $E$2 doit être définie comme référence absolue.

1. Sélectionnez la cellule qui contiendra la formule. Par exemple, nous sélectionnerons la cellule D4.

2. Entrez la formule pour calculer la valeur souhaitée. L'exemple entrerait =(B4*C4)*$E$2 , faisant de $E$2 une référence absolue.

3. Appuyez sur Entrée sur le clavier. La formule calculera et le résultat s'affichera dans la cellule.

4. Recherchez la poignée de remplissage dans le coin inférieur droit de la cellule souhaitée. L'exemple déterminera la position de la poignée de remplissage pour la cellule D4.

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

6. Relâchez la souris. La formule sera copiée dans les cellules sélectionnées avec des références absolues et les valeurs seront calculées dans chaque cellule.

Vous pouvez double-cliquer sur les cellules remplies pour vérifier l'exactitude des formules. Les références absolues doivent être les mêmes pour chaque cellule, tandis que les autres références sont relatives à la ligne de la cellule.

Assurez-vous d'inclure le signe dollar ( $ ) chaque fois que vous faites une référence absolue dans plusieurs cellules. Les signes dollar ont été omis dans l'exemple ci-dessous. Cela amène Excel à l'interpréter comme une référence relative, produisant un résultat incorrect lors de la copie vers d'autres cellules.

Utiliser des références de cellules avec plusieurs feuilles de calcul

Excel vous permet de référencer n'importe quelle cellule de n'importe quelle feuille de calcul, ce qui peut être particulièrement utile si vous souhaitez référencer une valeur spécifique d'une feuille de calcul à une autre. Pour ce faire, commencez simplement la référence de votre cellule par le nom de la feuille, suivi d'un point d'exclamation ( ! ). Par exemple, si vous souhaitez référencer la cellule A1 sur Sheet1 , sa référence de cellule sera Sheet1!A1.

Notez que si le nom de la feuille contient des espaces, vous devrez inclure des guillemets ( '' ) autour du nom. Par exemple, si vous souhaitez référencer la cellule A1 sur une feuille de calcul nommée Budget de juillet , sa référence de cellule serait « Budget de juillet » !A1.

Comment référencer des cellules sur des feuilles de calcul

L'exemple ci-dessous fera référence à une cellule avec une valeur calculée entre deux feuilles de calcul. Cela permet d'utiliser exactement la même valeur sur deux feuilles de calcul différentes sans réécrire les formules ni dupliquer les données.

1. Localisez la cellule que vous souhaitez référencer et notez sa feuille. Par exemple, nous souhaitons référencer la cellule E14 sur la feuille de calcul Ordre des menus .

2. Accédez à la feuille de calcul souhaitée. Par exemple, nous choisirons le tableur Facture de restauration .

3. Localisez et sélectionnez la cellule dans laquelle vous souhaitez que la valeur apparaisse. Par exemple, nous sélectionnerons la cellule C4.

4. Entrez le signe égal ( = ), le nom de la feuille de calcul suivi d'un point d'exclamation (!) et l'adresse de la cellule. Par exemple, saisissez ='Ordre du menu'!E14.

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

Si vous renommez votre feuille de calcul ultérieurement, la référence de cellule sera automatiquement mise à jour pour refléter le nouveau nom de la feuille de calcul.

Si vous entrez un mauvais nom de feuille, le #REF! une erreur apparaîtra dans la cellule. Dans l'exemple ci-dessous, le nom de la feuille a été mal orthographié. Pour modifier, ignorer ou rechercher des erreurs, cliquez sur le bouton Erreur à côté de la cellule et choisissez une option dans le menu.