Comment trouver des références circulaires dans Excel

Lorsque vous travaillez avec Microsoft Excel , vous pouvez parfois rencontrer ce que l'on appelle une référence circulaire. C'est à ce moment qu'une formule dans une cellule se réfère à elle-même, provoquant une boucle sans fin dans le processus de calcul.

Pour trouver des références circulaires dans Excel, utilisez l'outil "Vérification des erreurs" sous l'onglet "Formules" pour identifier les cellules avec une dépendance circulaire.

Cet article vous montre comment trouver des références circulaires dans Excel et les corriger. Vous apprendrez également à les éviter lorsque vous travaillez avec des feuilles de calcul volumineuses ou complexes.

Table des matières

• Que sont les références circulaires
• La fenêtre de message d'erreur de référence circulaire
• Comment trouver des références circulaires dans Excel
• Comment trouver des références circulaires avec la barre d'état Excel
• Comment trouver des références circulaires cachées dans Excel
  • Comment utiliser l'outil dépendant de la trace
  • Outil Tracer les précédents
• 2 façons de supprimer les références circulaires dans Excel
  • 2. Utilisez des formules alternatives
• 5 conseils pour éviter les références circulaires
  • 1. Soyez prudent lors de la sélection des gammes
  • 2. Planifiez soigneusement vos formules
  • 3. Utilisez des formules alternatives
  • 4. Divisez les formules complexes en parties plus petites
  • 5. Résolvez les problèmes au fur et à mesure
• Comment activer le calcul itératif et pourquoi s'embêter ?

Que sont les références circulaires

Les références circulaires se produisent dans Excel lorsqu'un calcul de formule dans une cellule dépend de la valeur propre de la cellule. Cela crée une boucle et conduit à des résultats incorrects ou imprévisibles.

C'est plus facile à comprendre avec un exemple simple.

Supposons que vous ayez deux nombres dans les cellules A1 et B1. Vous souhaitez utiliser une formule dans la cellule A3 qui additionne les deux nombres. La formule dans la cellule A3 devrait ressembler à ceci :

=A1 + B1

Cependant, si vous faites une erreur et incluez C1 dans les calculs, vous avez maintenant une référence circulaire :

=A1 + B1 + C1

Cela peut être un problème évident à éviter lorsqu'il s'agit de deux cellules. Cependant, lorsque vous travaillez avec de grandes plages de cellules, les erreurs deviennent plus probables.

Heureusement, Excel est conçu pour gérer de telles situations en affichant un message d'avertissement lorsqu'une référence circulaire est détectée.

Cependant, dans certains cas, vous pouvez rencontrer une référence circulaire sans recevoir d'alerte. Par conséquent, il est important de savoir comment rechercher et résoudre ces problèmes manuellement.

La fenêtre de message d'erreur de référence circulaire

Par défaut, Excel surveille les références circulaires lorsque vous entrez des formules dans les cellules. Lorsque vous appuyez sur Entrée dans une cellule présentant un problème, une fenêtre contextuelle affiche le message d'erreur de référence circulaire.

Voici le texte explicatif :

Il existe une ou plusieurs références circulaires où une formule fait référence à sa propre cellule directement ou indirectement. Cela pourrait les amener à calculer de manière incorrecte. Essayez de supprimer ou de modifier ces références ou de déplacer les formules vers différentes cellules.

Lorsque vous voyez cette boîte de dialogue, vous devez cliquer sur "OK" et utiliser les méthodes de cet article pour résoudre le problème.

Cependant, vous n'avez pas besoin de corriger l'erreur immédiatement. Vous pouvez continuer à travailler avec la feuille de calcul dans l'intention de résoudre le problème ultérieurement.

Dans ce cas, vous devez savoir comment trouver des références circulaires dans Excel après avoir ignoré la boîte d'avertissement.

Comment trouver des références circulaires dans Excel

Excel dispose d'un outil de vérification des erreurs intégré qui détecte plusieurs types d'erreurs, y compris les références circulaires. Vous accédez à l'outil dans l'onglet Formules.

Cette image montre l'emplacement au sein du groupe Audit de formule.

Maintenant que vous savez comment trouver l'outil, voici les étapes détaillées pour l'utiliser :

1. Accédez à l'onglet Formules.

1. Cliquez sur la liste déroulante Vérification des erreurs dans le groupe Audit de formule.

2. Sélectionnez "Références circulaires" dans la liste déroulante.

3. Cliquez sur le lien pour déplacer votre curseur vers la première cellule avec un problème.

L'image ci-dessous montre que l'outil a trouvé une erreur circulaire dans la cellule G1. La cellule affichée est un lien hypertexte.

Lorsque vous cliquez sur le lien, votre curseur se déplace vers la cellule. Ceci est très utile avec un grand volume de données.

