AGGREGATE-optiesmatrix en gatlekkage in HotXLS voor Delphi

HotXLS, de native Excel-spreadsheetcomponent voor Delphi en C++Builder, bracht in september 2026 twee verwante AGGREGATE-fixes uit. Versie 2.382.0 corrigeerde het optieargument zodat codes 1/3/5/7 verborgen rijen negeren, 2/3/6/7 fouten negeren, en 0 tot en met 3 geneste SUBTOTAL- en AGGREGATE-cellen negeren, precies zoals Microsoft het documenteert. Versie 2.382.3 zorgde er vervolgens voor dat die selectieflags niet meer in de evaluatie van de cellen waar de functie naar verwijst lekten. Het eerste defect is gênant op de manier waarop tabelovertypfouten altijd gênant zijn: de bitposities waren omgewisseld, dus elke formule met een optiecode ongelijk nul kreeg een beleid dat de auteur niet had gevraagd. Het tweede is interessanter, want het is een vorm die u in elke evaluator tegenkomt die een tijdelijk veld gebruikt om context een recursieve wandeling in te dragen. Een buitenste aggregatie zet een flag, loopt door een bereik, en haalt een cel op waarvan de formule nog niet is berekend. Die formule draait op dezelfde calculator, ziet dezelfde gezette flag, en aggregeert stilzwijgend de verkeerde rijen, wat een getal oplevert dat er met een bedrag naast zit dat niemand uit de formuletekst alleen kan verklaren

Wat selecteren de AGGREGATE-opties 0 tot en met 7 nu eigenlijk?

Het optieargument van AGGREGATE is een matrix van drie bits, en de drie bits zijn onafhankelijk. Bit 0 (waarde 1) betekent verborgen rijen negeren, bit 1 (waarde 2) betekent foutwaarden negeren, en bit 2 (waarde 4) betekent ophouden met het negeren van geneste SUBTOTAL- en AGGREGATE-cellen, want ze overslaan is de standaard voor de lage codes. Twee dingen hieraan zijn makkelijk om te draaien. De bit voor verborgen rijen is de laagste bit, niet de middelste, dus AGGREGATE(9,1,...) is de gefilterde-totaalvorm en AGGREGATE(9,2,...) de fouttolerante. En het beleid voor geneste aggregaties staat omgekeerd ten opzichte van de andere twee: alleen codes 4 tot en met 7 behandelen een cel waarvan de eigen formule een SUBTOTAL of AGGREGATE is als een gewone waarde. ECMA-376 Part 1 §18.17.7 definieert SUBTOTAL met dezelfde splitsing tussen in- en uitsluiten van verborgen rijen over codes 1-11 en 101-111, en AGGREGATE, in OOXML-bestanden opgeslagen onder de prefix _xlfn., generaliseert die splitsing naar het optieargument, dus de tabel die Microsoft voor de AGGREGATE-functie publiceert is het contract waaraan een engine moet voldoen, geen gemak

Optie	Verborgen rijen	Foutwaarden	Geneste SUBTOTAL / AGGREGATE
0	meegenomen	doorgegeven	genegeerd
1	genegeerd	doorgegeven	genegeerd
2	meegenomen	genegeerd	genegeerd
3	genegeerd	genegeerd	genegeerd
4	meegenomen	doorgegeven	meegenomen
5	genegeerd	doorgegeven	meegenomen
6	meegenomen	genegeerd	meegenomen
7	genegeerd	genegeerd	meegenomen

Waarom had HotXLS de AGGREGATE-opties omgekeerd?

Omdat de oorspronkelijke TXLSCalculator.CalcAggregateFunc was geschreven op basis van een parafrase van de tabel in plaats van de tabel zelf. De code berekende ignoreErrors := (optCode >= 4) and (optCode <= 7) en zette de gate voor verborgen rijen voor codes 2, 3, 6 en 7, terwijl het beleid voor geneste aggregaties helemaal niet was geïmplementeerd. Het eerdere artikel over SUBTOTAL en AGGREGATE met verborgen rijen noemde dat gat als open limiet en beschreef de oude mapping zoals die toen was uitgebracht; de beschrijving klopte voor de code en niet voor Excel, en niemand merkte het lange tijd omdat de twee beleidsregels die de meeste mensen combineren, verborgen plus fouten, in beide tabellen op codes 3 en 7 uitkomen. Alleen een code met één bit stelde de verwisseling bloot: AGGREGATE(9,1,A1:A4) gaf de ongefilterde som terug, en AGGREGATE(9,2,...) sloeg verborgen rijen over terwijl #DIV/0! werd doorgegeven. Het defect kwam boven water uit een statische review van lxCalc.pas, geregistreerd als HXLS-008 in het known-issues-register van het project, niet uit een klantbestand, wat iets zegt over hoe zelden de codes met één bit in productieworkbooks voorkomen. Versie 2.382.0 herschreef de decode als drie set-membershiptoetsen en voegde een tweede gate toe voor het geneste beleid, bedraad via een nieuwe TXLSIsSubtotalCell-callback die de workbook naast TXLSIsRowHidden levert

