Matica volieb AGGREGATE a únik gate v HotXLS pre Delphi

HotXLS, teda natívna Excel tabuľková komponenta pre Delphi a C++Builder, dodal v septembri 2026 dve súvisiace opravy AGGREGATE. Verzia 2.382.0 opravila argument options tak, aby kódy 1/3/5/7 ignorovali skryté riadky, 2/3/6/7 ignorovali chyby a 0 až 3 ignorovali vnorené bunky SUBTOTAL a AGGREGATE, presne ako to dokumentuje Microsoft. Verzia 2.382.3 potom zastavila to, aby tie výberové príznaky prenikali do vyhodnocovania práve tých buniek, na ktoré funkcia odkazuje. Prvý defekt je trápny tak, ako sú trápne chyby pri prepise tabuliek vždy: bitové pozície boli vymenené, takže každá formula s nenulovým kódom volieb dostala politiku, o ktorú jej autor nežiadal. Druhý je zaujímavejší, pretože je to tvar, na ktorý narazíte v každom vyhodnocovači, ktorý používa prechodné pole na prenos kontextu do rekurzívneho prechodu. Vonkajšia agregácia vyzbrojí príznak, prejde rozsah a vytiahne bunku, ktorej formula ešte nie je spočítaná. Tá formula beží na tom istom kalkulátore, vidí ten istý vyzbrojený príznak a potichu agreguje nesprávne riadky, čím vyprodukuje číslo, ktoré je mimo o hodnotu, ktorú nikto nevie vysvetliť len z textu formuly

Čo vlastne vyberajú voľby AGGREGATE 0 až 7?

Argument options funkcie AGGREGATE je trojbitová matica a tie tri bity sú nezávislé. Bit 0 (hodnota 1) znamená ignoruj skryté riadky, bit 1 (hodnota 2) znamená ignoruj chybové hodnoty a bit 2 (hodnota 4) znamená prestaň ignorovať vnorené bunky SUBTOTAL a AGGREGATE, pretože ich preskakovanie je predvolené pre nízke kódy. Dve veci sa na tom dajú ľahko pochopiť naopak. Bit skrytých riadkov je ten najnižší, nie prostredný, takže AGGREGATE(9,1,...) je forma s filtrovaným súčtom a AGGREGATE(9,2,...) je tá tolerantná voči chybám. A politika vnorených agregácií je voči tým dvom obrátená: len kódy 4 až 7 berú bunku, ktorej vlastná formula je SUBTOTAL alebo AGGREGATE, ako obyčajnú hodnotu. ECMA-376 Part 1 §18.17.7 definuje SUBTOTAL s tým istým rozdelením na zahrnutie alebo vylúčenie skrytých riadkov cez kódy 1-11 a 101-111 a AGGREGATE, uložené v OOXML súboroch pod prefixom _xlfn., to rozdelenie zovšeobecňuje do argumentu options, takže tabuľka, ktorú Microsoft pre funkciu AGGREGATE publikuje, je kontrakt, ktorý engine musí splniť, a nie pohodlie

Voľba	Skryté riadky	Chybové hodnoty	Vnorené SUBTOTAL / AGGREGATE
0	zahrnuté	propagované	ignorované
1	ignorované	propagované	ignorované
2	zahrnuté	ignorované	ignorované
3	ignorované	ignorované	ignorované
4	zahrnuté	propagované	zahrnuté
5	ignorované	propagované	zahrnuté
6	zahrnuté	ignorované	zahrnuté
7	ignorované	ignorované	zahrnuté

Prečo mal HotXLS voľby AGGREGATE naopak?

Pretože pôvodné TXLSCalculator.CalcAggregateFunc bolo napísané podľa parafrázy tabuľky a nie podľa tabuľky. Počítalo ignoreErrors := (optCode >= 4) and (optCode <= 7) a vyzbrojilo gate skrytých riadkov pre kódy 2, 3, 6 a 7, kým politika vnorených agregácií nebola implementovaná vôbec. Skorší článok o skrytých riadkoch v SUBTOTAL a AGGREGATE uvádzal tú medzeru ako otvorený limit a opisoval staré mapovanie tak, ako vtedy vyšlo; ten opis bol presný ohľadne kódu a nesprávny ohľadne Excelu a nikto si to dlho nevšimol, pretože dve politiky, ktoré ľudia najčastejšie kombinujú, skryté plus chyby, padajú na kódy 3 a 7 v oboch tabuľkách. Výmenu odhalil len jednobitový kód: AGGREGATE(9,1,A1:A4) vracal nefiltrovaný súčet a AGGREGATE(9,2,...) preskakoval skryté riadky, ale stále propagoval #DIV/0!. Defekt sa vynoril zo statickej revízie lxCalc.pas, zapísaný ako HXLS-008 v registri známych problémov projektu, nie zo zákazníckeho súboru, čo čosi hovorí o tom, ako zriedka sa jednobitové kódy v produkčných zošitoch vyskytujú. Verzia 2.382.0 prepísala dekódovanie na tri testy členstva v množine a pridala druhý gate pre vnorenú politiku, zapojený cez nový callback TXLSIsSubtotalCell, ktorý workbook poskytuje popri TXLSIsRowHidden

