HotXLS, нативният Excel spreadsheet компонент за Delphi и C++Builder, достави две свързани AGGREGATE поправки през септември 2026. Версия 2.382.0 оправи аргумента options така, че кодовете 1/3/5/7 игнорират скрити редове, 2/3/6/7 игнорират грешки, а 0 до 3 игнорират вложени SUBTOTAL и AGGREGATE клетки, точно както го документира Microsoft. Версия 2.382.3 после спря тези selection флагове да изтичат в оценката на точно клетките, които функцията реферира. Първият дефект е неловък по начина, по който бъговете от преписване на таблица винаги са: битовите позиции бяха разменени, така че всяка формула, ползвала ненулев options код, получаваше политика, която авторът ѝ не беше искал. Вторият е по-интересен, защото е форма, която ще срещнете във всеки evaluator, ползващ transient поле, за да подава контекст в рекурсивно обхождане. Външна агрегация вдига флаг, обхожда диапазон и дърпа клетка, чиято формула още не е изчислена. Тази формула върви върху същия calculator, вижда същия вдигнат флаг и тихо агрегира грешните редове, произвеждайки число, чиято грешка не може да се обясни от самия текст на формулата
Какво всъщност избират опциите 0 до 7 на AGGREGATE?
Аргументът options на AGGREGATE е трибитова матрица, а трите бита са независими. Бит 0 (стойност 1) кара скритите редове да се игнорират, бит 1 (стойност 2) — грешните стойности да се игнорират, а бит 2 (стойност 4) — да се спре игнорирането на вложени SUBTOTAL и AGGREGATE клетки, защото тяхното прескачане е поведението по подразбиране за ниските кодове. Две неща тук лесно се объркват. Битът за скрити редове е долният бит, не средният, така че AGGREGATE(9,1,...) е вариантът с филтрирания сбор, а AGGREGATE(9,2,...) е вариантът, поносим към грешки. И политиката за вложени агрегати е обърната спрямо другите две: само кодовете 4 до 7 третират клетка, чиято собствена формула е SUBTOTAL или AGGREGATE, като обикновена стойност. ECMA-376 Part 1 §18.17.7 дефинира SUBTOTAL със същото разделяне вклити-или-изключи скрити редове между кодовете 1-11 и 101-111, а AGGREGATE, съхраняван в OOXML файлове под префикса _xlfn., обобщава това разделяне в аргумента options, така че таблицата, която Microsoft публикува за функцията AGGREGATE, е договорът, който една машина трябва да изпълни, а не удобство
| Опция | Скрити редове | Грешни стойности | Вложени SUBTOTAL / AGGREGATE |
|---|---|---|---|
| 0 | включени | предавани | игнорирани |
| 1 | игнорирани | предавани | игнорирани |
| 2 | включени | игнорирани | игнорирани |
| 3 | игнорирани | игнорирани | игнорирани |
| 4 | включени | предавани | включени |
| 5 | игнорирани | предавани | включени |
| 6 | включени | игнорирани | включени |
| 7 | игнорирани | игнорирани | включени |
Защо HotXLS беше разбъркал опциите на AGGREGATE?
Защото оригиналният TXLSCalculator.CalcAggregateFunc беше написан от парафраза на таблицата, а не от таблицата. Той изчисляваше ignoreErrors := (optCode >= 4) and (optCode <= 7) и вдигаше hidden-row gate за кодове 2, 3, 6 и 7, докато политиката за вложени агрегати изобщо не беше имплементирана. По-ранната статия за SUBTOTAL и AGGREGATE скритите редове посочваше тази дупка като отворено ограничение и описваше старото съответствие такова, каквото тогава се доставяше; описанието беше вярно за кода и грешно за Excel, и никой не забеляза дълго време, защото двете политики, които повечето хора съчетават — скрити плюс грешки — падат на кодове 3 и 7 и под двете таблици. Само еднобитов код изплуваше размяната: AGGREGATE(9,1,A1:A4) връщаше нефилтрирания сбор, а AGGREGATE(9,2,...) прескачаше скрити редове, докато все още предаваше #DIV/0!. Дефектът изплува от статичен преглед на lxCalc.pas, регистриран като HXLS-008 в known-issues регистъра на проекта, не от клиентски файл, което нещо говори за това колко рядко еднобитовите кодове се появяват в production работни книги. Версия 2.382.0 пренаписа декодирането като три теста за членство в множество и добави втори gate за вложената политика, свързан чрез нов TXLSIsSubtotalCell callback, който работната книга предоставя до TXLSIsRowHidden
// TXLSCalculator.CalcAggregateFunc, видът от v2.382.3
if (optCode < 0) or (optCode > 7) then
begin
Result := lxErrorValue; // Excel отхвърля кодове извън 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
// ... намести function_num към вътрешния iftab, обходи ref1..refN ...
finally
FIgnoreHiddenRows := prevIgnoreHidden;
FIgnoreSubtotalCells := prevIgnoreSubtotal;
end;Забележете, че двата флага се присвояват безусловно, а не се задават само когато опцията го иска. Версията в v2.382.0 все още ползваше if ... then FIgnoreHiddenRows := True, което означаваше, че AGGREGATE с код 4, вложен в SUBTOTAL(109, ...), наследява външния hidden-row gate вместо да го изчисти. Присвояването на декодирания вход и възстановяването на предишната стойност в finally блока кара всяко извикване на AGGREGATE да притежава своята политика само за времето на своето обхождане и нищо повече. Версия 2.382.0 направи и array формата честна: когато аргумент се оцени като едно- или двумерен Variant масив, CalcAggregateFunc вече обхожда всеки елемент и прилага политиката за грешки на елемент, докато старият код тестваше само за NaN double и иначе подаваше целия масив на ExcelSum
Защо външен AGGREGATE изтича във формулите, които реферира?
Защото FIgnoreHiddenRows и FIgnoreSubtotalCells са полета на calculator-а, а calculator-ът е споделен от всяка формула, оценявана по време на едно преизчисляване. Двата gate-а бяха проектирани като scratch полета точно за да могат шестте cell-walk цикъла да ги питат, без да провлачват параметър през всяка сигнатура, и този дизайн е здрав, стига всичко, което върви, докато gate е вдигнат, да принадлежи на агрегацията, която го е вдигнала. Допущението се чупи в една конкретна точка: FGetValue. Когато walker попита работната книга за стойност на клетка и тази клетка държи формула без кеширан резултат, книгата компилира формулата и я оценява на място, върху същия TXLSCalculator, с външните gate-ове все още вдигнати. Regression fixture-ът в HotXLS.WorkbookApiTests.pas показва провала с четири клетки. A1 държи 10, A2 държи 20 на скрит ред, A3 държи =1/0, а A4 държи =SUBTOTAL(9,A1:A2), чиято правилна стойност е 30. Сега оценете =AGGREGATE(9,7,A1:A4): игнорирай скрити редове, игнорирай грешки, брои вложения subtotal като стойност. Excel връща 10 + 30 = 40. С некеширано A4 engine-ът преди 2.382.3 вдигаше hidden-row gate, стигаше до A4, задействаше неговата оценка и CalcSubtotalFunc за код 9 наследяваше вдигнатия gate, защото той само вдига флага за кодове 101 до 111 и никога не го сваля. A4 се оцени на 10 вместо на 30, а външният сбор се върна като 20. Нищо в двете формули не споменава скрити редове по пътя, който произведе грешното число
Gate-ът за вложени агрегати изтичаше по същия начин и в другата посока. С кодове 0 до 3 FIgnoreSubtotalCells е вдигнат, а generic range walker-ът в GetValueItemRange го уважава, така че предшественик с формула =SUM(B1:B3) би пуснал тихо B2, ако B2 случайно съдържаше SUBTOTAL. По-зле: CalcSubtotalFunc ресетва FIgnoreSubtotalCells на False на изход, вместо да възстанови предишната стойност, така че некеширан SUBTOTAL предшественик, стигнат по средата на обхождане, разоръжаваше външния gate за всяка клетка след него. Known-issues регистърът на проекта води това под HXLS-008 като nested selection state leakage, и това е правилното име за класа бъг: глобален transient флаг, верен за рамката, която го е вдигнала, и грешен за всяка рамка, която го наследи
Изолация на обхождането чрез AggregateGetCellValue
Поправката в v2.382.3 слага граница около всяка точка, в която AGGREGATE прочита стойност, която не е изчислил сам. TXLSCalculator.AggregateGetCellValue обвива суровото извикване на FGetValue: запазва двата флага, изчиства ги, изпълнява извличането и ги възстановява в finally блок. Външната агрегация продължава да прилага собствената си политика върху клетката, която току-що е изтеглила, защото тестовете за скрит ред и вложена клетка стават в walker-а около извличането, но самата предшественическа формула върви без никаква политика, което е точно това, което прави 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; // формула-предшественик притежава собствената си политика
FIgnoreSubtotalCells := False;
try
Result := FGetValue(SheetIndex, Row, Col, Value, OutOfRange);
finally
FIgnoreHiddenRows := Hidden;
FIgnoreSubtotalCells := Nested;
end;
end;AggregateGetItemValue прави същото за аргументи, които не са диапазони, и му се налага да направи повече от изчистване на флагове, защото аргумент като A1:A4/(B1:B4-20) е изчислен масив, чиято форма на елементите трябва да оцелее. Обвивката превръща обикновен диапазон в двумерен Variant масив чрез AggregateGetCellValue, мапвайки клетка, върнала код на грешка, към VarAsError, така че политиката за грешки пак може да се приложи на елемент, и рекурсира през бинарните и унарните операторни възли (SA_ADD, SA_DIV, SA_UNARMINUS и останалите) с ApplyArrayBinaryOp и ApplyArrayUnaryOp; всичко останало се пропада към нормалното GetValueItem. Два пазителя стоят пред материализацията: диапазон, по-голям от EffectiveFormulaArrayMemoryLimit, връща lxErrorResourceLimit, а много-листов или обърнат диапазон връща #VALUE!. Код за изчерпан ресурс умишлено не се третира като пренебрежима клетъчна грешка дори под опциите 2/3/6/7, тъй като engine, който е погълнал собствения си out-of-memory сигнал, защото потребителят е поискал да прескача #N/A, би лъжял. И трите AGGREGATE walker-а — AggregateCollectRange за SUM семейството, AggregateReduceVariance за STDEV, VAR и PRODUCT, и AggregateReduceWithK за MEDIAN и квантилните форми — бяха превключени от FGetValue и GetValueItem към двете обвивки, и всеки получи теста за вложена клетка чрез FIsSubtotalCell
Коя грешка връща AGGREGATE, когато не игнорира грешки?
Оригиналната — от v2.382.3 нататък. Версия 2.382.0 откриваше грешните клетки коректно, но свиваше всяка една от тях до lxErrorValue, така че AGGREGATE(9,4,A1:A3) върху клетка с #DIV/0! връщаше #VALUE!, докато Excel предава първата срещната грешка непокътната. Заместващият helper AggregateErrorCode мапва Variant към съответния lxError* код — дали Variant-ът е истински varError или един от седемте error низа — а AggregateValueIsError вече е просто тест за ненулев резултат. Всеки walker записва първия код на грешка, който види, и връща него, което значи и че клетка, чиято формула никога не е била изчислявана и чиято грешка затова пристига като return код от FGetValue, а не като кеширан Variant, се предава по същия начин като кешираната. Две функции за броене получават специално лечение вътре в AggregateCollectRange, и то се съобразява със SUBTOTAL, а не със SUM. За вътрешна функция 0, COUNT, грешна клетка никога не се брои и никога не се предава, без значение от options кода, защото COUNT брои само числа. За вътрешна функция 169, COUNTA, грешната клетка е непразна стойност и брои като 1, освен ако options кодът не игнорира грешки, в който случай се прескача. Тази асиметрия е как Excel третира COUNT и COUNTA и извън AGGREGATE, и е точно видът подробност, който generic правило „ако грешка — предай нататък“ тихо обърква
Какво проверява регресионната матрица с осем опции
Описаният по-горе fixture се изпълнява като пълна матрица в AggregateFunc_OptionMatrixCoversHiddenErrorsAndNestedAggregates: за всеки options код от 0 до 7 той оценява и SUM формата, и MEDIAN формата върху A1:A4 и сверява резултата с ръчно изведено очакване. Кодовете 0, 1, 4 и 5 трябва да предадат #DIV/0! от A3, тъй като нито един не игнорира грешки. Код 2 дава SUM 30 и MEDIAN 15 — от 10 и 20, с пропуснатото вложено A4. Код 3 дава 10 и 10. Код 6 дава 60 и 20, защото 30-те в A4 вече се броят. Код 7 дава 40 и 20 — случаят, който връщаше 20 преди поправката на изтичането. По-широкият acceptance прогон, записан в known-issues регистъра, покрива всичките деветнайсет номера на функции срещу всичките осем кода, с всеки предшественик и кеширан, и некеширан, за 304 сценария на Win32 и 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 = 30
Sheet.RowHidden[2] := True;
Sheet.Cells[6, 1].Formula := '=AGGREGATE(9,1,A1:A4)'; // #DIV/0! скритите прескачани, грешката се предава
Sheet.Cells[7, 1].Formula := '=AGGREGATE(9,3,A1:A4)'; // 10 скрити + грешка + вложени прескачани
Sheet.Cells[8, 1].Formula := '=AGGREGATE(9,6,A1:A4)'; // 60 само грешките прескачани
Sheet.Cells[9, 1].Formula := '=AGGREGATE(9,7,A1:A4)'; // 40 беше 20 преди v2.382.3
Book.Recalculate;
Book.SaveAs('aggregate-options.xlsx');
finally
Book.Free;
end;
end;Къде границата все още стои
Три ограничения си струват да знаете, преди да градите върху това. Първо, предикатът за вложени агрегати е текстов. TXLSXWorkbook.GetCalcIsSubtotalCell и неговият близнак за classic engine отговарят True, когато формулата на клетка започва с SUBTOTAL(, AGGREGATE( или _xlfn.AGGREGATE(, със или без водещ знак за равенство, така че формула като =IF(C1,SUBTOTAL(9,B1:B9),0) или =SUBTOTAL(9,B1:B9)*2 не се разпознава като вложена и ще се преброи два пъти от кодове 0 до 3, докато Excel я прескача; генератор, който излъчва изчислени subtotals, трябва да държи агрегиращото повикване в главата на формулата. Второ, изолацията живее в трите AGGREGATE walker-а. CalcSubtotalFunc все още обхожда през GetValueItemRange, CollectRangeValues и SubtotalReduceVariance, които викат FGetValue директно, така че SUBTOTAL(109, ...), чийто диапазон съдържа некеширана предшественическа формула, все още може да подаде своя hidden-row gate в нея. Пълен Recalculate оценява предшествениците преди зависимите, така че се минава по кеширания път и gate-ът никога не се наследява; излагането е ограничено до ad hoc оценяване чрез Calculate и до работни книги, заредени без кеширани стойности, а ако разчитате на инкрементално преизчисляване върху dependency graph-а, за да държите големи модели отзивчиви, същата гаранция за подредба е това, което държи това изтичане в сън. Трето, двата gate-а са обусловени от Assigned(FIsRowHidden) и Assigned(FIsSubtotalCell). И двете workbook facade-и свързват callback-овете в своите конструктори, но код, който гради TXLSCalculator на ръка само с двата оригинални аргумента, получава тихо legacy поведението да включва всичко за всеки options код. Когато един сбор изглежда грешен, а текстът на формулата изглежда верен, проследяването на оценката стъпка по стъпка е най-бързият начин да видите дали предшественик е бил оценен под наследен gate или дали callback просто никога не е бил закачен
Изчислителният engine, описан тук, декодерът на опции, изолиращите fetch обвивки и регресионната матрица, която ги закова, се доставят всички като source с HotXLS Delphi spreadsheet компонент, който чете, пише и преизчислява XLS, XLSX и ODS работни книги в Delphi и C++Builder без инсталиран Excel