// TXLSCalculator.CalcAggregateFunc, vorm van v2.382.3
if (optCode < 0) or (optCode > 7) then
begin
  Result := lxErrorValue;            // Excel weigert codes buiten 0..7
  Exit;
end;
ignoreErrors := optCode in [2, 3, 6, 7];
prevIgnoreHidden := FIgnoreHiddenRows;
prevIgnoreSubtotal := FIgnoreSubtotalCells;
FIgnoreHiddenRows := (optCode in [1, 3, 5, 7]) and Assigned(FIsRowHidden);
FIgnoreSubtotalCells := (optCode in [0, 1, 2, 3]) and Assigned(FIsSubtotalCell);
try
  // ... map function_num op de inner iftab, loop door ref1..refN ...
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
  FIgnoreSubtotalCells := prevIgnoreSubtotal;
end;

Merk op dat de twee flags onvoorwaardelijk worden toegewezen in plaats van alleen gezet wanneer de optie erom vraagt. De versie van 2.382.0 gebruikte nog if ... then FIgnoreHiddenRows := True, wat betekende dat een AGGREGATE met code 4 genest in een SUBTOTAL(109, ...) de buitenste gate voor verborgen rijen erfde in plaats van hem te wissen. De gedecodeerde waarde bij binnenkomst toewijzen en de vorige waarde in het finally-blok herstellen maakt dat elke AGGREGATE-aanroep zijn eigen beleid bezit voor de duur van zijn wandeling en niets meer. Versie 2.382.0 maakte ook de arrayvorm eerlijk: wanneer een argument naar een eendimensionale of tweedimensionale Variant-array evalueert, loopt CalcAggregateFunc nu elk element langs en past het foutbeleid per element toe, waar de oude code alleen op een NaN-double testte en de hele array anders aan ExcelSum gaf

Waarom lekt een buitenste AGGREGATE in de formules waarnaar hij verwijst?

Omdat FIgnoreHiddenRows en FIgnoreSubtotalCells velden op de calculator zijn, en de calculator wordt gedeeld door elke formule die tijdens één herberekening wordt geëvalueerd. De gates waren juist als kladvelden ontworpen zodat zes celwandellussen ze konden raadplegen zonder een parameter door elke signatuur te rijgen, en dat ontwerp klopt zolang alles wat loopt terwijl een gate gezet is bij de aggregatie hoort die hem zette. De aanname breekt op één specifiek punt: FGetValue. Wanneer een walker de workbook om een celwaarde vraagt en die cel bevat een formule zonder gecacht resultaat, compileert de workbook de formule en evalueert hem ter plekke, op dezelfde TXLSCalculator, met de buitenste gates nog gezet. De regressiefixture in HotXLS.WorkbookApiTests.pas toont de fout met vier cellen. A1 bevat 10, A2 bevat 20 op een verborgen rij, A3 bevat =1/0, en A4 bevat =SUBTOTAL(9,A1:A2), waarvan de juiste waarde 30 is. Evalueer nu =AGGREGATE(9,7,A1:A4): verborgen rijen negeren, fouten negeren, de geneste subtotaal als waarde meetellen. Excel geeft 10 + 30 = 40. Met A4 ongecacht zette de engine van vóór 2.382.3 de gate voor verborgen rijen, liep naar A4, triggerde de evaluatie ervan, en CalcSubtotalFunc voor code 9 erfde de gezette gate, want die zet de flag alleen ooit voor codes 101 tot en met 111 en wist hem nooit. A4 evalueerde naar 10 in plaats van 30, en het buitenste totaal kwam terug als 20. Niets in een van beide formules noemt verborgen rijen op het pad dat het verkeerde getal opleverde

De gate voor geneste aggregaties lekte op dezelfde manier in de andere richting. Bij codes 0 tot en met 3 is FIgnoreSubtotalCells gezet, en de generieke bereikwandelaar in GetValueItemRange respecteert dat, dus een precedent met de formule =SUM(B1:B3) zou B2 stilzwijgend laten vallen als B2 toevallig een SUBTOTAL bevatte. Erger nog, CalcSubtotalFunc reset FIgnoreSubtotalCells naar False bij het verlaten in plaats van de vorige waarde te herstellen, dus een ongecacht SUBTOTAL-precedent dat midden in de wandeling werd bereikt ontwapende de buitenste gate voor elke cel erna. Het known-issues-register van het project registreert dit onder HXLS-008 als lekkage van geneste selectiestate, en dat is de juiste naam voor deze bugklasse: een globale tijdelijke flag die klopt voor het frame dat hem zette en fout is voor elk frame dat hem erft

