Supprimer ou autoriser une référence circulaire

Vous avez entré une formule, mais elle ne fonctionne pas. À la place, vous avez un message indiquant une « référence circulaire ». Des millions de personnes ont ce problème. Il se produit parce que votre formule essaie de se calculer elle-même et que la fonctionnalité appelée calcul itératif est désactivée. Voici à quoi ça ressemble :

Formule entraînant une référence circulaire

La formule =D1+D2+D3 ne fonctionne pas parce qu’elle se trouve dans la cellule D3. Et elle essaie de se calculer elle-même. Pour résoudre ce problème, vous pouvez déplacer la formule vers une autre cellule (dans la barre de formule, appuyez sur Ctrl+X pour couper la formule, sélectionnez une autre cellule et appuyez sur Ctrl+V).

Une autre erreur courante consiste à utiliser une fonction qui fait référence à elle-même ; par exemple, la cellule F3 contient = SOMME(A3:F3). Voici un exemple :

Votre navigateur ne prend pas en charge la vidéo. Installez Microsoft Silverlight, Adobe Flash Player ou Internet Explorer 9.

Vous pouvez également essayer une des techniques suivantes :

  • Si vous venez d’entrer une formule, commencez par cette cellule et vérifiez si vous faites référence à la cellule elle-même. Par exemple, la cellule A3 peut contenir la formule =(A1+A2)/A3. Les formules comme =A1+1 (dans la cellule A1) peuvent aussi provoquer des erreurs de référence circulaire.

Pendant que vous y êtes, vérifiez les références indirectes. Elles se produisent lorsque vous insérez une formule dans la cellule A1 et qu’elle utilise une autre formule dans B1 qui à son tour fait référence à la cellule A1. Si vous êtes quelque peu déconcerté en lisant ceci, imaginez ce que ça doit être pour Excel.

  • Si vous ne trouvez pas l’erreur, cliquez sur l’onglet Formules, cliquez sur la flèche en regard de Vérification des erreurs, pointez sur Références circulaires, puis cliquez sur la première cellule répertoriée dans le sous-menu.

    Commande Références circulaires

  • Examinez la formule figurant dans la cellule. Si vous ne pouvez pas déterminer si la cellule est la cause de la référence circulaire, cliquez sur la cellule suivante dans le sous-menu Références circulaires.

  • Continuez à examiner et corriger les références circulaires dans le classeur en répétant tout ou partie des étapes 1 à 3 jusqu’à ce que la barre d’état n’affiche plus « Références circulaires ».

Conseils    

  • La barre d’état située dans le coin inférieur gauche indique Références circulaires et l’adresse de cellule d’une référence circulaire.

    S’il existe des références circulaires dans d’autres feuilles de calcul, mais pas dans la feuille de calcul active, la barre d’état affiche seulement « Références circulaires » sans adresses de cellules.

  • Vous pouvez vous déplacer entre les différentes cellules d’une référence circulaire en double-cliquant sur la flèche d’audit. La flèche d’audit indique la cellule qui affecte la valeur de la cellule actuellement sélectionnée. Pour afficher la flèche d’audit, cliquez sur Formules, puis cliquez sur Repérer les antécédents ou sur Repérer les dépendants.

    Repérer les antécédents

Découvrir le message d’avertissement de référence circulaire

Lorsqu’Excel détecte une référence circulaire pour la première fois, le programme affiche un message d’avertissement. Cliquez sur OK ou fermez la fenêtre de message.

Lorsque vous fermez le message, Excel affiche un zéro ou la dernière valeur calculée de la cellule. Si vous vous demandez « Pourquoi la dernière valeur calculée ? », la réponse est qu’effectivement, dans certains cas, une formule peut fonctionner correctement avant d’essayer de se calculer elle-même. Par exemple, une formule qui utilise la fonction SI peut fonctionner jusqu’à ce qu’un utilisateur entre un argument (les données dont la formule a besoin pour s’exécuter correctement) qui amène la formule à se calculer elle-même. Lorsque cela se produit, Excel conserve la valeur du dernier calcul réussi.

Si vous soupçonnez qu’une cellule comporte une référence circulaire n’affichant pas zéro, essayez ce qui suit :

  • Cliquez sur la formule dans la barre de formule, puis appuyez sur Entrée.

