Comment trouver des références circulaires dans Excel de 4 façons

Avez-vous vu l'erreur de référence circulaire sur une feuille Excel et vous vous demandez si la feuille de calcul est cassée? Fais-moi confiance! Vous n'êtes pas le premier à faire face à cette erreur Excel. Des centaines et des milliers d'utilisateurs d'Excel rencontrent chaque jour des erreurs de référence circulaires. Ne pas s'inquiéter! Voici comment trouver des références circulaires dans Excel et les résoudre.

En tant qu'utilisateur passionné d'Excel, vous tapez rarement les mêmes données dans plusieurs cellules. Au lieu de cela, vous importez un ensemble de données à partir de la source et utilisez le référencement des cellules pour créer des formules. Ainsi, vous créez une feuille de calcul dynamique.

Lorsque quelqu'un modifie les valeurs de cellule des références de cellule, la feuille de calcul se met à jour automatiquement. C'est ce qu'on appelle l'automatisation de l'analyse des données et les analystes de données adorent jouer avec les références de cellules.

Cependant, le problème survient lorsque vous tapez intentionnellement ou accidentellement l'adresse de la cellule où vous effectuez actuellement un calcul. Ou, lorsque vous créez de nombreuses formules dans la feuille de calcul, une cellule contenant une formule apparaît indirectement dans une autre formule.

Dans de telles situations et dans de nombreuses autres situations similaires, vous obtenez l'erreur de référence circulaire dans Excel. Lisez la suite pour savoir ce qu'est une référence circulaire, ses raisons, pourquoi l'éviter et, surtout, comment trouver des références circulaires dans Excel pour se débarrasser définitivement de ces anomalies.

Excel rencontre une erreur de référence circulaire lorsqu'il constate qu'une ou plusieurs fonctions de la feuille de calcul font référence à l'adresse de cellule où leurs fonctions ont été créées.

Cela peut vous sembler déroutant, alors réfléchissez à la gravité de l'application Excel. En bref, vous ne voulez jamais mettre l'adresse de la cellule dans une formule où vous la créez. Regardez l'exemple suivant pour une clarification visuelle :

Vous essayez de multiplier 10 de la cellule B1 et mettre le résultat dans la cellule B1 lui-même.

Maintenant, dans le scénario ci-dessus, si vous appuyez simplement sur D'ACCORD, Excel fermera l'erreur et affichera zéro comme résultat calculé. Ceci est juste un exemple simple.

Maintenant, imaginez qu'il y ait des centaines de formules dans une feuille de calcul de comptabilité, une feuille de calcul de planification financière, la paie des employés, une feuille de calcul de paiement des fournisseurs, etc.

Vous ferez confiance aux données erronées calculées par Excel en raison de l'erreur de référence circulaire et cela aura à son tour un impact sur votre entreprise ou votre prise de décision professionnelle.

De plus, le référencement circulaire peut réduire considérablement la vitesse des calculs dans l'application Excel. Dans les cas graves, l'erreur même peut également entraîner des performances extrêmement lentes sur le PC si elle n'est pas traitée immédiatement.

Cependant, si vous travaillez sur des formules Excel avancées où votre modèle de données nécessite des entrées intermédiaires de la référence circulaire pour continuer avec une fonction longue, vous devrez peut-être l'utiliser. En fait, Excel vous permet d'utiliser cette méthode de calcul lorsque cela est nécessaire.

Par exemple, dans les unités de production d'énergie, comme les éoliennes, vous devez calculer l'énergie de sortie recyclée à partir d'une fraction de l'énergie d'entrée. Alternativement, dans une usine chimique, les ingénieurs utilisent la tige de sortie comme entrée pour la même chaudière ou une autre chaudière ou cuve de traitement. De tels calculs d'ingénierie de haut niveau nécessitent en effet des références circulaires pour des calculs itératifs.

De toute évidence, la fenêtre contextuelle de référence circulaire n'est pas un message d'erreur. C'est un moyen pour Excel de vous informer qu'il a rencontré une référence circulaire dans une ou plusieurs formules et que cela peut influencer son algorithme de calcul et ses performances.

Pour éviter tout dommage causé par les références circulaires dans les formules Excel, vous devez savoir quand elles apparaissent et quand elles n'apparaissent pas. Retrouvez ci-dessous les instances :

• Dans une feuille de calcul ou un classeur ouvert, vous créez une formule et entrez la référence circulaire pour la première fois.
• Vous ou quelqu'un avez supprimé toutes les références circulaires du fichier Excel et l'avez enregistré. Maintenant, encore une fois, vous ou un autre utilisateur entrez une référence circulaire dans le même fichier.
• Vous avez fermé tous les classeurs ouverts. Maintenant, ouvrez un classeur différent, puis tapez une formule contenant un référencement circulaire.

• Vous venez d'ouvrir un classeur qui contenait auparavant une ou plusieurs références circulaires. Vous verrez l'avertissement.
• Une fois qu'Excel vous a montré le message d'avertissement de référence circulaire une fois et que vous avez cliqué D'ACCORD, il n'affichera plus la même fenêtre contextuelle dans la même session, même si vous continuez à créer des formules référencées plus circulaires.

Ainsi, vous devez rechercher attentivement les instances ci-dessus pour détecter les avertissements de référence circulaire et les résoudre si vous ne recherchez pas de calculs itératifs.

Si vous voulez apprendre à trouver des références circulaires dans Excel, vous devez comprendre leurs types. Parce que trouver et éliminer les références circulaires dans Excel n'est pas une tâche automatisée. Trouvez ci-dessous les types courants de cette erreur :

