Comment rechercher et supprimer des références circulaires dans Excel

Comment rechercher et supprimer des références circulaires dans Excel

Les références circulaires peuvent être fastidieuses à tracer et à supprimer par vous-même. Apprenez à utiliser les outils Excel pour vous en débarrasser.





Que sont les références circulaires dans Excel ?

Une référence circulaire est un terme utilisé pour une formule qui visite une cellule plus d'une fois dans sa ligne de calcul. Pour cette raison, les calculs mettront beaucoup de temps à calculer les nombres et il est fort probable que les calculs renvoient une mauvaise réponse la plupart du temps.



Cependant, les références circulaires ne sont pas toujours préjudiciables. Dans certains cas, ils sont très utiles. Malgré cela, les références circulaires présentent un risque de devenir incontrôlables et de contribuer à des problèmes qui ne sont pas immédiatement apparents.





Si vous avez une référence circulaire dans votre feuille de calcul mais que vous n'avez jamais eu l'intention d'en avoir une, vous devriez vous en débarrasser.

Comprendre les références circulaires

Pour supprimer la formule, vous devez d'abord la comprendre. Une référence peut être circulaire de deux manières : directement et indirectement.



Références circulaires directes

Une référence circulaire directe se produit lorsqu'une cellule se réfère directement à elle-même. Faisons une référence circulaire directe :

  1. Dans les cellules A1 , A2, et A3 , entrez les nombres 100, 200 et 300.
  2. Sélectionnez la cellule A4 , et dans la barre de formule, saisissez la formule ci-dessous : |_+_| Il s'agit d'une fonction SUM simple qui additionnera les cellules A1, A2, A3 et A4.
  3. Une fois que vous avez tapé la formule, appuyez sur Entrer . Un message d'erreur apparaîtra.
  4. Dans le message d'erreur qui s'affiche, Excel vous avertit qu'une référence circulaire existe dans cette feuille de calcul. Cliquez sur d'accord .

Cette formule crée une référence circulaire dans laquelle A4 cherche à additionner un ensemble de valeurs avec la valeur de A4. Cependant, quelle est la valeur de A4 ? Vous ne le saurez jamais, et Excel non plus.



À la suite de cette référence circulaire, Excel renverra 0 pour la formule. Vous ne connaissez peut-être pas la réponse à cette formule, mais ce n'est pas 0.

En rapport: Fonctions de recherche Excel pour rechercher efficacement des feuilles de calcul



Références circulaires indirectes

Une référence circulaire indirecte se produit lorsqu'une cellule se réfère à elle-même à travers d'autres cellules.

  1. En cellule A1 , saisissez la formule ci-dessous : |_+_| Cela définira la valeur de A1 sur celle de D1. Quel qu'il soit.
  2. Entrez les nombres 100 et 200 dans les cellules B1 et C1 , respectivement.
  3. Sélectionnez la cellule D1 , et dans la barre de formule, saisissez la formule ci-dessous : |_+_| Il s'agit d'une autre fonction SUM qui additionnera les valeurs des cellules A1, B1 et C1 et renverra le résultat dans D1.
  4. presse Entrer . Un message d'erreur apparaîtra.
  5. Dans le message d'erreur, cliquez sur d'accord .

Comme précédemment, Excel renvoie 0. Cependant, contrairement à l'exemple précédent, la cellule D4 ne fait aucune référence directe à elle-même. Au lieu de cela, il additionne A1 avec un ensemble de valeurs. Qu'est-ce qu'A1 ? C'est D4. Alors, qu'est-ce que le D4 ? C'est A1 avec un tableau de valeurs.

Une référence circulaire a été formée.

montre galaxie active 2 vs montre galaxie 3

En rapport: Comment copier des formules dans Microsoft Excel

Rechercher et supprimer des références circulaires dans Excel

Dans des scénarios plus complexes, où de nombreuses cellules se réfèrent les unes aux autres, trouver des références circulaires peut ne pas être aussi simple que de regarder la formule seule. Excel dispose d'outils pour vous aider à trouver des références circulaires.

Avant de trouver des références circulaires, ajoutons-en à votre feuille de calcul :

  1. Dans les cellules A1 , A2 , et A3 , entrez 1, 2 et 3.
  2. Sélectionnez la cellule A4 .
  3. Dans la barre de formule, entrez la formule ci-dessous et appuyez sur Entrer : |_+_| Comme dans le premier exemple, il s'agit également d'une référence circulaire directe où A4 se réfère à lui-même.
  4. Dans le message d'erreur, cliquez sur d'accord .

