从零跑通 excel-formula-explainer:批量揪出 Excel 里的循环引用与死引用

举报
yd_274732647 发表于 2026/11/03 10:36:16 2026/11/03
【摘要】 excel-formula-explainer 是一个 Excel 公式静态分析工具:读入 .xlsx,批量提取所有公式,检测循环引用、死引用、跨表引用与硬编码值,输出纠错优化报告,并对可规则化的项支持备份后写回。本文是从零上手教程:装环境、跑出第一份报告、读懂四类检测项、三种安全修复模式,并拆解底层实现(三色 DFS 找环、引用正则的展开顺序、为什么排除 0 和 1)。

从零跑通 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          # 依赖锁定

两个设计点值得抄:

  1. 分析逻辑被拆成"读取 → 建图检测 → 编排 → 出报告"四层(excel_reader / graph / analyzer / report),每个文件单一职责。而不是把所有逻辑塞进一个 500 行的脚本。
  2. 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;文中检测规则、严重度分级与修复边界均取自项目内明确写明的实现与说明。

【声明】本内容来自华为云开发者社区博主,不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息,否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0)

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。