Comment corriger la référence circulaire dans Excel? – Tutoriel de solution rapide!

Dans Excel, les utilisateurs rencontrent souvent un problème courant appelé “références circulaires“. Il est frustrant de faire face à cette erreur car elle perturbe votre flux de travail et ruine votre humeur. Eh bien, dans cet article, j’aborde les détails de l’erreur de référence circulaire, en explorant les causes courantes et d’autres informations vitales ainsi que la façon de corriger la référence circulaire dans Excel.

Vous pouvez obtenir deux erreurs de référence circulaire:

“There are one or more circular references where a formula refers to its own cell either directly or indirectly.”

“Careful, we found one or more circular references in your workbook that might cause your formulas to calculate incorrectly.”

Examinons donc cet article et apprenons à résoudre les avertissements ci-dessus pour maintenir des calculs précis dans vos feuilles de calcul Excel.

Qu’est-ce qu’une erreur de référence circulaire dans Excel?

La référence circulaire est un problème qui se produit lorsque vous entrez une formule dans la feuille de calcul Excel mais que cela ne fonctionne pas. Au lieu de cela, vous commencerez à recevoir une erreur de référence circulaire.

Eh bien, un tel problème se pose parce que la formule Excel qui vous est assignée essaie de se calculer elle-même.

Prenons un exemple pour mieux comprendre:

Dans la cellule B3, vous utilisez la formule =D1+D2+D3. Donc, dans ce cas, vous utilisez une fonction qui inclut la référence à elle-même.

Pour résoudre ce problème, vous devez déplacer votre formule vers une autre cellule. Pour cela, appuyez simplement sur les touches Ctrl+X de votre clavier pour couper la formule, et après avoir sélectionné une autre cellule, appuyez sur les touches Ctrl+V pour la coller.

Quand une erreur de référence circulaire se produit-elle?

Cette erreur apparaît généralement lorsque :

• Essayer de créer une première référence circulaire dans le classeur Excel.
• Tentative d’ouverture du classeur XLS/XLXS avec la référence circulaire non résolue.
• Création ultérieure de la nouvelle référence circulaire en supprimant les illustrations précédentes.

Quelles sont les causes d’une référence circulaire dans Excel?

Une erreur de référence circulaire Excel se produit lorsque la formule fait directement ou indirectement référence à la cellule dans laquelle elle est déjà contenue. De ce fait, la formule recalcule son résultat déjà obtenu, ce qui génère ce type d’erreur.

Si votre classeur Excel comporte une référence circulaire, le calcul automatique ne sera pas effectué. Au lieu de cela, vous devez utiliser la fonction de vérification des erreurs d’Excel pour localiser l’emplacement des références circulaires, puis les supprimer.

Si vous les laissez tels quels, Excel calculera chaque cellule impliquée dans cette référence circulaire en utilisant les résultats obtenus lors de l’itération précédente.

Lire aussi: #VALUE! Erreur dans Excel démystifiée: un guide étape par étape

Comment corriger la référence circulaire dans Excel?

Méthode 1 – Rechercher une référence circulaire dans Excel

Vous devez d’abord trouver la référence circulaire, puis essayer de résoudre ce problème en suivant les étapes ci-dessous:

• Appuyez sur les formules.
• Appuyez sur le bouton Vérification des erreurs et appuyez sur la flèche présente à côté.

• Dans la liste déroulante, choisissez Références circulaires et appuyez à nouveau sur la flèche latérale.
• Dans le sous-menu, appuyez sur la première cellule.
• Examinez maintenant la cellule de formule.

• Si vous doutez que la cellule soit responsable d’une erreur de référence circulaire. Appuyez ensuite sur la cellule suivante présente dans le sous-menu Références circulaires, si elle est disponible.
• Continuez avec l’option de révision et apportez des corrections dans la référence circulaire.
• Très bientôt, votre barre d’état vous informera que “Référence circulaire” est présente dans votre classeur avec l’adresse de cette cellule.

• Si ces références circulaires sont présentes dans d’autres feuilles, la barre d’état ne vous montrera que le mot “Références circulaires” sans aucune adresse de cellule.

Voici quelques astuces supplémentaires que vous pouvez essayer pour supprimer la référence circulaire dans Excel:

• Après avoir attribué la formule dans une cellule, vérifiez si vous avez référencé cette cellule de formule dans la cellule elle-même.

Supposons que dans la cellule B3 vous ayez ajouté une formule = (A1+A2)/A3. Cela provoquera alors de telles erreurs de référence circulaire.

• La deuxième option consiste à vérifier s’il existe des références indirectes.

Cela se produira lorsque vous saisirez votre formule dans la cellule A1 et que celle-ci utilise une autre formule présente dans la cellule B1 qui à son tour renvoie à la cellule A1.

Si tout ce processus vous rend confus, réfléchissez simplement à la confusion dans votre application Excel.

