Matice voleb AGGREGATE a únik brány v HotXLS pro Delphi

HotXLS, nativní Excel tabulková komponenta pro Delphi a C++Builder, dodala v září 2026 dvě související opravy AGGREGATE. Verze 2.382.0 napravila argument options tak, že kódy 1/3/5/7 ignorují skryté řádky, 2/3/6/7 ignorují chyby a 0 až 3 ignorují vnořené buňky SUBTOTAL a AGGREGATE, přesně jak to dokumentuje Microsoft. Verze 2.382.3 pak zabránila těmto výběrovým příznakům prosakovat do vyhodnocování buněk, na které funkce právě odkazuje. První vada je trapná způsobem, jakým bývají bugy přepisu tabulek vždycky: bitové pozice byly prohozené, takže každý vzorec, který použil nenulový options kód, dostal politiku, kterou jeho autor nežádal. Druhá je zajímavější, protože je to tvar, na který narazíte v jakémkoli evaluátoru, který používá přechodné pole k předání kontextu rekurzivnímu průchodu. Vnější agregace nastaví příznak, projde rozsah a natáhne buňku, jejíž vzorec ještě nebyl spočítaný. Tenhle vzorec běží na stejném kalkulátoru, vidí tentýž nastavený příznak a potichu agreguje špatné řádky, takže výsledkem je číslo, které se míjí o hodnotu, kterou nikdo nedokáže vysvětlit ze samotného textu vzorce

Co volby AGGREGATE 0 až 7 doopravdy vybírají?

Argument options AGGREGATE je tříbitová matice a tři bity jsou nezávislé. Bit 0 (hodnota 1) znamená ignorovat skryté řádky, bit 1 (hodnota 2) znamená ignorovat chybové hodnoty a bit 2 (hodnota 4) znamená přestat ignorovat vnořené buňky SUBTOTAL a AGGREGATE, protože jejich vynechávání je default pro nízké kódy. Dvě věci se na tom snadno spletou pozpátku. Bit skrytých řádků je spodní bit, ne prostřední, takže AGGREGATE(9,1,...) je forma filtrovaného součtu a AGGREGATE(9,2,...) je ta odolná vůči chybám. A politika vnořených agregátů je obrácená vůči ostatním dvěma: jen kódy 4 až 7 berou buňku, jejíž vlastní vzorec je SUBTOTAL nebo AGGREGATE, jako obyčejnou hodnotu. ECMA-376 Part 1 §18.17.7 definuje SUBTOTAL se stejným rozdělením zahrň-vyjmi skryté řádky napříč kódy 1-11 a 101-111 a AGGREGATE, ukládaný v OOXML souborech pod prefixem _xlfn., generalizuje to rozdělení do argumentu options, takže tabulka, kterou Microsoft publikuje pro funkci AGGREGATE, je kontrakt, který musí engine splnit, ne pohodlí

Volba	Skryté řádky	Chybové hodnoty	Vnořené SUBTOTAL / AGGREGATE
0	zahrnuty	propagují se	ignorují se
1	ignorují se	propagují se	ignorují se
2	zahrnuty	ignorují se	ignorují se
3	ignorují se	ignorují se	ignorují se
4	zahrnuty	propagují se	zahrnuty
5	ignorují se	propagují se	zahrnuty
6	zahrnuty	ignorují se	zahrnuty
7	ignorují se	ignorují se	zahrnuty

Proč mělo HotXLS volby AGGREGATE pozpátku?

Protože původní TXLSCalculator.CalcAggregateFunc byl psaný z parafráze tabulky, ne z tabulky. Počítal ignoreErrors := (optCode >= 4) and (optCode <= 7) a nastavoval bránu skrytých řádků pro kódy 2, 3, 6 a 7, zatímco politika vnořených agregátů nebyla implementovaná vůbec. Dřívější článek o skrytých řádcích SUBTOTAL a AGGREGATE tu mezeru vyjmenovával jako otevřený limit a popisoval staré mapování, jak tehdy vyjelo; popis byl přesný ohledně kódu a špatný ohledně Excelu a nikdo si toho dlouho nevšiml, protože dvě politiky, které lidé nejčastěji kombinují, skryté plus chyby, padají na kódy 3 a 7 pod oběma tabulkami. Prohodit bity prozradil jen jednobitový kód: AGGREGATE(9,1,A1:A4) vrátil nefiltrovaný součet a AGGREGATE(9,2,...) vynechával skryté řádky, zatímco pořád propagoval #DIV/0!. Vada vyšla najevo ze statického přezkumu lxCalc.pas, zapsaná jako HXLS-008 do registru známých problémů projektu, ne ze zákaznického souboru, což něco říká o tom, jak vzácně se jednobitové kódy objevují v produkčních sešitech. Verze 2.382.0 přepsala dekód na tři testy členství v množině a přidala druhou bránu pro politiku vnořených, vedenou přes nový callback TXLSIsSubtotalCell, který sešit poskytuje po boku TXLSIsRowHidden

