AGGREGATE-optionsmatrisen och läckande grindar i HotXLS

HotXLS, den nativa Excel-kalkylbladskomponenten för Delphi och C++Builder, levererade två besläktade AGGREGATE-fixer i september 2026. Version 2.382.0 rättade optionsargumentet så att koderna 1/3/5/7 ignorerar dolda rader, 2/3/6/7 ignorerar fel, och 0 till 3 ignorerar nästlade SUBTOTAL- och AGGREGATE-celler, exakt som Microsoft dokumenterar. Version 2.382.3 stoppade sedan de urvalsflaggorna från att läcka in i evalueringen av just de celler funktionen refererar. Den första defekten är pinsam på det sätt tabellavskrivningsbuggar alltid är: bitpositionerna var omkastade, så varje formel som använde en nollskild optionskod fick en policy dess författare inte hade bett om. Den andra är mer intressant, för det är en form du kommer att möta i varje evaluator som använder ett tillfälligt fält för att skicka kontext in i en rekursiv genomgång. En yttre aggregering armar en flagga, går genom ett område, och hämtar en cell vars formel ännu inte har beräknats. Den formeln körs på samma kalkylator, ser samma armade flagga, och aggregerar tyst fel rader, vilket ger ett tal som är fel med ett belopp ingen kan förklara utifrån formeltexten allena

Vad väljer AGGREGATE-optionsvärdena 0 till 7 egentligen ut?

Optionsargumentet till AGGREGATE är en matris på tre bitar, och de tre bitarna är oberoende. Bit 0 (värde 1) betyder ignorera dolda rader, bit 1 (värde 2) betyder ignorera felvärden, och bit 2 (värde 4) betyder sluta ignorera nästlade SUBTOTAL- och AGGREGATE-celler, eftersom att hoppa över dem är standard för de låga koderna. Två saker är lätta att få bakvända här. Dolda raders bit är den låga biten, inte den mellersta, så AGGREGATE(9,1,...) är den filtrerade summaformen och AGGREGATE(9,2,...) den feltoleranta. Och policyn för nästlade aggregeringar är inverterad i förhållande till de andra två: bara koderna 4 till 7 behandlar en cell vars egen formel är en SUBTOTAL eller AGGREGATE som ett vanligt värde. ECMA-376 del 1 §18.17.7 definierar SUBTOTAL med samma inkludera-eller-uteslut-dolda-rader-delning över koderna 1-11 och 101-111, och AGGREGATE, som lagras i OOXML-filer under prefixet _xlfn., generaliserar den delningen till optionsargumentet, så tabellen Microsoft publicerar för AGGREGATE-funktionen är det kontrakt en motor måste uppfylla snarare än en bekvämlighet

Alternativ	Dolda rader	Felvärden	Nästlad SUBTOTAL / AGGREGATE
0	inkluderade	propagerade	ignorerade
1	ignorerade	propagerade	ignorerade
2	inkluderade	ignorerade	ignorerade
3	ignorerade	ignorerade	ignorerade
4	inkluderade	propagerade	inkluderade
5	ignorerade	propagerade	inkluderade
6	inkluderade	ignorerade	inkluderade
7	ignorerade	ignorerade	inkluderade

Varför hade HotXLS AGGREGATE-optionsvärdena bakvända?

Därför att den ursprungliga TXLSCalculator.CalcAggregateFunc skrevs utifrån en omskrivning av tabellen i stället för tabellen. Den räknade ignoreErrors := (optCode >= 4) and (optCode <= 7) och armade dolda raders-grinden för koderna 2, 3, 6 och 7, medan policyn för nästlade aggregeringar inte var implementerad alls. Den tidigare artikeln om dolda rader i SUBTOTAL och AGGREGATE listade den luckan som en öppen begränsning och beskrev den gamla mappningen som den då levererades; beskrivningen var korrekt om koden och fel om Excel, och ingen märkte det på länge eftersom de två policyer folk oftast kombinerar, dolda plus fel, landar på koderna 3 och 7 enligt båda tabellerna. Bara en enbitskod avslöjade omkastningen: AGGREGATE(9,1,A1:A4) returnerade den ofiltrerade summan, och AGGREGATE(9,2,...) hoppade över dolda rader medan #DIV/0! fortfarande propagerades. Defekten dök upp vid en statisk granskning av lxCalc.pas, loggad som HXLS-008 i projektets kända-fel-register, inte från en kundfil, vilket säger något om hur sällan enbitskoderna förekommer i produktionsarbetsböcker. Version 2.382.0 skrev om avkodningen till tre mängdtillhörighetstester och lade till en andra grind för den nästlade policyn, kopplad genom en ny återanropare TXLSIsSubtotalCell som arbetsboken tillhandahåller vid sidan av TXLSIsRowHidden

