AGGREGATE-Optionen und Nested-Gates in HotXLS für Delphi

Vier Zellen genügen, damit =AGGREGATE(9,7,A1:A4) in einem handgestrickten Evaluator 20 zurückgibt, während Excel 40 zeigt. A1 hält 10, A2 hält 20 in einer versteckten Zeile, A3 hält =1/0, und A4 hält =SUBTOTAL(9,A1:A2), ein Gruppen-Subtotal im Wert von 30. Option 7 bedeutet: versteckte Zeilen überspringen, Fehler überspringen, verschachtelte Subtotals als Werte zählen – Excel addiert also 10 und 30. Die 20 erscheint, wenn die Einstellung „versteckte Zeilen überspringen“ des äußeren Aufrufs noch eingeschaltet ist, während A4 seinen eigenen Wert berechnet, was A4 auf 10 schrumpfen lässt. Auf 40 zu kommen verlangt zwei Dinge: die drei Optionsbits an den richtigen Positionen zu dekodieren und sicherzustellen, dass eine referenzierte Formel nie die Optionen der Formel sieht, die sie referenziert

Was wählen die AGGREGATE-Optionen 0 bis 7 tatsächlich aus?

Das Options-Argument ist eine Drei-Bit-Matrix, und die Bits sind voneinander unabhängig. Bit 0 (Wert 1) überspringt versteckte Zeilen, Bit 1 (Wert 2) überspringt Fehlerwerte, und Bit 2 (Wert 4) hört auf, Zellen zu überspringen, deren eigene Formel ein SUBTOTAL oder AGGREGATE ist, denn das Überspringen ist bei den niedrigen Codes der Default. Zwei Dinge daran sind leicht verdreht zu verstehen. Das Versteckt-Zeilen-Bit ist das niederwertige Bit, also ist AGGREGATE(9,1,...) die gefilterte Summe und AGGREGATE(9,2,...) die fehlertolerante. Und die Nested-Policy läuft entgegengesetzt zu den anderen beiden: Nur die Codes 4 bis 7 behandeln eine verschachtelte Aggregation als gewöhnliche Zahl. ECMA-376 Part 1 §18.17.7 definiert SUBTOTAL mit derselben Einschließen-oder-Ausschließen-Splitzung bei versteckten Zeilen über die Codes 1–11 und 101–111; AGGREGATE, in OOXML unter dem _xlfn.-Präfix gespeichert, faltet diese Splitzung ins Options-Argument, und die Tabelle, die Microsoft für die Funktion veröffentlicht, ist der Kontrakt, den eine Engine erfüllen muss

Option	Versteckte Zeilen	Fehlerwerte	Verschachtelte SUBTOTAL / AGGREGATE
0	eingeschlossen	propagiert	ignoriert
1	ignoriert	propagiert	ignoriert
2	eingeschlossen	ignoriert	ignoriert
3	ignoriert	ignoriert	ignoriert
4	eingeschlossen	propagiert	eingeschlossen
5	ignoriert	propagiert	eingeschlossen
6	eingeschlossen	ignoriert	eingeschlossen
7	ignoriert	ignoriert	eingeschlossen

Jeder Code außerhalb von 0 bis 7 wird mit #VALUE! zurückgewiesen, und genau das tut Excel auch

Warum das Dekodieren gerade an Einzel-Bit-Codes scheitert

Ein Dekodieren, das aus einer Paraphrase der Tabelle statt aus der Tabelle selbst geschrieben wurde, sieht typischerweise so aus: Fehler ab Code 4 aufwärts ignorieren, versteckte Zeilen für 2, 3, 6 und 7 überspringen, und die Nested-Policy komplett weglassen. Diese Version überlebt lange in Produktion. Die Kombination, die die meisten tatsächlich nutzen – versteckte Zeilen plus Fehler –, landet unter beiden Lesarten bei den Codes 3 und 7, also zeigt sich der Tausch nur an einem Einzel-Bit-Code. AGGREGATE(9,1,A1:A4) liefert dann die ungefilterte Summe, und AGGREGATE(9,2,...) überspringt versteckte Zeilen, während es weiterhin #DIV/0! propagiert. Echte Workbooks nutzen die Codes 1 und 2 selten allein, und genau deshalb findet ein Review gegen die veröffentlichte Tabelle mehr als das Warten auf eine Kundendatei