// TXLSCalculator.CalcAggregateFunc, podoba v2.382.3
if (optCode < 0) or (optCode > 7) then
begin
  Result := lxErrorValue;            // Excel odmítá 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
  // ... mapuj function_num na vnitřní iftab, projdi ref1..refN ...
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
  FIgnoreSubtotalCells := prevIgnoreSubtotal;
end;

Všimněte si, že oba příznaky se přiřazují bezpodmínečně, místo aby se nastavovaly jen tehdy, když si to volba žádá. Verze ve v2.382.0 pouštěla pořád if ... then FIgnoreHiddenRows := True, což znamenalo, že AGGREGATE s kódem 4 vnořený do SUBTOTAL(109, ...) zdědí vnější bránu skrytých řádků místo jejího vymazání. Přiřazení dekódované hodnoty na vstupu a obnovení předchozí hodnoty v bloku finally zařizuje, že každé volání AGGREGATE vlastní svou politiku po dobu svého průchodu a nic víc. Verze 2.382.0 taky upřímnila formu pole: když se argument vyhodnotí na jednorozměrné nebo dvourozměrné Variant pole, CalcAggregateFunc teď projde každý prvek a aplikuje error politiku na prvek, kde starý kód testoval jen NaN double a jinak podal celé pole ExcelSum

Proč se vnější AGGREGATE prosakuje do vzorců, jež referencuje?

Protože FIgnoreHiddenRows a FIgnoreSubtotalCells jsou pole na kalkulátoru a kalkulátor sdílí každý vzorec vyhodnocovaný během jednoho přepočtu. Brány byly stavěné jako scratch pole přesně proto, aby o nich šest cyklů procházení buněk mohlo vědět bez protahování parametru skrz každou signaturu, a ten design je zdravý, dokud všechno, co běží, zatímco je brána nastavená, patří agregaci, která ji nastavila. Předpoklad se láme na jednom konkrétním místě: FGetValue. Když walker žádá sešit o hodnotu buňky a ta buňka drží vzorec bez cachovaného výsledku, sešit vzorec zkompiluje a vyhodnotí na místě, na stejném TXLSCalculator, s vnějšími bránami pořád nastavenými. Regresní fixture v HotXLS.WorkbookApiTests.pas ukazuje selhání na čtyřech buňkách. A1 drží 10, A2 drží 20 na skrytém řádku, A3 drží =1/0 a A4 drží =SUBTOTAL(9,A1:A2), jehož správná hodnota je 30. Teď vyhodnoťte =AGGREGATE(9,7,A1:A4): ignoruj skryté řádky, ignoruj chyby, ber vnořený subtotal jako hodnotu. Excel vrací 10 + 30 = 40. S A4 necachovaným nastavil engine před 2.382.3 bránu skrytých řádků, došel k A4, spustil jeho vyhodnocení a CalcSubtotalFunc pro kód 9 zdědil nastavenou bránu, protože příznak nastavuje jen pro kódy 101 až 111 a nikdy ho nemaže. A4 se vyhodnotilo na 10 místo 30 a vnější součet se vrátil jako 20. Nic v žádném z obou vzorců nezpomíná skryté řádky na cestě, která vyprodukovala špatné číslo

