必藏！10个超实用Excel函数公式，关键时刻能“救命”

在Excel数据处理中，常用函数大家都比较熟悉，但遇到一些特殊场景，它们就不够用了。今天给大家分享10个实用却不常见的函数公式。

数据统计公式

不重复个数公式

语法：=SUMPRODUCT(1/COUNTIF(A2:A7,A2:A7))

功能：精准计算指定区域内不重复数据的个数，避免重复统计造成的数据偏差。

示例

求解问题

统计上述表格中不同人物的个数。

公式应用

在B8单元格输入 =SUMPRODUCT(1/COUNTIF(A2:A7,A2:A7))，其中A2:A7为数据区域，涵盖所有待统计人物。

运算结果

4。公式中COUNTIF函数先对A2:A7区域内每个值出现的次数计数，再用1除以这些计数，SUMPRODUCT函数将所得结果求和，巧妙剔除重复项，得出不重复人物个数为4。

中国式排名公式

语法：=SUMPRODUCT(($D$4:$D$9>=D4)*(1/COUNTIF(D$4:D$9,D$4:D$9)))

功能：中国式排名讲究相同数据名次并列，且不占用后续名次，让排名结果更贴合国人习惯。

示例

求解问题

对上述人物按武力值进行中国式排名。

公式应用

在C4单元格输入 =SUMPRODUCT(($D$4:$D$9>=D4)*(1/COUNTIF(D$4:D$9,D$4:D$9)))，D4:D9为武力值数据区域。

运算结果

步骤解析：$D$4:$D$9>=D4部分判断每个武力值是否大于等于当前行武力值，返回TRUE或FALSE，对应1或0；1/COUNTIF(D$4:D$9,D$4:D$9)计算每个武力值出现次数的倒数，两者相乘后SUMPRODUCT求和，实现中国式排名。

文本处理公式

提取字符串任一位中的数字公式

语法：{=LOOKUP(9^9,MID(A1,MATCH(1,MID(A1,ROW(1:9),1)^0,0),ROW(1:9))*1)}（此为数组公式，输入后需按Ctrl + Shift + Enter组合键）

功能：能从复杂字符串中精准揪出隐藏的数字，为文本分析提供便利。

示例

求解问题

从“孙悟空的金箍棒重一万三千五百斤”中提取重量数字。

公式应用

在B1单元格输入 {=LOOKUP(9^9,MID(A1,MATCH(1,MID(A1,ROW(1:9),1)^0,0),ROW(1:9))*1)}，A1为包含文本的单元格。

运算结果

13500。MID函数从A1单元格文本不同位置截取字符，MATCH函数定位首个数字出现位置，LOOKUP函数查找最大数字，通过组合提取出数字13500。

金额大写转换公式

语法：=TEXT(LEFT(RMB(A2),LEN(RMB(A2))-3),"[>0][dbnum2]G/通用格式元;[<0]负[dbnum2]G/通用格式元;;") & TEXT(RIGHT(RMB(A2),2),"[dbnum2]0角0分;;整")

功能：将小写金额一键转换成规范的大写金额形式，满足财务等场景需求。

示例

求解问题

把1234.56转换成大写金额。

公式应用

在B2单元格输入 =TEXT(LEFT(RMB(A2),LEN(RMB(A2))-3),"[>0][dbnum2]G/通用格式元;[<0]负[dbnum2]G/通用格式元;;") & TEXT(RIGHT(RMB(A2),2),"[dbnum2]0角0分;;整")，A2为小写金额所在单元格。

运算结果

壹仟贰佰叁拾肆元伍角陆分。LEFT函数提取金额整数部分，按格式转换；RIGHT函数提取小数部分转换，两者拼接得到大写金额。

查找引用公式

一对多查找包含公式

语法：=COUNT(FIND({"AAA花苑", "CCC龙廷" },A2))

功能：在海量数据里，快速找出包含特定文本的记录，实现一对多高效查找。

示例

求解问题

查找人物住址中包含“AAA花苑”或“CCC龙廷”的记录。

公式应用

在B2单元格输入 =COUNT(FIND({"AAA花苑", "CCC龙廷" },A2))，A2为住址数据区域首个单元格。

运算结果

步骤解析：FIND函数逐个查找{"AAA花苑", "CCC龙廷"}里的文本在A2单元格中的位置，找到返回起始位置数字，未找到返回错误值，COUNT函数统计非错误值个数，即包含特定文本的次数。

Vlookup多表查找公式

