L'un des avertissements d'erreur les plus courants rencontrés par les utilisateurs dans Excel est la « référence circulaire ». Des milliers d'utilisateurs ont le même problème, et cela se produit lorsqu'une formule renvoie directement ou indirectement à sa propre cellule, provoquant une boucle sans fin de calculs.
Par exemple, vous avez deux valeurs dans les cellules B1 et B2. Lorsque la formule =B1+B2 est entrée dans B2, cela crée une référence circulaire ; la formule dans B2 se recalcule à plusieurs reprises car chaque fois qu'elle calcule, la valeur B2 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 feuille de calcul peuvent entraîner un calcul incorrect de votre formule.
Par conséquent, dans cet article, nous expliquerons tout ce que vous devez savoir sur les références circulaires et comment rechercher, 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 où réside votre formule. Fondamentalement, cela se produit lorsque votre formule essaie de se calculer.
Par exemple, vous avez une colonne de nombres dans la cellule A1:A4 et vous utilisez la fonction SOMME (=SOMME(A1:A5)) dans la cellule A5. La cellule A5 fait directement référence à sa propre cellule, ce qui est faux. 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 le bouton « Aide » pour en savoir plus sur l'erreur, ou fermer la fenêtre du message d'erreur en cliquant sur le bouton « OK » ou « X » et obtenir « 0 » comme résultat.
Parfois, les boucles de référence circulaires peuvent faire planter votre calcul ou ralentir les performances de votre feuille de calcul. La référence circulaire peut également conduire à un certain nombre d'autres problèmes, qui ne seront pas évidents immédiatement. 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 renvoie directement à sa propre cellule.
Dans l'exemple ci-dessous, la formule de la cellule A2 fait directement référence à sa propre cellule (A2).
Une fois que le message d'avertissement s'affiche, vous pouvez cliquer sur « OK », mais cela ne donnera que « 0 ».
Référence circulaire indirecte
Une référence circulaire indirecte dans Excel se produit lorsqu'une valeur dans une formule renvoie à sa propre cellule, mais pas directement. En d'autres termes, la référence circulaire peut être formée de deux cellules se référençant l'une l'autre.
Expliquons-nous avec cet 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 indiquée ci-dessous. Toutes les autres cellules dépendent de la cellule A1. Lorsque vous utilisez une référence de toute autre cellule de formule précédente dans A1, cela entraînera un avertissement de référence circulaire. Parce que la formule dans A1 fait référence à la cellule A5, qui fait référence à C3, et la cellule C3 fait référence à A1, d'où la référence circulaire.
Lorsque vous cliquez sur « OK », la valeur 0 dans la cellule A1 est obtenue et Excel crée une ligne liée affichant les antécédents de trace et les dépendances de trace, comme indiqué ci-dessous. Nous pouvons utiliser cette fonctionnalité pour trouver et corriger/supprimer facilement des références circulaires.
Comment activer / désactiver les références circulaires dans Excel
Par défaut, les calculs itératifs sont désactivés (désactivés) dans Excel. Les calculs itératifs sont des calculs répétitifs jusqu'à ce qu'ils remplissent une condition spécifique. Lorsqu'il est désactivé, Excel affiche un message de référence circulaire et renvoie un 0 comme résultat.
Cependant, des références circulaires sont parfois nécessaires pour calculer une boucle. Pour utiliser la référence circulaire, vous devez activer les calculs itératifs dans votre Excel et cela vous permettra d'effectuer vos calculs. Maintenant, laissez-nous vous montrer 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 un calcul itératif et donc une 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 accéder à Menu > Outils > Options > onglet Calcul.
Itérations maximales et paramètres de modification maximale
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 illustré dans la capture d'écran ci-dessous.
- Itérations maximales – Ce nombre spécifie combien de fois la formule doit recalculer avant de vous donner le résultat final. La valeur par défaut est 100. Si vous la remplacez par « 50 », Excel répétera les calculs 50 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 la variation maximale entre les résultats de calcul. Cette valeur détermine la précision du résultat. Plus le nombre est petit, plus le résultat sera précis et plus il faudra de temps pour calculer la feuille de calcul.
Si l'option de calculs itératifs est activée, vous ne recevrez aucun avertissement chaque fois qu'il y a une référence circulaire dans votre feuille de calcul. N'activez le calcul interactif que lorsque cela est absolument nécessaire.
Trouver une référence circulaire dans Excel
Supposons que vous ayez un jeu de données volumineux et que vous receviez l'avertissement de référence circulaire, vous devrez toujours savoir où (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 que vous voulez dans la liste et cela vous amènera à cette adresse de cellule pour résoudre le problème.
Utilisation de la barre d'état
Vous pouvez également trouver la référence circulaire dans 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 certaines choses que vous devez savoir lorsque vous manipulez une référence circulaire :
- La barre d'état n'affichera pas l'adresse de la cellule de référence circulaire lorsque l'option Calcul itératif est activée, vous devez donc la désactiver avant de commencer à consulter le classeur pour les références circulaires.
- Si aucune référence circulaire n'est trouvée dans la feuille active, la barre d'état n'affiche que « Références circulaires » sans adresse de cellule.
- Vous n'obtiendrez une invite de référence circulaire qu'une seule fois et après avoir cliqué sur "OK", l'invite ne s'affichera plus 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, il n'y a aucune option dans Excel qui vous permettra 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 des 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, changez la formule dans B6 en =SUM(B1:B5)*A5 (en changeant 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 Tracer les précédents et Tracer les dépendances pour la remonter à la source et la résoudre une par une. La flèche montre quelles cellules sont affectées par la cellule active.
Il existe deux méthodes de traçage qui peuvent vous aider à supprimer les références circulaires en affichant 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épendances" dans le groupe Audit de formule.
Tracer les précédents
Lorsque vous sélectionnez cette option, elle retrace les cellules qui affectent la valeur de la cellule active. Il dessine 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 fait également partie de la formule, ce qui en fait une référence circulaire et fait que la formule renvoie « 0 » comme résultat.
Cela peut être facilement corrigé en remplaçant B6 par B5 dans l'argument de SUM : =SUM(B1:B5).
Tracer les dépendants
La fonction Tracer les dépendants trace les cellules qui dépendent de la cellule sélectionnée. Cette fonctionnalité dessine une ligne bleue indiquant quelles cellules sont affectées par la cellule sélectionnée. C'est-à-dire qu'il affiche les cellules contenant des formules faisant référence à la cellule active. La touche de raccourci pour utiliser les personnes à charge est Alt + T U D.
Dans l'exemple suivant, la cellule D3 est affectée par B4. Il dépend de B4 pour que sa valeur produise des résultats. Par conséquent, la trace dépendante dessine une ligne bleue de B4 à D3, indiquant que D3 dépend de B4.
Utiliser délibérément des 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 arriver que vous ayez besoin d'une référence circulaire dans de rares cas pour obtenir la sortie souhaitée.
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 achetez une maison et que vous souhaitez donner une commission de 2% sur le coût total de la maison à 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. Comme 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 :
=SOMME(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 ou de 0 dans le résultat. Au lieu de cela, le résultat des cellules B6 et B4 sera calculé comme indiqué ci-dessus.
L'option de calculs itératifs est généralement désactivée par défaut. Si vous ne l'avez pas activé et lorsque vous entrez la formule en B4 qui créera une référence circulaire. Excel émettra l'avertissement et lorsque vous cliquerez sur « OK », la flèche du traceur s'affichera.
C'est ça. C'était tout ce que vous deviez savoir sur les références circulaires dans Excel.