HotXLS dekodiert die Optionen als drei Mengenzugehörigkeitstests, einen pro Spalte der Tabelle, und weist alle drei Ergebnisse zu, wenn der AGGREGATE-Aufruf startet, und stellt beim Ende wiederher, was vorher in Kraft war. Die Zuweisung ist absichtlich bedingungslos. Ein als „wenn die Option es verlangt, schalte das Flag ein“ geschriebenes Dekodieren ließe ein AGGREGATE mit Code 4, das in einem SUBTOTAL(109, ...) sitzt, die äußere Versteckt-Zeilen-Einstellung erben, statt sie zu löschen. Die Skizze unten zeigt dieselbe Regel für alle, die einen eigenen Evaluator schreiben oder reviewen

// Illustrarisches Dekodieren für Ihren eigenen Evaluator: TAggPolicy und
// DecodeAggregateOptions sind Namen, die Sie selbst vergeben, keine HotXLS-API
type
  TAggPolicy = record
    IgnoreHidden, IgnoreErrors, SkipNested: Boolean;
  end;

function DecodeAggregateOptions(OptCode: Integer; out P: TAggPolicy): Boolean;
begin
  Result := (OptCode >= 0) and (OptCode <= 7);   // alles andere ist #VALUE!
  if not Result then
    Exit;
  P.IgnoreHidden := OptCode in [1, 3, 5, 7];       // Bit 0
  P.IgnoreErrors := OptCode in [2, 3, 6, 7];       // Bit 1
  P.SkipNested   := OptCode in [0, 1, 2, 3];       // Bit 2 gelöscht
end;

Die SUBTOTAL-Seite derselben Versteckt-Zeilen-Regeln – auch, wie sich gefilterte Zeilen und manuell versteckte Zeilen verhalten – ist in dem Artikel zu SUBTOTAL und AGGREGATE bei versteckten Zeilen beschrieben

Warum greift ein äußeres AGGREGATE in die Formeln ein, die es referenziert?

Weil der naheliegende Weg, „versteckte Zeilen überspringen“ an sechs oder sieben Zellablauf-Schleifen weiterzureichen, ein Feld am Calculator-Objekt ist, und ein einziger Calculator jede Formel in einem Neuberechnungsdurchlauf evaluiert. Dieses Design trägt so lange, wie alles, was läuft, während das Feld gesetzt ist, zur Aggregation gehört, die es gesetzt hat. Es hört an einem konkreten Moment auf: dem Zell-Fetch. Wenn der Walker einen Zellwert anfordert und die Zelle eine Formel ohne gecachtes Ergebnis hält, kompiliert das Workbook diese Formel und evaluiert sie auf der Stelle, auf demselben Calculator, mit den äußeren Einstellungen noch in Kraft

Spielen Sie das Eröffnungsbeispiel durch eine solche Engine durch. AGGREGATE mit Code 7 schaltet das Überspringen versteckter Zeilen ein und startet bei A1. A1 liefert 10, A2 ist versteckt und wird übersprungen, A3 ist ein Fehler und wird übersprungen. Dann erreicht der Walker A4, das noch nicht berechnet wurde. A4s SUBTOTAL(9,A1:A2) läuft, und eine SUBTOTAL-Implementierung, die das Überspringen versteckter Zeilen nur für die Codes 101 bis 111 einschaltet (ein völlig vernünftiger Weg, sie zu schreiben), schaltet es für Code 9 nie aus. Sie findet die Einstellung schon eingeschaltet vor, überspringt A2 und liefert 10. Die äußere Summe wird 10 + 10 = 20. Nichts in A4s Formel erwähnt versteckte Zeilen

