Recalculation incrémentielle de formules dans HotXLS pour Delphi

HotXLS, la bibliothèque Excel native pour Delphi et C++Builder, effectue une recalculation incrémentielle des formules via TXLSXWorkbook.Recalculate. Le premier appel construit un graphe de dépendances de formules et évalue chaque cellule contenant une formule ; chaque appel ultérieur réévalue uniquement les cellules affectées par les écritures de valeurs depuis la dernière passe, dans l'ordre topologique, en un seul passage dont le coût est proportionnel au nombre de cellules modifiées (dirty) plutôt qu'à la taille du classeur

Cette décision de conception fait toute la différence entre un modèle financier qui réagit à une modification d'hypothèse en quelques millisecondes et un autre qui se bloque pendant plusieurs secondes. Si vous générez des rapports dans lesquels une poignée de cellules d'entrée alimentent des milliers de formules en aval, le reste de cet article explique le rôle du graphe, quelles fonctions refusent le mode incrémentiel, et comment les références circulaires sont signalées au lieu de tourner en boucle indéfiniment

Pourquoi la modification d'une seule cellule recalcule-t-elle cent mille formules ?

Un moteur de formules naïf n'a aucune mémoire de qui dépend de qui, de sorte que sa seule action sûre après une modification consiste à tout évaluer à nouveau. Pire encore, la stratégie récursive classique — lorsque la formule A fait référence à la formule B, évaluer B sur-le-champ — réévalue les cellules référencées inconditionnellement, en ignorant toute valeur mise en cache. Une chaîne de n formules faisant chacune référence à la précédente coûte O(n²) évaluations par passe complète, et une référence circulaire entraîne la récursion dans une boucle sans fin. Tout développeur de feuilles de calcul ayant intégré un modèle en cascade dans un évaluateur récursif a vu ces deux modes d'échec se produire

Excel lui-même a résolu ce problème il y a des décennies avec sa chaîne de calcul : un classement des cellules de formule est maintenu de sorte qu'une modification marque un petit ensemble de cellules comme modifiées (dirty) et le moteur ne parcourt que la partie affectée de la chaîne. HotXLS applique la même idée sous la forme d'un graphe de dépendances explicite, construit une fois à partir des arbres de formules compilés et réutilisé lors des passes de recalcul. Le but n'est pas d'être astucieux ; il s'agit de faire en sorte que le coût du recalcul suive la taille de votre modification, et non la taille de votre classeur

Comment le graphe de dépendances transforme une modification en une passe unique

Le graphe de dépendances de HotXLS attribue un nœud à chaque cellule de formule, avec des arêtes reliant les antécédents aux dépendants. Lorsque votre code écrit la valeur d'une cellule, le classeur marque la cellule comme modifiée (dirty) ; lorsque Recalculate s'exécute, l'état de modification se propage le long des arêtes vers chaque formule en aval, et le sous-graphe modifié est évalué exactement une fois dans l'ordre topologique à l'aide de l'algorithme de Kahn. Une formule n'étant jamais visitée avant ses antécédents, chaque nœud ne nécessite qu'une seule évaluation — c'est ce qui rend la passe O(dirty)

L'ordre topologique résout également le problème de la récursion à sa racine. Lors d'une passe de recalcul, le moteur bascule dans un mode dédié dans lequel toute référence à une autre cellule de formule lit directement la valeur mise en cache de cette cellule au lieu de la réévaluer — l'ordonnancement garantit que le cache est déjà à jour. Ce même mécanisme évite qu'un cycle de référence ne déclenche une récursion infinie : rien à l'intérieur de la passe ne réintroduit jamais l'évaluateur pour une cellule voisine

var
  Book: TXLSXWorkbook;
  Inputs, Model: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Inputs := Book.Sheets.Add('Inputs');
    Model  := Book.Sheets.Add('Model');

    Inputs.Cells[2, 2].Value := 0.05;                 // growth assumption
    Model.Cells[2, 2].Formula := 'Inputs!B2*1000';    // XLSX formulas take no leading '='
    Model.Cells[3, 2].Formula := 'B2*(1+Inputs!B2)';
    // ... thousands more rows cascading off the same assumption ...

    Book.Recalculate;                 // first call: builds the graph, full evaluation

    Inputs.Cells[2, 2].Value := 0.07; // one edit marks one cell dirty
    Book.Recalculate;                 // second call: only the downstream chain runs
  finally
    Book.Free;
  end;
end;

Chaque résultat lands dans la cellule de la valeur mise en cache (Value), ainsi après le retour de Recalculate vous lisez les sorties de la même manière que vous lisez n'importe quelle cellule. Dans une boucle de génération de rapports, le modèle correspond exactement au code ci-dessus : chargez ou construisez le modèle une fois, puis alternez entre l'écriture de quelques cellules d'entrée et l'appel à Recalculate, en ne payant que pour les formules qui dépendent réellement de ce qui a changé

Quelles fonctions Excel forcent le recalcul à chaque passe ?

