Comment supprimer des références circulaires dans Excel

Dans une feuille de calcul Microsoft Excel, vous pouvez avoir des formules se trouvant dans une cellule qui nécessitent les informations dans une autre cellule afin de calculer. Ces relations sont appelées références. Lorsqu'une cellule fait référence à elle-même ou à une autre cellule qui fait référence à la cellule d'origine, les formules ne peuvent pas se terminer car elles nécessitent un résultat qui dépend de la cellule d'origine. Cet événement est appelé une référence circulaire, et lorsque vous avez une grande feuille de calcul avec des milliers de formules, une référence circulaire peut empêcher la feuille entière de calculer correctement.

Comprendre les références Excel

Lorsque vous utilisez Microsoft Excel, il est courant qu'une formule dans une cellule référence une autre cellule afin d'utiliser les données de cette cellule dans un calcul. En général, une cellule est désignée à l'aide d'une lettre de ligne et d'un numéro de colonne, tel que A5 ou B10.

Lorsque les données de la cellule référencée sont mises à jour, les formules qui y font référence seront également mises à jour. Il n'est pas rare dans une feuille de calcul complexe d'avoir plusieurs couches de références, les résultats des calculs d'une formule étant introduits dans une autre.

Éliminer la référence circulaire dans Excel

Si une formule fait référence à sa propre cellule directement, ou via une chaîne de références à d'autres cellules, renvoie finalement à sa propre cellule, vous recevrez un avertissement de référence circulaire d'Excel. C'est parce que la formule ne peut pas être calculée de la manière habituelle, car lorsqu'elle est mise à jour, elle peut avoir un impact sur sa propre valeur.

C'est souvent le signe d'une faute de frappe ou d'une erreur de logique, et vous voudrez trouver ce qui crée la référence circulaire et l'éliminer.

  1. Ouvrez la feuille de calcul

  2. Ouvrez la feuille de calcul Microsoft Excel qui contient votre référence circulaire.

  3. Utilisez le menu Formules

  4. Cliquez sur l'onglet "Formules" dans le menu ruban en haut de la fenêtre Excel. Cliquez sur la petite flèche à côté du bouton "Vérification des erreurs" dans cette zone. Déplacez votre souris sur "Références circulaires" et la dernière référence circulaire saisie apparaîtra. Cliquez sur cette référence pour accéder à cette cellule de la feuille de calcul.

  5. Recherchez une référence circulaire

  6. Vérifiez la formule, qui apparaît dans la barre de formule juste au-dessus de la feuille de calcul, pour toute référence circulaire évidente, comme si la formule essaie de référencer la cellule dans laquelle elle se trouve actuellement. Sur les feuilles de calcul plus petites, vous pouvez même vérifier chaque référence de cellule à la main pour voyez si vous pouvez repérer la référence circulaire. Si vous ne pouvez pas le repérer, vous devrez tracer les références sur la feuille de calcul.

  7. Tracer les précédents avec Excel

  8. Cliquez sur le bouton "Tracer les précédents" dans la zone "Audit de formule" de l'onglet Formule du menu ruban. Cela créera une flèche sur la feuille de calcul à partir de chaque cellule mentionnée dans votre cellule spécifiée, vers la cellule spécifiée. Cliquez sur le bouton "Trace Dependents", qui créera des flèches de votre cellule spécifiée à chaque cellule qui fait référence à cette cellule spécifiée.

  9. Suivez la chaîne de référence

  10. Double-cliquez sur chaque ligne qui sort de la cellule avec votre référence circulaire pour accéder à la cellule à l'autre extrémité de la ligne. Si la cellule dans laquelle vous sautez contient à la fois le début d'une flèche, qui ressemble à un cercle, et la fin d'une flèche, qui ressemble à une tête de flèche, vous avez trouvé la référence circulaire. Modifiez cette cellule ou la référence de cette cellule dans votre cellule d'origine pour corriger la référence circulaire.

  11. Trouver plus de cercles au besoin

  12. Cliquez à nouveau sur la flèche déroulante à côté de "Vérification des erreurs" pour voir si une autre référence circulaire apparaît. Si tel est le cas, répétez ce processus pour l'effacer et continuez jusqu'à ce que plus aucune référence circulaire n'apparaisse.

Calcul itératif Excel

Dans certains cas, vous souhaiterez peut-être utiliser des références circulaires pour renvoyer le résultat d'un calcul dans le calcul. C'est ce qu'on appelle le calcul itératif dans Excel. C'est utile pour certaines formules et algorithmes où vous souhaitez itérer jusqu'à ce qu'un résultat particulier soit trouvé.

Pour activer le calcul itératif dans Excel, cliquez sur l'onglet «Fichier», puis sur «Options» et «Formules». Sous "Options de calcul", cochez la case "Activer le calcul itératif". Entrez le nombre maximal de fois que vous voulez qu'Excel recalcule la formule dans la zone «Nombre maximal d'itérations» et le montant maximal de modification que vous autoriserez avant qu'Excel doive recalculer à nouveau sous «Changement maximal».

Plus vous autorisez d'itérations et plus la quantité de changement que vous autorisez avant d'exiger un recalcul est réduite, plus les résultats que vous obtiendrez seront précis. Assurez-vous que vous souhaitez réellement utiliser une formule itérée plutôt que d'éliminer simplement les références circulaires avant de suivre cette voie.