工程师必备的20个Excel函数,收藏这一篇就够了

透视表拖拽再快，异常折算还是 SUMIFS；仪表盘再炫，KPI 也是 =良品/投入；条件格式超标自动变红，核心还是 =OR(值<下限, 值>上限)。

所以这篇把日常工作真正高频的 20 个函数整理成速查清单，7 大类，每个给"一句话用途 + 可直接抄的公式 + 常见坑"。不堆概念，都是我在良率分析、客诉追溯、周报、8D 里真实用过的。存到收藏夹，要用的时候翻一眼就行。

【解决方案概览】

按"你遇到什么问题"分 7 类：

一个工程师 90% 的活，这 20 个够用了。

【分步实操】

一、统计三兄弟：SUMIFS / COUNTIFS / AVERAGEIFS

这是最该先练熟的一组。 良率分析、批次汇总，全靠它们。

' 按产品汇总良品数=SUMIFS(良品列, 产品列, "PN-2048")' 统计某机台的 FAIL 批次数（多条件）=COUNTIFS(判定列, "FAIL", 机台列, "L03")' 某工段平均良率=AVERAGEIFS(良率列, 工段列, "Wafer")

要点：先写求和范围，再条件对成对写。条件可以引用单元格（D5）而不是写死字符串，这样下拉填充能复用。区域务必用 $ 锁死：$F$2:$F$97。

二、查找：VLOOKUP / XLOOKUP / INDEX+MATCH

从规格表把参数抓过来。三条工具各有分工：

' VLOOKUP：老版本通吃，只能从左往右查，第4参数必须写 0（精确匹配）=VLOOKUP(A2, 规格表!A:D, 3, 0)' XLOOKUP：365/2021+，能反向查、能给默认值=XLOOKUP(A2, 源!B:B, 源!A:A, "未找到")' INDEX+MATCH：想怎么查怎么查，双向都行=INDEX(结果列, MATCH(值, 查找列, 0))

车间老电脑用 VLOOKUP 兜底；自己电脑用 XLOOKUP 省事。永远别漏精确匹配参数，漏了就变"近似匹配"，查不到时返回错行数据，还不一定报错——这是最阴的一类问题。

三、逻辑：IF / IFS / IFERROR

' 单条件判定=IF(良率>=0.9, "达标", "不达标")' 多档评级，末层必须 TRUE 兜底=IFS(良率>=0.95,"优", 良率>=0.9,"良", 良率>=0.85,"合格", TRUE,"差")' 查不到时别让 #N/A 裸奔=IFERROR(VLOOKUP(...), "未找到")

IFS 的 TRUE 兜底别省，否则前面都不满足时会返回 #N/A。IFERROR 也不要到处乱包——它把真正的公式错误一起吞了，排错时你会抓瞎。

四、文本：TEXT / LEFT / MID / FIND

批次号 LOT2026-0927-A03 要拆出年份、流水号？这组就干这个。

=LEFT(批次号, 4)              ' 取前缀 LOT2...→LOT2=MID(批次号, 5, 4)            ' 从第5位取4个字符=FIND("-", 批次号)            ' 定位分隔符位置（区分大小写）=TEXT(NOW(), "yyyy-mm-dd")    ' 日期格式化显示

TEXT 转出来的是文本，不能拿去算数——这是最常见的新手坑。想算就用真正的日期/数值格式，别转文本。

五、日期：TODAY / EOMONTH / NETWORKDAYS

=TODAY()                      ' 今天（随系统变，不是定值）=EOMONTH(日期, 0)             ' 当月最后一天；-1=上月，1=下月=NETWORKDAYS(开始, 结束)       ' 两个日期之间的工作日数

月度良率截止、客诉响应时效、排程工期，都靠这组。NETWORKDAYS 默认不含周末，要含法定节假日得加第三参数（假日区）。

六、数学：SUMPRODUCT / ROUND

' 条件数组求和：算 L03 机台的不良总数=SUMPRODUCT((机台列="L03") * 不良列)' 四舍五入（注意：显示精度 ≠ 存储精度）=ROUND(良率, 4)