Important    Dans de nombreux cas, si vous créez des formules supplémentaires qui contiennent des références circulaires, Excel ne réaffichera pas le message d’avertissement. La liste suivante montre quelques scénarios, mais pas tous, dans lesquels s’affichera le message d’avertissement :

  • Vous créez la première instance d’une référence circulaire dans n’importe quel classeur ouvert.

  • Vous supprimez toutes les références circulaires dans tous les classeurs ouverts, puis créez une nouvelle référence circulaire.

  • Vous fermez tous les classeurs, en créez un nouveau, puis entrez une formule qui contient une référence circulaire.

  • Vous ouvrez un classeur qui contient une référence circulaire.

  • Lorsqu’aucun autre classeur n’est ouvert, vous ouvrez un classeur et créez une référence circulaire.

Découvrir le calcul itératif

Parfois, vous voudrez utiliser des références circulaires parce qu’elles entraînent l’itération de vos fonctions, c’est-à-dire leur répétition jusqu’à ce qu’une condition numérique spécifique soit remplie. Cela peut ralentir les performances de votre ordinateur. Par conséquent, les calculs itératifs sont généralement désactivés dans Excel.

Sauf si vous connaissez bien les calculs itératifs, vous ne voudrez probablement pas conserver les références circulaires. Si c’est le cas, vous pouvez activer les calculs itératifs, mais vous devez déterminer le nombre de fois que la formule doit être recalculée. Lorsque vous activez les calculs itératifs sans modifier les valeurs relatives au nombre maximum d’itérations ou de modifications, Excel cesse les calculs après 100 itérations, ou après que toutes les valeurs de la référence circulaire ont changé de moins de 0,001 entre les itérations, selon celui qui se produit en premier. Toutefois, vous pouvez contrôler le nombre maximum d’itérations et la quantité de modifications acceptables.

  1. Si vous utilisez Excel 2010 ou une version ultérieure, cliquez sur Fichier > Options > Formules. Si vous utilisez Excel pour Mac, cliquez sur le menu Excel, puis cliquez sur Préférences > Calcul.

    Si vous utilisez Excel 2007, cliquez sur le bouton Microsoft Office Image du bouton Office , cliquez sur Options Excel, puis cliquez sur la catégorie Formules.

  2. Dans la section Options de calcul, cochez la case Activer le calcul itératif. Sur Mac, cliquez sur Activer le calcul itératif.

  3. Pour définir le nombre maximal de fois où Excel recalcule, tapez le nombre d’itérations dans la zone Nb maximal d’itérations. Plus le nombre d’itérations est élevé, plus le temps nécessaire au calcul d’une feuille est important.

  4. Dans la zone Écart maximal, tapez la plus petite valeur requise pour que l’itération se poursuive. Il s’agit de la plus petite modification dans une valeur calculée. Plus le nombre est petit, plus le résultat est précis et plus le temps nécessaire au calcul d’une feuille par Excel est important.

Un calcul itératif peut engendrer les trois résultats suivants :

  • La solution converge, ce qui indique qu’un résultat final stable a été atteint. C’est la condition souhaitée.

  • La solution diverge, ce qui signifie que d’itération en itération, la différence augmente entre le résultat actuel et le précédent.

  • La solution bascule entre deux valeurs. Par exemple, après la première itération, le résultat est 1, il est de 10 après la prochaine, de 1 après la suivante, ainsi de suite.

Haut de la page

Vous avez besoin d’une aide supplémentaire ?

Vous pouvez toujours consulter un expert de la communauté technique Excel, obtenir une assistance dans la communauté Answers ou suggérer une nouvelle fonctionnalité ou une amélioration sur le forum Excel User Voice.

Voir aussi

Vue d’ensemble des formules dans Excel

Comment éviter les formules incorrectes

Rechercher et corriger les erreurs dans les formules

Raccourcis clavier et les touches de fonction Excel

Fonctions Excel (par ordre alphabétique)

Fonctions Excel (par catégorie)

Développez vos compétences
Découvrez des formations
Accédez aux nouvelles fonctionnalités en avant-première
Rejoignez le programme Office Insider

Ces informations vous ont-elles été utiles ?

Nous vous remercions pour vos commentaires.

Merci pour vos commentaires. Il serait vraisemblablement utile pour vous de contacter l’un de nos agents du support Office.

×