Brána vnořených agregátů prosakovala stejným způsobem opačným směrem. U kódů 0 až 3 je FIgnoreSubtotalCells nastavené a generický walker rozsahů v GetValueItemRange ho respektuje, takže precedent, jehož vzorec je =SUM(B1:B3), by potichu zahodil B2, kdyby B2 náhodou obsahovalo SUBTOTAL. Hůř, CalcSubtotalFunc resetuje FIgnoreSubtotalCells na False na výstupu místo obnovení předchozí hodnoty, takže necachovaný SUBTOTAL precedent dosažený v půlce průchodu odzbrojil vnější bránu pro každou buňku po něm. Registr známých problémů projektu vede tohle pod HXLS-008 jako únik vnořeného výběrového stavu a to je správné jméno pro tuhle třídu bugů: globální přechodný příznak, který je správný pro rámec, který ho nastavil, a špatný pro každý rámec, který ho zdědí

Jak AggregateGetCellValue a AggregateGetItemValue izolují průchod

Oprava ve v2.382.3 dává hranici kolem každého bodu, kde AGGREGATE čte hodnotu, kterou sám nespočítal. TXLSCalculator.AggregateGetCellValue obaluje surové volání FGetValue: uloží oba příznaky, vymaže je, provede fetch a obnoví je v bloku finally. Vnější agregace pořád aplikuje svou vlastní politiku na buňku, kterou právě natáhla, protože testy skrytých řádků a vnořených buněk se dějí ve walkeru kolem fetch, ale precedentní vzorec sám běží bez jakékoli politiky, což je to, co dělá 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;        // precedentní vzorec vlastní svou politiku
  FIgnoreSubtotalCells := False;
  try
    Result := FGetValue(SheetIndex, Row, Col, Value, OutOfRange);
  finally
    FIgnoreHiddenRows := Hidden;
    FIgnoreSubtotalCells := Nested;
  end;
end;

AggregateGetItemValue dělá totéž pro argumenty mimo rozsah a musí udělat víc než vymazat příznaky, protože argument jako A1:A4/(B1:B4-20) je vypočtené pole, jehož tvar prvků musí přežít. Wrapper materializuje holý rozsah do dvourozměrného Variant pole přes AggregateGetCellValue a mapuje buňku vracející chybový kód na VarAsError, takže error politika se pořád dá aplikovat na prvek, a rekurzuje skrz binární i unární uzly operátorů (SA_ADD, SA_DIV, SA_UNARMINUS a zbytek) přes ApplyArrayBinaryOp a ApplyArrayUnaryOp; cokoli jiného propadne k normálnímu GetValueItem. Před materializací sedí dvě hlídky: rozsah větší než EffectiveFormulaArrayMemoryLimit vrací lxErrorResourceLimit a multi-listový nebo obrácený rozsah vrací #VALUE!. Kód resource limitu se záměrně nebere jako ignorovatelná chyba buňky ani pod volbami 2/3/6/7, protože engine, který spolkne vlastní signál nedostatku paměti, protože uživatel žádal vynechávat #N/A, by lhal. Všechny tři walkery AGGREGATE, AggregateCollectRange pro rodinu SUM, AggregateReduceVariance pro STDEV, VAR a PRODUCT a AggregateReduceWithK pro MEDIAN a kvantilové formy, se přepnuly z FGetValue a GetValueItem na dva wrappery a každý dostal test vnořených buněk přes FIsSubtotalCell

Kterou chybu vrací AGGREGATE, když chyby neignoruje?

Původní, od v2.382.3. Verze 2.382.0 detekovala chybové buňky správně, ale slipla každou z nich do lxErrorValue, takže AGGREGATE(9,4,A1:A3) nad buňkou #DIV/0! vrátila #VALUE!, kde Excel propaguje první chybu, na kterou narazí, beze změny. Náhradní helper AggregateErrorCode mapuje Variant na odpovídající kód lxError*, zda je Variant opravdové varError, nebo jeden ze sedmi chybových řetězců, a AggregateValueIsError je teď jen test na nenulový výsledek. Každý walker zaznamená první chybový kód, který vidí, a vrátí ten kód, což taky znamená, že buňka, jejíž vzorec nebyl nikdy spočítaný a jejíž chyba proto přichází jako návratový kód z FGetValue místo jako cachovaný Variant, propaguje stejně jako cachovaná. Dvě počítací funkce dostávají speciální zacházení uvnitř AggregateCollectRange a zacházení odpovídá SUBTOTAL místo SUM. Pro vnitřní funkci 0, COUNT, se chybová buňka nikdy nepočítá a nikdy nepropaguje bez ohledu na options kód, protože COUNT počítá jen čísla. Pro vnitřní funkci 169, COUNTA, je chybová buňka neprázdná hodnota a počítá se jako 1, pokud options kód neignoruje chyby, v tom případě se vynechává. Ta asymetrie je způsob, jakým Excel zachází s COUNT a COUNTA i mimo AGGREGATE, a je to druh detailu, který generické pravidlo „pokud chyba, tak propaguj" potichu trefí špatně

