Spreadsheet & Data Wrangling Master

将杂乱的数据转化为清晰的洞察、自动化报告和决策可用的仪表盘。平台无关 —— 适用于 CSV、Excel、Google Sheets 或任何表格格式。

阶段 1：数据摄入与评估

在处理任何数据之前，先评估你所拥有的数据。

快速健康检查（满分 20 分）

data_intake:
  source: ""           # 文件路径、URL、API、数据库、手动输入
  format: ""           # CSV、XLSX、TSV、JSON、剪贴板粘贴
  rows: 0
  columns: 0
  file_size: ""
  encoding: ""         # UTF-8、Latin-1、Windows-1252 等
  delimiter: ""        # 逗号、制表符、竖线、分号

  health_score:        # 每项评分 0–4，总分 /20
    completeness: 0    # 4=缺失率 <1%，3=<5%，2=<15%，1=<30%，0=>30%
    consistency: 0     # 4=类型统一，3=少量混合，2=显著混合，1=混乱，0=不可用
    accuracy: 0        # 4=已验证，3=合理，2=部分异常值，1=错误较多，0=不可信
    freshness: 0       # 4=实时，3=<24小时，2=<7天，1=<30天，0=过时/未知
    structure: 0       # 4=整洁（每行=一条记录），3=轻微重构，2=需要转置，1=多级标题，0=自由格式

  issues_found: []     # 在修复前列出所有问题

前 10 个必须提问的问题

1. 数据有多少行和列？
2. 每一行代表什么？（一个客户？一笔交易？一天的数据？）
3. 是否有标题行？多层标题行？合并的单元格？
4. 各列的数据类型是什么？（日期、货币、百分比、ID、自由文本）
5. 缺失值有多少？哪些列缺失？
6. 是否存在重复数据？按哪个键判断重复？
7. 是否有唯一标识列？
8. 使用的是哪种日期格式？（MM/DD/YYYY vs DD/MM/YYYY vs YYYY-MM-DD vs 混合）
9. 货币或数字格式如何？（1,000.00 vs 1.000,00 vs 1000）
10. 数据来源是哪里？更新频率是多少？

阶段 2：数据清洗决策树

逐步清洗流程

START
  │
  ├─ 标题 → 标准化（小写、snake_case、去除空格和特殊字符）
  │
  ├─ 存在重复数据？
  │   ├─ 完全重复 → 删除，保留第一条
  │   ├─ 接近重复 → 标记待审（通过姓名+地址进行模糊匹配）
  │   └─ 有意重复 → 保留（如同一客户多次下单）
  │
  ├─ 存在缺失值？
  │   ├─ 缺失比例 <5% → 填充（数值用均值，分类用众数，时间序列用前向填充）
  │   ├─ 5%-30% → 标记并填充为“UNKNOWN”或插值，附说明
  │   ├─ >30% → 考虑删除该列或标记为不可靠
  │   └─ 关键字段整行缺失 → 删除并记录日志
  │
  ├─ 数据类型是否正确？
  │   ├─ 日期为文本 → 解析为日期（尝试多种格式，记录失败情况）
  │   ├─ 数字为文本 → 去除货币符号、逗号、空格，转换为数值
  │   ├─ ID/邮编含前导零 → 保持为文本（切勿转为数字）
  │   ├─ 电话号码 → 保留为文本，统一格式
  │   ├─ 列中类型混杂 → 拆分或强制转换，并记录错误
  │   └─ 布尔值变体 → 统一映射（Yes/No/True/False/1/0/Y/N → 一致）
  │
  ├─ 存在异常值？
  │   ├─ 计算 IQR：Q1 - 1.5×IQR 至 Q3 + 1.5×IQR
  │   ├─ 业务逻辑校验（负收入？年龄 200 岁？2099 年的日期？）
  │   ├─ 决策：修正（打字错误）、截断（Winsorize）、删除或保留并标记
  │   └─ 始终记录被修改的异常值及其原因
  │
  ├─ 是否需要标准化？
  │   ├─ 文本大小写 → 统一格式（姓名用首字母大写，代码用全大写）
  │   ├─ 空白字符 → 去除首尾空白，压缩内部空白
  │   ├─ 分类项 → 映射统一（"US"/"USA"/"United States" → "US"）
  │   ├─ 日期 → 内部使用 ISO 8601 格式（YYYY-MM-DD）
  │   ├─ 货币 → 统一符号位置与小数精度
  │   └─ 电话/邮箱 → 验证格式
  │
  └─ 结构性问题？
      ├─ 多级标题行 → 合并为单行标题
      ├─ 合并单元格 → 取消合并并向下填充
      ├─ 转置/交叉表 → 反转为整洁格式（每行=一条观测）
      ├─ 一张表中有多个数据表 → 拆分为独立工作表或文件
      └─ 元数据行（总计、备注）→ 与数据行分离