// TXLSCalculator.CalcAggregateFunc, tvar z v2.382.3
if (optCode < 0) or (optCode > 7) then
begin
  Result := lxErrorValue;            // Excel odmieta kódy mimo 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
  // ... namapuj function_num na vnútorný iftab, prejdi ref1..refN ...
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
  FIgnoreSubtotalCells := prevIgnoreSubtotal;
end;

Všimnite si, že tie dva príznaky sa priraďujú bezpodmienečne, a nie len nastavujú vtedy, keď o ne voľba žiada. Verzia 2.382.0 ešte používala if ... then FIgnoreHiddenRows := True, čo znamenalo, že AGGREGATE s kódom 4 vnorený do SUBTOTAL(109, ...) zdedil vonkajší gate skrytých riadkov namiesto toho, aby ho vyčistil. Priradenie dekódovanej hodnoty na vstupe a obnovenie predchádzajúcej hodnoty vo finally bloku spôsobí, že každé volanie AGGREGATE vlastní svoju politiku na čas svojho prechodu a nič viac. Verzia 2.382.0 tiež spravila pole v argumente čestným: keď sa argument vyhodnotí na jednorozmerné alebo dvojrozmerné pole Variant, CalcAggregateFunc teraz prejde každý element a aplikuje chybovú politiku na element, kým starý kód testoval len NaN double a inak odovzdal celé pole do ExcelSum

Prečo vonkajšie AGGREGATE preniká do formúl, na ktoré odkazuje?

Pretože FIgnoreHiddenRows a FIgnoreSubtotalCells sú polia na kalkulátore a kalkulátor je zdieľaný každou formulou, ktorá sa počas jedného prepočtu vyhodnocuje. Tie gate boli navrhnuté ako scratch polia práve preto, aby ich šesť slučiek prechádzajúcich bunky mohlo konzultovať bez pretláčania parametra cez každú signatúru, a ten dizajn je zdravý, dokým všetko, čo beží počas vyzbrojeného gatu, patrí agregácii, ktorá ho vyzbrojila. Predpoklad sa láme v jednom konkrétnom bode: FGetValue. Keď sa walker pýta workbooku na hodnotu bunky a tá bunka drží formulu bez cachovaného výsledku, workbook tú formulu skompiluje a vyhodnotí na mieste, na tom istom TXLSCalculator, s vonkajšími gatmi stále nastavenými. Regresná fixtura v HotXLS.WorkbookApiTests.pas ukazuje toto zlyhanie na štyroch bunkách. A1 drží 10, A2 drží 20 na skrytom riadku, A3 drží =1/0 a A4 drží =SUBTOTAL(9,A1:A2), ktorej správna hodnota je 30. Teraz vyhodnoťte =AGGREGATE(9,7,A1:A4): ignoruj skryté riadky, ignoruj chyby, počítaj vnorený subtotal ako hodnotu. Excel vracia 10 + 30 = 40. S necachovanou A4 engine pred v2.382.3 vyzbrojil gate skrytých riadkov, prešiel na A4, spustil jej vyhodnotenie a CalcSubtotalFunc pre kód 9 zdedil vyzbrojený gate, pretože príznak nastavuje len pre kódy 101 až 111 a nikdy ho nevyčistí. A4 sa vyhodnotila na 10 namiesto 30 a vonkajší súčet vyšel ako 20. Ani jedna z tých dvoch formúl nespomína skryté riadky na ceste, ktorá vyprodukovala nesprávne číslo

Gate vnorených agregácií prenikal rovnako aj opačným smerom. Pri kódoch 0 až 3 je FIgnoreSubtotalCells vyzbrojené a generický prechod rozsahom v GetValueItemRange ho rešpektuje, takže precedent, ktorého formula je =SUM(B1:B3), by potichu vyhodil B2, ak by B2 náhodou obsahovala SUBTOTAL. Horšie, CalcSubtotalFunc resetuje FIgnoreSubtotalCells na False pri odchode namiesto obnovenia predchádzajúcej hodnoty, takže necachovaný SUBTOTAL precedent dosiahnutý v polovici prechodu odzbrojil vonkajší gate pre každú bunku po ňom. Register známych problémov projektu to vedie pod HXLS-008 ako nested selection state leakage, a to je správne meno pre tú triedu chýb: globálny prechodný príznak, ktorý je správny pre rámec, ktorý ho nastavil, a nesprávny pre každý rámec, ktorý ho zdedí

