AGGREGATE opciómátrix és gate-szivárgás a HotXLS-ben

A HotXLS, a natív Excel-táblázatkomponens Delphihez és C++Builderhez, két összetartozó AGGREGATE-javítást adott ki 2026 szeptemberében. A 2.382.0 verzió kijavította az options argumentumot, hogy az 1/3/5/7 kódok figyelmen kívül hagyják a rejtett sorokat, a 2/3/6/7 kódok a hibákat, a 0-tól 3-ig terjedők pedig a beágyazott SUBTOTAL és AGGREGATE cellákat, pontosan ahogy a Microsoft dokumentálja. A 2.382.3 verzió ezután megállította, hogy ezek a kiválasztási flagek beszivárogjanak azoknak a celláknak a kiértékelésébe, amikre a függvény hivatkozik. Az első hiba abban a módban kínos, ahogy a táblázat-átírási hibák mindig: a bitpozíciók felcserélődtek, így minden képlet, ami nem nulla opciókódot használt, olyan politikát kapott, amit a szerzője nem kért. A második érdekesebb, mert az a forma, amibe bármely kiértékelőben belefutsz, ami átmeneti mezőt használ arra, hogy kontextust adjon át egy rekurzív bejárásnak. Egy külső aggregáció felhúz egy flaget, bejár egy tartományt, és kihúz egy cellát, aminek a képlete még nem számolódott ki. Az a képlet ugyanazon a kalkulátoron fut le, ugyanazt a felhúzott flaget látja, és csendben a rossz sorokat aggregálja, olyan számot adva, amit a képletszövegből senki nem tud megmagyarázni

Mit választanak ki valójában az AGGREGATE 0–7 opciói?

Az AGGREGATE options argumentuma egy hárombites mátrix, és a három bit független. A 0. bit (érték 1) a rejtett sorok figyelmen kívül hagyását jelenti, az 1. bit (érték 2) a hibaértékekét, a 2. bit (érték 4) pedig azt, hogy állj le a beágyazott SUBTOTAL és AGGREGATE cellák figyelmen kívül hagyásával, mert az alacsony kódoknál a kihagyásuk az alapértelmezés. Két dolgot könnyű ebből visszafelé érteni. A rejtett sorok bitje az alsó bit, nem a középső, így az AGGREGATE(9,1,...) a szűrt összesítés formája, az AGGREGATE(9,2,...) pedig a hibatűrő. A beágyazott aggregátumok politikája pedig fordított a másik kettőhöz képest: csak a 4–7 kódok kezelik úgy egy cella saját képletét, ami SUBTOTAL vagy AGGREGATE, mint közönséges értéket. Az ECMA-376 1. rész §18.17.7 ugyanezzel a rejtett-sor bevon-vagy-kizár felosztással definiálja a SUBTOTAL-t az 1–11 és 101–111 kódok között, az AGGREGATE pedig, amit az OOXML fájlokban a _xlfn. prefix alatt tárolnak, ezt a felosztást általánosítja az options argumentumba, így a Microsoft által az AGGREGATE függvényhez publikált táblázat az a szerződés, amit egy motornak teljesítenie kell, nem pedig egy kényelmi szolgáltatás

Opció	Rejtett sorok	Hibaértékek	Beágyazott SUBTOTAL / AGGREGATE
0	benne van	propagálódik	kihagyva
1	kihagyva	propagálódik	kihagyva
2	benne van	kihagyva	kihagyva
3	kihagyva	kihagyva	kihagyva
4	benne van	propagálódik	benne van
5	kihagyva	propagálódik	benne van
6	benne van	kihagyva	benne van
7	kihagyva	kihagyva	benne van

Miért voltak a HotXLS-ben visszafelé az AGGREGATE opciók?

