HotXLS 数组公式：Excel 为什么插入 @ 和 #VALUE!

文件里如果按普通公式存储，Excel 365 就会往 =SUM(A1:B1*{10,100}) 这类公式里插入 @，并在单元格显示 #VALUE!，因为这时 Excel 会对每个运算符的操作数套用旧式隐式交集。从 v2.384.68 起，HotXLS Delphi Component 改成和 Excel 365 一样的方式存储这些数组运算符公式：在 XLSX 里存成单单元格动态数组公式，在 XLS 里存成单单元格数组公式

这个症状能躲过代码审查。你的 Delphi 服务写出一个工作簿，HotXLS 重算后为 =SUM(A1:B1*{10,100}) 缓存了 210，客户用 Excel 16 一打开，编辑栏里是 =SUM(@A1:B1*@{10,100})，单元格里是 #VALUE!。文件没有任何格式错误。缺的是告诉 Excel「这个公式是在动态数组规则下写的」那段元数据，没有它，Excel 就退回动态数组出现之前的求值模型

HotXLS 明明算对了，Excel 365 为什么还要给公式加 @？

Excel 365 之所以加 @，是因为按定义，没有动态数组标记的公式就是旧式公式，而旧式公式在运算符期待单个值的每一处，都会把多单元格区域收缩成一个单元格。这个收缩就是隐式交集：Excel 取区域里与公式同行（竖直区域）或同列（水平区域）的那个单元格，找不到就得到 #VALUE!。Excel 365 对旧式公式保留这套语义，并把 @ 显示出来，让收缩可见

把 =SUM(A1:B1*{10,100}) 放进 E5，旧式读法就一目了然。A1:B1 是个水平区域，公式坐在 E 列，区域里没有 E 列的单元格，于是 @A1:B1 是 #VALUE!，整个 SUM 跟着中招。换成动态数组规则，同一段文本逐元素相乘，1 × 10 + 2 × 100，返回 210。HotXLS 的公式引擎自 v2.384.61 和 v2.384.63 两次发布起就在按动态数组方式求值了，只是文件格式一直没把这层意思说出来。设 A1:B2 里放着 1、2、3、4，下面是探测公式和 Excel 16 显示的结果：

公式	HotXLS 结果	Excel 16，按普通公式存储	v2.384.68 起的存储方式
=SUM(A1:B1*{10,100})	210	#VALUE!	动态数组，Excel 显示 210
=SUM((A1:B2>2)*1)	2	隐式交集，结果错误或报错	动态数组，Excel 显示 2
=SUMPRODUCT((A1:B2>2)*1)	2	隐式交集，结果错误或报错	动态数组，Excel 显示 2
=MAX(A1:B2-1)	3	隐式交集，结果错误或报错	动态数组，Excel 显示 3
=SUM(A1:B2)	10	10	普通公式，不变

最后一行跟前四行一样重要。SUM(A1:B2) 把区域直接传给一个接受引用的函数参数，没有任何运算符见到多单元格区域，交集也就无从发生。Excel 365 自己就把这个公式存成普通公式，HotXLS 同样如此

HotXLS 如何在 XLSX 与 XLS 里存储数组运算符公式

HotXLS 在 XLSX 里把数组运算符公式写成单单元格动态数组：<c> 元素带上 cm="1"，公式是 <f t="array" ref="E5">，包里多出 xl/metadata.xml，其中 XLDAPR 元数据类型的扩展块持有 dynamicArrayProperties fDynamic="1"。cm 属性是从 1 开始的索引，指向该部件的 cellMetadata 块，背后的 XLDAPR 记录才是告诉 Excel「按动态数组规则求值」的那道凭证。你在 Excel 16 里手敲同一公式再保存，写出的就是这套结构——当初的目标布局正是这么定下来的

XLS 里没有元数据部件，所以 HotXLS 用上 BIFF8 为数组求值准备的唯一构造：单单元格数组公式。单元格拿到一条 FORMULA 记录，其 token 流是单个指向自身的 PtgExp，后跟一条 ARRAY 记录（$0221），在单单元格范围上装着真正解析后的公式。Excel 365 往 XLS 写动态数组公式用的也是这条路，老版本 Excel 读到文件时看到的就是经典的 Ctrl+Shift+Enter 数组公式

