Recherches HotXLS et références circulaires fausses

Mettez =VLOOKUP(A1,B:B,1) dans une cellule de la colonne B et Excel la calcule sans récrimination. Donnez le même classeur à un moteur de recalcul par graphe de dépendances et vous aurez probablement une erreur de référence circulaire, car la formule dépend d'une plage qui contient la formule. HotXLS rapportait exactement cela jusqu'à la v2.361.98. La correction n'est pas un cas particulier pour les plages de colonnes entières ; c'est une distinction entre deux sortes d'arêtes de dépendance dont un moteur de tableur a besoin et qu'un graphe orienté simple n'a pas

L'argument de tableau de recherche de la famille de recherche, LOOKUP, MATCH, HLOOKUP, VLOOKUP, XLOOKUP et XMATCH, est maintenant marqué comme référence de balayage. Une référence de balayage marque toujours la formule comme à recalculer, donc éditer une cellule dans la plage recalcule la formule, mais elle ne contribue jamais à la détection de cycles ni à l'ordonnancement de l'évaluation. Les vrais cycles sont toujours trouvés ; les faux ont disparu

Pourquoi Excel permet-il à une plage de recherche de contenir la formule ?

Parce que cet argument n'est pas consommé comme l'est un opérande arithmétique. La famille de recherche balaye la plage à la recherche de valeurs en cache et renvoie une correspondance ; elle n'exige pas que la plage ait d'abord été évaluée à terme. Excel traite une plage de recherche se chevauchant elle-même comme lisant ce que ces cellules portent actuellement, ce qui est la même sémantique qu'il applique à tout classeur non itératif : les cellules qui n'ont pas été recalculées dans cette passe apportent leur dernière valeur calculée

Les références de colonnes entières en font le cas courant plutôt qu'exotique. B:B est la façon idiomatique d'écrire « toute la table de recherche » dans une feuille où des lignes sont ajoutées, et toute formule qui vit dans la colonne B se trouve alors à l'intérieur de sa propre plage de recherche. Les modèles financiers, les feuilles de rapprochement et les classeurs d'audit font cela en permanence, d'ordinaire sans que personne ne remarque que la plage se chevauche

Ce qu'un graphe de dépendances fait de la même formule

HotXLS recalcule de façon incrémentale, ce qui exige un vrai graphe de dépendances : des nœuds pour les cellules, des arêtes pour les références, un ordre topologique pour l'évaluation et une passe de composantes fortement connexes pour classer les cycles. Cette mécanique est décrite dans l'article sur le recalcul incrémental, et c'est précisément pourquoi le faux positif est apparu

Extrayez les dépendances de =VLOOKUP(A1,B:B,1) dans la cellule B7 et le second argument donne une plage contenant B7 elle-même. Le graphe a maintenant une boucle sur soi. Le degré entrant de ce nœud n'atteint jamais zéro, donc la passe topologique ne peut jamais l'ordonnancer, et la passe de composantes le classe comme un cycle. Le moteur raisonne correctement sur le graphe qu'on lui a donné. Le graphe est le mauvais modèle, car il encode un type d'arête là où le tableur en a deux

Deux classes d'arêtes, un graphe

Le changement ajoute un drapeau à l'enregistrement de référence résolue, TXLSDepRange.LookupScan, que l'extracteur de dépendances pose quand il parcourt l'argument de tableau de recherche de l'une des six fonctions. En aval, les arêtes provenant de ces références sont stockées à l'écart des arêtes ordinaires : le nœud de graphe garde des listes ScanDependents et ScanPrecedents à côté de ses listes normales de dépendants et de précédents

La séparation est ce qui rend la sémantique juste. Les arêtes de balayage sont parcourues par la propagation de saleté, donc une édition n'importe où dans B:B marque toujours B7 comme à recalculer et B7 recalcule. Les arêtes de balayage ne sont jamais comptées dans le degré entrant et n'entrent jamais dans le constructeur de composantes, donc elles ne peuvent créer aucun blocage topologique et ne peuvent être classées comme cycle. Les deux implémentations de graphe de la bibliothèque, le graphe classique par classeur et le graphe d'espace de travail inter-classeurs qui porte l'analyse de composantes, ont été changées ensemble ; les laisser diverger produirait un classeur qui recalcule différemment selon qu'il est ouvert seul ou dans un espace de travail

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Ledger');
    Sheet.Cells[1, 1].Value := 'ACC-4471';
    Sheet.Cells[1, 2].Value := 1200.00;
    // La plage de recherche couvre la colonne B, et cette formule y vit
    Sheet.Cells[7, 2].Formula := 'VLOOKUP(A1,B:B,1)';

    case Book.Recalculate of
      lxOk:
        // Avant la v2.361.98 cette branche était inatteignable pour cette feuille
        SaveReport(Book);
      lxErrorRef:
        LogWarning('Genuine circular reference - review model inputs');
    end;
  finally
    Book.Free;
  end;