清洗日志模板

cleaning_log:
  date: "YYYY-MM-DD"
  source_file: ""
  rows_before: 0
  rows_after: 0
  actions:
    - action: "移除完全重复项"
      rows_affected: 0
      key_columns: ["email"]
    - action: "填充缺失值"
      column: "state"
      method: "众数"
      values_filled: 0
    - action: "移除异常值"
      column: "revenue"
      criteria: "负值"
      rows_removed: 0
    - action: "标准化日期"
      column: "order_date"
      from_format: "MM/DD/YYYY"
      to_format: "YYYY-MM-DD"
      parse_failures: 0
  notes: ""

阶段 3：转换模式

12 种核心转换操作

连接策略决策指南

哪种连接方式适合你？

│

├─ 需要左表所有行 → LEFT JOIN

│ （可能有订单，也可能没有订单的客户）

│

├─ 只需要匹配的行 → INNER JOIN

│ （仅有订单的客户）

│

├─ 需要两个表的所有行 → FULL OUTER JOIN

│ （对账：发现不一致数据）

│

├─ 需要不在另一张表中的所有行 → LEFT JOIN + WHERE 右侧列 IS NULL

│ （从未下单的客户）

│

└─ 需要所有组合 → CROSS JOIN（较少使用，需谨慎）

（所有产品 × 所有门店，用于价格矩阵）

⚠️ 始终检查连接结果：

• 行数：是否爆炸式增长？（多对多连接常见）
• 行数：是否大幅减少？（键值不匹配）
• 空值列：外连接中预期为空，意外出现空值 = 键值不匹配

公式参考（跨平台）

阶段 4：分析框架

快速分析菜单

根据问题选择对应的分析类型：

描述性分析（发生了什么？）

• 摘要统计：数量、均值、中位数、最小值、最大值、标准差、分位数
• 频率分布：每类有多少？
• 时间趋势：按日/周/月聚合的时间序列
• 交叉表：类别 A × 类别 B 的分布情况

诊断性分析（为什么会发生？）

• 下钻分析：是哪个细分群体导致了变化？
• 同群分析：按注册月份划分的行为分析
• 相关性分析：哪些变量同步变动？
• 差异分析：实际 vs 预算/预测，按类别对比

预测性分析（可能会发生什么？）

• 趋势外推：线性/指数拟合 + 置信区间
• 移动平均：7/30/90天平滑处理
• 季节性分析：与去年同期同期比较
• 增长率：环比（MoM）、季度环比（QoQ）、同比（YoY）百分比变化

决策性分析（应该怎么做？）

• 帕累托法则（80/20）：哪20%的X带来了80%的Y？
• 场景分析：最佳/基准/最差情况，不同假设下的结果
• 敏感性分析：哪个输入变量影响最大？
• 盈亏平衡点：X在何时能覆盖Y？

洞察公式

每个发现必须遵循以下结构：

洞察：[你发现的内容 — 一句话]
证据：[具体数据]
所以呢：[对业务的意义]
行动：[应采取的措施]
信心：[高/中/低 + 原因]

示例：

洞察：第三季度客户获取成本相比第二季度上升了43%
证据：CAC从47美元升至67美元，主要由付费搜索点击单价上涨62%驱动
所以呢：当前LTV为180美元，回本周期从3.1个月延长至4.5个月
行动：将30%的付费搜索预算转移至邮件/推荐渠道（对应CAC分别为12美元和23美元）
信心：高 — 基于完整季度的Stripe + Google Ads数据

阶段 5：仪表板与报告模板

执行摘要仪表板（一页）

┌──────────────────────────────────────────────────┐
│  执行仪表板 — [期间]                             │
│                                                    │
│  ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐│
│  │ 收入     │ │ 客户数   │ │ 利润率  │ │ 增长率  ││
│  │ $XXX,XXX │ │  X,XXX  │ │  XX.X%  │ │ +XX.X%  ││
│  │ ▲ +X.X%  │ │ ▲ +XXX  │ │ ▼ -X.X% │ │ vs LY   ││
│  └─────────┘ └─────────┘ └─────────┘ └─────────┘│
│                                                    │
│  [趋势图 — 关键指标过去12个月]                     │
│                                                    │
│  最重要的3个洞察：                                 │
│  1. [洞察 + 行动]                                  │
│  2. [洞察 + 行动]                                  │
│  3. [洞察 + 行动]                                  │
│                                                    │
│  ┌──────────────────┐ ┌──────────────────┐        │
│  │ 按细分维度       │ │ 按渠道           │        │
│  │ （表格或图表）     │ │ （表格或图表）   │        │
│  └──────────────────┘ └──────────────────┘        │
└──────────────────────────────────────────────────┘

