Comment identifier les références circulaires dans Excel

Cela survient généralement au moment le plus inopportun : une feuille de calcul qui fonctionnait parfaitement hier affiche soudainement un avertissement de référence circulaire juste avant la date limite.

La bonne nouvelle ? Ces références circulaires sont plus faciles à corriger que vous ne le pensez.

Dans ce guide, je vais vous expliquer :

• Comment identifier les références circulaires dans Excel
• Comprenez les différentes façons dont ils se manifestent.
• Veuillez les corriger en utilisant des méthodes éprouvées.

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

Une référence circulaire dans Excel se produit lorsqu'une formule renvoie à sa propre cellule (directement ou indirectement) et crée une boucle de calcul qu'Excel ne peut pas résoudre. Par exemple, si la cellule A1 contient =A1+10, Excel continuera indéfiniment à essayer de calculer le résultat.

Même les boucles indirectes peuvent causer des problèmes. Si A1 dépend de B1, B1 dépend de C1 et C1 dépend de A1, Excel est bloqué. Le calcul du fichier peut prendre beaucoup de temps, afficher des valeurs incorrectes ou cesser complètement de se mettre à jour.

Certaines références circulaires sont de simples erreurs de formule, tandis que d'autres sont des choix de conception intentionnels utilisés dans des modèles avancés. Je reviendrai sur cette distinction ultérieurement, mais examinons d'abord les différents types.

Références circulaires directes

Une référence circulaire directe se produit lorsqu'une formule inclut sa propre adresse de cellule. Supposons que vous ayez deux valeurs dans les cellules A2 et B2. Si, dans la cellule C2, nous saisissons A2 + B2 + C2, C2 fait référence à lui-même directement. 

Lorsque vous saisissez la formule et appuyez sur Entrée, une fenêtre contextuelle de référence circulaire apparaît. Et lorsque vous cliquez sur OK, la formule renvoie un résultat inattendu.

Référence circulaire directe. Image fournie par l'auteur.

Cela renvoie 0 car lorsque nous additionnons A2 et B2, le résultat attendu est 6, mais « C2 » continue de s'ajouter. Excel ne peut pas calculer C2 sans connaître la valeur de C2. Cela crée une boucle immédiate qui ne se résout jamais.

Remarque: Ces références circulaires sont les plus faciles à identifier, car l'auto-référence est directement visible dans la syntaxe de la formule. En réalité, Excel les détecte et les signale dès que la formule est saisie.

Références circulaires indirectes

Une référence circulaire indirecte se produit lorsque plusieurs cellules forment une chaîne de dépendance qui revient au point de départ.

Par exemple :

• F2 références H4

• H4 références E4

• E4 références F2

Référence circulaire indirecte. Image fournie par l'auteur.

Aucune formule ne semble incorrecte en soi. Le problème ne se présente que lorsque Excel suit l'ensemble de la chaîne et constate que le calcul revient à son point de départ.

Ces boucles sont plus difficiles à détecter, car elles peuvent s'étendre sur plusieurs cellules et, dans les classeurs complexes, même sur plusieurs feuilles de calcul, avant qu'Excel n'identifie la dépendance circulaire.

Références circulaires cachées

Une référence circulaire cachée est une référence circulaire dont la boucle de dépendance n'est pas évidente à partir de la syntaxe de la formule. Ces derniers proviennent souvent de :

• Plages nommées

• Fonctions INDIRECT() ou OFFSET()

• Logique conditionnelle complexe

Par exemple, une plage nommée QuarterlyTotal comprend les cellules C3 à C5, qui contiennent les valeurs des ventes trimestrielles.

Dans la cellule C5, la barre de formule affiche :

=QuarterlyTotal*0.1

À première vue, cela semble correct. La formule ne fait pas directement référence à C5. Cependant, étant donné que QuarterlyTotal inclut C5, Excel doit calculer C5 dans le cadre du total utilisé pour calculer C5.

Cela crée une boucle cachée :

• Excel tente de calculer QuarterlyTotal

• QuarterlyTotal comprend C5

• C5 dépend de QuarterlyTotal