Hoe AggregateGetCellValue en AggregateGetItemValue de wandeling isoleren

De fix in v2.382.3 legt een grens rond elk punt waar AGGREGATE een waarde leest die hij niet zelf heeft berekend. TXLSCalculator.AggregateGetCellValue wikkelt de ruwe FGetValue-aanroep: het bewaart beide flags, wist ze, doet de fetch, en herstelt ze in een finally-blok. De buitenste aggregatie past nog steeds zijn eigen beleid toe op de cel die net is opgehaald, want de toetsen voor verborgen rijen en geneste cellen gebeuren in de walker rond de fetch, maar de precedentformule zelf loopt zonder enig beleid, en dat is wat Excel doet

function TXLSCalculator.AggregateGetCellValue(SheetIndex, Row, Col: Integer;
  var Value: Variant; var OutOfRange: Boolean): Integer;
var
  Hidden, Nested: Boolean;
begin
  Hidden := FIgnoreHiddenRows;
  Nested := FIgnoreSubtotalCells;
  FIgnoreHiddenRows := False;        // een precedentformule bezit zijn eigen beleid
  FIgnoreSubtotalCells := False;
  try
    Result := FGetValue(SheetIndex, Row, Col, Value, OutOfRange);
  finally
    FIgnoreHiddenRows := Hidden;
    FIgnoreSubtotalCells := Nested;
  end;
end;

AggregateGetItemValue doet hetzelfde voor argumenten die geen bereik zijn, en het moet meer doen dan flags wissen, want een argument als A1:A4/(B1:B4-20) is een berekende array waarvan de elementvorm moet overleven. De wrapper materialiseert een gewoon bereik naar een tweedimensionale Variant-array via AggregateGetCellValue, waarbij een cel die een foutcode teruggaf op VarAsError wordt gemapt zodat het foutbeleid nog per element kan worden toegepast, en hij recursiet door de binaire en unaire operatorknopen (SA_ADD, SA_DIV, SA_UNARMINUS en de rest) met ApplyArrayBinaryOp en ApplyArrayUnaryOp; al het andere valt door naar de normale GetValueItem. Twee bewakingen staan voor de materialisatie: een bereik groter dan EffectiveFormulaArrayMemoryLimit geeft lxErrorResourceLimit terug, en een bereik over meerdere sheets of een omgekeerd bereik geeft #VALUE!. Een resource-limietcode wordt bewust niet als een negeerbare celfout behandeld, ook niet onder opties 2/3/6/7, want een engine die zijn eigen out-of-memory-signaal inslikt omdat de gebruiker vroeg #N/A over te slaan zou liegen. Alle drie de AGGREGATE-walkers, AggregateCollectRange voor de SUM-familie, AggregateReduceVariance voor STDEV, VAR en PRODUCT, en AggregateReduceWithK voor MEDIAN en de kwantielvormen, zijn overgezet van FGetValue en GetValueItem op de twee wrappers, en elk kreeg de toets voor geneste cellen via FIsSubtotalCell

Welke fout geeft AGGREGATE terug wanneer hij fouten niet negeert?

De oorspronkelijke, sinds v2.382.3. Versie 2.382.0 detecteerde foutcellen correct maar ploegde ze allemaal samen tot lxErrorValue, dus gaf AGGREGATE(9,4,A1:A3) over een #DIV/0!-cel #VALUE! terug, waar Excel de eerste fout die hij tegenkomt ongewijzigd doorgeeft. De vervangende helper AggregateErrorCode mapt een Variant op de bijpassende lxError*-code, of de Variant nu een echte varError is of een van de zeven foutstrings, en AggregateValueIsError is nu gewoon een toets op een resultaat ongelijk nul. Elke walker onthoudt de eerste foutcode die hij ziet en geeft die code terug, wat ook betekent dat een cel waarvan de formule nooit is berekend, en waarvan de fout dus als returncode uit FGetValue komt in plaats van als gecachte Variant, op dezelfde manier doorwerkt als een gecachte. Twee telfuncties krijgen speciale behandeling binnen AggregateCollectRange, en die behandeling volgt SUBTOTAL en niet SUM. Voor innerfunctie 0, COUNT, wordt een foutcel nooit geteld en nooit doorgegeven, ongeacht de optiecode, want COUNT telt alleen getallen. Voor innerfunctie 169, COUNTA, is een foutcel een niet-lege waarde en telt hij als 1, tenzij de optiecode fouten negeert, in welk geval hij wordt overgeslagen. Die asymmetrie is hoe Excel COUNT en COUNTA ook buiten AGGREGATE behandelt, en het is precies het soort detail dat een generieke regel "bij fout dan doorgeven" stilzwijgend fout doet

