Skip to main content
 Web开发网 » office教程 » excel教程

成为同事眼中的Excel大神,学会这10组公式就够了,收藏备用吧

2021年11月24日5360百度已收录

Hello,大家好,今天跟大家分享10组常用的Excel公式,大家如果在以后遇到类似的问题,直接套用函数即可,让你成为同事眼中的Excel大神!废话不多说,让我们直接开始吧!

一、身份证号码计算出生日期公式:=--TEXT(MID(A2,7,8),"0-00-00")

使用方法:复制公式将A2更改为你表中的单元格位置即可

原理概述:首先利用mid函数将身份证号码中的出生日期提取出来,随后利用text函数将提取出来的字符串设置为日期的显示样式,最后利用2个减号将其设置为常规格式。为什么要使用减号呢?这是因为text函数是文本函数,它的结果也是文本格式,文本格式下的日期是不能使用函数计算的,所以需要转换格式

成为同事眼中的Excel大神,学会这10组公式就够了,收藏备用吧  excel公式 第1张

二、身份证号码计算年龄公式:=DATEDIF(TEXT(MID(A2,7,8),"0-00-00"),TODAY(),"y")

使用方法:复制公式后,只需更改A2为表格中对应的单元格即可

原理概述:首先利用TEXT(MID(A2,7,8),"0-00-00")来提取出出生日期,随后利用DATEDIF来计算出生日期与今天相差了多少年,TODAY()函数的作用是获取今天的日期

成为同事眼中的Excel大神,学会这10组公式就够了,收藏备用吧  excel公式 第2张

三、身份证号码计算性别公式:=IF(MOD(MID(A2,17,1),2)=1,"男","女")

使用方法:复制公式,更改A2为对应的单元格位置即可

原理概述:身份证号码第17位是性别代码,偶数为女性,奇数为男性。首先使用mid函数将17位的数字提取出来,随后利用mod函数判断其奇偶性,最后利用if判断返回对应的结果

成为同事眼中的Excel大神,学会这10组公式就够了,收藏备用吧  excel公式 第3张

四、多条件查询公式:=LOOKUP(1,0/((A2:A8=E2)*(B2:B8=F2)),C2:C8)

使用方法:复制公式更改为对应的单元格区域即可,格式为:=LOOKUP(1,0/(条件1*条件2*条件3),需要返回的结果列)

原理概述:这是一个数组公式,讲解起来比较复杂,主要是利用了逻辑值相乘,可以把true看做是1,false看着是0,两两相乘,结果为1对应的就是我们需要查找的数据

成为同事眼中的Excel大神,学会这10组公式就够了,收藏备用吧  excel公式 第4张

五、通过关键字查找数据公式:=VLOOKUP("*"&D2&"*",$A$1:$B$8,1,FALSE)

使用方法:D2需要更改为表格中对应的查找值,A1:B8需要更改表格中对应的查找区域并绝对引用

原理概述: *号是一个通配符,他表示任意多个字符,我们将查找值得前后分别连接两个星号,这样的话就可以实现通过关键字查找数据

成为同事眼中的Excel大神,学会这10组公式就够了,收藏备用吧  excel公式 第5张

六、反向查找公式:=INDEX(A1:A10,MATCH(E2,B1:B10,0))

使用方法:复制公式替换为自己表中的单元格数据即可,A1:A10是需要返回的结果列,E2是查找值,B1:B10是查找的数据区域

原理概括:所谓的反向查找就是找到查找值左侧的数据,利用vlookup是非常复杂的,但是利用index+match却非常的简单,首先用match函数查找数据位置,随后利用index函数返回对应的结果即可

成为同事眼中的Excel大神,学会这10组公式就够了,收藏备用吧  excel公式 第6张

七、统计重复数据公式:=IF(COUNTIF($A$2:$A$10,A2)=1,"","是")

使用方法:更改A2:A10与A2为你表中对应的单元格位置即可,A2:A10是统计的数据区域,需要进行绝对引用,A2是第一个数据

原理概述:主要是利用countif进行条件计数,如果数据不存在重复,它的计数结果就是1,否则的话结果就大于1,最后利用if函数做一个判断即可

成为同事眼中的Excel大神,学会这10组公式就够了,收藏备用吧  excel公式 第7张

八、统计不重复的个数公式:=SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10))

使用方法:复制公式后,只需要更改COUNTIF函数的第一第二参数为需要统计的数据区域即可

原理概述:首先利用countif函数计算重复值,随后用1除以这个结果,最后利用sumproduct求和,比如一个值重复出现3次,就会得到3个1/3,它们相加后结果是1

成为同事眼中的Excel大神,学会这10组公式就够了,收藏备用吧  excel公式 第8张

九、计算人数公式:=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1

使用方法:更改公式中的B2为你表格中的单元格,在SUBSTITUTE更换为你表格中的分符号。

原理概述:在这里每个姓名都是利用逗号隔开的,首先先计算下每个单元格的字符数,随后利用SUBSTITUTE函数将逗号都替换掉,然后再计算下字符数,两者相减就是逗号的个数,然后再加1就会得到人数

成为同事眼中的Excel大神,学会这10组公式就够了,收藏备用吧  excel公式 第9张

十、拆分姓名与电话提取姓名:=LEFT(A2,LENB(A2)-LEN(A2))

提取电话:=RIGHT(A2,LEN(A2)-(LENB(A2)-LEN(A2)))

使用方法:将公式中的A2替换为自己表格中的单元格位置即可

原理概括:中文在excel中会占据2个字节,而数值仅仅只会占用1个字节,先利用len函数得到字符数,再利用lenb计算字节数,两者相减就会得到汉字的个数,随后利用left或者right函数提取数据即可

成为同事眼中的Excel大神,学会这10组公式就够了,收藏备用吧  excel公式 第10张

以上就是今天分享的所有的Excel公式,大家可以收藏下,以后遇到类似的问题,直接套用即可

我是Excel从零到一,关注我,持续分享更多Excel技巧

(此处已添加圈子卡片,请到今日头条客户端查看)

评论列表暂无评论
发表评论
微信