La référence est circulaire, même si la syntaxe de la formule ne l'indique pas explicitement.

C'est ce qui rend les références circulaires cachées si difficiles à résoudre. Les formules semblent correctes, le classeur peut fonctionner normalement pendant un certain temps, et la référence circulaire n'apparaît que lorsque Excel évalue la plage nommée.

Référence circulaire cachée. Image fournie par l'auteur.

La logique conditionnelle peut rendre cela encore plus difficile à détecter. Pourquoi ? En effet, les instructions IF() imbriquées peuvent créer une référence circulaire uniquement dans des conditions spécifiques. Par conséquent, le fichier fonctionne jusqu'à ce que certaines valeurs soient modifiées.

Les références circulaires cachées sont les plus difficiles à corriger, car la boucle de dépendance est dissimulée par l'abstraction plutôt que par des références de cellules visibles.

Références circulaires intentionnelles et accidentelles

Certaines références circulaires sont intentionnelles. D'autres sont des erreurs qu'il est nécessaire d'éliminer.

Références circulaires intentionnelles (délibérées)

Une référence circulaire intentionnelle est créée délibérément pour répondre à des besoins de modélisation spécifiques où les valeurs dépendent les unes des autres.

Prenons l'exemple de la modélisation financière : les charges d'intérêts dépendent du solde de la dette en cours, tandis que le solde de la dette varie en fonction des flux de trésorerie, qui incluent les charges d'intérêts. Cela crée une boucle de rétroaction qui reflète la logique commerciale du monde réel.

Le modèle commence par une structure simple :

• Le solde de la dette représente le montant d'ouverture.
• Les frais d'intérêt sont calculés en pourcentage de la dette.
• La dette actualisée comprend à la fois le solde initial et les intérêts.

Références circulaires intentionnelles. Image fournie par l'auteur.

Lorsque les intérêts sont calculés sur la base de la dette et que la dette actualisée inclut ces intérêts, le calcul revient naturellement à son point de départ. Excel signale cela comme une référence circulaire et renvoie zéro par défaut, car il ne peut pas résoudre la dépendance en un seul passage.

Pour éviter cela, veuillez activer le calcul itératif de la manière suivante :

1. Veuillez vous rendre sur Fichiers > Options > Formules
2. Dans l'option de calcul section Options de calcul , veuillez cocher la case Activer le calcul itératif .

Veuillez activer l'option de calcul itératif. Image fournie par l'auteur.

Une fois le calcul itératif activé, Excel recalcule de manière répétée les intérêts et les valeurs actualisées de la dette. À chaque passage, les chiffres s'ajustent légèrement jusqu'à ce qu'ils se stabilisent et cessent de changer. 

Le résultat final représente une solution convergente plutôt qu'un calcul en une seule étape. Cela fonctionne car la référence circulaire est intentionnelle. Les intérêts ont une incidence sur la dette, et la dette a une incidence sur les intérêts. Chaque valeur influence les autres, ce qui implique que des recalculs répétés sont à prévoir et nécessaires.

Calcul itératif. Image fournie par l'auteur.

Remarque : Le calcul itératif ne garantit pas à lui seul un résultat significatif. Cela reproduit la logique que vous avez intégrée au modèle, c'est pourquoi les références circulaires intentionnelles nécessitent une surveillance et une validation minutieuses.

Références circulaires accidentelles

Une référence circulaire accidentelle peut résulter d'erreurs de formule, d'erreurs de copier-coller ou de modifications involontaires. Supposons que vous saisissiez les valeurs 2, 4 et 6 dans les cellules A1:A3.

Dans A4, vous souhaitez obtenir un total, vous saisissez donc :

=SUM(A1:A4)

La cellule A4 est désormais incluse dans le calcul.

Lorsque vous appuyez sur Entrée, Excel affiche un avertissement de référence circulaire. Après avoir cliqué sur OK, Excel affiche le message suivant : 0.

Référence circulaire accidentelle. Image fournie par l'auteur.

Cette référence circulaire ne représente pas une logique réelle. Il existe parce que la plage de formules est incorrecte.

Comment résoudre les références circulaires dans Excel

