AGGREGATE opcije i curenje gatea u HotXLS-u za Delphi

HotXLS, izvorna Excel komponenta za proračunske tablice za Delphi i C++Builder, isporučio je u rujnu 2026. dva povezana AGGREGATE popravka. Verzija 2.382.0 ispravila je argument s opcijama tako da kodovi 1/3/5/7 ignoriraju skrivene retke, 2/3/6/7 ignoriraju greške, a 0 do 3 ignoriraju ugniježđene SUBTOTAL i AGGREGATE ćelije, točno onako kako Microsoft dokumentira. Verzija 2.382.3 zatim je zaustavila da se te zastavice odabira preliju u evaluaciju upravo onih ćelija koje funkcija referencira. Prva greška neugodna je na način na koji su greške u prepisivanju tablica uvijek neugodne: bit pozicije bile su zamijenjene, pa je svaka formula koja je koristila opcijski kod različit od nule dobila politiku koju njezin autor nije tražio. Druga je zanimljivija, jer je to oblik na koji ćete naići u svakom evaluatoru koji koristi prolazno polje za prenošenje konteksta u rekurzivni hod. Vanjska agregacija naoruža zastavicu, prođe kroz raspon i povuče ćeliju čija formula još nije izračunata. Ta formula izvršava se na istom kalkulatoru, vidi istu naoružanu zastavicu i tiho agregira pogrešne retke, dajući broj koji odstupa za iznos koji nitko ne može objasniti samo iz teksta formule

Što AGGREGATE opcije 0 do 7 zapravo odabiru?

Argument s opcijama funkcije AGGREGATE je matrica od tri bita, i ta tri bita neovisna su. Bit 0 (vrijednost 1) znači ignoriraj skrivene retke, bit 1 (vrijednost 2) znači ignoriraj vrijednosti grešaka, a bit 2 (vrijednost 4) znači prestani ignorirati ugniježđene SUBTOTAL i AGGREGATE ćelije, jer je njihovo preskakanje zadano za niske kodove. Dvije je stvari oko toga lako okrenuti naopako. Bit skrivenih redaka je niski bit, a ne srednji, pa je AGGREGATE(9,1,...) oblik filtriranog zbroja, a AGGREGATE(9,2,...) onaj tolerantan na greške. A politika ugniježđenih agregacija obrnuta je u odnosu na druge dvije: samo kodovi 4 do 7 tretiraju ćeliju čija je vlastita formula SUBTOTAL ili AGGREGATE kao običnu vrijednost. ECMA-376 Part 1 §18.17.7 definira SUBTOTAL s istom podjelom na uključivanje ili isključivanje skrivenih redaka kroz kodove 1-11 i 101-111, a AGGREGATE, spremljen u OOXML datotekama pod prefiksom _xlfn., tu podjelu generalizira u argument s opcijama, pa je tablica koju Microsoft objavljuje za funkciju AGGREGATE ugovor koji engine mora ispuniti, a ne pogodnost

Opcija	Skriveni reci	Vrijednosti grešaka	Ugniježđeni SUBTOTAL / AGGREGATE
0	uključeni	propagira se	ignoriraju se
1	ignoriraju se	propagira se	ignoriraju se
2	uključeni	ignoriraju se	ignoriraju se
3	ignoriraju se	ignoriraju se	ignoriraju se
4	uključeni	propagira se	uključeni
5	ignoriraju se	propagira se	uključeni
6	uključeni	ignoriraju se	uključeni
7	ignoriraju se	ignoriraju se	uključeni

Zašto je HotXLS imao AGGREGATE opcije naopako?

Zato što je izvorni TXLSCalculator.CalcAggregateFunc napisan prema parafrazi tablice, a ne prema tablici. Računao je ignoreErrors := (optCode >= 4) and (optCode <= 7) i naoružavao gate za skrivene retke za kodove 2, 3, 6 i 7, dok politika ugniježđenih agregacija nije bila implementirana uopće. Raniji članak o skrivenim recima u SUBTOTAL i AGGREGATE naveo je tu prazninu kao otvoreno ograničenje i opisao staro preslikavanje onako kako je tada bilo isporučeno; opis je bio točan o kodu, a pogrešan o Excelu, i nitko to dugo nije primijetio jer dvije politike koje većina ljudi kombinira, skriveno plus greške, padaju na kodove 3 i 7 pod obama tablicama. Samo je kod s jednim bitom razotkrio zamjenu: AGGREGATE(9,1,A1:A4) vraćao je nefiltrirani zbroj, a AGGREGATE(9,2,...) preskakao je skrivene retke dok je i dalje propagirao #DIV/0!. Greška je isplivala iz statičkog pregleda lxCalc.pas, evidentirana kao HXLS-008 u registru poznatih problema projekta, a ne iz datoteke kupca, što govori nešto o tome kako rijetko kodovi s jednim bitom pojavljuju u produkcijskim radnim knjigama. Verzija 2.382.0 prepisala je dekodiranje u tri testa pripadnosti skupu i dodala drugi gate za ugniježđenu politiku, spojen kroz novi TXLSIsSubtotalCell callback koji radna knjiga pruža uz TXLSIsRowHidden

