Comment trouver des références circulaires dans Microsoft Excel

Une référence circulaire dans Excel se produit lorsqu’une cellule contenant une formule dépend d’une certaine manière de son propre résultat, créant un cycle qui ne peut pas être résolu. Si vous souhaitez arrêter cette erreur, vous devez rechercher et supprimer ces liens pour permettre à Excel de terminer le calcul. Voici comment.

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

Pour expliquer plus en détail les références circulaires Excel, imaginons un scénario. Dans l’exemple de feuille de calcul Excel, il y a trois cellules avec des valeurs – A2, A3 et A4.

Chacune de ces cellules a une valeur créée avec un simple calcul de somme. A2 additionne les valeurs de A3 et A4 tandis que A3 est la somme de A2 et A4 et A4 est la somme de A2 et A3. Malheureusement, ces trois calculs ne peuvent coexister sans une référence circulaire.

A2 ne peut pas trouver la somme de A3 et A4 car A3 et A4 incluent tous deux un calcul impliquant A2.

En effet, une cellule contenant une formule ou un calcul ne peut pas se référer à elle-même (directement ou indirectement) sans provoquer une erreur de référence circulaire. Une boucle de calcul sans fin est créée et ne peut pas être traitée.

Cela signifie que vous ne voyez pas la valeur que vous recherchez – Excel se fige et ne peut pas continuer.

Pour la plupart des utilisateurs d’Excel, il s’agit d’un résultat inattendu car vous souhaitez que le calcul se termine. Pour résoudre ce problème, vous devez trouver les liens et les corriger.

Trouver des références circulaires dans Excel

Excel vous avertira si une référence circulaire pose un problème dans votre classeur. Si vous ne trouvez pas l’erreur vous-même, vous pouvez utiliser le menu Vérificateur d’erreurs pour trouver toutes les références circulaires dans votre livre.

Utilisation du menu de vérification des erreurs

Pour rechercher des références circulaires existantes, ouvrez le classeur et sélectionnez l’onglet « Formules » dans la barre de ruban. Ensuite, cliquez sur le bouton « Vérifier les erreurs ».

Dans le menu déroulant, passez la souris sur l’option Références circulaires pour afficher une liste de toutes les références cycliques de votre classeur. Pour accéder directement à la cellule qui la contient, sélectionnez la référence de cellule dans le menu.

Excel se déplacera pour mettre en surbrillance la cellule contenant votre référence circulaire, vous permettant de la résoudre manuellement.

Suivi d’une référence circulaire

Si vous traitez des formules complexes, il peut être difficile de déterminer la source d’une référence circulaire dans Excel en raison du grand nombre de prédécesseurs ou de cellules dépendantes. Les cellules précédentes sont des cellules qui modifient la valeur de la cellule sélectionnée, tandis que les cellules dépendantes sont des cellules qui nécessitent la valeur de la cellule sélectionnée pour effectuer le calcul.

Pour vous aider à les gérer, vous pouvez retracer la référence circulaire jusqu’à la source à l’aide des outils Use Case Tracker et Dependency Tracker. Pour tracer une référence circulaire dans Excel, ouvrez un classeur et sélectionnez la cellule contenant la référence circulaire.

Choisissez ensuite Formules > Afficher les formules. Cela fera passer votre classeur en mode Formule, vous permettant d’afficher toutes les formules utilisées sans sélectionner de cellules individuelles.

Ce n’est pas nécessaire, car Excel affichera visuellement la boucle circulaire des liens à l’aide de flèches, mais cela peut vous aider à identifier rapidement les liens des cellules problématiques dans un grand classeur.

Avec la vue de formule active et une cellule contenant une référence circulaire sélectionnée, cliquez sur le bouton Suivre les cas.

Une flèche apparaîtra pointant vers d’autres cellules – faites attention à la trace de la flèche pour déterminer la source du problème.

Lorsque vous avez terminé, cliquez sur « Tracer les personnes à charge » et vérifiez chaque cellule vers laquelle elle pointe.

Comme précédemment, faites attention aux cellules qui causent le problème. Excel identifie visuellement une boucle de support circulaire avec des flèches et des icônes rondes.

Pour chaque lien circulaire que vous avez, vous devrez peut-être répéter ces étapes pour obtenir une réponse complète afin de résoudre le problème.

Correction des références circulaires dans Excel

La seule façon de vraiment résoudre une référence circulaire dans Excel est de modifier le calcul qui l’a provoquée. Si la boucle est rompue, Excel peut terminer le calcul. La façon la plus rapide (et la meilleure) de le faire est de remplacer les formules contenant des références de cellules par les valeurs appropriées.

Voici un exemple. Supposons à nouveau que les trois cellules de votre classeur (A2, A3 et A4) contiennent des calculs simples qui se référencent. Pour éliminer la référence circulaire, vous devez remplacer le calcul en A2 et A4 par une valeur de valeur égale.

Les conditions de calcul en A3 seront remplies, permettant à Excel de calculer la valeur sans créer de boucle. Si vous ne souhaitez pas utiliser de valeur directe, vous devrez faire référence à une autre référence de cellule – une référence qui n’a pas de référence existante à la cellule sélectionnée.

Ceci est un exemple simple, mais le même principe s’applique aux classeurs Excel volumineux et complexes.