Une fois qu'Excel a détecté une boucle, il est nécessaire de localiser précisément son point de départ et de déterminer comment la résoudre. 

Examinons quelques méthodes pour corriger les références circulaires, selon qu'elles sont accidentelles ou intentionnelles : 

Détection rapide pour localiser le problème

Excel intègre plusieurs outils de détection permettant d'identifier rapidement les références circulaires, chacun présentant ses propres avantages et limites. Examinons ces options. 

Notifications dans la barre d'état

Le moyen le plus rapide de détecter une référence circulaire consiste à utiliser la barre d'état située dans le coin inférieur gauche de la fenêtre Excel.

Lorsqu'il existe une référence circulaire, Excel affiche un message tel que « Références circulaires : ». G4. Cela vous indique la cellule la plus récemment détectée impliquée dans une boucle.

Afficher la référence circulaire dans la barre d'état. Image fournie par l'auteur.

Cependant, la barre d'état n'affiche qu'une seule référence circulaire à la fois. Si plusieurs boucles existent, Excel les affiche une par une.

Voici comment résoudre ce problème : 

1. Veuillez corriger la cellule affichée dans la barre d'état.
2. Veuillez vérifier à nouveau la barre d'état.
3. Veuillez répéter jusqu'à ce que le message disparaisse.

Conseil : Veuillez noter l'adresse de chaque cellule, car la correction d'une boucle révèle souvent l'existence d'une autre.

Messages d'avertissement initiaux

Excel affiche également un message d'avertissement lorsque :

• Vous créez une référence circulaire, ou
• Vous ouvrez un classeur qui contient déjà un

L'avertissement indique généralement qu'une ou plusieurs formules font référence à elles-mêmes et peuvent être calculées de manière incorrecte.

Ces avertissements peuvent cesser d'apparaître si :

• Vous avez précédemment ignoré le message en cliquant sur OK.
• Le calcul itératif est activé.
• Le mode de calcul est défini sur Manuel (Formules > Options de calcul > Manuel).

Si les résultats semblent incorrects et qu'aucun avertissement n'apparaît, veuillez forcer un nouveau calcul comme suit :

• Veuillez appuyer sur la touche F9 pour recalculer la feuille de calcul active.
• Veuillez appuyer sur Ctrl + Alt + F9 pour forcer le recalcul de toutes les feuilles de calcul.

Conseil: La fermeture et la réouverture du classeur peuvent également entraîner l'affichage à nouveau de l'avertissement par Excel.

Outil de vérification des erreurs

Pour visualiser plusieurs références circulaires simultanément, veuillez utiliser l'outil de vérification des erreurs d'Excel : Veuillez vous rendre dans Formules > Vérification des erreurs > Références circulaires.

Ce menu répertorie toutes les références circulaires détectées et vous permet d'accéder directement à chaque cellule, ce qui facilite le diagnostic des classeurs complexes.

Si le sous-menu Références circulaires apparaît vide mais qu'Excel continue de fonctionner de manière incorrecte, veuillez appuyer sur F9 pour forcer le recalcul et actualiser la liste.

Un outil de vérification des erreurs permettant de détecter les références circulaires. Image fournie par l'auteur.

Remarque: Dans Excel 2007 et les versions ultérieures, cette fonctionnalité est accessible dans le ruban. Les versions antérieures proposent des options similaires via la barre de menu.

Outils de traçage visuel

Une fois que vous avez identifié une cellule problématique, veuillez utiliser des outils de traçage visuel pour observer comment la référence circulaire se forme.

Ces outils tracent des flèches entre les cellules et rendent les dépendances visibles, plutôt que cachées dans les formules. Les deux sont disponibles dans le groupe Audit des formules de l'onglet Formules:

• La fonction « Trace Precedents » (Suivre les antécédents) indique quelles cellules alimentent la formule sélectionnée. Excel trace des flèches depuis les cellules de saisie vers la cellule contenant la formule. Son raccourci clavier est Alt + M, P.
• La fonction « Trace Dependents » (Suivre les dépendances) indique quelles cellules utilisent la valeur de la cellule sélectionnée. Excel trace des flèches entre la cellule sélectionnée et toutes les formules qui y font référence. Son raccourci clavier est Alt + M, D.

