Excel-Formel-Caches mit HotXLS Deep Recalc prüfen

HotXLS beantwortet die Frage, die jede Spreadsheet-Pipeline irgendwann stellen muss: Stimmen die in einer Arbeitsmappe gespeicherten Zahlen noch mit den Formeln überein, die sie erzeugt haben? CalculateAndVerify berechnet den ganzen Abhängigkeitsgraphen in ein isoliertes Overlay neu, vergleicht jedes Ergebnis mit dem Cache-Wert, der bereits in der Zelle steht, und meldet die Abweichungen. Standardmäßig ändert es nichts

Der Grund, warum das zählt: Eine Spreadsheet-Datei speichert pro Formelzelle zwei Dinge — die Formel und den letzten Wert, den irgendwer für sie berechnet hat. Excel hält beide synchron. Alles andere auf der Welt vielleicht nicht. Eine Datei, die durch eine ältere Bibliothek, eine teilweise Neuberechnung, einen handeditierten XML-Teil oder ein Tool ging, das Werte schrieb, ohne sie neu zu berechnen, präsentiert fröhlich eine Summe, die nicht mehr aus ihren Eingaben folgt — und nichts im Dateiformat markiert das

Warum ist ein von seiner Formel abweichender Cache-Wert so gefährlich?

Weil er in jedem gewöhnlichen Lesepfad unsichtbar ist. Datei in einem Viewer öffnen, Zelle über eine API lesen, nach CSV oder PDF exportieren — man bekommt die Cache-Zahl. Die Formel steht direkt in derselben Zelle, und niemand vergleicht sie. Die Abweichung taucht erst auf, wenn jemand die Arbeitsmappe in Excel öffnet, das unter den meisten Einstellungen beim Laden neu rechnet — und plötzlich zeigt ein Report, der im letzten Quartal abgenickt wurde, andere Summen

Das Audit existiert, um aus diesem Vergleich eine bewusste, eingeplante Operation zu machen statt eines Zufalls. Es ist das Spreadsheet-Äquivalent einer Prüfsummenkontrolle: billig genug für eine Eingangs-Pipeline, und das Einzige, was aus einem stillen Datenintegritätsproblem einen Report macht, mit dem man etwas anfangen kann

var
  Book: TXLSWorkbook;
  Options: TXLSRecalcAuditOptions;
  Report: TXLSCalculationAuditReport;
  I: Integer;
begin
  Book := TXLSWorkbook.Create(nil);
  try
    Book.LoadFromFile('quarterly-close.xls');
    Options := TXLSRecalcAuditOptions.Default;
    Options.MaxIssues := 500;
    Report := Book.CalculateAndVerify(Options);
    try
      for I := 0 to Report.Count - 1 do
        if Report[I].Kind = xlcaiCacheMismatch then
          Writeln(Report[I].SheetName, '!',
                  Report[I].Row, ':', Report[I].Col, '  ',
                  Report[I].Formula,
                  '  cached=', VarToStr(Report[I].Actual),
                  '  recomputed=', VarToStr(Report[I].Expected));
      if Report.Truncated then
        Writeln('issue budget reached, raise MaxIssues');
    finally
      Report.Free;
    end;
  finally
    Book.Free;
  end;
end;

Es gibt drei Überladungen, und sie beantworten drei verschiedene Fragen. Das parameterlose CalculateAndVerify liefert eine Abweichungszahl, mehr braucht ein Health Check nicht. Die Überladung mit einem out-Array der Abweichungen gibt die Zellen. Die Überladung mit TXLSRecalcAuditOptions gibt einen vollständigen TXLSCalculationAuditReport zurück — die richtige, wenn man nicht nur wissen will, dass ein Wert abweicht, sondern warum das Audit etwas nicht auswerten konnte

Das Overlay, und warum das Audit nicht schreibt

Jeder neu berechnete Wert landet in einem Overlay statt im Zellcache, und das Overlay wird in beiden Workbook-Engines ganz vorne in den Cell-Read-Callback injiziert. Diese Platzierung macht das Audit selbstkonsistent: Wird B1 neu berechnet und C1 hängt von B1 ab, sieht C1 den Wert aus diesem Audit-Durchlauf, nicht den alten Cache-Wert. Ohne das würde ein einzelner Upstream-Fehler einmal gemeldet und dann absorbiert, und jede Downstream-Zelle würde scheinbar mit einer falschen Eingabe übereinstimmen