HotXLS traite NOW, TODAY, RAND, OFFSET et INDIRECT comme volatiles : toute formule contenant l'une de ces fonctions est réévaluée à chaque passe de Recalculate, que des éléments en amont aient changé ou non. Les trois premières sont volatiles pour la même raison que dans Excel — leur résultat dépend du moment de l'évaluation, non des autres cellules. OFFSET et INDIRECT sont volatiles pour une raison plus subtile : les cellules qu'elles lisent sont calculées au moment de l'exécution, le graphe ne peut donc pas savoir statiquement quelles arêtes dessiner pour elles

La même règle de prudence s'applique aux références que le générateurs de graphe ne peut pas limiter à un seul rectangle. Une formule utilisant une plage nommée multi-zones, ou une formule faisant référence à un classeur externe, est également dégradée en volatile et réévaluée à chaque passe. Cette politique est délibérée : une évaluation supplémentaire coûte un peu de temps, mais une arête de dépendance manquante entraîne une valeur obsolète non signalée dans un rapport généré, ce qui constitue un échec bien plus grave. Si votre modèle s'appuie sur des noms de portée classeur, l'article connexe sur les noms définis et les formules inter-feuilles explique comment les noms à zone unique sont résolus — ceux-ci participent normalement au graphe

Les conseils pratiques en découlent directement. Maintenez les chemins critiques d'un grand modèle sur des références simples de cellules et de plages où le graphe peut faire son travail, et limitez OFFSET et INDIRECT aux rares endroits qui nécessitent réellement un adressage dynamique. Un modèle contenant mille formules volatiles réexécute ces mille formules à chaque passe, quelle que soit la taille de la modification — exactement le comportement que les utilisateurs d'Excel connaissent avec les classeurs qui « recalculent à chaque frappe de touche »

Comment HotXLS signale-t-il les références circulaires ?

TXLSXWorkbook.Recalculate renvoie lxOk lors d'une passe correcte et lxErrorRef lorsqu'il détecte un cycle de référence. Les membres du cycle sont identifiés lors du tri topologique — ce sont les nœuds que l'algorithme de Kahn ne peut jamais libérer — et ils sont ignorés plutôt que traités en boucle : leurs valeurs mises en cache restent inchangées, tandis que chaque formule en dehors du cycle continue de s'évaluer normalement dans l'ordre. Votre point d'appel obtient un code d'erreur précis au lieu d'un blocage

case Book.Recalculate of
  lxOk:
    SaveReport(Book);
  lxErrorRef:
    // a reference cycle exists; cycle members kept their previous
    // cached values and everything outside the cycle is up to date
    LogWarning('Circular reference detected - review model inputs');
end;

Trouver quelles cellules forment le cycle est un travail de débogage, et le traceur d'évaluation de formules est l'outil idéal pour cela : tracez la formule suspecte et la chaîne de référence qui se replie sur elle-même devient visible étape par étape. Les cycles dans les modèles réels sont presque toujours une erreur d'écriture — une ligne de totalisation accidentellement incluse dans sa propre plage SUM — de sorte qu'un code d'erreur bien visible au moment du recalcul est précisément ce que vous souhaitez

Formules matricielles, suivi des modifications (dirty tracking) et reconstruction du graphe

Les formules matricielles CSE obtiennent un seul nœud pour tout le rectangle ancré, et non un nœud par cellule. La formule racine est évaluée une fois par passe ; la matrice résultante est écrite directement dans chaque cellule membre, et une formule qui fait référence à n'importe quelle cellule à l'intérieur de la plage ancrée — pas seulement l'ancre supérieure gauche — reçoit une arête de dépendance depuis ce nœud racine. Les résultats scalaires sont diffusés sur tout le rectangle selon les règles sémantiques des matrices héritées d'Excel

Le suivi des modifications (dirty tracking) intercepte les modificateurs de propriétés ordinaires, de sorte que rien ne change dans votre code. L'écriture de Value sur une cellule notifie le classeur et marque les dépendants comme modifiés ; l'attribution d'une nouvelle Formula est un changement structurel, elle marque donc l'ensemble du graphe comme obsolète, et le prochain Recalculate le reconstruit avant l'évaluation. L'ajout, la suppression ou le déplacement de feuilles invalide également le graphe, puisque l'identité des nœuds encode l'index de la feuille. Lorsqu'aucun graphe n'est actif — un classeur sur lequel vous n'appelez jamais Recalculate — les points d'interception coûtent une simple vérification de pointeur nul (nil check) par affectation, de sorte que les charges de travail d'écriture simples ne sont pas affectées

Une limite mérite d'être énoncée honnêtement : le graphe suit les dépendances entre les cellules, ainsi une fonction définie par l'utilisateur enregistrée via OnUserFunction is re-evaluated when the cells feeding its arguments change, like any other formula. Si vous étendez le moteur de cette manière, l'article sur les fonctions personnalisées dans le moteur de formules HotXLS présente le contrat de rappel et la réception des valeurs d'arguments

Le recalcul incrémentiel fait partie du moteur XLSX standard du composant HotXLS Delphi Excel Component, aux côtés du calculateur de formules, des noms définis et du pipeline d'importation/exportation qu'il accélère. Si votre application Delphi ou C++Builder gère des modèles dynamiques — feuilles de tarification, classeurs de consolidation, cascades de rapports — Recalculate fait toute la différence entre recalculer un classeur et recalculer une modification