Matrika možnosti AGGREGATE in uhajanje pregrad v HotXLS

HotXLS, izvorna komponenta za preglednice Excel za Delphi in C++Builder, je septembra 2026 izdala dva povezana popravka AGGREGATE. Različica 2.382.0 je popravila argument z možnostmi, tako da kode 1/3/5/7 izpuščajo skrite vrstice, 2/3/6/7 izpuščajo napake in 0 do 3 izpuščajo gnezdene celice SUBTOTAL in AGGREGATE, natanko tako, kot dokumentira Microsoft. Različica 2.382.3 je nato preprečila, da bi te izbirne zastavice uhajale v vrednotenje prav tistih celic, na katere se funkcija sklicuje. Prva napaka je nerodna tako, kot so napake pri prepisovanju tabel vedno: bitna položaja sta bila zamenjana, zato je vsaka formula z neničelno kodo možnosti dobila politiko, ki si je njen avtor ni želel. Druga je bolj zanimiva, ker je to oblika, na katero boste naleteli v vsakem vrednotenju, ki s prehodnim poljem prenaša kontekst v rekurzivni prehod. Zunanja agregacija oboroži zastavico, prehodi obseg in potegne celico, katere formula še ni izračunana. Ta formula se izvede na istem kalkulatorju, vidi isto oboroženo zastavico in tiho agregira napačne vrstice, pri čemer da število, ki je za znesek, ki ga iz besedila formule samega ni mogoče pojasniti, mimo

Kaj možnosti AGGREGATE od 0 do 7 pravzaprav izbirajo?

Argument z možnostmi funkcije AGGREGATE je matrika treh bitov in ti trije biti so neodvisni. Bit 0 (vrednost 1) pomeni izpusti skrite vrstice, bit 1 (vrednost 2) pomeni izpusti vrednosti napak, bit 2 (vrednost 4) pa pomeni, da prenehaš izpuščati gnezdene celice SUBTOTAL in AGGREGATE, ker je njihovo preskakovanje privzeto pri nizkih kodah. Dve stvari pri tem je lahko obrniti narobe. Bit skritih vrstic je nizki bit in ne srednji, zato je AGGREGATE(9,1,...) oblika filtriranega seštevka, AGGREGATE(9,2,...) pa tista, ki prenaša napake. In politika do gnezdenih agregacij je glede na druga dva obrnjena: le kode od 4 do 7 celico, katere lastna formula je SUBTOTAL ali AGGREGATE, obravnavajo kot običajno vrednost. ECMA-376, del 1 §18.17.7, definira SUBTOTAL z isto delitvijo na vključevanje oziroma izpuščanje skritih vrstic po kodah 1-11 in 101-111, AGGREGATE, ki se v datotekah OOXML hrani s predpono _xlfn., pa to delitev posploši v argument z možnostmi, zato je tabela, ki jo Microsoft objavlja za funkcijo AGGREGATE, pogodba, ki jo mora pogon izpolniti, in ne udobje

Možnost	Skrite vrstice	Vrednosti napak	Gnezdeni SUBTOTAL / AGGREGATE
0	vključene	prenesene	izpuščen
1	izpuščene	prenesene	izpuščen
2	vključene	izpuščene	izpuščen
3	izpuščene	izpuščene	izpuščen
4	vključene	prenesene	vključen
5	izpuščene	prenesene	vključen
6	vključene	izpuščene	vključen
7	izpuščene	izpuščene	vključen

Zakaj je imel HotXLS možnosti AGGREGATE obrnjene?

Ker je bil prvotni TXLSCalculator.CalcAggregateFunc napisan po parafrazi tabele in ne po tabeli. Izračunal je ignoreErrors := (optCode >= 4) and (optCode <= 7) in oborožil pregrado za skrite vrstice pri kodah 2, 3, 6 in 7, medtem ko politika do gnezdenih agregacij sploh ni bila izvedena. Prejšnji članek o skritih vrsticah pri SUBTOTAL in AGGREGATE je to vrzel navedel kot odprto omejitev in opisal staro preslikavo, kakršna je takrat šla v izdajo; opis je bil natančen glede kode in napačen glede Excela, pa tega dolgo ni nihče opazil, ker dve politiki, ki jih ljudje najpogosteje kombinirate, skrite in napake, po obeh tabelah pristaneta na kodah 3 in 7. Zamenjavo je razkrila šele koda z enim samim bitom: AGGREGATE(9,1,A1:A4) je vrnil nefiltrirani seštevek, AGGREGATE(9,2,...) pa je izpustil skrite vrstice in še vedno prenesel #DIV/0!. Napaka je prišla na dan iz statičnega pregleda datoteke lxCalc.pas, zabeležena kot HXLS-008 v registru znanih težav projekta, in ne iz stranke, kar nekaj pove o tem, kako redko se kode z enim samim bitom pojavljajo v delovnih zvezkih v proizvodnji. Različica 2.382.0 je dekodiranje prepisala v tri preizkuse pripadnosti množici in dodala drugo pregrado za politiko do gnezdenih agregacij, povezano skozi nov povratni klic TXLSIsSubtotalCell, ki ga delovni zvezek ponuja ob TXLSIsRowHidden

