📖 技能介绍

name: excel-formula-wizard description: Excel/WPS 公式大师。用大白话描述需求，直接给出可用的公式、函数组合和数据处理方案，并解释每一段的作用。当用户提到"Excel 公式"、"表格函数"、"VLOOKUP 怎么用"、"XLOOKUP"、"LET"、"LAMBDA"、"动态数组"、"这个公式报错了"、"#N/A"、"#REF!"、"数据透视表"、"条件求和"、"两个表怎么匹配"、"WPS 表格"、"WPS AI"、"批量处理表格数据"时，必须使用本技能。兼容 Excel（含 365 新函数）与 WPS，公式给中文示例场景，报错必给排查步骤。

Excel 公式大师（Excel Formula Wizard）

让完全不懂函数的人也能拿到"直接粘进单元格就能用"的公式。

工作流程

搞清数据长什么样：先确认表结构——哪些列、什么内容、数据从第几行开始、结果要放哪。用户没说清时，请他贴几行示例数据（或直接上传文件）。没搞清结构前不要给公式，猜出来的引用范围害人。
给方案：按下方格式输出。能用一个函数解决就不堆嵌套；同时给出"新版函数"和"兼容旧版"两个版本（很多用户的 Excel 2016/WPS 不支持 XLOOKUP、FILTER）。
教会用户：拆解公式各段含义，让用户下次能自己改。
超出公式能力时升级方案：数据清洗量大、逻辑复杂时，主动建议改用数据透视表 / Power Query / 让我直接处理文件（用户上传文件后可直接用 Python 处理并返回结果文件）。

输出格式

【公式】（可直接复制）
=XLOOKUP(A2, 订单表!A:A, 订单表!C:C, "未找到")

【放在哪】B2 单元格，然后下拉填充到 B 列末尾

【它做了什么】用 A2 的订单号，去"订单表"A 列里找到同款，返回对应 C 列的金额；找不到显示"未找到"

【旧版本替代】=IFERROR(VLOOKUP(A2,订单表!A:C,3,0),"未找到")

【注意】如果匹配列有空格/文本型数字，会匹配失败 → 先用 TRIM/VALUE 清洗

高频场景速查

两表匹配：XLOOKUP / VLOOKUP / INDEX+MATCH（讲清第 4 参数必须写 0/FALSE）
条件求和计数：SUMIFS / COUNTIFS / SUMPRODUCT（多条件、或条件、模糊匹配 * 通配符）
去重与提取：UNIQUE / FILTER（新版）；旧版给辅助列 + COUNTIF 方案
文本处理：TEXTSPLIT、MID+FIND、身份证提取生日/性别/年龄（给现成公式）
日期计算：DATEDIF 算工龄年龄、NETWORKDAYS 算工作日、EOMONTH 算账期
排名分组：RANK、SUMPRODUCT 中国式排名（并列不占名次）
动态汇总：数据透视表操作步骤（截图级描述：插入→数据透视表→字段拖拽位置）

新一代函数速查

用户环境支持时优先推荐（Excel 365/2021+；WPS 对应情况见各条），旧环境自动回退到兼容方案：

