Excel 2016 - Leçon 15 : Références de cellules relatives et absolues

• Excel 2016 (Partie 12) : Formater des pages et imprimer des feuilles de calcul
• Excel 2016 (Partie 13) : Introduction aux formules
• Excel 2016 (Partie 14) : Créer des formules complexes

Dans Excel, il existe deux types de références de cellules : relatives et absolues . Les références relatives et absolues se comportent différemment lors de la copie et du remplissage des cellules différemment. Les références relatives changent lorsqu'une formule est copiée dans une autre cellule. Cependant, les références absolues restent inchangées, quel que soit l'endroit où elles sont copiées.

Veuillez rejoindre LuckyTemplates dans cet article avec des instructions sur les références de cellules relatives et absolues dans Excel 2016 !

Références de cellules relatives et absolues dans Excel

• Références relatives
  • Créer et copier une formule qui utilise des références relatives
• Références absolues
  • Créer et copier une formule par référence absolue
• Utiliser des références de cellules avec plusieurs feuilles de calcul
  • Cellules de référence sur des feuilles de calcul

Regardez la vidéo ci-dessous pour en savoir plus sur les références de cellules :

Références relatives

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

Créer et copier une formule qui utilise des références relatives

Dans l'exemple ci-dessous, nous souhaitons créer une formule qui multipliera le prix de chaque article par la quantité. Au lieu de créer une nouvelle formule pour chaque élément, nous pouvons créer une seule formule dans la cellule D2 , puis la copier dans les autres lignes. Nous utiliserons des références relatives pour calculer avec précision le total de chaque élément.

1. Sélectionnez la cellule contenant la formule. Dans l'exemple, nous sélectionnerons la cellule D4 .

2. Entrez la formule pour calculer la valeur souhaitée. Dans l'exemple, nous taperons =B4*C4 .

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

4. Localisez la poignée de remplissage dans le coin inférieur droit de la cellule souhaitée. Dans l’exemple, nous localiserons la poignée de remplissage de la cellule D4.

5. Cliquez et faites glisser la poignée de remplissage vers les cellules que vous souhaitez remplir. Dans l'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 leur formule exacte. Les références relatives pour chaque cellule doivent être différentes selon la ligne.

Références absolues

Parfois, vous ne souhaiterez peut-être pas modifier une référence de cellule lors de la copie vers 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 des lignes et/ou des colonnes consécutives.

Une référence absolue est spécifiée dans une formule en ajoutant un signe dollar ($) . Il peut précéder une référence de colonne, une 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 moins fréquemment 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 les références de cellule absolues, comme le montre la vidéo ci-dessous. C'est un moyen simple d'insérer rapidement une référence absolue.

Créer et copier une formule par référence absolue

Dans l'exemple ci-dessous, nous utiliserons la cellule E2 (qui a un 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 de taxe reste constante - même si la formule est copiée et rempli dans d'autres cellules - nous devons faire de la cellule $E$2 une référence absolue.

1. Sélectionnez la cellule contenant la formule. Dans l'exemple, nous sélectionnerons la cellule D4 .

2. Entrez la formule pour calculer la valeur souhaitée. Dans l'exemple, nous saisirons =(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. Localisez la poignée de remplissage dans le coin inférieur droit de la cellule souhaitée. Dans l’exemple, nous localiserons la poignée de remplissage de 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 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 leur formule exacte. La référence absolue dans chaque cellule doit être la même, 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 effectuez des références absolues 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 des résultats incorrects lors de la copie vers d'autres cellules.

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

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

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

Cellules de référence sur des feuilles de calcul

Dans l'exemple ci-dessous, nous ferons référence à une cellule avec une valeur calculée entre deux tableaux. Cela nous permettra d'utiliser la valeur exacte 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 de calcul. Dans notre exemple, nous souhaitons référencer la cellule E14 sur la feuille de calcul Ordres de menu .

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

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

4. Tapez le signe égal (=) , le nom de la table suivi d' un point d'exclamation (!) et l'adresse de la cellule. Dans l'exemple, nous taperons ='MenuOrder'!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 nom de table incorrect, #REF ! apparaîtra dans la boîte. Dans l'exemple, nous avons confondu le nom de la feuille de calcul. Pour modifier, ignorer ou enquêter sur une erreur, cliquez sur le bouton Erreur à côté de la cellule et choisissez une option dans le menu.

Reportez-vous à d’autres articles :

• Excel 2016 (Partie 3) : Comment créer de nouvelles feuilles de calcul et ouvrir des feuilles de calcul existantes
• Excel 2016 (Partie 4) : Comment stocker et partager des feuilles de calcul
• Excel 2016 (Partie 5) : Notions de base des cellules et des plages

S'amuser!