【原】我工作10年，才明白这15组函数公式永远的神，直接套用，建议收藏！

关键字：vlookup；函数；sum

栏目：技巧集锦

全文1493字，预计5分钟读完

哈喽，小伙伴们，你们好呀~

今天跟大家分享工作中经常遇到的15个Excel函数公式。

如果你恰好遇到类似的问题，又不知道怎么写公式？

别急，翻出文章，找到公式，直接套用就可以啦！

不用谢我，请叫我雷锋。

话不多说我们开始吧！

一、提取出生年月

如图所示，根据A列身份证号码提取出生年月=TEXT(MID(A2,7,8),"0-00-00")

二、合并单元格求和

选中数据区域D2:D11，在编辑栏输入公式：=SUM(C2:C$11)-SUM(D3:$D$11)，按Ctrl+Enter结束。

三、多条件汇总

下图所示为某公司员工基本情况登记表，现在需要统计性别为“女”、学历为“本科”的员工的工资总和，可以使用公式：

=SUMIFS(H3:H18,C3:C18,"男",F3:F18,"本科")

四、条件求和

如图，数据源里没有月份只有具体的销售日期，试问你能用SUMIF求和吗？

可以用SUMPRODUCT函数呀！

=SUMPRODUCT((MONTH(A2:A15)=10)*C2:C15)

五、多条件查找

在K6单元格中输入公=VLOOKUP(K4,B:G,MATCH(I4,B3:G3,0),0)

K4单元格是对应条件②的月份值，查找区域为B:G列，查找区域中的列数使用MATCH函数进行判断。

MATCH的对象就是条件①的姓名，查找范围就是B3:G3，查找方式选择0为精确查找。

因为VLOOKUP第三参数返回的列数是根据条件①的姓名动态变化而变化，所以嵌套MATCH函数对条件①姓名进行查找。

六、针对多列的条件求和

如图，要求和的数据位于多列。

公式为：=SUMPRODUCT(($A$2:$A$22=I2)*$C$2:$G$22)

七、字符截取

常用的字符截取类函数有三个，从左边开始截取用LEFT，从右边截取用RIGHT，从中间截取用MID，以下分别举例说明。

公式=LEFT(A2,LENB(A2)-LEN(A2)-1)可以将单元格左边的汉字截取出来。

八、数字截取

公式=LEFT(D2,LEN(D2)*2-LENB(D2))可以将单元格左边的数字截取出来。

九、向右截取

公式=RIGHT(G2,11)可以将单元格右边的手机号截取出来。

公式=RIGHT(J2,LENB(J2)-LEN(J2))可以将单元格右边的数量单位截取出来。

十、统计最高销量

公式为：=MAX(SUBTOTAL(9,OFFSET($A$2,,ROW(1:5),9,)))

要在不知道每周合计的情况下，统计最高周销量就需要用到MAX+SUBTOTAL+OFFSET组合，对于这个组合最大的难点在于没有用SUM去求和而用了SUBTOTAL，原因就在于这个例子中OFFSET得到的是一个多维引用，SUBTOTAL函数支持函数返回的三维引用，故能返回正确结果；SUM函数不支持函数返回的三维引用，故不能使用。

十一、快速统计差异

利用COUNTIF函数核对两列数据的差异，例如根据人员名单在总名单中找出入选的人，如图所示。

输入公式：=COUNTIF($D$2:$D$7,A2)

十二、快速标注重复

如图所示，输入公式：=IF(COUNTIF(A:A,A2)=1,"","重复")

十三、按照提成比例计算提成金额

输入公式：

=B2*LOOKUP(B2,IMREAL($E$2:$E$6&"i"),$F$2:$F$6)

IMREAL($E$2:$E$6&"i")这部分就是业绩范围中“-”左侧的内容。实际上根据复数的表示方法，当我们遇到a+b或者a-b形式的内容，都可以用&符号链接i将数据变成a+bi或者a-bi的形式，再用IMREAL函数来提取他的实部。

十四、统计人数

如图所示，需要统计女性人数。

输入公式=COUNTIF(B:B,G2)

十五：多条件统计

输入公式=COUNTIFS(B:B,G2,C:C,G3)

共有两组条件，B列是对性别进行判断，C列是对学历进行判断。

好啦，以上就是今天给大家分享的15个函数公式啦！

怎么样，你学会了吗？