20 个高频 Excel 场景，Hy4 把公式和说明一起给了

我提的需求

需求原文：

Hy4 的完整产出

目录 · 点击跳转

1. 开课准备：统一练习数据与全册地图 0.1 为什么要先建「统一练习数据」 0.2 20 个场景速查总表（打印出来贴工位） 0.3 版本兼容对比表（决定你该教哪一个） 0.4 五类问题的选型决策表

• 0.1 为什么要先建「统一练习数据」
• 0.2 20 个场景速查总表（打印出来贴工位）
• 0.3 版本兼容对比表（决定你该教哪一个）
• 0.4 五类问题的选型决策表
• 一、查找类（场景 01–04） 1.0 本类完整模板（照着搭表） 场景 01｜VLOOKUP 精确查找：按订单号带出客户名称 场景 02｜反向查找：知道客户名，反查订单号/编码 场景 03｜多条件查找：客户 + 大区双条件定位唯一记录 场景 04｜一对多查找：把一个客户的所有订单列出来 / 拼成一格 1.5 查找类小结与话术库
• 1.0 本类完整模板（照着搭表）
• 场景 01｜VLOOKUP 精确查找：按订单号带出客户名称
• 场景 02｜反向查找：知道客户名，反查订单号/编码
• 场景 03｜多条件查找：客户 + 大区双条件定位唯一记录
• 场景 04｜一对多查找：把一个客户的所有订单列出来 / 拼成一格
• 1.5 查找类小结与话术库
• 二、条件求和类（场景 05–08） 2.0 本类完整模板（照着搭表） 场景 05｜SUMIF 单条件求和：按客户汇总金额 场景 06｜SUMIFS 多条件求和：客户 + 大区 + 日期区间 场景 07｜SUMPRODUCT：或逻辑、函数条件与旧版兜底 场景 08｜模糊匹配 + 动态月份区间：按关键词与任意月份汇总 2.5 条件求和类小结与话术库
• 2.0 本类完整模板（照着搭表）
• 场景 05｜SUMIF 单条件求和：按客户汇总金额
• 场景 06｜SUMIFS 多条件求和：客户 + 大区 + 日期区间
• 场景 07｜SUMPRODUCT：或逻辑、函数条件与旧版兜底
• 场景 08｜模糊匹配 + 动态月份区间：按关键词与任意月份汇总
• 2.5 条件求和类小结与话术库
• 三、去重计数类（场景 09–12） 3.0 本类完整模板（照着搭表） 场景 09｜单列不重复计数：本月一共有多少个客户 场景 10｜带条件去重计数：华东区有多少个不同客户 场景 11｜提取不重复值清单：生成下拉菜单用的客户列表 场景 12｜标记首次出现 / 判重 / 重复次数 3.5 去重计数类小结与话术库
• 3.0 本类完整模板（照着搭表）
• 场景 09｜单列不重复计数：本月一共有多少个客户
• 场景 10｜带条件去重计数：华东区有多少个不同客户
• 场景 11｜提取不重复值清单：生成下拉菜单用的客户列表
• 场景 12｜标记首次出现 / 判重 / 重复次数
• 3.5 去重计数类小结与话术库
• 四、日期计算类（场景 13–16） 4.0 本类完整模板（照着搭表） 场景 13｜DATEDIF：精确计算工龄 / 账龄「X 年 X 月 X 天」 场景 14｜工作日计算：扣掉周末和法定假日的实际天数 / 到期日 场景 15｜月初 / 月末 / N 个月后：账期与月报的时间轴 场景 16｜日期拆分、季度、星期与中文日期 4.5 日期计算类小结与话术库
• 4.0 本类完整模板（照着搭表）
• 场景 13｜DATEDIF：精确计算工龄 / 账龄「X 年 X 月 X 天」
• 场景 14｜工作日计算：扣掉周末和法定假日的实际天数 / 到期日
• 场景 15｜月初 / 月末 / N 个月后：账期与月报的时间轴
• 场景 16｜日期拆分、季度、星期与中文日期
• 4.5 日期计算类小结与话术库
• 五、文本处理类（场景 17–20） 5.0 本类完整模板（照着搭表） 场景 17｜拆分字符串：订单号取年月、手机号脱敏、按分隔符取第 N 段 场景 18｜合并文本：多列拼地址、按条件把多个订单号拼成一格 场景 19｜清洗脏文本：空格、换行、不可见字符、全半角（VLOOKUP 查不到的元凶） 场景 20｜抽取数字、格式规范化与文本数字互转 5.5 文本处理类小结与话术库
• 5.0 本类完整模板（照着搭表）
• 场景 17｜拆分字符串：订单号取年月、手机号脱敏、按分隔符取第 N 段
• 场景 18｜合并文本：多列拼地址、按条件把多个订单号拼成一格
• 场景 19｜清洗脏文本：空格、换行、不可见字符、全半角（VLOOKUP 查不到的元凶）
• 场景 20｜抽取数字、格式规范化与文本数字互转
• 5.5 文本处理类小结与话术库
• 六、组合套路实战：查找 + 求和 + 去重（月度客户对账看板） 6.1 业务背景与交付目标 6.2 五步搭建法（照着做，每步都有可复制公式） 6.3 365 一键溢出总表（LET + HSTACK，可选进阶） 6.4 组合套路的「三类协同」对照表 6.5 验收对照表（上线前必做） 6.6 落地执行清单（8 步 SOP，可直接贴到项目群）
• 6.1 业务背景与交付目标
• 6.2 五步搭建法（照着做，每步都有可复制公式）
• 6.3 365 一键溢出总表（LET + HSTACK，可选进阶）
• 6.4 组合套路的「三类协同」对照表
• 6.5 验收对照表（上线前必做）
• 6.6 落地执行清单（8 步 SOP，可直接贴到项目群）
• 七、附录 7.1 错误值速查表（打印版） 7.2 公式自检 10 条（写完公式念一遍） 7.3 全册「保底写法」速查（公司版本杂时只背这一列） 7.4 讲师逐字稿：开场 / 过渡 / 收尾 7.5 学员答疑话术库（可直接复制发送） 7.6 课后作业与评分标准
• 7.1 错误值速查表（打印版）
• 7.2 公式自检 10 条（写完公式念一遍）
• 7.3 全册「保底写法」速查（公司版本杂时只背这一列）
• 7.4 讲师逐字稿：开场 / 过渡 / 收尾
• 7.5 学员答疑话术库（可直接复制发送）
• 7.6 课后作业与评分标准
• 八、一页纸总结