Si le traçage des antécédents et des dépendances ramène finalement à la cellule de départ, les flèches forment une boucle et confirment la référence circulaire.

Trace des précédents et trace des dépendances. Image fournie par l'auteur.

Après avoir corrigé la formule, les flèches ne forment plus de boucle et la référence circulaire disparaît. Vous pouvez supprimer les flèches en utilisant l'option Supprimer les flèches dans le même menu.

Restructuration de la formule et calculs alternatifs

La plupart des références circulaires surviennent en raison d'un ordre incorrect des calculs, et non d'un dysfonctionnement d'Excel. Pour ce type de références circulaires accidentelles, la restructuration des formules constitue la solution la plus efficace. 

Voici quelques méthodes pour résoudre ce problème :  

Réorganiser les formules afin d'éliminer les dépendances internes.

Les références circulaires apparaissent souvent lorsqu'une formule tente de calculer une valeur en utilisant son propre résultat.

Par exemple, une formule telle que =A2+1 dans la cellule A2 crée une boucle immédiate, car la cellule dépend d'elle-même. Pour résoudre ce problème, veuillez déplacer la valeur requise dans une cellule distincte et référencer cette valeur à la place.

Une fois la dépendance supprimée, Excel peut calculer la formule normalement.

Veuillez utiliser des colonnes auxiliaires pour les calculs intermédiaires.

Les références circulaires peuvent également se produire lorsqu'une seule formule tente de traiter plusieurs étapes à la fois.

Par exemple, si deux cellules dépendent l'une de l'autre pour calculer une valeur partagée, Excel ne dispose d'aucun point de départ clair. Pour résoudre ce problème, veuillez introduire une cellule d'aide qui effectue d'abord le calcul intermédiaire.

En calculant les valeurs partagées dans une cellule d'aide distincte, Excel peut suivre un ordre clair :

1. La cellule auxiliaire effectue le premier calcul.
2. Les formules dépendantes calculent ensuite

Cette approche simplifie les formules et élimine les dépendances circulaires.

Résumés distincts des calculs

Les totaux et les formules de synthèse ne doivent pas être mélangés dans les mêmes cellules dont ils dépendent. Lorsqu'un total génère des chiffres à partir d'une plage qui l'inclut lui-même, Excel crée une référence circulaire. 

Par exemple, si A2 contient un montant de base de 1 000 et B2 contient un taux de 10 %, C2 affiche le montant total et D2 calcule les frais.

Si C2 utilise =A2+D2 et D2 utilise =C2*$B$2, une référence circulaire apparaît car le total dépend des frais, et les frais dépendent du total. 

Excel ne peut calculer aucun des deux en premier lieu.

Pour remédier à cela, les frais devraient être calculés à partir de la valeur de base, et non à partir du total. Veuillez donc remplacer D2 par =A2*$B$2 et conserver C2 comme =A2+D2.

Veuillez utiliser des cellules distinctes afin d'éviter les références circulaires. Image fournie par l'auteur.

Excel peut désormais calculer les frais en premier lieu, puis les ajouter à la valeur de base. La référence circulaire disparaît car chaque formule a un point de départ clair.

Activation du calcul itératif pour une logique circulaire intentionnelle

Le calcul itératif ne doit être utilisé que lorsque les références circulaires sont des éléments de conception intentionnels et non des erreurs accidentelles. Cette méthode convient aux modèles financiers et autres scénarios où il existe des boucles de rétroaction légitimes, et où la logique circulaire reflète le comportement réel.

Pour activer le calcul itératif dans Excel pour Windows :

1. Veuillez vous rendre dans Fichier > Options > Formules.
2. Sous Options de calcul, veuillez cocher Activer le calcul itératif.
3. Veuillez cliquer sur OK.

Une fois cette option activée, Excel recalcule de manière répétée les formules impliquées dans les références circulaires jusqu'à ce qu'elles se stabilisent ou atteignent une condition d'arrêt.

