HotXLS : intersection implicite des noms définis en Delphi

Un nom défini qui renvoie à une colonne entière est lu par Excel comme une seule cellule dès qu'il apparaît dans une position scalaire : =Vertical+1 en ligne 7 signifie « la cellule de la ligne 7 de Vertical », pas toute la plage. HotXLS Delphi Component applique cette intersection implicite à deux niveaux, pendant l'évaluation et pendant l'extraction des dépendances, parce qu'obtenir la bonne valeur ne suffit pas. Un parcours de dépendances qui étend le nom à toute sa plage laisse toute formule en aval qui alimente une cellule de cette plage refermer un cycle qui n'existe pas, et TXLSXWorkbook.Recalculate devrait alors refuser le classeur entier comme circulaire

Un classeur d'amortissement de prêt avec 4805 formules est le cas de contrainte qui met tout cela à l'épreuve. Le recalculer correctement depuis des caches périmés dépend de trois mécanismes séparés : l'intersection implicite des noms, l'ordonnancement des balayages de recherche, et le type numérique utilisé pour l'arithmétique imbriquée. Se tromper sur l'un d'eux et le symptôme est soit lxErrorRef, le code de référence circulaire, soit des totaux qui s'écartent d'un calcul indépendant ligne par ligne. Cet article parcourt chaque mécanisme avec le code source en cause

Pourquoi une référence scalaire à un nom de colonne crée-t-elle un faux cycle ?

Parce qu'un graphe de dépendances ne connaît que des arêtes, et qu'une arête allant d'une formule à une plage de 480 lignes représente 480 arêtes, dont l'une repart vers une cellule qui dépend de la formule. Prenez =IF(TRUE,Vertical+1,0) en B1 avec Vertical défini comme Inputs!$A$1:$A$2, et =B1+1 en A2. Excel évalue B1 comme A1+1 et A2 comme B1+1, une simple chaîne. Un parcours qui enregistre B1 comme dépendant de A1:A2 fait de A2 un précédent de B1, alors que A2 liste déjà B1 comme précédent, et la file de Kahn qui pilote le recalcul incrémental dans HotXLS ne voit jamais ni l'un ni l'autre nœud atteindre un degré entrant nul. C'est le motif dont sont faits les modèles de prêt : chaque ligne de période référence des colonnes nommées pour le solde, le taux et le nombre de paiements, chaque nom couvre tout l'échéancier, et chaque ligne écrit aussi dans ces colonnes. Étendez les noms et le graphe devient une seule grande composante fortement connexe. Évaluez-les avec intersection implicite et le graphe redevient un ensemble de chaînes courtes, une par ligne, ce que décrit ECMA-376 Partie 1 §18.17.2 pour un opérande de référence consommé là où une valeur unique est requise

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Inputs');
    Book.DefinedNames.Add('Vertical', 'Inputs!$A$1:$A$2');
    Book.DefinedNames.Add('Alias', '=Vertical');
    Sheet.Cells[1, 1].Value := 1;
    // Position scalaire : Vertical se réduit à A1 car la formule est en ligne 1
    Sheet.Cells[1, 2].Formula := '=IF(TRUE,Vertical+1,0)';
    Sheet.Cells[2, 1].Formula := '=B1+1';
    // Un nom défini par un autre nom s'intersecte quand même, donc ici c'est A2
    Sheet.Cells[2, 2].Formula := '=Alias';
    // Argument de classe référence : toute la plage est sommée, pas intersection
    Sheet.Cells[3, 2].Formula := '=SUM(Vertical)';
    // La ligne 6 est hors de A1:A2, intersection vide et IFERROR l'attrape
    Sheet.Cells[6, 2].Formula := '=IFERROR(Vertical,42)';

    if Book.Recalculate = lxOk then
    begin
      // B1 = 2, A2 = 3, B2 = 3, B3 = 4, B6 = 42
      // Étendre Vertical à A1:A2 ici transformerait B1 -> A2 -> B1 en faux cycle
    end;
  finally
    Book.Free;
  end;
end;

Comment HotXLS décide-t-il qu'un argument est scalaire ?

HotXLS lit la réponse dans la table des fonctions plutôt que dans la forme de l'argument. Chaque entrée de TXLSFormula.InitFuncHash est enregistrée via THashFunc.SetValue avec une chaîne de classes par argument facultative : 'IF' porte '100', 'SUMIF' porte '010', 'VLOOKUP' porte '1011', et 'SUM' n'en porte aucune, donc tous ses arguments retombent sur la classe 0 au niveau de la fonction. TXLSFormula.FunctionArgumentClass(APtg, AArgument) expose cet octet via THashFuncEntry.ArgClass, et un résultat de 1 signifie classe valeur. Ce sont les trois mêmes classes que [MS-XLS] §2.2.2 attribue aux jetons d'opérande, et l'encodeur s'y fie aussi : quand il écrit une référence, il calcule le ptg comme $24 + $20 * aClass, ce qui donne PtgRef pour la classe 0, PtgRefV pour la classe 1 et PtgRefA pour la classe 2. Un fichier BIFF écrit par Excel stocke cette classe dans chaque jeton de référence, donc un moteur dont la table correspond à la spécification peut répondre « cet argument est-il scalaire » sans regarder les données. L'argument du milieu de SUMIF est le critère, une valeur ; le premier et le troisième sont des zones, des références. SUMPRODUCT est enregistré avec la classe 2 au niveau de la fonction, tableau, et c'est pourquoi =SUMPRODUCT(Vertical,Vertical) multiplie encore toute la plage