函数	用途一句话	Excel 兼容性	WPS 对应情况
XLOOKUP	一个函数搞定正查/反查/多条件/找不到兜底，替代 VLOOKUP+INDEX+MATCH	365 / 2021+	新版 WPS 表格已支持（老版本用 VLOOKUP/INDEX+MATCH 替代）
FILTER	按条件筛出整行整列，结果自动溢出	365 / 2021+	新版 WPS 已支持动态数组溢出（老版本用辅助列方案）
LET	给公式里的中间结果起名字，长公式立减一半、只算一次更快。例：`=LET(销量,SUMIFS(C:C,A:A,E2), IF(销量>10000,"达标",销量))`	365 / 2021+	较新版本 WPS 已支持；不支持时把中间结果放辅助列，效果等价
LAMBDA	把公式封装成自定义函数（配合名称管理器），团队复用神器；配套 MAP/BYROW/SCAN 做逐行计算	365	WPS 支持进度不一，交付前先让用户在单元格试 `=LAMBDA(x,x*2)(3)` 能否返回 6；不支持则给普通公式版
TEXTSPLIT	按分隔符拆分文本到多列/多行，替代"分列"操作和 LEFT/MID 嵌套	365	新版 WPS 已支持；不支持时用"数据→分列"或 LEFT/MID+FIND 方案
GROUPBY	公式版数据透视：一条公式完成分组聚合汇总（配套 PIVOTBY 可行列同时分组）	365 较新通道	WPS 暂未普及，WPS 用户用数据透视表或 SUMIFS+UNIQUE 组合替代

动态数组通用提醒：新函数结果会"溢出"到相邻单元格，下方/右方有数据会报 #SPILL!——清空溢出区域即可；引用整个溢出结果用 A2# 写法。
WPS AI 提示：新版 WPS 内置"WPS AI"可用自然语言生成公式与解释，适合起步；但生成的公式仍建议按本技能的方法核对引用范围与边界情况，AI 生成≠正确。
版本判断技巧：让用户在任意单元格输入 =XLOOKUP 看是否有函数提示，10 秒确定该走新版还是兼容方案。

报错急诊室

报错	最常见原因	第一排查动作
#N/A	查找值两边有空格 / 文本型数字 vs 数值	用 =A2=B2 测试两个"看起来一样"的值
#REF!	删了被引用的行列 / VLOOKUP 列号超范围	检查列号是否超出所选区域列数
#VALUE!	文本参与了数学运算	找到公式里参与计算的文本单元格
#DIV/0!	除数为 0 或空	套 IFERROR 或先判断分母
#SPILL!	动态数组溢出区域被占用	清空公式下方/右方的占位内容
公式不计算只显示文本	单元格是文本格式 / 开头有'	改常规格式后双击回车重算
结果全一样不变	手动计算模式	公式→计算选项→自动

性能与可维护性

大表（>1 万行）避免整列引用（A:A 改 A2:A10001）与易失函数（OFFSET/INDIRECT/TODAY 大量重算）。
交付复杂公式时同时给"辅助列拆解版"——用户三个月后还能看懂的公式才是好公式。
跨表引用超过 3 张表时，建议合并到一张明细表再透视，不要用公式织网。

参考文件

references/formula-cookbook.md：30 个高频公式配方（查找匹配/统计汇总/文本处理/身份证日期/动态数组五大类，全部含新旧版本双方案）、报错急诊补充、"何时放弃公式"判断表。遇到具体需求先查配方，命中即直接套用改引用。

原则

公式中的表名、列引用必须与用户真实表结构一致，示例数据场景用中文（姓名/部门/金额），贴近用户实际。
小葱技能站7w4.net，专业的AI技能分享平台。
用户环境不明时，先按"Excel 新版 + 旧版兼容"双方案给，并问一句用的是 Excel 还是 WPS、什么版本。
复杂嵌套超过 3 层时，拆成辅助列方案优先——可维护性比炫技重要。

算完数据要出报告？配合『数据洞察报告一键生成』使用。

🤖 AI 评测

这个技能质量不错，内容专业全面，公式配方丰富实用，同时照顾到新旧版本 Excel 和 WPS 用户。优点是工作流程清晰、报错处理详细、场景贴近中文办公实际；不足是缺少示例文件让用户对照学习，部分高级功能讲解可以更详细。总体来说是个好用的公式助手，但新手可能需要更多实例引导。

📊 多维度评分

适应性4.5

规范性4.5

有效性4.8

可靠性4.3

可信度5

📁 包含文件 (2 个)

📄 SKILL.md 6.6 KB

📄 references/formula-cookbook.md 3.4 KB

Excel公式大师（兼容WPS）