Excel 表格处理专家

技能文档

---
name: excel01
title: Excel 表格处理专家
description: 以表格文件为主要输入或输出时的 Excel 处理技能：打开、读取、编辑、修复现有 .xlsx/.xlsm/.csv/.tsv 文件（加列、算公式、格式化、图表、清洗乱数据）；从零或其他数据源创建新表格；表格格式互转；把混乱的表格数据整理为规范表格。用户提到表格文件名或路径并要求处理或产出表格时触发。主要交付物是 Word 文档、HTML 报告、独立 Python 脚本或数据库管道时不适用。
category: 办公效率
---

# Excel 创建、编辑与分析

## 输出要求

### 所有 Excel 文件

- **专业字体**：统一使用专业字体（如 Arial、Times New Roman），用户另有要求除外
- **零公式错误**：交付的 Excel 模型必须没有任何公式错误（#REF!、#DIV/0!、#VALUE!、#N/A、#NAME?）
- **保留既有模板**：修改已有模板文件时，先研究并严格遵循现有格式、样式与惯例；模板惯例始终优先于本指南

### 财务模型

**颜色编码标准**（用户或既有模板另有规定除外）：

- **蓝色文字 (0,0,255)**：硬编码输入和用户会改动做情景分析的数字
- **黑色文字 (0,0,0)**：所有公式和计算
- **绿色文字 (0,128,0)**：同一工作簿内跨工作表引用
- **红色文字 (255,0,0)**：指向其他文件的外部链接
- **黄色背景 (255,255,0)**：需要关注的关键假设或待更新单元格

**数字格式标准**：

- **年份**：格式化为文本（如 "2024" 而非 "2,024"）
- **货币**：用 `$#,##0` 格式；表头必须注明单位（如「收入（百万美元）」）
- **零值**：用数字格式把所有零显示为 `-`，百分比同理（如 `$#,##0;($#,##0);-`）
- **百分比**：默认 0.0%（一位小数）
- **倍数**：估值倍数（EV/EBITDA、P/E）格式化为 0.0x
- **负数**：用括号 (123)，不用负号 -123

**公式构建规则**：

- 所有假设（增长率、利润率、倍数等）放入独立的假设单元格
- 公式中用单元格引用而非硬编码数值，例如用 `=B5*(1+$B$6)` 而非 `=B5*1.05`
- 防错检查：核对单元格引用、范围差一错误、预测期公式一致性、零值/负数边界、意外循环引用
- 硬编码值的注释要求：在单元格批注或旁边注明「来源：[系统/文档]，[日期]，[具体出处]，[URL]」

---

# 概览

用户可能要求创建、编辑或分析 .xlsx 文件内容。不同任务对应不同的工具与工作流。

## 重要前提

**公式重算需要 LibreOffice**：假定环境已安装 LibreOffice，用 `scripts/recalc.py` 脚本重算公式值。脚本首次运行时自动配置 LibreOffice，包括限制 Unix 套接字的沙箱环境（由 `scripts/office/soffice.py` 处理）。

## CSV 编码规则

**关键：凡是可能在 Excel 中打开的 CSV 文件，一律用 `utf-8-sig`（带 BOM 的 UTF-8）写出。** Excel 通过文件开头的 BOM 识别编码；没有 BOM 时 Excel 会退回系统区域编码（中文 Windows 为 GBK），导致中文乱码。

### ❌ 错误——缺少编码或 BOM

```python
df.to_csv('output.csv')                        # 用系统区域编码——跨平台不可靠
df.to_csv('output.csv', encoding='utf-8')      # 无 BOM——Excel 按 GBK 打开，中文乱码
```

### ✅ 正确——带 BOM 的 UTF-8（供 Excel 使用）

```python
df.to_csv('output.csv', encoding='utf-8-sig')  # 含 BOM——Excel 正确识别 UTF-8
```

### macOS 兼容性提示

`utf-8-sig` 在 **Excel for Mac** 和 **macOS 上的 LibreOffice Calc** 下正常，但与其他 macOS 工具有已知问题：

- **macOS Numbers**：旧版本可能把 BOM 显示为首单元格可见的 `` 前缀。输出给 Numbers 用时改用纯 `utf-8`，并指导用户通过「文件 > 打开」手动选择编码。
- **macOS 命令行工具**（`cat`、`awk`、`grep`、`sort` 等）：3 字节 BOM（`\xef\xbb\xbf`）会出现在首行开头，破坏针对第 1 列的模式匹配和分列。
- **Python `read_csv` 用 `encoding='utf-8'`**：BOM 会以 `\ufeff` 出现在首列名前，导致列名不匹配。读回时始终用 `encoding='utf-8-sig'` 自动去除 BOM。

**写出 CSV 的编码决策表**：