Die Nested-Aggregate-Einstellung leakt in beide Richtungen. Bei den Codes 0 bis 3 ist sie eingeschaltet, und ein generischer Range-Walker, der sie beehrt, würde eine Präzedenz wie =SUM(B1:B3) B2 fallen lassen, falls B2 zufällig ein SUBTOTAL enthält. In die andere Richtung schaltet ein SUBTOTAL, das das Überspringen verschachtelter Zellen am Ende abschaltet, statt den früheren Zustand wiederherzustellen, die Policy des äußeren AGGREGATE für den Rest seines Durchlaufs ab. Jeder Evaluator, der ein transientes Feld nutzt, um Kontext in einen rekursiven Durchlauf zu reichen, hat diese Form; das Feld ist korrekt für den Rahmen, der es setzt, und falsch für jeden Rahmen, der es erbt

Die Policy an der Fetch-Grenze abschneiden

HotXLS legt eine Grenze um jeden Punkt, an dem AGGREGATE einen Wert liest, den es nicht selbst berechnet hat. Vor dem Fetch werden beide Einstellungen gesichert und gelöscht, danach in einem finally-Block wiederhergestellt. Die äußere Aggregation wendet ihre eigene Policy weiterhin auf die Zelle an, die sie gerade gezogen hat, denn die Versteckt-Zeilen- und Nested-Cell-Tests passieren im Walker um den Fetch herum. Die referenzierte Formel läuft ohne jede Policy, was zu Excel passt. Alle drei Walker-Familien gehen durch diese Grenze: die SUM-Familie (SUM, AVERAGE, COUNT, COUNTA, MAX, MIN), die geschlossenen Reduktionen (PRODUCT, STDEV, VAR) und die Formen mit einem k-Argument (MEDIAN, LARGE, SMALL, die Perzentile und Quartile)

// Illustrarischer Walker für Ihren eigenen Evaluator. FPolicy, FetchCell,
// IsRowHidden und IsSubtotalCell sind Member, die Sie selbst definieren
function TMyEvaluator.FetchForAggregate(Sheet, Row, Col: Integer;
  var V: Variant): Integer;
var
  Saved: TAggPolicy;
begin
  Saved := FPolicy;
  FPolicy := Default(TAggPolicy);     // die referenzierte Formel besitzt ihre eigene Policy
  try
    Result := FetchCell(Sheet, Row, Col, V);
  finally
    FPolicy := Saved;
  end;
end;

// innerhalb des Range-Durchlaufs: die äußere Policy wird um den Fetch herum angewendet
for R := R1 to R2 do
begin
  if FPolicy.IgnoreHidden and IsRowHidden(Sheet, R) then
    Continue;
  for C := C1 to C2 do
  begin
    if FPolicy.SkipNested and IsSubtotalCell(Sheet, R, C) then
      Continue;
    Code := FetchForAggregate(Sheet, R, C, V);
    // ... die Fehler-Policy auf Code / V anwenden, dann akkumulieren ...
  end;
end;

Argumente, die keine schlichten Ranges sind, brauchen mehr als gelöschte Flags. Ein Argument wie A1:A4/(B1:B4-20) ist ein berechnetes Array, und seine Elementform muss überleben, damit Option 6 genau die Elemente fallen lassen kann, die durch null teilten. HotXLS verwandelt jeden referenzierten Range durch dieselbe Grenze in ein zweidimensionales Array, speichert eine gescheiterte Zelle als Fehler-Element, statt abzubrechen, und wendet die arithmetischen und Vergleichsoperatoren Element für Element an. Ein Range über mehrere Sheets oder einer mit invertierten Ecken gibt #VALUE! zurück. Ein Range, der zu groß für TXLSCalculator.FormulaArrayMemoryLimit ist (standardmäßig 64 MB), liefert ein Resource-Limit-Ergebnis, und dieses Ergebnis liegt bewusst außerhalb der Fehlermenge, die die Optionen 2, 3, 6 und 7 überspringen dürfen. Das eigene Out-of-Memory-Signal der Engine zu verschlucken, weil die Formel bat, #N/A zu ignorieren, würde eine souveräne, falsche Zahl produzieren, also scheitert die Formel stattdessen

