【临研】Excel一些常用函数(二

Excel函数是提升工作效率的核心工具。单个函数的能力有限，但组合使用可以解决复杂的实际问题，本篇将10类最常用的Excel函数组合，包含核心作用、公式拆解和经典案例。

---

第一类：经典查找与引用

1. INDEX + MATCH（VLOOKUP的升级版）

• 核心作用：实现横向、纵向、逆向、双向查找，不受数据表结构限制。

• 解析：

• MATCH负责找位置（在第几行/第几列）。

• INDEX负责根据位置取数。

• 公式模板：=INDEX(返回数据列, MATCH(查找值, 查找列, 0))

• 案例：根据姓名（E1）查找其对应的部门（B列在姓名C列的左边）。=INDEX(B:B, MATCH(E1, C:C, 0))

2. XLOOKUP（新时代万能查找）

• 核心作用：一个函数替代VLOOKUP、HLOOKUP、INDEX+MATCH。如果使用的是Office 365或Excel 2021以上版本，首选这个。

• 解析：直接指定查找数组和返回数组，逻辑最直观。

• 公式模板：=XLOOKUP(查找值, 查找数组, 返回数组, [未找到值])

• 案例：查找“产品A”的价格。=XLOOKUP(“产品A”, A:A, C:C)

---

第二类：条件求和与计数

3. SUMIF + SUMIFS （单条件 vs 多条件）

• 核心作用：对满足条件的单元格求和。

• 解析：

• SUMIF：先找条件，再求和。

• SUMIFS：注意参数顺序不同（求和列在第一位）。

• 公式模板：=SUMIF(条件列, 条件, 求和列)=SUMIFS(求和列, 条件列1, 条件1, 条件列2, 条件2)

4. SUMPRODUCT（多条件加权求和）

• 核心作用：实现多条件求和，且支持数组运算（不需要按Ctrl+Shift+Enter的老版数组公式）。

• 解析：将多个条件相乘（*表示且的关系），最后相加。

• 案例：计算“销售一部”且“业绩>10000”的总金额。=SUMPRODUCT((A:A=“销售一部”) * (B:B>10000), C:C)

---

第三类：逻辑判断与错误处理

5. IF + AND / OR（多条件判断）

• 核心作用：处理复杂的逻辑分支。

• 解析：

• AND(条件1， 条件2)：两个条件同时成立才为真。

• OR(条件1， 条件2)：两个条件只需一个成立就为真。

• 案例：如果年龄>=18且身份为“员工”，则返回“参会”，否则“拒绝”。=IF(AND(A2>=18, B2=“员工”), “参会”, “拒绝”)

6. IFERROR + VLOOKUP（容错处理）

• 核心作用：美化表格，避免显示难看的#N/A错误。

• 解析：如果VLOOKUP找不到结果，则返回指定的值（如空文本或0）。

• 公式模板：=IFERROR(VLOOKUP(查找值， 区域， 列， 0), “”)

---

第四类：文本处理

7. LEFT / MID / RIGHT + FIND / LEN（智能截取）

• 核心作用：提取不固定长度的文本（如提取邮箱中的用户名，或地址中的省份）。

• 解析：先用FIND找到特定字符（如“@”或“省”）的位置，再用截取函数提取。

• 案例：从邮箱地址中提取@前的用户名。=LEFT(A2, FIND(“@”, A2)-1)

8. TEXTJOIN + IF（条件合并）

• 核心作用：将符合某个条件的所有项目合并到一个单元格里。

• 解析：这是Excel 2019以上版本才有的强大功能。

• 案例：将“销售一部”的所有员工姓名合并，用逗号隔开。=TEXTJOIN(“， ”, TRUE, IF(部门列=“销售一部”， 姓名列, “”))注：旧版Excel需按Ctrl+Shift+Enter。

---

第五类：统计与排名

9. SUBTOTAL（筛选后统计）

• 核心作用：对筛选后的数据进行求和、计数、平均值等。

• 解析：第一参数决定功能（9=求和， 3=计数， 101=忽略隐藏行的平均值）。

• 案例：对B列筛选后的数据进行求和。=SUBTOTAL(9, B:B)

10. COUNTIFS + OFFSET（动态排名/高频出现次数统计）

• 核心作用：统计某值在数据集中是第几次出现（用于去重标记）。

• 案例：标记重复出现的第几次。=COUNTIFS($A$2:A2， A2)注：锁头不锁尾，下拉时区域自动扩大，实现累计计数。

---

总结：如何选择组合？

需求场景	推荐组合	理由
查找数据	XLOOKUP  > INDEX+MATCH > VLOOKUP	按效率从高到低排列。新函数最简洁。
条件求和	SUMIFS  / SUMPRODUCT	SUMIFS易读，SUMPRODUCT更灵活。
避免错误值	IFERROR  + 原公式	提升表格专业度。
文本拆分	TEXTSPLIT  (新) / LEFT+FIND (旧)	新版本有专门拆分函数，旧版用组合技。
合并内容	TEXTJOIN	告别&符号的繁琐拼接。

---

下方为所有合集：

DM系列临床试验小游戏

七天玩转VBAVba学习

学习VBAVBA宏

相关合集推荐书籍：

    如果对您有帮助，欢迎关注、留言、点赞、分享、收藏这个公众号，小编会不定期发布手写实用宏以及分享一些写代码过程中的语句，但是真的真的不定期哦！