处理

数据 社区
  • 作者仓库星标 0
  • 许可证 Proprietary. LICENSE.txt has complete terms
  • 作者更新于 2026年8月21日 17:10

技能解读

xlsx 是给生成 / 修改 Excel 文件的指南——它把「行业级 financial model」的所有不成文规则成文:字体一致、零公式错误、保留既有模板、严格的颜色编码、数字格式标准、公式构造规则。核心戒律:每份交付的 Excel 必须零 #REF! / #DIV/0! / #VALUE! / #N/A / #NAME?。

通用要求

  • 专业字体:除非用户另说,全部用一致的专业字体(Arial / Times New Roman)
  • 零公式错误:任何 #REF! 之类直接判废
  • 保留既有模板:修改文件时严格按既有 format / style / convention,不要把"标准化"强加到已有模式上——既有 template 永远 override 这份指南

Financial model 的颜色编码(行业标准)

  • 蓝色文字 (RGB 0,0,255):硬编码 input、用户会改的情景数字
  • 黑色文字 (RGB 0,0,0):所有公式与计算
  • 绿色文字 (RGB 0,128,0):本工作簿内跨 sheet 的链接
  • 红色文字 (RGB 255,0,0):外部文件链接
  • 黄色背景 (RGB 255,255,0):关键假设 / 需要更新的单元格

数字格式标准

  • 年份:当文本("2024" 而非 "2,024")
  • 货币:$#,##0,header 必须标单位("Revenue ($mm)")
  • 零:用数字格式让所有 0 显示成 "-",含百分比($#,##0;($#,##0);-)
  • 百分比:默认 0.0%(一位小数)
  • 倍数:0.0x(valuation multiples 如 EV/EBITDA、P/E)
  • 负数:用括号 (123),不用 -123

公式构造规则

  • 假设单元格独立:所有 assumption(增长率、margin、倍数)放独立 cell;公式里用引用而非硬编码——=B5*(1+$B$6) 优于 =B5*1.05
  • 错误防护:核 cell 引用、查 range 的 off-by-one、所有 projection 期间公式一致、用边缘值(0 / 负数)测、查无意中的循环引用
  • 硬编码必须文档化:紧邻 cell 注一行 "Source: [System/Document], [Date], [Specific Reference], [URL if applicable]"

工作流要点

读取 + 分析数据先做;改文件时先看、再仿、再改——不要替别人重新 design。重算公式前确认 calculation mode(automatic / manual)。

适合的场景

  • 生成 / 修改 financial model(DCF / LBO / 三表)
  • 维护既有 Excel 模板,需严格按规范填数
  • 多人协作的财务交付物

不适合

  • 表格只是数据展示(无公式 / 无模型):CSV / 简单 xlsx 写出即可
  • 用 Google Sheets / Numbers:本指南面向 Excel 公式语义

配套

pdf / make-pdf(导出固化为不可编辑的 PDF)、pptx(财务模型搬上 deck)、theme-factory(演示视觉风格)、landing-report(财务输出附在发布说明)。

流狐档案 运行与权限由流狐静态检查推断,不代表作者声明或实际测试结果。
领域
数据
作者声明的智能体
未找到明确声明;不据此推断已兼容或已测试
静态检查
94 / 100 · 启发式扫描,不代表运行安全
运行环境证据
未执行;当前没有隔离运行测试证据
作者 / 版本 / 许可证
@anthropics · Proprietary. LICENSE.txt has complete terms
上下文用量评级
低消耗
接入复杂程度
即装即用
是否需要外部接口密钥
不需要
兼容的系统
未声明(默认跨平台)
运行环境要求
Python
检测到的文件与系统行为
  • 只读
  • 允许写入 / 修改
网络行为
仅限本地
安装命令数
无(仅作为资料)

需要注意: 未声明工具权限范围;实际权限由运行环境决定。

作者说明文件(原文) SKILL.md

# XLSX creation, editing, and analysis

| Task | Approach |
|---|---|
| **Create** or **edit** with formulas/formatting | `openpyxl` — see gotchas below |
| **Bulk data** in or out | `pandas` (`read_excel`, `to_excel`) |
| **Quick look** at a sheet | `markitdown file.xlsx` — `## SheetName` per sheet; reads `.xlsm` too. No cell coordinates, so don't plan edits from it |
| **Read** a model (formulas *and* values) | two `load_workbook` passes — see gotchas |

> `openpyxl`, `pandas`, and `markitdown` are preinstalled — do not run `pip install` first; write the script and import directly. Only if an import fails (or the `markitdown` command is missing): `pip install` the missing package.

> Script paths below are relative to this skill's directory.

## Requirements for every output

- **Professional font** (Arial, Times New Roman) throughout, unless the user says otherwise.
- **Zero formula errors.** Never ship while `recalc.py` reports `errors_found`. If you think an error predates you, prove it: load the *original* with `data_only=True` and look at that cell. An error you introduced looks exactly like one you inherited.
- **Use formulas, never hardcoded results.** Write `sheet['B10'] = '=SUM(B2:B9)'`, not the Python-computed total. The sheet must recalculate when its inputs change.
- **Follow the user's spec literally.** Exact tab names, exact column headers, and the formula they spelled out. A redesign that computes something else fails, however elegant.
- **Document every assumption and hardcoded number** where the reader will see it — a cell comment, or an adjacent cell at a table's end. Cite a real source when one exists (`Source: Company 10-K, FY2024, Page 45, Revenue Note, [SEC EDGAR URL]`); when the number came from the user, say so plainly.
- **A workbook *you create* for someone to fill in** needs a short legend naming which cells to edit, and one example row of realistic values showing the expected format. Never add such a row to a file you were asked to edit.
- **Editing an existing file: match its conventions exactly.** They override every guideline here. Find its designated input cells first — a distinct font color, fill, or shading marks them — write only there, and leave every existing formula untouched.

## Recalculate (mandatory whenever the file contains formulas)

openpyxl writes formulas as strings with **no cached values**. Until you recalculate, every
formula cell reads back as `None` to anything reading cached values — `pandas`,
`load_workbook(data_only=True)`, and most previewers.

```bash
python scripts/recalc.py output.xlsx [timeout_seconds]   # default 30
```

LibreOffice computes every formula, the file is **rewritten in place**, and you get JSON:
`status` (`success` | `errors_found`), `total_formulas`, `total_errors`, and an
`error_summary` naming up to 100 cells per error type (`locations_truncated` says how many it
withheld — trust `total_errors`, not the length of the list). Fix what it names and run it
again. **JSON with an `error` key instead of a `status` means nothing was recalculated**, and
only that case exits non-zero — `errors_found` exits 0, so never treat a clean exit as a clean
workbook.

**A green recalc proves your formulas *evaluate*, not that they are *right*.** An off-by-one
range or a reference to the wrong row yields a clean, error-free file with wrong numbers.
Write 2–3 formulas first and check they pull the values you expect, before building out a grid.

**A workbook that links to another file loses those links** if you re-save it with openpyxl and
then recalculate. Such a formula reads `='[1]Returns Analysis'!$B$2` — the `[1]` is an index
into the workbook's external-reference list, naming a *separate file on disk*, not a sheet.
That file is rarely present here, so the cell's cached value is the only thing holding its
data. openpyxl strips that value on save; LibreOffice then has to resolve the reference for
real, fails, writes `#NAME?`, and deletes every link. `recalc.py` refuses to run in that state
— copy those cells' values out of the original before you save over them (`--force` overrides,
and accepts the loss).