Trois fonctions ne consultent pas leur propre entrée de table au-delà du premier argument. IF (ptg 1), CHOOSE (ptg 100) et IFERROR (ptg 255) transmettent ce qu'elles sélectionnent, si bien que leurs arguments de branche héritent de la classe de la position qu'occupe la fonction elle-même. C'est cette règle unique qui permet à =CHOOSE(1,Vertical,0) en G2 de se résoudre en A2 alors que =SUMIF(Vertical,">0",Vertical) juste à côté additionne encore les deux lignes, et c'est la règle qu'un échéancier d'amortissement sollicite le plus, parce que ses cellules de période s'appuient sur IF pour tester si le prêt est toujours ouvert

Propager la classe à travers le parcours de dépendances

L'extracteur de dépendances dans lxCalc.pas est un Walk récursif sur l'arbre syntaxique compilé, et il existe en double, une fois dans TXLSCalculator.ExtractDependencies pour le graphe par classeur et une fois dans ExtractWorkspaceDependencies pour le graphe inter-classeurs. Les deux parcours portent deux paramètres supplémentaires. AScalar démarre à True à la racine d'une formule, est recalculé pour chaque enfant de fonction à partir de FunctionArgumentClass, et est transmis tel quel pour les arguments de branche des ptg 1, 100 et 255. ANameRoot ne passe à True que lorsque le parcours descend dans la définition compilée d'un nom, et il ne survit qu'à travers les nœuds SA_GROUP, les parenthèses, pour qu'un nom défini comme =A1:A2+1 ne soit pas pris pour une simple plage. Quand les deux drapeaux sont à True sur un nœud SA_RANGE, AddResolvedRange réduit la plage avec le même utilitaire qu'utilise l'évaluateur avant d'enregistrer la dépendance. L'utilitaire est assez court pour être cité en entier

function IntersectNamedScalarRange(CurRow, CurCol: Integer;
  var Row1, Row2, Col1, Col2: Integer): Boolean;
begin
  Result := False;
  if (Row1 = Row2) and (Col1 = Col2) then Exit(True);   // déjà une cellule
  if (Col1 = Col2) and (CurRow >= Row1) and (CurRow <= Row2) then
  begin
    Row1 := CurRow; Row2 := CurRow;                     // colonne unique : prendre cette ligne
    Exit(True);
  end;
  if (Row1 = Row2) and (CurCol >= Col1) and (CurCol <= Col2) then
  begin
    Col1 := CurCol; Col2 := CurCol;                     // ligne unique : prendre cette colonne
    Result := True;
  end;
end;

Tout ce que l'utilitaire rejette — une zone bidimensionnelle, une référence multi-feuilles ou une formule dont la ligne tombe hors de la colonne nommée — produit #VALUE! côté évaluation et aucune dépendance du tout côté graphe, ce qui est le comportement d'Excel pour une intersection vide. Le côté évaluation vit dans TXLSCalculator.GetValueItemName : il retire les enveloppes SA_GROUP de la définition compilée, et si la racine est un SA_RANGE, il appelle GetRangeInfo, intersecte, et récupère l'unique cellule via FGetValue au lieu d'évaluer toute la définition. Les références externes prennent le chemin ordinaire, parce qu'il n'y a pas de ligne locale contre laquelle intersecter. L'origine du stockage et de la portée d'un nom est traitée dans l'article sur les noms définis et les formules inter-feuilles ; ce qui compte ici, c'est seulement ce que fait le moteur une fois le nom résolu

Pourquoi MATCH doit-il attendre une colonne à moitié calculée ?

Parce que l'argument de matrice de recherche de MATCH est une référence de balayage, et que les références de balayage sont délibérément tenues hors de l'ordre d'évaluation dur. L'article sur le balayage de recherche introduit TXLSDepRange.LookupScan et détaille ce à quoi vous renoncez en excluant les arêtes de balayage de l'ordonnancement : une formule de recherche peut s'exécuter avant que chaque cellule de sa plage ait été recalculée et lire des valeurs périmées. Dans une session interactive, cela converge à la passe suivante. Dans un recalcul par lots unique d'un modèle dont les caches sont périmés, non : PaymentCount, défini comme =MATCH(0.01,Balances,-1)+1, lirait les valeurs périmées encore posées dans la colonne de solde et renverrait un nombre de périodes qui ne pourrait pas être juste

