跨表引用为什么总被判定成死引用?读一个开源 Excel 公式检查工具的实现
今天在 AI Agent 开发实践中心翻到一个挺实用的开源小工具:Excel 公式批量分析。它会读取 .xlsx,把所有公式抽出来,做静态检测——循环引用、死引用、跨表引用、硬编码数值,然后输出一份报告,对能规则化的修复项还支持写回。
这种工具的价值在于:财务那张表跑了一年,公式套公式、跨表引分表,人眼根本查不出错。有工具能批量扫一遍,哪怕只抓出两条真问题,也够回本。
我拿它跑了一个自己构造的最小样例,结果在跨表引用上翻车了。这个过程挺值得记一下,因为它是一个"看起来很对的实现"里非常典型的一类漏洞。
一、先看现象
我造了一个只有两个 Sheet 的文件:
import openpyxl
wb = openpyxl.Workbook()
ws = wb.active
ws.title = "Sheet1"
ws["A1"] = 10
ws["A5"] = "=Sheet2!B2"
ws2 = wb.create_sheet("Sheet2")
ws2["B2"] = 5
wb.save("test_formula.xlsx")
Sheet1!A5 引用的是 Sheet2!B2,而 Sheet2!B2 里确实有值 5。这行公式完全正常。
跑 analyze_excel.py,输出是这样:
=== Sheet1!A5 ===
公式: =Sheet2!B2
引用信息:
B2: 值=5
跨表引用: Sheet2
【本地检测到的问题】
[!!高!!] dead_ref: 引用了不存在的单元格 B2
[提示] cross_sheet: 跨表引用了工作表【Sheet2】
注意看:同一段输出里,"引用信息"已经明确解析出 B2: 值=5,紧接着下一段又说"引用了不存在的单元格 B2"。工具自己和自己打架。
而且这条被标成了 high 级别——也就是最该让人警觉的那一档。
二、再看一个矛盾
同一个文件,再跑修复模块 fix_excel.py 的 dry-run:
[需人工确认] Sheet1!A5
类型: rewrite_formula
原值: =Sheet2!B2
新值: (保持不变)
原因: 引用了常量值单元格 B2(值:5),属正常用法
修复模块的判断是对的:目标存在,是常量,正常用法。
所以两个模块对同一个格给出了相反结论。这说明问题不是"作者没想到跨表这种情况",而是两条代码路径对引用的解析方式不一样。
三、根因:解析的时候把表名丢了
看 graph.py 里提取引用的函数:
def _parse_cell_ref(ref: str) -> Tuple[int, int, str]:
if '!' in ref:
ref = ref.split('!')[1] # ← 这里把工作表前缀切掉了
clean = ref.replace('$', '')
...
Sheet2!B2 被规约成 B2,表名在这一步就丢了。
然后死引用检测是这么写的:
def find_dead_refs(self) -> List[LocalIssue]:
for cell_key, refs in self.graph.items():
own = cell_key.split('!')[0] # 当前公式所在的表,这里是 Sheet1
for ref in refs:
info = self._resolve(f"{own}!{ref}") # ← 拼成 Sheet1!B2 去查
if info is None:
issues.append(LocalIssue("dead_ref", "high", ...))
own 恒等于公式所在的表。所以查的是 Sheet1!B2——不存在——直接落进第一个 if,报 high。
结论:只要公式里有跨表引用,就一定产生一条"高级别死引用"误报,和这个引用本身有没有问题完全无关。
这个 bug 的隐蔽之处在于:它不是"少写了某个分支",而是"数据结构里丢了信息,后面所有依赖这个信息的判断都静默失效"。图构建阶段把表名剥离,检测阶段就拿不回来了,只能拿当前表去凑——凑出来必然错。
四、影响面比想象的大
如果只是单看这一个格,好像无所谓。放到真实工作簿里:
- high 级别失去意义。报告里 high 是最需要看的,现在混进一批必然存在的误报,用户要么逐条排查(比人工查还累),要么干脆不信 high——两种结果都让工具失去价值。
- 修复计划会跟着歪。
fix_excel.py的规则 2 是按 dead_ref 的引用目标分类的,上游给的分类错了,下游怎么写回都不可靠。这次两个模块结论不一致,纯粹是因为它们各自解析了一遍,恰好的那条路径是对的。 - 噪音随跨表引用数量线性增长。多分表汇总是极常见场景,一个汇总页能引用几十个分表,报告直接被淹没。
五、怎么修
两个方向,任选其一:
方案 A:把表名一路带下去。 让 extract_references() 返回 (sheet, addr) 或者保留带前缀的完整引用,find_dead_refs() 解析时优先用引用自带的表名,只有裸引用(不带 !)才回落到当前表。
方案 B:在检测处加个短路。 引用自带表名且与 own 不同时,跳过死引用判定,交给 find_cross_sheet_refs() 处理。这样不会对同一个格重复报两条。
方案 A 更彻底,方案 B 改动更小。不管选哪个,都建议补一对用例:跨表引用 + 目标存在、跨表引用 + 目标不存在。有这个用例,这个分支以后就不容易回退。
六、顺手提一句另一个设计问题
同一份报告里,=A1*1.13 这种"引用一个常量参数单元格"也会被报成 dead_ref(严重度 low,修复模块会标"属正常用法")。
问题不在于报它,而在于它和"引用了根本不存在的单元格"共用同一个类别名。一个是致命的,一个是设计选择,塞在同一个 dead_ref 里,读报告的人第一眼分不清哪个是哪个。如果类别不好拆,至少 message 里应该一眼能看出区别——现在这两条的区别只有严重度和括号里的值。
七、这类工具的通用检查清单
借这个案例,列几条我以后写类似静态分析工具时会先问自己的问题:
1. 限定符(表名、命名空间、模块前缀)在解析阶段是否被保留? 一旦在入口丢掉,后面所有判断都会静默降级成"猜当前作用域"。
2. 同一个事实是否有两处独立解析? 如果有,它们迟早会不一致。要么共用一份解析结果,要么至少加一致性测试。
3. severity 高的类别,误报率是多少? 高优先级类别必须近乎零误报,否则等于没有高优先级。
4. 有没有一条"最小反例"用例? 跨表、跨文件、带引号表名('My Sheet'!A1)这类边界,一条用例就能挡住整类回退。
工具已经提交了 issue,仓库在 agent-best-practices/excel-formula-explainer,感兴趣的可以拿自己的表跑一遍——记得把跨表引用的结果单独看一眼,别被 high 吓到。
- 点赞
- 收藏
- 关注作者
评论(0)