Mert az eredeti TXLSCalculator.CalcAggregateFunc a táblázat egy parafrázisából íródott, nem magából a táblázatból. Az ignoreErrors := (optCode >= 4) and (optCode <= 7) értéket számolta, és a 2, 3, 6 és 7 kódokhoz húzta fel a rejtett-sor kaput, a beágyazott aggregátumok politikája pedig egyáltalán nem volt implementálva. A SUBTOTAL és AGGREGATE rejtett sorokról szóló korábbi cikk nyitott korlátként sorolta fel ezt a hiányt, és úgy írta le a régi leképezést, ahogy akkor kiment; a leírás pontos volt a kódról és hibás az Excelről, és sokáig senki nem vette észre, mert a két politika, amit a legtöbben kombinálnak, a rejtett plusz hibák, mindkét táblázatban a 3-as és a 7-es kódra esik. Csak egy egybites kód mutatta meg a cserét: az AGGREGATE(9,1,A1:A4) a szűretlen összeget adta vissza, az AGGREGATE(9,2,...) pedig kihagyta a rejtett sorokat, miközben továbbra is propagálta a #DIV/0!-t. A hiba az lxCalc.pas statikus átvizsgálásából került elő, HXLS-008 néven jegyezték be a projekt ismert hibák nyilvántartásába, nem egy ügyfélfájlból, ami elmond valamit arról, milyen ritkán jelennek meg az egybites kódok produkciós munkafüzetekben. A 2.382.0 verzió három halmaztagsági vizsgálatra írta át a dekódolást, és hozzáadott egy második kaput a beágyazott politikához, egy új TXLSIsSubtotalCell callbacken keresztül, amit a munkafüzet a TXLSIsRowHidden mellett biztosít

// TXLSCalculator.CalcAggregateFunc, a v2.382.3 formája
if (optCode < 0) or (optCode > 7) then
begin
  Result := lxErrorValue;            // az Excel visszautasítja a 0..7-en kívüli kódokat
  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
  // ... képezd le a function_num-ot a belső iftabra, járd be a ref1..refN-t ...
finally
  FIgnoreHiddenRows := prevIgnoreHidden;
  FIgnoreSubtotalCells := prevIgnoreSubtotal;
end;

Vedd észre, hogy a két flag feltétel nélkül kap értéket, nem csak akkor állítódik be, amikor az opció kéri őket. A 2.382.0 verzió még if ... then FIgnoreHiddenRows := True formát használt, ami azt jelentette, hogy egy 4-es kódú, SUBTOTAL(109, ...) belsejébe ágyazott AGGREGATE a külső rejtett-sor kaput örökölte ahelyett, hogy törölte volna. A dekódolt értéket belépéskor értékül adni, a korábbi értéket pedig a finally blokkban visszaállítani azt éri el, hogy minden AGGREGATE hívás a saját politikáját birtokolja a bejárása idejére, és semmi többet. A 2.382.0 verzió emellett becsületessé tette a tömbös formát is: amikor egy argumentum egy- vagy kétdimenziós Variant tömböt ad ki, a CalcAggregateFunc mostantól minden elemet bejár, és elemenként alkalmazza a hibat politikát, ahol a régi kód csak egy NaN double-t vizsgált, egyébként pedig az egész tömböt átadta az ExcelSum-nak

Miért szivárog be egy külső AGGREGATE az általa hivatkozott képletekbe?