KPI 格式规范

图表选择指南

你在展示什么？
│
├─ 随时间的变化 → 折线图（≤5条系列）或面积图（堆叠组成）
│
├─ 不同类别的比较 → 柱状图（长标签建议横向）
│
├─ 整体占比 → 饼图（≤5个扇区）或堆叠柱状图（>5个或随时间变化）
│
├─ 分布情况 → 直方图或箱形图
│
├─ 两变量关系 → 散点图
│
├─ 地理位置 → 地图（若有地理位置数据）
│
├─ 排名列表 → 横向柱状图，按降序排列
│
└─ 单一关键指标 → 大数字 + 趋势指示器

⚠️ 绝对不要使用：
- 3D图表（扭曲视觉感知）
- 双Y轴（误导读者）
- 饼图超过7个扇区（改用柱状图）
- 彩虹色系（最多使用2-3种主色 + 灰色）

阶段 6：定期报告自动化

自动化检查清单

recurring_report:
  name: ""
  frequency: ""          # daily, weekly, monthly, quarterly
  owner: ""
  recipients: []

  data_sources:
    - source: ""         # 文件路径、API、数据库
      refresh: ""        # 数据如何更新
      format: ""

  processing_steps:
    - step: "加载数据"
      tool: ""           # Python、Excel 宏、Google Apps Script
    - step: "清洗"
      rules: []          # 参考清洗协议
    - step: "转换"
      operations: []
    - step: "分析"
      calculations: []
    - step: "格式化"
      template: ""       # 填充的仪表板模板
    - step: "交付"
      method: ""         # 邮件、Slack、共享驱动器、API 推送

  quality_checks:
    - "行数在预期范围内（±20%）"
    - "关键列中无空值"
    - "总计与源系统一致"
    - "日期范围符合预期周期"
    - "关键指标通过合理性检查（无10倍增长且无解释）"

  error_handling:
    - trigger: "数据源不可用"
      action: "使用缓存的上一次有效版本 + 发送告警"
    - trigger: "行数超出范围"
      action: "暂停处理 + 标记人工审查"
    - trigger: "指标超过历史标准差3倍"
      action: "在报告中包含异常提示"

Python 自动化模板

"""
定期报告：[NAME]
调度频率：[FREQUENCY]
"""
import pandas as pd
from pathlib import Path
from datetime import datetime

# --- 配置 ---
INPUT_PATH = Path("data/raw/")
OUTPUT_PATH = Path("data/reports/")
REPORT_DATE = datetime.now().strftime("%Y-%m-%d")

# --- 加载 ---
df = pd.read_csv(INPUT_PATH / "source.csv", parse_dates=["date"])

# --- 清洗 ---
df = df.drop_duplicates()
df = df.dropna(subset=["required_column"])
df["amount"] = pd.to_numeric(df["amount"], errors="coerce")

# --- 转换 ---
summary = (
    df.groupby("category")
    .agg(
        total=("amount", "sum"),
        count=("id", "count"),
        avg=("amount", "mean"),
    )
    .sort_values("total", ascending=False)
    .reset_index()
)

# --- 质量检查 ---
assert len(df) > 0, "数据集为空！"
assert df["amount"].isna().sum() / len(df) < 0.05, "缺失金额比例过高"

# --- 输出 ---
output_file = OUTPUT_PATH / f"report-{REPORT_DATE}.csv"
summary.to_csv(output_file, index=False)
print(f"✅ 报告已保存：{output_file} | {len(summary)} 行")

阶段 7：常见电子表格模式

模式 1：财务模型

工作表结构：
├── 假设 → 所有可编辑输入集中在一个位置（高亮单元格）
├── 收入 → 公式引用假设
├── 成本 → 公式引用假设
├── 利润表 → 汇总收入与成本
├── 现金流 → 由利润表 + 营运资金推导
├── 资产负债表 → 由现金流推导
├── 场景 → 最佳/基准/最差切换，影响假设
└── 仪表板 → 图表 + KPI，数据来自利润表/现金流

