AGGREGATE opcije i curenje gate-a u HotXLS-u za Delphi

HotXLS, nativna Excel komponenta za rad sa tabelama za Delphi i C++Builder, isporučio je u septembru 2026. dve povezane popravke za AGGREGATE. Verzija 2.382.0 ispravila je argument opcija tako da kodovi 1/3/5/7 ignorišu skrivene redove, 2/3/6/7 ignorišu greške, a 0 do 3 ignorišu ugnježđene SUBTOTAL i AGGREGATE ćelije, tačno kako Microsoft dokumentuje. Verzija 2.382.3 zatim je zaustavila te flag-ove izbora da cure u evaluaciju upravo onih ćelija koje funkcija referencira. Prvi defekt je neprijatan onako kako bug-ovi u prepisivanju tabela uvek jesu: pozicije bitova bile su zamenjene, pa je svaka formula koja je koristila nenulti kod opcija dobijala politiku koju njen autor nije tražio. Drugi je zanimljiviji, jer je to oblik na koji ćete naići u svakom evaluatoru koji koristi prolazno polje da prenese kontekst u rekurzivni hod. Spoljna agregacija naoruža flag, prođe kroz opseg, i povuče ćeliju čija formula još nije izračunata. Ta formula se izvršava na istom kalkulatoru, vidi isti naoružani flag, i tiho agregira pogrešne redove, dajući broj koji odstupa za iznos koji niko ne može da objasni samo iz teksta formule

Šta AGGREGATE opcije 0 do 7 zapravo biraju?

Argument opcija kod AGGREGATE je matrica od tri bita, i ta tri bita su nezavisna. Bit 0 (vrednost 1) znači ignorisanje skrivenih redova, bit 1 (vrednost 2) znači ignorisanje vrednosti greške, a bit 2 (vrednost 4) znači prestani da ignorišeš ugnježđene SUBTOTAL i AGGREGATE ćelije, jer je njihovo preskakanje podrazumevano za niske kodove. Dve stvari oko toga lako je pobrkati. Bit za skrivene redove je niski bit, ne srednji, pa je AGGREGATE(9,1,...) oblik za filtrirani zbir a AGGREGATE(9,2,...) onaj koji toleriše greške. A politika prema ugnježdenim agregacijama je obrnuta u odnosu na druge dve: samo kodovi 4 do 7 tretiraju ćeliju čija je formula SUBTOTAL ili AGGREGATE kao običnu vrednost. ECMA-376 Part 1 §18.17.7 definiše SUBTOTAL sa istom podelom na uključivanje ili isključivanje skrivenih redova kroz kodove 1-11 i 101-111, a AGGREGATE, koji se u OOXML fajlovima čuva pod prefiksom _xlfn., generalizuje tu podelu u argument opcija, pa je tabela koju Microsoft objavljuje za funkciju AGGREGATE ugovor koji engine mora da ispuni, a ne pogodnost

Opcija	Skriveni redovi	Vrednosti greške	Ugnježdeni SUBTOTAL / AGGREGATE
0	uključeni	propagirane	ignorisan
1	ignorisani	propagirane	ignorisan
2	uključeni	ignorisane	ignorisan
3	ignorisani	ignorisane	ignorisan
4	uključeni	propagirane	uključen
5	ignorisani	propagirane	uključen
6	uključeni	ignorisane	uključen
7	ignorisani	ignorisane	uključen

Zašto je HotXLS imao AGGREGATE opcije naopako?