// TXLSCalculator.CalcAggregateFunc, oblika iz v2.382.3
if (optCode < 0) or (optCode > 7) then
begin
  Result := lxErrorValue;            // Excel zavrne kode zunaj 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 v notranji iftab, prehodi ref1..refN ...
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
  FIgnoreSubtotalCells := prevIgnoreSubtotal;
end;

Opazite, da sta zastavici dodeljeni brezpogojno in ne le nastavljeni, kadar to možnost zahteva. Različica 2.382.0 je še uporabljala if ... then FIgnoreHiddenRows := True, kar je pomenilo, da je AGGREGATE s kodo 4, gnezden znotraj SUBTOTAL(109, ...), podedoval zunanjo pregrado za skrite vrstice, namesto da bi jo počistil. Dodelitev dekodirane vrednosti ob vstopu in obnovitev prejšnje vrednosti v bloku finally dosežeta, da vsak klic AGGREGATE svojo politiko poseduje za čas svojega prehoda in nič več. Različica 2.382.0 je tudi naredila nizovno obliko pošteno: kadar se argument ovrednoti v eno- ali dvodimenzionalni niz Variant, CalcAggregateFunc zdaj prehodi vsak element in politiko do napak uporabi na element, medtem ko je stara koda preizkusila le, ali gre za dvojno vrednost NaN, sicer pa celoten niz predala v ExcelSum

Zakaj zunanji AGGREGATE uhaja v formule, na katere se sklicuje?

Ker sta FIgnoreHiddenRows in FIgnoreSubtotalCells polji kalkulatorja, kalkulator pa si delijo vse formule, ovrednotene med enim preračunom. Pregradi sta bili zasnovani kot delovni polji prav zato, da bi lahko šest zank za prehod po celicah pogledalo vanju, ne da bi bilo treba parameter vleči skozi vsak podpis, in ta zasnova je trdna, dokler vse, kar se izvaja, ko je pregrada oborožena, pripada agregaciji, ki jo je oborožila. Predpostavka se zlomi na eni konkretni točki: FGetValue. Ko prehod po obsegih vpraša delovni zvezek za vrednost celice in ta celica nosi formulo brez predpomnjenega rezultata, delovni zvezek formulo prevede in jo ovrednoti pri priči, na istem TXLSCalculator, z zunanjima pregradama še vedno nastavljenima. Regresijska fiksna datoteka v HotXLS.WorkbookApiTests.pas pokaže odpoved s štirimi celicami. A1 nosi 10, A2 nosi 20 v skriti vrstici, A3 nosi =1/0, A4 pa nosi =SUBTOTAL(9,A1:A2), katerega pravilna vrednost je 30. Zdaj ovrednotite =AGGREGATE(9,7,A1:A4): izpusti skrite vrstice, izpusti napake, gnezdeni seštevek štej kot vrednost. Excel vrne 10 + 30 = 40. Ker A4 ni bil predpomnjen, je pogon pred različico 2.382.3 oborožil pregrado za skrite vrstice, prišel do A4, sprožil njegovo vrednotenje in CalcSubtotalFunc za kodo 9 je podedoval oboroženo pregrado, ker zastavico nastavi le pri kodah od 101 do 111 in je nikoli ne počisti. A4 se je ovrednotil v 10 namesto v 30, zunanji seštevek pa se je vrnil kot 20. Nobena od obeh formul na poti, ki je dala napačno število, ne omenja skritih vrstic

Pregrada za gnezdene agregacije je uhajala enako, le v drugo smer. Pri kodah od 0 do 3 je FIgnoreSubtotalCells oborožena in splošni prehod po obsegih v GetValueItemRange jo upošteva, zato bi predhodnica s formulo =SUM(B1:B3) tiho izpustila B2, če bi B2 slučajno vseboval SUBTOTAL. Še slabše, CalcSubtotalFunc ob izhodu ponastavi FIgnoreSubtotalCells na False, namesto da bi obnovil prejšnjo vrednost, zato je nepredpomnjen seštevek-predhodnica, dosežen sredi prehoda, razorožil zunanjo pregrado za vsako celico za sabo. Register znanih težav projekta to pod HXLS-008 zapiše kot uhajanje gnezdenega izbirnega stanja, in to je pravo ime za to vrsto napake: globalna prehodna zastavica, ki je pravilna za okvir, ki jo je nastavil, in napačna za vsak okvir, ki jo podeduje