Méthode 2 – Activer/désactiver les références circulaires pour corriger les erreurs de référence circulaire dans Excel

Comme cela a déjà été mentionné, ce calcul itératif est désactivé par défaut dans Excel. Afin de faire fonctionner vos formules circulaires, vous devez activer les calculs itératifs dans votre fichier Excel.

Pour les utilisateurs d’Excel 2003:

• Cliquez sur l’option Calcul itératif présente dans l’onglet Menu > Outils > Options > Calcul.

Pour les utilisateurs d’Excel 2007:

• Cliquez sur le bouton Office > Options Excel > Formules > Zone d’itération.

Pour les utilisateurs d’Excel 2019/2016/2013/2010:

• Cliquez sur Fichier>Options. Appuyez maintenant sur l’onglet Formules et choisissez la case à cocher Activer le calcul itératif présente dans les « Options de calcul »

Après avoir activé les calculs itératifs, vous devez spécifier ces deux options:

• Zone d’itérations maximales –

Cette option permet de spécifier le nombre de fois où la formule sera recalculée. Si vous définissez un nombre d’itérations plus élevé, le calcul prendra plus de temps.

• Zone de changement maximum –

En utilisant cette option, vous pouvez définir la variation maximale entre les résultats du calcul. Il est préférable de définir un petit nombre car cela donnera un résultat plus précis. Si vous définissez un nombre plus élevé, Excel prendra plus de temps pour effectuer les calculs.

Remarque: Généralement, Excel arrête d’effectuer des calculs après 100 itérations. Ou lorsque toutes les valeurs présentes dans la référence circulaire ont été modifiées de moins de 0,001 entre les itérations.

Pourquoi devriez-vous éviter d’utiliser des références circulaires dans Excel?

L’idée d’utiliser la référence circulaire dans Excel n’est pas une approche recommandée. Je dis cela car, outre l’affichage d’un message d’avertissement ou d’un problème de performances, cela entraîne plusieurs autres problèmes qui n’apparaîtront pas immédiatement.

Supposons que vous ayez sélectionné une cellule contenant la référence circulaire et que vous ayez ensuite désactivé par erreur le mode d’édition de formule. Ainsi, lorsque vous appuyez sur le bouton Entrée sans apporter aucune modification à la formule, cela vous donnera le résultat zéro.

C’est la raison pour laquelle de nombreux experts Excel suggèrent également d’éviter l’utilisation de références circulaires dans votre classeur Excel si cela est possible.

Lisez aussi: Excel, cette valeur ne correspond pas à l’erreur de validation des données

Comment éviter les erreurs de références circulaires Excel?

Suivez les mesures de précaution ci-dessous pour éviter cette erreur:

• Lorsque vous devez basculer entre les cellules de la feuille de calcul de référence circulaire, faites-le en double-cliquant sur la flèche du traceur. Cette flèche indique les cellules qui affectent la valeur de votre cellule actuellement sélectionnée.
• Affichez la flèche du traceur en appuyant sur les formules, puis choisissez Trace Dependents ou Trace Precedents.
• Fermez tous vos classeurs ouverts, puis créez un nouveau classeur. Après cela, entrez la formule qui contient une référence circulaire.

FAQ connexes:

Les références circulaires peuvent-elles provoquer une corruption des données dans Excel?

Oui, les références circulaires provoquent une corruption des données dans Excel.

La référence circulaire est-elle bonne ou mauvaise?

Franchement, une référence circulaire reste une mauvaise chose.

Comment autoriser les références circulaires dans Excel?

Vous pouvez autoriser les références circulaires dans Excel en suivant ces étapes:

• Allez dans l’onglet Fichier >> choisissez « Options ».
• Choisissez un onglet « Formules » >> section « Options de calcul ».
• Cochez la case « Activer le calcul itératif ».
• Maintenant, modifiez le champ d’option « Itérations maximales » à 100 » >> appuyez sur “OK”.

Quelle est l’alternative à la référence circulaire dans Excel?

Il existe diverses méthodes alternatives à la référence circulaire dans MS Excel, notamment la méthode macro copier-coller, la méthode algébrique, etc.

Est-il toujours nécessaire de résoudre les références circulaires?

Les références circulaires n’affectent pas toujours vos calculs, mais il est conseillé de les résoudre pour préserver l’exactitude des données.

Que dois-je faire si je rencontre fréquemment des références circulaires?

Si vous rencontrez fréquemment des avertissements de référence circulaire, demandez l’aide de professionnels ou de communautés Excel.

Verdict final

Eh bien, la gestion des références circulaires est un aspect essentiel du maintien de feuilles de calcul Excel précises et fiables. Mais lorsque vous utilisez cette fonctionnalité, vous pouvez rencontrer des avertissements à tout moment. Pour faire face à de telles situations, vous devez suivre les solutions mentionnées ci-dessus pour supprimer la référence circulaire dans Excel et améliorer votre expérience Excel.

C’est tout…