Zato što je originalni TXLSCalculator.CalcAggregateFunc napisan iz parafraze tabele, a ne iz tabele. Računao je ignoreErrors := (optCode >= 4) and (optCode <= 7) i naoružavao gate za skrivene redove za kodove 2, 3, 6 i 7, dok politika prema ugnježdenim agregacijama nije bila implementirana uopšte. Raniji članak o skrivenim redovima u SUBTOTAL i AGGREGATE naveo je tu prazninu kao otvoreno ograničenje i opisao staro mapiranje onako kako je tada isporučeno; opis je bio tačan u pogledu koda a pogrešan u pogledu Excel-a, i niko to dugo nije primetio jer dve politike koje većina ljudi kombinuje, skriveni redovi plus greške, sležu na kodove 3 i 7 pod obema tabelama. Samo jednobitni kod razotkrio je zamenu: AGGREGATE(9,1,A1:A4) vraćao je nefiltrirani zbir, a AGGREGATE(9,2,...) preskakao je skrivene redove dok je i dalje propagirao #DIV/0!. Defekt je isplivao iz statičkog pregleda lxCalc.pas, zaveden kao HXLS-008 u registru poznatih problema projekta, a ne iz korisničkog fajla, što govori nešto o tome kako retko jednobitni kodovi izlaze u produkcijskim radnim knjigama. Verzija 2.382.0 prepisala je dekodiranje kao tri testa pripadnosti skupu i dodala drugi gate za ugnježdenu politiku, povezan kroz novi callback TXLSIsSubtotalCell 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 van 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
  // ... mapiraj function_num u unutrašnju iftab, prođi kroz ref1..refN ...
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
  FIgnoreSubtotalCells := prevIgnoreSubtotal;
end;

Primetite da se dva flag-a dodeljuju bezuslovno, a ne samo postavljaju kada to opcija traži. Verzija 2.382.0 još je koristila if ... then FIgnoreHiddenRows := True, što je značilo da AGGREGATE sa kodom 4 ugnježden unutar SUBTOTAL(109, ...) nasleđuje spoljni gate za skrivene redove umesto da ga obriše. Dodela dekodirane vrednosti pri ulasku i vraćanje prethodne vrednosti u finally bloku čini da svaki AGGREGATE poziv poseduje svoju politiku za vreme trajanja svog hoda i ništa više. Verzija 2.382.0 učinila je i array formu iskrenom: kada se argument evaluira u jednodimenzionalni ili dvodimenzionalni Variant niz, CalcAggregateFunc sada prolazi kroz svaki element i primenjuje politiku grešaka po elementu, dok je stari kod testirao samo NaN double a u suprotnom predavao ceo niz funkciji ExcelSum

Zašto spoljni AGGREGATE curi u formule koje referencira?

Zato što su FIgnoreHiddenRows i FIgnoreSubtotalCells polja na kalkulatoru, a kalkulator dele sve formule koje se evaluiraju tokom jednog preračunavanja. Gate-ovi su i zamišljeni kao scratch polja upravo da bi šest petlji hoda kroz ćelije moglo da ih konsultuje bez provlačenja parametra kroz svaki potpis, i taj dizajn je 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 tački: FGetValue. Kada walker zatraži od radne knjige vrednost ćelije a ta ćelija drži formulu bez keširanog rezultata, radna knjiga kompajlira formulu i evaluira je odmah, na istom TXLSCalculator-u, sa još postavljenim spoljnim gate-ovima. Regresioni fikstur u HotXLS.WorkbookApiTests.pas pokazuje kvar sa četiri ćelije. A1 drži 10, A2 drži 20 u skrivenom redu, A3 drži =1/0, a A4 drži =SUBTOTAL(9,A1:A2), čija je ispravna vrednost 30. Sada evaluirajte =AGGREGATE(9,7,A1:A4): ignorisanje skrivenih redova, ignorisanje grešaka, ugnježdeni subtotal se računa kao vrednost. Excel vraća 10 + 30 = 40. Sa nekeširanim A4, engine pre 2.382.3 naoružao je gate za skrivene redove, došao do A4, pokrenuo njegovu evaluaciju, a CalcSubtotalFunc za kod 9 nasledio je naoružani gate, jer on flag postavlja samo za kodove 101 do 111 i nikada ga ne briše. A4 se evaluirao u 10 umesto u 30, i spoljni zbir se vratio kao 20. Ni jedna od dve formule ne pominje skrivene redove na putu koji je proizveo pogrešan broj

