XLSX 创建、编辑与分析
| 任务 | 方法 |
|---|---|
| 创建或编辑带公式/格式 | openpyxl — 请参见下方注意事项 |
| 批量数据导入/导出 | pandas (read_excel, to_excel) |
| 快速查看工作表 | markitdown file.xlsx — 每个工作表显示 ## SheetName;也支持 .xlsm。不提供单元格坐标,因此不要据此规划编辑 |
| 读取模型(公式和值) | 两次 load_workbook 传递 — 请参见下方注意事项 |
openpyxl、pandas和markitdown已预装 — 无需运行pip install;直接编写脚本并导入。仅在导入失败(或markitdown命令缺失)时:pip install缺失的包。
以下脚本路径相对于此技能目录。
每个输出文件的要求
- 专业字体(Arial、Times New Roman)贯穿始终,除非用户另有说明。
- 零公式错误。 切勿在
recalc.py报告errors_found时交付。如果你认为错误在你之前就已存在,请证明:以data_only=True加载原始文件并查看该单元格。你引入的错误看起来与继承的错误完全一样。 - 使用公式,绝不使用硬编码结果。 写入
sheet['B10'] = '=SUM(B2:B9)',而不是 Python 计算的总和。工作表必须在其输入更改时重新计算。 - 严格遵循用户的规格说明。 精确的工作表名称、精确的列标题以及他们指定的公式。重新设计出计算其他内容的方案是失败的,无论多么优雅。
- 在读者能看到的地方记录每一个假设和硬编码数字 — 单元格批注,或表格末尾的相邻单元格。当有真实来源时,引用该来源(
来源:公司 10-K,2024 财年,第 45 页,收入注释,[SEC EDGAR URL]);当数字来自用户时,直接说明。 - 你创建的供他人填写的工作簿需要有一个简短的图例,说明要编辑哪些单元格,并包含一行示例数据,展示预期的格式。永远不要将这样的行添加到要求你编辑的文件中。
- 编辑现有文件:完全匹配其约定。 它们会覆盖此处的每条指南。首先找到其指定的输入单元格 — 通过不同的字体颜色、填充或阴影来识别 — 仅在这些位置写入,并保持所有现有公式不变。
重新计算(只要文件包含公式就必须执行)
openpyxl 将公式作为无缓存值的字符串写入。在重新计算之前,每个公式单元格对于任何读取缓存值的工具(如 pandas、load_workbook(data_only=True) 以及大多数预览器)都会返回 None。
python scripts/recalc.py output.xlsx [timeout_seconds] # 默认 30
LibreOffice 计算每个公式,文件被就地重写,你会得到 JSON:status(success | errors_found)、total_formulas、total_errors,以及一个 error_summary,按错误类型列出最多 100 个单元格(locations_truncated 表示隐藏了多少个 — 请信任 total_errors,而不是列表长度)。修复它指出的问题并再次运行。JSON 中包含 error 键而不是 status 意味着未进行任何重新计算,并且只有这种情况会以非零退出;errors_found 会以 0 退出,因此切勿将正常的退出视为工作簿干净。
成功的重新计算证明你的公式能求值,而不是正确。 范围偏移一位或引用错误行会导致文件无错误但结果错误。在构建整个网格之前,先编写 2-3 个公式并检查它们是否提取了预期的值。
链接到其他文件的工作簿,如果你用 openpyxl 重新保存然后重新计算,这些链接会丢失。这样的公式读取为 ='[1]Returns Analysis'!$B$2 — [1] 是工作簿外部引用列表中的索引,命名的是磁盘上的另一个文件,而不是工作表。该文件在这里很少存在,因此该单元格的缓存值是唯一持有其数据的东西。openpyxl 在保存时会剥离该值;然后 LibreOffice 必须真正解析引用,失败,写入 #NAME?,并删除所有链接。recalc.py 会拒绝在此状态下运行 — 在覆盖前从原始文件中复制这些单元格的值(--force 会覆盖并接受损失)。
选择能通过验证的公式
LibreOffice 实现的函数比 Excel 少,它无法求值的公式会变成字面 #NAME? 并固化在你交付的文件中。
- 优先使用 Excel-2007 时代的函数 —
SUMIFS、INDEX、MATCH、IFERROR、SUMPRODUCT— 这些函数不需要任何前缀。 - 六个 2007 年后的函数可以工作,但需要
_xlfn.前缀,因为 openpyxl 按原样将你的公式写入 XML,而 Excel 存储 2007 年后的名称时带有前缀(其 UI 隐藏了前缀):_xlfn.TEXTJOIN、_xlfn.CONCAT、_xlfn.IFS、_xlfn.SWITCH、_xlfn.MAXIFS、_xlfn.MINIFS。裸写每个都会产生#NAME?。 - 永远不要使用
XLOOKUP、XMATCH、SORT、FILTER、UNIQUE或SEQUENCE。 运行时的 LibreOffice 在任何前缀下都无法求值它们。较新版本确实可以求值,但它们是溢出数组函数,而 openpyxl 写入的文件没有溢出元数据,因此只有区域左上角的单元格会获得值 — 并且recalc.py在截断的结果上报告total_errors: 0。使用INDEX/MATCH进行查找,并在写入单元格之前在 Python 中排序、过滤和去重。 - LibreOffice 无法解析的公式会被转为小写写回 — 这是
#NAME?旁边的快速识别标志。
openpyxl 注意事项
- 读取模型需要两次加载。
data_only=True返回缓存值但公式丢失;默认模式返回公式字符串但无值。一次传递无法同时给出两者。 - 保存时
data_only=True具有破坏性。 该工作簿不再有公式,因此保存会将每个公式永久替换为字面值。 - 在 openpyxl 刚写入的文件上使用
data_only=True会到处返回None— 请先运行recalc.py。(结果为""的公式也会返回None。) - 合并单元格:仅写入左上角锚点。 区域内的其他每个单元格都是
MergedCell,其.value是只读的。 - 除非向
load_workbook传递keep_vba=True,否则.xlsm会丢失其宏。 - 包含空格的工作表名称在跨工作表引用中必须加引号:
='Assumptions Inputs'!$B$5。不加引号会求值为#VALUE!。
财务模型
除非用户另有说明,或者现有文件已采用其他方式。
颜色: 蓝色文本 (0,0,255) 用于硬编码输入和情景杠杆 · 黑色用于公式 · 绿色 (0,128,0) 用于对其他工作表的链接 · 红色 (255,0,0) 用于对其他文件的链接 · 黄色填充 (255,255,0) 用于关键假设和用户应填写的单元格。
数字: 货币 $#,##0,并在标题中注明单位(Revenue ($mm)) · 零显示为 -,包括百分比($#,##0;($#,##0);-) · 负数用括号 · 百分比 0.0%,以分数形式存储(0.15 显示为 15.0%;存储 15 会显示为 1500.0%) · 估值倍数 0.0x · 年份为文本("2024",绝不使用 2,024)。
结构: 每个假设都在其自己的标签单元格中,由使用它的公式引用(=B5*(1+$B$6),绝不使用 =B5*1.05) · 每个预测期间的公式保持一致,因为行中单个被编辑的单元格是最常见的静默错误 · 保护可能为零的分母。
依赖项
openpyxl、pandas、markitdown(pip,已预装 — 仅在导入失败或命令缺失时安装) · LibreOffice(soffice,已通过 scripts/office/soffice.py 自动配置为沙盒环境)