Paramètres d'itération clés

Deux paramètres déterminent le comportement du calcul itératif :

• Le nombre maximal d'itérations détermine le nombre de fois qu'Excel recalcule les formules avant de s'arrêter. Une valeur de 100 itérations convient à la plupart des modèles commerciaux et financiers.
• La modification maximale définit la différence minimale entre les résultats de calcul avant qu'Excel cesse de recalculer. Une valeur de 0,001 est un point de départ courant qui équilibre précision et performance.

L'augmentation de l'un ou l'autre de ces paramètres peut améliorer la précision, mais peut ralentir le recalcul dans les classeurs volumineux.

Quand utiliser cette méthode

Veuillez activer le calcul itératif uniquement lorsque :

• La référence circulaire représente une logique métier valide.
• Le modèle inclut des boucles de rétroaction légitimes (par exemple, l'intérêt dépend de la dette, tandis que la dette varie en fonction de l'intérêt).
• Vous vous attendez à ce que les valeurs convergent plutôt qu'elles n'oscillent indéfiniment.

Cette méthode ne permet pas de corriger les références circulaires accidentelles causées par des erreurs de formule.

Avertissements importants à prendre en considération

Avant d'activer le calcul itératif, veuillez prendre en considération les risques suivants :

• Les références circulaires accidentelles peuvent ne plus déclencher d'alertes, ce qui rend les erreurs plus difficiles à détecter.
• Les modèles volumineux ou complexes peuvent nécessiter un temps de recalcul plus long.
• Si les valeurs fluctuent au lieu de se stabiliser, les résultats peuvent ne pas être fiables.

En raison de ces risques, les calculs itératifs doivent toujours être documentés de manière explicite.

Mise en œuvre d'un disjoncteur pour des modèles complexes

Un disjoncteur est une technique avancée qui vous permet de contrôler quand la logique circulaire est active. Au lieu de supprimer une référence circulaire, vous pouvez la suspendre ou l'autoriser de manière intentionnelle en utilisant une valeur de contrôle dans la formule.

Cette approche convient particulièrement aux modélisateurs financiers et aux utilisateurs expérimentés qui ont besoin d'une logique circulaire dans certains scénarios, mais pas systématiquement.

Un disjoncteur utilise généralement :

• Une cellule de contrôle (souvent définie sur 0 ou 1)

• Une instruction IF qui alterne entre la logique circulaire et non circulaire

Le modèle général se présente comme suit :

=IF($A$1=1, circular_logic, non_circular_alternative)

• Lorsque la cellule de contrôle correspond à 0, la formule évite la référence circulaire.
• Lorsque la cellule de contrôle correspond à l'adresse 1, la logique circulaire peut s'exécuter.

Supposons que vous saisissiez les valeurs suivantes : 

• A1: Disjoncteur (0 ou 1)

• A2: Solde initial = 1000

• B2: Intérêt

• C2: Solde actualisé

Dans B2, veuillez utiliser =IF($A$1=1, C2*10%, A2*10%) et dans C2, veuillez saisir =A2+B2

À présent :

• Lorsque A1 = 0, les intérêts sont calculés à partir du solde initial. La référence circulaire est désactivée et Excel effectue ses calculs normalement.

• Lorsque A1 = 1, les intérêts sont calculés à partir du solde actualisé. La logique circulaire s'active et crée une boucle de rétroaction intentionnelle.

Utilisation d'un disjoncteur. Image fournie par l'auteur.

Quand utiliser un disjoncteur

Les disjoncteurs sont utiles pour :

• Désactiver la logique circulaire lors de la création et de la validation de formules
• Activer et désactiver la logique circulaire pour isoler les problèmes et vérifier les résultats
• Présentation du modèle avec la logique circulaire désactivée afin de faciliter l'explication des hypothèses et des mécanismes.

Supprimer les formules problématiques et repartir à zéro

Il s'agit de la solution ultime : à utiliser lorsque les autres méthodes échouent. Il convient aux classeurs présentant des dépendances complexes, aux fichiers hérités dont la logique n'est pas claire ou aux situations où le temps presse et où une solution rapide mais fiable est nécessaire.