end;

Ce que vous abandonnez en excluant les arêtes de balayage de l'ordonnancement

Exactement une chose, et elle vaut la peine d'être énoncée franchement plutôt que cachée. Comme les arêtes de balayage ne participent pas à l'ordre topologique, une formule de recherche peut être évaluée dans la même passe avant que certaines cellules de sa plage de recherche aient été recalculées, et elle lira alors leurs valeurs précédentes. Le résultat converge au recalcul suivant

C'est acceptable parce que c'est ce que fait Excel. Pour un classeur sans calcul itératif activé, la propre réponse d'Excel pour une valeur pas encore recalculée dans la passe courante est la dernière valeur calculée, donc un moteur qui reproduit ce comportement rejoint l'implémentation de référence plutôt que de l'approcher. Si vous avez besoin d'une réponse réellement convergée sur un modèle autoréférentiel, le mécanisme pour cela est le calcul itératif avec une limite d'itérations explicite, couvert dans l'article sur le calcul itératif, et il s'applique aux vrais cycles plutôt qu'aux chevauchements de balayage

Le danger de régence caché dans la correction

Ajouter LookupScan à TXLSDepRange a introduit un risque qui n'a rien à voir avec les recherches et tout à voir avec Pascal. TXLSDepRange est un enregistrement non géré, donc une variable locale de ce type n'est pas initialisée à zéro. Chaque endroit de la base de code qui en construit un à la main, y compris les blocs de dépendances de tables de données et plusieurs aides de test, a donc dû être mis à jour pour poser le nouveau champ explicitement. En rater un et l'octet qui se trouvait sur la pile décide si cette référence est traitée comme une arête de balayage, ce qui produit un bug de recalcul qui apparaît et disparaît avec des changements de code sans rapport

// Un nouveau champ booléen dans un enregistrement non géré fait de chaque site
// de construction manuelle un bug latent. Deux idiomes sûrs :
var
  R: TXLSDepRange;
begin
  FillChar(R, SizeOf(R), 0);      // tout mettre à zéro, puis remplir
  R.Sheet1 := SheetIndex;
  R.Sheet2 := SheetIndex;
  R.Row1 := Row; R.Col1 := Col;
  R.Row2 := Row; R.Col2 := Col;

  // ou poser chaque champ, y compris le nouveau, sur chaque site
  R.LookupScan := False;
end;

La règle générale que cela a rapportée : ajouter un champ à un enregistrement construit sur la pile à plus de quelques endroits est un changement plus risqué qu'il n'y paraît, et le compilateur ne vous aidera pas à trouver les sites. Si l'enregistrement est atteignable depuis un chemin chaud, préférez un aide qui l'initialise complètement à faire confiance à chaque site d'appel pour être mis à jour

Distinguer un vrai cycle d'un chevauchement de balayage

Rien dans ce changement n'affaiblit la détection de cycles. =B7+1 dans B7 est toujours un cycle, une chaîne de trois formules qui se referme sur elle-même est toujours un cycle, et les deux sont toujours rapportés par le résultat de recalcul avec les membres du cycle conservant leurs précédentes valeurs en cache tandis que tout ce qui est hors du cycle reste à jour. Ce qui a changé est seulement que l'argument de tableau de recherche ne fabrique plus de cycles qu'Excel ne voit pas

Si vous auditez un classeur et voulez savoir quelles références le moteur a réellement résolues et dans quel ordre, le traceur d'évaluation est l'outil pour cela ; l'article sur le traceur d'évaluation de formules couvre comment lire sa sortie. HotXLS est un composant tableur natif Delphi et C++Builder qui lit et écrit XLS, XLSX, ODS et CSV sans Excel installé, et le moteur de recalcul est le même sur chaque format ; la couverture actuelle des fonctions et du moteur est listée sur la page produit du HotXLS Delphi spreadsheet component