// TXLSCalculator.CalcAggregateFunc, oblik iz v2.382.3
if (optCode < 0) or (optCode > 7) then
begin
  Result := lxErrorValue;            // Excel odbija kodove izvan 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
  // ... preslikaj function_num u unutarnji iftab, prođi ref1..refN ...
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
  FIgnoreSubtotalCells := prevIgnoreSubtotal;
end;

Primijetite da se dvije zastavice dodjeljuju bezuvjetno, a ne samo postavljaju kad to opcija traži. Verzija 2.382.0 još je koristila if ... then FIgnoreHiddenRows := True, što je značilo da AGGREGATE s kodom 4 ugniježđen unutar SUBTOTAL(109, ...) nasljeđuje vanjski gate za skrivene retke umjesto da ga očisti. Dodjela dekodirane vrijednosti pri ulazu i vraćanje prethodne vrijednosti u finally bloku čine da svaki AGGREGATE poziv posjeduje svoju politiku za vrijeme trajanja svog hoda i ništa više. Verzija 2.382.0 učinila je i array oblik iskrenim: kad se argument evaluira u jednodimenzionalni ili dvodimenzionalni Variant array, CalcAggregateFunc sada prolazi kroz svaki element i primjenjuje politiku grešaka po elementu, dok je stari kod testirao samo na NaN double i inače predavao cijeli array funkciji ExcelSum

Zašto se vanjski AGGREGATE prelijeva u formule koje referencira?

Zato što su FIgnoreHiddenRows i FIgnoreSubtotalCells polja na kalkulatoru, a kalkulator dijeli svaka formula evaluirana tijekom jednog ponovnog izračuna. Gateovi su zamišljeni kao radna polja upravo zato da šest petlji za hod kroz ćelije može ih konzultirati bez provlačenja parametra kroz svaki potpis, i taj je dizajn ispravan dok god sve što se izvršava dok je gate naoružan pripada agregaciji koja ga je naoružala. Pretpostavka se lomi na jednoj konkretnoj točki: FGetValue. Kad walker zatraži od radne knjige vrijednost ćelije, a ta ćelija drži formulu bez cachiranog rezultata, radna knjiga kompajlira formulu i evaluira je odmah, na istom TXLSCalculatoru, s vanjskim gateovima još postavljenima. Regresijski uzorak u HotXLS.WorkbookApiTests.pas pokazuje kvar s četiri ćelije. A1 drži 10, A2 drži 20 na skrivenom retku, A3 drži =1/0, a A4 drži =SUBTOTAL(9,A1:A2), čija je ispravna vrijednost 30. Evaluirajte sada =AGGREGATE(9,7,A1:A4): ignoriraj skrivene retke, ignoriraj greške, računaj ugniježđeni subtotal kao vrijednost. Excel vraća 10 + 30 = 40. S necachiranim A4 engine prije v2.382.3 naoružao je gate za skrivene retke, došao do A4, pokrenuo njegovu evaluaciju, a CalcSubtotalFunc za kod 9 naslijedio je naoružani gate, jer on zastavicu postavlja samo za kodove 101 do 111 i nikad je ne čisti. A4 se evaluirao u 10 umjesto u 30, a vanjski zbroj vratio se kao 20. Ni jedna od tih dviju formula ne spominje skrivene retke na putu koji je proizveo pogrešan broj

Gate za ugniježđene agregacije curio je na isti način u drugom smjeru. S kodovima 0 do 3 FIgnoreSubtotalCells je naoružan, a generički walker raspona u GetValueItemRange ga poštuje, pa bi prethodnik čija je formula =SUM(B1:B3) tiho ispustio B2 ako bi B2 slučajno sadržavao SUBTOTAL. Još gore, CalcSubtotalFunc resetira FIgnoreSubtotalCells na False pri izlazu umjesto da vrati prethodnu vrijednost, pa je necachirani SUBTOTAL prethodnik dosegnut na pola hoda razoružao vanjski gate za svaku ćeliju nakon sebe. Registar poznatih problema projekta vodi to pod HXLS-008 kao curenje ugniježđenog stanja odabira, i to je pravo ime za tu klasu bugova: globalna prolazna zastavica koja je ispravna za okvir koji ju je postavio, a pogrešna za svaki okvir koji ju naslijedi