Wat de regressiematrix met acht opties verifieert

De hierboven beschreven fixture wordt als volledige matrix doorlopen in AggregateFunc_OptionMatrixCoversHiddenErrorsAndNestedAggregates: voor elke optiecode van 0 tot en met 7 evalueert hij zowel de SUM-vorm als de MEDIAN-vorm over A1:A4 en toetst hij het resultaat tegen een met de hand afgeleide verwachting. Codes 0, 1, 4 en 5 moeten de #DIV/0! uit A3 doorgeven, want geen ervan negeert fouten. Code 2 geeft SUM 30 en MEDIAN 15, uit 10 en 20 met de geneste A4 overgeslagen. Code 3 geeft 10 en 10. Code 6 geeft 60 en 20, omdat de 30 in A4 nu meetelt. Code 7 geeft 40 en 20, en dat is het geval dat 20 teruggaf vóór de lekfix. De bredere acceptatierun die in het known-issues-register is vastgelegd dekt alle negentien functienummers tegen alle acht codes, met elk precedent zowel gecacht als ongecacht, voor 304 scenario's op Win32 en Win64

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)';   // subtotaal van de groep = 30
    Sheet.RowHidden[2] := True;

    Sheet.Cells[6, 1].Formula := '=AGGREGATE(9,1,A1:A4)'; // #DIV/0!  verborgen overgeslagen, fout gaat door
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       verborgen + fout + genest overgeslagen
    Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60       alleen fouten overgeslagen
    Sheet.Cells[9, 1].Formula := '=AGGREGATE(9,7,A1:A4)'; // 40       was 20 vóór v2.382.3
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

Waar de grens nog steeds ligt

Drie limieten zijn het kennen waard voordat u hierop bouwt. Ten eerste is het predicaat voor geneste aggregaties tekstueel. TXLSXWorkbook.GetCalcIsSubtotalCell en zijn tegenhanger in de klassieke engine antwoorden True wanneer de formule van een cel begint met SUBTOTAL(, AGGREGATE( of _xlfn.AGGREGATE(, met of zonder het voorloopgelijkteken, dus een formule als =IF(C1,SUBTOTAL(9,B1:B9),0) of =SUBTOTAL(9,B1:B9)*2 wordt niet als genest herkend en wordt door codes 0 tot en met 3 dubbel geteld waar Excel hem zou overslaan; een generator die berekende subtotalen uitschrijft kan de aggregatieaanroep beter aan het hoofd van de formule houden. Ten tweede zit de isolatie in de drie AGGREGATE-walkers. CalcSubtotalFunc loopt nog steeds via GetValueItemRange, CollectRangeValues en SubtotalReduceVariance, die FGetValue direct aanroepen, dus een SUBTOTAL(109, ...) waarvan het bereik een ongecacht precedent bevat kan zijn gate voor verborgen rijen nog steeds in dat precedent doorgeven. Een volledige Recalculate evalueert precedenten vóór afhankelijken, dus wordt het gecachte pad genomen en wordt de gate nooit geërfd; de blootstelling beperkt zich tot ad-hoc-evaluatie via Calculate en tot workbooks die zonder gecachte waarden zijn geladen, en als u op incrementele herberekening over de dependency graph leunt om grote modellen responsief te houden, is dezelfde ordeningsgarantie wat dit lek slapend houdt. Ten derde zijn beide gates voorwaardelijk aan Assigned(FIsRowHidden) en Assigned(FIsSubtotalCell). Beide workbookfacades bedraden de callbacks in hun constructors, maar code die een TXLSCalculator met de hand bouwt met alleen de twee oorspronkelijke argumenten krijgt stilzwijgend het oude alles-meetellen-gedrag voor elke optiecode. Wanneer een totaal er fout uitziet en de formuletekst er goed uitziet, is de evaluatie stap voor stap tracen de snelste manier om te zien of een precedent onder een geërfd gate is geëvalueerd of dat er simpelweg nooit een callback is gekoppeld

De hier beschreven rekenengine, de optiedecoder, de geïsoleerde fetch-wrappers en de regressiematrix die ze vastpint, worden allemaal als source meegeleverd met de HotXLS Delphi spreadsheet component, die XLS-, XLSX- en ODS-workbooks leest, schrijft en herberekent in Delphi en C++Builder zonder Excel-installatie