Welchen Fehler gibt AGGREGATE zurück, wenn es Fehler nicht ignoriert?

Den ersten, dem es begegnet, unverändert. Jeden Fehler zu #VALUE! zusammenzufalten ist der bequeme Kurzschluss, und er lässt AGGREGATE(9,4,A1:A3) über eine #DIV/0!-Zelle von Excel abweichen, das #DIV/0! durchreicht. HotXLS mappt sowohl echte Fehlervarianten als auch die sieben Fehler-Strings (#NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #N/A) auf ihre eigenen Codes zurück, und jeder Walker gibt den ersten Code zurück, den er notiert. Eine Zelle, deren Formel nie berechnet wurde, liefert ihren Fehler als Rückgabecode aus dem Fetch statt als gecachter Wert; sie propagiert sich exakt wie eine gecachte

Zwei Zählfunktionen folgen SUBTOTAL statt SUM. Für Funktion 2, COUNT, wird eine Fehlerzelle nie gezählt und nie propagiert, ganz gleich welcher Optionscode, denn COUNT zählt nur Zahlen. Für Funktion 3, COUNTA, ist eine Fehlerzelle ein nicht-leerer Wert und zählt als 1, außer der Optionscode überspringt Fehler. Diese Asymmetrie gilt auch außerhalb von AGGREGATE, denn so behandelt Excel COUNT und COUNTA ebenfalls, und eine pauschale „wenn Fehler, dann propagiere“-Regel bekommt beide falsch

Alle acht Optionen gegen ein einziges Arbeitsblatt prüfen

Die vier Eröffnungszellen ergeben eine kompakte Prüfung über jeden Code: Für jeden Code von 0 bis 7 evaluiert man die SUM-Form und die MEDIAN-Form über A1:A4 und vergleicht mit von Hand ausgerechneten Werten. Die Codes 0, 1, 4 und 5 müssen das #DIV/0! aus A3 propagieren, denn keiner von ihnen überspringt Fehler. Code 2 liefert SUM 30 und MEDIAN 15, aus 10 und 20 mit übersprungenem verschachteltem A4. Code 3 liefert 10 und 10. Code 6 liefert 60 und 20, denn die 30 in A4 zählt jetzt. Code 7 liefert 40 und 20 – und das ist der Code, den eine Engine ohne Fetch-Grenze als 20 meldet. Die Prüfung ist nur aussagekräftig, wenn A4 beim Lauf des AGGREGATE ungecached ist, also evaluieren Sie jeden Fall einmal mit bereits berechneten Präzedenzen und einmal ohne

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Data');
    Sheet.Cells[1, 1].Value := 10;
    Sheet.Cells[2, 1].Value := 20;
    Sheet.Cells[3, 1].Formula := '=1/0';
    Sheet.Cells[4, 1].Formula := '=SUBTOTAL(9,A1:A2)';    // Gruppen-Subtotal = 30
    Sheet.RowHidden[2] := True;

    Sheet.Cells[6, 1].Formula := '=AGGREGATE(9,1,A1:A4)'; // #DIV/0!  versteckte übersprungen, Fehler propagiert
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       versteckt, Fehler und verschachtelt übersprungen
    Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60       nur Fehler übersprungen
    Sheet.Cells[9, 1].Formula := '=AGGREGATE(9,7,A1:A4)'; // 40       20 bedeutet, eine Einstellung leakt in A4
    Sheet.Cells[10, 1].Formula := '=AGGREGATE(12,6,A1:A4)'; // 20     MEDIAN von 10, 20, 30
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

