Excel verbirgt einen kleinen Debugger in aller Öffentlichkeit. Wählen Sie eine Zelle aus, öffnen Sie „Formeln“ und klicken Sie auf „Formelauswertung“, und ein Dialogfeld zeigt die Formel mit einem unterstrichenen Teilausdruck an. Drücken Sie auf „Auswerten“, und dieser Teilausdruck schrumpft auf seinen Wert zusammen, dann wird der nächste unterstrichen, und Sie können beobachten, wie ein langer Ausdruck durch eine Reduzierung nach der anderen auf eine einzige Zahl schrumpft. Es ist der schnellste Weg, um herauszufinden, welcher Zweig einer verschachtelten IF-Funktion tatsächlich ausgeführt wurde oder welche Referenz eine falsche Summe geliefert hat. HotXLS reproduziert genau dieses Verhalten durch TXLSFormulaTracer, sodass ein Delphi- oder C++Builder-Programm dieselbe Schrittliste zum Überprüfen einer Arbeitsmappe, zum Debuggen einer generierten Formel oder zum Erklären, warum ein Ergebnis so ausgefallen ist, wiedergeben kann. Jeder aufgezeichnete Schritt enthält den Text des Teilausdrucks und den Wert, auf den er reduziert wird
Wie die Reduzierungs-Engine den Ausdruck durchläuft
Der Tracer greift nicht in die Berechnungs-Engine ein. Er zerlegt die Formel in Token und analysiert sie mit einem Recursive-Descent-Parser, dann reduziert er den Baum tiefenzuerst, wobei der am weitesten innen liegende, auswertbare Teilausdruck zuerst verarbeitet wird. Wenn ein Knoten auf einen Wert reduziert wird, wird dieser Wert als Literal wieder in den umgebenden Ausdruck eingesetzt, und die Engine fordert den echten Rechner auf, den nun einfacheren Ausdruck neu zu berechnen. Da jeder Schritt über die öffentliche Methode Calculate des Arbeitsblatts und nicht über eine private Abkürzung ausgewertet wird, stimmt jeder Schritt exakt mit dem überein, was eine vollständige Neuberechnung der Zelle ergeben würde. Der Parser ist absichtlich nicht-invasiv konzipiert, wodurch er auf jedes Arbeitsblatt angewendet werden kann, ohne dessen Zustand zu stören
Der Parser folgt einer Leiter der Operatorrangfolge, mit einer rekursiven Ebene pro Prioritätsband. Von der niedrigsten bis zur höchsten Bindung lauten die Bänder: Stufe 0 Vergleich (=, <>, <, >, <=, >=), Stufe 1 Zeichenfolgenverkettung (&), Stufe 2 Addition und Subtraktion, Stufe 3 Multiplikation und Division, Stufe 4 Potenzierung und schließlich darunter das unäre Plus und Minus. Jede Stufe parst die Stufe darüber nach ihren Operanden, sodass ein höheres Band enger bindet. Dies ist dieselbe Rangfolge, die auch Excel anwendet, weshalb A1*B1+A2*B1 die beiden Produkte vor der Summe reduziert: Die Multiplikation befindet sich auf Stufe 3, die Addition auf Stufe 2, sodass die Multiplikationen tiefer im Baum liegen und zuerst reduziert werden
Eine Formel verfolgen und die Schritte durchlaufen
Die Verwendung spiegelt das mitgelieferte Demo unter Demo/Delphi/FormulaTrace/FormulaTrace.dpr wider. Erstellen Sie ein Arbeitsblatt (oder öffnen Sie eine vorhandene Arbeitsmappe), konstruieren Sie einen Tracer über das Blatt, rufen Sie Trace auf und iterieren Sie das zurückgegebene Array. Jeder TXLSFormulaStep stellt Depth für die Einrückung, Source für den ursprünglichen Teilausdruck, Expression für diesen Teilausdruck mit seinen bereits eingesetzten Operanden und Value für das Ergebnis des Schrittes zur Verfügung
uses
SysUtils, Variants, lxHandle, lxHandleX, lxFormulaTrace;
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Tracer: TXLSFormulaTracer;
Steps: TXLSFormulaStepArray;
Final: Variant;
I: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Order');
Sheet.Cells[1, 1].Value := 10; // A1 units
Sheet.Cells[1, 2].Value := 25; // B1 unit price
Sheet.Cells[1, 3].Value := 0.08; // C1 tax rate
Tracer := TXLSFormulaTracer.Create(Sheet);
try
Final := Tracer.Trace('A1*B1*(1+C1)', Steps);
for I := 0 to High(Steps) do
Writeln(StringOfChar(' ', Steps[I].Depth * 2),
Steps[I].Source, ' -> ', Steps[I].Expression,
' = ', VarToStr(Steps[I].Value));
Writeln('result = ', VarToStr(Final));
finally
Tracer.Free;
end;
finally
Book.Free;
end;
end;Die Zellbezüge werden zuerst aufgelöst und erscheinen als eigene Schritte, dann reduzieren sich die Produkte, dann der eingeklammerte Steuerfaktor, und die finale Multiplikation schließt das Ganze ab. Das Feld Depth ermöglicht es Ihnen einzurücken, sodass die am weitesten innen liegenden Reduzierungen optisch am tiefsten sitzen, genau wie Excel den am weitesten innen liegenden Begriff unterstreicht, bevor ein äußerer Begriff an die Reihe kommt
Die lokalisierungsfreie Literal-Falle
Das gefährlichste Detail in diesem gesamten Schema ist auf einem englischen Rechner unsichtbar und geht auf einem deutschen Rechner lautstark kaputt. Wenn eine berechnete Zahl wieder in den Formeltext eingesetzt wird, muss sie als Zeichenfolge geschrieben und dann von der Berechnungs-Engine, die . als Dezimalpunkt behandelt, neu geparst werden. Wenn die Einsetzung das Gebietsschema des Systems verwenden würde, würde ein deutsches TFormatSettings 1,08 für den Steuerfaktor schreiben, das Komma würde als Argumenttrennzeichen gelesen werden, und die Neuberechnung von A1*B1*1,08 würde entweder in die falsche Form geparst werden oder komplett fehlschlagen
Der Tracer vermeidet dies, indem er jedes numerische Literal durch ein privates TFormatSettings formatiert, das er bei der Erstellung festlegt, wobei DecimalSeparator auf . erzwungen und ThousandSeparator auf #0 gesetzt wird, sodass niemals ein Gruppierungszeichen ausgegeben wird. FloatToStr erzeugt dann ein Literal, das die Engine immer zurücklesen kann, unabhängig von den regionalen Einstellungen des Bedieners
// Conceptually what the tracer pins once, at construction
FFloatFmt := FormatSettings;
FFloatFmt.DecimalSeparator := '.';
FFloatFmt.ThousandSeparator := #0;
// every reduced number is written with: FloatToStr(Double(V), FFloatFmt)Dies ist die Art von Fehler, die bei den eigenen Tests des Autors nie auftritt und erst ans Licht kommt, wenn ein Kunde in einem anderen Gebietsschema denselben Code ausführt. Daher ist es wert, es klar und deutlich auszusprechen: Das Hin- und Herschieben eines Wertes durch Formeltext ist ein Serialisierungsproblem, und Serialisierung muss lokalisierungsfrei sein
Boolesche Werte reduzieren sich auf 1 und 0
Eine verwandte Einsetzungsentscheidung betrifft logische Werte. Wenn ein Teilausdruck zu einem booleschen Wert ausgewertet wird, schreibt ihn der Tracer als 1 oder 0 zurück, nicht als TRUE oder FALSE. Der Grund dafür ist, dass das reduzierte Literal im jeweiligen umgebenden Kontext sauber neu geparst werden muss, und Arithmetik ist hier der anspruchsvolle Fall. Wenn ein Vergleich wie A1>A2 auf den Text TRUE reduziert werden würde und dieser Text innerhalb von TRUE*B1 landen würde, würde die Neuberechnung davon abhängen, dass die Engine ein nacktes boolesches Schlüsselwort in einer Multiplikation akzeptiert. Das Ersetzen durch 1 umgeht die Frage vollständig, da 1*B1 an jeder arithmetischen Position eindeutig ist. Es entspricht auch der eigenen Typumwandlung von Excel, bei der sich TRUE wie 1 und FALSE wie 0 verhält, sobald eine Zahl erwartet wird
Funktionsaufrufe reduzieren sich atomar
Eine naive Schritt-Engine würde zuerst die Argumente einer Funktion reduzieren und dann den Aufruf. Das ist für Excel falsch, und der Tracer tut es absichtlich nicht. Ein Funktionsaufruf wird als Ganzes anhand seines Originaltextes in einem einzigen Schritt ausgewertet. Der Grund dafür ist die Kurzschlussauswertung (Short-Circuit Semantics). IF, CHOOSE und IFERROR werten nur den Zweig aus, den sie auswählen, und wenn man die Argumente zuerst reduzieren würde, wäre die Engine gezwungen, Zweige zu berechnen, die Excel niemals berührt. Das klassische Opfer ist ein Schutz vor einer Division durch Null wie IF(B1=0,0,A1/B1): Wenn der Tracer A1/B1 reduzieren würde, bevor das IF ausgewertet wird, würde der Schutz ins Leere laufen und genau den Fehler auslösen, den er eigentlich verhindern soll. Durch die atomare Auswertung des gesamten Aufrufs bewahrt der Tracer die verzögerte Auswertung (Lazy Evaluation), die solche Schutzmechanismen funktionsfähig macht
// IF is one atomic step; only the selected branch is evaluated
Final := Tracer.Trace('IF(A1>A2,A1*B1,A2*B1)', Steps);
// A1>A2 is true, so the step records A1*B1 as the chosen result;
// A2*B1 is never computed, exactly as Excel would do it.Der Kompromiss besteht darin, dass Sie nicht in den Funktionsaufruf als separate Schritte hineinsehen können, aber das ist das korrekte Verhalten. Die Anzeige von Argumentreduzierungen, die Excel niemals durchführt, wäre eine irreführendere Ablaufverfolgung, als den Aufruf als die einzelne Auswertungseinheit zu behandeln, die er in Wirklichkeit ist
Argumenttrennzeichen und intakte Bereiche
Zwei weitere Normalisierungen halten die Neuberechnung ehrlich. Der Compiler der Berechnungs-Engine erwartet ; als Trennzeichen für Funktionsargumente. Wenn der Tracer also einen Funktionsaufruf aus seinem geparsten Baum wieder aufbaut, verbindet er die Argumente mit ;, auch wenn der Benutzer ursprünglich , eingegeben hat. Eine als SUM(A1,A2,A3) geschriebene Formel wird als SUM(A1;A2;A3) neu berechnet, was die Engine akzeptiert. Das Einsetzen von Werten macht diesen Wiederaufbau überhaupt erst erforderlich, und das richtige Trennzeichen sorgt dafür, dass der Wiederaufbau fehlerfrei geparst wird
Bereichsbezüge sind der andere Fall. Ein Bereich wie A1:A3 ist kein Skalar und darf nicht in drei separate Werte aufgeteilt werden, da die Funktion, die ihn konsumiert, ein Bereichsargument erwartet. Der Tracer belässt einen Bereich intakt in seinem Originaltext und lässt die umschließende Funktion als Ganzes reduzieren. In SUM(A1:A3)*B1 bleibt der Bereich ganz, SUM(A1:A3) reduziert sich in einem einzigen atomaren Schritt auf eine Zahl, und erst dann wird die äußere Multiplikation ausgeführt. Dies ist dieselbe Grenze, die Excel zwischen einem Bereichsoperanden und dem Skalar zieht, den er letztendlich beisteuert
// The range A1:A3 is never split; SUM is one atomic reduction,
// then the product with B1 reduces on top of it.
Final := Tracer.Trace('SUM(A1:A3)*B1', Steps);
for I := 0 to High(Steps) do
Writeln(Steps[I].Source, ' = ', VarToStr(Steps[I].Value));Zusammengenommen machen diese Regeln die Schrittliste zu einem getreuen Spiegelbild des Excel-Befehls Formelauswertung und nicht nur zu einer bloßen Annäherung daran. Die Reduzierungen erfolgen in der Reihenfolge, in der Excel sie durchführt, die eingesetzten Literale überstehen jedes Gebietsschema, boolesche Werte werden so umgewandelt wie in Excel, und Lazy Functions bleiben faul. Wenn Sie die Engine mit Ihren eigenen Funktionen weiter antreiben möchten, zeigt Ihnen der Artikel über Formel-Engine und benutzerdefinierte Funktionen, wie Sie diese registrieren können, und für schwerere numerische Arbeiten deckt der Artikel über statistische Verteilungsfunktionen in Delphi die integrierte Bibliothek ab, gegen die der Tracer auswertet. All dies wird als Teil der HotXLS-Tabellenkalkulationskomponente für Delphi und C++Builder ausgeliefert, zusammen mit den Lese-, Schreib-, Formatierungs- und Berechnungs-APIs, die an anderer Stelle in diesem Blog behandelt werden