• Les références circulaires intentionnelles se produisent lorsque vous les utilisez intentionnellement à des fins de calcul itératif. Par exemple, vous devez saisir un horodatage sur une autre cellule lorsque quelqu'un entre une valeur dans une cellule référencée à l'aide de fonctions volatiles et logiques telles que IF, OR, SUMIF, etc.

• Référencement circulaire involontaire lorsque vous essayez de travailler plus rapidement sur une formule Excel et que vous vous référez par erreur à la cellule contenant la formule. Par exemple, vous devez appliquer le SOMME fonction de la cellule A1 pour A9 et récupère le résultat dans la cellule A10. Mais par erreur, vous tapez la formule comme celle-ci sur la cellule A10 :

=SOMME(A2:A10)

• Les références circulaires masquées sont celles qui n'affichent aucun message d'avertissement lorsque vous créez une formule ou ouvrez la feuille de calcul pour la première fois. Cela se produit parce que quelqu'un a autorisé le calcul itératif sur le fichier Excel.

• Les références circulaires indirectes sont également populaires et se produisent lorsque vous créez une nouvelle formule dans n'importe quelle cellule, par exemple, la cellule A1, et cette nouvelle formule utiliserait une autre fonction dans une autre cellule, comme cellule B1, qui utilise déjà la cellule A1 pour référence.

Maintenant que vous avez compris les bases des références circulaires dans Excel, découvrons ci-dessous comment trouver des références circulaires dans Excel :

Si la fonctionnalité de calcul itératif est désactivée, vous pouvez voir les notifications suivantes concernant les références circulaires dans la barre d'état Excel :

• Références circulaires: C3 (ou toute autre référence de cellule) lorsque vous êtes sur une feuille de calcul contenant une formule circulaire dans le classeur.

• Vous ne verrez que Références circulaires si vous êtes sur une feuille de calcul qui n'en contient pas mais que le classeur contient quelques-unes de ces formules circulaires.

C'est le meilleur moyen de vérifier les références circulaires dans un classeur ou une feuille de calcul. Voici les étapes que vous devez connaître :

• Ouvrez une feuille de calcul que vous soupçonnez de contenir des formules circulaires.
• Clique le Formules menu du ruban Excel.

• Dans le Audit de formule groupe de commandes, cliquez sur le Vérification des erreursla liste déroulante.
• Passez le curseur sur le Références circulaires option pour voir toutes les instances disponibles dans toutes les feuilles de calcul.
• Sélectionnez chaque instance et Excel identifiera automatiquement ces cellules.

Si vous ne trouvez pas la méthode de vérification des erreurs suffisamment efficace pour détecter un grand nombre de formules avec des références circulaires, vous pouvez essayer ces étapes pour utiliser la méthode de suivi des formules :

• Sélectionnez une cellule référencée ou la cellule contenant la formule.
• Allez à la Formules dans le menu du ruban Excel, puis cliquez sur Tracer des précédents.
• Excel mettra en surbrillance les cellules qui affectent le résultat de la formule sur la cellule actuelle avec des flèches.

• Vous pouvez également cliquer Suivre les personnes à charge pour découvrir les cellules impactées par la valeur de la référence de cellule sélectionnée.
• Si Excel n'affiche aucune erreur après avoir cliqué sur les commandes ci-dessus, il n'y a pas de références circulaires dans votre feuille de calcul.
• Répétez les étapes sur toutes les feuilles de calcul pour vérifier l'ensemble du classeur.

Cette méthode est particulièrement importante lorsque les fonctionnalités de calcul itératif sont actives sur la feuille de calcul Excel.

• Clique le Déposer onglet sur le ruban Excel, puis ouvrez Options Excel dans la barre latérale qui s'affiche.
• Sur le Options Excel boîte de dialogue, sélectionnez la Formules menu dans la barre latérale, puis décochez la case pour Activer le calcul itératif.

• Cliquez sur D'ACCORD pour enregistrer les modifications.
• Maintenant, le classeur ou la feuille de calcul vous montrera automatiquement l'avertissement de référence circulaire.
• S'il ne s'affiche pas instantanément, enregistrez le classeur, fermez-le et rouvrez-le pour voir l'avertissement.
• Une fois que vous voyez l'avertissement, vérifiez le Barre d'état Excel pour savoir quelle cellule se trouve dans la référence circulaire.

Le moyen le plus sûr de corriger une ou plusieurs erreurs de référence circulaire sur Excel consiste à modifier manuellement chaque formule qui la contient. De cette façon, vous pouvez résoudre les références circulaires accidentelles et indirectes dans Excel.

Un autre moyen simple consiste à supprimer la formule de la cellule actuelle et à la placer ailleurs dans la même feuille de calcul.

Comme Excel, Google Sheets n'autorise aucune référence circulaire dans une ou plusieurs formules si vous n'avez pas activé la fonction de calcul itératif. Par conséquent, Google Sheets affichera immédiatement le #REF! erreur sur la cellule qui contient une formule circulaire.

Par conséquent, vous pouvez simplement sélectionner toutes les cellules contenant le #REF! avertissement pour rechercher et résoudre des références circulaires sur Google Sheets.

Donc, maintenant vous connaissez tous les secrets pour trouver des références circulaires dans Excel. Utilisez la méthode que vous aimez et abandonnez toutes les références circulaires pour vous assurer de ne pas être confus par des données non fiables provenant d'un rapport Excel ou d'un tableau de bord mathématique.

Ensuite, le meilleurs modèles de calendrier Excel pour n'importe quelle année et le meilleurs modèles de budget Excel.