Au lieu de tenter de résoudre des formules défectueuses, il est préférable de réinitialiser le modèle et de le reconstruire méthodiquement. 

Voyons comment.

Étape 1 : Veuillez créer une copie de sauvegarde.

Avant d'effectuer des modifications, veuillez créer une sauvegarde complète du classeur. Pour ce faire :

• Veuillez ouvrir le classeur dans Excel.

• Veuillez vous rendre dans Fichier > Enregistrer sous > Parcourir et sélectionner un autre emplacement ou conserver le même dossier pour enregistrer votre fichier.

• Veuillez renommer le fichier, par exemple, Workbook_Backup.xlsx

• Veuillez cliquer Enregistrer

Veuillez créer une copie de sauvegarde d'un classeur. Image fournie par l'auteur.

Cela permet de conserver les formules, la logique et les résultats d'origine afin que vous puissiez vous y référer ultérieurement ou revenir en arrière si nécessaire.

Veuillez ne jamais appliquer cette méthode sur la seule copie d'un fichier.

Étape 2 : Documenter la structure actuelle

Ensuite, veuillez noter le fonctionnement actuel du classeur :

• Veuillez prendre des captures d'écran des sections importantes.
• Veuillez noter ce que chaque feuille de calcul évalue.
• Identifier les entrées, les sorties et les résumés

Cette documentation vous aide à comprendre ce qui doit être reconstruit et empêche la perte de logique importante lors du nettoyage.

Étape 3 : Remplacer les formules par des valeurs

Pour supprimer toutes les dépendances circulaires en une seule fois, veuillez copier les plages concernées et utiliser la fonction « Collage spécial » d'. Cliquez avec le bouton droit de la souris > Collage spécial > Valeurs pour remplacer les formules par leurs résultats calculés.

Veuillez utiliser l'option Collage spécial. Image fournie par l'auteur.

Cela supprime immédiatement toutes les références circulaires tout en conservant les chiffres visibles comme référence temporaire.

Étape 4 : Reconstruire les formules de manière incrémentielle

Veuillez reconstruire le classeur section par section plutôt que dans son intégralité :

1. Ajouter des formules pour un bloc logique
2. Presse F9 pour forcer le recalcul.
3. Veuillez vérifier qu'aucune référence circulaire n'apparaît.
4. Veuillez passer à la section suivante.

Étape 5 : Vérifier les résultats par rapport à la sauvegarde

Au fur et à mesure que chaque section est reconstruite, veuillez comparer les résultats avec le fichier de sauvegarde. Vous pourriez remarquer des différences qui indiquent :

• Données manquantes
• Logique de formule incorrecte
• Erreurs de calcul précédemment non détectées

Détection avancée des références difficiles à identifier

Certaines références circulaires ne sont pas détectées par les outils standard d'Excel. Dans de tels cas, veuillez envisager des techniques avancées : 

Macros VBA pour une analyse complète

Lorsque le traçage manuel est trop lent, VBA peut analyser des milliers de cellules en quelques secondes et identifier les références circulaires dans l'ensemble du classeur.

L'approche de base se présente comme suit :

• Appuyez sur Alt + F11 pour ouvrir l'éditeur Visual Basic.
• Veuillez vous rendre à Insérer > Module.
• Veuillez insérer une macro de détection des références circulaires.
• Presse F5 pour l'exécuter.

Interface VBA. Image fournie par l'auteur.

Conseil: Il n'est pas nécessaire de créer du code VBA à partir de zéro. Veuillez décrire clairement ce que vous souhaitez et laissez chatGPT générer une macro de détection personnalisée.

Voici un exemple de script VBA : 

