Liefern SUBTOTAL(109, ...) und SUBTOTAL(9, ...) dieselbe Zahl in einem Workbook mit ausgeblendeten Zeilen, ist eines der beiden falsch. HotXLS, die native Excel-Tabellenkalkulationskomponente für Delphi und C++Builder, verhielt sich bis Version 2.197.0 exakt so, weil ihre Berechnungs-Engine keine Möglichkeit hatte, ein Arbeitsblatt zu fragen, ob eine bestimmte Zeile ausgeblendet war
Das Symptom kommt selten als Bugreport über Formelcodes an. Es kommt als Diskrepanz an: Ein Batch-Job auf dem Server berechnet eine Summe, ein Nutzer öffnet dieselbe Datei in Excel mit angewendetem Filter, und die beiden Zahlen unterscheiden sich um genau das, was die herausgefilterten Zeilen zufällig summierten. Niemand verdächtigt die Aggregationsfunktion, weil die Formel-Zeichenkette in der Zelle an beiden Stellen identisch ist. Der Unterschied liegt gänzlich darin, was der Auswerter sehen durfte
Warum bezieht SUBTOTAL 109 ausgeblendete Zeilen ein?
Weil in den meisten Engine-Designs die Schicht, die eine Formel auswertet, nie von der Zeilensichtbarkeit erfährt. HotXLS war ein Lehrbuchfall: Die Berechnungs-Engine in lxCalc.pas erreichte Zellwerte über einen einzigen TXLSGetValue-Callback, der auf ein (Sheet-, Zeilen-, Spalten-)Tripel mit einem Wert antwortet und sonst nichts. Sichtbarkeit ist ein Präsentationsattribut, gespeichert im Zeilendatensatz, und kein Teil dieses Datensatzes reiste die Aufrufkette hinab. Die Engine hatte daher einen einzigen Aggregationspfad, und beide Hälften der SUBTOTAL-Funktionsnummerntabelle lösten dorthin auf. Das ist kein Defekt der Kategorie Rundungsfehler: Es ist der ganze Grund, warum die zweite Hälfte der Tabelle existiert. ECMA-376 Teil 1, veröffentlicht als ISO/IEC 29500-1, definiert SUBTOTAL in seinen Formelfunktions-Definitionen (§18.17.7) mit einem ersten Argument, das sowohl die innere Aggregation als auch die Richtlinie für ausgeblendete Zeilen wählt. Codes 1 bis 11 bilden auf AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR und VARP ab, wobei Werte auf manuell ausgeblendeten Zeilen einbezogen werden. Codes 101 bis 111 wählen dieselben elf Aggregationen und schließen sie aus. Ein Nutzer, der 109 statt 9 eintippt, macht eine bewusste Aussage über ausgeblendete Daten, und eine Engine, die die Unterscheidung einebnet, übergeht diese Aussage still
Wohin die Funktionsnummern in der Engine abbilden
HotXLS löst das erste SUBTOTAL-Argument in CalcSubtotalFunc auf, das die Codes 101 bis 111 auf dieselben inneren Funktionsbezeichner wie die Codes 1 bis 11 normalisiert und dann anhand der Aggregation selbst dispatcht. Der Großteil der Familie fließt durch den inkrementellen ExcelSum-Akkumulator, denjenigen, der SUM, COUNT, COUNTA, MIN, MAX und AVERAGE behandelt. Fünf davon können das nicht: STDEV, VAR, STDEVP, VARP und PRODUCT brauchen einen geschlossenen Durchlauf über die Daten, sodass CalcSubtotalFunc die inneren Codes 12, 46, 193, 194 und 183 an einen separaten Reducer weiterleitet, SubtotalReduceVariance. Diese Aufteilung ist das Erste, was man abbilden sollte, bevor man irgendetwas anfasst, denn zwei unabhängige Aggregationspfade bedeuten zwei unabhängige Zellendurchlaufschleifen, und ein Fix, der nur auf einen davon angewendet wird, erzeugt das denkbar schlechteste Ergebnis: SUBTOTAL(109, ...) respektiert den Filter, während SUBTOTAL(107, ...) auf demselben Bereich es nicht tut. Beim Zählen der Schleifen in HotXLS kamen sechs davon zum Vorschein, sobald AGGREGATE einbezogen wurde, verteilt über Bereichsauswertung, schlichte Bereichssammlung und drei separate Reducer
Warum ein Scratch-Feld statt sechs neuer Signaturen?
Weil einen neuen Parameter durch sechs Zellendurchlauf-Funktionen zu fädeln, plus alles, was sie aufruft, eine breite Änderung an einem heißen Code-Pfad um eines einzigen Booleans willen ist. HotXLS hatte bereits einen Präzedenzfall für die Alternative: ein transientes Feld auf dem Kalkulator, im selben Geist wie das Scratch-Feld, das GetRangeInfo nutzt, um zu vermerken, wann eine 3D-Referenz in ein externes Workbook aufgelöst wurde. Version 2.197.0 fügte ein zweites hinzu. Die Engine erhielt einen Callback-Typ, TXLSIsRowHidden, deklariert als Funktion von (SheetIndex, Zeile), die einen Boolean zurückgibt, gespeichert in FIsRowHidden, plus ein transientes FIgnoreHiddenRows-Flag. Das Flag wird am Eintritt in CalcSubtotalFunc scharf geschaltet, wenn der Funktionscode zwischen 101 und 111 liegt, und am Eintritt in CalcAggregateFunc für die AGGREGATE-Optionscodes, die den Ausschluss ausgeblendeter Zeilen wählen. Jede Zellendurchlaufschleife prüft es dann und überspringt eine Zeile, wenn es gesetzt ist, wobei jede nur eine einzige Zeile ergänzt
// The shape repeated in all six cell-walk loops
for sh := s1 to s2 do
for rr := r1 to r2 do
begin
if FIgnoreHiddenRows and Assigned(FIsRowHidden) and FIsRowHidden(sh, rr) then
Continue;
for cc := c1 to c2 do
begin
// ... fold Cells[rr, cc] into the accumulator ...
end;
end;Zwei Details im Scharfschalt-Code tragen die Korrektheit des gesamten Schemas. Das Flag wird gesichert und wiederhergestellt statt schlicht gesetzt und gelöscht, denn ein SUBTOTAL-Argument kann einen Ausdruck enthalten, der seine eigene Auswertung durchführt, während die äußere Aggregation noch auf dem Stapel liegt, und diese verschachtelte Arbeit darf das äußere Gate weder erben noch zerstören. Und die Wiederherstellung liegt in einem finally-Block, denn CalcSubtotalFunc hat mehrere frühe Ausstiege für Fehlercodes; ein nach einem Fehler-Return scharf gebliebenes Flag würde still die nächste, unabhängige Formel in der Neuberechnungsreihenfolge korrumpieren
prevIgnoreHidden := FIgnoreHiddenRows;
if (fnCode >= 101) and (fnCode <= 111) and Assigned(FIsRowHidden) then
FIgnoreHiddenRows := True;
try
// aggregate over Item.Child[2] .. Item.Child[ChildCount]
// every Exit path below is covered by the finally
finally
FIgnoreHiddenRows := prevIgnoreHidden;
end;Der Assigned-Test ist es, der die Änderung kompatibel hält. HotXLS erweiterte den Kalkulator-Konstruktor um einen dritten Parameter mit Standardwert nil, sodass jeder Code, der einen TXLSCalculator mit dem alten Zwei-Argument-Aufruf baut, weiterhin kompiliert und weiterhin das alte Verhalten mit Einbeziehung ausgeblendeter Zeilen erhält. An der Form der bestehenden API änderte sich nichts
Woher kommt das Bit für ausgeblendete Zeilen tatsächlich?
Vom Arbeitsblatt, über zwei verschiedene Quellen, denn HotXLS trägt zwei Workbook-Engines. Die Legacy-BIFF-Seite antwortet aus TXLSRowInfoList.GetHidden, erreicht über TXLSWorkbook.GetRowHidden. Die OOXML-Seite antwortet aus TXLSXWorksheet.GetRowHidden, erreicht über TXLSXWorkbook.GetCalcRowHidden. Beide sind bei der Konstruktion mit dem Kalkulator verdrahtet, neben dem Zellwert-Callback, den sie spiegeln. Die Zeilenkonventionen sind die Stelle, an der diese Art von Brücke normalerweise schiefgeht, also lohnt es sich, sie explizit zu benennen. Der Kalkulator übergibt dem Callback eine 0-basierte Zeile, passend zu den Koordinaten, die TXLSGetValue bereits verwendet. Das XLSX-Arbeitsblatt schlüsselt seine Row-Hidden-Map nach 1-basierter Zeilennummer, genau wie Excel Zeilen nummeriert, was auch das, was die öffentliche RowHidden[ARow]-Eigenschaft offenlegt. Die XLSX-Brücke addiert daher eins vor dem Lookup, die BIFF-Brücke nicht, weil TXLSRowInfoList bereits 0-basiert ist. Beide Brücken behandeln einen Sheet-Index oder eine Zeile außerhalb des gültigen Bereichs als sichtbar, sodass eine Abfrage außerhalb der Grenzen zur alten Antwort mit Einbeziehung ausgeblendeter Zeilen degradiert, statt Daten zu verlieren
Was sich für gefilterte Workbooks ändert
Das ist der Fall, der die Support-Tickets erzeugt. Das Anwenden eines AutoFilters in HotXLS über ApplyAutoFilter wertet die Spaltenkriterien aus und blendet jede Datenzeile aus, die nicht passt, genau das, was Excel tut, wenn ein Nutzer auf ein Filter-Dropdown klickt. Vor v2.197.0 waren diese ausgeblendeten Zeilen für den Nutzer unsichtbar und für die Berechnungs-Engine vollständig sichtbar, sodass ein serverseitiges SUBTOTAL(109, ...) die ungefilterte Summe meldete. Jetzt meldet derselbe Aufruf die gefilterte
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
VisibleRows: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('orders.xlsx');
Sheet := Book.Sheets[0];
Sheet.SetAutoFilter('A1:E500');
Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');
VisibleRows := Sheet.ApplyAutoFilter; // hides the non-matching rows
Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
Book.Recalculate;
// The cell value now agrees with what Excel shows for the same filter,
// and VisibleRows tells you how many rows fed into it
Book.SaveAs('orders-filtered.xlsx');
finally
Book.Free;
end;
end;Manuelles Ausblenden funktioniert genauso, da RowHidden[ARow] := True derselbe Zustand ist, den der Filter schreibt. Diese Äquivalenz ist in Excel beabsichtigt und gilt nun auch in HotXLS. Eine Konsequenz verdient einen Hinweis in jeder Dokumentation, die mit den erzeugten Workbooks ausgeliefert wird: Eine mit Code 109 berechnete Summe ist eine ansichtsabhängige Zahl, sodass ein Empfänger, der den Filter aufhebt, sie verändert. Muss ein Bericht eine feste Zahl unabhängig davon nennen, was der Leser an der Ansicht ändert, ist Code 9 die richtige Wahl und war es immer. Filter, Validierung und Tabellen werden gemeinsam im Artikel zu Datenvalidierung, AutoFilter und Tabellen behandelt. Weil das Ausblenden von Zeilen keine Formel berührt, verschmutzt es auch von sich aus nicht den Abhängigkeitsgraphen, was wichtig zu wissen ist, wenn man sich auf inkrementelle Neuberechnung über den verschmutzten Teilgraphen verlässt, um große Workbooks reaktionsschnell zu halten
AGGREGATE-Optionscodes und eine noch offene Grenze
AGGREGATE ist SUBTOTAL mit einem zweiten Richtlinien-Argument, und HotXLS behandelt es in CalcAggregateFunc. Das Optionsargument kodiert unabhängige Schalter: ob verschachtelte SUBTOTAL- und AGGREGATE-Aufrufe innerhalb des Bereichs übersprungen werden, ob Werte auf ausgeblendeten Zeilen übersprungen werden, und ob Fehlerwerte unterdrückt statt weitergegeben werden. HotXLS schaltet das gemeinsame Hidden-Row-Gate für die Optionscodes 2, 3, 6 und 7 scharf und unterdrückt Fehlerwerte für die Optionscodes 4 bis 7. Das Funktionsnummer-Argument wählt dann die Aggregation genau wie SUBTOTAL, einschließlich der Weiterleitung von Varianz, Standardabweichung und Produkt an ihre eigenen Reducer. Eine dokumentierte Lücke bleibt, und sie hier zu benennen ist besser, als sie in der Produktion zu entdecken: Die Ignore-Nested-SUBTOTAL-Semantik der niedrigen Optionscodes ist in HotXLS nicht implementiert. Ein verschachteltes SUBTOTAL innerhalb eines referenzierten Bereichs zu erkennen erfordert, den Auswerter-Rekursionszustand zu markieren, sodass eine innere Aggregation sich der äußeren ankündigen kann, was eine größere Änderung ist als das Hidden-Row-Gate. In der Praxis ist die Auswirkung gering, denn echte Workbooks platzieren SUBTOTAL-Formeln fast immer außerhalb der Bereiche, über die andere SUBTOTAL-Formeln aggregieren. Baut Ihr Generator überlappende Aggregationsbereiche, verlassen Sie sich nicht darauf, dass die niedrigen Optionscodes diese deduplizieren
Der Arity-Guard, der gleich mit ausgeliefert wurde
Version 2.197.0 schloss auch eine Validierungslücke im selben Dispatcher, und der Designgrund ist derselbe, der das Scratch-Feld motivierte: die Prüfung dorthin legen, wo sie einmal geschrieben werden kann. Rund 280 eingebaute Funktionskörper prüften jeweils ihre eigene Argumentanzahl gegen Item.ChildCount, was für den Fall zu vieler Argumente keine konsistente Grenze ließ. Ein Aufruf wie =SIN(1,2) erreichte einen Funktionskörper, der sein erstes Argument untersuchte, den Überschuss ignorierte und eine plausible Zahl zurückgab, wo Excel #VALUE! liefert. HotXLS speicherte bereits die deklarierte Arität jeder eingebauten Funktion in seiner Funktionsregistrierung, freigelegt als THashFunc.ArgsCnt, wobei -1 eine variadische Funktion wie SUM, IF oder CONCAT markiert. Version 2.197.0 leitete das über eine neue TXLSFormula.FuncArgsCntByPtg-Eigenschaft weiter und fügte ein Gate am Anfang von GetValueItemFunc hinzu, dem Hauptdispatcher
lDeclaredArgs := FFormula.FuncArgsCntByPtg[Item.IntValue];
if lDeclaredArgs >= 0 then
begin
lProvidedArgs := Item.ChildCount - 1; // Child[0] is the function node
if lProvidedArgs > lDeclaredArgs then
begin
Result := lxErrorValue; // =SIN(1,2) now yields #VALUE!
Exit;
end;
end;Der Guard weist zu viele Argumente zurück und sagt bewusst nichts über zu wenige. Ein nachgestelltes optionales Argument wegzulassen ist in Excel bei VLOOKUP, SUBSTITUTE und einer langen Liste weiterer Funktionen legal, sodass eine symmetrische Prüfung korrekte Formeln kaputt gemacht hätte, um fehlerhafte abzufangen. Unbekannte Bezeichner werden als variadisch gemeldet und überspringen das Gate vollständig, was benutzerdefinierte Funktionen aus dem Weg hält; wer eigene Funktionen registriert, findet das im Leitfaden zur Formel-Engine und benutzerdefinierten Funktionen beschriebene Verhalten unberührt. Den Zu-wenige-Fall zu zentralisieren ist eine separate Aufgabe, denn jeder dieser 280 Funktionskörper hat seine eigene Fehlercode-Semantik, und sie müssen einzeln geprüft werden, statt es anzunehmen
Die hier beschriebene Berechnungs-Engine, beide Workbook-Fassaden sowie die AutoFilter- und Zeilensichtbarkeits-APIs, die sie speisen, sind Teil der HotXLS Delphi Spreadsheet Component, die mit vollständigem Quelltext für Delphi und C++Builder ausgeliefert wird und auf der ausführenden Maschine keine Excel-Installation benötigt