Calcul itératif des références circulaires en Delphi

Pour calculer des références circulaires intentionnelles en Delphi, HotXLS expose le calcul itératif sur son moteur XLSX : passez TXLSXWorkbook.Iterate à True et TXLSXWorkbook.Recalculate mène chaque cycle de références détecté vers un point fixe — jusqu'à IterateCount passes, ou jusqu'à ce que chaque cellule varie de moins que IterateDelta — au lieu de renvoyer #REF! et de renoncer

Cette distinction compte plus que ne le laisse croire un simple booléen. Le même moteur qui détecte un cycle de références et refuse de boucler dessus va, une propriété basculée, évaluer ce cycle délibérément jusqu'à ce qu'il se stabilise. Garder les deux comportements bien distincts — quand un cycle est un défaut à signaler et quand il est un modèle à résoudre — est tout le sujet de cet article

Pourquoi une référence circulaire produit-elle une erreur par défaut ?

Par défaut, HotXLS traite tout cycle de références comme une erreur de saisie et le signale au lieu de le calculer. TXLSXWorkbook.Recalculate construit un graphe de dépendances de formules, évalue chaque cellule de formule en ordre topologique, et renvoie lxErrorRef dès qu'il trouve un cycle — les nœuds qui ne peuvent jamais être libérés pendant le tri topologique. Ces membres du cycle conservent leurs valeurs mises en cache précédentes ; chaque formule hors du cycle s'évalue encore normalement. La mécanique de ce graphe, et la raison pour laquelle les membres du cycle sont sautés plutôt que bouclés, sont traitées dans l'article compagnon sur le recalcul incrémental des formules et le graphe de dépendances

Le comportement par défaut est le plus sûr, car la plupart des cycles sont des bogues : une ligne de synthèse accidentellement tirée dans sa propre plage SUM, un copier-coller qui a décalé une référence sur elle-même. Un code d'erreur bien visible au moment du recalcul est exactement ce que vous voulez dans ces cas. Mais une classe de modèles précise et importante est circulaire à dessein. Les échéanciers à intérêts composés, les répartitions circulaires de coûts ou de frais généraux entre services, et les calculs de frais pilotés par le solde décrivent tous une valeur qui alimente légitimement ses propres entrées — et Excel ne les calcule qu'une fois que l'utilisateur coche Fichier → Options → Formules → Activer le calcul itératif

Comment activer le calcul itératif dans HotXLS ?

HotXLS reflète cette case à cocher d'Excel avec trois propriétés sur TXLSXWorkbook : Iterate active le mode, et quand elle vaut True un cycle détecté est confié à un solveur itératif au lieu de produire lxErrorRef. Prenez la paire de cellules canonique des intérêts composés, où le solde de clôture dépend des intérêts et où les intérêts dépendent du solde

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Model');
    Sheet.Cells[1, 2].Value   := 1000;     // B1 : capital initial
    Sheet.Cells[2, 2].Value   := 0.05;     // B2 : taux de la période
    Sheet.Cells[3, 2].Formula := 'B1+B4';  // B3 : solde     = capital + intérêts
    Sheet.Cells[4, 2].Formula := 'B3*B2';  // B4 : intérêts  = solde * taux

    Book.Iterate := True;                   // adhésion au calcul itératif
    if Book.Recalculate = lxOk then
      // B3 converge vers 1052,63..., B4 vers 52,63...
      Report(Sheet.Cells[3, 2].Value);
  finally
    Book.Free;
  end;
end;

B3 référence B4 et B4 référence B3, donc le graphe de dépendances signale un cycle à deux nœuds. Avec Iterate laissée à sa valeur par défaut False, cette paire reviendrait en lxErrorRef et aucune des deux cellules ne se stabiliserait. Avec True, Recalculate amorce le cycle à partir des valeurs mises en cache courantes et réévalue ses membres passe après passe, en réinjectant les sorties de chaque passe comme entrées de la suivante, jusqu'à ce que les nombres cessent de bouger. Ici la forme close est principal / (1 - rate), si bien que le solde se fixe à 1052,63 et les intérêts à 52,63 — les mêmes chiffres qu'Excel produit avec l'itération activée

Qu'est-ce qui arrête l'itération ?

Deux conditions d'arrêt indépendantes bornent le solveur, et comprendre les deux est ce qui évite à un modèle de tomber en erreur ou de tourner sans fin. IterateCount est le plafond dur du nombre de réévaluations des membres du cycle ; sa valeur par défaut est 100, comme dans Excel. IterateDelta est le seuil de convergence : après chaque passe, le solveur mesure la plus grande variation numérique sur toutes les cellules du cycle, et dès que ce maximum descend sous IterateDelta — 0,001 par défaut — la boucle de passes se rompt par anticipation. La condition atteinte la première met fin à l'itération

Book.Iterate := True;
Book.IterateCount := 1000;    // plafond dur : au plus 1000 passes sur le cycle
Book.IterateDelta := 0.0001;  // convergence : arrêt dès que chaque cellule bouge de < 0,0001

case Book.Recalculate of
  lxOk:
    // le cycle a convergé, OU il a atteint le plafond de 1000 passes et gardé
    // les valeurs de sa dernière itération -- les deux chemins renvoient lxOk si Iterate vaut True
    SaveWorkbook(Book);
  lxErrorRef:
    // atteignable seulement avec Iterate = False : le cycle a été signalé, pas résolu
    LogWarning('Circular reference with iteration disabled');
end;