Ako AggregateGetCellValue a AggregateGetItemValue izolujú prechod

Oprava vo v2.382.3 stavia hranicu okolo každého bodu, kde AGGREGATE číta hodnotu, ktorú si sám nevypočítal. TXLSCalculator.AggregateGetCellValue obalí surové volanie FGetValue: uloží oba príznaky, vyčistí ich, vykoná načítanie a vo finally bloku ich obnoví. Vonkajšia agregácia stále aplikuje svoju vlastnú politiku na bunku, ktorú práve načítala, pretože testy skrytých riadkov a vnorených buniek sa dejú vo walkeri okolo načítania, ale samotná formula precedentu beží úplne bez politiky, čo je to, čo robí Excel

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;        // formula precedentu vlastní svoju vlastnú politiku
  FIgnoreSubtotalCells := False;
  try
    Result := FGetValue(SheetIndex, Row, Col, Value, OutOfRange);
  finally
    FIgnoreHiddenRows := Hidden;
    FIgnoreSubtotalCells := Nested;
  end;
end;

AggregateGetItemValue robí to isté pre argumenty, ktoré nie sú rozsahom, a musí urobiť viac než len vyčistiť príznaky, pretože argument ako A1:A4/(B1:B4-20) je vypočítané pole, ktorého tvar elementov musí prežiť. Wrapper materializuje obyčajný rozsah do dvojrozmerného poľa Variant cez AggregateGetCellValue, pričom bunku, ktorá vrátila kód chyby, mapuje na VarAsError, aby sa chybová politika dala stále aplikovať na element, a rekurzívne prechádza binárne a unárne operátorové uzly (SA_ADD, SA_DIV, SA_UNARMINUS a ostatné) cez ApplyArrayBinaryOp a ApplyArrayUnaryOp; čokoľvek iné prepadne na bežný GetValueItem. Pred materializáciou sedia dve stráže: rozsah väčší než EffectiveFormulaArrayMemoryLimit vráti lxErrorResourceLimit a viaclistový alebo prevrátený rozsah vráti #VALUE!. Kód limitu zdrojov sa zámerne neberie ako ignorovateľná chyba bunky ani pod voľbami 2/3/6/7, keďže engine, ktorý by prehltol svoj vlastný out-of-memory signál len preto, že používateľ požiadal o preskočenie #N/A, by klamal. Všetky tri AGGREGATE walkery, AggregateCollectRange pre rodinu SUM, AggregateReduceVariance pre STDEV, VAR a PRODUCT a AggregateReduceWithK pre MEDIAN a kvantilové formy, boli prepnuté z FGetValue a GetValueItem na tie dva wrappery a každý dostal test vnorenej bunky cez FIsSubtotalCell

Ktorú chybu AGGREGATE vráti, keď chyby neignoruje?

Tú pôvodnú, od verzie v2.382.3. Verzia 2.382.0 detegovala chybové bunky správne, ale všetky zrútila na lxErrorValue, takže AGGREGATE(9,4,A1:A3) nad bunkou s #DIV/0! vracalo #VALUE!, kým Excel propaguje prvú chybu, na ktorú narazí, nezmenenú. Náhradný helper AggregateErrorCode mapuje Variant na zodpovedajúci kód lxError*, či už je ten Variant skutočný varError, alebo jeden zo siedmich chybových reťazcov, a AggregateValueIsError je teraz len test na nenulový výsledok. Každý walker si zaznamená prvý kód chyby, ktorý vidí, a vráti ten kód, čo zároveň znamená, že bunka, ktorej formula nebola nikdy spočítaná a ktorej chyba teda prichádza ako návratový kód z FGetValue a nie ako cachovaný Variant, sa propaguje rovnako ako cachovaná. Dve počítacie funkcie dostávajú vnútri AggregateCollectRange špeciálne spracovanie a to spracovanie zodpovedá SUBTOTAL, nie SUM. Pre vnútornú funkciu 0, COUNT, sa chybová bunka nikdy nepočíta a nikdy nepropaguje bez ohľadu na kód volieb, pretože COUNT počíta len čísla. Pre vnútornú funkciu 169, COUNTA, je chybová bunka neprázdna hodnota a počíta sa ako 1, ak kód volieb neignoruje chyby, v ktorom prípade sa preskočí. Tá asymetria je spôsob, akým Excel zaobchádza s COUNT a COUNTA aj mimo AGGREGATE, a je to ten druh detailu, ktorý generické pravidlo "ak chyba, tak propaguj" potichu spraví zle