Kako AggregateGetCellValue in AggregateGetItemValue izolirata prehod

Popravek v v2.382.3 okoli vsake točke, kjer AGGREGATE prebere vrednost, ki je ni izračunal sam, postavi mejo. TXLSCalculator.AggregateGetCellValue ovije surovi klic FGetValue: shrani obe zastavici, ju počisti, izvede branje in ju v bloku finally obnovi. Zunanja agregacija svojo politiko še vedno uporabi na celici, ki jo je pravkar prebrala, ker se preizkusa skritih vrstic in gnezdenih celic zgodita v prehodu okoli branja, sama formula predhodnice pa teče brez vsakršne politike, kar je tisto, kar počne 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 predhodnice ima svojo politiko
  FIgnoreSubtotalCells := False;
  try
    Result := FGetValue(SheetIndex, Row, Col, Value, OutOfRange);
  finally
    FIgnoreHiddenRows := Hidden;
    FIgnoreSubtotalCells := Nested;
  end;
end;

AggregateGetItemValue stori isto za argumente, ki niso obsegi, in mora narediti več kot počistiti zastavici, ker je argument, kot je A1:A4/(B1:B4-20), izračunani niz, katerega oblika elementov mora preživeti. Ovoj navaden obseg materializira v dvodimenzionalni niz Variant skozi AggregateGetCellValue, celico, ki je vrnila kodo napake, preslika v VarAsError, da je politiko do napak mogoče še vedno uporabiti na vsakem elementu, rekurzivno pa gre skozi vozlišča dvojiških in enojnih operatorjev (SA_ADD, SA_DIV, SA_UNARMINUS in ostala) z ApplyArrayBinaryOp in ApplyArrayUnaryOp; vse drugo pade skozi v običajni GetValueItem. Pred materializacijo stojita dve varovali: obseg, večji od EffectiveFormulaArrayMemoryLimit, vrne lxErrorResourceLimit, obseg čez več listov ali obrnjen obseg pa vrne #VALUE!. Koda omejitve virov se namenoma ne obravnava kot izpustljiva napaka celice niti pod možnostmi 2/3/6/7, saj bi pogon, ki bi pogoltnil svoj lastni signal o pomanjkanju pomnilnika zato, ker je uporabnik prosil, naj se #N/A preskoči, lagal. Vsi trije prehodi AGGREGATE, AggregateCollectRange za družino SUM, AggregateReduceVariance za STDEV, VAR in PRODUCT ter AggregateReduceWithK za MEDIAN in kvantilne oblike, so bili preklopljeni z FGetValue in GetValueItem na ovoja, vsak pa je dobil preizkus gnezdenih celic skozi FIsSubtotalCell

Katero napako vrne AGGREGATE, kadar napak ne izpušča?

Prvotno, od različice v2.382.3. Različica 2.382.0 je celice z napakami zaznala pravilno, a vsako od njih strla v lxErrorValue, zato je AGGREGATE(9,4,A1:A3) nad celico #DIV/0! vrnil #VALUE!, kjer Excel prvo naletelo napako prenese nespremenjeno. Nadomestni pomožnik AggregateErrorCode preslika vrednost Variant v ustrezno kodo lxError*, ne glede na to, ali je Variant pravi varError ali eden od sedmih nizov napak, AggregateValueIsError pa je zdaj le preizkus, ali je rezultat neničeln. Vsak prehod zabeleži prvo kodo napake, ki jo vidi, in vrne to kodo, kar pomeni tudi, da se celica, katere formula ni bila nikoli izračunana in katere napaka zato prispe kot koda vrnitve iz FGetValue in ne kot predpomnjeni Variant, prenese enako kot predpomnjeni. Dve funkciji štetja dobita v AggregateCollectRange posebno obravnavo in ta obravnava se ujema s SUBTOTAL in ne s SUM. Za notranjo funkcijo 0, COUNT, se celica z napako nikoli ne šteje in nikoli ne prenese, ne glede na kodo možnosti, ker COUNT šteje samo števila. Za notranjo funkcijo 169, COUNTA, je celica z napako neprazna vrednost in se šteje kot 1, razen kadar koda možnosti napake izpušča, in takrat se preskoči. Ta asimetrija je način, kako Excel obravnava COUNT in COUNTA tudi zunaj funkcije AGGREGATE, in je vrsta podrobnosti, ki jo splošno pravilo »če napaka, potem prenesi« tiho zgreši