// TXLSCalculator.CalcAggregateFunc, formen från v2.382.3
if (optCode < 0) or (optCode > 7) then
begin
  Result := lxErrorValue;            // Excel avvisar koder utanför 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
  // ... mappa function_num till den inre iftab, gå genom ref1..refN ...
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
  FIgnoreSubtotalCells := prevIgnoreSubtotal;
end;

Notera att de två flaggorna tilldelas ovillkorligt i stället för att bara sättas när optionen ber om dem. Versionen från v2.382.0 använde fortfarande if ... then FIgnoreHiddenRows := True, vilket innebar att ett AGGREGATE med kod 4 nästlat inuti en SUBTOTAL(109, ...) ärvde den yttre dolda raders-grinden i stället för att rensa den. Att tilldela det avkodade värdet vid ingången och återställa det föregående värdet i finally-blocket gör att varje AGGREGATE-anrop äger sin policy under sin genomgång och ingenting mer. Version 2.382.0 gjorde också arrayformen ärlig: när ett argument evalueras till en- eller tvådimensionell Variant-array går CalcAggregateFunc nu genom varje element och tillämpar felpolicyn per element, där den gamla koden bara testade för en NaN-double och annars lämnade hela arrayen till ExcelSum

Varför läcker ett yttre AGGREGATE in i formlerna det refererar?

Därför att FIgnoreHiddenRows och FIgnoreSubtotalCells är fält på kalkylatorn, och kalkylatorn delas av varje formel som evalueras under en omräkning. Grindarna konstruerades som scratch-fält just för att sex cellgenomgångsloopar skulle kunna konsultera dem utan att trä en parameter genom varje signatur, och den konstruktionen är sund så länge allt som körs medan en grind är armad tillhör den aggregering som armade den. Antagandet brister vid en specifik punkt: FGetValue. När en genomgång begär ett cellvärde från arbetsboken och den cellen håller en formel utan cachat resultat kompilerar arbetsboken formeln och evaluerar den på stället, på samma TXLSCalculator, med de yttre grindarna fortfarande satta. Regression fixturen i HotXLS.WorkbookApiTests.pas visar felet med fyra celler. A1 håller 10, A2 håller 20 på en dold rad, A3 håller =1/0, och A4 håller =SUBTOTAL(9,A1:A2), vars korrekta värde är 30. Evaluera nu =AGGREGATE(9,7,A1:A4): ignorera dolda rader, ignorera fel, räkna den nästlade delsumman som ett värde. Excel returnerar 10 + 30 = 40. Med A4 ocachad armade motorn före 2.382.3 dolda raders-grinden, gick till A4, utlöste dess evaluering, och CalcSubtotalFunc för kod 9 ärvde den armade grinden, eftersom den bara någonsin sätter flaggan för koderna 101 till 111 och aldrig rensar den. A4 evaluerades till 10 i stället för 30, och den yttre summan kom tillbaka som 20. Ingenting i någon av formlerna nämner dolda rader på den väg som producerade det felaktiga talet

Grinden för nästlade aggregeringar läckte på samma sätt i andra riktningen. Med koderna 0 till 3 är FIgnoreSubtotalCells armad, och den generiska områdesgenomgången i GetValueItemRange lyder den, så ett prejudikat vars formel är =SUM(B1:B3) skulle tyst släppa B2 om B2 råkade innehålla en SUBTOTAL. Värre: CalcSubtotalFunc nollställer FIgnoreSubtotalCells till False vid utgången i stället för att återställa det föregående värdet, så ett ocachat SUBTOTAL-prejudikat som nåddes mitt i genomgången avväpnade den yttre grinden för varje cell efter det. Projektets kända-fel-register för detta under HXLS-008 som läckage av nästlat urvalstillstånd, och det är rätt namn för den här felklassen: en global tillfällig flagga som är korrekt för den ram som satte den och fel för varje ram som ärver den