Mert az FIgnoreHiddenRows és az FIgnoreSubtotalCells a kalkulátor mezői, a kalkulátort pedig minden képlet megosztja, amit egy újraszámítás során kiértékelnek. A kapuk szándékosan scratch-mezőként készültek, pont azért, hogy hat cellabejáró ciklus konzultálhasson velük anélkül, hogy minden szignatúrán át kellene fűzni egy paramétert, és ez a terv addig helyes, amíg minden, ami egy felhúzott kapu alatt fut, ahhoz az aggregációhoz tartozik, amelyik felhúzta. A feltevés egy konkrét ponton törik meg: az FGetValue-nél. Amikor egy bejáró elkér egy cellaértéket a munkafüzettől, és az a cella egy gyorsítótárazott eredmény nélküli képletet hordoz, a munkafüzet lefordítja a képletet, és azon nyomban kiértékeli, ugyanazon a TXLSCalculator-on, a külső kapukkal még felhúzva. A HotXLS.WorkbookApiTests.pas regressziós fixture négy cellával mutatja a hibát. Az A1-ben 10, az A2-ben 20 áll egy rejtett sorban, az A3-ban =1/0, az A4-ben =SUBTOTAL(9,A1:A2), aminek a helyes értéke 30. Értékeld ki most a =AGGREGATE(9,7,A1:A4) képletet: rejtett sorok kihagyása, hibák kihagyása, a beágyazott részösszeg értékként számít. Az Excel 10 + 30 = 40-et ad. A 2.382.3 előtti motor az A4 gyorsítótár nélküli állapotában felhúzta a rejtett-sor kaput, bejárta az A4-et, kiváltotta a kiértékelését, a 9-es kódhoz tartozó CalcSubtotalFunc pedig örökölte a felhúzott kaput, mert ő csak a 101–111 kódokhoz állítja be a flaget, és soha nem törli. Az A4 30 helyett 10-re értékelődött, a külső összesítés pedig 20-ként jött vissza. Egyik képlet sem említ rejtett sorokat azon az úton, ami a rossz számot előállította

A beágyazott aggregátum kapuja ugyanígy szivárgott, csak a másik irányba. A 0–3 kódoknál az FIgnoreSubtotalCells fel van húzva, a GetValueItemRange általános tartománybejárója pedig tiszteli, így egy olyan precedens, aminek a képlete =SUM(B1:B3), csendben eldobná a B2-t, ha a B2 épp egy SUBTOTAL-t tartalmazna. Ráadásul a CalcSubtotalFunc az FIgnoreSubtotalCells-t kilépéskor False-ra állítja vissza a korábbi érték visszaállítása helyett, így egy bejárás közben elért, gyorsítótár nélküli SUBTOTAL-precedens lefegyverezte a külső kaput minden utána következő cellára. A projekt ismert hibák nyilvántartása ezt HXLS-008 alatt a beágyazott kiválasztási állapot szivárgásaként jegyzi, és ez a helyes neve a hibacsztálynak: egy globális átmeneti flag, ami helyes annak a frame-nek, amelyik beállította, és hibás minden frame-nek, amelyik örökli

Hogyan szigeteli el a bejárást az AggregateGetCellValue és az AggregateGetItemValue

A 2.382.3 javítása határt tesz minden pont körül, ahol az AGGREGATE olyan értéket olvas, amit nem maga számolt ki. A TXLSCalculator.AggregateGetCellValue körbecsomagolja a nyers FGetValue hívást: elmenti mindkét flaget, törli őket, végrehajtja a lekérdezést, és egy finally blokkban visszaállítja őket. A külső aggregáció továbbra is a saját politikáját alkalmazza arra a cellára, amit épp lekérdezett, mert a rejtett-sor és beágyazott-cella vizsgálatok a bejáróban, a lekérdezés körül történnek, maga a precedens képlet viszont mindenféle politika nélkül fut le, ami az, amit az Excel tesz

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;        // a precedens képlet a saját politikáját birtokolja
  FIgnoreSubtotalCells := False;
  try
    Result := FGetValue(SheetIndex, Row, Col, Value, OutOfRange);
  finally
    FIgnoreHiddenRows := Hidden;
    FIgnoreSubtotalCells := Nested;
  end;
end;