Co regresní matice osmi voleb ověřuje

Fixture popsaná výše se protahuje jako plná matice v AggregateFunc_OptionMatrixCoversHiddenErrorsAndNestedAggregates: pro každý options kód od 0 do 7 vyhodnotí jak formu SUM, tak formu MEDIAN nad A1:A4 a zkontroluje výsledek proti ručně odvozenému očekávání. Kódy 0, 1, 4 a 5 musí propagovat #DIV/0! z A3, protože žádný neignoruje chyby. Kód 2 dává SUM 30 a MEDIAN 15, z 10 a 20 s vynechaným vnořeným A4. Kód 3 dává 10 a 10. Kód 6 dává 60 a 20, protože 30 v A4 se teď počítá. Kód 7 dává 40 a 20, což je případ, který vracel 20 před opravou úniku. Širší acceptance běh zaznamenaný v registru známých problémů pokrývá všech devatenáct čísel funkcí proti všem osmi kódům, s každým precedentem cachovaným i necachovaným, za 304 scénářů 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é vynechány, chyba propaguje
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       skryté + chyba + vnořené vynechány
    Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60       vynechány jen chyby
    Sheet.Cells[9, 1].Formula := '=AGGREGATE(9,7,A1:A4)'; // 40       bylo 20 před v2.382.3
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

Kde hranice pořád je

Tři limity stojí za znát, než na tom postavíte. Za prvé, predikát vnořených agregátů je textový. TXLSXWorkbook.GetCalcIsSubtotalCell a jeho dvojče klasického enginu odpovídají True, když vzorec buňky začíná SUBTOTAL(, AGGREGATE( nebo _xlfn.AGGREGATE(, s úvodním rovnítkem nebo bez, takže vzorec jako =IF(C1,SUBTOTAL(9,B1:B9),0) nebo =SUBTOTAL(9,B1:B9)*2 se nepozná jako vnořený a kódy 0 až 3 ho započtou dvakrát, kde by ho Excel vynechal; generátor, který emituje vypočtené subtotaly, by měl držet volání agregace na hlavě vzorce. Za druhé, izolace bydlí ve třech walkerech AGGREGATE. CalcSubtotalFunc pořád projde přes GetValueItemRange, CollectRangeValues a SubtotalReduceVariance, které volají FGetValue přímo, takže SUBTOTAL(109, ...), jehož rozsah obsahuje necachovaný precedentní vzorec, může pořád protáhnout svoji bránu skrytých řádků do toho precedentu. Plný Recalculate vyhodnocuje precedenty před závislými, takže se jde cachovanou cestou a brána se nikdy nezdědí; expozice je omezená na ad hoc vyhodnocení přes Calculate a na sešity načtené bez cachovaných hodnot, a pokud se spoléháte na inkrementální přepočet nad grafem závislostí, aby velké modely zůstaly svižné, totéž pořadová záruka je to, co drží tenhle únik spánku. Za třetí, obě brány jsou podmíněné Assigned(FIsRowHidden) a Assigned(FIsSubtotalCell). Obě fasády sešitu vedou callbacky ve svých konstruktorech, ale kód, který staví TXLSCalculator ručně s jen dvěma původními argumenty, dostane legacy chování zahrň-vše pro každý options kód, potichu. Když součet vypadá špatně a text vzorce vypadá dobře, trasování vyhodnocení krok za krokem je nejrychlejší způsob, jak zjistit, jestli se precedent vyhodnotil pod zděděnou bránou, nebo jestli callback prostě nebyl nikdy připojený

Výpočetní engine popsaný tady, dekodér voleb, izolované fetch wrappery i regresní matice, která je přibíjí, všechny se dodávají jako zdroják s tabulkovou komponentou HotXLS pro Delphi, která čte, zapisuje a přepočítává sešity XLS, XLSX a ODS v Delphi a C++Builderu bez instalace Excelu