Option Explicit
Public Sub HighlightCircularReferences_ActiveSheet()
    Dim ws As Worksheet, c As Range
    Dim hasAny As Boolean

    Set ws = ActiveSheet

    Application.ScreenUpdating = False

    ' Optional: clear previous highlighting (only on used range)
    On Error Resume Next
    ws.UsedRange.Interior.Pattern = xlNone
    On Error GoTo 0

    ' Scan formula cells only
    On Error Resume Next
    For Each c In ws.UsedRange.SpecialCells(xlCellTypeFormulas)
        On Error GoTo 0

        If IsCircularFormula(c) Then
            c.Interior.Color = RGB(255, 230, 153) ' light yellow
            hasAny = True
        End If
    Next c

    Application.ScreenUpdating = True

    If hasAny Then
        MsgBox "Circular reference cells highlighted on: " & ws.Name, vbInformation
    Else
        MsgBox "No circular references found on: " & ws.Name, vbInformation
    End If
End Sub

Private Function IsCircularFormula(ByVal startCell As Range) As Boolean
    Dim inPath As Object
    Set inPath = CreateObject("Scripting.Dictionary")

    IsCircularFormula = TraceForCycle(startCell, startCell, inPath, 0)
End Function

Private Function TraceForCycle(ByVal startCell As Range, ByVal curCell As Range, _
                               ByVal inPath As Object, ByVal depth As Long) As Boolean
    Dim prec As Range, a As Range, x As Range
    Dim key As String

    ' Safety limit to avoid deep/huge graphs freezing Excel
    If depth > 200 Then Exit Function

    key = curCell.Parent.Name & "!" & curCell.Address(False, False)

    If inPath.Exists(key) Then
        ' We hit a loop in the current path
        TraceForCycle = True
        Exit Function
    End If

    inPath.Add key, True

    ' Direct precedents (cells referenced by curCell)
    On Error Resume Next
    Set prec = curCell.DirectPrecedents
    On Error GoTo 0

    If Not prec Is Nothing Then
        For Each a In prec.Areas
            For Each x In a.Cells
                ' If any precedent is the start cell -> circular
                If x.Address(External:=True) = startCell.Address(External:=True) Then
                    TraceForCycle = True
                    Exit Function
                End If

                ' Recurse only within worksheets (avoid names/constants)
                If x.Parent Is curCell.Parent Or True Then
                    If TraceForCycle(startCell, x, inPath, depth + 1) Then
                        TraceForCycle = True
                        Exit Function
                    End If
                End If
            Next x
        Next a
    End If

    inPath.Remove key
End Function

Veuillez utiliser les macros VBA pour identifier les cellules contenant des références circulaires. Image fournie par l'auteur.

Cette macro vérifie la feuille active pour détecter les références circulaires dans les formules. Si une formule fait référence à elle-même, directement ou par l'intermédiaire d'autres cellules, la macro met ces cellules en évidence afin que vous puissiez facilement identifier le problème. Cela fonctionne même si le calcul itératif est activé.

VBA a mis en évidence les cellules de référence circulaire. Image fournie par l'auteur.

Analyse inter-feuilles de calcul

Les références circulaires qui s'étendent sur plusieurs feuilles de calcul sont plus difficiles à détecter, car les flèches de suivi ne traversent pas les onglets.

Cela pourrait ressembler à ceci :

• La feuille A fait référence à la feuille B.
• La feuille B fait référence à la feuille C.
• La feuille C fait référence à la feuille A.

Une méthode pour détecter cela consiste à cartographier les dépendances des feuilles. Pour ce faire, veuillez noter quelles feuilles font référence à d'autres feuilles et rechercher les boucles dans cette liste.

Vous pouvez également utiliser Rechercher et remplacer pour localiser les références entre feuilles :

• Appuyez sur Ctrl + H

• Veuillez saisir un nom de feuille suivi de ! (par exemple, Sheet1!).

• Cliquez sur Rechercher tout

Excel répertorie toutes les formules qui font référence à cette feuille, ce qui vous permet de suivre les dépendances entre les tableaux croisés.

Veuillez identifier les feuilles connectées dans un classeur. Image fournie par l'auteur.

Remarque: La fonction Rechercher et remplacer ne détecte pas automatiquement les références circulaires. Il identifie uniquement les dépendances entre feuilles, que vous analysez ensuite pour détecter les boucles.

Dépannage des plages nommées

Les plages nommées peuvent masquer les références circulaires, car les formules n'affichent plus les adresses directes des cellules. Pour les examiner :