Kako AggregateGetCellValue i AggregateGetItemValue izoliraju hod

Popravak u v2.382.3 postavlja granicu oko svake točke gdje AGGREGATE čita vrijednost koju nije sam izračunao. TXLSCalculator.AggregateGetCellValue omata sirovi poziv FGetValue: sprema obje zastavice, čisti ih, obavlja dohvat i vraća ih u finally bloku. Vanjska agregacija i dalje primjenjuje svoju politiku na ćeliju koju je upravo dohvatila, jer se testovi skrivenih redaka i ugniježđenih ćelija događaju u walkeru oko dohvata, ali sama formula prethodnika izvršava se bez ikakve politike, što je ono što Excel radi

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 prethodnika posjeduje vlastitu politiku
  FIgnoreSubtotalCells := False;
  try
    Result := FGetValue(SheetIndex, Row, Col, Value, OutOfRange);
  finally
    FIgnoreHiddenRows := Hidden;
    FIgnoreSubtotalCells := Nested;
  end;
end;

AggregateGetItemValue radi isto za argumente koji nisu rasponi, i mora učiniti više od čišćenja zastavica, jer je argument poput A1:A4/(B1:B4-20) izračunati array čiji oblik elemenata mora preživjeti. Wrapper materijalizira običan raspon u dvodimenzionalni Variant array kroz AggregateGetCellValue, preslikavajući ćeliju koja je vratila kod greške u VarAsError da se politika grešaka i dalje može primijeniti po elementu, i rekurzivno prolazi kroz čvorove binarnih i unarnih operatora (SA_ADD, SA_DIV, SA_UNARMINUS i ostale) s ApplyArrayBinaryOp i ApplyArrayUnaryOp; sve ostalo pada na uobičajeni GetValueItem. Dvije zaštite stoje pred materijalizacijom: raspon veći od EffectiveFormulaArrayMemoryLimit vraća lxErrorResourceLimit, a raspon preko više listova ili obrnut raspon vraća #VALUE!. Kod resursnog ograničenja namjerno se ne tretira kao greška ćelije koju se može ignorirati čak ni pod opcijama 2/3/6/7, jer bi engine koji proguta vlastiti signal o nedostatku memorije zato što je korisnik tražio da se preskoči #N/A lagao. Sva tri AGGREGATE walkera, AggregateCollectRange za obitelj SUM, AggregateReduceVariance za STDEV, VAR i PRODUCT, i AggregateReduceWithK za MEDIAN i kvantilne oblike, prebačena su s FGetValue i GetValueItem na ta dva wrappera, i svaki je dobio test ugniježđenih ćelija kroz FIsSubtotalCell

Koju grešku AGGREGATE vraća kad ne ignorira greške?

Izvornu, od v2.382.3. Verzija 2.382.0 detektirala je ćelije s greškom ispravno, ali ih je sve sažimala u lxErrorValue, pa je AGGREGATE(9,4,A1:A3) nad ćelijom s #DIV/0! vraćao #VALUE!, dok Excel propagira prvu grešku na koju naiđe nepromijenjenu. Zamjenski pomoćnik AggregateErrorCode preslikava Variant u odgovarajući lxError* kod, bio Variant pravi varError ili jedan od sedam stringova grešaka, a AggregateValueIsError sada je samo test na rezultat različit od nule. Svaki walker bilježi prvi kod greške koji vidi i vraća taj kod, što također znači da ćelija čija formula nikad nije izračunata, i čija greška zato stiže kao povratni kod iz FGetValue, a ne kao cachirani Variant, propagira se na isti način kao cachirana. Dvije funkcije brojanja dobivaju poseban tretman unutar AggregateCollectRange, i taj tretman odgovara SUBTOTALU, a ne SUMU. Za unutarnju funkciju 0, COUNT, ćelija s greškom nikad se ne broji i nikad se ne propagira bez obzira na opcijski kod, jer COUNT broji samo brojeve. Za unutarnju funkciju 169, COUNTA, ćelija s greškom je neprazna vrijednost i broji se kao 1, osim ako opcijski kod ignorira greške, u kojem se slučaju preskače. Ta asimetrija je način na koji Excel tretira COUNT i COUNTA i izvan AGGREGATEa, i to je vrsta pojedinosti koju generičko pravilo "ako je greška, propagiraj" tiho pogreši