Une conséquence mérite d'être énoncée sans détour, car elle est la limite honnête de la fonctionnalité. Quand le plafond est atteint sans que la variation descende sous IterateDelta, SolveCycleIteratively ne lève pas d'erreur — il renvoie lxOk et laisse les cellules porter les valeurs de leur dernière itération, exactement comme Excel écrit les derniers nombres calculés quand sa propre limite d'itération est atteinte sans convergence. Un code de retour réussi de Recalculate en mode itératif signifie donc « le solveur a tourné », pas « le solveur a convergé ». Un modèle dont la boucle de rétroaction diverge ou oscille consommera discrètement toutes les passes de IterateCount et rendra des nombres qui ne sont pas du tout un point fixe, sans qu'aucune exception marque la différence

Comment le réglage est-il enregistré dans les fichiers XLSX et XLS ?

Les réglages de calcul itératif persistent dans les deux formats de feuille de calcul, si bien qu'un classeur ouvert dans Excel se comporte comme votre code Delphi l'a configuré. Côté XLSX, l'écrivain n'émet l'élément OOXML <calcPr> que lorsque Iterate vaut True, et il omet tout attribut resté à sa valeur par défaut afin de garder une sortie minimale : un classeur qui utilise les valeurs par défaut écrit simplement <calcPr iterate="1"/>, tandis que iterateCount n'apparaît que s'il diffère de 100 et iterateDelta que s'il diffère de 0,001. À l'ouverture, TXLSXWorkbook relit les trois mêmes attributs, si bien que l'aller-retour est symétrique

Le moteur BIFF8 hérité (.xls), TXLSWorkbook, porte l'état équivalent dans trois enregistrements séparés sous un autre triplet de propriétés. EnableIteration correspond à l'enregistrement CalcIter ($0011, [MS-XLS] §2.4.33), MaxIterations à l'enregistrement CalcCount ($000C, [MS-XLS] §2.4.31), et MaxIterationChange à l'enregistrement CalcDelta ($0010, [MS-XLS] §2.4.32). Les accesseurs en écriture imposent les plages de la spécification — CalcCount doit se situer dans 1..32767, donc MaxIterations est borné, et un MaxIterationChange négatif revient à la valeur par défaut de 0,001. Réglez-les sur un classeur .xls chargé et les trois enregistrements de calcul sont écrits fidèlement à la sauvegarde

var
  Book: TXLSWorkbook;   // moteur BIFF8 (.xls)
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open('model.xls');
    Book.EnableIteration    := True;    // enregistrement CalcIter  $0011
    Book.MaxIterations      := 500;     // enregistrement CalcCount $000C (borné à 1..32767)
    Book.MaxIterationChange := 0.0001;  // enregistrement CalcDelta $0010
    Book.SaveAs('model.xls');           // les trois enregistrements de calcul font l'aller-retour
  finally
    Book.Free;
  end;
end;

Notez la séparation délibérée des noms : le moteur XLSX parle Iterate / IterateCount / IterateDelta (le vocabulaire OOXML), tandis que le moteur BIFF8 parle EnableIteration / MaxIterations / MaxIterationChange (aligné sur les noms d'enregistrements [MS-XLS]). Les deux triplets décrivent les trois mêmes boutons — un interrupteur, un plafond d'itérations et un delta de convergence — avec les mêmes valeurs par défaut : désactivé, 100 et 0,001

Quand une référence circulaire est-elle un bogue plutôt qu'un modèle ?

Activer l'itération n'est pas un moyen de faire disparaître les avertissements de référence circulaire, et la traiter ainsi est le piège. Activer Iterate globalement convertit chaque cycle accidentel — ceux que le code d'erreur par défaut était là pour attraper — en un nombre silencieusement convergé ou silencieusement non convergé. La discipline est inverse : gardez Iterate à False comme mode normal afin que les vraies erreurs de saisie remontent encore en lxErrorRef, et n'activez l'itération que sur les classeurs dont la circularité est voulue et comprise

Quand un cycle apparaît et que vous ne savez pas de quel type il est, le traceur d'évaluation de formules est l'outil qui les distingue : tracez la formule suspecte et la chaîne de références qui se replie sur elle-même devient visible étape par étape, ce qui vous permet de décider si elle encode une vraie boucle de rétroaction ou une auto-référence égarée. Il est aussi utile de se rappeler qu'une cellule du cycle peut appeler n'importe quelle fonction intégrée en parcourant la boucle — le même calculateur qui résout une formule d'ingénierie ou de nombres complexes évalue les membres du cycle à chaque passe — si bien qu'un modèle qui diverge relève souvent d'un problème de formule à l'intérieur de la boucle, pas d'un problème de réglages d'itération

La liste de contrôle pratique est courte. Vérifiez que la boucle possède un vrai point fixe avant de compter sur l'itération ; gardez IterateDelta assez serré pour que « convergé » veuille bien dire ce dont votre modèle a besoin ; et après un Recalculate censé converger, contrôlez une sortie connue plutôt que de vous fier au seul lxOk, puisque ce code ne sait pas distinguer une convergence d'un plafond d'itérations atteint

Le calcul itératif des références circulaires fait partie du moteur XLSX du HotXLS Delphi Excel Component, aux côtés du recalcul incrémental par graphe de dépendances sur lequel il repose et de la persistance OOXML et BIFF8 qui porte le réglage dans chaque fichier que vous écrivez. Pour les modèles financiers et d'ingénierie circulaires à dessein, c'est la différence entre un code d'erreur et une réponse