Excel 日常办公高频公式 20 场景 · 培训师手册

定位 ：一套能直接照着讲课、照着搭表、照着复制公式、照着发工作群的落地材料。 覆盖 ：查找、条件求和、去重计数、日期计算、文本处理（各 4 个场景，共 20 个）。 适用版本 ：Excel 2016 / 2019 / 2021 / Microsoft 365 / WPS 表格。凡涉及 365 新函数，均给出 旧版替代写法 。 符号约定 ：公式一律写在独立的深色代码块中， 可整行复制 ； $ 表示绝对引用（下拉不跑偏）；若因区域设置提示公式有误，请把公式里的 , 换成 ; （欧洲/部分中文区域设置）。

0. 开课准备：统一练习数据与全册地图

0.1 为什么要先建「统一练习数据」

培训班最常见的翻车点，是每个人的表结构不一样，公式抄过去全报错。所以 第一节课先花 5 分钟，让全班把表建成一模一样 。下面这两张表贯穿全册 20 个场景，编号、列序、字段含义都不变。

表 1： Sheet1 销售订单明细（数据区 A2:H101 ，共 100 行）

列 字段名 类型 示例值 在公式中的角色 A 订单号 文本 DD-2024-0301 查找值 / 去重对象 B 下单日期 真日期 2024/3/7 日期区间条件 C 客户名称 文本 华东商贸 查找值 / 分组条件 / 去重对象 D 大区 文本 华东 多条件筛选 E 产品名称 文本 笔记本电脑 模糊匹配 / 去重对象 F 数量 数值 3 计算源 G 单价 数值 5200 计算源 H 金额 数值 15600 求和区（= F*G ）