Une fois que vous avez résolu la première référence circulaire, vous pouvez revenir à l'outil de vérification des erreurs pour trouver la suivante sur la feuille.

Suivez les étapes des sections suivantes de cet article pour résoudre le problème et utilisez l'outil pour passer au problème suivant.

S'il n'y a plus de problèmes circulaires, l'élément de menu "Références circulaires" dans le menu déroulant sera grisé. Vous n'avez plus rien à faire.

Comment trouver des références circulaires avec la barre d'état Excel

Une autre façon de repérer une référence circulaire consiste à regarder la barre d'état au bas de la feuille de calcul Excel.

Lorsqu'il y a un ou plusieurs problèmes dans la feuille de calcul, la barre d'état affiche l'emplacement d'une référence de cellule de problème.

Par exemple, s'il y a une référence circulaire dans la cellule C1 et qu'il n'y a pas d'autres problèmes avec la feuille de calcul, la barre d'état indiquera : "Références circulaires : C1".

Cette image vous montre où voir l'affichage.

Comment trouver des références circulaires cachées dans Excel

Les références circulaires masquées dans Excel sont des références circulaires qui ne sont pas immédiatement détectées par les fonctionnalités d'erreur intégrées. En d'autres termes, Excel n'affiche pas l'avertissement standard.

Ils se produisent lorsqu'une cellule fait indirectement référence à une autre cellule, via une série de cellules intermédiaires, et se réfère finalement à la cellule d'origine.

Excel dispose de deux outils qui vous aident à suivre les relations et à étudier ces problèmes :

1. Outil Tracer les personnes à charge

2. Outil Tracer les précédents

Comment utiliser l'outil dépendant de la trace

Les utilisateurs expérimentés d'Excel tirent parti de l'outil Tracer les personnes à charge pour étudier ces problèmes et corriger les références circulaires. En choisissant une ou plusieurs cellules et en utilisant cet outil, Excel vous montrera les cellules dépendantes.

L'outil est disponible dans la section Audit des formules de l'onglet Formules.

Pour utiliser l'outil, procédez comme suit :

1. Cliquez sur la ou les cellules pour lesquelles vous souhaitez rechercher des personnes à charge.

2. Allez dans l'onglet 'Formules' du ruban Excel.

3. Cliquez sur le bouton 'Trace Dependents' dans le groupe 'Formula Auditing'.

4. Passez en revue la ou les flèches de suivi indiquant les dépendances.

Excel affiche des flèches de la valeur de la cellule active vers toutes les cellules qui en dépendent. La flèche bleue dans l'image ci-dessous montre que la cellule D6 dépend de la cellule B2.

Outil Tracer les précédents

Les précédents sont à l'opposé des personnes à charge. L'outil Trace Precedents vous permet de choisir une cellule avec une formule et de voir toutes les cellules dont elle dépend, c'est-à-dire les précédents.

L'outil est disponible dans la section Audit des formules de l'onglet Formules.

Pour utiliser l'outil, procédez comme suit :

1. Cliquez sur la cellule contenant la formule que vous souhaitez étudier.

2. Accédez à l'onglet Formule dans le ruban Excel.

3. Cliquez sur le bouton « Trace Precedents » dans le groupe « Formula Auditing ».

4. Passez en revue les flèches de trace affichées par Excel.

Les flèches bleues dans l'image ci-dessous vous montrent que la cellule D6 dépend de la plage de cellules A2: B5.

L'utilisation des deux outils vous aide à trouver des références circulaires Excel qui ne sont pas immédiatement apparentes. L'étape suivante consiste à résoudre le problème dans la cellule sélectionnée. Vous pouvez ensuite continuer à rechercher le même problème dans d'autres cellules.

2 façons de supprimer les références circulaires dans Excel

Il existe deux méthodes principales pour résoudre les références circulaires :

• briser la référence circulaire

• utiliser des formules alternatives

Expliquons les deux façons.

1. Briser la référence circulaire

Briser la référence circulaire dans Excel est un moyen efficace de résoudre le problème. Cela vous oblige à identifier les cellules impliquées dans la référence circulaire et à modifier leurs formules en conséquence.

Pour ce faire, suivez les étapes décrites précédemment pour trouver l'adresse de la cellule.

Vous devez ensuite examiner la formule dans la barre de formule. Recherchez une référence à la cellule elle-même.

Dans notre premier exemple de référence circulaire, la formule d'addition dans la cellule C1 comprenait une référence à la cellule elle-même :

=A1 + B1 + C1

Cela peut être corrigé en supprimant le dernier ajout. Une fois ce problème résolu, vous passez à la recherche d'autres références circulaires et vous les traitez.

2. Utilisez des formules alternatives