Az AggregateGetItemValue ugyanezt teszi a nem tartományos argumentumokkal, és neki többet kell tennie a flagek törlésénél, mert egy olyan argumentum, mint az A1:A4/(B1:B4-20), egy kiszámolt tömb, aminek az elemalakját túl kell élnie. A wrapper egy sima tartományt kétdimenziós Variant tömbbé materializál az AggregateGetCellValue-n keresztül, egy hibakódot visszaadó cellát VarAsError-ra képezve, hogy a hibat politika elemenként továbbra is alkalmazható legyen, és rekurzívan bejárja a bináris és unáris operátorcsomópontokat (SA_ADD, SA_DIV, SA_UNARMINUS és a többit) az ApplyArrayBinaryOp-pal és az ApplyArrayUnaryOp-tal; minden más a szokásos GetValueItem-re esik át. Két őr ül a materializáció előtt: az EffectiveFormulaArrayMemoryLimit-nél nagyobb tartomány lxErrorResourceLimit-et ad vissza, egy több munkalapra átnyúló vagy fordított tartomány pedig #VALUE!-t. Egy resource-limit kódot szándékosan nem kezel a rendszer kihagyható cellahibaként a 2/3/6/7 opciók alatt sem, mivel egy motor, ami lenyelné a saját out-of-memory jelzését, mert a felhasználó a #N/A kihagyását kérte, hazudna. Mindhárom AGGREGATE-bejárót, az AggregateCollectRange-et a SUM családhoz, az AggregateReduceVariance-t a STDEV, VAR és PRODUCT függvényekhez, és az AggregateReduceWithK-t a MEDIAN és a kvantilis formákhoz, átállították az FGetValue-ról és a GetValueItem-ről a két wrapperre, és mindegyik megkapta a beágyazott-cella vizsgálatot az FIsSubtotalCell-en keresztül

Melyik hibát adja vissza az AGGREGATE, amikor nem hagyja ki a hibákat?

Az eredetit, a v2.382.3 óta. A 2.382.0 verzió helyesen észlelte a hibacellákat, de mindegyiket összeomlasztotta lxErrorValue-ba, így az AGGREGATE(9,4,A1:A3) egy #DIV/0! cella fölött #VALUE!-t adott vissza ott, ahol az Excel az első általa talált hibát változatlanul propagálja. A helyettesítő AggregateErrorCode helper egy Variantot a hozzá tartozó lxError* kódra képez le, akár valódi varError a Variant, akár a hét hiba string egyike, az AggregateValueIsError pedig mostantól egyszerűen egy nem nulla eredmény vizsgálata. Minden bejáró rögzíti az első általa látott hibakódot, és azt a kódot adja vissza, ami azt is jelenti, hogy egy olyan cella, aminek a képletét soha nem számolták ki, és aminek a hibája ezért az FGetValue-ból visszatérési kódként érkezik a cache-elt Variant helyett, ugyanúgy propagálódik, mint egy cache-elt. Két számláló függvény külön kezelést kap az AggregateCollectRange-en belül, és a kezelés a SUBTOTAL-hoz igazodik, nem a SUM-hoz. A 0-s belső függvénynél, a COUNT-nál egy hibacella soha nem számolódik és soha nem propagálódik, az opciókódtól függetlenül, mert a COUNT csak számokat számol. A 169-es belső függvénynél, a COUNTA-nál egy hibacella nem üres érték, és 1-ként számít, hacsak az opciókód ki nem hagyja a hibákat, amikor is kimarad. Ez az aszimmetria ugyanígy kezeli az Excel a COUNT-ot és a COUNTA-t az AGGREGATE-en kívül is, és ez az a fajta részlet, amit egy általános „ha hiba, akkor propagáld” szabály csendben elront

Mit igazol a nyolc opciós regressziós mátrix?