表 2： Sheet2 产品参数表（数据区 A2:D21 ）

列 字段名 示例值 A 产品编码 P001 B 产品名称 笔记本电脑 C 含税单价 5200 D 账期天数 30

表 3： Sheet3 参数与输出区（放条件、放结果）

单元格 用途 示例 B1 统计月份（该月 1 日） 2024/3/1 B2 目标客户 华东商贸 B3 目标大区 华东 F1:F30 输出的客户清单 由公式溢出生成 K2:K10 法定节假日列表 2024/4/4 等

1. 日期列必须是 真日期 ：输入 2024/3/7 后单元格默认右对齐；左对齐说明是文本，后面所有日期公式都会 #VALUE! 。
2. 数据区 不要合并单元格、不要插空行、不要放小计行 ，否则 FILTER / UNIQUE / SUMPRODUCT 全部错位。
3. 原始数据区 只放数据 ，公式和结果一律放 Sheet3 ，养成「源数据—参数—输出」三层分离的习惯。

搭表三条纪律 （写进课件第一页） 1. 日期列必须是 真日期 ：输入 2024/3/7 后单元格默认右对齐；左对齐说明是文本，后面所有日期公式都会 #VALUE! 。 2. 数据区 不要合并单元格、不要插空行、不要放小计行 ，否则 FILTER / UNIQUE / SUMPRODUCT 全部错位。 3. 原始数据区 只放数据 ，公式和结果一律放 Sheet3 ，养成「源数据—参数—输出」三层分离的习惯。

0.2 20 个场景速查总表（打印出来贴工位）

编号 类别 场景一句话 首选公式 旧版/备用公式 最低版本 01 查找 按订单号精确带出客户名称 VLOOKUP INDEX+MATCH 全版本 02 查找 从右往左反向查找 XLOOKUP INDEX+MATCH / LOOKUP(1,0/…) 365 / 全版本 03 查找 客户+大区双条件定位 XLOOKUP 布尔乘法 INDEX+MATCH 拼接 CSE 365 / 全版本 04 查找 一个客户返回全部订单（一对多） FILTER + TEXTJOIN INDEX+SMALL+IF CSE 365 / 全版本 05 条件求和 单条件按客户汇总金额 SUMIF SUMPRODUCT 全版本 06 条件求和 客户+大区+日期区间三条件汇总 SUMIFS SUMPRODUCT 2007+ 07 条件求和 「华东或华南」或逻辑、按月汇总 SUMPRODUCT SUM(SUMIFS(...{...})) 全版本 08 条件求和 名称模糊匹配 + 按月初月末动态区间 SUMIFS + EOMONTH SUMPRODUCT+MONTH 2007+ 09 去重计数 统计不重复客户数 COUNTA(UNIQUE()) SUMPRODUCT(1/COUNTIF()) 365 / 全版本 10 去重计数 华东区不重复客户数（带条件去重） ROWS(UNIQUE(FILTER())) SUMPRODUCT(/COUNTIFS()) 365 / 全版本 11 去重计数 直接列出不重复客户清单 SORT(UNIQUE()) INDEX+MATCH+COUNTIF CSE 365 / 全版本 12 去重计数 标记首次/第 N 次出现、判重 扩展区域 COUNTIF 条件格式 / 辅助列 全版本 13 日期计算 精确算工龄「X 年 X 月 X 天」 DATEDIF YEARFRAC 全版本 14 日期计算 扣周末节假日的实际工作日 NETWORKDAYS.INTL SUMPRODUCT+WEEKDAY 2010+ 15 日期计算 取月初/月末、N 个月后同日 EOMONTH / EDATE DATE(YEAR,MONTH+1,0) 2007+ 16 日期计算 拆年月、判季度、中文星期 TEXT / WEEKDAY 自定义单元格格式 全版本 17 文本处理 按分隔符拆字符串、手机号脱敏 TEXTBEFORE/TEXTAFTER LEFT/MID/FIND 365 / 全版本 18 文本处理 多列合并、按条件合并成一串 TEXTJOIN & / CONCATENATE 2019+ / 全版本 19 文本处理 清洗空格、不可见字符、全半角 TRIM+CLEAN+SUBSTITUTE 分列 / Ctrl+H 全版本 20 文本处理 从混杂文本中抽数字、补前导零 TEXTJOIN+MID 数组 / TEXT Ctrl+E 快速填充 365 / 全版本

