HotXLS, нативний компонент електронних таблиць Excel для Delphi і C++Builder, випустив у вересні 2026 два пов'язані виправлення AGGREGATE. Версія 2.382.0 виправила аргумент опцій так, щоб коди 1/3/5/7 ігнорували приховані рядки, 2/3/6/7 ігнорували помилки, а 0–3 ігнорували вкладені клітинки SUBTOTAL і AGGREGATE — точно так, як це документує Microsoft. Версія 2.382.3 потім зупинила витікання цих прапорців вибору в обчислення тих самих клітинок, на які посилається функція. Перший дефект ганебний так, як завжди ганебні баги переписування таблиць: позиції бітів були переплутані, тож кожна формула з ненульовим кодом опцій отримувала політику, якої її автор не просив. Другий цікавіший, бо це форма, яку ви зустрінете в будь-якому обчислювачі, що використовує тимчасове поле для передачі контексту в рекурсивний обхід. Зовнішня агрегація озброює прапорець, обходить діапазон і натрапляє на клітинку, чию формулу ще не обчислено. Та формула виконується на тому самому калькуляторі, бачить той самий озброєний прапорець і тихо агрегує не ті рядки, видаючи число, яке ніхто не може пояснити з самого лише тексту формули
Що насправді вибирають опції AGGREGATE від 0 до 7?
Аргумент опцій 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., узагальнює цей поділ в аргументі опцій, тож таблиця, яку Microsoft публікує для функції AGGREGATE, — це контракт, який рушій мусить виконати, а не зручність
| Опція | Приховані рядки | Значення помилок | Вкладені SUBTOTAL / AGGREGATE |
|---|---|---|---|
| 0 | включено | поширюється | ігноруються |
| 1 | ігноруються | поширюється | ігноруються |
| 2 | включено | ігноруються | ігноруються |
| 3 | ігноруються | ігноруються | ігноруються |
| 4 | включено | поширюється | включено |
| 5 | ігноруються | поширюється | включено |
| 6 | включено | ігноруються | включено |
| 7 | ігноруються | ігноруються | включено |
Чому в HotXLS опції AGGREGATE були перевернуті?
Бо початковий TXLSCalculator.CalcAggregateFunc написано з переказу таблиці, а не з таблиці. Він обчислював ignoreErrors := (optCode >= 4) and (optCode <= 7) і озброював прапорець прихованих рядків для кодів 2, 3, 6 і 7, а політику щодо вкладених агрегатів не було реалізовано взагалі. Раніша стаття про приховані рядки в SUBTOTAL і AGGREGATE перелічувала цю прогалину як відкрите обмеження й описувала старе зіставлення таким, як воно тоді йшло в реліз; опис був точний щодо коду й неправильний щодо Excel, і ніхто довго цього не помічав, бо дві політики, які більшість людей поєднує — приховані плюс помилки, — за обома таблицями припадають на коди 3 і 7. Лише однобітний код викрив підміну: AGGREGATE(9,1,A1:A4) повертав невідфільтровану суму, а AGGREGATE(9,2,...) пропускав приховані рядки, але все одно поширював #DIV/0!. Дефект виринув зі статичного огляду lxCalc.pas, зареєстрований як HXLS-008 у реєстрі відомих проблем проєкту, а не з клієнтського файла, — і це щось каже про те, як рідко однобітні коди трапляються в робочих книгах продакшену. Версія 2.382.0 переписала декодування як три перевірки належності до множини й додала другий прапорець для політики щодо вкладених, під'єднаний через новий колбек TXLSIsSubtotalCell, який робоча книга надає поряд із 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;Зверніть увагу, що обидва прапорці присвоюються безумовно, а не лише виставляються, коли опція їх просить. Версія 2.382.0 усе ще використовувала if ... then FIgnoreHiddenRows := True, а це означало, що AGGREGATE з кодом 4, вкладений у SUBTOTAL(109, ...), успадковував зовнішній прапорець прихованих рядків замість того, щоб його очистити. Присвоєння декодованого значення на вході й відновлення попереднього значення у блоці finally робить так, що кожен виклик AGGREGATE володіє своєю політикою на час свого обходу і не більше. Версія 2.382.0 також зробила чесною форму масиву: коли аргумент обчислюється в одномірний або двомірний масив Variant, CalcAggregateFunc тепер обходить кожен елемент і застосовує політику щодо помилок поелементно, тоді як старий код перевіряв лише на NaN-подвійне й інакше віддавав весь масив у ExcelSum
Чому зовнішній AGGREGATE протікає у формули, на які посилається?
Бо FIgnoreHiddenRows і FIgnoreSubtotalCells — це поля калькулятора, а калькулятор спільний для кожної формули, обчисленої під час одного перерахунку. Прапорці задумані як тимчасові поля саме для того, щоб шість циклів обходу клітинок могли звертатися до них без протягування параметра через кожну сигнатуру, і цей задум слушний, доки все, що виконується з озброєним прапорцем, належить тій агрегації, яка його озброїла. Припущення ламається в одній конкретній точці: FGetValue. Коли обхід просить у робочої книги значення клітинки, а та клітинка містить формулу без кешованого результату, робоча книга компілює формулу й обчислює її на місці, на тому самому TXLSCalculator, із усе ще встановленими зовнішніми прапорцями. Регресійна фікстура в HotXLS.WorkbookApiTests.pas показує цю відмову на чотирьох клітинках. A1 містить 10, A2 містить 20 на прихованому рядку, A3 містить =1/0, а A4 містить =SUBTOTAL(9,A1:A2), чиє правильне значення — 30. Тепер обчисліть =AGGREGATE(9,7,A1:A4): ігнорувати приховані рядки, ігнорувати помилки, рахувати вкладений підсумок як значення. Excel повертає 10 + 30 = 40. Із некешованою A4 рушій до 2.382.3 озброював прапорець прихованих рядків, доходив до A4, запускав її обчислення, і CalcSubtotalFunc для коду 9 успадковував озброєний прапорець, бо він лише виставляє прапорець для кодів 101–111 і ніколи його не очищає. A4 обчислювалася в 10 замість 30, і зовнішній підсумок повертався як 20. Ніщо в жодній із формул не згадує приховані рядки на тому шляху, що дав неправильне число
Прапорець вкладених агрегатів протікав так само, тільки в інший бік. З кодами 0–3 FIgnoreSubtotalCells озброєний, і загальний обхід діапазону в GetValueItemRange його шанує, тож предок, чия формула =SUM(B1:B3), тихо викинув би B2, якби B2 випадково містив SUBTOTAL. Гірше того, CalcSubtotalFunc скидає FIgnoreSubtotalCells у False на виході замість відновлення попереднього значення, тож некешований предок SUBTOTAL, досягнутий посеред обходу, роззброював зовнішній прапорець для кожної клітинки після нього. Реєстр відомих проблем проєкту записує це під HXLS-008 як витік вкладеного стану вибору, і це правильна назва для цього класу багів: глобальний тимчасовий прапорець, який правильний для того кадру, що його встановив, і неправильний для кожного кадру, який його успадковує
Як AggregateGetCellValue і AggregateGetItemValue ізолюють обхід
Виправлення у v2.382.3 ставить межу навколо кожної точки, де AGGREGATE читає значення, яке він не обчислив сам. TXLSCalculator.AggregateGetCellValue обгортає сирий виклик FGetValue: він зберігає обидва прапорці, очищає їх, виконує вибірку й відновлює їх у блоці finally. Зовнішня агрегація все одно застосовує свою політику до щойно вибраної клітинки, бо перевірки прихованих рядків і вкладених клітинок відбуваються в обході навколо вибірки, але сама формула-предок виконується взагалі без політики — і саме так робить 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, бо рушій, який проковтнув би власний сигнал про нестачу пам'яті лише тому, що користувач попросив пропускати #N/A, просто брехав би. Усі три обходи AGGREGATE — 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 поширює першу-ліпшу помилку без змін. Заміна-хелпер AggregateErrorCode зіставляє Variant із відповідним кодом lxError*, незалежно від того, чи Variant є справжнім varError, чи одним із семи рядків помилок, а AggregateValueIsError тепер є просто перевіркою на ненульовий результат. Кожен обхід запам'ятовує перший код помилки, який побачив, і повертає саме цей код, а це також означає, що клітинка, чию формулу ніколи не обчислювали і чия помилка тому надходить як код повернення з FGetValue, а не як кешований Variant, поширюється так само, як кешована. Дві функції підрахунку отримують особливу обробку всередині AggregateCollectRange, і ця обробка відповідає SUBTOTAL, а не SUM. Для внутрішньої функції 0, COUNT, клітинка з помилкою ніколи не рахується й ніколи не поширюється незалежно від коду опцій, бо COUNT рахує лише числа. Для внутрішньої функції 169, COUNTA, клітинка з помилкою є непорожнім значенням і рахується як 1, якщо код опцій не ігнорує помилки, і в такому разі вона пропускається. Ця асиметрія — це те, як Excel поводиться з COUNT і COUNTA і поза AGGREGATE, і це саме та деталь, яку загальне правило «якщо помилка, то поширити» тихо отримує неправильно
Що перевіряє регресійна матриця з восьми опцій
Описану вище фікстуру проганяють як повну матрицю в AggregateFunc_OptionMatrixCoversHiddenErrorsAndNestedAggregates: для кожного коду опцій від 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. Ширший приймальний прогін, записаний у реєстрі відомих проблем, покриває всі дев'ятнадцять номерів функцій проти всіх восьми кодів, із кожним предком і кешованим, і некешованим, — 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)'; // груповий підсумок = 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 та його двійник у класичному рушії відповідають True, коли формула клітинки починається з SUBTOTAL(, AGGREGATE( або _xlfn.AGGREGATE(, з провідним знаком рівності чи без нього, тож формула на кшталт =IF(C1,SUBTOTAL(9,B1:B9),0) або =SUBTOTAL(9,B1:B9)*2 не розпізнається як вкладена й буде порахована двічі кодами 0–3 там, де Excel її пропустив би; генератор, який видає обчислені підсумки, має тримати виклик агрегації на початку формули. Друге: ізоляція живе в трьох обходах AGGREGATE. CalcSubtotalFunc усе ще ходить через GetValueItemRange, CollectRangeValues і SubtotalReduceVariance, які викликають FGetValue напряму, тож SUBTOTAL(109, ...), чий діапазон містить некешовану формулу-предок, усе ще може передати свій прапорець прихованих рядків у того предка. Повний Recalculate обчислює предків до залежних, тож береться кешований шлях і прапорець ніколи не успадковується; вразливість обмежена спеціальним обчисленням через Calculate і робочими книгами, завантаженими без кешованих значень, і якщо ви покладаєтеся на інкрементальний перерахунок за графом залежностей, щоб тримати великі моделі чутливими, та сама гарантія порядку й тримає цей витік у сплячому стані. Третє: обидва прапорці обумовлені Assigned(FIsRowHidden) і Assigned(FIsSubtotalCell). Обидва фасади робочої книги під'єднують колбеки у своїх конструкторах, але код, який будує TXLSCalculator вручну лише з двома початковими аргументами, отримує застарілу поведінку «включити все» для кожного коду опцій — і тихо. Коли підсумок виглядає неправильним, а текст формули — правильним, трасування обчислення крок за кроком — найшвидший спосіб побачити, чи предка обчислили під успадкованим прапорцем, чи колбек просто ніколи не під'єднали
Описаний тут рушій обчислень, декодер опцій, ізольовані обгортки вибірки й регресійна матриця, яка їх фіксує, постачаються з вихідним кодом у складі компонента HotXLS для Delphi, який читає, записує й перераховує робочі книги XLS, XLSX і ODS у Delphi та C++Builder без встановленого Excel