规则：
- 输入项 = 蓝色字体或黄色背景（选择一种，保持一致）
- 公式 = 黑色字体，公式中不硬编码数字
- 每个公式必须回溯到假设或原始数据
- 无循环引用
- 包含版本号 + 最后更新日期

模式 2：CRM / 联系人追踪器

必需列：
- id（自动递增或 UUID）
- 姓名、邮箱、电话、公司
- 来源（如何获取客户）
- 状态（潜在客户 → 已联系 → 已评估 → 提案 → 成功/失败）
- 上次联系时间
- 下一步行动 + 下一步行动时间
- 交易金额
- 备注

衍生列：
- 从上次联系至今天数 = TODAY() - last_contact_date
- 流程阶段停留天数 = TODAY() - stage_entry_date
- 是否过期 = 从上次联系至今天数 > 14

仪表板指标：
- 各阶段管道价值
- 阶段间转化率
- 各阶段平均停留天数
- 过期联系人数量（需处理）

模式 3：库存 / 库存追踪器

必需列：
- sku、名称、类别
- 当前库存量、补货点、补货数量
- 单位成本、单位售价
- 最近入库时间、最近销售时间
- 供应商

衍生列：
- 库存价值 = 当前库存量 × 单位成本
- 毛利率 = (单位售价 - 单位成本) / 单位售价
- 库存可用天数 = 当前库存量 / 日均销量
- 是否需要补货 = 当前库存量 ≤ 补货点

预警：
- 🔴 低于补货点
- 🟡 7天内可能缺货（基于周转速度）
- ⚪ 死库存（90天内无销售）

模式 4：项目 / 任务追踪器

必需列：
- 任务编号、任务名称、描述
- 责任人、优先级（P0-P3）
- 状态（待办 → 进行中 → 审核 → 完成）
- 开始日期、截止日期、完成日期
- 预估工时、实际工时

衍生列：
- 剩余天数 = 截止日期 - TODAY()
- 是否逾期 = 截止日期 < TODAY() 且状态 ≠ "完成"
- 工时偏差 = 实际工时 - 预估工时
- 完成率 = 已完成任务数 / 总任务数

仪表板：
- 剩余工作量趋势图（剩余 vs 时间）
- 状态分布饼图
- 逾期任务列表
- 团队工作负载（每人任务数）

模式 5：预算 vs 实际

结构：
- 行：费用类别 + 收入项目
- 列：预算 | 实际 | 差异 | 差异百分比
- 分组：按月或季度

关键公式：
- 差异 = 实际 - 预算
- 差异百分比 = (实际 - 预算) / 预算
- 累计预算 = 当前月份及之前所有月份之和
- 年化速率 = (累计实际 / 已过去月份数) × 12

条件格式：
- 绿色：有利差异（收入超预期，成本低于预算）
- 红色：不利差异（收入未达预期，成本超预算）
- 阈值：若 |差异| > 10%，则标记

阶段 8：数据质量规则

验证检查清单（在任何分析前运行）

validation:
  structural:
    - "无重复的列名"
    - "无完全空的列"
    - "无完全空的行（除有意设置的分隔行外）"
    - "所有行的列数保持一致"
    - "表头位于第1行（不支持多行表头，除非已处理）"

  type_integrity:
    - "日期列能正确解析为有效日期"
    - "数值列中不含文本（表头除外）"
    - "ID 列在预期位置具有唯一性"
    - "邮箱列符合基本邮箱格式"
    - "电话列格式保持一致"

  business_rules:
    - "收入 ≥ 0（或说明负值代表退款）"
    - "日期在合理范围内（历史数据不应包含未来日期）"
    - "百分比在 0-100 之间（或 0-1，需保持一致）"
    - "状态值在允许的列表中"
    - "外键在参考表中存在"

  completeness:
    - "必填列缺失值少于 5%"
    - "无孤立记录（子记录无父记录）"
    - "日期范围连续（每日数据无间隔）"

数据质量评分（0-100）

评分 = Σ(权重 × 得分/4 × 100)

• 90-100：可直接用于生产环境
• 75-89：需少量修正
• 50-74：需显著清洗
• <50：需重新采集或重构后方可使用

阶段 9：格式转换与互操作性

转换决策矩阵

编码生存指南

文本乱码？请按顺序尝试以下编码：
1. UTF-8（默认，支持所有语言）
2. UTF-8-BOM（Windows 导出常带 BOM）
3. Latin-1 / ISO-8859-1（西欧语言）
4. Windows-1252（Windows “ANSI”）
5. Shift-JIS（日文）
6. GB2312 / GBK（中文）