Hur AggregateGetCellValue och AggregateGetItemValue isolerar genomgången

Fixen i v2.382.3 sätter en gräns runt varje punkt där AGGREGATE läser ett värde det inte har räknat fram själv. TXLSCalculator.AggregateGetCellValue lindar in det råa FGetValue-anropet: den sparar båda flaggorna, rensar dem, utför hämtningen, och återställer dem i ett finally-block. Den yttre aggregeringen tillämpar fortfarande sin egen policy på cellen den just hämtade, eftersom testerna för dolda rader och nästlade celler sker i genomgången runt hämtningen, men själva prejudikatformeln körs utan någon policy alls, vilket är vad Excel gör

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;        // en prejudikatformel äger sin egen policy
  FIgnoreSubtotalCells := False;
  try
    Result := FGetValue(SheetIndex, Row, Col, Value, OutOfRange);
  finally
    FIgnoreHiddenRows := Hidden;
    FIgnoreSubtotalCells := Nested;
  end;
end;

AggregateGetItemValue gör samma sak för argument som inte är områden, och den måste göra mer än att rensa flaggor, eftersom ett argument som A1:A4/(B1:B4-20) är en beräknad array vars elementform måste överleva. Wrappern materialiserar ett vanligt område till en tvådimensionell Variant-array genom AggregateGetCellValue, mappar en cell som returnerade en felkod till VarAsError så att felpolicyn fortfarande kan tillämpas per element, och den rekurerar genom de binära och unära operatornoderna (SA_ADD, SA_DIV, SA_UNARMINUS och resten) med ApplyArrayBinaryOp och ApplyArrayUnaryOp; allt annat faller igenom till den vanliga GetValueItem. Två vakter sitter framför materialiseringen: ett område större än EffectiveFormulaArrayMemoryLimit returnerar lxErrorResourceLimit, och ett område över flera ark eller ett inverterat område returnerar #VALUE!. En resursgränskod behandlas medvetet inte som ett ignorerbart cellfel ens under optionerna 2/3/6/7, eftersom en motor som svalde sin egen minnesbrist-signal för att användaren bad att hoppa över #N/A skulle ljuga. Alla tre AGGREGATE-genomgångarna, AggregateCollectRange för SUM-familjen, AggregateReduceVariance för STDEV, VAR och PRODUCT, samt AggregateReduceWithK för MEDIAN och kvantilformerna, växlades från FGetValue och GetValueItem till de två wrapprarna, och var och en fick det nästlade celltestet genom FIsSubtotalCell

Vilket fel returnerar AGGREGATE när det inte ignorerar fel?

Det ursprungliga, sedan v2.382.3. Version 2.382.0 upptäckte felceller korrekt men kollapsade var och en av dem till lxErrorValue, så AGGREGATE(9,4,A1:A3) över en #DIV/0!-cell returnerade #VALUE!, där Excel propagerar det första felet det möter oförändrat. Ersättningshjälparen AggregateErrorCode mappar en Variant till den matchande lxError*-koden, vare sig Varianten är en äkta varError eller en av de sju felsträngarna, och AggregateValueIsError är nu bara ett test för ett nollskilt resultat. Varje genomgång registrerar den första felkoden den ser och returnerar den koden, vilket också betyder att en cell vars formel aldrig beräknades, och vars fel därför anländer som en returkod från FGetValue i stället för som en cachad Variant, propagerar på samma sätt som en cachad. Två räknefunktioner får särskild behandling inuti AggregateCollectRange, och behandlingen matchar SUBTOTAL snarare än SUM. För den inre funktionen 0, COUNT, räknas en felcell aldrig och propageras aldrig oavsett optionskoden, eftersom COUNT bara räknar tal. För den inre funktionen 169, COUNTA, är en felcell ett icke-tomt värde och räknas som 1 om inte optionskoden ignorerar fel, i vilket fall den hoppas över. Den asymmetrin är hur Excel behandlar COUNT och COUNTA även utanför AGGREGATE, och det är den sortens detalj en generisk regel av typen "propagera vid fel" tyst får fel

