HotXLS for Delphi 中的 AGGREGATE 选项与嵌套门控

四个单元格就足以让 =AGGREGATE(9,7,A1:A4) 在一个手写求值器里算出 20，而 Excel 给出 40。A1 是 10，A2 在隐藏行上是 20，A3 是 =1/0，A4 是 =SUBTOTAL(9,A1:A2)，一个值为 30 的分组 subtotal。选项 7 表示跳过隐藏行、跳过错误、把嵌套 subtotal 当作普通值，所以 Excel 把 10 和 30 相加。当外层调用的「跳过隐藏行」设置在 A4 求自己的值时仍然开着，A4 被缩成 10，20 就出现了。要得到 40 需要两件事：把三个选项位解码到正确的位置上，并确保被引用的公式永远看不到引用它的公式所带的选项

AGGREGATE 的 options 0 到 7 到底选了什么？

options 参数是一个三位矩阵，而这三个位彼此独立。第 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 以 _xlfn. 前缀存储在 OOXML 里，把那个划分折进了自己的 options 参数，微软为该函数发布的那张表就是一个引擎必须满足的契约

选项	隐藏行	错误值	嵌套的 SUBTOTAL / AGGREGATE
0	计入	传播	忽略
1	忽略	传播	忽略
2	计入	忽略	忽略
3	忽略	忽略	忽略
4	计入	传播	计入
5	忽略	传播	计入
6	计入	忽略	计入
7	忽略	忽略	计入

0 到 7 之外的代码会被以 #VALUE! 拒绝，Excel 也是这么做的

为什么解码错误总在单一位代码上才现形

照表格的转述版而不是表格本身写出来的解码，往往长这样：代码 4 起忽略错误，代码 2、3、6、7 跳过隐藏行，嵌套策略干脆整个丢掉。这个版本能在生产环境里活很久。大多数人实际用的组合——隐藏行加错误——在两种读法下都落在代码 3 和 7 上，所以错位只在单一位代码上才现形：AGGREGATE(9,1,A1:A4) 返回未过滤的和，而 AGGREGATE(9,2,...) 跳过隐藏行、却仍然传播 #DIV/0!。真实工作簿很少单独用代码 1 和 2，这正是为什么对照发布的表格审一遍解码，比等客户文件出事能抓到更多问题

HotXLS 把 options 解码成三个集合成员测试，表格的每一列一个，在 AGGREGATE 调用开始时赋上全部三个结果，结束时恢复先前生效的状态。赋值是刻意写成无条件的。写成「选项要求就置位」的解码，会让嵌在 SUBTOTAL(109, ...) 里的代码 4 AGGREGATE 继承外层的隐藏行设置而不是清掉它。下面的示意代码把同一条规则展示给所有自己写或审求值器的人

// 自己写求值器时的示意解码：TAggPolicy 和
// DecodeAggregateOptions 是你自己起的名字，不是 HotXLS API
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;

同一套隐藏行规则的 SUBTOTAL 侧，包括筛选行与手工隐藏行的对比，见SUBTOTAL 与 AGGREGATE 隐藏行一文

为什么外层 AGGREGATE 会泄进它引用的公式？

因为要把「跳过隐藏行」传给六七个单元格遍历循环，最自然的做法就是在求值器对象上放一个字段，而一次重算里的所有公式都共用同一个求值器。只要字段设着期间运行的一切都属于设下它的那次聚合，这个设计就成立。它在一个特定时刻失效：取值。当遍历器要一个单元格的值，而那个单元格装着一个还没有缓存结果的公式时，工作簿会当场编译并求值该公式——就在同一个求值器上，外层设置还原封不动

把开头的例子放进这样一个引擎里走一遍。代码 7 的 AGGREGATE 打开隐藏行跳过，从 A1 开始。A1 给 10，A2 在隐藏行上被跳过，A3 是错误也被跳过。然后遍历器走到还没算过的 A4。A4 的 SUBTOTAL(9,A1:A2) 开始运行，而一个只在代码 101 到 111 才打开隐藏行跳过的 SUBTOTAL 实现（一种完全合理的写法）在代码 9 下从不把它关掉。它发现设置已经开着，跳过 A2，返回 10。外层总和变成 10 + 10 = 20。A4 的公式里没有一个字提到隐藏行

嵌套聚合设置往两个方向都泄漏。代码 0 到 3 时它是开的，尊重它的通用区域遍历器会让 =SUM(B1:B3) 这样的先导在 B2 恰好含有 SUBTOTAL 时丢掉 B2。往另一个方向，一个在结束时把嵌套跳过关掉而不是恢复先前状态的 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;

