12组常用Excel函数公式,工作中直接套用,推荐收藏备用

点击下方 ↓ 关注，每天免费看Excel专业教程

置顶公众号或设为星标 ↑ 才能每天及时收到推送

只要你能在工作中用好Excel函数公式，必然事半功倍，但很多人遇到问题却找不到对应的公式，所以本文挑选出常用的12组Excel函数公式，方便大家在工作中直接套用！

下文中的公式涉及多种领域的各种用法，有图示、有公式、有解析，涵盖工作中出现遇到的各种问题，如果你担心用时找不到，推荐收藏备用。

除了本文内容，还想全面、系统、快速提升Excel技能，少走弯路的同学，请搜索微信公众号“跟李锐学Excel”点击底部菜单的“知识店铺”或下方扫码进入

更多不同内容、不同方向的Excel视频课程

长按识别二维码↓获取

（手机微信扫码▲识别图中二维码）

一、从身份证号码中提取出生日期

要求从身份证号码中提取出生年月日，数据很多，下图仅展示部分。

C2单元格输入公式：

=TEXT(MID(B2,7,8),"0-00-00")

公式原理解析：

借助MID函数提取8位出生年月日所在位置，其中4位年份+2位月份+2位日期，再利用TEXT函数自定义显示结果。

二、从身份证号码中提取年龄

要求从身份证号码中提取年龄，数据很多，下图仅展示部分。

C2单元格输入公式：

=DATEDIF(--TEXT(MID(B2,7,8),"0-00-00"),NOW(),"y")

公式原理解析：

借助MID函数提取8位出生年月日所在位置，其中4位年份+2位月份+2位日期，再利用TEXT函数转换为日期格式，作为出生日期；

将出生日期传递给DATEDIF函数，第二参数使用NOW函数提取当前日期时间，第三参数使用y按年计算，返回从出生日期到当前日期的间隔年数，即年龄。

三、从身份证号码中判断性别

要求从身份证号码中判断性别，数据很多，下图仅展示部分。

C2单元格输入公式：

=IF(MOD(MID(B2,17,1),2),"男","女")

公式原理解析：

首先要明确身份证号码中第17位的数字代表男女性别，奇数是男性，偶数是女性；

先借助MID函数从身份证号码中提取出第17位数字，传递给MOD函数；

再利用MOD函数对2求余的规律，对第17位数字判断奇偶；

最后利用IF函数根据MOD函数返回的奇偶判断结果，即返回男女性别。

四、判断身份证号码是否重复

要求判断身份证号码是否存在重复，数据很多，下图仅展示部分。

C2单元格输入公式：

=IF(COUNTIF($B$2:$B$10,B2&"*")>1,"重复","")

公式原理解析：

借助COUNTIF函数实现按条件进行计数统计；

由于身份证号码长度达到18位，所以在公式中连接&"*"将其转换为文本格式进行识别判断；

最后利用IF函数根据COUNTIF函数传递回来的结果进行判断，显示重复或空。

五、统计不重复个数

要求从A列数据中统计出不重复的个数。

C2单元格输入公式：

=SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10))

公式原理解析：

借助COUNTIF函数按条件统计数据个数，这里第二参数的判断条件没有写单值，而是写了一个区域，目的是对此区域中的每一个单元格逐一判断，结果是返回一个数组；

将上述数组中每一个数字取倒数，再交给SUMPRODUCT函数进行汇总求和，即得到不重复数据个数。

六、且关系多条件判断

要求同时满足多条件下进行逻辑判断，数据很多，下图仅展示部分。

自动统计奖金，对同时满足出勤天数不低于25天，且考核得分90分以上的人员给予1000元奖金奖励；

两个条件需同时满足，缺一不可，否则奖金为0。

D2单元格输入公式：

=IF(AND(B2>=25,C2>90),1000,0)

公式原理解析：

借助AND函数进行并列多条件判断，全部条件满足才算满足，否则返回逻辑否；

将AND函数判断结果传递给IF进行结果判断，不再赘述。

七、或关系多条件判断

要求任意满足多条件其中之一，进行逻辑判断，数据很多，下图仅展示部分。

自动统计奖金，对任意满足出勤天数不低于25天，且考核得分90分以上这两个条件其中之一的人员给予200元奖金奖励；

两个条件不必同时满足，择一即可奖励200元，全部条件不满足则奖金为0。

D2单元格输入公式：

=IF(OR(B2>=25,C2>90),200,0)

公式原理解析：

借助OR函数进行并列多条件判断，任意条件满足就算满足，否则返回逻辑否；

将OR函数判断结果传递给IF进行结果判断，不再赘述。

八、按姓名分类汇总求和

要求按姓名统计其业绩汇总，如下图动图所示。

F2单元格输入公式：

=SUMIF(A:A,E2,B:B)

公式原理解析：

条件求和问题常用SUMIF函数解决，此案例为最基础应用，不再赘述。

九、按条件统计个数

要求统计查询姓名出现的次数，如下动图所示。

F2单元格输入公式：

=COUNTIF(A:A,E2)

公式原理解析：

条件计数统计问题常用COUNTIF函数解决，此案例为最基础应用，不再赘述。

十、按条件统计最大值、最小值

要求查询选中的姓名下所有记录中的最大值、最小值。

按姓名条件统计最大值，F2单元格输入公式：

=MAXIFS(B:B,A:A,E2)

按姓名条件统计最小值，G2单元格输入公式：

=MINIFS(B:B,A:A,E2)

公式原理解析：

MAXIFS和MINIFS经常用于条件下的极值提取；

两个函数的参数结构一致，第一参数为返回结果所在区域，第二参数为条件区域，第三参数为判断条件。

十一、屏蔽敏感数据（手机号保护）

工作中经常要对一些敏感数据进行屏蔽，以免信息泄露。

此案例要求保护手机号，将其中4位号码屏蔽为****，数据很多，下图仅展示部分。

C2单元格输入公式：

=REPLACE(B2,4,4,"****")

公式原理解析：

REPLACE函数经常用于文本替换，此案例为基础用法；

第一参数为文本所在位置，第二参数为替换开始位置，第三参数为替换长度，第四参数为替换成什么；

将手机号中从第4位开始后的4位长度替换为****，则完成手机号保护。

十二、去除单位，拆分提取数值

采购价格包含数值和单位，要求从中拆分提取出数值，数据很多，下图仅展示部分。

C2单元格输入公式：

=--LEFT(B2,LEN(B2)-4)

公式原理解析：

首先观察数据源规律，发现数值都在最左侧，后面是单位；

单位都是“元/KG”，长度为4位；

先用LEN函数判断整体数据长度，从中减去4位，得到左侧数值的长度；

将其传递给LEFT函数从左侧提取数值，则得到单价；

最后添加--进行减负运算，将LEFT函数返回的文本数字转换为数值格式，便于后期直接参与各种计算。

希望这篇文章能帮到你！

更多函数公式技术，已整理成超清视频的系统课程，方便你系统提升。

函数公式初级班（扫码↓查看课程大纲）

（手机微信扫码▲识别图中二维码）

函数公式进阶班（扫码↓查看课程大纲）（手机微信扫码▲识别图中二维码）

函数公式中级班（扫码↓查看课程大纲）（手机微信扫码▲识别图中二维码）

函数公式应用班（扫码↓查看课程大纲）（手机微信扫码▲识别图中二维码）

>>推荐阅读 <<

（点击蓝字可直接跳转）