Öffnet man die gespeicherte Datei in Excel und drückt Strg+Alt+F9, gibt das eine zweite Meinung zu denselben Zellen. Für einen breiteren Sweep lassen Sie jede Funktionsnummer von 1 bis 19 gegen alle acht Codes laufen

Die Callbacks verdrahten, wenn Sie den Calculator selbst bauen

Beide Einstellungen hängen an zwei Callbacks. TXLSCalculator.Create nimmt nach dem Value-Getter einen TXLSIsRowHidden und einen TXLSIsSubtotalCell entgegen, und beide defaulten auf nil. Fehlt einer von ihnen, greift die entsprechende Spalte der Tabelle nie, also schließt jeder Optionscode versteckte Zeilen, verschachtelte Aggregationen oder beides ein. Die XLSX- und Classic-XLS-Workbook-Objekte reichen ihre eigenen Implementierungen in ihren Konstruktoren durch; Code, der einen TXLSCalculator über seine eigene Datenquelle von Hand anlegt, muss beide liefern. Die Workbooks erkennen eine verschachtelte Aggregation an einer Formel, die mit SUBTOTAL(, AGGREGATE( oder _xlfn.AGGREGATE( beginnt, mit oder ohne führendes Gleichheitszeichen, und eine eigene Quelle sollte dieselbe Regel verwenden

// TMySheetSource und FormulaAt sind Ihre eigenen Typen; die drei Methoden-
// signaturen entsprechen TXLSGetValue, TXLSIsRowHidden und TXLSIsSubtotalCell
// (SheetIndex 1-basiert, Row und Col nullbasiert, wie die Workbooks sie übergeben)
function TMySheetSource.IsSubtotalCell(SheetIndex, Row, Col: Integer): Boolean;
var
  F: string;
begin
  F := FormulaAt(SheetIndex, Row, Col);
  if (F <> '') and (F[1] = '=') then
    Delete(F, 1, 1);
  Result := SameText(Copy(F, 1, 9), 'SUBTOTAL(') or
            SameText(Copy(F, 1, 10), 'AGGREGATE(') or
            SameText(Copy(F, 1, 16), '_xlfn.AGGREGATE(');
end;

// Extern ist die Sheet-Namen-Tabelle, die Ihr Host bereits führt
Calc := TXLSCalculator.Create(Extern, Source.GetCellValue,
  Source.IsRowHidden, Source.IsSubtotalCell);

Die Präfix-Regel hat eine Konsequenz für generierte Workbooks. Ein Generator, der =ROUND(SUBTOTAL(9,B2:B9),2) schreibt, produziert eine Zelle, die der Präfix-Test nicht erkennt, also zählen die Codes 0 bis 3 sie, und die Gruppe landet doppelt in der Gesamtsumme. Halten Sie den SUBTOTAL- oder AGGREGATE-Aufruf ganz außen und verlagern Sie das Runden ins Zahlenformat. Ein volles Recalculate evaluiert Präzedenzen vor Abhängigen, also lesen Aggregationen normalerweise gecachte Werte, und dieselbe Ordnung gilt unter der inkrementellen Neuberechnung über den Dependency Graph. Wenn eine Summe immer noch falsch aussieht und der Formeltext richtig aussieht, zeigt die Schritt-für-Schritt-Verfolgung der Evaluation, ob eine Präzedenz on demand innerhalb des Durchlaufs lief oder ob ein Callback nie angeschlossen wurde

Der Options-Decoder, die Fetch-Grenze und die hier beschriebenen Callback-Hooks kommen mit der HotXLS Delphi spreadsheet component, die XLS-, XLSX- und ODS-Workbooks aus Delphi- und C++Builder-Code liest, schreibt und neu berechnet, ohne dass Excel auf der Maschine installiert ist