12组Excel常用公式,5小时才这里好的,收藏备用吧!

Sadu 关注

收藏于 : 2022-12-02 19:30   被转藏 : 2   

关注我们并标星,不错过每一篇实用教程
Hello,大家好,今天跟大家分享12组Excel公式组合的用法,都是一些工作中比较常见问题,以后如果遇到此类问题,直接套用即可,废话不多说,让我们直接开始吧


1.多条件判断(同时满足)

在这里要求出勤天数大于等于24天,考核得分大于等于85分同时满足才能获得500奖金,否则的话没有奖金,公式为:
                          =  IF  (  AND  (B3>=  24  ,C3>=  85  ),  500  ,  0  )   
在这里将and函数作为了if函数的第一参数,and函数的结果是逻辑值,只有条件全部正确才会返回true,否则的话就会返回false


2.多条件判定(满足一个即可)

如果我们将上述条件更改为出勤天数大于等于24天,考核得分大于等于85分满足任意一个即可获得500奖金,公式为:
                          =  IF  (  OR  (B3>=  24  ,C3>=  85  ),  500  ,  0  )   
OR函数他的结果也是一个逻辑值,不同的是OR函数的特点是只要有一个条件是正确的他就会返回true这个结果,只有条件全部是错的才会返回false这个结果


3.身份证号码提取出生日期

                          公式:=TEXT(MID(B3,  7  ,  8  ),  "0-00-00"    
在这里我们是使用mid函数提取身份证号码中出生年月的位置,然后再利用text函数来设置数字的显示格式,如果你需要使用这个公式,只需要将B3更改为你需要单元格位置即可


4.身份证号码计算性别

                          公式:=  IF  (MOD(MID(B3,  17  ,  1  ),  2  ),  "男"  ,  "女"  )   
身份证号码的第十七位是性别代码,偶数为女性,奇数为男性,在这里先利用mid函数将第十七位提取出来,然后利用mod函数判断奇偶性,最后利用if函数输出结果,如果你需要使用这个函数,只需要将B3单元格更改为你需要的单元格位置即可


5.身份证号码计算年龄

                        公式:=DATEDIF(TEXT(MID(B3,  7  ,  8  ),  "0-00-00"  ),TODAY(),  "Y"  )   
Datedif函数的作用计算两个日期的差值,在这里我们先利用TEXT(MID(B3,7,8),"0-00-00"))将出生日期提取出来将其作为第一参数开始的时间,利用today函数来获取当天的日期作为第二参数,第三参数为y表示计算类型为年,如果需要套用公式,只需更改b3单元格即可


6.身份证号码查找重复

                          公式:=  IF  (B3=  ""  ,  ""  ,  IF  (COUNTIF(  $B   $3:   $B   $9  ,B3&  "*"  )>  1  ,  "重复"  ,  ""  ))   
在这里如果身份证号码不重复,函数会返回空值,如果重复的话就会返回重复两个字,在这里需要注意的是countif函数中的数据区域一定要进行绝对引用


7.统计不重复的个数

                           公式:=SUMPRODUCT(1/COUNTIF(A3:A13,A3:A13))    
首先函数会利用countif函数计算出重复的个数,然后再用1除以这个结果,最后利用sumproduct函数求和,比如一个数据重复3次,那么countif就会得到三个3,然后用1除以countif就会得到三个1/3,相加的结果就是1,想要使用函数只需要将A3:A13替换为你需要的数据区域即可


8.分离单位与数字

                          公式:=LEFT(C3,LEN(C3)-4)   
在这里C3是单元格的位置,4就是单位(kg/元)所占的字符数个数


9.条件求和

比如在这里想要求一下鲁班总和的数据,公式:
                         =SUMIF(A3:A13,D4,B3:B13)    
A3:A13是条件求和判断的数据区域,D4是求和条件,B3:B13是条件求和的区域


10.条件计数

比如在这里想要鲁班姓名出现的次数
                           公式:=COUNTIF(A3:A13,D4)    
A3:A13是条件计数用于判断的数据区域,D4是条件计数条件


11条件求最大值最小值

                           最大值公式:=MAXIFS(B3:B12,A3:A12,D4)       最小值公式:=MINIFS(B3:B12,A3:A12,D4)    
这两个函数的参数都是一样的,第一参数:B3:B12要确定最大值的单元格,第二参数:A3:A12用于判断的数据区域,第三参数:D4判断的条件


12.设置手机号保护

                          公式:=SUBSTITUTE(B3,MID(B3,  4  ,  4  ),  "****"  )   
Subtitute这个函数的作用是将字符串的某一部分替换为另一部分,在这里我们利用mid函数将手机号码中的一部分数据提取数据,然后再替换为四个星号
以上就是今天分享的12个公式组合,公式组合还有很多,你想要学了解哪些,不妨留言告诉我~

往期推荐

贴公式3分钟搞定!" data-recommend-article-content-url="https://mp.weixin.qq.com/s?__biz=MzU2ODc0MjI2OA==&mid=2247506545&idx=1&sn=fa259baf1feac7a42785a30ae7c94cb7&chksm=fc8bc262cbfc4b74519a42e93ca16dc22c1bc87b68060a8fb04442d99f7e002e6b88d2889c67#rd">

生成300个二维码,我用了1天,同事粘公式3分钟搞定!

2022-11-30

Excel多表格神器,3分钟搞定1小时的工作量!偷偷摸鱼到下班

2022-11-29

如果你想要学 习Excel,可以了解下我的课程,仅售39.9,已经更新了123个章节,时长1400分钟,带你快速成为Excel高手

END


不要忘记把我们推荐给你的同事跟朋友呀~
觉得有用,点个“在看”支持下吧~

 阅读文章全部内容  
点击查看
文章点评
相关文章
Sadu 关注

文章收藏:41

TA的最新收藏