从零跑通 excel-formula-explainer:批量揪出 Excel 里的循环引用与死引用
从零跑通 excel-formula-explainer:批量揪出 Excel 里的循环引用与死引用
项目地址:https://gitcode.com/agent-best-practices/excel-formula-explainer 本文是一篇上手教程:从零装环境、跑出第一份公式体检报告,到安全地做修复写回,"教程 + 原理"一起讲透。 一句话定位:它是 Excel 公式的静态分析器 + 有限度的自动修复器。
读完这篇你能做到什么
装环境 → 分析整个工作簿 → 读懂四类问题报告 → 生成修复计划
→ 写修复副本 → 备份后写回原文件 → 模板自检
适用场景很明确:别人丢给你一个几十个 Sheet 的 .xlsx,里面公式层层嵌套,你既不知道哪些是错的,也不敢随手改。这个工具先用静态检测把问题列出来,再只对能规则化的项动手。
前置条件清单
| 项 | 要求 | 说明 |
|---|---|---|
| Python | 3.x | 运行 scripts/ 下脚本 |
| openpyxl | 3.1.5(requirements.txt 锁版) | 读写 .xlsx |
| 待分析文件 | 必须是 .xlsx |
脚本会校验后缀,.xls 不支持 |
第 1 步:拿项目,认清谁干什么
git clone https://gitcode.com/agent-best-practices/excel-formula-explainer.git
cd excel-formula-explainer
这个仓库的目录结构本身就是一套推荐的技能布局规范,值得单独看:
excel-formula-explainer/
├── SKILL.md # 核心指令:frontmatter + 八段式运行规则
├── README.md
├── references/
│ ├── runbook.md # 执行走查表(逐步命令照抄 + 排查速查)
│ └── analysis-guide.md # 检测项判定要素 + 报告输出模板
├── scripts/
│ ├── validate_template.py # 模板自检脚本
│ ├── analyze_excel.py # CLI 入口(分析)
│ ├── fix_excel.py # CLI 入口(修复 + 写回)
│ ├── excel_reader.py # 读取 xlsx,提取公式与缓存值
│ ├── graph.py # 依赖图、循环/死引用/跨表/硬编码检测
│ ├── analyzer.py # 编排检测分析
│ └── report.py # 格式化报告输出
├── evolution/constraints.md # 演进约束(叙述式)
├── evolutions.json # 演进条目(结构化)
└── requirements.txt # 依赖锁定
两个设计点值得抄:
- 分析逻辑被拆成"读取 → 建图检测 → 编排 → 出报告"四层(
excel_reader/graph/analyzer/report),每个文件单一职责。而不是把所有逻辑塞进一个 500 行的脚本。 - CLI 与逻辑分离:
analyze_excel.py只做参数解析和打印,真正的分析在analyzer.py里——所以你能把检测能力当库用,而不只是当命令用。
第 2 步:安装依赖
pip install -r requirements.txt
requirements.txt 里锁的是 openpyxl==3.1.5,并附了两条环境提醒:
- 优先使用标准库;确需第三方包时在该文件里固定版本
- Windows 下注意编码:脚本读取建议用
utf-8-sig以容忍 BOM
第 3 步:跑出第一份报告
# 分析整个文件
python scripts/analyze_excel.py 销售报表.xlsx
# 只分析前 5 个公式(试跑用,报告短、跑得快)
python scripts/analyze_excel.py 销售报表.xlsx -n 5
# 报告写入文件
python scripts/analyze_excel.py 销售报表.xlsx -o report.txt
参数就三个:位置参数是文件、-n/--max 限制公式数量、-o/--output 输出到文件。
CLI 有两条前置校验,失败即退出(退出码 1):
- 文件不存在 →
错误: 文件不存在: xxx - 后缀不是
.xlsx→错误: 只支持 .xlsx 文件
建议第一次用
-n 5试跑。 大工作簿全量分析的报告会很长,先用 5 条公式确认输出格式符合预期,再放开跑全量。
第 4 步:读懂四类检测项
报告里的问题分四类,每类带严重度:
| 检测项 | 说明 | 严重度 |
|---|---|---|
| 循环引用 | 通过 DFS 检测公式间的循环依赖 | high |
| 死引用 | 引用空单元格 / 不存在的单元格 / 常量值 | high / medium / low |
| 跨表引用 | 引用其他工作表的单元格 | low |
| 硬编码值 | 公式里直接写数值常量,而非引用参数单元格 | low |
为什么严重度这么分?
- 循环引用是 high:A1 引用 B1、B1 又引用 A1,Excel 自己都算不出结果(会报 0 或警告),这是硬错误。
- 死引用分三级,因为它有三种性质完全不同的成因:
- 引用了不存在的单元格(high)——多半是删行删列后公式没跟着改
- 引用了空单元格(medium)——公式结构没问题,只是数据还没填;这类是可以自动修的
- 引用了常量值单元格(low)——公式引的是个写死的数,不是计算出来的
- 跨表和硬编码是 low:它们不是错误,是设计味道(code smell)。跨表引用会让表结构脆弱(改表名就断),硬编码值会让参数不可调。
第 5 步:修复——三种模式,先看不动手
fix_excel.py 有三种用法,安全性依次递减,务必按顺序用:
# ① 只看修复计划(dry-run,不写任何文件)——永远从这一步开始
python scripts/fix_excel.py 销售报表.xlsx
# ② 写到修复副本,原文件完全不动(推荐)
python scripts/fix_excel.py 销售报表.xlsx -o 销售报表_fixed.xlsx
# ③ 写回原文件(自动生成 .bak 备份)
python scripts/fix_excel.py 销售报表.xlsx --apply
# 也可以只评估前 5 个公式的修复项
python scripts/fix_excel.py 销售报表.xlsx -n 5
关键:它只自动修两类,其余一律交人工。
| 规则 | 处理方式 |
|---|---|
| 空单元格引用(medium) | 自动:向目标空单元格写入 0 |
| 引用超出实际行数(high) | 自动:收缩到同列最后一个有效行 |
| 循环引用、换引用目标、硬编码提取、跨表/常量引用 | 需人工确认:只出建议,征询意图后再改 |
这个边界划得很克制,也划得对:能确定的("这里引用了个空单元格")自动修;需要判断意图的("循环引用该断开哪一条"、"这个硬编码该提取成哪个参数")绝不猜。
写回的两道保险:-o 输出副本(原文件不动)、--apply 自动备份 .bak。建议永远先走 ②,确认后再考虑 ③。
第 6 步:模板自检(如果你要基于它做二次开发)
python scripts/validate_template.py # 成品体检:任一 FAIL 退出码 1
python scripts/validate_template.py --allow-placeholders # 模板开发期复核(占位符/命名仅告警)
python scripts/validate_template.py --root DIR # 指定技能根目录
它检查的是技能本身的规范性(占位符是否清理干净、命名是否合规、版本号是否同步)。退出码 1 表示有 FAIL,可以直接接进 CI。
第 7 步:检测原理拆解(想改代码的必读)
这部分是我最建议看的地方——它的实现非常"教科书",可以当作"如何给 Excel 公式做静态分析"的参考实现。
7.1 先解决"怎么从公式里抠出引用"
用两条正则分别匹配区域引用和单元格引用:
_CELL_REF = r"(?:'[^']*'|[A-Za-z_][\w]*(?=\!)\!)?\$?[A-Z]{1,3}\$?\d+"
_RANGE_REF = r"(?:'[^']*'|[A-Za-z_][\w]*!)?\$?[A-Z]{1,3}\$?\d+:\$?[A-Z]{1,3}\$?\d+"
这两条正则处理了三个容易漏的细节:
- 带引号的工作表名:
'销售 报表'!A1(表名含空格必须加引号) - 绝对引用:
$A$1、A$1里的$要被吃掉 - 列号可以到三位:
{1,3}覆盖到XFD(Excel 最大列)
抽取时的顺序也很讲究:
refs = set()
for m in _RANGE_REF.finditer(formula):
refs.update(_expand_range(m.group(0))) # ① 先把区域展开成一个个单元格
cleaned = _RANGE_REF.sub("", formula) # ② 再把区域从公式里抹掉
for m in _CELL_REF.finditer(cleaned): # ③ 剩下的才是单格引用
refs.add(m.group(0))
为什么必须先处理区域、再处理单格? 因为 A1:B2 里本身就包含 A1 这样的子串,如果先扫单格,会把区域的两个端点当成两个孤立引用,而漏掉中间所有单元格。先展开、再抹除、最后扫残量,是一个很干净的顺序。
7.2 依赖图:同时建正向和反向
self.graph[cell_key] = set(refs) # 正向:本格 → 我引用了谁
self.reverse.setdefault(ref, set()).add(cell_key) # 反向:被引用格 → 谁引用了我
节点用 工作表名!单元格 做键(如 Sheet1!A1),这样跨表也不会撞名。
正向图用来遍历依赖、找环;反向图可以回答"这个单元格一旦改动,谁会受影响"——这是后来做影响面分析的基础。
另外单独记录 cross_refs:只保存"引用了别的表"这件事,用于出跨表报告。
7.3 找循环引用:DFS 三色标记
WHITE, GRAY, BLACK = 0, 1, 2 # 未访问 / 在栈上 / 已完成
def dfs(node):
color[node] = GRAY
path.append(node)
for neighbor in graph.get(node, set()):
if color.get(nk) == GRAY: # 撞到栈上的节点 → 找到环
cycles.append(path[path.index(nk):].copy())
elif color.get(nk) == WHITE:
dfs(nk)
path.pop()
color[node] = BLACK
这是有向图找环的标准做法:灰色代表"当前 DFS 路径上",一旦遍历到灰色节点,说明路走回来了,path 从该节点到当前节点的这一段就是环。
比起"反复遍历直到不再变化"的朴素办法,三色 DFS 一次遍历就能找出所有环,复杂度是 O(V+E)。
7.4 死引用的三级判定
对每个引用去解析它指向的单元格,分三种情况:
info = self._resolve(f"{own}!{ref}")
if info is None: # 单元格根本不存在
→ high "引用了不存在的单元格"
elif info.value is None and info.formula is None: # 存在,但既无值也无公式(空)
→ medium "引用了空单元格"
elif info.formula is None and info.value is not None: # 存在,是常量
→ low "引用了常量值单元格(值:...)"
「存在但为空」和「不存在」必须分开——前者补个值就好,后者说明表结构变了。判定精度就体现在这里。
7.5 硬编码检测:为什么排除 0 和 1
num_pat = re.compile(r'(?<=[+\-*/^<>=,(])\s*\d+(?:\.\d+)?')
for m in num_pat.finditer(cell.formula):
val = m.group(0).strip()
if val in ('0', '1'): # ← 关键
continue
两个设计要点:
- 正则是后顾断言(
(?<=...)):只匹配出现在运算符或括号逗号之后的数字,所以SUM(A1:A10)里的10不会被误报成硬编码(它在括号里、且前面是A)。这是在主动压制误报。 - 排除
0和1:这两个数字在公式里太常见了,IF(A1>0,...)、A1*1、ROW()-1到处都是。如果不排除,报告会被噪声淹没,用户直接不看这份报告了。
这条经验很值得迁移到任何"规则检测"工具:一个检测项的价值 = 真阳性 − 误报成本。宁可少报,也别让用户因为噪声而放弃工具。
7.6 一次分析的全流程
def get_all_issues(self):
result = []
result.extend(self._find_cycles_as_issues()) # 循环引用
result.extend(self.find_dead_refs()) # 死引用(三级)
result.extend(self.find_cross_sheet_refs()) # 跨表
result.extend(self.find_hardcoded()) # 硬编码
return result
analyzer.py 负责编排(读文件 → 建图 → 汇总 → 交给报告层),report.py 只负责格式化。检测与呈现彻底分离——所以你可以把报告输出换成 JSON、HTML、Markdown,而完全不用碰检测逻辑。
第 8 步:数据是怎么读的(双模式读取)
从 requirements.txt 的说明可以看到关键设计:excel_reader.py 用 openpyxl 读两遍——
- 公式模式:读出公式字符串(
=SUM(A1:A10)),用于静态分析 data_only缓存值模式:读出 Excel 上次保存时缓存的计算结果,用于判断"这个单元格有没有值"
# 概念示意
wb_formula = load_workbook(path, data_only=False) # 拿公式
wb_values = load_workbook(path, data_only=True) # 拿缓存值
这里有个必须知道的坑:data_only=True 拿到的是打开并保存时由 Excel 计算后写入缓存的值。如果这个 .xlsx 是程序生成的、或者从未被 Excel 打开保存过,缓存值可能全是 None。
后果是:所有引用常量值的单元格会被误判成"引用了空单元格"。
规避办法:分析前先用 Excel(或 WPS)打开该文件并保存一次,让缓存值落地,再跑分析。
附录 A:命令速查
| 目的 | 命令 |
|---|---|
| 分析整个文件 | python scripts/analyze_excel.py 文件.xlsx |
| 只分析前 N 个公式 | python scripts/analyze_excel.py 文件.xlsx -n 5 |
| 报告写入文件 | python scripts/analyze_excel.py 文件.xlsx -o report.txt |
| 只看修复计划 | python scripts/fix_excel.py 文件.xlsx |
| 写修复副本(推荐) | python scripts/fix_excel.py 文件.xlsx -o 文件_fixed.xlsx |
| 写回原文件(自动 .bak) | python scripts/fix_excel.py 文件.xlsx --apply |
| 技能模板自检 | python scripts/validate_template.py |
附录 B:排错速查
| 现象 | 原因 | 处理 |
|---|---|---|
错误: 只支持 .xlsx 文件 |
传了 .xls/.xlsm |
转存为 .xlsx |
错误: 文件不存在 |
路径错(含中文路径要加引号) | ".../销售报表.xlsx" |
| 报告里全是"引用了空单元格" | 文件从未被 Excel 打开保存,data_only 缓存值为空 |
先用 Excel 打开保存一次再分析 |
| Windows 下读文件报编码错 | 文件带 BOM | 用 utf-8-sig 读取(仓库已在说明中提示) |
| 报告太长看不过来 | 全量分析大工作簿 | 用 -n 5 先试跑 |
| 修复后原文件被改了 | 用了 --apply |
从同名 .bak 恢复;下次先用 -o 出副本 |
常见问题(FAQ)
Q:它会不会自动改坏我的表? 默认不会。fix_excel.py 不带参数时是 dry-run,只打印修复计划、不写文件。只有 -o(写副本)或 --apply(写回并备份)才会落盘,而且自动修的范围被严格限制在两类可规则化的项上。
Q:循环引用它会自动帮我断开吗? 不会。循环引用只出建议——因为"该断开哪一条边"取决于业务意图,工具猜不出来。这也是它比"一键修复"的工具更可靠的地方。
Q:跨表引用和硬编码为什么也算问题? 它们是 low 级"设计味道",不是错误。跨表引用会让表结构脆(改表名就断链),硬编码会让参数不可调。知道有这些点,你可以自己决定要不要重构。
Q:能分析 .xls 或 .xlsm 吗? 不能。CLI 明确校验后缀,只支持 .xlsx。
Q:能对接 CI 吗? 可以。validate_template.py 任一 FAIL 返回退出码 1,analyze_excel.py / fix_excel.py 的错误路径也用非零退出码,适合接进流水线做卡口。
结语
excel-formula-explainer 有两个地方特别值得学:
一是"克制":它明明能自动改很多东西,却只自动修两类规则明确的问题,其余一律出建议交人工。能自动化的边界画在哪里,比自动化能力本身更能体现工程成熟度。
二是"可迁移的实现":三色 DFS 找环、区域先展开再抹除的正则顺序、硬编码检测排除 0/1 压误报、"存在但为空"与"不存在"分开判定——这些手法拿来写任何静态分析器都能直接用。
如果你手上有一堆交接过来的 Excel,建议直接第 3 步跑起来;如果你在写自己的检查工具,建议精读 scripts/graph.py —— 200 行,信息密度很高。
参考资料:项目 README.md、requirements.txt、scripts/graph.py(200 行)、scripts/analyze_excel.py;文中检测规则、严重度分级与修复边界均取自项目内明确写明的实现与说明。
- 点赞
- 收藏
- 关注作者
评论(0)