0.3 版本兼容对比表（决定你该教哪一个）

函数 2016 2019 2021 365 WPS 备注 VLOOKUP / SUMIF(S) / COUNTIF(S) ✅ ✅ ✅ ✅ ✅ 全版本通用，必修 INDEX+MATCH / SUMPRODUCT ✅ ✅ ✅ ✅ ✅ 旧版万能兜底 IFERROR ✅ ✅ ✅ ✅ ✅ 2007+ TEXTJOIN / CONCAT ❌ ✅ ✅ ✅ ✅ 2019 起 IFS / MAXIFS ❌ ✅ ✅ ✅ ✅ 2019 起 XLOOKUP / FILTER / UNIQUE / SORT / SEQUENCE ❌ ❌ ✅ ✅ 部分版本 动态数组 ，会溢出 TEXTBEFORE / TEXTAFTER / TEXTSPLIT ❌ ❌ ❌ ✅ ❌ 仅 365 LET / LAMBDA / HSTACK ❌ ❌ ❌ ✅ ❌ 仅 365 数组公式需 Ctrl+Shift+Enter 需要 需要 不需要 不需要 视版本 旧版讲 CSE 必演示

选型决策一句话 ： 公司电脑版本杂 → 教 INDEX+MATCH + SUMPRODUCT ；全员 365 → 直接上 XLOOKUP + FILTER + UNIQUE 。课堂上两套都讲，但让学员只背一套「保底写法」。

0.4 五类问题的选型决策表

你的问题里出现这些词 归到哪一类 第一反应公式 「带过来」「匹配」「查一下」「对应的是」 查找 XLOOKUP 「汇总」「合计」「总计」「按…统计金额」 条件求和 SUMIFS 「有多少个不同的」「不重复」「首次」「判重」 去重计数 UNIQUE 「多少天」「几个月」「到期」「星期几」「第几季度」 日期计算 DATEDIF / EOMONTH 「拆开」「合并」「去掉空格」「提取数字」 文本处理 TEXTSPLIT / TEXTJOIN

🎤 开课话术（可直接照念）

「今天不背 400 个函数，只解决 20 个你明天上班就会碰到的问题。每个问题我给你三样东西：一句能复制的公式、一张能照抄的表、一段能直接发给同事的解释。公式记不住没关系，记住这张速查表贴在哪就行。」

📋 可直接复制的落地话术（发工作群 / 写进 SOP）

「以后所有汇总表统一按『源数据 / 参数 / 输出』三层建，源数据区不合并单元格、不插空行、不手工改数；公式统一放输出区，改条件只改参数单元格，不改公式。」

一、查找类（场景 01–04）

1.0 本类完整模板（照着搭表）

输入区 ： Sheet3!B2 放要查的值（如订单号 DD-2024-0301 ）。 数据区 ： Sheet1!$A$2:$H$101 （查找列必须在最左，或用 INDEX+MATCH 解除这个限制）。 输出区 ： Sheet3!C2 起放结果。 标准骨架（三选一，全册通用） ：

=VLOOKUP($B$2,$A$2:$H$101,3,0) =INDEX($C$2:$C$101,MATCH($B$2,$A$2:$A$101,0)) =XLOOKUP($B$2,$A$2:$A$101,$C$2:$C$101,"未找到",0)

三类查找方案横向对比表

维度 VLOOKUP INDEX+MATCH XLOOKUP FILTER 能否向左查 ❌ ✅ ✅ ✅ 插入列后是否错位 ❌ 列号写死 ✅ ✅ ✅ 多条件 需辅助列 需数组拼接 ✅ 布尔乘法 ✅ 原生 返回多个结果 ❌ 只返第一个 ❌ ❌（默认） ✅ 自带找不到容错 ❌ 要套 IFERROR ❌ ✅ 第 4 参数 ✅ 第 3 参数 版本要求 全版本 全版本 365/2021 365/2021 推荐度 会看、会维护即可 旧版保底首选 新版首选 一对多首选

