Excel公式大师（兼容WPS）
📄 办公效率
免费
📖 技能介绍
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 张表时，建议合并到一张明细表再透视，不要用公式织网。
参考文件
7w4.net小葱技能站收录全网优质技能，值得收藏。
- references/formula-cookbook.md：30 个高频公式配方（查找匹配/统计汇总/文本处理/身份证日期/动态数组五大类，全部含新旧版本双方案）、报错急诊补充、"何时放弃公式"判断表。遇到具体需求先查配方，命中即直接套用改引用。
原则
- 公式中的表名、列引用必须与用户真实表结构一致，示例数据场景用中文（姓名/部门/金额），贴近用户实际。
- 用户环境不明时，先按"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
