LlosLab Software Development Blog面向 PDF、Delphi 和文档软件开发者的工程笔记

技术文章

HotXLS 查找扫描与虚假循环引用

HotXLS Delphi Component

把 =VLOOKUP(A1,B:B,1) 放进 B 列的一个单元格里,Excel 毫无怨言地计算它。把同一个工作簿喂给依赖图重算引擎,你很可能得到一个循环引用错误,因为公式依赖一个包含公式自身的区域。HotXLS 在 v2.361.98 之前报的正是这个。修复不是针对整列区域的特例;而是一种区分——两类依赖边的区分,电子表格引擎需要它,而普通有向图没有

查找家族——LOOKUP、MATCH、HLOOKUP、VLOOKUP、XLOOKUP 与 XMATCH——的查找数组参数现在被标记为扫描引用。扫描引用仍然播种脏标记,因此编辑区域内的单元格仍会触发公式重算,但它绝不参与循环检测或求值排序。真循环照样找得到;假循环消失了

为什么 Excel 允许查找区域包含公式自身?

因为这个参数的消耗方式不同于算术操作数。查找家族在区域内扫描缓存值并返回匹配;它不要求区域先被完整求值。Excel 把自重叠的查找区域视为读取这些单元格当前持有的内容——这与它对任何非迭代工作簿的语义一致:本轮未重算的单元格贡献其上一次计算的值

整列引用让这成为常态而非冷僻案例。B:B 是在行会追加的工作表里写"整张查找表"的地道方式,而任何住在 B 列的公式都因此落在自己的查找区域之内。财务模型、对账表与审计工作簿天天这么写,通常没人注意到区域重叠

单元格 B7 的 VLOOKUP(A1,B:B,1) 位于自己的整列查找区域 B:B 之内,这种自重叠 Excel 用缓存值毫无怨言地计算
整列查找区域让自重叠成为财务模型与审计工作簿的常态,而不是冷僻角落

依赖图对同一个公式会怎么做

HotXLS 做增量重算,这需要一个真正的依赖图:单元格为节点、引用为边、求值用拓扑序,再用强连通分量一遍来分类循环。那套机制在增量重算一文中描述,而假阳性正是拜它所现

从单元格 B7 的 =VLOOKUP(A1,B:B,1) 提取依赖,第二个参数给出一个包含 B7 自身的区域。图里从此有了自环。该节点的入度永远到不了零,拓扑一遍永远排不上它,分量一遍把它归类为循环。引擎对交给它的图推理得完全正确。错的是图这个模型——电子表格有两种边,它只编码了一种

B:B 查找区域给图节点 B7 一个自环,入度永不到零,v2.361.98 之前的 HotXLS 因此报告虚假循环引用
重算引擎对交给它的图推理正确;错的是用图给电子表格建模

两类边,同一张图

这次改动给已解析引用记录加了一个标志:TXLSDepRange.LookupScan,依赖提取器在遍历六个函数之一的查找数组参数时设置它。在下游,来源于这些引用的边与普通边分开存储:图节点在正常依赖方与先决方列表之外,另存 ScanDependents 与 ScanPrecedents 列表

正是这个分离让语义正确。扫描边由脏传播遍历,因此编辑 B:B 任何位置仍把 B7 标脏,B7 照样重算。扫描边从不计入入度,也从不进入分量构建器,因此既不能制造拓扑死锁,也不能被归类为循环。库里两套图实现——经典的单工作簿图与承载分量分析的跨工作簿工作区图——一并修改;任其漂移,同一工作簿单独打开和作为工作区一部分打开,重算结果就会不同

来自 TXLSDepRange.LookupScan 的扫描边驱动脏传播进入 ScanPrecedents 与 ScanDependents,但绝不计入入度或循环
B:B 内的编辑仍把公式标脏,而扫描边既不能让拓扑遍历死锁,也不能制造循环
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Ledger');
    Sheet.Cells[1, 1].Value := 'ACC-4471';
    Sheet.Cells[1, 2].Value := 1200.00;
    // 查找区域覆盖 B 列,而这条公式就住在里面
    Sheet.Cells[7, 2].Formula := 'VLOOKUP(A1,B:B,1)';

    case Book.Recalculate of
      lxOk:
        // v2.361.98 之前,这个分支对此表不可达
        SaveReport(Book);
      lxErrorRef:
        LogWarning('Genuine circular reference - review model inputs');
    end;
  finally
    Book.Free;
  end;
end;

把扫描边排除在排序之外,你放弃了什么

恰好一样东西,值得直说而不是藏着。因为扫描边不参与拓扑序,查找公式可能在同一遍里先于其查找区域中某些单元格的重算而被求值,从而读到它们的上一次值。结果在下一轮重算时收敛

这是可接受的,因为 Excel 就是这么做的。对未启用迭代计算的工作簿,Excel 自己对"本轮尚未重算的值"给的答案就是上一次计算的值,所以复现这个行为的引擎是在贴合参照实现,而不是近似它。如果你需要自引用模型上真正收敛的答案,那个机制是带显式迭代上限的迭代计算,迭代计算一文有所覆盖,它适用于真循环,而不是扫描重叠

藏在修复里的回归风险

给 TXLSDepRange 加 LookupScan 引入了一个与查找毫无关系、与 Pascal 息息相关的风险。TXLSDepRange 是非托管记录,该类型的局部变量不会被零初始化。代码库里每一处手工构建它的地方——包括数据表依赖块与若干测试辅助——都必须更新为显式设置新字段。漏掉一处,栈上碰巧是什么字节就决定该引用是否被当作扫描边,由此产生的重算 bug 会随无关的代码改动时隐时现

// 非托管记录里新增一个 Boolean 字段,让每处手工
// 构建点都成为潜在 bug。两种安全惯用法:
var
  R: TXLSDepRange;
begin
  FillChar(R, SizeOf(R), 0);      // 先全部清零,再逐项填入
  R.Sheet1 := SheetIndex;
  R.Sheet2 := SheetIndex;
  R.Row1 := Row; R.Col1 := Col;
  R.Row2 := Row; R.Col2 := Col;

  // 或者在每处把每个字段——包括新字段——都设置一遍
  R.LookupScan := False;
end;

由此挣得的一般规则:给一个在多处栈上构建的记录加字段,是比看上去风险更高的改动,而且编译器不会帮你找出那些位置。如果记录可从热路径到达,与其指望每个调用点都被更新,不如用一个把它完整初始化的辅助例程

区分真循环与扫描重叠

这次改动丝毫没有削弱循环检测。B7 里的 =B7+1 仍是循环,三条公式首尾相接的链仍是循环,两者仍通过重算结果报告,循环成员保留其上一次缓存值,循环之外的一切保持最新。改变的只是:查找数组参数不再制造 Excel 看不见的循环

如果你在审计工作簿,想知道引擎实际解析了哪些引用、按什么顺序,求值跟踪器就是干这个的工具;公式求值跟踪器一文介绍如何解读其输出。HotXLS 是原生 Delphi 与 C++Builder 电子表格组件,读写 XLS、XLSX、ODS 与 CSV 无需安装 Excel,重算引擎在每种格式上都相同;当前函数与引擎覆盖情况列在 HotXLS Delphi spreadsheet component 产品页