A fent leírt fixture-t teljes mátrixként hajtja végig az AggregateFunc_OptionMatrixCoversHiddenErrorsAndNestedAggregates: minden 0-tól 7-ig terjedő opciókódra kiértékeli mind a SUM, mind a MEDIAN formát az A1:A4 fölött, és az eredményt egy kézzel levezetett elváráshoz hasonlítja. A 0, 1, 4 és 5 kódoknak propagálniuk kell az A3-ból jövő #DIV/0!-t, mivel egyik sem hagyja ki a hibákat. A 2-es kód 30-at ad SUM-ra és 15-öt MEDIAN-ra, a 10-ből és 20-ból, a beágyazott A4 kihagyásával. A 3-as kód 10-et és 10-et ad. A 6-os kód 60-at és 20-at, mert az A4-ben lévő 30 mostantól számít. A 7-es kód 40-et és 20-at, ami az az eset, ami 20-at adott vissza a szivárgás javítása előtt. Az ismert hibák nyilvántartásában rögzített szélesebb elfogadási futás mind a tizenkilenc függvényszámot lefedi mind a nyolc kóddal, minden precedenssel gyorsítótárazva és anélkül is, 304 forgatókönyvre Win32-n és Win64-en

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)';   // csoport-részösszeg = 30
    Sheet.RowHidden[2] := True;

    Sheet.Cells[6, 1].Formula := '=AGGREGATE(9,1,A1:A4)'; // #DIV/0!  rejtett kihagyva, hiba propagálódik
    Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10       rejtett + hiba + beágyazott kihagyva
    Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60       csak a hibák kihagyva
    Sheet.Cells[9, 1].Formula := '=AGGREGATE(9,7,A1:A4)'; // 40       20 volt a v2.382.3 előtt
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

Hol van még a határvonal

Három korlátot érdemes tudni, mielőtt erre építesz. Először, a beágyazott aggregátum predikátuma szöveges. A TXLSXWorkbook.GetCalcIsSubtotalCell és a klasszikus motor ikertestvére akkor ad True-t, amikor egy cella képlete SUBTOTAL(-lel, AGGREGATE(-tel vagy _xlfn.AGGREGATE(-tel kezdődik, a vezető egyenlőségjellel vagy anélkül, így egy olyan képlet, mint a =IF(C1,SUBTOTAL(9,B1:B9),0) vagy a =SUBTOTAL(9,B1:B9)*2, nem ismerődik beágyazottként, és a 0–3 kódok duplán számolják, ahol az Excel kihagyná; egy generátor, ami kiszámolt részösszegeket bocsát ki, tartsa az aggregációs hívást a képlet élén. Másodszor, a szigetelés a három AGGREGATE-bejáróban él. A CalcSubtotalFunc továbbra is a GetValueItemRange-en, a CollectRangeValues-en és a SubtotalReduceVariance-on keresztül jár, amik közvetlenül az FGetValue-t hívják, így egy SUBTOTAL(109, ...), aminek a tartománya egy gyorsítótár nélküli precedens képletet tartalmaz, továbbra is átadhatja a rejtett-sor kapuját annak a precedensnek. Egy teljes Recalculate a precedenseket a függőképek előtt értékeli ki, így a cache-elt út érvényesül, és a kapu soha nem öröklődik; a kitettség a Calculate-en keresztüli ad hoc kiértékelésre és a gyorsítótárazott értékek nélkül betöltött munkafüzetekre korlátozódik, és ha az inkrementális újraszámításra a függőségi gráf fölött támaszkodsz, hogy a nagy modellek reszponzívak maradjanak, ugyanaz a sorrendezési garancia tartja dormant állapotban ezt a szivárgást. Harmadszor, mindkét kapu az Assigned(FIsRowHidden) és az Assigned(FIsSubtotalCell) feltételén múlik. Mindkét munkafüzet-homlokzat beköti a callbackeket a konstruktorában, az a kód viszont, ami egy TXLSCalculator-t kézzel épít fel csak a két eredeti argumentummal, csendben a régi, mindent bevonó viselkedést kapja minden opciókódra. Amikor egy összesítés hibásnak látszik, a képletszöveg pedig helyesnek, a kiértékelés lépésenkénti nyomkövetése a leggyorsabb módja annak, hogy meglásd, egy precedens örökölt kapu alatt értékelődött-e ki, vagy egy callbacket egyszerűen soha nem csatoltak be

Az itt leírt számítási motor, az opciódekódoló, a szigetelt fetch-wrapperek és az őket rögzítő regressziós mátrix mind forráskóddal együtt jelennek meg a HotXLS Delphi spreadsheet component részeként, ami XLS, XLSX és ODS munkafüzeteket olvas, ír és számol újra Delphiben és C++Builderben Excel-telepítés nélkül