不需要任何新 API。两个引擎里，标记都发生在你通过普通单元格 API 赋公式的那一刻。XLSX 这边就是 TXLSXCell.Formula：

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Data');
    Sheet.Cells[1, 1].Value := 1;
    Sheet.Cells[1, 2].Value := 2;
    Sheet.Cells[2, 1].Value := 3;
    Sheet.Cells[2, 2].Value := 4;

    // 运算符作用在区域或内联数组上：按动态数组存储
    Sheet.Cells[5, 5].Formula := '=SUM(A1:B1*{10,100})';
    Sheet.Cells[6, 5].Formula := '=SUM((A1:B2>2)*1)';
    // 区域直接传给函数：保持普通 <f>
    Sheet.Cells[7, 5].Formula := '=SUM(A1:B2)';

    if Book.Recalculate = lxOk then
      Writeln(VarToStr(Sheet.Cells[5, 5].Value));   // 210

    // 数组根单元格回读的文本不带前导 '='
    Writeln(Sheet.Cells[5, 5].Formula);              // SUM(A1:B1*{10,100})

    Book.SaveAs('probe.xlsx');   // E5 和 E6 得到 cm="1" + t="array"
  finally
    Book.Free;
  end;
end;

转换之后，TXLSXCell.Formula 回读的文本不带 =，与 TXLSXRange.SetDynamicArrayFormula 存储的形式一致，所以赋值后再比较公式字符串的代码，应该先把前导 = 归一化

经典引擎通过单单元格上的 IXLSRange.Formula 遵循同一条规则。赋公式的动作在内部被改道到单单元格数组路径，所以保存出的 XLS 里是 FORMULA 加 ARRAY 那一对：

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Range['A1', 'A1'].Value := 1;
  Sh.Range['B1', 'B1'].Value := 2;
  Sh.Range['A2', 'A2'].Value := 3;
  Sh.Range['B2', 'B2'].Value := 4;

  Sh.Range['E5', 'E5'].Formula := '=SUM(A1:B1*{10,100})';  // ARRAY 记录
  Sh.Range['E6', 'E6'].Formula := '=MAX(A1:B2-1)';         // ARRAY 记录
  Sh.Range['E7', 'E7'].Formula := '=SUM(A1:B2)';           // 普通 FORMULA

  Writeln(VarToStr(Sh.Range['E5', 'E5'].Value));   // 210
  Writeln(VarToStr(Sh.Range['E6', 'E6'].Value));   // 3
  Wb.SaveAs('probe.xls');
end;

如果你要落的是多单元格结果而不是标量聚合，显式 API 仍是对的工具：预先量好尺寸的矩形用 SetArrayFormula，见HotXLS 的动态数组溢出公式一文；想在自己定尺寸的区域上要 XLSX 动态数组标记就用 TXLSXRange.SetDynamicArrayFormula。本文的自动路径只覆盖敲进单个单元格的公式

HotXLS 把哪些公式标记成动态数组？

只有当某个运算符的子树里长出能产生数组的操作数时，HotXLS 才做标记。检查在编译后的语法树上进行，一个操作数在三种情况下产生数组：它是多单元格区域、是内联数组常量、或是自身带有这类操作数的另一个运算符表达式。括号是透明的。算数的运算符包括四则与乘方（+ - * / ^）、拼接（&）、六种比较、一元正负号和百分号：

• A1:B1*{10,100}、(A1:B2>2)*1、--(B1:B2>0) 和 A1:B2-1 会被标记，不管出现在公式哪个位置，SUMPRODUCT 里也算
• SUM(A1:B2) 和 SUMPRODUCT(A1:A2,{1;10}) 不标记，因为区域和数组直接进了函数实参，没有运算符碰它们
• A1*2 或 SUM(A1,B1)*2 不标记：对这道检查来说，单单元格引用和函数结果都是标量

