L’un des avertissements d’erreur les plus courants que les utilisateurs rencontrent dans Excel est la «référence circulaire». Des milliers d’utilisateurs ont le même problème, et il se produit lorsqu’une formule renvoie directement ou indirectement à sa propre cellule, créant une boucle sans fin de calculs.
Par exemple, vous avez deux valeurs dans les cellules A1 et A2. Lorsque la formule=A1 + A2 est entrée dans A2, cela crée une référence circulaire; la formule en A2 se recalcule d’elle-même à plusieurs reprises car à chaque fois qu’elle calcule, la valeur A2 a changé.
La plupart des références circulaires sont des erreurs involontaires; Excel vous en avertira. Cependant, il existe également des références circulaires prévues, qui sont utilisées pour effectuer des calculs itératifs. Les références circulaires involontaires dans votre classeur peuvent entraîner un calcul incorrect de votre formule.
Par conséquent, dans cet article, nous couvrirons tout ce que vous devez savoir sur les références circulaires, ainsi que sur la façon de trouver, corriger, supprimer et utiliser des références circulaires dans Excel.
Comment rechercher et gérer une référence circulaire dans Excel
Lorsque vous travaillez avec Excel, nous rencontrons parfois des erreurs de référence circulaires qui se produisent lorsque vous entrez une formule qui inclut la cellule dans laquelle réside votre formule. En gros, cela se produit lorsque votre formule essaie de se calculer elle-même.
Par exemple, vous avez une colonne de nombres dans la cellule A1: A4 et vous utilisez la fonction SOMME (=SUM (A1: A5)) dans la cellule A5. La cellule A5 fait directement référence à sa propre cellule, ce qui n’est pas possible. Par conséquent, vous obtiendrez l’avertissement de référence circulaire suivant:
Une fois que vous avez reçu le message d’avertissement ci-dessus, vous pouvez cliquer sur”Aide”pour plus d’informations, ou fermer la fenêtre du message en cliquant sur le bouton”OK”ou”X”et obtenir”0″comme résultat.
Ces boucles de référence circulaires peuvent bloquer votre calcul ou ralentir les performances de votre feuille de calcul. Ils peuvent également entraîner un certain nombre d’autres problèmes, qui ne sont pas immédiatement apparents. Il est donc préférable de les éviter.
Références circulaires directes et indirectes
Les références circulaires peuvent être classées en deux types: les références circulaires directes et les références circulaires indirectes.
Référence directe
Une référence circulaire directe est assez simple. Le message d’avertissement de référence circulaire directe apparaît lorsque la formule dans une cellule fait directement référence à sa propre cellule.
Par exemple, la formule de la cellule A2 ci-dessous fait directement référence à sa propre cellule (A2).
Une fois le message d’avertissement affiché, l’utilisateur peut cliquer sur”OK”, mais cela ne donnera que”0″.
Référence circulaire indirecte
Une référence circulaire indirecte dans Excel impose des places lorsqu’une valeur dans une formule fait référence à sa propre cellule, pas directement mais à un certain niveau. En d’autres termes, une référence circulaire peut être établie par deux cellules se ciblant l’une l’autre.
Prenons un exemple simple.
Maintenant, la valeur commence à partir de A1 qui a la valeur 20.
Ensuite, la cellule C3 fait référence à la cellule A1.
Ensuite, la cellule A5 fait référence à la cellule C3.
Remplacez maintenant la valeur 20 dans la cellule A1 par la formule comme indiqué ci-dessous. Toutes les autres cellules dépendent de la cellule A1. Lorsque vous utilisez une référence de n’importe quelle autre cellule dans A1, il crée un avertissement de référence circulaire. Parce que la formule en A1 fait référence à la cellule A5, qui fait référence à C3, et la cellule C3 renvoie à A1, d’où la référence circulaire.
Lorsque vous cliquez sur”OK”, il en résulte une valeur de 0 dans la cellule A1 et Excel crée une ligne liée affichant les antécédents de trace et les dépendances de trace comme indiqué dans la capture d’écran ci-dessous. Nous pouvons utiliser cette fonctionnalité pour trouver et corriger facilement une référence circulaire.
Comment activer/désactiver les références circulaires dans Excel
Par défaut, les calculs itératifs sont désactivés dans Excel. Les calculs itératifs sont des calculs répétés jusqu’à ce qu’une condition spécifique soit remplie. Lorsqu’elle est désactivée, Excel renvoie une invite de référence circulaire et renvoie un 0 comme résultat.
Cependant, il existe de rares cas où des références circulaires sont nécessaires pour effectuer une boucle. Pour utiliser la référence circulaire, vous devez activer les calculs itératifs dans votre classeur Excel et cela vous permettra d’effectuer vos calculs. Voyons maintenant comment activer ou désactiver les calculs itératifs.
Dans Excel 2010, Excel 2013, Excel 2016, Excel 2019 et Microsoft 365, accédez à l’onglet”Fichier”dans le coin supérieur gauche d’Excel, puis cliquez sur”Options”dans le volet gauche.
Dans la fenêtre Options Excel, accédez à l’onglet”Formule”et cochez la case”Activer le calcul itératif”dans la section”Options de calcul”. Cliquez ensuite sur”OK”pour enregistrer les modifications.
Cela permettra le calcul itératif et donc la référence circulaire.
Pour y parvenir dans les versions précédentes d’Excel, procédez comme suit:
- Dans Excel 2007, cliquez sur le bouton Office> Options Excel> Formules> Zone d’itération.
- Dans Excel 2003 et les versions antérieures, vous devez aller dans Menu> Outils> Options> Calcul onglet.
Nombre maximum d’itérations et paramètres de modification maximum
Une fois que vous avez activé les calculs itératifs, vous pouvez contrôler les calculs itératifs, en spécifiant deux options disponibles dans la section Activer le calcul itératif comme indiqué dans la capture d’écran ci-dessous.
- Itérations maximales -Il spécifie combien de fois la formule doit recalculer avant de vous donner le résultat final. Comme nous pouvons le voir ici, la valeur par défaut est 100. Cela signifie qu’Excel répétera les calculs 100 fois avant de vous donner le résultat final. N’oubliez pas que plus le nombre d’itérations est élevé, plus il faut de ressources et de temps pour calculer.
- Changement maximal -Il détermine le changement maximal entre les résultats du calcul. Il s’agit de la précision du résultat. Plus le nombre est petit, plus le résultat sera précis et plus le calcul de la feuille de calcul prend du temps.
Après cela, vous ne recevrez aucun avertissement chaque fois qu’il y aura une référence circulaire dans votre feuille de calcul. N’activez le calcul interactif que lorsque cela est absolument nécessaire.
Rechercher une référence circulaire dans Excel
Supposons que vous ayez un ensemble de données volumineux et que vous obteniez l’avertissement de référence circulaire, vous devrez toujours trouver dans quelle cellule l’erreur s’est produite afin de la corriger. Pour rechercher des références circulaires dans Excel, procédez comme suit:
Utilisation de l’outil de vérification des erreurs
Tout d’abord, ouvrez la feuille de calcul où la référence circulaire s’est produite. Allez dans l’onglet «Formule», cliquez sur la flèche à côté de l’outil «Vérification des erreurs». Ensuite, placez simplement le curseur sur l’option «Références circulaires», Excel vous montrera la liste de toutes les cellules impliquées dans la référence circulaire, comme indiqué ci-dessous.
Cliquez sur l’adresse de cellule de votre choix et cela vous mènera à cette cellule particulière pour résoudre le problème.
Utilisation de la barre d’état
Un autre moyen simple de trouver la référence circulaire consiste à consulter la barre d’état. Dans la barre d’état d’Excel, il vous montrera la dernière adresse de cellule avec une référence circulaire, telle que”Références circulaires: B6″(voir la capture d’écran ci-dessous).
Il y a peu de choses que vous devez savoir lorsque vous travaillez avec une référence circulaire:
- La barre d’état n’affiche pas l’adresse de la cellule de référence circulaire lorsque l’option de calcul itératif est activée, vous devez donc la désactiver avant de commencer à rechercher des références circulaires dans le classeur.
- In si la référence circulaire n’est pas trouvée dans la feuille active, la barre d’état n’affiche que «Références circulaires» sans adresse de cellule.
- Vous n’obtiendrez qu’un avertissement de référence circulaire une fois et après l’avoir fermé, il ne sera pas afficher à nouveau l’invite la prochaine fois.
- Si votre classeur a des références circulaires, il vous montrera l’invite chaque fois que vous l’ouvrirez jusqu’à ce que vous résolviez la référence circulaire ou jusqu’à ce que vous activiez le calcul itératif.
Supprimer une référence circulaire dans Excel
Trouver des références circulaires est facile, mais les corriger n’est pas si simple. Malheureusement, rien dans Excel ne vous permet de supprimer toutes les références circulaires à la fois.
Pour corriger les références circulaires, vous devez rechercher chaque référence circulaire individuellement et essayer de la modifier, supprimer complètement la formule circulaire ou la remplacer par une autre.
Parfois, dans les formules simples, il suffit de réajuster les paramètres de la formule pour qu’elle ne se réfère pas à elle-même. Par exemple, modifiez la formule de B6 en=SOMME (B1: B5) * A5 (en remplaçant B6 en B5).
Il renverra le résultat du calcul sous la forme”756″.
Dans les cas où une référence circulaire Excel est difficile à trouver, vous pouvez utiliser les fonctionnalités Trace Precedents et Trace Dependents pour la retracer jusqu’à la source et la résoudre une par une. La flèche indique les cellules affectées par la cellule active.
Il existe deux méthodes de traçage qui vous permettront de supprimer des références circulaires en traçant les relations entre les formules et les cellules.
Pour accéder aux méthodes de traçage, accédez à l’onglet”Formules”, puis cliquez sur”Tracer les précédents”ou”Tracer les dépendants”dans le groupe Audit des formules.
Tracer les précédents
Lorsque vous sélectionnez cette option, elle effectue le suivi des cellules qui affectent la valeur de la cellule active. Il trace une ligne bleue indiquant quelles cellules affectent la cellule actuelle. La touche de raccourci pour utiliser les précédents de trace est Alt + T U T .
Dans l’exemple ci-dessous, la flèche bleue montre que les cellules qui affectent la valeur B6 sont B1: B6 et A5. Comme vous pouvez le voir ci-dessous, la cellule B6 est également incluse, ce qui en fait une référence circulaire et fait que la formule renvoie 0.
Cela peut être facilement résolu en remplaçant B6 par B5 dans l’argument de SUM:=SUM (B1: B5).
Tracer les personnes à charge
La fonction de trace des dépendants trace les cellules qui dépendent de la cellule active. Cette fonction trace des lignes indiquant les cellules affectées par la cellule sélectionnée. En d’autres termes, il montre quelles cellules contiennent des formules qui font référence à la cellule active. La touche de raccourci pour utiliser les dépendants est Alt + T U D .
Dans l’exemple suivant, la cellule D3 est affectée par B4. Il dépend de B4 pour sa valeur pour produire des résultats. Par conséquent, trace dépendante trace une ligne bleue de B4 à D3, indiquant que D3 dépend de B4.
Utilisation délibérée de références circulaires dans Excel
L’utilisation délibérée de références circulaires n’est pas recommandée, mais il peut y avoir des cas spécifiques où vous avez besoin d’une référence circulaire pour obtenir le résultat souhaité.
Expliquons cela en utilisant un exemple.
Pour commencer, activez”Calcul itératif”dans votre classeur Excel. Une fois que vous avez activé le calcul itératif, vous pouvez commencer à utiliser des références circulaires à votre avantage.
Supposons que vous achetiez une maison et que vous ayez décidé de verser une commission de 2% sur le coût total à votre agent. Le coût total sera calculé dans la cellule B6 et le pourcentage de commission (frais d’agent) est calculé dans B4. La commission est calculée à partir du coût total et le coût total comprend la commission. Étant donné que les cellules B4 et B6 dépendent l’une de l’autre, cela crée une référence circulaire.
Entrez la formule pour calculer le coût total dans la cellule B6:
=SUM (B1: B4)
Étant donné que le coût total comprend les frais d’agent, nous avons inclus B4 dans la formule ci-dessus.
Pour calculer les frais d’agent de 2%, insérez cette formule dans B4:
=B6 * 2%
Maintenant, la formule de la cellule B4 dépend de la valeur de B6 pour calculer 2% des frais totaux et la formule de B6 dépend de B4 pour calculer le coût total (y compris les frais d’agent), d’où la référence circulaire.
Si le calcul itératif est activé, Excel ne vous donnera pas d’avertissement ni de 0 dans le résultat. Au lieu de cela, le résultat des cellules B6 et B4 sera calculé comme indiqué ci-dessus.
Le calcul itératif est généralement désactivé par défaut. Si vous ne l’avez pas activé et lorsque vous entrez la formule en B4 qui va créer une référence circulaire. Excel émettra un avertissement et lorsque vous cliquerez sur”OK”, la flèche du traceur sera affichée.
C’est tout. C’est tout ce que vous devez savoir sur les références circulaires dans Excel.
