Trouver et corriger une référence circulaire dans Excel
Une référence circulaire se produit lorsqu'une formule renvoie à sa propre cellule, directement ou par l'intermédiaire d'autres cellules. Excel ne peut pas la calculer : il affiche un avertissement et renvoie en général 0. Pour la corriger, ouvrez Formules > Vérification des erreurs > Références circulaires, allez à la cellule indiquée et modifiez la formule pour qu'elle ne dépende plus d'elle-même.
Ce guide montre comment trouver la boucle, les cinq situations qui en provoquent la plupart et comment corriger chacune, ainsi que le cas rare où une boucle est voulue. Entraînez-vous aux compétences sous-jacentes dans les leçons liées à la fin.
1. Qu'est-ce qu'une référence circulaire
Une formule comme =A1+B1 saisie dans A1 demande à Excel une valeur qui a besoin de A1 en premier, et A1 a besoin de la formule. Il n'y a pas de point de départ, donc Excel s'arrête. Une boucle peut aussi être indirecte : A1 utilise B1, B1 utilise C1, et C1 utilise A1. Aucune formule ne semble fausse isolément, mais ensemble, elles se poursuivent en rond.
Lorsque vous saisissez une telle formule, Excel affiche un avertissement. Une fois l'avertissement fermé, la cellule affiche en général 0, et la barre d'état indique Références circulaires suivi d'une adresse de cellule.
2. Trouver la cellule
Allez dans Formules > Vérification des erreurs, pointez Références circulaires et choisissez une cellule dans la liste. Excel s'y rend. Cliquez sur Formules > Afficher les formules, ou appuyez sur Ctrl + ` (la touche au-dessus de Tab), pour voir toutes les formules sous forme de texte, ce qui facilite la détection d'une auto-référence. Utilisez Formules > Repérer les antécédents pour tracer des flèches vers les cellules lues par la formule : une flèche qui revient vers la même cellule forme la boucle.
La barre d'état ne nomme qu'une seule cellule, et ce peut être une cellule au milieu de la boucle. Corrigez-la, puis vérifiez à nouveau la liste, car un classeur peut contenir plusieurs boucles.
3. Corriger un total qui s'inclut lui-même
La cause la plus courante : un total placé à l'intérieur de la plage qu'il additionne, comme =SOMME(B2:B10) saisie en B10. La formule additionne la cellule où elle se trouve. Modifiez la plage pour qu'elle s'arrête au-dessus du total, =SOMME(B2:B9), ou déplacez le total hors des données. Insérer une ligne à l'intérieur d'une plage peut aussi l'étendre jusqu'à une ligne de total : vérifiez donc la plage après une insertion.
4. Corriger un pourcentage ou un prix qui se nourrit de lui-même
Deux cellules qui dépendent chacune de l'autre forment une boucle indirecte. Par exemple, un prix avec taxes en C2 qui utilise une remise en D2, alors que D2 est calculée en pourcentage de C2. Décidez quelle valeur est la donnée et laquelle est le résultat, puis calculez le résultat à partir de la donnée seulement. Ici, calculez la remise à partir du prix hors taxes, puis le prix final à partir des deux.
Notez d'abord l'ordre du calcul sur papier : les données, puis chaque résultat à son tour. Si l'ordre exige un résultat qui n'existe pas encore, le modèle pose un vrai problème de logique circulaire, et vous devez le restructurer, pas seulement modifier une formule.
5. Corriger un total cumulé qui se réfère à lui-même
Un total cumulé en C2 écrit =C2+B2 se réfère à lui-même. La forme correcte ajoute le total précédent : en C3, =C2+B3, et en C2 simplement =B2. Recopiez vers le bas. Fixez la première cellule d'une plage avec un signe dollar lorsque vous additionnez depuis le haut : =SOMME($B$2:B3). Vous pouvez ainsi copier la formule sans déplacer le début.
6. Corriger une formule copiée ou déplacée
Parfois, une bonne formule devient circulaire après une copie, car une référence relative pointe désormais vers sa propre cellule. Vérifiez la formule à son nouvel emplacement, et utilisez un signe dollar sur la partie qui ne doit pas bouger. Une table de recherche collée par-dessus les cellules qu'elle lit est une autre source de problème. Cherchez aussi une référence, dans une autre feuille ou un autre classeur, qui renvoie à la feuille d'origine.
7. Quand une boucle est voulue
Certains modèles ont besoin d'une boucle, par exemple un intérêt calculé sur un solde moyen qui comprend l'intérêt lui-même. Dans ce cas, allez dans Fichier > Options > Formules et cochez Activer le calcul itératif. Définissez le nombre maximal d'itérations (100 par défaut) et l'écart maximal (0.001). Excel recalcule alors la boucle jusqu'à ce que la variation soit inférieure à cette limite.
N'activez cette option que si vous en avez vraiment besoin. Avec les itérations activées, Excel n'avertit plus des boucles accidentelles, si bien qu'une erreur de saisie peut passer inaperçue longtemps. Ajoutez une note à côté du modèle indiquant que les itérations sont nécessaires.
Erreurs courantes
Activer le calcul itératif pour faire taire l'avertissement. L'avertissement disparaît, mais la formule dépend toujours d'elle-même et donne un résultat faux.
Correction : Trouvez la cellule, corrigez la formule, et laissez les itérations désactivées sauf si le modèle en a besoin.
Ignorer le résultat 0. Une formule circulaire affiche en général 0, ce qui ressemble à une vraie valeur.
Correction : Considérez un 0 surprenant comme un signe qu'il faut vérifier la barre d'état pour les références circulaires.
Ne corriger que la cellule nommée par Excel. La barre d'état nomme une cellule, mais la boucle peut passer par d'autres.
Correction : Après chaque correction, rouvrez la vérification des erreurs jusqu'à ce que l'entrée Références circulaires disparaisse.
Questions fréquemment posées
Pourquoi Excel affiche-t-il 0 pour une référence circulaire ?
Excel ne peut pas terminer le calcul, donc il conserve la dernière valeur qu'il avait, soit 0 pour une nouvelle formule. La cellule n'est pas correcte, même si elle contient un nombre.
Pourquoi n'y a-t-il aucun avertissement de référence circulaire à l'ouverture du fichier ?
Le classeur peut avoir le calcul itératif activé, ou l'avertissement peut être désactivé dans les options d'Excel. Vérifiez Fichier > Options > Formules.
Est-ce que {fn:IF} peut supprimer une référence circulaire ?
Pas à lui seul. Excel voit toujours la référence dans la formule, même si SI (Anglais: IF) ne l'utiliserait pas. Supprimez la référence, ou utilisez un ordre de calcul qui n'en a pas besoin.
Fonctions associées
- SUMSUM additionne des nombres, des cellules et des plages entières en une seule formule.
- IFIF vérifie une condition et renvoie une valeur si elle est vraie, une autre si elle est fausse.
- IFERRORIFERROR renvoie la valeur de votre choix lorsqu'une formule produit une erreur comme #N/A ou #DIV/0!, et le résultat normal sinon.
En savoir plus : références circulaires
Le guide montre comment trouver une boucle et corriger les causes courantes. Cette partie ajoute comment lire les flèches, comment une boucle se cache entre feuilles, et comment construire un modèle qui nécessite des itérations en toute sécurité.
Lire les flèches de dépendance
Sélectionnez la cellule nommée par Excel et choisissez Formules > Repérer les antécédents. Des flèches bleues pointent depuis chaque cellule lue par la formule. Cliquez à nouveau pour remonter d'un niveau. Dans une boucle, les flèches vous font faire le tour et revenir à la cellule de départ. Repérer les dépendants fonctionne dans l'autre sens : il montre les cellules qui utilisent la cellule sélectionnée, ce qui aide lorsque vous ne savez pas quelle formule modifier en premier.
Supprimez les flèches avec Supprimer les flèches une fois terminé, car elles restent sur la feuille tant que vous ne les effacez pas.
Boucles cachées entre feuilles
Une boucle ne doit pas rester sur une seule feuille. Une feuille de synthèse peut lire une feuille de détail qui, dans un coin peu ouvert, la relit à son tour. La vérification des erreurs indique la cellule, et le nom de la feuille apparaît dans la barre de formule. Lorsque les flèches de dépendance atteignent une autre feuille, elles deviennent une ligne en pointillés avec une petite icône de feuille. Double-cliquez sur la flèche en pointillés pour aller à l'autre cellule.
=SOMME(Detail!C2:C20)
=Summary!B2*0,1Utiliser les itérations en toute sécurité
Si le modèle a vraiment besoin d'une boucle, gardez-la petite. Placez la formule en boucle dans une cellule clairement étiquetée, et donnez-lui une valeur de départ raisonnable pour que le premier passage ne produise pas d'erreur. Encadrez la formule avec SIERREUR (Anglais: IFERROR), car une erreur dans une boucle se propage sans jamais disparaître. Comparez une fois le résultat à un calcul fait à la main, pour vérifier que la boucle converge vers le bon nombre.
Entraînez-vous aux références qui évitent la plupart des boucles dans [la leçon sur les références de cellules](/learn/cell-references), et aux outils d'erreur dans [la leçon sur les erreurs](/learn/errors-iferror).
Habitudes qui évitent les boucles
Séparez les données, les calculs et les résultats dans des zones distinctes, et laissez l'information circuler dans un seul sens, des données vers les résultats. Gardez les totaux en dehors des plages qu'ils additionnent. Lorsque vous insérez des lignes, vérifiez les formules qui additionnent de part et d'autre du point d'insertion. La plupart des boucles viennent d'un total déplacé ou d'une plage qui s'est agrandie, et une mise en page claire rend les deux faciles à repérer.