Excel renvoie 0 car la formule au format A4 est une référence circulaire directe. Créons-en une indirecte également :

  1. En cellule C1 , taper vingt .
  2. Entrez la formule ci-dessous pour la cellule E4 : =SUM(A1+A2+A3+A4)
  3. Entrez la formule ci-dessous pour la cellule G1 : =D1
  4. Enfin, sélectionnez la cellule C1 dans la barre de formule et remplacez vingt avec la ligne ci-dessous : |_+_|

Comme prévu, Excel renvoie 0 car ces cellules se réfèrent les unes aux autres dans un cercle. Cependant, comme ils ne se réfèrent pas directement à eux-mêmes, ce sont des références circulaires indirectes.

En rapport: Comment compter les valeurs uniques dans Excel

Voyons comment vous pouvez utiliser les outils Excel pour rechercher des références circulaires dans votre feuille de calcul. Gardez à l'esprit que votre feuille de calcul contient quatre références circulaires, à savoir A4 (direct), C1, E4 et G1 (indirect).

  1. Dans le menu du ruban, accédez à la Formule languette.
  2. Dans le Audit de formule section, cliquez sur le bouton fléché à côté de Vérification des erreurs .
  3. Dans le menu, passez votre souris sur Références circulaires . Cela ouvrira une liste de références circulaires.
  4. Cliquez sur la cellule dans la liste. Cela vous amènera à la cellule avec une référence circulaire.
  5. Vous remarquerez qu'Excel n'affiche qu'une seule des quatre références. En effet, Excel gère ces chaînes une à la fois.
  6. Une fois que vous avez fixé la première référence circulaire, vous pouvez passer à la suivante.
  7. A l'étape suivante, fixer la référence circulaire .
  8. Revenir à Vérification des erreurs et cliquez Références circulaires .
  9. Dans la liste, sélectionnez la référence circulaire suivante et procédez à sa correction.

Suivi des relations cellulaires

Le suivi des relations entre les cellules peut devenir difficile dans les feuilles de calcul où trop de cellules se réfèrent les unes aux autres. C'est là qu'Excel Audit de formule fonctionnalité peut être utile. Il peut vous aider à obtenir une meilleure visualisation de vos formules.

  1. Sélectionnez une cellule contenant une formule.
  2. Depuis le ruban, allez au Formule languette.
  3. Dans le Audit de formule rubrique, cliquez sur Tracer les précédents .
  4. Toutes les cellules qui affectent les valeurs de la cellule sélectionnée seront connectées les unes aux autres avec des flèches bleues.
  5. Ensuite, dans la même section, cliquez sur Tracer les dépendants . (Cette méthode dessinera des flèches bleues de la cellule sélectionnée vers chacune des cellules qu'elle affecte.)
  6. Enfin, pour voir les formules au lieu des valeurs pour toutes les cellules, cliquez sur Afficher les formules .

Briser le cercle

Excel utilise des références circulaires lorsqu'il essaie de calculer le résultat d'une cellule qui a été visitée plusieurs fois au cours du cycle de calcul. Donc, cela ne vous aidera pas tout le temps.

Pourtant, de nombreux modèles financiers contiennent des références circulaires. Mais ils doivent être évités autant que possible. Maintenant que vous savez comment gérer les références circulaires, le moment est peut-être venu d'apprendre des fonctions Excel plus avancées.

Partager Partager Tweeter E-mail Comment utiliser les instructions IF dans Microsoft Excel

Que vous soyez un expert chevronné ou un débutant en tableur, vous voudrez consulter ce guide des instructions IF dans Excel.

Lire la suite Rubriques connexes
  • Productivité
  • Tableur
  • Microsoft Excel
  • Microsoft Office 365
  • Conseils Microsoft Office
A propos de l'auteur Amir M. Intelligence(39 articles publiés)

Amir est un étudiant en pharmacie passionné par la technologie et les jeux. Il aime jouer de la musique, conduire des voitures et écrire des mots.

Plus de Amir M. Bohlooli

Abonnez-vous à notre newsletter

Rejoignez notre newsletter pour des conseils techniques, des critiques, des ebooks gratuits et des offres exclusives !

Cliquez ici pour vous abonner