场景 01｜VLOOKUP 精确查找：按订单号带出客户名称

一句话问题描述 ：手上有一个订单号，要从明细表里把这个订单对应的客户名称自动带出来。

可直接复制的公式

=IFERROR(VLOOKUP($B$2,$A$2:$H$101,3,0),"未找到该订单")

下拉批量版（把 B2 换成每一行的订单号）： =IFERROR(VLOOKUP($A2,$A$2:$H$101,3,0),"")

具体示例

参数单元格 值 公式 返回结果 B2 DD-2024-0301 上式 华东商贸 B2 DD-2024-0999 上式 未找到该订单 B2 DD-2024-0301 （尾部多一个空格） 上式（未套 TRIM） #N/A → 被 IFERROR 吞成「未找到」

参数说明

参数位 本例写法 含义 易错点 第 1 参 lookup_value $B$2 要找的值 必须与数据区首列类型一致 ：文本对文本、数字对数字 第 2 参 table_array $A$2:$H$101 查找区域 查找列必须是这个区域的第一列 ；务必加 $ 绝对引用 第 3 参 col_index_num 3 返回区域内第 3 列（即 C 列客户名称） 数的是 区域内的相对列号 ，不是工作表列号；超过区域列数 → #REF! 第 4 参 range_lookup 0 0 / FALSE 精确； 1 / TRUE 近似 省略默认近似匹配 ，会返回看似合理实则错误的结果，是头号事故源 外层 IFERROR(…,"…") 把 #N/A 转成友好提示 别用 IF(ISNA(...)) 写两遍，培训时统一教 IFERROR

常见错误与替代写法

现象 / 错误码 根因 一句话修法 替代 / 兼容写法 #N/A 值不存在、有隐藏空格、文本数字 vs 真数字 =VLOOKUP(TRIM($B$2&""),…,3,0) ；数字文本互转用 -- 或 &"" =XLOOKUP($B$2,$A$2:$A$101,$C$2:$C$101,"未找到",0) #REF! 第 3 参大于区域列数，或删了被引用列 改列号；改用 MATCH 动态取列号 =INDEX($A$2:$H$101,MATCH($B$2,$A$2:$A$101,0),MATCH("客户名称",$A$1:$H$1,0)) 结果整体错一行 忘了第 4 参，走了近似匹配 补 ,0) 全册统一写 0 ，不写 FALSE ，省字符 下拉后结果全变 区域没加 $ ，跟着跑 F4 一键加绝对引用 把数据区转成「表格」(Ctrl+T)，用结构化引用 [订单号] 查不到「左边的列」 VLOOKUP 结构性限制 换函数，别硬凑 =INDEX($A$2:$A$101,MATCH($B$2,$C$2:$C$101,0)) （见场景 02） 只返回第一个匹配 设计如此 要全部结果请用 FILTER 见场景 04

🎤 课堂话术

「VLOOKUP 四个参数，你只要记住一句：找谁、在哪找、第几列、要不要精确。第四参数不写等于埋雷——它不报错，它给你一个错的答案，这才最可怕。所以我们的规矩是：宁可多打两个字符 ,0 ，绝不省略。」

📋 可直接复制的落地话术

「这张表的匹配公式统一写成 =IFERROR(VLOOKUP(查找值,绝对引用区域,列号,0),"未找到") ，第四参数必须是 0；查不到先检查两边有没有空格、格式是不是一个文本一个数字，九成问题出在这儿。」

（正文节选，完整模板可在对话里查看。）

用下来的真实感受

• 整篇 10298 字左右，从提问到出稿大概几分钟，中间没再补问 —— 一次说清要求比来回追问省事。
• 免费档不扣积分，这种结构化文档完全够用；缺点是任务越大等得越久，赶时间要提前排。
• 输出可以直接改成自己的版本：把示例里的数字、行业词换掉就能用，不用从零搭框架。
• 表格部分是最好用的，直接复制到文档里改两列就是自己的模板。

#WorkBuddy #Hy4