Čo overuje regresná matica ôsmich volieb

Fixtura opísaná vyššie je precvičovaná ako plná matica v AggregateFunc_OptionMatrixCoversHiddenErrorsAndNestedAggregates: pre každý kód volieb od 0 do 7 vyhodnotí formu SUM aj formu MEDIAN nad A1:A4 a skontroluje výsledok proti ručne odvodenej očakávanej hodnote. Kódy 0, 1, 4 a 5 musia propagovať #DIV/0! z A3, keďže žiadny z nich neignoruje chyby. Kód 2 dáva SUM 30 a MEDIAN 15, z 10 a 20 s preskočenou vnorenou A4. Kód 3 dáva 10 a 10. Kód 6 dáva 60 a 20, pretože tých 30 v A4 sa teraz počíta. Kód 7 dáva 40 a 20, čo je ten prípad, ktorý pred opravou úniku vracal 20. Širší akceptačný beh zaznamenaný v registri známych problémov pokrýva všetkých deväťnásť čísel funkcií proti všetkým ôsmim kódom, s každým precedentom cachovaným aj necachovaným, teda 304 scenárov na Win32 a 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)';   // skupinový subtotal = 30
    Sheet.RowHidden[2] := True;

    Sheet.Cells[6, 1].Formula := '=AGGREGATE(9,1,A1:A4)'; // #DIV/0!  skryté preskočené, chyba sa propaguje
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       skryté + chyba + vnorené preskočené
    Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60       preskočené len chyby
    Sheet.Cells[9, 1].Formula := '=AGGREGATE(9,7,A1:A4)'; // 40       pred v2.382.3 bolo 20
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

Kde tá hranica ešte je

Tri limity stoja za to vedieť, než na tomto postavíte. Po prvé, predikát vnorenej agregácie je textový. TXLSXWorkbook.GetCalcIsSubtotalCell a jeho dvojča pre classic engine vrátia True, keď formula bunky začína SUBTOTAL(, AGGREGATE( alebo _xlfn.AGGREGATE(, s vedúcim znamienkom rovná sa alebo bez neho, takže formula ako =IF(C1,SUBTOTAL(9,B1:B9),0) alebo =SUBTOTAL(9,B1:B9)*2 sa nerozpozná ako vnorená a kódy 0 až 3 ju započítajú dvakrát tam, kde by Excel preskočil; generátor, ktorý emituje vypočítané subtotality, by mal držať volanie agregácie na čele formuly. Po druhé, izolácia žije v troch AGGREGATE walkeroch. CalcSubtotalFunc stále prechádza cez GetValueItemRange, CollectRangeValues a SubtotalReduceVariance, ktoré volajú FGetValue priamo, takže SUBTOTAL(109, ...), ktorého rozsah obsahuje necachovaný precedent, môže stále odovzdať svoj gate skrytých riadkov do toho precedentu. Úplné Recalculate vyhodnocuje precedenty pred závislými, takže sa použije cachovaná cesta a gate sa nikdy nezdedí; expozícia je obmedzená na ad hoc vyhodnotenie cez Calculate a na zošity načítané bez cachovaných hodnôt, a ak sa spoliehate na inkrementálny prepočet nad grafom závislostí, aby veľké modely zostali svižné, práve tá istá záruka poradia drží tento únik v latentnom stave. Po tretie, oba gate sú podmienené Assigned(FIsRowHidden) a Assigned(FIsSubtotalCell). Obe fasády workbooku tie callbacky zapájajú vo svojich konštruktoroch, ale kód, ktorý si TXLSCalculator postaví ručne len s dvoma pôvodnými argumentmi, dostane legacy správanie zahrňujúce všetko pre každý kód volieb, a to potichu. Keď súčet vyzerá zle a text formuly vyzerá dobre, vystopovanie vyhodnotenia krok za krokom je najrýchlejšia cesta, ako zistiť, či sa precedent vyhodnotil pod zdedeným gatom alebo či sa callback jednoducho nikdy nezapojil

Výpočtový engine opísaný vyššie, dekodér volieb, izolované wrappery načítania aj regresná matica, ktorá ich pripína, sú dodávané ako zdrojový kód s HotXLS Delphi spreadsheet komponentou, ktorá číta, zapisuje a prepočítava zošity XLS, XLSX a ODS v Delphi a C++Builder bez inštalácie Excelu