Die HotXLS Delphi Component wertet =1<2<3 als FALSE aus, dieselbe Antwort wie Excel 16, denn seit v2.384.3 faltet ihr Formel-Parser Vergleichsoperatoren von links nach rechts: 1<2 wird zu TRUE, und TRUE<3 ist FALSE, weil ein Boolean über jeder Zahl rangiert. Dasselbe Release macht einen leeren Operanden sowohl gleich 0 als auch gleich "" und lässt SUMIF einen Ein-Zell-Summenbereich auf die Form seines Kriterienbereichs dehnen. Jedes davon wirkt wie eine Kleinigkeit, bis eine in Delphi berechnete Arbeitsmappe derselben in Excel geöffneten Arbeitsmappe widerspricht
Die Meinungsverschiedenheit beginnt meist mit einer Formel, die jemand aus Intuition geschrieben hat. Jemand tippt =0<B2<100, um zu prüfen, dass eine Menge im Rahmen liegt, Excel antwortet still FALSE für jede Zeile, und das Sheet geht mit diesem Bug gebacken in Produktion. Eine Berechnungs-Engine darf die Absicht des Nutzers nicht reparieren; ihre Aufgabe ist es, den Wert zu liefern, den Excel liefern würde, damit das gecachte Ergebnis, das HotXLS in die Datei schreibt, dem entspricht, was Excel nach einer Neuberechnung zeigt. Vor v2.384.3 antwortete HotXLS auf diesen Bereichs-Check in jeder Zeile mit TRUE, falsch in die andere Richtung, und ein auf einem Server generierter Bericht widersprach demselben Bericht auf dem Desktop
Warum liefert =1<2<3 in Excel FALSE?
Excel liefert FALSE, weil es eine Kette von Vergleichen als (1<2)<3 liest, und das innere TRUE verliert dann den Typ-Ranking-Wettkampf gegen die Zahl 3. Der alte HotXLS-Parser las denselben Text als 1<(2<3): TXLSSyntax.Parse_expr in lxFormula.pas parste einen Operanden, sah ein Vergleichs-Token und stieg für die rechte Seite in Parse_expr ab, was den Operator rechtsassoziativ macht. Daraus wurde 1<TRUE, eine Zahl liegt unter einem Boolean, also war das Ergebnis TRUE. Der Fehler war symmetrisch: =3>2>1 ist in Excel TRUE und war in HotXLS FALSE, und =1=1=TRUE ist in Excel TRUE und war vor dem Fix FALSE. Der Regressionstest CalculateFormula_ComparisonChainsFoldLeftToRight pinnt sieben solcher Formeln gegen die Werte, die Excel 16 liefert, und führt jede durch beide Engine-Architekturen, das klassische TXLSWorkbook und das XLSX-native TXLSXWorkbook, über die Calculate-Methode, die im Überblick über die HotXLS-Formel-Engine beschrieben wird
const
Formulas: array [0..6] of string = ('=1<2<3', '=3>2>1', '=(1<2)<3',
'=1<(2<3)', '=1=1=TRUE', '=3>2>1=TRUE', '=1<2<3=FALSE');
// Was Excel 16 liefert: FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE
var
Classic: IXLSWorkbook;
Xlsx: TXLSXWorkbook;
i: Integer;
begin
Classic := TXLSWorkbook.Create;
Xlsx := TXLSXWorkbook.Create;
try
// TXLSXWorkbook.Calculate wertet gegen das aktive Sheet aus und
// liefert Null, wenn die Arbeitsmappe gar kein Sheet hat
Xlsx.Sheets.Add('Data');
for i := 0 to High(Formulas) do
Writeln(Formulas[i], ' classic=', VarToStr(Classic.Calculate(Formulas[i])),
' xlsx=', VarToStr(Xlsx.Calculate(Formulas[i])));
finally
Xlsx.Free;
end;
end;
Der Fix verwandelt Parse_expr in eine Schleife derselben Form, die Parse_expr1 bereits für +, - und & nutzt. Er parst den ersten Operanden mit Parse_expr1, und solange das nächste Token eines von =, <>, <, >, <= oder >= ist, erzeugt er einen Vergleichsknoten, hängt das akkumulierte linke Ergebnis als erstes Kind an, parst den nächsten Operanden mit Parse_expr1 statt mit Parse_expr und macht den neuen Knoten zum linken Ergebnis der nächsten Runde. Zwei Details waren beim Umbau von Rekursion in Iteration leicht falsch zu bekommen, und beide stehen in den Maintainer-Notizen: Der akkumulierte Knoten muss in dieser Reihenfolge übergeben werden (lChild := Item; Item := nil), und der Fehlerpfad muss nach dem Freigeben des halb gebauten Knotens Exit aufrufen, statt aus der Schleife zu fallen und einen herrenlosen Baum zurückzugeben
Wie rangieren Zahlen, Text und Booleans in einem HotXLS-Vergleich?
HotXLS rangiert gemischte Typen wie Excel: Jede Zahl ist kleiner als jeder Textwert, und jeder Textwert ist kleiner als jeder Boolean. TXLSCalculator.CompareVariants in lxCalc.pas klassifiziert beide Operanden mit GetRetValueType in die Aufzählung TXLSRetValueType = (xlVariantValue, xlNumberValue, xlStringValue, xlBooleanValue), und wenn die beiden Klassen differieren, vergleicht es schlicht ihre Ordinale – die Deklarationsreihenfolge dieser Enum ist also die Regel übergreifender Typvergleiche. Innerhalb einer Klasse ist der Vergleich der natürliche, mit einer Excel-spezifischen Wendung bei Text: Beide Strings laufen zuerst durch lxUpperCase, daher ist ="abc"="ABC" TRUE. Ohne dieses Ranking ist das Ketten-Ergebnis nicht zu begründen. TRUE<3 ist keine Umwandlung von TRUE zu 1, sondern ein Boolean, der mit einer Zahl verglichen wird, und der Boolean gewinnt. Datumswerte sind für die Engine Serial-Nummern (varDate klassifiziert als xlNumberValue), also liegt ein Datum stets unter jedem Text, einschließlich Text, der zufällig wie ein Datum aussieht
Womit ist eine Leerzelle in einem Vergleich gleich?
Eine als Vergleichsoperand genutzte Leerzelle ist gleich 0, wenn die andere Seite eine Zahl ist, gleich "", wenn die andere Seite Text ist, und seit v2.384.53 gleich FALSE, wenn die andere Seite ein logischer Wert ist – bei leerem A1 sind =A1=0, =A1="" und =A1=FALSE also alle TRUE. TXLSCalculator.CompareVarValues, das alle sechs Vergleichsoperatoren bedient, ersetzt die Leerzelle vor dem Aufruf von CompareVariants: Ist genau ein Operand Null, wird er zu WideString(''), wenn sein Partner ein String ist, zu False, wenn sein Partner ein Boolean ist, und ansonsten zu 0. Zwei Leerzellen vergleichen sich weiterhin ohne Substitution als gleich. Der Arithmetik-Pfad hatte eine Leerzelle stets in 0 verwandelt, daher lieferte =A1+1 die 1, aber CompareVariants behielt Null als eigenen untersten Rang, unter jeder Zahl, und die Vergleichsoperatoren nutzten diesen Rang direkt
var
Wb: IXLSWorkbook;
Sh: TXLSWorksheet;
begin
Wb := TXLSWorkbook.Create;
Sh := Wb.Sheets.Add;
Sh.Range['B1', 'B1'].Value := 5; // A1 bleibt absichtlich leer
Writeln(VarToStr(Wb.Calculate('=A1=0'))); // True
Writeln(VarToStr(Wb.Calculate('=A1=""'))); // True
Writeln(VarToStr(Wb.Calculate('=A1<B1'))); // True: die Leerzelle vergleicht als 0
Writeln(VarToStr(Wb.Calculate('=A1<0'))); // False; vor v2.384.3 True
end;
Die letzte Zeile ist die, die in der Praxis wehgetan hat. Unter dem alten Rang war eine Leerzelle kleiner als jede Zahl, negative eingeschlossen, also markierte =IF(A1<0,"overdrawn","ok") jede leere Saldozelle als überzogen, und =A1=0 war FALSE für eine Zelle, die jeder Nutzer als null beschrieben hätte. Eine Grenze blieb nach v2.384.3: Die Substitution wählte nur zwischen 0 und dem leeren String, also wurde eine mit einem Boolean verglichene Leerzelle zu 0, was unter sowohl TRUE als auch FALSE rangiert, und =A1=FALSE auf leerem A1 ergab FALSE. Seit HotXLS 2.384.53 wird eine mit einem logischen Wert verglichene Leerzelle in beiden Engines, XLS wie XLSX, als FALSE behandelt, wie Excel es tut: Bei leerem A1 liefern =A1=FALSE und =A1<TRUE TRUE und =A1=TRUE liefert FALSE. Das bedeutet auch, dass der Vergleich eine Leerzelle nicht von FALSE unterscheiden kann, in Excel wie in HotXLS; braucht ein Sheet diese Unterscheidung, testen Sie mit ISBLANK oder =A1=""
Warum lieferte SUMIF mit Ein-Zell-Summenbereich 0?
SUMIF lieferte 0, weil HotXLS die Iteration auf den kleineren der beiden Bereiche klemmte, während Excel die Form des Kriterienbereichs beibehält und den Summenbereich nur für dessen obere linke Zelle nutzt. =SUMIF(A1:A10,">5",B1) bedeutet in Excel also B1:B10, ein Komfort, auf den viele handgebaute Templates setzen. Der gemeinsame Worker TXLSCalculator.GetValueItemRange2 verkleinerte seine Zeilen- und Spaltenzahlen auf die des Wertebereichs, was das Beispiel auf einen einzigen Test von A1 gegen B1 reduzierte. v2.384.3 entfernt die Klemme: Die Schleife läuft jetzt den Kriterienbereich ab und liest jeden Wert mit demselben Offset von der oberen linken Ecke des Summenbereichs. Da CalcSumIF und CalcAverageIF beide diesen Worker rufen, bekommt AVERAGEIF dieselbe Aufweitung, und ein größerer Summenbereich wird aus demselben Grund auf die Kriterienform zugeschnitten. Das Kriterien-Argument in der Mitte ist wertklassig, die äußeren beiden sind referenzklassig – die Unterscheidung, die im Artikel über implizite Intersection und Argumentklassen behandelt wird
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Row: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Sales');
for Row := 1 to 10 do
begin
Sheet.Cells[Row, 1].Value := Row; // Kriterien-Spalte: 1..10
Sheet.Cells[Row, 2].Value := Row * 100; // Beträge: 100..1000
end;
Sheet.Cells[1, 4].Formula := '=SUMIF(A1:A10,">5",B1)'; // Ein-Zell-Summenbereich
Sheet.Cells[2, 4].Formula := '=SUMIF(A1:A10,">5",B1:B10)'; // expliziter Summenbereich
if Book.Recalculate = lxOk then
// D1 und D2 sind beide 4000 (600+700+800+900+1000); D1 war vor v2.384.3 0
Writeln(VarToStr(Sheet.Cells[1, 4].Value), ' ', VarToStr(Sheet.Cells[2, 4].Value));
finally
Book.Free;
end;
end;
INDIRECT und YEARFRAC: zwei leisere Korrekturen
INDIRECT ehrt jetzt sein zweites Argument, und Text hinter einer gültigen Referenz ist ein Fehler, statt ignoriert zu werden. Bei a1 FALSE wird der Text als absolute R1C1 geparst, also liest =INDIRECT("R2C3",FALSE) C2; der alte Code ignorierte das Flag, las „R2“ als Spalte R, Zeile 2, und lieferte stumm die falsche Zelle. Das Flag wird über seinen Variant-Typ dispatcht (Boolean, Zahl oder Text), weil die direkte Umwandlung eines String-Variants in Double eine Exception wirft. Relativer R1C1-Text wie R[1]C[1] liefert #REF!, weil INDIRECT keinen Formelzellen-Ursprung hat, gegen den er auflösen könnte, und A1-Text mit angehängten Zeichen, "B2 junk", liefert ebenfalls #REF!. YEARFRAC mit Basis 0 wendet jetzt die NASD-Ende-des-Februar-Regeln an, die DAYS360 bereits implementiert hatte: Liegen beide Daten am letzten Februar-Tag, wird der Endtag zu 30, danach ein am letzten Februar-Tag liegender Start zu 30. Von 2024-02-29 bis 2025-02-28 zählt die Funktion jetzt 360 Tage, ein Bruchteil von exakt 1, wo das bisherige Days360US 359 zählte
Was garantieren diese Fixes, und was ist die Lehre?
Das Vergleichsketten-Verhalten ist durch einen Test abgesichert, der beide Engines gegen in Excel 16 gemessene Werte stellt, und dieser Test existiert, weil die erste Beschreibung des Fixes falsch war. Die Release-Notiz zu v2.384.3 behauptete ursprünglich, das Folding von links nach rechts mache =1<2<3 zu TRUE – genau das, was der alte rechtsassoziative Parser produzierte und das Gegenteil dessen, was sowohl Excel als auch der neue Code liefern. Niemand hatte das Beispiel ausgeführt; es war aus der Intuition heraus geschrieben, „1 ist kleiner als 2 ist kleiner als 3“. Die Notiz wurde korrigiert und der Sieben-Formeln-Test in einem Follow-up-Commit ergänzt, und die Regel, die daraus fiel, gilt für jeden, der Tabellenkalkulations-Semantik dokumentiert: Führen Sie das Beispiel in Excel aus, bevor Sie den erwarteten Wert aufschreiben. Die Leerzellen-Substitution und die SUMIF-Aufweitung folgen demselben Excel-Verhalten, seit v2.384.53 auch der Fall Leerzelle gegen Boolean, und bedingte Aggregate, die zusätzlich gefilterte oder ausgeblendete Zeilen überspringen müssen, folgen den separaten Regeln aus dem Artikel zu SUBTOTAL und AGGREGATE bei ausgeblendeten Zeilen
HotXLS ist eine native Delphi- und C++Builder-Tabellenkomponente, die XLS, XLSX, ODS und CSV ohne installiertes Excel liest, neu berechnet und schreibt, und die hier beschriebenen Vergleichs-, Leerzellen- und SUMIF-Regeln stecken in der Berechnungs-Engine, die beide Arbeitsmappen-Architekturen teilen. Die vollständige Funktionsliste und die Lizenzoptionen finden Sie auf der Produktseite der HotXLS Delphi spreadsheet component