Setzen Sie =VLOOKUP(A1,B:B,1) in eine Zelle der Spalte B, und Excel berechnet sie ohne Murren. Geben Sie dieselbe Arbeitsmappe an eine Recalculation-Engine mit Abhängigkeitsgraph, erhalten Sie wahrscheinlich einen Zirkelbezug-Fehler, denn die Formel hängt von einer Range ab, die die Formel enthält. HotXLS meldete genau das bis v2.361.98. Der Fix ist kein Sonderfall für Ganzspalten-Ranges; er ist eine Unterscheidung zwischen zwei Arten von Abhängigkeitskante, die eine Tabellen-Engine braucht und ein schlichter gerichteter Graph nicht hat
Das Lookup-Array-Argument der Lookup-Familie – LOOKUP, MATCH, HLOOKUP, VLOOKUP, XLOOKUP und XMATCH – ist nun als Scan-Referenz markiert. Eine Scan-Referenz säht weiterhin Unsauberkeit, also berechnet eine Bearbeitung einer Zelle innerhalb der Range die Formel neu, aber sie trägt nie zur Zykluserkennung oder zur Auswertungsordnung bei. Echte Zyklen werden weiterhin gefunden; die falschen sind weg
Warum erlaubt Excel eine Lookup-Range, die die Formel enthält?
Weil dieses Argument nicht so konsumiert wird wie ein arithmetischer Operand. Die Lookup-Familie durchsucht die Range nach gecachten Werten und gibt einen Treffer zurück; sie verlangt nicht, dass die Range zuvor vollständig ausgewertet wurde. Excel behandelt eine sich selbst überlappende Lookup-Range so, als lese sie das, was diese Zellen gerade halten, was dieselbe Semantik ist, die es auf jede nicht-iterative Arbeitsmappe anwendet: Zellen, die in diesem Durchlauf noch nicht neu berechnet wurden, steuern ihren zuletzt berechneten Wert bei
Ganzspalten-Referenzen machen das zum gewöhnlichen Fall statt zum exotischen. B:B ist die idiomatische Art, „die gesamte Lookup-Tabelle“ zu schreiben, in einem Blatt, in dem Zeilen angehängt werden, und jede Formel, die in Spalte B lebt, sitzt damit in ihrer eigenen Lookup-Range. Finanzmodelle, Abstimmungsblätter und Audit-Arbeitsmappen tun das ständig, meist ohne dass jemand bemerkt, dass die Range sich überlappt
Was ein Abhängigkeitsgraph mit derselben Formel macht
HotXLS berechnet inkrementell neu, was einen echten Abhängigkeitsgraph verlangt: Knoten für Zellen, Kanten für Referenzen, eine topologische Ordnung für die Auswertung und einen Durchlauf über stark zusammenhängende Komponenten, um Zyklen zu klassifizieren. Diese Maschinerie beschreibt der Artikel zur inkrementellen Neuberechnung, und genau deshalb erschien der Falsch-Positiv
Zieht man aus =VLOOKUP(A1,B:B,1) in Zelle B7 die Abhängigkeiten, liefert das zweite Argument eine Range, die B7 selbst enthält. Der Graph hat nun eine Selbstschleife. Der Eingangsgrad dieses Knotens erreicht nie null, also kann der topologische Durchlauf ihn nie einplanen, und der Komponentendurchlauf klassifiziert ihn als Zyklus. Die Engine schließt korrekt über den Graph, den sie bekam. Der Graph ist das falsche Modell, denn er codiert einen Kantentyp, wo die Tabelle zwei hat
Zwei Kantenklassen, ein Graph
Die Änderung ergänzt ein Flag am aufgelösten Referenzdatensatz, TXLSDepRange.LookupScan, das der Abhängigkeitsextraktor setzt, wenn er das Lookup-Array-Argument einer der sechs Funktionen durchläuft. Downstream werden Kanten aus diesen Referenzen getrennt von gewöhnlichen Kanten gespeichert: Der Graphknoten führt ScanDependents- und ScanPrecedents-Listen neben seinen normalen Abhängigen- und Vorgänger-Listen
Die Trennung macht die Semantik richtig. Scan-Kanten werden von der Dirty-Propagation durchlaufen, also markiert eine Bearbeitung irgendwo in B:B B7 weiterhin als dirty, und B7 berechnet neu. Scan-Kanten zählen nie in den Eingangsgrad und gelangen nie in den Komponenten-Builder, also können sie keinen topologischen Deadlock erzeugen und nicht als Zyklus klassifiziert werden. Beide Graph-Implementierungen in der Bibliothek, der klassische Graph je Arbeitsmappe und der arbeitsmappenübergreifende Workspace-Graph, der die Komponentenanalyse trägt, wurden gemeinsam geändert; sie auseinanderdriften zu lassen, würde eine Arbeitsmappe produzieren, die unterschiedlich neu berechnet, je nachdem, ob sie allein oder als Teil eines Workspace geöffnet wurde
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;
// Die Lookup-Range deckt Spalte B ab, und diese Formel lebt in ihr
Sheet.Cells[7, 2].Formula := 'VLOOKUP(A1,B:B,1)';
case Book.Recalculate of
lxOk:
// Vor v2.361.98 war dieser Zweig für dieses Blatt unerreichbar
SaveReport(Book);
lxErrorRef:
LogWarning('Genuine circular reference - review model inputs');
end;
finally
Book.Free;
end;
end;Was man aufgibt, wenn Scan-Kanten aus der Ordnung ausgeschlossen bleiben
Genau eine Sache, und sie lohnt sich, klar auszusprechen statt zu verstecken. Weil Scan-Kanten nicht an der topologischen Ordnung teilnehmen, kann eine Lookup-Formel im selben Durchlauf ausgewertet werden, bevor manche Zellen in ihrer Lookup-Range neu berechnet wurden, und sie liest dann deren vorherige Werte. Das Ergebnis konvergiert bei der nächsten Neuberechnung
Das ist akzeptabel, denn es ist, was Excel tut. Für eine Arbeitsmappe ohne aktivierte iterative Berechnung ist Excels eigene Antwort auf einen Wert, der im aktuellen Durchlauf noch nicht neu berechnet wurde, der zuletzt berechnete Wert, also trifft eine Engine, die dieses Verhalten reproduziert, die Referenzimplementierung, statt sie zu approximieren. Wer eine wirklich konvergierte Antwort über ein selbstreferenzielles Modell braucht, für den ist der Mechanismus dafür die iterative Berechnung mit einer expliziten Iterationsgrenze, behandelt in dem Artikel zur iterativen Berechnung, und sie gilt für echte Zyklen statt für Scan-Überlappungen
Die Regressionsgefahr, die im Fix verborgen liegt
LookupScan zu TXLSDepRange hinzuzufügen, brachte ein Risiko mit sich, das nichts mit Lookups zu tun hat und alles mit Pascal. TXLSDepRange ist ein nicht verwalteter Datensatz, also ist eine lokale Variable dieses Typs nicht null-initialisiert. Jede Stelle in der Codebasis, die einen von Hand baut, einschließlich der Data-Table-Abhängigkeitsblöcke und mehrerer Test-Helfer, musste daher aktualisiert werden, um das neue Feld explizit zu setzen. Verpasst man eine, entscheidet das Byte, das zufällig auf dem Stack lag, ob diese Referenz als Scan-Kante behandelt wird, was einen Neuberechnungs-Bug produziert, der mit unzusammenhängenden Codeänderungen erscheint und verschwindet
// Ein neues Boolean-Feld in einem nicht verwalteten Datensatz macht jede
// manuelle Baustelle zu einem latenten Bug. Zwei sichere Idiome:
var
R: TXLSDepRange;
begin
FillChar(R, SizeOf(R), 0); // alles nullen, dann eintragen
R.Sheet1 := SheetIndex;
R.Sheet2 := SheetIndex;
R.Row1 := Row; R.Col1 := Col;
R.Row2 := Row; R.Col2 := Col;
// oder jedes Feld, auch das neue, an jeder Stelle setzen
R.LookupScan := False;
end;Die allgemeine Regel, die das eingebracht hat: Ein Feld zu einem Datensatz hinzuzufügen, der an mehr als einer Handvoll Stellen auf dem Stack konstruiert wird, ist eine riskantere Änderung, als sie aussieht, und der Compiler hilft Ihnen nicht, die Stellen zu finden. Ist der Datensatz von einem Hot Path erreichbar, ziehen Sie einen Helfer vor, der ihn vollständig initialisiert, statt jeder Aufrufstelle zu vertrauen, dass sie aktualisiert wurde
Einen echten Zyklus von einer Scan-Überlappung unterscheiden
Nichts an dieser Änderung schwächt die Zykluserkennung. =B7+1 in B7 ist weiterhin ein Zyklus, eine Kette von drei Formeln, die sich selbst schließt, ist weiterhin ein Zyklus, und beide werden weiterhin über das Recalculation-Ergebnis gemeldet, wobei die Zyklusmitglieder ihre vorherigen gecachten Werte behalten, während alles außerhalb des Zyklus aktuell bleibt. Geändert hat sich nur, dass das Lookup-Array-Argument keine Zyklen mehr fabriziert, die Excel nicht sieht
Wer eine Arbeitsmappe auditiert und wissen will, welche Referenzen die Engine tatsächlich aufgelöst hat und in welcher Reihenfolge, für den ist der Auswertungs-Tracer das Werkzeug; der Artikel zum Formelauswertungs-Tracer erklärt, wie man seine Ausgabe liest. HotXLS ist eine native Delphi- und C++Builder-Tabellenkomponente, die XLS, XLSX, ODS und CSV liest und schreibt ohne installiertes Excel, und die Recalculation-Engine ist in jedem Format dieselbe; die aktuelle Funktions- und Engine-Abdeckung listet die Produktseite der HotXLS Delphi Tabellenkomponente