点击下方 ↓ 关注,每天免费看Excel专业教程
置顶公众号或设为星标 ↑ 才能每天及时收到推送
个人微信号 | (ID:LiRuiExcel520)
微信服务号 | 跟李锐学Excel(ID:LiRuiExcel)
微信公众号 | Excel函数与公式(ID:ExcelLiRui)
只要你能在工作中用好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单元格输入公式:
公式原理解析:
借助COUNTIF函数实现按条件进行计数统计;
最后利用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函数返回的文本数字转换为数值格式,便于后期直接参与各种计算。
希望这篇文章能帮到你!
更多函数公式技术,已整理成超清视频的系统课程,方便你系统提升。
函数公式初级班(扫码↓查看课程大纲)
别图中二维码)
函数公式进阶班(扫码↓查看课程大纲)
函数公式中级班(扫码↓查看课程大纲)
函数公式应用班(扫码↓查看课程大纲)
>><<
VLOOKUP遇到她,瞬间秒成渣!
99%的财务会计都会用到的表格转换技术
86%的人都撑不到90秒,这条万能公式简直有毒!
最有用最常用最实用10种Excel查询通用公式,看完已经赢了一半人
以一当十:财务中10种最偷懒的Excel批量操作
为什么要用Excel数据透视表?这是我见过最好的答案
如此精简的公式,却刷新了我对Excel的认知…
错把油门当刹车的十大Excel车祸现场,最后一个亮了…
让人脑洞大开的VLOOKUP,竟然还有这种操作!
Excel动态数据透视表,你会吗?
让VLOOKUP如虎添翼的三种扩展用法
这个Excel万能公式轻松KO四大难题,就是这么简单!
SUM函数到底有多强大,你真的不知道!
老学员随时复学小贴士
由于有的老学员是4年前购买的课程,因买过的课程较多或因时间久忘记从哪里听课,所以专门将各平台的已购课程入口统一整理至下图。
1、搜索微信公众号“跟李锐学Excel已购课程”,即可查看到你在各平台的已购课程,方便大家找到并随时复学课程。
2、课程分销推广的奖金也是由此公众号转账至大家的微信钱包( 关注后可自动收钱,进入你的微信零钱,在微信支付有转账记录),老学员可以进“ 知识店铺推广赚钱”或者“我的”-“推广中心”查询到推广奖励明细记录,支持主动提现。
此外,里面还有小助手的联系方式,有问题或学习需求可以留言反馈,助手在24小时内回给到回复。
请把这个公众号推荐给你的朋友:)
今天就先到这里吧,更多干货文章加下方小助手查看。
如果你喜欢这篇文章
欢迎点个在看,分享转发到朋友圈
干货教程 · 信息分享
关注微信公众号(ExcelLiRui),每天有干货
关注后置顶公众号或设为星标
再也不用担心收不到干货文章了
▼
关注后每天都可以收到Excel干货教程
请把这个公众号推荐给你的朋友
全面、专业、系统提升Excel实战技能返回搜狐,查看更多
责任编辑: