Comment trouver une référence circulaire dans Excel (rapide et facile)

Lorsque vous travaillez avec des formules Excel, vous pouvez parfois voir l'invite d'avertissement suivante.

Cette invite vous indique qu'il existe une référence circulaire dans votre feuille de calcul et cela peut entraîner un calcul incorrect par les formules. Il vous demande également d'aborder cette question de référence circulaire et de la trier.

Dans ce tutoriel, je couvrirai tout ce que vous devez savoir sur la référence circulaire, ainsi que comment rechercher et supprimer des références circulaires dans Excel.

Alors, commençons!

Qu'est-ce que la 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 en 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 ayez le jeu de données dans la cellule A1:A5 et que vous utilisiez la formule ci-dessous dans la cellule A6 :

=SOMME(A1:A6)

Cela vous donnera un avertissement de référence circulaire.

En effet, vous souhaitez additionner les valeurs de la cellule A1:A6 et le résultat doit se trouver dans la cellule A6.

Cela crée une boucle car Excel continue d'ajouter la nouvelle valeur dans la cellule A6, qui continue de changer (d'où une boucle de référence circulaire).

Comment trouver des 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 des références circulaires dans la feuille de calcul, vous devez connaître un moyen de les trouver d'une manière ou d'une autre.

Voici les étapes pour trouver une référence circulaire dans Excel :

1. Activer la feuille de calcul qui a la référence circulaire
2. Cliquez sur l'onglet Formules
3. Dans le groupe Édition de formule, cliquez sur l'icône déroulante Vérification des erreurs (petite flèche pointant vers le bas à droite)
4. Placez le curseur sur l'option Références circulaires. Il vous montrera la cellule qui a une référence circulaire dans la feuille de calcul
5. Cliquez sur l'adresse de la cellule (affichée) et cela vous amènera à cette cellule dans la feuille de calcul.

Une fois que vous avez résolu le problème, vous pouvez à nouveau suivre les mêmes étapes ci-dessus et il affichera 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. Sur la partie gauche de celui-ci, il vous montrera le texte Référence circulaire avec l'adresse de la cellule.

Vous devez savoir certaines choses lorsque vous travaillez avec des références circulaires :

1. Si le calcul itératif est activé (voir plus loin dans ce didacticiel), la barre d'état n'affichera pas l'adresse de la cellule de référence circulaire
2. Si 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 la référence circulaire et non l'adresse de la cellule
3. Si vous recevez une fois une invite d'avertissement de référence circulaire et que vous la rejetez, elle n'affichera plus l'invite la prochaine fois.
4. Si vous ouvrez un classeur qui a la référence circulaire, il vous montrera l'invite dès que le classeur s'ouvre.

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 (à moins que vous ne vouliez qu'elles soient là pour une raison).

Malheureusement, ce n'est pas aussi simple que d'appuyer sur la touche Suppr. Étant donné que ceux-ci dépendent des formules et que chaque formule est différente, vous devez analyser cela au cas par cas.

S'il s'agit simplement d'avoir la référence de cellule à l'origine du problème par erreur, vous pouvez simplement corriger en ajustant la référence.

Mais parfois, ce n'est pas si simple.

La référence circulaire peut également être provoquée en fonction de plusieurs cellules qui s'alimentent les unes aux autres à plusieurs niveaux.

Permettez-moi de vous montrer un exemple.

Ci-dessous, il y a une référence circulaire dans la cellule C6, mais ce n'est pas seulement un simple cas d'auto-référence. C'est à plusieurs niveaux où les cellules qu'il utilise dans les calculs se réfèrent également les unes aux autres.

• Les formules de la cellule A6 sont =SUM(A1:A5)+C6
• La formule est la cellule C1 est =A6*0.1
• La formule dans la cellule C6 est =A6+C1

Dans l'exemple ci-dessus, le résultat de la cellule C6 dépend des valeurs des cellules A6 et C1, qui à leur tour dépendent de la cellule C6 (provoquant ainsi l'erreur de référence circulaire)

Et encore une fois, j'ai choisi un exemple très simple à des fins de démonstration. En réalité, ceux-ci pourraient être assez difficiles à comprendre et peut-être éloignés dans la même feuille de calcul ou même dispersés sur plusieurs feuilles de calcul.

Dans un tel cas, il existe un moyen d'identifier les cellules qui provoquent une référence circulaire, puis de les traiter.

C'est en utilisant l'option Tracer les précédents.

Vous trouverez ci-dessous les étapes à suivre pour utiliser les précédents de trace pour rechercher les cellules qui alimentent la cellule qui a la référence circulaire :

1. Sélectionnez la cellule qui a la référence circulaire
2. Cliquez sur l'onglet Formules
3. Cliquez sur Tracer les précédents

Les étapes ci-dessus vous montreraient des flèches bleues qui vous diraient 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.

Si vous travaillez avec des modèles financiers complexes, il est possible que ces précédents s'étendent également à plusieurs niveaux.

Cela fonctionne bien si vous avez toutes les formules faisant référence à 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 l'invite 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 s'y accrocher. Il renvoie donc un 0.

Mais dans certains cas, vous souhaiterez peut-être que la référence circulaire soit active et effectuer quelques itérations. Dans un tel cas, au lieu d'une boucle infinie, vous pouvez décider combien de fois la boucle doit être exécutée.

C'est appelé calcul itératif dans Excel.

Vous trouverez ci-dessous les étapes pour activer et configurer les calculs itératifs dans Excel :

1. Cliquez sur l'onglet Fichier
2. Cliquez sur Options. Cela ouvrira la boîte de dialogue Options Excel
3. Sélectionnez Formule dans le volet de gauche
4. 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 ça! Les étapes ci-dessus permettraient un calcul itératif dans Excel.

Permettez-moi également d'expliquer rapidement les deux options du calcul itératif :

• Itérations maximales: C'est le nombre maximum de fois que vous voulez qu'Excel calcule avant de vous donner le résultat final. Donc, si vous spécifiez 100, Excel exécutera la boucle 100 fois avant de vous donner le résultat final.
• Changement maximal: C'est le changement maximum, qui s'il n'est pas atteint entre les itérations, le calcul serait arrêté. Par défaut, la valeur est 0,001. Plus cette valeur est basse, plus le résultat sera précis.

N'oubliez pas que plus les itérations sont exécutées, plus il faut de temps et de ressources à Excel pour le faire. Si vous maintenez le nombre maximal d'itérations élevé, cela peut entraîner un ralentissement ou un blocage de votre Excel.

Remarque : lorsque les calculs itératifs sont activés, Excel ne vous montrera pas l'invite d'avertissement de référence circulaire et l'affichera également maintenant dans la barre d'état.

Utiliser délibérément des 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 vers différentes cellules."

Mais il peut y avoir des cas spécifiques où vous avez besoin d'une référence circulaire afin d'obtenir le résultat souhaité.

Un de ces cas spécifiques que j'ai déjà écrit à ce sujet obtenir l'horodatage dans une cellule dans une cellule dans Excel.

Par exemple, supposons que vous souhaitiez créer une formule de sorte que chaque entrée soit effectuée dans une cellule de la colonne A, l'horodatage apparaisse dans la colonne B (comme indiqué ci-dessous) :

Bien que vous puissiez insérer facilement, insérez un horodatage en utilisant la formule ci-dessous :

=SI(A2"",SI(B2"",B2,MAINTENANT()),"")

Le problème avec la formule ci-dessus est qu'elle mettrait à jour tous les horodatages dès qu'une modification est apportée à la feuille de calcul ou si la feuille de calcul est rouverte (car la formule MAINTENANT 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 existe également d'autres cas où la possibilité d'utiliser une référence circulaire est souhaitée (vous pouvez trouver un exemple ici).

Remarque : Bien qu'il puisse y avoir des cas où vous pouvez utiliser une référence circulaire, je pense qu'il est préférable d'éviter de l'utiliser. Les références circulaires peuvent également nuire aux performances de votre classeur et le ralentir. Dans les rares cas où vous en avez besoin, je préfère toujours utiliser des codes VBA pour faire le travail.

J'espère que vous avez trouvé ce tutoriel utile!

Autres didacticiels Excel qui pourraient vous être utiles :

• #REF ! Erreur dans Excel; Comment corriger l'erreur de référence !
• Gestion des erreurs Excel VBA
• Utilisez IFERROR avec RECHERCHEV pour éliminer les erreurs #N/A
• Comment référencer une autre feuille ou un classeur dans Excel (avec des exemples)
• Références de cellules absolues, relatives et mixtes dans Excel