Gate za ugnježdene agregacije curio je isto tako, u drugom smeru. Sa kodovima 0 do 3, FIgnoreSubtotalCells je naoružan, i generički walker opsega u GetValueItemRange ga poštuje, pa bi precedent čija je formula =SUM(B1:B3) tiho ispustio B2 ako bi B2 slučajno sadržao SUBTOTAL. Gore od toga, CalcSubtotalFunc resetuje FIgnoreSubtotalCells na False pri izlasku umesto da vrati prethodnu vrednost, pa je nekeširani SUBTOTAL precedent do kojeg se došlo na pola hoda razoružao spoljni gate za svaku ćeliju posle njega. Registar poznatih problema projekta vodi to pod HXLS-008 kao curenje ugnježdenog stanja izbora, i to je pravo ime za tu klasu bug-a: globalni prolazni flag koji je ispravan za frame koji ga je postavio a pogrešan za svaki frame koji ga nasledi

Kako AggregateGetCellValue i AggregateGetItemValue izoluju hod

Popravka u v2.382.3 stavlja granicu oko svake tačke gde AGGREGATE čita vrednost koju nije sam izračunao. TXLSCalculator.AggregateGetCellValue obavija sirovi poziv FGetValue: čuva oba flag-a, briše ih, obavlja čitanje, i vraća ih u finally bloku. Spoljna agregacija i dalje primenjuje svoju politiku na ćeliju koju je upravo pročitala, jer se testovi za skriveni red i ugnježdenu ćeliju izvršavaju u walker-u oko čitanja, ali sama formula precedenta se izvršava bez ikakve politike, što je upravo 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 precedenta poseduje svoju 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 opsezi, i mora da uradi više od brisanja flag-ova, jer je argument kao A1:A4/(B1:B4-20) izračunati niz čiji oblik elemenata mora da preživi. Wrapper materijalizuje običan opseg u dvodimenzionalni Variant niz kroz AggregateGetCellValue, preslikavajući ćeliju koja je vratila kod greške u VarAsError da bi politika grešaka i dalje mogla da se primeni po elementu, i rekurzivno prolazi kroz čvorove binarnih i unarnih operatora (SA_ADD, SA_DIV, SA_UNARMINUS i ostale) kroz ApplyArrayBinaryOp i ApplyArrayUnaryOp; sve ostalo pada na normalni GetValueItem. Dva čuvara stoje ispred materijalizacije: opseg veći od EffectiveFormulaArrayMemoryLimit vraća lxErrorResourceLimit, a opseg preko više listova ili obrnut opseg vraća #VALUE!. Kod ograničenja resursa namerno se ne tretira kao greška ćelije koju treba ignorisati čak ni pod opcijama 2/3/6/7, jer bi engine koji proguta sopstveni signal o nedostatku memorije zato što je korisnik tražio da preskoči #N/A lagao. Sva tri AGGREGATE walker-a, AggregateCollectRange za SUM familiju, AggregateReduceVariance za STDEV, VAR i PRODUCT, i AggregateReduceWithK za MEDIAN i kvantilne oblike, prebačena su sa FGetValue i GetValueItem na dva wrapper-a, i svaki je dobio test ugnježdene ćelije kroz FIsSubtotalCell

Koju grešku AGGREGATE vraća kada ne ignoriše greške?

Originalnu, od v2.382.3. Verzija 2.382.0 detektovala je ćelije sa greškom ispravno, ali je svaku od njih svela na lxErrorValue, pa je AGGREGATE(9,4,A1:A3) nad ćelijom #DIV/0! vraćao #VALUE!, dok Excel propagira prvu grešku na koju naiđe, nepromenjenu. Zamenski pomoćnik AggregateErrorCode preslikava Variant u odgovarajući lxError* kod, bilo da je Variant pravi varError bilo jedan od sedam stringova grešaka, a AggregateValueIsError je sada samo test za rezultat različit od nule. Svaki walker beleži prvi kod greške koji vidi i vraća taj kod, što takođe znači da ćelija čija formula nikada nije izračunata, i čija greška zato stiže kao povratni kod iz FGetValue umesto kao keširani Variant, propagira isto kao keširana. Dve funkcije brojanja dobijaju poseban tretman unutar AggregateCollectRange, i taj tretman odgovara SUBTOTAL-u a ne SUM-u. Za unutrašnju funkciju 0, COUNT, ćelija sa greškom se nikada ne broji i nikada ne propagira bez obzira na kod opcija, jer COUNT broji samo brojeve. Za unutrašnju funkciju 169, COUNTA, ćelija sa greškom je neprazna vrednost i broji se kao 1 osim ako kod opcija ignoriše greške, u kom slučaju se preskače. Ta asimetrija je način na koji Excel tretira COUNT i COUNTA i van AGGREGATE-a, i to je vrsta detalja koji generičko pravilo „ako je greška, propagiraj“ tiho pogreši

