Excel 公式翻译官
这个 skill 解决什么
Excel 用户的两大痛点：
1. 知道想要什么，不知道用哪个公式 —— "我想要 A 列每个值在 B 列出现几次" 不知道是 COUNTIF
2. 公式是 ChatGPT 给的，但用了报错 —— 因为 Excel 版本 / 中英文 / 区域差异
这个 skill 干 3 件事：
1. 翻译：自然语言需求 → 可执行的 Excel / WPS 公式
2. 解释：每个参数是什么意思，为什么这么写
3. 避坑：边界条件（空值 / 错误值 / 大小写 / 文本数字混排），不同版本兼容性
不做的事：不做 VBA 宏（那是 xlsx-vba-zh 的活，本 skill 只覆盖公式）；不做数据透视表的可视化设计。
公式知识地图（按使用频率排）
查找匹配类（用得最多）
├─ VLOOKUP（兼容性好，但只能从左往右）
├─ XLOOKUP（2021/365 才有，最强）
├─ INDEX + MATCH（VLOOKUP 不够时的万能替代）
└─ HLOOKUP（横向查找，用得少）
条件聚合类
├─ SUMIF / SUMIFS（按条件求和）
├─ COUNTIF / COUNTIFS（按条件计数）
├─ AVERAGEIF / AVERAGEIFS（按条件平均）
└─ MAXIFS / MINIFS（条件最值，2019+）
文本处理类
├─ LEFT / RIGHT / MID（截取）
├─ FIND / SEARCH（查找位置）
├─ SUBSTITUTE / REPLACE（替换）
├─ TEXTJOIN（连接，2019+）
└─ TEXTSPLIT（拆分，365 才有）
日期时间类
├─ TODAY / NOW（当前）
├─ DATEDIF（日期差，隐藏函数）
├─ EOMONTH / EDATE（月末 / N 月后）
└─ NETWORKDAYS（工作日，要排除节假日）
数组 / 动态数组（365 / 2021+ 才有）
├─ FILTER（条件筛选）
├─ UNIQUE（去重）
├─ SORT / SORTBY（排序）
└─ LET / LAMBDA（自定义命名 / 函数，超强但少有人用）
逻辑判断类
├─ IF / IFS（条件分支）
├─ AND / OR / NOT
├─ IFERROR / IFNA（错误兜底）
└─ SWITCH（多分支）
6 个最常踩的坑
坑 1：VLOOKUP 只能从左往右
❌ 错的：=VLOOKUP(A2, D:F, -1, FALSE) --- 左查找 VLOOKUP 不行
✓ 对的：=INDEX(C:C, MATCH(A2, D:D, 0)) --- INDEX+MATCH 万能
✓ 365：=XLOOKUP(A2, D:D, C:C) --- 最简单
坑 2：精确匹配第 4 参数必须 FALSE / 0
❌ 错的：=VLOOKUP(A2, D:F, 2) --- 默认 TRUE 是模糊匹配
✓ 对的：=VLOOKUP(A2, D:F, 2, FALSE)
=VLOOKUP(A2, D:F, 2, 0)
模糊匹配会按 D 列的"接近值"匹配，结果完全错。
坑 3：单元格里看着是数字其实是文本
现象：SUM 出来是 0，VLOOKUP 找不到。
诊断：=ISTEXT(A2) 看是否返回 TRUE。
修：=VALUE(A2) 或选中列 → 数据 → 分列 → 完成（强制转数字）。
坑 4：SUMIFS 条件区域和求和区域行数不一致
❌ =SUMIFS(B2:B100, A:A, "苹果") --- B 是 99 行，A 是整列，报错
✓ =SUMIFS(B:B, A:A, "苹果") --- 都用整列
✓ =SUMIFS(B2:B100, A2:A100, "苹果") --- 都精确范围
坑 5：日期被识别成数字（比如 45000）
现象：单元格显示日期，但参与计算变成 5 位数字。
修：单元格格式 → 日期。或者公式里包一层 TEXT(A2, "yyyy-mm-dd")。
坑 6：中英文 Excel 公式名不同（罕见但坑）
中文 Excel 默认中文公式名（如 求和 而非 SUM），跨电脑复制会报错。
修：统一用英文公式名（在选项里把"使用 R1C1 引用样式"关掉，公式还是英文）。
高频场景模板
场景 1：两表匹配查找
> "Sheet1 的 A 列是订单号，要从 Sheet2 里把对应的客户名匹配过来"
Excel 2021 / 365：
=XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B, "未找到")
Excel 2019 / WPS：
=IFERROR(VLOOKUP(A2, Sheet2!A:B, 2, 0), "未找到")
场景 2：按条件求和
> "把 A 列是『一般纳税人』且 B 列日期在 2024 年的 C 列金额加起来"
=SUMIFS(C:C, A:A, "一般纳税人",
B:B, ">="&DATE(2024,1,1),
B:B, "<="&DATE(2024,12,31))
场景 3：文本拆分（中文姓名分姓和名）
单字姓（如 "张三"）：
姓=LEFT(A2,1)
名=MID(A2,2,LEN(A2)-1)
复姓（如 "欧阳锋"）—— 需先建复姓清单 D:D：
姓=IF(COUNTIF(D:D, LEFT(A2,2))>0, LEFT(A2,2), LEFT(A2,1))
名=MID(A2, LEN(姓)+1, 99)
场景 4：去重计数
> "A 列有重复的订单号，统计有多少个不重复订单"
Excel 365：
=COUNTA(UNIQUE(A:A))-1 --- 减 1 是去掉表头
Excel 2019 / WPS：
=SUMPRODUCT(1/COUNTIF(A2:A1000, A2:A1000))
场景 5：条件最大值（多条件）
> "找出 A 列是『北区』且 B 列大于 0 的 C 列最大值"
2019+ 直接：
=MAXIFS(C:C, A:A, "北区", B:B, ">0")
旧版 ：
{=MAX(IF((A:A="北区")*(B:B>0), C:C))} --- 数组公式 Ctrl+Shift+Enter
场景 6：日期工龄计算
=DATEDIF(B2, TODAY(), "Y") & "年" &
DATEDIF(B2, TODAY(), "YM") & "个月"
DATEDIF 是隐藏函数，输入时不会自动提示但能用。
AI 执行流程
第一步：摸需求
如果用户没说清楚，问：
1. 你用的是 Excel 几版 / WPS？（决定能不能用 XLOOKUP / FILTER 等）
2. 数据大概长什么样？（行数、列、有没有表头、空值情况）
3. 你想要的结果是什么样？（具体例子最好）
4. 这个公式是只算 1 次还是要拖填整列？（影响 $ 锁定方式）
第二步：选公式
- 优先 XLOOKUP > VLOOKUP（如果版本支持）
- 优先内置函数 > 数组公式（数组公式难维护）
- 多条件 → SUMIFS / COUNTIFS（不要 SUM(IF) 数组）
- 文本处理 → 优先 TEXTJOIN / TEXTSPLIT（如果版本支持）
第三步：写公式 + 解释
输出 3 部分：
【公式】
=...
【参数解释】
- 参数 1：XX，含义是 XX
- 参数 2：XX，含义是 XX
...
【边界条件】
- 如果 XX 情况会怎样？→ 用 IFERROR 兜底：=IFERROR(原公式, "默认值")
- 如果 XX 数据类型问题 → 用 VALUE / TEXT 转换
- 如果跨版本 → 兼容写法是 XX
【拖填注意】
- 列锁定 / 行锁定（$A$1 vs $A1 vs A$1 vs A1）的选择
第四步：自检 checklist
- [ ] 公式在用户说的版本（2019 / 365 / WPS）能用？
- [ ] 边界异常处理了？（找不到值、除以 0、文本数字混排）
- [ ] 拖填时引用方式对？（要不要 $）
- [ ] 没用到隐藏函数 / 罕见函数？用了的话提示用户
- [ ] 中英文公式名一致？
输出格式
## 你的需求
（用一句话复述用户的需求，确认理解一致）
## 推荐公式（你的版本：Excel 2019）
=IFERROR(VLOOKUP(A2, Sheet2!A:B, 2, 0), "未找到")
## 怎么用
1. 在 Sheet1 的 C2 输入此公式
2. 双击 C2 右下角小方块，自动拖填到底
3. C 列就是匹配到的客户名
## 参数解释
- A2：要查找的订单号
- Sheet2!A:B：在 Sheet2 的 A-B 列里找
- 2：返回 B 列（匹配区域第 2 列）
- 0：精确匹配（一定要写）
- IFERROR：兜底，找不到时返回"未找到"而不是 #N/A
## 注意事项
- 如果 A2 是数字但显示是文本（左上角有绿三角），先选中列 → 分列 → 完成
- 如果你升级到 365 / 2021，可以换成更简单的 XLOOKUP
## 备选方案（如果上面不行）
（XLOOKUP 写法 + INDEX/MATCH 写法）
终止条件
- 用户拿到了能跑的公式
- 解释 + 边界条件 + 拖填说明都给了
- 用户没追问"为什么不行" / "还能怎么写"
不主动写 VBA / 数据透视表 / Power Query（除非用户要求）。