Zellen, deren neu berechneter Wert zum Cache passt, betreten das Overlay gar nicht. Das ist keine Mikro-Optimierung, sondern das, was das Audit bezahlbar hält. Eine saubere Arbeitsmappe mit hunderttausend Formeln macht null Overlay-Schreibvorgänge, und der Durchlauf bleibt innerhalb eines 1,35x-Budgets gegenüber einer vollen Neuberechnung — der Unterschied zwischen etwas, das man bei jedem Eingang laufen lassen kann, und etwas, das man einmal im Quartal anwirft

Die Auswertung folgt einer seriellen topologischen Ordnung aus dem Abhängigkeitsgraphen, wobei jeder Knoten zuerst als dirty markiert wird, sodass jede Zelle genau einmal nach ihren Eingaben berechnet wird. Wer die inkrementelle Mechanik will, die eine lebendige Arbeitsmappe aktuell hält, statt eine gespeicherte zu auditieren, meint einen anderen Mechanismus — beschrieben in inkrementeller Neuberechnung und dem Abhängigkeitsgraphen

Fehler werden klassifiziert, nicht in einen Topf geworfen

Eine Zelle, die das Audit nicht auswerten kann, ist ein anderer Befund als eine Zelle, deren Wert abweicht, und TXLSCalculationAuditIssueKind hält die Kategorien auseinander. xlcaiCacheMismatch ist die Wertabweichung. xlcaiMissingFunction und xlcaiMissingName sagen, dass der Evaluator auf etwas traf, das er nicht implementiert oder nicht auflösen kann. xlcaiUnsupportedArguments deckt Argumentformen außerhalb der unterstützten Teilmenge ab. xlcaiExternalReferenceDenied und xlcaiExternalReferenceMissing trennen eine Policy-Verweigerung von einer fehlenden Arbeitsmappe. xlcaiCircularReference, xlcaiDataTableSkipped, xlcaiParseFailure, xlcaiCancelled und xlcaiInternalFailure vervollständigen das Set

Ein Unterschied verdient die Erwähnung, weil er eine gängige Annahme umkehrt. Ein positiver Excel-Fehlercode ist ein Ergebnis, kein Fehler. Eine Zelle, die legitim zu #DIV/0! auswertet, hat korrekt gerechnet, also speichert das Audit diesen Fehler im Overlay und vergleicht ihn mit dem Cache wie jeden anderen Wert. Eine Arbeitsmappe voller absichtlicher Fehlerzellen liefert null Befunde, und eine Arbeitsmappe, in der seit die Werte gecacht wurden ein Fehler aufgetaucht oder verschwunden ist, liefert exakt die Befunde, die man will

Zirkelbezüge bekommen ihre eigene Behandlung. Knoten in einem Zyklus gelangen nie in die topologische Ordnung, also wird jeder einzeln als xlcaiCircularReference gemeldet, und das Audit lässt den iterativen Solver nicht laufen. Das ist ein bewusster Read-only-Vertrag: Ob Iteration eingeschaltet ist, beeinflusst, wie der Ergebniscode zu interpretieren ist, nicht was das Audit tut. Die Mechanik der iterativen Auswertung behandelt separat iterative Berechnung und Zirkelbezüge

Eine Fehlerkette lesen

Wenn eine Formel bei der Auswertung scheitert, reicht das Wissen, welche Zelle scheiterte, selten, denn der Fehler steckt meist drei Ebenen tief in einer Kette von Referenzen. Jedes Issue trägt deshalb einen Stack-String, der den äußersten Frame zuerst rendert, in der Form Sheet1!A1 > Sheet1!B2 > Data!C7 — der Report zeigt also auf die Zelle, die tatsächlich brach, nicht auf die, die man zufällig ansah

Der Rekorder ist begrenzt. MaxStackFrames steht per Default auf 64 mit einer Untergrenze von 8, und die tiefste fehlschlagende Kette ist die, die behalten wird: Ein innerer Frame zeichnet die Kette auf, wenn der Fehler dort entsteht, und äußere Frames, die danach abwickeln, überschreiben sie nicht. Hat irgendeine Kette das Budget überschritten, ist Report.StackTruncated gesetzt — das unterscheidet eine kurze Kette von einer, die man nicht ganz gesehen hat