Kaj preverja regresijska matrika osmih možnosti

Zgoraj opisana fiksna datoteka se preizkusi kot polna matrika v AggregateFunc_OptionMatrixCoversHiddenErrorsAndNestedAggregates: za vsako kodo možnosti od 0 do 7 ovrednoti tako obliko SUM kot obliko MEDIAN nad A1:A4 in rezultat preizkusi proti ročno izpeljanemu pričakovanju. Kode 0, 1, 4 in 5 morajo prenesti #DIV/0! iz A3, ker nobena od njih ne izpušča napak. Koda 2 da SUM 30 in MEDIAN 15, iz 10 in 20 z izpuščenim gnezdenim A4. Koda 3 da 10 in 10. Koda 6 da 60 in 20, ker se 30 v A4 zdaj šteje. Koda 7 da 40 in 20, kar je primer, ki je pred popravkom uhajanja vračal 20. Širši sprejemni zagon, zabeležen v registru znanih težav, pokriva vseh devetnajst številk funkcij proti vsem osmim kodam, z vsako predhodnico tako predpomnjeno kot nepredpomnjeno, kar da 304 scenarijev na Win32 in 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)';   // seštevek skupine = 30
    Sheet.RowHidden[2] := True;

    Sheet.Cells[6, 1].Formula := '=AGGREGATE(9,1,A1:A4)'; // #DIV/0!  skrite izpuščene, napaka se prenese
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       skrite + napaka + gnezdeno izpuščeno
    Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60       izpuščene le napake
    Sheet.Cells[9, 1].Formula := '=AGGREGATE(9,7,A1:A4)'; // 40       pred v2.382.3 je bilo 20
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

Kje je meja še danes

Tri omejitve velja poznati, preden na tem gradite. Prvič, predikat gnezdenih agregacij je besedilen. TXLSXWorkbook.GetCalcIsSubtotalCell in njegov dvojnik v klasičnem pogonu odgovorita True, kadar se formula celice začne s SUBTOTAL(, AGGREGATE( ali _xlfn.AGGREGATE(, z vodilnim enačajem ali brez njega, zato formula, kot je =IF(C1,SUBTOTAL(9,B1:B9),0) ali =SUBTOTAL(9,B1:B9)*2, ni prepoznana kot gnezdene in jo bodo kode od 0 do 3 štele dvakrat, kjer bi jo Excel preskočil; generator, ki izdaja izračunane seštevke, bi moral klic agregacije obdržati na začetku formule. Drugič, izolacija živi v treh prehodih AGGREGATE. CalcSubtotalFunc še vedno hodi skozi GetValueItemRange, CollectRangeValues in SubtotalReduceVariance, ki kličejo FGetValue neposredno, zato lahko SUBTOTAL(109, ...), katerega obseg vsebuje nepredpomnjeno formulo predhodnice, svojo pregrado za skrite vrstice še vedno prenese v to predhodnico. Poln Recalculate predhodnice ovrednoti pred odvisnimi, zato se uporabi predpomnjena pot in pregrada ni nikoli podedovana; izpostavljenost je omejena na sprotno vrednotenje skozi Calculate in na delovne zvezke, naložene brez predpomnjenih vrednosti, in če se zanašate na inkrementalni preračun po grafu odvisnosti, da veliki modeli ostanejo odzivni, je prav isto jamstvo o vrstnem redu tisto, ki to uhajanje drži v mirovanju. Tretjič, obe pregradi sta pogojeni z Assigned(FIsRowHidden) in Assigned(FIsSubtotalCell). Obe fasadi delovnega zvezka povratna klica povežeta v svojih konstruktorjih, koda, ki pa TXLSCalculator zgradi ročno samo z dvema izvirnima argumentoma, tiho dobi staro vedenje, ki vključuje vse, pri vsaki kodi možnosti. Ko je seštevek videti napačen in je besedilo formule videti pravilno, je sledenje vrednotenju korak za korakom najhitrejša pot, da vidite, ali je bila predhodnica ovrednotena pod podedovano pregrado ali pa povratni klic preprosto ni bil nikoli pripet

Pogon za izračune, opisan tukaj, dekoder možnosti, izolirana ovoja za branje in regresijska matrika, ki jih pripne, se vsi dobavljajo v izvorni kodi z komponento za preglednice HotXLS za Delphi, ki bere, piše in preračunava delovne zvezke XLS, XLSX in ODS v Delphiju in C++Builderju brez nameščenega Excela