Excel萌新：请问Excel常用函数有哪些冷门王炸用法？

千万别学Excel：下面这10个Excel常用函数的冷门用法是真正高手必备的王炸！它们能帮你解决复杂场景的问题！下面听我来给你讲解吧

1. SUMPRODUCT：多维数据计算王炸

- 常规用法：计算数组乘积之和（

"=SUMPRODUCT(A2:A10, B2:B10)"）。

- 王炸用法：

- 条件计数/求和（无需Ctrl+Shift+Enter）：

=SUMPRODUCT((A2:A100="产品A")*(B2:B100>100)) 统计产品A且销量>100的订单数

- 加权平均值计算：

=SUMPRODUCT(B2:B10, C2:C10)/SUM(C2:C10) 数据区域*权重，再除以权重和

- 多对多交叉验证（行列匹配）：

=SUMPRODUCT(($A$2:$A$100=F2)*($B$2:$B$100=G2)*($C$2:$C$100))

同时满足两列条件的求和

⚡ 2. SUMIFS：隐藏的下拉菜单动态求和

- 常规用法：多条件求和（

"=SUMIFS(求和列,条件列1,条件1,...)"）。

- 王炸用法：

- 快速生成动态分类汇总表（无透视表）：

=SUMIFS($C$2:$C$100, $A$2:$A$100, F2, $B$2:$B$100, G1)

行列标题联动，自动扩展区域！

- 跨表动态聚合数据（避开INDIRECT）：

=SUMIFS(INDIRECT(""&A2&"!C:C"), INDIRECT(""&A2&"!A:A"), ">="&B2)

A2为工作表名，自动拼接引用

关键技巧：搭配

"#34;绝对引用区域，行列标题匹配逻辑严密！

3. IF：公式逻辑缓存加速器

- 常规用法：基础条件判断（

"=IF(逻辑,值1,值2)"）。

- 王炸用法：

- 缓存复杂计算（避免重复公式）：

=IF(A1>0, LET(x, SUM(B:B)*0.2, x/(COUNT(C:C)+1)), "N/A")

用LET将SUM(B:B)的结果缓存为x，避免重复计算

- 构建数组逻辑（代替IFS多层嵌套）：

=IF({1,0}, VLOOKUP(A2,数据表,2,0), VLOOKUP(A2,数据表,3,0))

一次返回两列结果，适合INDEX+MATCH复杂场景

4. VLOOKUP：反向查询 + 动态多列提取

- 常规用法：正向查询（

"=VLOOKUP(值,表格区域,列序号,0)"）。

- 王炸用法：

- 打破只能向右查的限制（组合IF{1,0}）：

=VLOOKUP(F2, IF({1,0}, B2:B100, A2:A100), 2, 0)

调换AB列位置，实现向左查询！

- 一次性取多列值（数组公式）：

=VLOOKUP($A2, $C:$F, {2,3,4}, 0)

按Ctrl+Shift+Enter输入，一次性返回3列的值，告别逐个拖拽!

5. FILTER：自动数组动态筛选器

- 常规用法：筛选区域（

"=FILTER(数据范围, 条件)"）。

- 王炸用法：

- 多层级联动筛选（下拉菜单自动联动）：

=FILTER(A2:D100, (B2:B100=G2)*(C2:C100=H2))

G2/H2为下拉菜单选项，秒杀透视表

- 跨表动态合并多表数据：

=FILTER(UNIQUE(VSTACK(表1!A2:A100, 表2!A2:A100)),

UNIQUE(VSTACK(表1!A2:A100, 表2!A2:A100))<>"")

合并两个表的非重复数据源

6. SEARCH+FIND：模糊定位数据清洗神器

- 常规用法：在文本内寻找字符位置。

- 王炸用法：

- 从混乱文本中提取数字（兼容非固定位置）：

=MID(A2, MIN(SEARCH({0,1,2,3,4,5,6,7,8,9}, A2&"0123456789")), LEN(A2))

无视数字出现在文本中的位置，强制提取整段数字

- 精准拆分地址/名称（省市区自动分离）：

=TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",99)), (COLUMN(A1)-1)*99+1, 99))

按分隔符"-"分列，跨版本通用

7. INDEX+MATCH：矩阵数据精准定位

- 常规用法：双条件查找（比VLOOKUP灵活）。

- 王炸用法：

