Liefern SUBTOTAL(109, ...) und SUBTOTAL(9, ...) dieselbe Zahl in einem Workbook mit ausgeblendeten Zeilen, ist eines der beiden falsch. Es ist eine der leichtesten Fallen beim Bau einer Spreadsheet-Berechnungs-Engine, und HotXLS, die native Excel-Spreadsheet-Komponente für Delphi und C++Builder, geht ihr aus dem Weg, indem ihre Berechnungs-Engine das Arbeitsblatt fragen kann, ob eine bestimmte Zeile ausgeblendet ist
Das Symptom kommt selten als Frage nach 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 würde SUBTOTAL 109 ausgeblendete Zeilen einschließen?
Weil in den meisten Engine-Designs die Schicht, die eine Formel auswertet, nie von der Zeilensichtbarkeit erfährt. Das naheliegende Design erreicht Zellwerte über einen einzigen Wert-Callback, wie den TXLSGetValue-Callback in lxCalc.pas, der auf ein (Sheet-, Zeilen-, Spalten-)Tripel mit einem Wert antwortet und sonst nichts. Sichtbarkeit ist ein Präsentationsattribut, gespeichert im Zeilendatensatz, und reist nichts anderes die Aufrufkette hinab, hat die Engine am Ende einen einzigen Aggregationspfad, auf den beide Hälften der SUBTOTAL-Funktionsnummerntabelle auflösen. Das ist kein Fehler der Kategorie Rundungsfehler: Die Versteckt-Zeilen-Richtlinie ist der ganze Grund, warum die zweite Hälfte der Tabelle existiert. ECMA-376 Part 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 Versteckt-Zeilen-Richtlinie 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 eine Versteckt-Zeilen-Prüfung, die nur auf eine davon angewendet wird, erzeugt das denkbar schlechteste Ergebnis: SUBTOTAL(109, ...) respektiert den Filter, während SUBTOTAL(107, ...) auf demselben Bereich es nicht tut. HotXLS hat sechs davon, sobald AGGREGATE einbezogen ist, 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 hat einen Präzedenzfall für die Alternative: ein transientes Feld auf dem Calculator, im selben Geist wie das Scratch-Feld, das GetRangeInfo nutzt, um zu vermerken, wann eine 3D-Referenz in ein externes Workbook aufgelöst wurde. Das Versteckt-Zeilen-Gate ist ein zweites: ein 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
// Die Form, die sich in allen sechs Zellendurchlauf-Schleifen wiederholt
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
// ... Cells[rr, cc] in den Akkumulator einarbeiten ...
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
// über Item.Child[2] .. Item.Child[ChildCount] aggregieren
// jeder Exit-Pfad unten wird vom finally abgedeckt
finally
FIgnoreHiddenRows := prevIgnoreHidden;
end;Der Assigned-Test ist es, der die Calculator-API kompatibel hält. Der TXLSCalculator-Konstruktor nimmt einen dritten Parameter mit Standardwert nil, sodass Code, der einen Calculator mit dem Zwei-Argument-Aufruf baut, unverändert kompiliert und das Verhalten mit Einbeziehung ausgeblendeter Zeilen bekommt
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 Calculator 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 Calculator ü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 ist, 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 Antwort mit Einbeziehung ausgeblendeter Zeilen degradiert, statt Daten zu verlieren
Was sich für gefilterte Workbooks ändert
Das ist der Fall, bei dem die Versteckt-Zeilen-Richtlinie am meisten zählt. 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. Diese Zeilen sind für den Nutzer unsichtbar, und weil die Berechnungs-Engine denselben Zeilenzustand konsultiert, meldet ein serverseitiges SUBTOTAL(109, ...) die gefilterte Summe statt der ungefilterten
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; // blendet die nicht passenden Zeilen aus
Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
Book.Recalculate;
// Der Zellwert stimmt mit dem überein, was Excel für denselben Filter zeigt,
// und VisibleRows sagt Ihnen, wie viele Zeilen hineingeflossen sind
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 in HotXLS ebenso. 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
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 Versteckt-Zeilen-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. Echte Workbooks platzieren SUBTOTAL-Formeln fast immer außerhalb der Bereiche, über die andere SUBTOTAL-Formeln aggregieren; baut Ihr Generator überlappende Aggregationsbereiche, halten Sie die inneren Summen aus dem äußeren Bereich heraus, statt sich darauf zu verlassen, dass die Optionscodes sie deduplizieren
Ein Arity-Guard im selben Dispatcher
Derselbe Dispatcher trägt eine Argumentanzahl-Prüfung, und der Designgrund ist derselbe wie hinter dem Scratch-Feld: die Prüfung dorthin legen, wo sie einmal geschrieben werden kann. Bei rund 280 eingebauten Funktionskörpern jedem zu überlassen, seine eigene Argumentanzahl gegen Item.ChildCount zu prüfen, lässt für den Fall zu vieler Argumente keine konsistente Grenze, und die Falle ist leicht zu übersehen: Ein Aufruf wie =SIN(1,2) erreicht einen Funktionskörper, der sein erstes Argument untersucht, den Überschuss ignoriert und eine plausible Zahl zurückgibt, wo Excel #VALUE! liefert. HotXLS speichert 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, leitet sie über die TXLSFormula.FuncArgsCntByPtg-Eigenschaft weiter und prüft sie in einem Gate am Anfang von GetValueItemFunc, dem Hauptdispatcher
lDeclaredArgs := FFormula.FuncArgsCntByPtg[Item.IntValue];
if lDeclaredArgs >= 0 then
begin
lProvidedArgs := Item.ChildCount - 1; // Child[0] ist der Funktionsknoten
if lProvidedArgs > lDeclaredArgs then
begin
Result := lxErrorValue; // =SIN(1,2) ergibt #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 kaputtmachen würde, 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. Der Zu-wenige-Fall bleibt bei jedem Funktionskörper, denn jeder dieser Körper hat seine eigene Fehlercode-Semantik
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