三条边界是刻意划的。第一，只在公式经 API 敲入时才标记，即 XLSX 引擎的 TXLSXCell.Formula，经典引擎的单单元格 Formula 或 Value 赋值。从文件加载来的公式原样写回，因为别的生成器产出的旧式公式可能刻意依赖隐式交集。第二，文本里既没有 : 也没有 { 的，不做第二次编译直接跳过。第三，本会溢出的公式，比如单独一句 =A1:B1*2，会标记成锚定在你放置位置上的单单元格动态数组。HotXLS 不替它溢出，Excel 下次重算时会把结果扩展到相邻单元格

这条操作数规则与HotXLS 里定义名称的隐式交集一文讲的实参类别规则是姊妹篇。那篇说的是声明为 value 类别的函数参数；这篇说的是运算符——旧式模型里运算符永远只要值

计算引擎里改了什么，才让结果对上

v2.384.68 的存储修复，前提是 HotXLS 公式引擎早已能返回 Excel 365 的值，这靠两个引擎里更早的几个修复。最显眼的是 SUMPRODUCT：v2.384.61 之前它只接受两个以上的普通区域，所以 SUMPRODUCT((B1:B2>0)*1)、SUMPRODUCT(--(B1:B2>0))，连单实参的 SUMPRODUCT(B1:B2) 都返回 #N/A。现在 HotXLS 按 Excel 的规则逐元素求值表达式实参：

• 每个实参形状必须完全一致，标量按 1 × 1 计，否则结果是 #VALUE!
• 任一实参里的错误值直接作为结果返回
• 文本和逻辑元素按 0 计，所以仍需 (B1:B2>0)*1 或 -- 把 TRUE 变成 1
• 实参全是普通区域时保留原先的流式循环，大区域不会被物化成数组

SUM 家族（SUM、COUNT、AVERAGE、MIN、MAX、COUNTA）在实参是作用在区域上的运算符表达式时，用同一套逐元素求值器，所以 =SUM((B1:B2>0)*1) 会把两行都数进去，而不是只看第一个单元格。v2.384.62 让空格交集运算符返回两个引用的公共矩形，不相交时给 #NULL!，于是 =SUM(A1:B2 B1:B2) 是 6 而不是 2，结果也能喂给 ROWS、INDEX 这类引用参数。v2.384.63 给解析器加了 {1,2;3,4} 这样的内联数组常量（逗号分列、分号分行）和 (A1:B2,D4) 这样的引用并集。逐元素比较还给空白元素按另一侧定类型，对逻辑值就定 FALSE，与 v2.384.53 的标量规则一致，详见HotXLS 里的比较链与空白单元格

var
  V: Variant;
begin
  // Book 是第一个例子里的 TXLSXWorkbook；
  // 它的活动表上放着 A1:B2 = 1, 2, 3, 4
  V := Book.Calculate('=SUMPRODUCT((A1:B2>2)*1)');   // 2
  V := Book.Calculate('=SUMPRODUCT(A1:B2)');          // 10，单实参
  V := Book.Calculate('=SUMPRODUCT(A1:A2,{1;10})');   // 31 = 1*1 + 3*10
  V := Book.Calculate('=SUM(A1:B2 B1:B2)');           // 6，公共区域 B1:B2
  V := Book.Calculate('=SUM((A1:B2,B1:B2))');         // 16，重叠部分计两次
  V := Book.Calculate('=ROWS({1,2,3;4,5,6})');        // 2
  V := Book.Calculate('=TRUE*1');                     // 1，v2.384.61 之前是 -1
end;

TXLSXWorkbook.Calculate 对活动表求值一段公式字符串但不存储它，是查引擎行为的快捷手段。关于 @ 本身要提醒一句：HotXLS 历来把两个引用之间的 @ 当二元交集接受，现在也按真正的交集语义求值这种形式。而在 Excel 365 里，@ 是一元的隐式交集前缀。别往公式文本里写 @ 还指望它是 Excel 的那个意思；要交集就用空格，动态数组的语义交给上面的存储规则处理

Excel 为什么拒开文件或算出错值？

让 Excel 接受动态数组标记花了三个修复，任何一个自往返测试都抓不到，因为每一种情况下 HotXLS 读自己的输出都是对的。每一个都是在 Excel 16 里打开 HotXLS 输出、一次换一个变量试出来的：

1. 扩展 GUID 必须全小写。xl/metadata.xml 里的 ext uri 必须恰好是 {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}。旧版 HotXLS 模板把它写成大小写混合，Excel 16 直接拒开整个包，不只是那个单元格。v2.384.68 之前用 TXLSXRange.SetDynamicArrayFormula 建的工作簿也有同样问题
2. 数组根文本不带前导 =。XLSX 写出器把数组根的存储文本逐字发进 <f>。转换后的单元格如果留着 =，元素就成了 <f t="array" ref="E5">=SUM(...)</f>，Excel 开文件时同样拒收。HotXLS 在转换时把它剥掉，这也是 TXLSXCell.Formula 回读不到它的原因
3. Delphi 里 Double(True) 是 -1。Variant 转换遵循 COM 约定，TRUE 是全 1 位，VarIsNumeric(True) 也返回 True。v2.384.61 之前，这让 =TRUE*1 返回 -1，还让逻辑数组元素被归类成数字，(B1:B2>0)=TRUE 这类比较因此出错。现在 HotXLS 在标量算术、数组算术和数组元素归类里都先测 varBoolean 再把 Variant 当数字，TRUE 按 1 计

BIFF8 操作数类别：写给格式实现者的字节级细节

BIFF8 里每个操作数 token 都在 token 字节自身里带着操作数类别，而 Excel 信任这个类别胜过公式的结构。[MS-XLS] 把类别定义为 token 第 5、6 位上的两位 PtgDataType 字段：1 是引用，2 是值，3 是数组。低五位命名 token，于是同一个区域引用有三种拼法：

Token	引用类别	值类别	数组类别
PtgRef	$24	$44	$64
PtgArea	$25	$45	$65
PtgArray	$20	$40	$60

HotXLS 在不同地方把其中三条搞错了，每一条都在 Excel 里给出一个独特的症状，而 HotXLS 自己读回来一切正常：

• 引用类别的数组常量。编码器按上下文选类别，而 SUM、ROWS 的参数是引用类别，于是 =SUM({1,2}) 里的 PtgArray 被写成 $20。Excel 把整个公式显示成 =#N/A。数组常量永远不可能是引用，所以从 v2.384.63 起，上下文要引用时 HotXLS 一律写数组类别 $60
• PtgIsect 与 PtgUnion 的值类别操作数。二元运算符取值类别操作数，这对 * 是对的，对引用运算符就是错的。PtgIsect（$0F）前面放上 $45 的区域，Excel 会把 =SUM(A1:B2 B1:B2) 读成 =SUM(@A1:B2 @B1:B2)，返回 #VALUE!。从 v2.384.62 起，PtgIsect 和 PtgUnion（$10）的操作数按引用类别 $25 写出
• ARRAY 记录里的值类别操作数。只要操作数是值类别，Excel 在数组公式内部也照样套隐式交集。HotXLS 在那里写的是 $45，于是 =SUM(A1:B1*{10,100}) 的单单元格数组公式在 Excel 里算出 10。从 v2.384.68 起，ARRAY 记录的 token 流把每个值类别的引用和数组常量都提升到数组类别 $65 和 $60，这正是 Excel 写出的形式

忽略类别位的读取器对这三种都能愉快往返，所以如果你自己维护 BIFF8 写出器，请把每个操作数 token 的类别位与 Excel 保存的同一公式文件比对，别只比 token 编号

速查

• 普通未标记公式里的运算符收到多单元格区域或内联数组时，Excel 365 显示 @
• HotXLS v2.384.68 及以后把这类公式存成 XLSX 单单元格动态数组（cm="1"、t="array"、XLDAPR 元数据）和 XLS 单单元格数组公式（FORMULA 带 PtgExp 加 ARRAY $0221）
• 只有运算符操作数才算数；直接传给函数实参的区域仍是普通公式
• 只有经 TXLSXCell.Formula 或经典引擎单单元格 Formula / Value 敲入的公式才被标记；加载来的公式不动
• 转换后的根单元格回读时没有前导 =
• 动态数组的 ext uri GUID 必须小写，否则 Excel 拒收整个包
• Delphi 里 Double(True) 是 -1；数值转换前先测 varBoolean
• BIFF8：数组常量永远不做引用类别，PtgIsect / PtgUnion 操作数用引用类别，ARRAY 记录操作数用数组类别

HotXLS 在 Delphi 和 C++Builder 上原生读写并计算 XLS 与 XLSX 工作簿，存储数组运算符公式的方式让 Excel 365 打开时看到的就是 HotXLS 算出的那几个值。版本、文档与试用下载见 HotXLS Delphi 电子表格组件