- 二维矩阵匹配取值（替代HLOOKUP+VLOOKUP嵌套）：

=INDEX($B$2:$E$100, MATCH(H2,$A$2:$A$100,0), MATCH(I2,$B$1:$E$1,0))

行列交叉精准定位取值

- 反向匹配+动态区域引用（避开OFFSET不稳定性）：

=INDEX(A:A, MATCH("总计", B:B, 0))

直接返回B列中"总计"所在行的A列值（无视排序）

8. TEXT：日期文本的终极变形术

- 常规用法：格式化数字（

"=TEXT(A2,"0.00%")"）。

- 王炸用法：

- 动态生成月度报表标题：

="截止"&TEXT(EOMONTH(TODAY(),-1),"yyyy年mm月dd日")&"销售汇总"

自动拼接上月最后一天日期到标题中

- 将数值转换为可运算日期：

=--TEXT(A2,"0000-00-00")

将20231205 → 日期格式并可直接加减天数

9. LET：命名中间变量，公式速度飞起来

- 常规用法：变量定义（仅Excel 365支持）。

- 王炸用法：

- 简化超长公式为可读模块：

=LET(

x, FILTER(A2:A100, B2:B100="是"),

y, UNIQUE(x),

TEXTJOIN(", ", TRUE, y)

)

x/y定义中间步骤，结果一目了然，调试简单10倍

- 缓存高频计算值（加速大数据集计算）：

=LET(base, SUM(C2:C1000), IF(A2>A1, base*0.3, base*0.5))

避免重复计算SUM(C2:C1000)

10. XLOOKUP：维度自由的条件聚合

- 常规用法：VLOOKUP的升级版。

- 王炸用法：

- 返回匹配项的多列数据（替代INDEX+MATCH组合）：

=XLOOKUP(F2, A2:A100, B2:D100)

1个函数搞定3列结果返回！不再需要辅助列

- 多对多交叉条件查找（矩阵匹配核心函数）：

=XLOOKUP(1, (A2:A100="产品X")*(B2:B100>50), C2:C100)

同时满足两个条件的值查找，告别数组公式！

核心建议：冷门用法就像被低估的股票——它们平时不起眼，一旦学会就是降维打击职场问题。

千万别学Excel提醒您：现在考考自己是否已掌握了上面讲解的知识点，请做完再看答案哦，答案附在最后：

选择题 1：在需要统计“产品A且销量>100”的订单数时，下列哪个公式能避免数组公式的三键操作？

A.

"=COUNTIFS(A2:A100,"产品A", B2:B100,">100")"

B.

"=SUMPRODUCT((A2:A100="产品A")*(B2:B100>100))"

C.

"=SUMIFS(B2:B100, A2:A100,"产品A", B2:B100,">100")"

D.

"=FILTER(A2:A100, (A2:A100="产品A")*(B2:B100>100))"

选择题 2：要从文本“ID2023-办公用品-¥256”中提取纯数字“256”，以下公式的核心思路是？

=MID(A2, MIN(SEARCH({0,1,2,3,4,5,6,7,8,9}, A2&"0123456789")), LEN(A2))

A. 通过

"SEARCH"定位首个数字位置，再截取后续字符

B. 用

"SEARCH"查找所有符号并替换为空

C. 将文本与数字拼接后删除非数字字符

D. 用

"FIND"精确匹配¥符号后的数字

选择题 3：如何用

"VLOOKUP"实现向左查询“员工工号”对应的“姓名”（原表：A列姓名，B列工号）？

A.

"=VLOOKUP(F2, A:B, 1, 0)"

B.

"=VLOOKUP(F2, B:B, 1, 0)"

C.

"=VLOOKUP(F2, IF({1,0}, B2:B100, A2:A100), 2, 0)"

D.

"=VLOOKUP(F2, CHOOSE({1,2}, B2:B100, A2:A100), 2, 0)"

答案

1. B（SUMPRODUCT条件计数无需三键）

2. A（SEARCH在拼接后的文本中定位首个数字起始位置，MID从该位置截取）

3. C（IF{1,0}重构数组将B列(工号)置于第一列，实现向左查询姓名）

欢迎关注：千万别学excel，这里有数千篇excel相关视频和文章，带你玩转excel！祝你早日升任CEO，迎娶白富美，走上人生巅峰～(￣▽￣～)~