Vad regressionsmatrisen med åtta optionsvärden verifierar

Fixturen som beskrivs ovan körs som en full matris i AggregateFunc_OptionMatrixCoversHiddenErrorsAndNestedAggregates: för varje optionskod från 0 till 7 evaluerar den både SUM-formen och MEDIAN-formen över A1:A4 och kontrollerar resultatet mot en handhärledd förväntan. Koderna 0, 1, 4 och 5 måste propagera #DIV/0! från A3, eftersom ingen av dem ignorerar fel. Kod 2 ger SUM 30 och MEDIAN 15, från 10 och 20 med den nästlade A4 överhoppad. Kod 3 ger 10 och 10. Kod 6 ger 60 och 20, eftersom 30:an i A4 nu räknas. Kod 7 ger 40 och 20, vilket är fallet som returnerade 20 före läckfixen. Den bredare acceptanskörningen som registrerats i kända-fel-registret täcker alla nitton funktionsnummer mot alla åtta koder, med varje prejudikat både cachat och ocachat, totalt 304 scenarier på Win32 och 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)';   // gruppdelsumma = 30
    Sheet.RowHidden[2] := True;

    Sheet.Cells[6, 1].Formula := '=AGGREGATE(9,1,A1:A4)'; // #DIV/0!  dolda hoppas över, felet propagerar
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       dolda + fel + nästlade hoppas över
    Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60       bara fel hoppas över
    Sheet.Cells[9, 1].Formula := '=AGGREGATE(9,7,A1:A4)'; // 40       var 20 före v2.382.3
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

Var gränsen fortfarande går

Tre gränser är värda att känna till innan du bygger på detta. För det första är predikatet för nästlade aggregeringar textuellt. TXLSXWorkbook.GetCalcIsSubtotalCell och dess tvilling i den klassiska motorn svarar True när en cells formel börjar med SUBTOTAL(, AGGREGATE( eller _xlfn.AGGREGATE(, med eller utan inledande likhetstecken, så en formel som =IF(C1,SUBTOTAL(9,B1:B9),0) eller =SUBTOTAL(9,B1:B9)*2 känns inte igen som nästlad och kommer att dubbelräknas av koderna 0 till 3 där Excel skulle hoppa över den; en generator som skickar ut beräknade delsummor bör hålla aggregeringsanropet först i formeln. För det andra ligger isoleringen i de tre AGGREGATE-genomgångarna. CalcSubtotalFunc går fortfarande genom GetValueItemRange, CollectRangeValues och SubtotalReduceVariance, som anropar FGetValue direkt, så en SUBTOTAL(109, ...) vars område innehåller en ocachad prejudikatformel kan fortfarande föra sin dolda raders-grind vidare in i det prejudikatet. En full Recalculate evaluerar prejudikat före beroende, så den cachade vägen tas och grinden ärvs aldrig; exponeringen är begränsad till ad hoc-evaluering genom Calculate och till arbetsböcker som laddats utan cachade värden, och om du förlitar dig på inkrementell omräkning över beroendegrafen för att hålla stora modeller responsiva är samma ordningsgaranti vad som håller det här läckaget sovande. För det tredje är båda grindarna villkorade på Assigned(FIsRowHidden) och Assigned(FIsSubtotalCell). Båda arbetsboksfasaderna kopplar in återanroparen i sina konstruktorer, men kod som bygger en TXLSCalculator för hand med bara de två ursprungliga argumenten får det äldre inkludera-allt-beteendet för varje optionskod, tyst. När en summa ser fel ut och formeltexten ser rätt ut är att spåra evalueringen steg för steg det snabbaste sättet att se om ett prejudikat evaluerades under en ärvd grind eller om en återanropare helt enkelt aldrig kopplades in

Kalkylmotorn som beskrivs här, optionsavkodaren, de isolerade hämtningswrapprarna och regressionsmatrisen som fäster dem levereras alla som källkod med HotXLS Delphi-kalkylbladskomponenten, som läser, skriver och räknar om XLS-, XLSX- och ODS-arbetsböcker i Delphi och C++Builder utan en Excel-installation