语法（方法一）：=IFERROR(VLOOKUP(A2,服务!A:G,7,0), IFERROR(VLOOKUP(A2,人事!A:G,7,0), IFERROR(VLOOKUP(A2,综合!A:G,7,0), IFERROR(VLOOKUP(A2,财务!A:G,7,0), IFERROR(VLOOKUP(A2,销售!A:G,7,0) ,"无此人信息")))))

语法（方法二）：=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({"销售";"服务";"人事";"综合";"财务"}&"! a:a"),A2 ),{"销售";"服务";"人事";"综合";"财务"})&"!a:g"),7,0)

功能：跨多个工作表查询数据，精准定位所需信息，告别繁琐的手动切换查找。

示例

假设存在“服务”“人事”“综合”“财务”“销售”五个工作表，都有A列姓名和G列基本工资。

求解问题

根据姓名“孙悟空”在多个工作表中查找基本工资。

公式应用（方法一）

在B2单元格输入 =IFERROR(VLOOKUP(A2,服务!A:G,7,0), IFERROR(VLOOKUP(A2,人事!A:G,7,0), IFERROR(VLOOKUP(A2,综合!A:G,7, (VLOOKUP(A2,综合!A:G,7,0), IFERROR(VLOOKUP(A2,财务!A:G,7,0), IFERROR(VLOOKUP(A2,销售!A:G,7,0) ,"无此人信息")))))`，A2为姓名所在单元格。

公式应用（方法二）

在B2单元格输入 =VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({"销售";"服务";"人事";"综合";"财务"}&"! a:a"),A2 ),{"销售";"服务";"人事";"综合";"财务"})&"!a:g"),7,0)，A2为姓名所在单元格。

运算结果

假设在“服务”表中孙悟空基本工资为5000，使用公式后B2单元格显示5000。方法一中，IFERROR函数嵌套VLOOKUP依次在各表查找，找到返回值，未找到继续下一个表查找；方法二中，LOOKUP结合INDIRECT函数先确定目标表，再用VLOOKUP查找。

其他实用公式

Sumif多表求和公式

语法：当工作表名称不规则时，借助宏表函数定义名称“sh”（=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,99)&T(now())），使用公式 =SUMPRODUCT((SUMIF(INDIRECT(sh&"!b:b"),D2,INDIRECT(sh &"!c:c"))))

功能：应对工作表名称杂乱情况，实现跨多表按条件求和，确保数据统计准确全面。

示例

假设有“一季度销售”“二季度销售”“三季度销售”等不规则命名工作表，B列产品，C列销售额，要统计产品“金箍棒”销售额。

求解问题

汇总各季度表中“金箍棒”的销售额。

公式应用

先按语法定义名称“sh”，再在B2单元格输入 =SUMPRODUCT((SUMIF(INDIRECT(sh&"!b:b"),D2,INDIRECT(sh &"!c:c"))))，D2为产品“金箍棒”所在单元格。

运算结果

假设各季度“金箍棒”销售额分别为1000、1500、2000，汇总结果为4500。宏表函数获取工作表名称，SUMIF函数在各表按条件求和，SUMPRODUCT汇总结果。

筛选后连续序号公式

语法：先插入辅助列输入公式 =1，再在序号列使用 =SUBTOTAL(2,B$1:B2)

功能：筛选数据后，序号仍能保持连续，让数据展示更清晰有序。

示例

求解问题

对筛选后的人物数据生成连续序号。

公式应用

在C2单元格输入 =1，向下填充；在D2单元格输入 =SUBTOTAL(2,B$1:B2)，向下填充。B列数据为人物所在列。

运算结果

假设筛选出“孙悟空”“宋江”，则序号显示为1、2，SUBTOTAL函数根据筛选情况对B列数据计数，生成连续序号。

用QQ聊天的excel公式

语法：=HYPERLINK ("tencent://message/?uin="&C3,"点击联系他")

功能：在Excel表格里直接生成QQ聊天超链接，点击即可开启对话，便捷沟通。

示例

求解问题

为“孙悟空”生成QQ聊天超链接。

公式应用

在C3单元格输入 =HYPERLINK ("tencent://message/?uin="&C3,"点击联系他")，C3为QQ号所在单元格。

运算结果

表格C3单元格出现“点击联系他”超链接，点击可打开与对应QQ号的聊天窗口。

这10个函数公式宛如10把数据处理的金钥匙，能解锁诸多工作难题。建议大家赶紧收藏，有空多练练手。要是觉得此文帮到您了，劳烦点赞、转发，关注我，后续还有更多Excel高能技巧，助您办公一路开挂！