不是普通区域的参数需要的比清掉的标志更多。像 A1:A4/(B1:B4-20) 这样的参数是计算出来的数组，它的元素形状必须保留下来，选项 6 才能只丢掉除以零的那些元素。HotXLS 把每个被引用的区域经由同一条边界变成二维数组，把失败的单元格存成错误元素而不是中途放弃，并逐元素应用算术与比较运算符。跨多个表的区域、或角点倒置的区域，返回 #VALUE!。对 TXLSCalculator.FormulaArrayMemoryLimit（默认 64 MB）来说过大的区域返回资源上限结果，而这个结果被刻意排除在选项 2、3、6、7 可以跳过的错误集合之外。因为公式要求忽略 #N/A 就把引擎自己的内存耗尽信号吞掉，会产出一个自信而错误的数字，所以公式直接失败

不忽略错误时 AGGREGATE 返回哪个错误？

遇到的第一个，原样返回。把所有错误折叠成 #VALUE! 是省事的捷径，它会让覆盖 #DIV/0! 单元格的 AGGREGATE(9,4,A1:A3) 与 Excel 不一致——Excel 会把 #DIV/0! 原样传出。HotXLS 把真正的错误 Variant 和七种错误字符串（#NULL!、#DIV/0!、#VALUE!、#REF!、#NAME?、#NUM!、#N/A）都映射回各自的码，每个遍历器返回它记录的第一个码。公式从未被计算的单元格以取值返回码的形式交付错误，而不是以缓存值的形式；它的传播方式与缓存错误完全一样

两个计数函数跟随的是 SUBTOTAL 而非 SUM。函数 2（COUNT）下，错误单元格无论选项代码如何都不计数也不传播，因为 COUNT 只数数字。函数 3（COUNTA）下，错误单元格是非空值、计 1，除非选项代码跳过错误。这种不对称正是 Excel 在 AGGREGATE 之外对待 COUNT 和 COUNTA 的方式，而一条笼统的「遇错即传播」规则会把这两个都搞错

在一张工作表上核对全部八个代码

开头的四个单元格构成对每个代码的紧凑核对。对 0 到 7 的每个代码，对 A1:A4 求 SUM 形式和 MEDIAN 形式，与手推的值比较。代码 0、1、4、5 必须传播 A3 的 #DIV/0!，因为它们都不跳过错误。代码 2 给 SUM 30、MEDIAN 15，来自 10 和 20、嵌套的 A4 被跳过。代码 3 给 10 和 10。代码 6 给 60 和 20，因为 A4 的 30 这次计入。代码 7 给 40 和 20，而没有取值边界的引擎报的正是 20。只有 AGGREGATE 运行时 A4 未缓存，这个核对才有意义，所以每种情况都要跑两遍：一遍先导已算好，一遍没有

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 说明有设置泄进了 A4
    Sheet.Cells[10, 1].Formula := '=AGGREGATE(12,6,A1:A4)'; // 20     10、20、30 的 MEDIAN
    Book.Recalculate;
    Book.SaveAs('aggregate-options.xlsx');
  finally
    Book.Free;
  end;
end;

在 Excel 里打开保存好的文件并按下 Ctrl+Alt+F9，对同样的单元格得到第二意见。更大规模的扫描，则让 1 到 19 的每个函数号都过一遍全部八个代码

自己构建求值器时如何接回调

两个设置都依赖两个回调。TXLSCalculator.Create 在值 getter 之后接受一个 TXLSIsRowHidden 和一个 TXLSIsSubtotalCell，两者默认都是 nil。缺了任何一个，表格里对应的那一列就永远不会生效，于是每个选项代码都等于包含隐藏行、嵌套聚合，或者两者都包含。XLSX 和经典 XLS 工作簿对象在自己的构造函数里传入各自的实现；在自己的数据源上手工创建 TXLSCalculator 的代码必须把两个都提供。工作簿靠以 SUBTOTAL(、AGGREGATE( 或 _xlfn.AGGREGATE( 开头（带不带前导等号都行）的公式识别嵌套聚合，自定义数据源也应使用同一条规则

// TMySheetSource 和 FormulaAt 是你自己的类型；三个方法
// 签名与 TXLSGetValue、TXLSIsRowHidden 和 TXLSIsSubtotalCell 一致
//（SheetIndex 从 1 起，Row 和 Col 从 0 起，与工作簿传入的一致）
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 一起交付，它在 Delphi 和 C++Builder 代码里读写并重算 XLS、XLSX 和 ODS 工作簿，机器上无需安装 Excel