| 目标消费方 | 编码 |
|----------------|---------|
| Excel（Windows 或 Mac） | `utf-8-sig` |
| Excel for Mac + LibreOffice Calc | `utf-8-sig` |
| macOS Numbers | `utf-8`（无 BOM） |
| macOS / Linux 命令行工具或脚本 | `utf-8`（无 BOM） |
| 未知 / 通用 | `utf-8-sig`（对人类用户最稳妥） |

### 读取 CSV 文件

读取可能来自中文 Windows 系统的 CSV 时，先探测编码：

```python
import chardet

with open('input.csv', 'rb') as f:
    raw = f.read()
    encoding = chardet.detect(raw)['encoding'] or 'utf-8'

df = pd.read_csv('input.csv', encoding=encoding)
```

读取用 `utf-8-sig` 写出的 CSV 时，用匹配的编码自动去 BOM：

```python
df = pd.read_csv('input.csv', encoding='utf-8-sig')  # 自动去 BOM
```

**编码规则速查**：

| 场景 | 应使用编码 |
|----------|------------------|
| 写 CSV 给 Excel（任意平台） | `utf-8-sig` |
| 写 CSV 给 Numbers（macOS） | `utf-8`（无 BOM） |
| 写 CSV 给命令行 / 脚本 | `utf-8`（无 BOM） |
| 写 CSV 用途未知 / 通用 | `utf-8-sig` |
| 读取来源未知的 CSV | 先用 `chardet` 探测，回退 `utf-8` |
| 读取已知的带 BOM UTF-8 | `utf-8-sig` |
| 读取来自中文 Windows 的 CSV | `gbk` 或 `gb18030` |

---

## 读取与分析数据

### 用 pandas 做数据分析

数据分析、可视化和基础操作用 **pandas**：

```python
import pandas as pd

# 读取 Excel
df = pd.read_excel('file.xlsx')  # 默认第一个工作表
all_sheets = pd.read_excel('file.xlsx', sheet_name=None)  # 所有工作表（字典）

# 分析
df.head()      # 预览
df.info()      # 列信息
df.describe()  # 统计

# 写出 Excel
df.to_excel('output.xlsx', index=False)
```

## Excel 文件工作流

## 关键：用公式，不要硬编码数值

**始终使用 Excel 公式，而不是在 Python 里算好再写入结果。** 这样表格才能保持动态、可更新。

### ❌ 错误——硬编码计算结果

```python
# 差：在 Python 里求和再写死结果
total = df['Sales'].sum()
sheet['B10'] = total  # 硬编码 5000

# 差：在 Python 里算增长率
growth = (df.iloc[-1]['Revenue'] - df.iloc[0]['Revenue']) / df.iloc[0]['Revenue']
sheet['C5'] = growth  # 硬编码 0.15

# 差：Python 算平均值
avg = sum(values) / len(values)
sheet['D20'] = avg  # 硬编码 42.5
```

### ✅ 正确——使用 Excel 公式

```python
# 好：让 Excel 自己求和
sheet['B10'] = '=SUM(B2:B9)'

# 好：增长率用 Excel 公式
sheet['C5'] = '=(C4-C2)/C2'

# 好：平均用 Excel 函数
sheet['D20'] = '=AVERAGE(D2:D19)'
```

适用于所有计算——合计、百分比、比率、差值等。源数据变化时表格应能重算。

## 技能工作流

### 步骤1：选择工具

数据处理用 pandas，公式与格式用 openpyxl。

### 步骤2：创建或加载

新建工作簿或加载现有文件。

### 步骤3：修改

添加/编辑数据、公式和格式。

### 步骤4：保存

写入文件。

### 步骤5：重算公式（用了公式则必须执行）

```bash
python scripts/recalc.py output.xlsx
```

### 步骤6：验证并修复错误

- 脚本返回含错误详情的 JSON
- `status` 为 `errors_found` 时，查看 `error_summary` 中的错误类型与位置
- 修复后再次重算
- 常见错误：
  - `#REF!`：无效单元格引用
  - `#DIV/0!`：除以零
  - `#VALUE!`：公式中的数据类型错误
  - `#NAME?`：无法识别的公式名称

### 创建新的 Excel 文件

```python
# 用 openpyxl 处理公式和格式
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment

wb = Workbook()
sheet = wb.active

# 写入数据
sheet['A1'] = '你好'
sheet.append(['一行', '数据'])

# 写入公式
sheet['B2'] = '=SUM(A1:A10)'

# 格式
sheet['A1'].font = Font(bold=True, color='FF0000')
sheet['A1'].fill = PatternFill('solid', start_color='FFFF00')
sheet['A1'].alignment = Alignment(horizontal='center')

# 列宽
sheet.column_dimensions['A'].width = 20

wb.save('output.xlsx')
```

### 编辑现有 Excel 文件

