Матрица опций AGGREGATE и утечка флага в HotXLS для Delphi

HotXLS, нативный компонент работы с электронными таблицами Excel для Delphi и C++Builder, выпустил в сентябре 2026 года два связанных исправления AGGREGATE. Версия 2.382.0 исправила аргумент options так, что коды 1/3/5/7 игнорируют скрытые строки, 2/3/6/7 игнорируют ошибки, а 0–3 игнорируют вложенные ячейки SUBTOTAL и AGGREGATE — ровно так, как это описывает Microsoft. Затем версия 2.382.3 не дала этим флагам выбора протекать в вычисление тех самых ячеек, на которые ссылается функция. Первый дефект досаден так же, как всегда досадны ошибки переписывания таблицы: позиции битов были перепутаны, поэтому каждая формула с ненулевым кодом options получала политику, о которой её автор не просил. Второй интереснее, потому что это форма, которую вы встретите в любом вычислителе, использующем временное поле для передачи контекста в рекурсивный обход. Внешняя агрегация взводит флаг, идёт по диапазону и вытаскивает ячейку, формула которой ещё не вычислена. Эта формула выполняется на том же калькуляторе, видит тот же взведённый флаг и тихо агрегирует не те строки, выдавая число, отличающееся на величину, которую никто не может объяснить по одному тексту формулы

Что на самом деле выбирают опции AGGREGATE от 0 до 7?

Аргумент 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, — это контракт, который движок обязан выполнять, а не удобство

Option	Hidden rows	Error values	Nested SUBTOTAL / AGGREGATE
0	included	propagated	ignored
1	ignored	propagated	ignored
2	included	ignored	ignored
3	ignored	ignored	ignored
4	included	propagated	included
5	ignored	propagated	included
6	included	ignored	included
7	ignored	ignored	included

Почему в 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 типа double, а в остальных случаях отдавал весь массив в 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 промежуточный итог. Хуже того, CalcSubtotalFunc сбрасывает FIgnoreSubtotalCells в False на выходе, а не восстанавливает прежнее значение, поэтому незакешированный прецедент-промежуточный итог, до которого дошли посреди обхода, снимал внешний флаг для каждой ячейки после себя. Реестр известных проблем проекта заводит это под 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*, будь то настоящий 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