Što provjerava regresijska matrica s osam opcija

Gore opisani uzorak vježba se kao puna matrica u AggregateFunc_OptionMatrixCoversHiddenErrorsAndNestedAggregates: za svaki opcijski kod od 0 do 7 evaluira i SUM oblik i MEDIAN oblik nad A1:A4 i provjerava rezultat prema ručno izvedenom očekivanju. Kodovi 0, 1, 4 i 5 moraju propagirati #DIV/0! iz A3, jer nijedan od njih ne ignorira greške. Kod 2 daje SUM 30 i MEDIAN 15, iz 10 i 20 uz preskočeni ugniježđeni A4. Kod 3 daje 10 i 10. Kod 6 daje 60 i 20, jer se 30 u A4 sada broji. Kod 7 daje 40 i 20, što je slučaj koji je vraćao 20 prije popravka curenja. Širi acceptance prolaz zabilježen u registru poznatih problema pokriva svih devetnaest brojeva funkcija protiv svih osam kodova, sa svakim prethodnikom i cachiranim i necachiranim, za 304 scenarija na Win32 i 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)';   // subtotal grupe = 30
    Sheet.RowHidden[2] := True;

    Sheet.Cells[6, 1].Formula := '=AGGREGATE(9,1,A1:A4)'; // #DIV/0!  skriveno preskočeno, greška se propagira
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       skriveno + greška + ugniježđeno preskočeno
    Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60       preskočene samo greške
    Sheet.Cells[9, 1].Formula := '=AGGREGATE(9,7,A1:A4)'; // 40       bilo 20 prije v2.382.3
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

Gdje je granica još danas

Tri ograničenja vrijedi znati prije nego na ovome gradite. Prvo, predikat ugniježđene agregacije je tekstualan. TXLSXWorkbook.GetCalcIsSubtotalCell i njegov blizanac u klasičnom engineu vraćaju True kad formula ćelije počinje s SUBTOTAL(, AGGREGATE( ili _xlfn.AGGREGATE(, sa ili bez vodećeg znaka jednakosti, pa se formula poput =IF(C1,SUBTOTAL(9,B1:B9),0) ili =SUBTOTAL(9,B1:B9)*2 ne prepoznaje kao ugniježđena i kodovi 0 do 3 će je brojiti dvaput ondje gdje bi je Excel preskočio; generator koji emitira izračunate subtotale trebao bi poziv agregacije držati na čelu formule. Drugo, izolacija živi u tri AGGREGATE walkera. CalcSubtotalFunc i dalje hoda kroz GetValueItemRange, CollectRangeValues i SubtotalReduceVariance, koji pozivaju FGetValue izravno, pa SUBTOTAL(109, ...) čiji raspon sadrži necachiranu formulu prethodnika još može prenijeti svoj gate za skrivene retke u tog prethodnika. Puni Recalculate evaluira prethodnike prije zavisnika, pa se ide cachiranim putem i gate se nikad ne nasljeđuje; izloženost je ograničena na ad hoc evaluaciju kroz Calculate i na radne knjige učitane bez cachiranih vrijednosti, a ako se oslanjate na inkrementalni ponovni izračun preko grafa ovisnosti da veliki modeli ostanu responzivni, upravo to jamstvo redoslijeda drži ovo curenje uspavanim. Treće, oba gatea uvjetovana su s Assigned(FIsRowHidden) i Assigned(FIsSubtotalCell). Oba facadea radne knjige spajaju callbackove u svojim konstruktorima, ali kod koji ručno gradi TXLSCalculator sa samo dva izvorna argumenta dobiva naslijeđeno ponašanje uključi-sve za svaki opcijski kod, tiho. Kad zbroj izgleda pogrešno, a tekst formule izgleda ispravno, praćenje evaluacije korak po korak najbrži je način da vidite je li prethodnik evaluiran pod naslijeđenim gateom ili callback jednostavno nikad nije bio priključen

Engine za izračun opisan ovdje, dekoder opcija, izolirani wrapperi za dohvat i regresijska matrica koja ih prikiva isporučuju se kao izvorni kod uz HotXLS Delphi komponentu za proračunske tablice, koja čita, zapisuje i ponovno izračunava XLS, XLSX i ODS radne knjige u Delphiju i C++Builderu bez instalacije Excela