LБлог losLab по разработке программного обеспеченияИнженерные заметки для разработчиков PDF, Delphi и программного обеспечения для работы с документами.

Техническая статья

Опции AGGREGATE и вложенные фильтры в HotXLS для Delphi

HotXLS Delphi Component

Четырёх ячеек достаточно, чтобы =AGGREGATE(9,7,A1:A4) вернула 20 в самописном вычислителе, тогда как Excel показывает 40. В A1 лежит 10, в A2 — 20 на скрытой строке, в A3 — =1/0, а в A4 — =SUBTOTAL(9,A1:A2), групповой промежуточный итог, равный 30. Опция 7 означает «пропускать скрытые строки, пропускать ошибки и считать вложенные промежуточные итоги значениями», так что Excel складывает 10 и 30. Двадцатка появляется тогда, когда настройка «пропускать скрытые строки» внешнего вызова всё ещё включена, пока A4 вычисляет собственное значение, отчего A4 усохает до 10. Чтобы получить 40, нужно две вещи: декодировать три бита опций на правильных позициях и гарантировать, что формула-прецедент никогда не увидит опций ссылающейся на неё формулы

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

Аргумент опций — это матрица из трёх бит, и биты независимы друг от друга. Бит 0 (значение 1) пропускает скрытые строки, бит 1 (значение 2) пропускает значения-ошибки, а бит 2 (значение 4) перестаёт пропускать ячейки, чья собственная формула — SUBTOTAL или AGGREGATE, потому что для младших кодов пропускать их — поведение по умолчанию. Две вещи здесь легко прочитать наоборот. Бит скрытых строк — младший, так что AGGREGATE(9,1,...) — это сумма по отфильтрованным данным, а AGGREGATE(9,2,...) — устойчивая к ошибкам форма. А политика вложенных ячеек направлена противоположно двум другим: только коды 4–7 считают вложенную агрегацию обычным числом. ECMA-376 Part 1 §18.17.7 определяет SUBTOTAL с тем же разделением «включать или исключать скрытые строки» по кодам 1–11 и 101–111; AGGREGATE, хранимая в OOXML под префиксом _xlfn., вминает это разделение в свой аргумент опций, и таблица, которую Microsoft публикует для функции, — контракт, который движок обязан выполнять

OptionHidden rowsError valuesNested SUBTOTAL / AGGREGATE
0includedpropagatedignored
1ignoredpropagatedignored
2includedignoredignored
3ignoredignoredignored
4includedpropagatedincluded
5ignoredpropagatedincluded
6includedignoredincluded
7ignoredignoredincluded

Любой код вне диапазона 0–7 отвергается с #VALUE! — ровно так же поступает Excel

Почему декодирование спотыкается именно об однобитные коды

Декодирование, написанное по пересказу таблицы, а не по самой таблице, обычно выглядит так: игнорировать ошибки для кодов 4 и выше, пропускать скрытые строки для 2, 3, 6 и 7, а политику вложенных ячеек вообще не трогать. Такой вариант может годами жить в продакшене. Сочетание, которое реально используют все, — скрытые строки плюс ошибки — попадает на коды 3 и 7 при обоих прочтениях, поэтому перестановка видна только на однобитном коде. AGGREGATE(9,1,A1:A4) в таком движке вернёт неотфильтрованную сумму, а AGGREGATE(9,2,...) пропустит скрытые строки, продолжая передавать #DIV/0!. В рабочих книгах коды 1 и 2 сами по себе встречаются редко — именно поэтому сверка с опубликованной таблицей ловит больше, чем ожидание файла от заказчика

HotXLS декодирует опции как три проверки принадлежности множеству, по одной на столбец таблицы, и присваивает все три результата на входе вызова AGGREGATE, восстанавливая прежнее состояние на выходе. Присваивание сделано безусловным намеренно. Декод в духе «если опция просит — взвести флаг» позволяет AGGREGATE с кодом 4, вложенному в SUBTOTAL(109, ...), унаследовать внешний флаг скрытых строк вместо того, чтобы его снять. Набросок ниже показывает то же правило для тех, кто пишет или ревьюит собственный вычислитель

// Иллюстративный декод для собственного вычислителя: TAggPolicy и
// DecodeAggregateOptions — имена, которые вы задаёте сами, это не API HotXLS
type
  TAggPolicy = record
    IgnoreHidden, IgnoreErrors, SkipNested: Boolean;
  end;

function DecodeAggregateOptions(OptCode: Integer; out P: TAggPolicy): Boolean;
begin
  Result := (OptCode >= 0) and (OptCode <= 7);   // всё остальное — #VALUE!
  if not Result then
    Exit;
  P.IgnoreHidden := OptCode in [1, 3, 5, 7];       // бит 0
  P.IgnoreErrors := OptCode in [2, 3, 6, 7];       // бит 1
  P.SkipNested   := OptCode in [0, 1, 2, 3];       // бит 2 сброшен
end;
Сравнение двух декодирований опций AGGREGATE: декод по пересказу взводит флаг скрытых строк для кодов 2, 3, 6, 7 и игнорирует ошибки с 4 и выше без политики вложенности, а декод HotXLS проверяет скрытые строки в 1, 3, 5, 7, ошибки в 2, 3, 6, 7 и пропуск вложенных в 0–3
Перестановку вскрывают только однобитные коды, потому что популярное сочетание скрытых строк и ошибок попадает на коды 3 и 7 в обеих таблицах, а коды вне 0–7 возвращают #VALUE! ровно так, как их отвергает Excel

Сторона SUBTOTAL тех же правил скрытых строк, включая то, как соотносятся отфильтрованные и вручную скрытые строки, разобрана в статье о скрытых строках в SUBTOTAL и AGGREGATE

Почему внешний AGGREGATE протекает в формулы, на которые ссылается?

Потому что естественный способ протащить «пропускать скрытые строки» вниз через шесть-семь циклов обхода ячеек — это поле на объекте-калькуляторе, а один и тот же калькулятор вычисляет каждую формулу за проход пересчёта. Такой дизайн держится, пока всё, что выполняется при взведённом поле, принадлежит агрегации, которая его взвела. Ломается он в один конкретный момент: при выборке ячейки. Когда обходчик запрашивает значение ячейки, а там формула без закешированного результата, книга компилирует и вычисляет эту формулу на месте, на том же калькуляторе, со всё ещё действующими внешними настройками

Прогоните входной пример через такой движок. AGGREGATE с кодом 7 включает пропуск скрытых строк и стартует с A1. A1 даёт 10, A2 скрыта — пропущена, A3 — ошибка — пропущена. Затем обходчик доходит до A4, которая ещё не вычислялась. Запускается SUBTOTAL(9,A1:A2) из A4, а реализация SUBTOTAL, которая включает пропуск скрытых строк только для кодов 101–111 (совершенно разумный способ её написать), для кода 9 его не выключает. Она находит настройку уже включённой, пропускает A2 и возвращает 10. Внешний итог становится 10 + 10 = 20. В формуле A4 о скрытых строках не сказано ни слова

Как внешний AGGREGATE без изоляции протекает в свои прецеденты: при включённом пропуске скрытых строк для кода 7 обход доходит до незакешированной A4 с SUBTOTAL 9 по A1:A2, выборка ячейки вычисляет её на том же калькуляторе, вложенный SUBTOTAL наследует настройку и возвращает 10 вместо 30, поэтому итог показывает 20 там, где Excel возвращает 40
Вложенная настройка протекает и в обратную сторону: SUBTOTAL, который на выходе выключает пропуск вложенных ячеек вместо восстановления прежнего состояния, обезоруживает внешнюю политику для каждой ячейки после незакешированного промежуточного итога, встреченного посреди обхода

Настройка вложенных агрегатов протекает в обе стороны. При кодах 0–3 она включена, и универсальный обходчик диапазона, который её учитывает, заставил бы прецедент вида =SUM(B1:B3) выбросить B2, как только B2 окажется SUBTOTAL. В обратную сторону: SUBTOTAL, который на выходе выключает пропуск вложенных ячеек, вместо того чтобы восстановить прежнее состояние, гасит политику внешнего AGGREGATE на остаток его обхода. Любой вычислитель, который тащит контекст в рекурсивный обход через временное поле, имеет такую форму; поле верно для кадра, который его выставил, и неверно для каждого кадра, который его унаследовал

Отсекаем политику на границе выборки

HotXLS ставит границу вокруг каждой точки, где AGGREGATE читает значение, которое сам не вычислял. Перед выборкой обе настройки сохраняются и сбрасываются, после — восстанавливаются в блоке finally. Внешняя агрегация по-прежнему применяет собственную политику к ячейке, которую только что выбрала, потому что проверки скрытых строк и вложенных ячеек происходят в обходчике вокруг выборки. Формула-прецедент выполняется вовсе без политики — как у Excel. Через эту границу проходят все три семейства обходчиков: семейство SUM (SUM, AVERAGE, COUNT, COUNTA, MAX, MIN), редукции в закрытой форме (PRODUCT, STDEV, VAR) и формы с аргументом k (MEDIAN, LARGE, SMALL, процентили и квартили)

// Иллюстративный обходчик для собственного вычислителя. FPolicy, FetchCell,
// IsRowHidden и IsSubtotalCell — члены, которые вы задаёте сами
function TMyEvaluator.FetchForAggregate(Sheet, Row, Col: Integer;
  var V: Variant): Integer;
var
  Saved: TAggPolicy;
begin
  Saved := FPolicy;
  FPolicy := Default(TAggPolicy);     // формула-прецедент сама владеет своей политикой
  try
    Result := FetchCell(Sheet, Row, Col, V);
  finally
    FPolicy := Saved;
  end;
end;

// внутри обхода диапазона: внешняя политика применяется вокруг выборки
for R := R1 to R2 do
begin
  if FPolicy.IgnoreHidden and IsRowHidden(Sheet, R) then
    Continue;
  for C := C1 to C2 do
  begin
    if FPolicy.SkipNested and IsSubtotalCell(Sheet, R, C) then
      Continue;
    Code := FetchForAggregate(Sheet, R, C, V);
    // ... применяем политику ошибок к Code / V, затем накапливаем ...
  end;
end;
Изоляция в HotXLS: перед каждой выборкой ячейки обе настройки сохраняются и сбрасываются, значение читается, и обе восстанавливаются в блоке finally, поэтому формула-прецедент вычисляется без политики, а внешний обходчик всё равно применяет проверки скрытых строк и вложенных ячеек вокруг выборки
Вычисляемые аргументы-массивы получают ту же границу и сохраняют поэлементную ошибку для каждой отказавшей ячейки, а результат исчерпания ресурса никогда не считается игнорируемой ошибкой при опциях пропуска ошибок

Аргументам, которые не являются простыми диапазонами, одного сброса флагов мало. Аргумент вроде A1:A4/(B1:B4-20) — вычисляемый массив, и его поэлементная форма должна выжить, чтобы опция 6 выкинула только элементы, делившие на ноль. HotXLS превращает каждый ссылочный диапазон в двумерный массив через ту же границу, сохраняет отказавшую ячейку как элемент-ошибку вместо аварийного завершения и применяет арифметические операторы и операторы сравнения поэлементно. Диапазон, расползающийся на несколько листов, или диапазон с перепутанными углами возвращает #VALUE!. Диапазон, не влезающий в TXLSCalculator.FormulaArrayMemoryLimit (64 МБ по умолчанию), возвращает результат исчерпания ресурса, и этот результат намеренно вынесен из множества ошибок, которые могут пропускать опции 2, 3, 6 и 7. Проглотить собственный сигнал нехватки памяти, потому что формула попросила игнорировать #N/A, — значит выдать уверенное неверное число, поэтому формула падает вместо этого

Какую ошибку возвращает AGGREGATE, когда он не игнорирует ошибки?

Первую встреченную, без изменений. Схлопнуть все ошибки в #VALUE! — соблазнительный shortcut, из-за которого AGGREGATE(9,4,A1:A3) над ячейкой с #DIV/0! расходится с Excel, передающим #DIV/0! насквозь. HotXLS отображает и настоящие варианты ошибок, и семь строк ошибок (#NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #N/A) обратно в их коды, и каждый обходчик возвращает первый записанный код. Ячейка, чья формула никогда не вычислялась, отдаёт ошибку кодом возврата выборки, а не закешированным значением; передаётся она в точности как закешированная