```python
# 用 openpyxl 保留公式与格式
from openpyxl import load_workbook

wb = load_workbook('existing.xlsx')
sheet = wb.active  # 或 wb['SheetName'] 指定工作表

# 遍历多个工作表
for sheet_name in wb.sheetnames:
    sheet = wb[sheet_name]

# 修改单元格
sheet['A1'] = '新值'
sheet.insert_rows(2)   # 在第2行插入行
sheet.delete_cols(3)   # 删除第3列

# 新增工作表
new_sheet = wb.create_sheet('NewSheet')

wb.save('modified.xlsx')
```

## 重算公式

openpyxl 创建或修改的 Excel 文件中公式只是字符串，没有计算值。用 `scripts/recalc.py` 重算：

```bash
python scripts/recalc.py <excel文件> [超时秒数]
```

脚本功能：

- 首次运行自动配置 LibreOffice 宏
- 重算所有工作表的全部公式
- 扫描所有单元格的 Excel 错误（#REF!、#DIV/0! 等）
- 返回含错误位置与数量的 JSON
- Linux 与 macOS 均可用

## 公式核对清单

### 基础核对

- [ ] **抽查 2-3 个引用**：搭建完整模型前验证引用取值正确
- [ ] **列映射**：确认 Excel 列号对应（如第 64 列是 BL 而非 BK）
- [ ] **行偏移**：Excel 行号从 1 开始（DataFrame 第 5 行 = Excel 第 6 行）

### 常见坑

- [ ] **NaN 处理**：用 `pd.notna()` 检查空值
- [ ] **靠右的列**：财年数据常在第 50 列之后
- [ ] **多处匹配**：搜索所有出现位置，不只第一处
- [ ] **除零**：公式用 `/` 前检查分母（#DIV/0!）
- [ ] **错误引用**：核对所有单元格引用指向预期单元格（#REF!）
- [ ] **跨表引用**：用正确格式（Sheet1!A1）

### 公式测试策略

- [ ] **从小到大**：先在 2-3 个单元格上测试再铺开
- [ ] **核对依赖**：公式引用的所有单元格都存在
- [ ] **边界用例**：覆盖零、负数和超大值

### 解读 scripts/recalc.py 输出

```json
{
  "status": "success",           // 或 "errors_found"
  "total_errors": 0,             // 错误总数
  "total_formulas": 42,          // 文件中公式数量
  "error_summary": {             // 仅发现错误时存在
    "#REF!": {
      "count": 2,
      "locations": ["Sheet1!B5", "Sheet1!C10"]
    }
  }
}
```

## 最佳实践

### 库选择

- **pandas**：数据分析、批量操作、简单导出
- **openpyxl**：复杂格式、公式、Excel 特有功能

### openpyxl 注意事项

- 单元格索引从 1 开始（row=1, column=1 即 A1）
- 用 `data_only=True` 读计算值：`load_workbook('file.xlsx', data_only=True)`
- **警告**：以 `data_only=True` 打开后保存，公式会被值替换且永久丢失
- 大文件：读取用 `read_only=True`，写入用 `write_only=True`
- 公式只保留不计算——用 `scripts/recalc.py` 更新值

### pandas 注意事项

- 指定数据类型避免推断错误：`pd.read_excel('file.xlsx', dtype={'id': str})`
- 大文件只读需要的列：`pd.read_excel('file.xlsx', usecols=['A', 'C', 'E'])`
- 正确处理日期：`pd.read_excel('file.xlsx', parse_dates=['日期列'])`

## 代码风格

**生成 Excel 操作的 Python 代码时**：

- 写简洁、最少的代码，不加多余注释
- 避免冗长变量名和冗余操作
- 避免不必要的 print

**对 Excel 文件本身**：

- 复杂公式或重要假设的单元格加批注
- 硬编码值注明数据来源

使用说明

# Excel 表格处理专家

以表格文件为主要输入或输出的处理技能：创建、读取、编辑、修复 .xlsx/.xlsm/.csv/.tsv，保证交付零公式错误。

## 适用场景

- 打开、编辑或修复现有表格（加列、算公式、格式化、图表、清洗乱数据）
- 从零或从其他数据源创建新表格、表格格式互转
- 财务模型搭建（行业标准颜色编码与数字格式）

## 最简用法

```bash
python scripts/recalc.py output.xlsx   # 用公式后必须重算并校验
```

```text
帮我把这份 csv 转成 xlsx，加上合计行和图表
```

## 说明

- 公式重算依赖 LibreOffice（scripts/recalc.py 自动配置）
- CSV 一律用 utf-8-sig 写出，避免中文乱码
- 优先用 Excel 公式而非硬编码计算结果

如何安装此技能？

访问技能市场，点击「安装」按钮，按提示将技能包放入 AI 编程助手的 skills 目录即可。