Afin d'effectuer des calculs précis dans Excel, il est essentiel de comprendre le fonctionnement des différents types de références de cellules.
Références A1 vs R1C1
Les feuilles de calcul Excel contiennent de nombreuses cellules et (par défaut) chaque cellule est identifiée par son lettre de colonne suivi de son numéro de ligne. C'est ce qu'on appelle le référencement de style A1. Exemples : A1, B4, C6
Style de référence A1
En option, vous pouvez passer à Mode de référence R1C1 pour se référer à la ligne d'une cellule & numéro de colonne. Au lieu de faire référence à la cellule A1, vous feriez référence à R1C1 (ligne 1, colonne 1). La cellule C4 serait appelée R4C3.
Style de référence R1C1
Le référencement de style R1C1 est extrêmement rare dans Excel. À moins que vous n'ayez une bonne raison, vous devriez probablement vous en tenir au mode de référence par défaut de style A1. Cependant, si vous utilisez VBA, vous rencontrerez probablement ce style de référence.
Passer au style de référence R1C1
Pour changer de style de référence, accédez à Fichier > Option > Formule. Cochez la case à côté de Style de référence R1C1.
Plages nommées
L'une des fonctionnalités les plus sous-utilisées d'Excel est la fonctionnalité Plages nommées. Au lieu de faire référence à une cellule (ou à un groupe de cellules) par son emplacement de cellule (ex B3 ou R3C2), vous pouvez nommer cette plage et simplement référencer le nom de la plage dans vos formules.
Pour nommer une plage :
- Sélectionnez la ou les cellules que vous souhaitez nommer
- Cliquez à l'intérieur de la zone de nom de plage
- Entrez votre nom désiré
- Appuyez sur Entrée
Vous pouvez maintenant référencer la cellule A1 en tapant = range_name1 à la place. Ceci est très utile lorsque vous travaillez avec des classeurs volumineux avec plusieurs feuilles de calcul.
Gamme de cellules
Lorsque vous utilisez les fonctions intégrées d'Excel, vous devrez peut-être référencer des plages de cellules. Les plages de cellules apparaissent comme ceci « A1 : B3 ». Cette référence fait référence à toutes les cellules entre A1 et B3 : les cellules A1, A2, A3, B1, B2, B3.
Pour sélectionner une plage de cellules lors de la saisie d'une formule :
- Saisissez la plage (séparez la plage de début et de fin par un point-virgule)
- Utilisez votre souris pour cliquer sur la première référence de cellule, maintenez le bouton de la souris enfoncé et faites glisser la plage souhaitée.
- Maintenez la touche Maj enfoncée et naviguez avec les touches fléchées pour sélectionner votre plage
Références absolues (gelées) et relatives
Lorsque vous saisissez des références de cellule dans des formules, vous pouvez utiliser des références relatives ou absolues (gelées). Les références relatives se déplacent proportionnellement lorsque vous copiez la formule dans une nouvelle cellule. Les références absolues resteront inchangées. Regardons quelques exemples :
Référence relative
Une référence relative dans Excel ressemble à ceci
=A1
Lorsque vous copiez et collez une formule avec des références relatives, les références relatives se déplacent proportionnellement. Sauf indication contraire de votre part, vos références de cellules seront relatives (dégelées) par défaut.
Exemple : si vous copiez « =A1 » sur une ligne, la référence devient « =A2 ».
Références de cellules absolues (gelées)
Si vous ne souhaitez pas que vos références de cellules se déplacent lorsque vous copiez une formule, vous pouvez « geler » vos références de cellules en ajoutant des signes dollar ($s) devant la référence que vous souhaitez geler. Désormais, lorsque vous copiez et collez la formule, la référence de la cellule reste inchangée. Vous pouvez choisir de geler la référence de ligne, la référence de colonne ou les deux.
A1 : Rien n'est gelé
$A1 : la colonne est figée, mais la ligne n'est pas figée
A$1 : la ligne est figée, mais la colonne n'est pas figée
$A$1 : la ligne et la colonne sont gelées
Raccourci de référence absolue
L'ajout manuel de signes dollar ($s) dans vos formules n'est pas très pratique. Au lieu de cela, lors de la création de votre formule, utilisez la touche F4 pour basculer entre les références de cellules absolues/relatives.
Exemple de référence de cellule absolue
Quand auriez-vous réellement besoin de geler une référence de cellule ? Un exemple courant est lorsque vous avez des cellules d'entrée qui sont fréquemment référencées. Dans l'exemple ci-dessous, nous voulons calculer la taxe de vente pour chaque quantité d'éléments de menu. La taxe de vente est constante pour tous les articles, nous ferons donc référence à la cellule de taxe de vente à plusieurs reprises.
Pour trouver la taxe de vente totale, entrez la formule « =(B3*C3)*$C$1 » dans la colonne D et copiez la formule vers le bas.
Référence mixte
Vous avez peut-être entendu parler de références de cellules mixtes. Une référence mixte se produit lorsque la référence de ligne ou de colonne est verrouillée (mais pas les deux).
Référence mixte
N'oubliez pas qu'en utilisant la touche "F4", vous pouvez faire défiler vos références de cellules relatives et absolues.
Références de cellule - Insertion et suppression de lignes/colonnes
Vous vous demandez peut-être ce qu'il advient de vos références de cellule lorsque vous insérez ou supprimez des lignes/colonnes ?
La référence de cellule sera mise à jour automatiquement pour faire référence à la cellule d'origine. C'est le cas, que la référence de cellule soit ou non gelée.
Références 3D
Parfois, vous devrez peut-être travailler avec plusieurs feuilles de calcul avec des modèles de données identiques. Excel vous permet de faire référence à plusieurs feuilles à la fois sans avoir à saisir manuellement chaque feuille de calcul. Vous pouvez référencer une plage de feuilles de la même manière que vous feriez référence à une plage de cellules. L'exemple "Feuille1:Feuille5!A1" référencerait les cellules A1 sur toutes les feuilles de Sheet1 à Sheet5.
Parcourons un exemple :
Vous souhaitez additionner le nombre total d'unités vendues pour chaque produit dans tous les magasins. Chaque magasin a sa propre feuille de calcul et toutes les feuilles de calcul ont un format identique. Vous pouvez créer une formule similaire à celle-ci :
Ce n'est pas trop difficile avec seulement quatre feuilles de calcul, mais si vous aviez 40 feuilles de calcul ? Voudriez-vous vraiment ajouter manuellement chaque référence de cellule ?
Au lieu de cela, vous pouvez utiliser une référence 3D pour référencer plusieurs feuilles à la fois avec facilité (de la même manière que vous pouvez référencer une plage de cellules).
Fais attention! L'ordre de vos feuilles de calcul est important. Si vous déplacez une autre feuille entre les feuilles référencées (StoreA et StoreD), cette feuille sera incluse. Inversement, si vous déplacez une feuille en dehors de la plage de feuilles (avant StoreA ou après StoreD), elle ne sera plus incluse.
Référence de cellule circulaire
Une référence de cellule circulaire se produit lorsqu'une cellule se réfère à elle-même. Par exemple, si le résultat de la cellule B1 est utilisé comme entrée pour la cellule B1, une référence circulaire est créée. La cellule n'a pas besoin de se référer directement à elle-même. Il peut y avoir des étapes intermédiaires.
Exemple:
Dans ce cas, la formule de la cellule B2 est « A2+A3+B2 ». Parce que vous êtes dans la cellule B2, vous ne pouvez pas utiliser B2 dans l'équation. Cela déclencherait une référence circulaire et la valeur de la cellule « B2 » sera automatiquement définie sur « 0 ».
Les références circulaires sont généralement le résultat d'une erreur de l'utilisateur, mais dans certaines circonstances, vous souhaiterez peut-être utiliser une référence circulaire. Le principal exemple d'utilisation d'une référence circulaire consiste à calculer des valeurs de manière itérative. Pour ce faire, vous devez vous rendre sur Fichier > Options > Formules et Calcul itératif activé :
Références externes
Parfois, lors du calcul des données, vous devrez peut-être vous référer à des données en dehors de votre classeur. C'est ce qu'on appelle une référence externe (lien).
Pour sélectionner une référence externe lors de la création d'une formule, accédez au classeur externe et sélectionnez votre référence comme vous le feriez normalement.
Pour accéder à l'autre classeur, vous pouvez utiliser le raccourci CTRL + TAB ou accéder à Affichage > Changer de fenêtre.
Une fois que vous avez sélectionné la cellule, vous verrez qu'une référence externe qui ressemble à ceci :
Notez que le nom du classeur est entouré de crochets [].
Une fois que vous fermez le classeur référencé, la référence affichera l'emplacement du fichier :
Lorsque vous rouvrirez le classeur contenant le lien externe, vous serez invité à activer la mise à jour automatique des liens. Si vous le faites, Excel ouvrira la valeur de référence avec la valeur actuelle du classeur. Même si c'est fermé ! Fais attention! Vous pouvez ou non le vouloir.
Plages nommées et références externes
Qu'arrive-t-il à votre référence de cellule externe lorsque des lignes/colonnes sont ajoutées ou supprimées du classeur de référence ? Si les deux classeurs sont ouverts, les références de cellule sont mises à jour automatiquement. Cependant, si les deux classeurs ne sont pas ouverts, les références de cellule ne seront pas mises à jour et ne seront plus valides. C'est une préoccupation majeure lors de la liaison à des classeurs externes. De nombreuses erreurs sont causées par cela.
Si vous créez un lien vers des classeurs externes, vous devez nommer la référence de cellule avec une plage nommée (voir la section précédente pour plus d'informations). Désormais, votre formule fera référence à la plage nommée, quelles que soient les modifications apportées au classeur externe.