• Veuillez ouvrir le « Gestionnaire de noms » à l'aide de Ctrl + F3
• Veuillez vérifier chaque définition de plage nommée.
• Recherchez les plages qui contiennent des cellules faisant référence au même nom de plage dans les formules.

Veuillez vérifier les plages nommées. Image fournie par l'auteur.

Problèmes de logique conditionnelle

Certaines références circulaires n'apparaissent que dans des conditions spécifiques.

Par exemple :

=IF(A1>100, A2+10, 100)

Lorsque A1 est inférieur à 100, Excel applique la deuxième condition et la référence circulaire n'existe pas. Lorsque A1 dépasse 100, Excel évalue la première condition et tente de calculer A2 à l'aide d'A2 lui-même, ce qui déclenche une référence circulaire.

Pour détecter ces problèmes :

• Veuillez tester différentes valeurs d'entrée afin de vérifier tous les chemins conditionnels.
• Veuillez être attentif aux avertissements de référence circulaire qui n'apparaissent que dans certaines conditions.

Pour les corriger, veuillez déplacer la logique circulaire dans des cellules d'aide distinctes et simplifier les instructions IF imbriquées en calculs plus clairs, étape par étape.

Comment éviter les références circulaires dans Excel

Quelques habitudes prises dès le départ peuvent vous faire gagner des heures de débogage par la suite, à mesure que les classeurs s'étoffent ou sont partagés avec d'autres personnes. 

Voici donc quelques bonnes pratiques que vous devriez suivre : 

Planification du classeur et conception des formules

Une structure adéquate empêche la plupart des références circulaires avant même qu'elles ne se produisent. Veuillez vous assurer de procéder comme suit :

• Séparez les entrées, les calculs et les sorties afin que les formules suivent une direction claire.

• Veuillez utiliser des cellules ou des colonnes d'aide plutôt que de tout regrouper dans une seule formule.

• Les calculs doivent dépendre des valeurs antérieures, et non de leurs propres résultats ou des totaux en aval.

• Veuillez garder les cellules de saisie à distance des totaux et des résumés afin d'éviter les plages d'auto-référence.

Opérations de copier-coller et gestion des références

De nombreuses références circulaires sont introduites lors du copier-coller. Voici donc les éléments à prendre en considération : 

• Veuillez d'abord coller une formule dans une cellule et vérifier la barre de formule avant de la copier dans une plage.

• Veuillez utiliser des références absolues (par exemple, $A$1) pour les taux et les hypothèses qui ne devraient pas changer.

• Veuillez ne pas placer de formules de synthèse à l'intérieur des plages qu'elles calculent.

• Des noms clairs permettent de comprendre immédiatement à quoi se réfère une formule et réduisent les erreurs à mesure que les plages s'étendent.

Documentation et audits réguliers

Une documentation claire est d'une grande utilité. C'est pourquoi il est important de garder ces éléments à l'esprit lorsque vous travaillez :

• Veuillez ajouter de brefs commentaires dans les cellules ou tenir à jour une feuille de travail simple.

• Veuillez vérifier les formules à l'aide des outils de vérification des formules avant les présentations ou les transferts importants.

• Veuillez sauvegarder les versions des fichiers critiques afin de pouvoir retracer quand les problèmes sont apparus.

• Dans les fichiers collaboratifs, veuillez vérifier régulièrement les modifications afin d'éviter la propagation de petites erreurs.

Conclusions finales

Si vous êtes actuellement confronté à une référence circulaire, veuillez vérifier si elle est accidentelle ou intentionnelle. Cette décision détermine si vous devez restructurer les formules, activer l'itération ou repenser une partie du modèle.

Veuillez ne pas vous précipiter vers les paramètres ou les solutions de contournement avant d'être certain que la logique elle-même est cohérente.

Une fois le problème résolu, veuillez prendre un moment pour faciliter l'utilisation du fichier à l'avenir. Veuillez ajouter de brèves notes aux formules importantes, enregistrer une copie propre du classeur et vous assurer que toute autre personne qui l'utilise comprend comment les calculs sont censés fonctionner.