Šta verifikuje regresiona matrica od osam opcija

Fikstur opisan gore vežba se kao puna matrica u AggregateFunc_OptionMatrixCoversHiddenErrorsAndNestedAggregates: za svaki kod opcija od 0 do 7 evaluira i SUM formu i MEDIAN formu nad A1:A4 i proverava rezultat prema ručno izvedenom očekivanju. Kodovi 0, 1, 4 i 5 moraju da propagiraju #DIV/0! iz A3, jer nijedan od njih ne ignoriše greške. Kod 2 daje SUM 30 i MEDIAN 15, iz 10 i 20 sa preskočenim ugnježdenim A4. Kod 3 daje 10 i 10. Kod 6 daje 60 i 20, jer se 30 iz A4 sada računa. Kod 7 daje 40 i 20, što je slučaj koji je vraćao 20 pre popravke curenja. Širi prijemni test zabeležen u registru poznatih problema pokriva svih devetnaest brojeva funkcija prema svih osam kodova, sa svakim precedentom i keširanim i nekeširanim, što je 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!  skriveni preskočen, greška se propagira
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       skriveni + greška + ugnježdeni preskočeni
    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 pre v2.382.3
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

Gde je granica još uvek

Tri ograničenja vredi znati pre nego što gradite na ovome. Prvo, predikat za ugnježdenu agregaciju je tekstualan. TXLSXWorkbook.GetCalcIsSubtotalCell i njegov blizanac iz klasičnog engine-a odgovaraju True kada formula ćelije počinje sa SUBTOTAL(, AGGREGATE( ili _xlfn.AGGREGATE(, sa vodećim znakom jednakosti ili bez njega, pa formula kao =IF(C1,SUBTOTAL(9,B1:B9),0) ili =SUBTOTAL(9,B1:B9)*2 nije prepoznata kao ugnježdena i biće dvaput uračunata kod kodova 0 do 3 tamo gde bi je Excel preskočio; generator koji emituje izračunate subtotal-e treba da drži poziv agregacije na čelu formule. Drugo, izolacija živi u tri AGGREGATE walker-a. CalcSubtotalFunc i dalje hoda kroz GetValueItemRange, CollectRangeValues i SubtotalReduceVariance, koji pozivaju FGetValue direktno, pa SUBTOTAL(109, ...) čiji opseg sadrži nekeširanu formulu precedenta i dalje može da prenese svoj gate za skrivene redove u taj precedent. Puno Recalculate evaluira precedente pre zavisnih, pa se koristi keširana putanja i gate se nikada ne nasleđuje; izloženost je ograničena na ad hoc evaluaciju kroz Calculate i na radne knjige učitane bez keširanih vrednosti, a ako se oslanjate na inkrementalno preračunavanje preko grafa zavisnosti da veliki modeli ostanu responzivni, ista garancija redosleda je ono što drži to curenje uspavanim. Treće, oba gate-a su uslovljena sa Assigned(FIsRowHidden) i Assigned(FIsSubtotalCell). Obe fasade radne knjige povezuju te callback-ove u svojim konstruktorima, ali kod koji gradi TXLSCalculator ručno, sa samo dva originalna argumenta, dobija nasleđeno ponašanje uključi-sve za svaki kod opcija, tiho. Kada zbir izgleda pogrešno a tekst formule izgleda ispravno, praćenje evaluacije korak po korak je najbrži način da vidite da li je precedent evaluiran pod nasleđenim gate-om ili neki callback prosto nikada nije bio povezan

Engine za izračunavanje opisan ovde, dekoder opcija, izolovani wrapper-i za čitanje i regresiona matrica koja ih prikiva, isporučuju se kao izvorni kod uz HotXLS Delphi spreadsheet komponentu, koja čita, upisuje i preračunava XLS, XLSX i ODS radne knjige u Delphi-ju i C++Builder-u bez instalacije Excel-a