Две считающие функции следуют правилам SUBTOTAL, а не SUM. Для функции 2, COUNT, ячейка с ошибкой никогда не считается и никогда не передаётся, каким бы ни был код опций, потому что COUNT считает только числа. Для функции 3, COUNTA, ячейка с ошибкой — непустое значение и считается за 1, если только код опций не пропускает ошибки. Именно так Excel обращается с COUNT и COUNTA и вне AGGREGATE, а обобщённое правило «если ошибка — передай» ошибается в обеих

Проверка всех восьми опций на одном листе

Входные четыре ячейки — компактная проверка всех кодов сразу. Вычислите форму SUM и форму MEDIAN по A1:A4 для каждого кода от 0 до 7 и сверьте с заранее посчитанными вручную значениями. Коды 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. Проверка осмыслена, только если A4 не закеширована на момент запуска AGGREGATE, поэтому прогоните каждый случай один раз с уже вычисленными прецедентами и один раз без

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 означает, что настройка протекла в A4
    Sheet.Cells[10, 1].Formula := '=AGGREGATE(12,6,A1:A4)'; // 20     MEDIAN от 10, 20, 30
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

Откройте сохранённый файл в Excel и нажмите Ctrl+Alt+F9 — получите второе мнение по тем же ячейкам. Для более широкого прогона выполните все номера функций от 1 до 19 против всех восьми кодов

Подключение колбэков, когда вы собираете калькулятор сами

Обе настройки держатся на двух колбэках. TXLSCalculator.Create принимает TXLSIsRowHidden и TXLSIsSubtotalCell после геттера значений, и оба по умолчанию равны nil. Стоит не задать один из них — и соответствующий столбец таблицы никогда не сработает, так что любой код опций включает скрытые строки, вложенные агрегации или и то и другое. Объекты книг XLSX и классического XLS передают собственные реализации в конструкторах; код, который создаёт TXLSCalculator вручную над своим источником данных, обязан предоставить оба. Книги распознают вложенную агрегацию по формуле, начинающейся с SUBTOTAL(, AGGREGATE( или _xlfn.AGGREGATE(, со знаком равенства или без, и пользовательский источник должен следовать тому же правилу

// TMySheetSource и FormulaAt — ваши собственные типы; три сигнатуры
// методов совпадают с TXLSGetValue, TXLSIsRowHidden и TXLSIsSubtotalCell
// (SheetIndex — с единицы, Row и Col — с нуля, как их передают книги)
function TMySheetSource.IsSubtotalCell(SheetIndex, Row, Col: Integer): Boolean;
var
  F: string;
begin
  F := FormulaAt(SheetIndex, Row, Col);
  if (F <> '') and (F[1] = '=') then
    Delete(F, 1, 1);
  Result := SameText(Copy(F, 1, 9), 'SUBTOTAL(') or
            SameText(Copy(F, 1, 10), 'AGGREGATE(') or
            SameText(Copy(F, 1, 16), '_xlfn.AGGREGATE(');
end;

// Extern — таблица имён листов, которую ваш хост уже хранит
Calc := TXLSCalculator.Create(Extern, Source.GetCellValue,
  Source.IsRowHidden, Source.IsSubtotalCell);

У правила префикса есть последствие для генерируемых книг. Генератор, записывающий =ROUND(SUBTOTAL(9,B2:B9),2), получает ячейку, которую префиксный тест не распознает, поэтому коды 0–3 её посчитают, и групповой итог попадёт в общий дважды. Держите вызов SUBTOTAL или AGGREGATE самым внешним, а округление убирайте в числовой формат. Полный Recalculate вычисляет прецеденты раньше зависимых, поэтому агрегации обычно читают закешированные значения, и тот же порядок действует при инкрементальном пересчёте по графу зависимостей. Когда итог всё ещё выглядит неверным, а текст формулы — правильным, пошаговая трассировка вычисления покажет, выполнялся ли прецедент по запросу внутри обхода или колбэк вообще не был подключён

Декодер опций, граница выборки и точки подключения колбэков, описанные здесь, поставляются в составе HotXLS Delphi spreadsheet component, который читает, записывает и пересчитывает книги XLS, XLSX и ODS из кода на Delphi и C++Builder без установленного Excel на машине