Certaines des causes les plus courantes de références circulaires sont lors de la sommation ou de la moyenne des cellules. L'erreur est d'inclure la cellule de résultats dans la formule.

Dans notre exemple précédent, la formule utilisait des opérateurs d'addition pour calculer la somme d'une plage de cellules. La méthode alternative consiste à utiliser la fonction Excel SOMME.

Pour corriger l'exemple, vous remplacez la formule par :

=SOMME(A1:B1)

De même, vous pouvez faire la moyenne d'une plage avec une formule comme =(A1+B1)/2. Cependant, la fonction MOYENNE est moins susceptible de conduire à une erreur.

=MOYENNE(A1:B1)

5 conseils pour éviter les références circulaires

Pour éviter les références circulaires dans Microsoft Excel, il est essentiel de bien comprendre comment vos formules interagissent les unes avec les autres dans votre feuille Excel.

En identifiant les relations entre les cellules et en sachant comment ces connexions fonctionnent ensemble, vous pouvez éviter de créer des formules circulaires.

Voici nos cinq meilleurs conseils pour vous aider à éviter les références circulaires.

1. Soyez prudent lors de la sélection des gammes

D'après mon expérience, l'une des causes les plus courantes de références circulaires est lorsque les utilisateurs sélectionnent rapidement des plages à inclure dans leurs formules.

Lorsque vous glissez verticalement et/ou horizontalement pour grouper des cellules, il est facile de sélectionner une cellule supplémentaire adjacente à la plage correcte. Si cette cellule est également la cellule de résultats, vous avez créé par inadvertance une référence circulaire.

Cette erreur est généralement commise lorsque vous travaillez avec de grands ensembles de données, tels que l'exécution de tests du chi carré. La solution est d'être prudent avec votre sélection de gamme.

2. Planifiez soigneusement vos formules

Avant de saisir des formules, réfléchissez à la logique derrière vos calculs et à la manière dont ils doivent circuler dans les cellules.

Vous pouvez tracer les calculs à l'avance à l'aide d'outils de création de diagrammes ou d'un tableau blanc.

Cela peut vous aider à identifier les boucles possibles qui pourraient conduire à des références circulaires.

3. Utilisez des formules alternatives

Certaines fonctions, telles que INDIRECT ou OFFSET , peuvent créer par inadvertance des références circulaires lorsqu'elles sont utilisées de manière incorrecte.

Envisagez d'utiliser des fonctions alternatives qui offrent des fonctionnalités similaires sans risque de références circulaires.

4. Divisez les formules complexes en parties plus petites

Au lieu de créer une seule formule complexe avec plusieurs fonctions imbriquées, envisagez de diviser vos calculs en parties plus petites et plus faciles à gérer.

Cela facilite non seulement la compréhension et la maintenance de votre classeur, mais réduit également les risques d'introduction d'une référence circulaire.

5. Résolvez les problèmes au fur et à mesure

Lorsque vous travaillez avec des classeurs volumineux ou compliqués, il est également essentiel de surveiller les références circulaires. Au lieu d'ignorer les problèmes jusqu'à ce qu'un grand ensemble de données et de formules ait été assemblé, il est préférable de corriger chaque erreur au fur et à mesure qu'elle survient.

Vous pouvez choisir d'ignorer un message d'avertissement de référence circulaire pendant un court instant. Cependant, vous devez utiliser la fonction de vérification des erreurs pour rattraper les références circulaires existantes avant trop longtemps.

Cela les rend plus faciles à corriger et évite leurs effets sur votre classeur.

Comment activer le calcul itératif et pourquoi s'embêter ?

Vous souhaitez généralement éviter les références circulaires dans votre feuille de calcul. Cependant, il existe certains scénarios où ils sont nécessaires.

Par exemple, certains calculs techniques ou scientifiques doivent utiliser le résultat d'un calcul précédent. C'est ce qu'on appelle une solution itérative.

Chaque boucle affine les résultats d'un calcul jusqu'à ce qu'un seuil ou une cible spécifique soit atteint.

Dans ces cas, il est essentiel d'activer les calculs itératifs dans les paramètres Excel pour autoriser les références circulaires prévues. Cela permet au logiciel de répéter les calculs un nombre de fois spécifié, convergeant vers une solution.

Suivez ces étapes pour faire fonctionner une référence circulaire dans votre fichier Excel :

1. Cliquez sur l'onglet Fichier.

2. Sélectionnez les options.

3. Choisissez la catégorie Formules.

4. Dans la section "Options de calcul", cochez la case "Activer le calcul itératif".

5. Modifiez le nombre dans la zone d'itérations maximales ou laissez la valeur par défaut de 100.

6. Utilisez la zone de modification maximale pour réduire les itérations selon vos besoins.