15个常用excel函数公式|10个经典的excel小公式一学就会
Hello,大家好,今天跟大家分享10组常用的Excel公式,大家如果在以后遇到类似的问题,直接套用函数即可,让你成为同事眼中的Excel大神!废话不多说,让我们直接开始吧!
一、身份证号码计算出生日期公式:=--TEXT(MID(A2,7,8),"0-00-00")
使用方法:复制公式将A2更改为你表中的单元格位置即可
原理概述:首先利用mid函数将身份证号码中的出生日期提取出来,随后利用text函数将提取出来的字符串设置为日期的显示样式,最后利用2个减号将其设置为常规格式。为什么要使用减号呢?这是因为text函数是文本函数,它的结果也是文本格式,文本格式下的日期是不能使用函数计算的,所以需要转换格式
二、身份证号码计算年龄公式:=DATEDIF(TEXT(MID(A2,7,8),"0-00-00"),TODAY(),"y")
使用方法:复制公式后,只需更改A2为表格中对应的单元格即可
原理概述:首先利用TEXT(MID(A2,7,8),"0-00-00")来提取出出生日期,随后利用DATEDIF来计算出生日期与今天相差了多少年,TODAY()函数的作用是获取今天的日期
三、身份证号码计算性别公式:=IF(MOD(MID(A2,17,1),2)=1,"男","女")
使用方法:复制公式,更改A2为对应的单元格位置即可
原理概述:身份证号码第17位是性别代码,偶数为女性,奇数为男性。首先使用mid函数将17位的数字提取出来,随后利用mod函数判断其奇偶性,最后利用if判断返回对应的结果
四、多条件查询公式:=LOOKUP(1,0/((A2:A8=E2)*(B2:B8=F2)),C2:C8)
使用方法:复制公式更改为对应的单元格区域即可,格式为:=LOOKUP(1,0/(条件1*条件2*条件3),需要返回的结果列)
原理概述:这是一个数组公式,讲解起来比较复杂,主要是利用了逻辑值相乘,可以把true看做是1,false看着是0,两两相乘,结果为1对应的就是我们需要查找的数据
五、通过关键字查找数据公式:=VLOOKUP("*"&D2&"*",$A$1:$B$8,1,FALSE)
使用方法:D2需要更改为表格中对应的查找值,A1:B8需要更改表格中对应的查找区域并绝对引用
原理概述: *号是一个通配符,他表示任意多个字符,我们将查找值得前后分别连接两个星号,这样的话就可以实现通过关键字查找数据
六、反向查找公式:=INDEX(A1:A10,MATCH(E2,B1:B10,0))
使用方法:复制公式替换为自己表中的单元格数据即可,A1:A10是需要返回的结果列,E2是查找值,B1:B10是查找的数据区域
原理概括:所谓的反向查找就是找到查找值左侧的数据,利用vlookup是非常复杂的,但是利用index+match却非常的简单,首先用match函数查找数据位置,随后利用index函数返回对应的结果即可
七、统计重复数据公式:=IF(COUNTIF($A$2:$A$10,A2)=1,"","是")
使用方法:更改A2:A10与A2为你表中对应的单元格位置即可,A2:A10是统计的数据区域,需要进行绝对引用,A2是第一个数据
原理概述:主要是利用countif进行条件计数,如果数据不存在重复,它的计数结果就是1,否则的话结果就大于1,最后利用if函数做一个判断即可
八、统计不重复的个数公式:=SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10))
使用方法:复制公式后,只需要更改COUNTIF函数的第一第二参数为需要统计的数据区域即可
原理概述:首先利用countif函数计算重复值,随后用1除以这个结果,最后利用sumproduct求和,比如一个值重复出现3次,就会得到3个1/3,它们相加后结果是1
九、计算人数公式:=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1
使用方法:更改公式中的B2为你表格中的单元格,在SUBSTITUTE更换为你表格中的分符号。
原理概述:在这里每个姓名都是利用逗号隔开的,首先先计算下每个单元格的字符数,随后利用SUBSTITUTE函数将逗号都替换掉,然后再计算下字符数,两者相减就是逗号的个数,然后再加1就会得到人数
十、拆分姓名与电话提取姓名:=LEFT(A2,LENB(A2)-LEN(A2))
提取电话:=RIGHT(A2,LEN(A2)-(LENB(A2)-LEN(A2)))
使用方法:将公式中的A2替换为自己表格中的单元格位置即可
原理概括:中文在excel中会占据2个字节,而数值仅仅只会占用1个字节,先利用len函数得到字符数,再利用lenb计算字节数,两者相减就会得到汉字的个数,随后利用left或者right函数提取数据即可
以上就是今天分享的所有的Excel公式,大家可以收藏下,以后遇到类似的问题,直接套用即可
相关内容
-
误删除数据恢复的办法|数据恢复实用教程
误删除数据恢复的办法|数据恢复实用教程,数据恢复,实用教程,...
-
win7备份工具排行榜|电脑数据备份软件推荐
win7备份工具排行榜|电脑数据备份软件推荐,数据备份,软件推荐...
-
最好的数据恢复软件|免费的数据恢复软件有哪些
最好的数据恢复软件|免费的数据恢复软件有哪些比较好,数据恢...
-
wps公式大全及使用方法|wpsword表格的计算公式
wps公式大全及使用方法|wpsword表格的计算公式教程,使用方法,...
-
vlookup公式使用方法|vlookup函数的简单易懂的
vlookup公式使用方法|vlookup函数的简单易懂的使用方法,使用...
-
word新建文档快捷键|word常用快捷键大全一览表
word新建文档快捷键|word常用快捷键大全一览表,常用,快捷键大...
-
wps宏的使用方法|wps表格的excel怎么启用宏功能
wps宏的使用方法|wps表格的excel怎么启用宏功能,怎么启用宏,...
-
u盘格式化后数据能恢复吗|优盘格式化后怎么恢复
u盘格式化后数据能恢复吗|优盘格式化后怎么恢复原有文件,恢复...
-
word文档查找快捷键|word最常用的8个通用键
word文档查找快捷键|word最常用的8个通用键,查找,快捷键,通用...
-
wps怎样筛选重复数据|wps怎么快速找出重复项
wps怎样筛选重复数据|wps怎么快速找出重复项,快速,找出,重复...
-
wps表格怎样设置打印区域|excel表格怎么调节打
wps表格怎样设置打印区域|excel表格怎么调节打印区域,区域,打...
-
华为手机如何一键换机|华为旧手机换新手机怎么
华为手机如何一键换机|华为旧手机换新手机怎么转移数据,华为,...
-
wps查找替换在哪|WPS文字查找替换详细教程
wps查找替换在哪|WPS文字查找替换详细教程,查找替换,文字,在...
-
WPS表格怎么求和将表格数据进行求和计算有哪些
WPS表格怎么求和将表格数据进行求和计算有哪些方法,方法,计算...
-
WPS表格中将符合条件的某些特殊数据隐藏附GIF演
WPS表格中将符合条件的某些特殊数据隐藏附GIF演示动画,隐藏,...