EXCEL身份证函数公式:提取出生年月、性别、计算年龄及退休年龄,防止身份证号重复

2019年6月28日09:37:09 发表评论 586 views
EXCEL身份证函数公式:提取出生年月、性别、计算年龄及退休年龄,防止身份证号重复

身份证号码不单单是一串简单的数字,其蕴藏着很多重要的信息,例如,出生年月、性别等……那么,如何在Excel中提取身份证号码中的出生年月、性别等信息呢?不妨从学习下文开始吧……


一、Text+Mid:提取出生年月。

EXCEL身份证函数公式:提取出生年月、性别、计算年龄及退休年龄,防止身份证号重复

方法:

在目标单元格中输入公式:=TEXT(MID(C3,7,8),"00-00-00")。

解读:

1、身份证号码中从第7位开始,一直到第15位结束为出生年月。

2、Mid函数的作用是:从指定的字符串中提取指定长度的字符。其语法结构为:=Mid(字符串或引用,提取开始位置,字符长度)。

3、Text函数的作用是:对指定的字符串按照指定的格式进行设置。


二、If+Mod+Mid:提取性别。

EXCEL身份证函数公式:提取出生年月、性别、计算年龄及退休年龄,防止身份证号重复

方法:

在目标单元格中输入公式:=IF(MOD(MID(C3,17,1),2),"男","女")。

解读:

1、身份证号码中的第17位代表的为性别,如果÷2余数为1,则为“男”,如果÷2余数为0,则为“女”。

2、首先利用Mid函数提取第17位上的值,然后利用Mod函数求余,最后用If函数判断求余的结果,如果余数为1,则返回“男”,如果余数为“0”,则返回“女”。


三、Datedif函数:计算年龄。

EXCEL身份证函数公式:提取出生年月、性别、计算年龄及退休年龄,防止身份证号重复

方法:

在目标单元格中输入公式:=DATEDIF(D3,TODAY(),"y")。

解读:

1、Datedif函数为系统隐藏函数。其作用为按照指定的统计方式计算两个日期之间的差值。其中“y”为“年”,暨计算两个日期相差的年份。

2、语法结构:=Datedif(开始日期,结束日期,统计方式)。

3、示例中用Today()函数替代“结束日期”。


四、Edate函数:计算退休年龄。

EXCEL身份证函数公式:提取出生年月、性别、计算年龄及退休年龄,防止身份证号重复

方法:

在目标单元格中输入公式:=EDATE(D3,MOD(MID(C3,17,1),2)*120+600)。

解读:

1、Edate函数的作用为:返回一串日期,指示起始日期之前或之后的月份。

2、公式:=EDATE(D3,MOD(MID(C3,17,1),2)*120+600)是按照男同志60岁退休,女同志50岁退休来计算的,具体过程为:首先判断性别,如果为“男”,则在生日的基础上加上1*120+600=720(月),暨60年;如果为“女”,则在生日的基础生加上0*120+600=600(月),暨50年;然后用Edate函数返回生日之后60年或50年的日期。


五、If+Countif:判断身份证号码是否重复。

1、一般方法(错误方法)。

EXCEL身份证函数公式:提取出生年月、性别、计算年龄及退休年龄,防止身份证号重复

方法:

在目标单元格中输入公式:=IF(COUNTIF(C$3:C$9,C3)>1,"重复","")。

解读:

1、从单元格中的身份证号码中可以看出并不重复,而且公式也没有错误,但返回的结果为“重复”,Why???

2、在Excel中,数值的有效位数为15位,暨超过15位的值被视为0处理,而单元格中的身份证号只有后3位不同,被视为0处理,所以返回的结果为“重复”。


2、正确做法:添加“*”。

EXCEL身份证函数公式:提取出生年月、性别、计算年龄及退休年龄,防止身份证号重复

方法:

在目标单元格中输入公式:=IF(COUNTIF(C$3:C$9,C3&"*")>1,"重复","")。

解读:

对两个公式进行比较,发现只是在计数条件的后面添加了“*”,其实在Excel中,“*”被称为通配符,加上通配符的作用就是将当前的数值内容强制转换为文本,然后进行对比得到正确的结果。


六、If+Countif:防止重复。

EXCEL身份证函数公式:提取出生年月、性别、计算年龄及退休年龄,防止身份证号重复

方法:

1、选定目标数据源,【数据】-【数据验证】。

2、选择额【允许】中的【自定义】,并在【公式】中输入:=COUNTIF(C$3:C$9,C3&"*")=1。

3、完善警告信息!


结束语:
此文从提取出生年月、性别、计算年龄及退休年龄,防止身份证号重复等方面做了详细的介绍,是职场办公人员必备的技能之一哦!

作者:Excel函数公式
来源:知乎

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: