Lorsque vous travaillez avec des formules Excel, vous pouvez parfois voir l’invite d’avertissement suivante.
Cette invite vous indique qu’il y a une référence circulaire dans votre feuille de calcul et cela peut conduire ou un calcul incorrect par les formules. Elle vous demande également d’aborder ce problème de référence circulaire et de le trier.
Dans ce tutoriel, je vais couvrir tout ce que vous devez savoir sur la référence circulaire, ainsi que la façon de trouver et de supprimer les références circulaires dans Excel.
Alors, commençons !
Ce tutoriel couvre :
Qu’est-ce qu’une référence circulaire dans Excel ?
En termes simples, une référence circulaire se produit lorsque vous finissez par avoir une formule dans une cellule – qui elle-même utilise la cellule (dans laquelle elle a été saisie) pour le calcul.
Laissez-moi essayer d’expliquer cela par un exemple simple.
Supposons que vous avez le jeu de données dans la cellule A1:A5 et que vous utilisez la formule ci-dessous dans la cellule A6:
=SUM(A1:A6)
Ceci vous donnera un avertissement de référence circulaire.
C’est parce que vous voulez additionner les valeurs dans la cellule A1:A6, et le résultat devrait être dans la cellule A6.
Cela crée une boucle car Excel ne cesse d’ajouter la nouvelle valeur dans la cellule A6, qui ne cesse de changer (d’où une boucle de référence circulaire).
Comment trouver les références circulaires dans Excel ?
Bien que l’invite d’avertissement de référence circulaire soit assez aimable pour vous dire qu’elle existe dans votre feuille de calcul, elle ne vous dit pas où elle se produit et quelles références de cellule en sont la cause.
Donc, si vous essayez de trouver et de gérer les références circulaires dans la feuille de calcul, vous devez connaître un moyen de les trouver en quelque sorte.
Voici les étapes à suivre pour trouver une référence circulaire dans Excel :
- Activer la feuille de calcul qui a la référence circulaire
- Cliquer sur l’onglet Formules
- Dans le groupe Édition de formule, cliquez sur l’icône déroulante de contrôle d’erreur (petite flèche pointant vers le bas à droite)
- Passer le curseur sur l’option Références circulaires. Cela vous montrera la cellule qui a une référence circulaire dans la feuille de calcul
- Cliquez sur l’adresse de la cellule (qui est affichée) et cela vous mènera à cette cellule dans la feuille de calcul.
Une fois que vous avez réglé le problème, vous pouvez à nouveau suivre les mêmes étapes ci-dessus et cela montrera plus de références de cellules qui ont la référence circulaire. S’il n’y en a pas, vous ne verrez aucune référence de cellule,
Un autre moyen rapide et facile de trouver la référence circulaire est de regarder la barre d’état. Dans la partie gauche de celle-ci, elle vous montrera le texte Référence circulaire ainsi que l’adresse de la cellule.
Il y a quelques choses que vous devez savoir lorsque vous travaillez avec des références circulaires :
- Dans le cas où le calcul itératif est activé (abordé plus loin dans ce tutoriel), la barre d’état n’affichera pas l’adresse de la cellule de référence circulaire
- Dans le cas où la référence circulaire n’est pas dans la feuille active (mais dans d’autres feuilles du même classeur), elle n’affichera que Référence circulaire et non l’adresse de la cellule
- Dans le cas où vous obtenez une fois une invite d’avertissement de référence circulaire et que vous l’écartez, elle n’affichera pas à nouveau l’invite la fois suivante.
- Si vous ouvrez un classeur qui a la référence circulaire, il vous montrera l’invite dès l’ouverture du classeur.
Comment supprimer une référence circulaire dans Excel ?
Une fois que vous avez identifié qu’il y a des références circulaires dans votre feuille, il est temps de les supprimer (sauf si vous voulez qu’elles soient là pour une raison).
Malheureusement, ce n’est pas aussi simple que d’appuyer sur la touche de suppression. Puisque ceux-ci dépendent des formules et que chaque formule est différente, vous devez analyser cela au cas par cas.
Dans le cas où il s’agit juste d’avoir la référence de la cellule qui cause le problème par erreur, vous pouvez simplement corriger en ajustant la référence.
Mais parfois, ce n’est pas aussi simple.
La référence circulaire peut également être causée sur la base de plusieurs cellules qui se nourrissent les unes des autres à plusieurs niveaux.
Laissez-moi vous montrer un exemple.
Ci-après, il y a une référence circulaire dans la cellule C6, mais ce n’est pas un simple cas d’autoréférence. C’est un cas à plusieurs niveaux où les cellules qu’il utilise dans les calculs se réfèrent également les unes aux autres.
- Les formules dans la cellule A6 est =SUM(A1:A5)+C6
- La formule est cellule C1 est =A6*0.1
- La formule de la cellule C6 est =A6+C1
Dans l’exemple ci-dessus, le résultat de la cellule C6 dépend des valeurs de la cellule A6 et C1, qui à leur tour dépendent de la cellule C6 (ce qui provoque l’erreur de référence circulaire)
Et encore une fois, j’ai choisi un exemple vraiment simple juste à des fins de démonstration. En réalité, ceux-ci pourraient être assez difficiles à déterminer et peut-être éloignés dans la même feuille de calcul ou même dispersés sur plusieurs feuilles de calcul.
Dans ce cas, il existe une façon d’identifier les cellules qui causent la référence circulaire et de les traiter ensuite.
C’est en utilisant l’option Tracer les précédents.
Voici les étapes pour utiliser l’option Tracer les précédents afin de trouver les cellules qui alimentent la cellule qui a la référence circulaire :
- Sélectionnez la cellule qui a la référence circulaire
- Cliquez sur l’onglet Formules
- Cliquez sur Tracer les précédents
Les étapes ci-dessus vous montreraient des flèches bleues qui vous indiqueront quelles cellules alimentent la formule dans la cellule sélectionnée. De cette façon, vous pouvez inspecter les formules et les cellules et vous débarrasser de la référence circulaire.
Dans le cas où vous travaillez avec des modèles financiers complexes, il est possible que ces précédents aillent également à plusieurs niveaux de profondeur.
Cela fonctionne bien si toutes les formules se réfèrent à des cellules dans la même feuille de calcul. Si c’est dans plusieurs feuilles de calcul, cette méthode n’est pas efficace.
Comment activer/désactiver les calculs itératifs dans Excel
Lorsque vous avez une référence circulaire dans une cellule, vous obtenez d’abord la demande d’avertissement comme indiqué ci-dessous, et si vous fermez cette boîte de dialogue, elle vous donnera 0 comme résultat dans la cellule.
C’est parce que lorsqu’il y a une référence circulaire, c’est une boucle sans fin et Excel ne veut pas être pris dedans. Il renvoie donc un 0.
Mais dans certains cas, vous pouvez effectivement vouloir que la référence circulaire soit active et fasse quelques itérations. Dans un tel cas, au lieu d’une boucle infinie, et vous pouvez décider combien de fois la boucle doit être exécutée.
C’est ce qu’on appelle le calcul itératif dans Excel.
Voici les étapes pour activer et configurer les calculs itératifs dans Excel :
- Cliquez sur l’onglet Fichier
- Cliquez sur Options. Cela ouvrira la boîte de dialogue Options Excel
- Sélectionnez Formule dans le volet de gauche
- Dans la section Options de calcul, cochez la case ‘Activer le calcul itératif’. Ici, vous pouvez spécifier les itérations maximales et la valeur de changement maximale
C’est tout ! Les étapes ci-dessus permettraient d’activer le calcul itératif dans Excel.
Laissez-moi également expliquer rapidement les deux options du calcul itératif :
- Itérations maximales : Il s’agit du nombre maximum de fois que vous voulez qu’Excel calcule avant de vous donner le résultat final. Ainsi, si vous l’indiquez comme étant 100, Excel exécutera la boucle 100 fois avant de vous donner le résultat final.
- Changement maximal : Il s’agit du changement maximal, qui, s’il n’est pas atteint entre les itérations, le calcul serait arrêté. Par défaut, la valeur est de 0,001. Plus cette valeur est faible, plus le résultat serait précis.
Rappelez-vous que plus le nombre d’itérations est élevé, plus Excel a besoin de temps et de ressources pour le faire. Dans le cas où vous gardez le maximum d’itérations élevé, cela peut conduire à un ralentissement ou à un plantage de votre Excel.
Utilisation délibérée de références circulaires
Dans la plupart des cas, la présence d’une référence circulaire dans votre feuille de calcul serait une erreur. Et c’est pourquoi Excel vous montre une invite qui dit – « Essayez de supprimer ou de modifier ces références ou de déplacer les formules dans des cellules différentes. »
Mais il pourrait y avoir certains cas spécifiques où vous avez besoin d’une référence circulaire afin que vous puissiez obtenir le résultat souhaité.
Un de ces cas spécifiques sur lequel j’ai déjà écrit est l’obtention de l’horodatage dans une cellule dans une cellule dans Excel.
Par exemple, supposons que vous vouliez créer une formule pour que chaque fois qu’une entrée est faite dans une cellule de la colonne A, l’horodatage apparaisse dans la colonne B (comme indiqué ci-dessous):
Alors que vous pouvez insérer facilement un horodatage en utilisant la formule ci-dessous :
=IF(A2<>"",IF(B2<>"",B2,NOW()),"")
Le problème avec la formule ci-dessus est qu’elle mettrait à jour tous les horodatages dès qu’un changement est effectué dans la feuille de calcul ou si la feuille de calcul est rouverte (car la formule NOW est volatile)
Pour contourner ce problème, vous pouvez utiliser une méthode de référence circulaire. Utilisez la même formule, mais activez le calcul itératif.
Il y a aussi d’autres cas où avoir la possibilité d’utiliser la référence circulaire est souhaité (vous pouvez trouver un exemple ici).
J’espère que vous avez trouvé ce tutoriel utile !
Autres tutoriels Excel que vous pourriez trouver utiles :
.