SUMPRODUCT 是"不用数组三键的高级玩法"，各区域大小必须一致。ROUND 别和单元格百分比格式搞混：格式是显示层，ROUND 是改真值。

七、动态数组：UNIQUE / FILTER（365 / 2021+）

=UNIQUE(产品列)                          ' 一键去重清单=FILTER(数据区, 机台列="L03", "无结果")   ' 条件筛选整块表

这两个是新一代 Excel 的效率怪兽，一个公式替代以前的"辅助列+透视"。缺点：老版本不支持，发给同事前确认他的 Excel 版本。

【关键参数说明】

几个通用铁律，比背函数本身更重要：

• $ 绝对引用：$F$2:$F$97。不锁，公式一拖拽、一切片器刷新，引用就飘到别处去了。
• 精确匹配参数：VLOOKUP 第 4 参、MATCH 第 3 参，一律写 0 或 FALSE。漏写 = 近似匹配，静默返回错数据。
• 区域大小一致：SUMPRODUCT 里每个区域行数必须相同；SUMIFS 的条件区要和求和区对齐。
• 条件可以是单元格引用：=SUMIFS(F:F, B:B, D5) 比写死 "PN-2048" 灵活，下拉即可批量算。
• 数值 vs 文本：从系统导出的数字常是文本格式，VLOOKUP 查不到、SUMIFS 算成 0。用 VALUE() 或分列转一下。
• 百分比是显示层：0.887 设成百分比格式就显示 88.7%，底层值没变，参与计算的还是 0.887。

【常见问题和避坑提醒】

1. VLOOKUP 查得到却返回错行 → 第 4 参数漏写 0，跑了近似匹配，前提是查找列还得是升序，否则结果不可预测。
2. SUMIFS 算出来是 0 → 数字是文本格式（左上角绿三角），或条件写了全角/空格。
3. IFERROR 包了一切，公式真错了也查不出 → 排错时先拆掉 IFERROR，让错误现形。
4. IFS 报错 #N/A → 没有 TRUE 兜底分支。
5. SUMPRODUCT 结果不对/报错 → 各区域大小不一致，或里面混了文本。
6. TEXT 转出来的日期不能比较大小 → 它已经是文本了。比较请保留真日期。
7. XLOOKUP / UNIQUE / FILTER 同事打开报错 → 他的 Excel 是 2019 或更早。发之前换成 VLOOKUP / INDEX+MATCH 版本。
8. ROUND 了还是显示一长串小数 → 你改的是单元格格式不是 ROUND，或反过来。两者各管一层。

【总结】

Excel 这 20 个函数，撑起了前面整个系列：

• 良率透视分析（#36）→ SUMIFS / AVERAGEIFS
• 多层查找（#37）→ VLOOKUP / XLOOKUP / IFERROR
• SPC 自动标红（#38）→ IF / OR / COUNTIF
• VBA 自动周报（#39）→ SUMIFS / ROUND
• Power Query 合并（#40）→ TEXT / LEFT
• 质量仪表盘（#41）→ SUMIFS + 百分比格式 KPI

不用一次背完。先吃透 SUMIFS + VLOOKUP + IFERROR + COUNTIFS 这 4 个，日常报表就够用；再往上补 XLOOKUP、SUMPRODUCT、动态数组，你的效率会再上一个台阶。

收藏这篇，忘了就回来翻表。

【领取资料】

回复【Excel函数速查表】，领取《工程师20个Excel函数速查表.xlsx》：

• 函数速查表：20 个函数按 7 类排列，每个给一句话用途 + 可直接抄的示例公式 + 适用场景 + 常见坑
• 实战示例：10 行批次数据 + 良率/判定公式，右侧放好 6 个高频函数真公式（SUMIFS / COUNTIFS / VLOOKUP / XLOOKUP / TEXT / ROUND），打开即出数
• 使用说明：怎么改成你自己的数据 + 兼容性提醒

打开就能抄，数据换成你自己的，公式自动重算。