⚡

电子表格操作Skill working-with-spreadsheets

电子表格操作技能专注于使用Python库（如openpyxl和pandas）自动化创建、编辑和分析Excel文件。它强调金融建模最佳实践，包括使用公式而非硬编码值、遵循标准颜色编码（蓝色输入、黑色公式、绿色链接）、应用专业数字格式（货币、百分比、倍数），以及通过验证清单和错误检查确保模型完整性。该技能适用于量化金融、数据分析、财务建模和自动化报告生成，能高效处理.xlsx文件、构建可维护的金融模型并执行复杂的数据操作。

数据分析 0 次安装 0 次浏览 更新于 3/2/2026

name: working-with-spreadsheets description: | 使用公式、格式化和金融建模标准创建和编辑Excel电子表格。 适用于处理.xlsx文件、金融模型、数据分析或公式密集的电子表格。 涵盖公式重新计算、颜色编码标准和常见陷阱。

电子表格操作

快速开始

from openpyxl import Workbook

wb = Workbook()
sheet = wb.active
sheet['A1'] = '收入'
sheet['B1'] = 1000
sheet['B2'] = '=B1*1.1'  # 使用公式，不要硬编码值！
wb.save('output.xlsx')

关键规则：使用公式，不要硬编码值

始终使用Excel公式，而不是在Python中计算。

# 错误 - 硬编码计算值
total = df['销售额'].sum()
sheet['B10'] = total  # 硬编码5000

# 正确 - 使用Excel公式
sheet['B10'] = '=SUM(B2:B9)'

金融模型颜色编码标准

颜色 RGB 用途
蓝色文本 0,0,255 硬编码输入、情景值
黑色文本 0,0,0 所有公式和计算
绿色文本 0,128,0 来自其他工作表的链接
红色文本 255,0,0 指向其他文件的外部链接
黄色背景 255,255,0 需要注意的关键假设 
from openpyxl.styles import Font

# 输入单元格（用户可更改）
sheet['B5'].font = Font(color='0000FF')  # 蓝色

# 公式单元格
sheet['C5'] = '=B5*1.1'
sheet['C5'].font = Font(color='000000')  # 黑色

# 跨工作表链接
sheet['D5'] = "=Sheet2!A1"
sheet['D5'].font = Font(color='008000')  # 绿色

数字格式化标准

# 带千位分隔符的货币
sheet['B5'].number_format = '$#,##0'

# 零显示为短横线
sheet['B5'].number_format = '$#,##0;($#,##0);-'

# 带一位小数的百分比
sheet['C5'].number_format = '0.0%'

# 估值倍数
sheet['D5'].number_format = '0.0x'

# 年份作为文本（不是2,024）
sheet['A1'] = '2024'  # 字符串，不是数字

库选择

任务 库 示例
数据分析 pandas df = pd.read_excel('file.xlsx')
公式和格式化 openpyxl sheet['A1'] = '=SUM(B:B)'
大文件（读取） openpyxl load_workbook('file.xlsx', read_only=True)
大文件（写入） openpyxl Workbook(write_only=True)

读取Excel文件

import pandas as pd
from openpyxl import load_workbook

# pandas - 数据分析
df = pd.read_excel('file.xlsx')
all_sheets = pd.read_excel('file.xlsx', sheet_name=None)  # 数据框字典

# openpyxl - 保留公式
wb = load_workbook('file.xlsx')
sheet = wb.active
print(sheet['A1'].value)  # 返回公式字符串

# openpyxl - 获取计算值（警告：保存时会丢失公式！）
wb = load_workbook('file.xlsx', data_only=True)

创建Excel文件

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment

wb = Workbook()
sheet = wb.active
sheet.title = '模型'

# 表头
sheet['A1'] = '指标'
sheet['B1'] = '2024'
sheet['A1'].font = Font(bold=True)

# 带公式的数据
sheet['A2'] = '收入'
sheet['B2'] = 1000000
sheet['B2'].font = Font(color='0000FF')  # 蓝色 = 输入

sheet['A3'] = '增长率'
sheet['B3'] = '=B2*0.1'
sheet['B3'].font = Font(color='000000')  # 黑色 = 公式

# 格式化
sheet['B2'].number_format = '$#,##0'
sheet.column_dimensions['A'].width = 20

wb.save('model.xlsx')

编辑现有文件

from openpyxl import load_workbook

wb = load_workbook('existing.xlsx')
sheet = wb['Data']  # 或 wb.active

# 修改单元格
sheet['A1'] = '更新值'
sheet.insert_rows(2)
sheet.delete_cols(3)

# 添加新工作表
new_sheet = wb.create_sheet('分析')
new_sheet['A1'] = '=Data!B5'  # 跨工作表引用

wb.save('modified.xlsx')

公式重新计算

openpyxl写入公式但不计算值。 使用LibreOffice重新计算：

# 重新计算并检查错误
python recalc.py output.xlsx

脚本返回JSON：

{
  "status": "success",  // 或 "errors_found"
  "total_errors": 0,
  "total_formulas": 42,
  "error_summary": {
    "#REF!": {"count": 2, "locations": ["Sheet1!B5", "Sheet1!C10"]}
  }
}

公式验证清单

构建前

  • [ ] 首先测试2-3个样本引用
  • [ ] 确认列映射（第64列 = BL，不是BK）
  • [ ] 记住：DataFrame第5行 = Excel第6行（1索引）

常见陷阱

  • [ ] 使用值前用pd.notna()检查NaN
  • [ ] FY数据通常在50+列（最右侧）
  • [ ] 搜索所有出现，不仅仅是第一个匹配
  • [ ] 除法前检查分母（#DIV/0!）
  • [ ] 验证跨工作表引用使用正确格式（Sheet1!A1）

构建后

  • [ ] 运行recalc.py并修复任何错误
  • [ ] 验证 #REF!、#DIV/0!、#VALUE!、#NAME? = 0

常见错误

错误 原因 修复
#REF! 无效单元格引用 检查删除的行/列
#DIV/0! 除以零 添加IF检查：=IF(B5=0,0,A5/B5)
#VALUE! 错误数据类型 检查单元格包含预期类型
#NAME? 未知函数 检查拼写，文本周围引号

验证

运行：python scripts/verify.py

相关技能

  • building-nextjs-apps - 电子表格上传的前端
  • scaffolding-fastapi-dapr - 电子表格处理的API