// Standardmäßig read-only. ApplyResults committet das Overlay erst nach
// einem vollständig erfolgreichen Audit, unter einer Write-Guard, die den
// Commit ablehnt, wenn sich die Workbook-Struktur während des Audits änderte
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0;     // exakter Vergleich, macht Drift sichtbar
Options.RelativeTolerance := 0;
Options.OnProgress := HandleProgress;

Report := Book.CalculateAndVerify(Options);
try
  if Report.Applied then
    Book.SaveToFile('quarterly-close-repaired.xls')
  else
    Writeln('not applied: ', Report.Count, ' issues blocked the commit');
finally
  Report.Free;
end;

procedure THarness.HandleProgress(ASender: TObject;
  ACurrent, ATotal: Integer; var ACancel: Boolean);
begin
  ACancel := FUserRequestedStop;   // das Audit stoppt an der nächsten Knotengrenze
end;

Wann darf das Audit die Arbeitsmappe reparieren?

Nur wenn das Audit vollständig frei von Fehlerklassen-Issues zurückkam — genau die Bedingung, die ApplyResults für Sie durchsetzt. Der Commit passiert nach einem vollständig erfolgreichen Durchlauf, der nicht abgebrochen wurde, und passiert eine strukturelle Guard: Die Binary-Engine beobachtet einen Workbook-Änderungsidentifikator, die OOXML-Engine macht einen Schnappschuss der Strukturgeneration pro Worksheet. Hat sich während des Audits irgendwas bewegt, beschreiben die Ergebnisse eine Arbeitsmappe, die nicht mehr existiert, und der Commit wird verweigert

Beachten Sie die bewusste Asymmetrie. Cache-Abweichungen blockieren die Anwendung nicht, denn sie sind genau das, was der Commit reparieren soll. Fehlerklassen-Issues blockieren sie wohl, denn eine Arbeitsmappe, in der manche Formeln nicht ausgewertet werden konnten, würde halb repariert — und eine halb reparierte Arbeitsmappe ist schlimmer als eine unreparierte, der man zu misstrauen weiß

Toleranz ist eine Policy-Entscheidung, kein Default

Der Standardvergleich ist eine absolute Toleranz von 1E-6 mit deaktivierter relativer Toleranz, was das klassische Verhalten bewahrt und still eine Drift von 4E-7 akzeptiert. Meistens ist das richtig: Unterschiede in der Gleitkomma-Auswertungsreihenfolge zwischen dem, was die Datei erzeugt hat, und dem aktuellen Evaluator produzieren bei langen Summen Abweichungen genau dieser Größe, und sie als Integritätsbefunde zu melden ist Rauschen

Beide Toleranzen auf null setzen, wenn die Frage eine andere ist — wenn man herausfinden will, ob ein Evaluator zwischen Versionen sein Verhalten geändert hat, oder ob ein Drittanbieter-Tool Werte auf subtil andere Weise umschreibt. Bei null wird dieselbe 4E-7-Drift sichtbar, und alles andere auch. Wählen Sie die Toleranz nach der Frage, die Sie stellen, und halten Sie die Entscheidung neben dem Report fest, denn ein Report ohne seine Toleranz ist nicht interpretierbar

Zwei Nachbarfähigkeiten vervollständigen das Bild. Wer wissen will, warum eine einzelne Formel den Wert produziert, den sie produziert, greift zur Schritt-für-Schritt-Ansicht in dem Formelauswertungs-Tracer. Wer bewusst Cache-Werte ohne jede Neuberechnung gelten lassen will, etwa auf einem Eingangspfad, der die Datei exakt so reproduzieren muss, wie sie ankam, findet diesen Modus in Cache-Formelwerte ohne Neuberechnung lesen beschrieben. Das Audit sitzt zwischen beiden: Es sagt Ihnen, ob dem Cache zu trauen sicher ist. Es liegt der HotXLS Delphi spreadsheet component bei, für die Binary- wie für die OOXML-Engine