## Choosing formulas that survive verification

LibreOffice implements fewer functions than Excel, and one it cannot evaluate becomes a
literal `#NAME?` baked into the file you deliver.

- **Prefer Excel-2007-era functions** — `SUMIFS`, `INDEX`, `MATCH`, `IFERROR`, `SUMPRODUCT` — which need no prefix.
- **Six post-2007 functions work, but only with an `_xlfn.` prefix**, because openpyxl writes your formula into the XML verbatim and Excel stores post-2007 names prefixed (its UI hides the prefix): `_xlfn.TEXTJOIN`, `_xlfn.CONCAT`, `_xlfn.IFS`, `_xlfn.SWITCH`, `_xlfn.MAXIFS`, `_xlfn.MINIFS`. Written bare, each yields `#NAME?`.
- **Never use `XLOOKUP`, `XMATCH`, `SORT`, `FILTER`, `UNIQUE`, or `SEQUENCE`.** The runtime's LibreOffice cannot evaluate them under *any* prefix. Newer builds do evaluate them, but they are spilling array functions and an openpyxl-written file has no spill metadata, so only the top-left cell of the range gets a value — and `recalc.py` reports `total_errors: 0` on the truncated result. Use `INDEX`/`MATCH` for lookups, and sort, filter, and de-duplicate in Python before writing the cells.
- A formula LibreOffice could not parse is written back **lowercased** — a quick tell beside a `#NAME?`.

## openpyxl gotchas

- **Reading a model takes two loads.** `data_only=True` yields cached values with the formulas gone; the default yields formula strings with no values. One pass cannot give you both.
- **`data_only=True` is destructive if you save.** That workbook has no formulas left, so saving replaces every one with a literal — permanently.
- **`data_only=True` on a file openpyxl just wrote returns `None` everywhere** — run `recalc.py` first. (A formula whose result is `""` also reads back as `None`.)
- **Merged cells: write the top-left anchor only.** Every other cell in the range is a `MergedCell` whose `.value` is read-only.
- **`.xlsm` loses its macros unless you pass `keep_vba=True`** to `load_workbook`.
- **A sheet name containing a space must be quoted** in a cross-sheet reference: `='Assumptions Inputs'!$B$5`. Unquoted, it evaluates to `#VALUE!`.

## Financial models

Unless the user says otherwise, or the existing file already does something else.

**Color:** blue text (`0,0,255`) for hardcoded inputs and scenario levers · black for formulas ·
green (`0,128,0`) for links to another sheet · red (`255,0,0`) for links to another file ·
yellow fill (`255,255,0`) for key assumptions and cells the user should fill in.

**Numbers:** currency `$#,##0`, with the unit named in the header (`Revenue ($mm)`) · zeros
render as `-`, including in percentages (`$#,##0;($#,##0);-`) · negatives in parentheses ·
percentages `0.0%`, **stored as fractions** (`0.15` renders `15.0%`; storing `15` renders
`1500.0%`) · valuation multiples `0.0x` · years as text (`"2024"`, never `2,024`).

**Structure:** every assumption in its own labeled cell, referenced by the formulas that use it
(`=B5*(1+$B$6)`, never `=B5*1.05`) · formulas consistent across every projection period, since a
lone edited cell mid-row is the commonest silent error · guard denominators that can be zero.

## Dependencies

`openpyxl`, `pandas`, `markitdown` (pip, preinstalled — install only if an import fails or the command is missing) · LibreOffice (`soffice`, auto-configured for sandboxed environments via `scripts/office/soffice.py`)

讨论

基于 GitHub Discussions。登录 GitHub 即可参与讨论、点赞、订阅更新。