Python: pd.read_csv("file.csv", encoding="utf-8-sig")
检测工具：chardet 或 charset-normalizer 库

阶段 10：性能与扩展性

数据规模阈值

性能优化建议

• 仅读取所需列：pd.read_csv(file, usecols=["col1","col2"])
• 提前指定数据类型：避免推断开销
• 大文件使用分块读取：pd.read_csv(file, chunksize=50000)
• 低基数列使用分类类型：df["status"] = df["status"].astype("category")
• 避免 iterrows：改用向量化操作或 .apply()
• DuckDB 用于文件上执行 SQL：duckdb.sql("SELECT * FROM 'file.csv' WHERE x > 100")

阶段 11：边缘情况与常见陷阱

十二大常见数据问题

1. 日期格式混杂 — 美式（MM/DD）、欧式（DD/MM）、ISO（YYYY-MM-DD）在同一列
2. Excel 日期序列号 — 44927 而非 2023-01-01
3. 前导零丢失 — 邮编 01234 变为 1234
4. 科学计数法 — ID 1234567890123 变为 1.23457E+12
5. 隐藏字符 — 不间断空格、零宽字符、BOM 标记
6. 合并单元格 — 仅左上角有值，其余为空
7. 数字作为文本 — "100"（文本）与 100（数值），外观相同
8. 本地化小数点 — 1,234.56 与 1.234,56
9. 空字符串 vs NULL — "" 与 NaN 行为不同
10. 尾随空格 — "New York " ≠ "New York"
11. Excel 1904 日期系统 — Mac 生成文件，日期偏移约 4 年
12. 公式结果 vs 公式本身 — 复制粘贴值会无声丢失公式

多币种处理

currency_rules:
  - 金额与币种代码分列存储
  - 单列中不得混合多种币种，除非包含币种代码
  - 使用 ISO 4217 币种代码（USD、GBP、EUR、BTC）
  - 记录转换所用汇率及汇率日期
  - 保留原始金额与转换后金额为独立列
  - 明确说明：此汇率是交易时的汇率还是当前汇率？

时区处理

timezone_rules:
  - 内部时间戳统一存储为 UTC
  - 记录原始时区信息
  - 仅在展示时转换为本地时间
  - “当日结束” = 业务时区的 23:59:59，而非 UTC
  - 夏令时切换可能导致一天为 23 小时或 25 小时
  - 日度聚合应在业务时区进行，而非 UTC

阶段 12：质量评分标准（0-100）

评分标准（总分 100）

评分等级：

• 90+ = 生产级分析
• 75–89 = 稳定成果
• 60–74 = 需改进
• <60 = 重新处理

自然语言指令

"Clean this CSV"           → 执行第2阶段清洗流程，输出干净文件 + 日志
"Analyze this data"        → 第1阶段评估 → 第4阶段分析 → 输出洞察
"Build a dashboard"        → 使用第5阶段模板 → 填充数据生成可视化
"Convert this to Excel"    → 第9阶段转换，保持质量不变
"Find duplicates"          → 执行去重分析，支持精确匹配与模糊匹配
"What's wrong with this data?" → 第1阶段健康检查 + 第8阶段验证
"Create a monthly report"  → 生成第6阶段自动化模板
"Compare these two files"  → 合并 + 差异分析
"Summarize by category"    → 分组聚合 + 帕累托分析
"Make this data tidy"      → 执行反透视/重塑，实现每行一个观测值
"Set up a budget tracker"  → 应用第7阶段模式5模板
"Profile this dataset"     → 完整执行第1阶段 + 第8阶段质量评分

相关 AfrexAI 技能

• afrexai-data-analyst — 高级分析方法论（DICE 框架、统计分析）
• afrexai-fpa-engine — 财务规划与分析，含电子表格模型
• afrexai-budget-tracker — 个人或企业预算管理
• afrexai-business-automation — 重复性电子表格工作流自动化

⚡ 提升你的数据能力 → [AfrexAI 上下文包 ($47)](https://afrexai-cto.github.io/context-packs/) — 行业专用数据模板、KPI 框架与报告自动化工具，涵盖 SaaS、金融科技、制造、电商等领域。

🔗 更多免费 AfrexAI 技能：

• afrexai-data-analyst — 完整的数据分析方法论
• afrexai-business-automation — 工作流自动化框架
• afrexai-seo-content-engine — SEO 优化内容创作
• afrexai-customer-success — 客户留存与健康度评分
• afrexai-devops-engine — DevOps 与平台工程

📦 浏览全部：[AfrexAI 商店](https://afrexai-cto.github.io/context-packs/)