TXLSDepGraph.TopoOrder traite donc les arêtes de balayage comme des arêtes d'ordonnancement souples. À côté du degré entrant dur, il tient un tableau ScanInDeg, qui compte les précédents de balayage sales par nœud et le décrémente à mesure que ces précédents sont émis, en s'appuyant sur les listes ScanPrecedents, ScanDependents et ScanPrecedentCount que le graphe stocke déjà. À chaque itération, la file de Kahn parcourt sa fenêtre de nœuds prêts pour trouver le premier dont ScanInDeg est nul et l'échange en tête ; si tous les nœuds prêts attendent encore un précédent de balayage, la tête est extraite dans son ordre stable. Les arêtes de balayage n'entrent jamais dans le degré entrant dur, donc un VLOOKUP autoréférent sur sa propre colonne reste légal, mais une recherche qui peut attendre un précédent capable de se terminer le fait. Avec trois cellules de solde portant des valeurs périmées, PaymentCount revient à 3 ; basculez l'entrée à zéro et =IFERROR(PaymentCount,99) voit le #N/A et renvoie 99

Pourquoi l'arithmétique imbriquée est-elle forcée en Double

Parce que l'arithmétique Variant de Delphi a un piège qui ne se montre que dans les positions imbriquées. Recopier un + ou un - de premier niveau dans deux locaux Double garde =B1-A1 exact. À l'intérieur de =IF(TRUE,B1-A1,0), l'implémentation évidente exécute la même soustraction en Value := Value - SubValue sur deux Variants, et quand un opérande est une valeur de cellule Int64 et l'autre un Double, le résultat observé est un Currency, un type à virgule fixe à quatre décimales, donc 1066.1854641400994 moins 120 revient tronqué à quatre décimales. Sur un échéancier où chaque paiement se compose à partir de la ligne précédente, cette erreur traverse des centaines de périodes avant d'atteindre les totaux, et c'est pourquoi TXLSCalculator.GetValueItem convertit les deux opérandes avant que l'opérateur ne tourne

// TXLSCalculator.GetValueItem, branche arithmétique binaire (lxCalc.pas)
if VarIsNull(Value) then Value := 0;
if VarIsNull(SubValue) then SubValue := 0;
// L'arithmétique Variant mixte Int64/Double peut promouvoir en Currency.
// L'arithmétique de tableur doit conserver la précision flottante.
if VarIsNumeric(Value) then Value := Double(Value);
if VarIsNumeric(SubValue) then SubValue := Double(SubValue);

Le garde-fou s'exécute avant SA_ADD, SA_SUB, SA_MUL et SA_DIV de la même façon, donc avec Int64(120) en A1 et 1066.1854641400994 en B1, la différence, la somme, le produit et le quotient imbriqués gardent tous la précision double. HotXLS ne prétend pas connaître chaque règle de promotion que la RTL applique aux types Variant mixtes selon les versions du compilateur ; il traite l'arithmétique de tableur comme du double IEEE et met les deux opérandes en double avant que l'opérateur ne les voie, ce qui supprime la question

Ce que le moteur garantit pour les colonnes nommées

Les trois mécanismes en place, les deux architectures de moteur recalculent le modèle d'amortissement depuis des caches périmés jusqu'à lxOk, et les 4805 valeurs correspondent à une attente indépendante ligne par ligne à 1E-7 près. Aucune itération n'est activée et aucun code d'erreur n'est masqué pour y arriver. Un vrai cycle passant par un nom, =B1 en A1 avec B1 qui lit encore Vertical, renvoie toujours une erreur, exactement comme il se doit

Les règles suivent Excel. L'intersection implicite s'applique à un nom dont la définition compilée, une fois les parenthèses retirées, est une plage d'une seule colonne ou d'une seule ligne sur une seule feuille ; un nom bidimensionnel en position scalaire donne #VALUE!, comme dans Excel, et une fonction que la table ne connaît pas reçoit la classe 0 de FunctionArgumentClass, donc ses arguments de nom sont étendus en entier. L'ordonnancement souple est une préférence, pas une garantie : un cycle uniquement de balayage s'évalue dans l'ordre stable et lit ce qui est en cache, ce qui est le compromis que l'article sur le balayage de recherche accepte à dessein. HotXLS est un composant tableur natif Delphi et C++Builder qui lit, recalcule et écrit XLS, XLSX, ODS et CSV sans Excel installé ; l'intersection des noms, la table de classes d'arguments et l'ordonnancement souple des balayages s'appliquent à chaque format parce que le moteur de calcul est partagé, et la couverture actuelle des fonctions est listée sur la page produit du HotXLS Delphi spreadsheet component