Une colonne de 10 000 formules, dont chacune majore la valeur de la cellule au-dessus d'elle, tient à peu près au modèle le moins cher qu'un tableur puisse héberger. Dans un évaluateur qui calcule une formule référencée sur-le-champ chaque fois qu'il en rencontre une, les totaux restent justes, mais la facture arithmétique non : la dernière cellule entraîne 9 999 ancêtres en évaluation, celle du dessus 9 998, et une seule passe complète s'ajoute à environ 50 millions d'évaluations pour 10 000 multiplications. Changez un taux de croissance en haut et cet évaluateur doit repayer toute la facture, car il ne garde aucune trace de quelle cellule lit laquelle
Pourquoi une cellule éditée peut coûter une passe sur tout le classeur
Deux raccourcis produisent ce comportement, et la plupart des moteurs de formules faits maison en prennent un. Le raccourci récursif évalue chaque formule référencée à neuf et ignore toute valeur déjà en cache, ce qui transforme une chaîne en travail quadratique et une référence circulaire en débordement de pile. Le raccourci passe-complète est plus sûr : évaluer chaque formule dans un ordre fixe, autant de fois qu'il faut pour que plus rien ne change. C'est correct, et son coût suit la taille du classeur plutôt que la taille de l'édition
Excel évite les deux avec sa chaîne de calcul, un ordonnancement des cellules de formule qui permet à une édition de marquer un petit ensemble de cellules et de ne parcourir que la queue affectée. Le moteur XLSX de HotXLS emploie la même idée sous la forme d'un graphe de dépendances explicite derrière TXLSXWorkbook.Recalculate. Le premier appel construit le graphe à partir de chaque formule de chaque feuille et les évalue toutes. Chaque appel suivant n'évalue que les formules en aval des cellules dont la valeur a changé depuis la passe précédente, plus une courte liste de formules qui doivent tourner à chaque fois
Comment Recalculate transforme une édition en une seule passe ordonnée
Chaque cellule de formule devient un nœud, et une arête va de chaque cellule de formule qu'une formule lit vers la formule qui la lit. Une référence telle que SUM(Model!B2:B500) produit une arête depuis chaque cellule de formule à l'intérieur de ce rectangle, et une référence 3D sur une série de feuilles est confrontée à chaque feuille de la série. Les constantes simples ne sont pas des nœuds du tout ; elles ne comptent que comme le point de départ d'une édition
Écrire Value sur une cellule ne fait presque rien au moment de l'écriture. Le classeur met l'adresse de la cellule en file d'attente, et c'est tout. Quand Recalculate démarre, chaque formule dont les références couvrent une adresse en file d'attente est marquée dirty, et la marque circule le long des arêtes vers tous les dépendants de cette formule, puis vers les leurs. Un classeur sur lequel Recalculate n'a jamais été appelé ne garde aucun graphe, et ses écritures coûtent un unique test de nil, si bien qu'un code qui ne fait que lire et écrire des valeurs ne paie rien pour la fonctionnalité
La partie sale du graphe est ensuite triée avec l'algorithme de Kahn. Le détail qui rend la passe bon marché est la façon dont les degrés entrants sont comptés : seuls les antécédents sales comptent. Un antécédent propre détient déjà une valeur en cache valide, si bien qu'une formule ne l'attend jamais. Pendant la passe, une formule qui référence une autre cellule de formule lit la valeur en cache de cette cellule au lieu de la réévaluer ; le tri garantit que le cache est déjà à jour. Chaque formule sale est évaluée exactement une fois
Le code ci-dessous construit la chaîne du paragraphe d'ouverture et édite son unique entrée. Les index de feuilles dans HotXLS commencent à 1, si bien que Inputs est la feuille 1 et Model la feuille 2 ; lignes et colonnes dans Cells sont en base 1 également
uses
SysUtils, lxCalc, lxHandleX; // lxOk est déclaré dans lxCalc
var
Book: TXLSXWorkbook;
Inputs, Model: TXLSXWorksheet;
Row: Integer;
Last: Variant;
begin
Book := TXLSXWorkbook.Create;
try
Inputs := Book.Sheets.Add('Inputs'); // Sheets[1]
Model := Book.Sheets.Add('Model'); // Sheets[2]
Inputs.Cells[2, 2].Value := 0.05; // hypothèse de croissance dans Inputs!B2
Model.Cells[2, 2].Formula := 'Inputs!B2*1000';
for Row := 3 to 10000 do
Model.Cells[Row, 2].Formula :=
'B' + IntToStr(Row - 1) + '*(1+Inputs!$B$2)';
if Book.Recalculate <> lxOk then // construit le graphe, évalue 9 999 formules
Exit;
Inputs.Cells[2, 2].Value := 0.07; // met une adresse en file d'attente
if Book.Recalculate = lxOk then // les formules alimentées par Inputs!B2 tournent à nouveau (ici : les 9 999)
Last := Model.Cells[10000, 2].Value; // résultat en cache, lu comme n'importe quelle valeur
// Lecture ponctuelle : RecalcCell lance Recalculate, puis renvoie la cellule
// (feuille 2 = Model, ligne 10000, colonne 2)
Book.RecalcCell(2, 10000, 2, Last);
finally
Book.Free;
end;
end;
Dans ce modèle particulier, chaque formule lit Inputs!$B$2, si bien que la seconde passe touche encore les 9 999 ; le graphe ne peut pas rétrécir une édition qui affecte réellement tout. Le gain apparaît dans les modèles à nombreuses entrées, où une boucle de scénarios écrit trois ou quatre cellules et le reste du classeur garde ses valeurs en cache
Les écritures de valeurs restent bon marché, les écritures de formules reconstruisent le graphe
Seules les éditions de valeurs restent incrémentielles. Tout ce qui peut changer l'ensemble des nœuds ou des arêtes marque tout le graphe comme obsolète, et le prochain Recalculate le reconstruit de zéro et évalue chaque formule. Cela couvre l'affectation de Formula, le vidage d'une cellule de formule en y écrivant Null ou une valeur vide, l'ajout, le déplacement ou la suppression de feuilles, l'insertion ou la suppression de lignes et de colonnes, l'édition de noms définis, et la modification des tables de données what-if. Enregistrer ou désenregistrer une fonction utilisateur, ou affecter OnUserFunction, garde le graphe mais marque chaque nœud dirty
La conséquence pratique pour les générateurs de rapports est une forme de boucle. Disposez les formules une fois, appelez Recalculate, et à partir de là ne touchez que des valeurs. Une boucle qui réécrit un modèle de formule par client puis recalcule obtient une reconstruction complète à chaque itération, exactement le coût que le graphe existe pour éviter. Quand un changement de modèle est vraiment nécessaire, groupez toutes les éditions de formules devant un unique Recalculate, pour que la reconstruction n'arrive qu'une fois. La boucle de scénarios elle-même n'écrit ensuite que des valeurs
// Modèle construit une fois ; Recalculate déjà appelé
for I := 0 to High(Scenarios) do
begin
Inputs.Cells[2, 2].Value := Scenarios[I].Growth; // écritures de valeurs seulement
Inputs.Cells[3, 2].Value := Scenarios[I].Discount;
if Book.RecalcNeeded then // True quand des cellules sont en file ou dirty
if Book.Recalculate <> lxOk then
Break;
Results[I] := Model.Cells[10000, 2].Value;
end;
// Laisser SaveAs recalculer seulement si quelque chose pend encore
Book.RecalcBeforeSave := xrmIfDirty;
Book.SaveAs('scenarios.xlsx');
RecalcBeforeSave vaut xrmManual par défaut, qui ne recalcule jamais pendant un enregistrement. xrmIfDirty est le réglage à utiliser pour les fichiers générés : il ne recalcule que quand le graphe est obsolète ou porte des formules dirty, si bien qu'un classeur déjà recalculé dans la boucle est écrit tel quel, et qu'un classeur avec une édition oubliée n'est pas enregistré avec des valeurs en cache périmées
Quelles fonctions Excel forcent le recalcul à chaque passe ?
Les formules qui appellent NOW, TODAY, RAND, RANDBETWEEN, RANDARRAY, OFFSET, INDIRECT, CELL, INFO ou GETPIVOTDATA sont volatiles dans HotXLS : elles sont marquées dirty au début de chaque passe, que quelque chose en amont ait changé ou non, et tous leurs dépendants le sont aussi. Les fonctions d'horloge et aléatoires sont volatiles pour la raison évidente. OFFSET et INDIRECT sont volatiles parce que les cellules qu'elles lisent sont calculées à partir des valeurs d'arguments à l'exécution, et aucune marche statique de la formule ne peut dire quelles arêtes dessiner
La même règle vaut pour les références que le constructeur de graphe ne peut pas épingler à un rectangle concret, pour un nom défini qui se réfère à lui-même à travers d'autres noms, pour les références de spill de tableaux dynamiques, et pour les tables de données what-if. Les noms définis en général ne sont pas un problème : le constructeur développe un nom vers les plages qu'il désigne, si bien que les formules qui passent par des noms de portée classeur ou feuille obtiennent de vraies arêtes. L'article sur les noms définis et les formules inter-feuilles couvre la résolution de ces noms
La direction conservatrice est la bonne ici. Une évaluation inutile coûte un peu de temps ; une arête manquante signifie un nombre périmé dans un rapport qui a l'air fini. Gardez OFFSET et INDIRECT hors des chemins critiques des grands modèles et confinez-les aux rares cellules qui ont réellement besoin d'adresses calculées, car une cellule volatile près du haut d'une chaîne rend toute la chaîne volatile en pratique
Les fonctions fournies via OnUserFunction ou RegisterUserFunction sont à l'autre bout. Elles ne sont pas volatiles, si bien qu'un gestionnaire n'est rappelé que quand les cellules alimentant ses arguments changent, comme n'importe quelle fonction intégrée. Un gestionnaire qui lit un taux de change depuis une base de données continue donc de rendre sa dernière réponse à travers les passes. Écrivez plutôt le taux dans une cellule d'entrée et passez cette cellule en argument ; le graphe voit alors la dépendance, et la formule ne rejoue que quand le taux change. L'article sur les fonctions personnalisées dans le moteur de formules HotXLS explique comment les arguments du gestionnaire arrivent
Références circulaires : lxErrorRef ou calcul itératif
Avec le défaut Iterate = False, TXLSXWorkbook.Recalculate renvoie lxErrorRef quand le tri trouve un cycle. Les membres du cycle sont les nœuds sales que l'algorithme de Kahn ne peut jamais libérer, et toute formule sale en aval d'eux est bloquée pour la même raison. Ces formules gardent leurs précédentes valeurs en cache, tandis que tout le reste de l'ensemble sale s'évalue dans l'ordre. Une passe en échec marque aussi le graphe comme obsolète, si bien que l'appel suivant reconstruit le graphe et évalue chaque formule une fois de plus
case Book.Recalculate of
lxOk:
Book.SaveAs(OutputFile);
lxErrorRef:
// le cycle et ses formules en aval gardent encore leurs anciens caches ;
// LogWarning est votre propre routine
LogWarning('Circular reference in model, report not written');
else
LogWarning('Recalculation did not complete');
end;
Mettre Iterate := True active le même calcul itératif qu'offre Excel. Le moteur évalue le cycle et ses dépendants bloqués de façon répétée, jusqu'à IterateCount passes (défaut 100), jusqu'à ce qu'aucune valeur ne bouge de IterateDelta (défaut 0.001) ou plus. Les membres de cycle vides démarrent à 0. Atteindre la limite de passes garde les derniers résultats et renvoie quand même lxOk, voilà pourquoi l'itération doit rester désactivée pour les rapports générés : les cycles dans de vrais modèles de rapports sont presque toujours des erreurs, comme une ligne de total prise dans sa propre plage SUM, et un code d'erreur franc est ce que vous en voulez. Ne l'activez que pour des modèles construits autour d'une boucle voulue, comme un intérêt calculé sur un solde moyen qui inclut l'intérêt
Un cas qui a l'air circulaire n'est pas signalé comme cycle. L'argument table de recherche de VLOOKUP, HLOOKUP, LOOKUP, MATCH, XLOOKUP et XMATCH rend toujours la formule dirty quand une cellule de cette plage change, mais il ne compte pas dans la détection de cycles, si bien qu'un VLOOKUP(A1,B:B,1) écrit en colonne B s'évalue normalement. Pour les vrais cycles, le traceur d'évaluation de formules montre la chaîne de références qui se replie sur elle-même, une étape à la fois
Formules matricielles et passes multithread
Une formule matricielle CSE héritée est un nœud pour tout son rectangle ancré. La formule racine est évaluée une fois par passe, sa matrice est écrite dans chaque cellule membre (un résultat scalaire est diffusé sur le rectangle), et une formule qui référence n'importe quelle cellule du rectangle, pas seulement l'ancre supérieure gauche, reçoit une arête de la racine. Les formules de tableaux dynamiques sont traitées avec plus de prudence : elles tournent à chaque passe, et quand un spill change de taille, Recalculate reconstruit le graphe et répète la passe jusqu'à ce que les formes de spill se stabilisent. Si elles ne se stabilisent jamais en 32 tours, l'appel renvoie lxErrorCalc
RecalcThreads mis au-dessus de 1 évalue chaque niveau topologique de formules indépendantes sur des threads de travail, avec un calculateur séparé par thread. Les tables de données tournent toujours en série, et un classeur qui contient des tableaux dynamiques reste sur le chemin série. Tout gestionnaire de fonction utilisateur doit être thread-safe avant que vous montiez cette valeur ; pour un gestionnaire qui touche à un état partagé, garder RecalcThreads à son défaut de 0 est le choix le plus sûr
Le graphe de dépendances, la gestion des volatiles et le calcul itératif font partie du moteur XLSX du HotXLS Delphi Excel Component, qui couvre aussi le calculateur de formules, les noms définis et le pipeline de lecture et d'écriture autour d'eux. Pour des applications Delphi et C++Builder qui gardent des feuilles de tarification ou des classeurs de consolidation ouverts et les alimentent en nouvelles entrées toute la journée, le travail par édition suit alors les cellules que l'édition atteint réellement