身份证号码不单单是一串简单的数字,其蕴藏着很多重要的信息,例如,出生年月、性别等……那么,如何在Excel中提取身份证号码中的出生年月、性别等信息呢?不妨从学习下文开始吧……
一、Text+Mid:提取出生年月。
方法:
在目标单元格中输入公式:=TEXT(MID(C3,7,8),"00-00-00")。
解读:
1、身份证号码中从第7位开始,一直到第15位结束为出生年月。
2、Mid函数的作用是:从指定的字符串中提取指定长度的字符。其语法结构为:=Mid(字符串或引用,提取开始位置,字符长度)。
3、Text函数的作用是:对指定的字符串按照指定的格式进行设置。
二、If+Mod+Mid:提取性别。
方法:
在目标单元格中输入公式:=IF(MOD(MID(C3,17,1),2),"男","女")。
解读:
1、身份证号码中的第17位代表的为性别,如果÷2余数为1,则为“男”,如果÷2余数为0,则为“女”。
2、首先利用Mid函数提取第17位上的值,然后利用Mod函数求余,最后用If函数判断求余的结果,如果余数为1,则返回“男”,如果余数为“0”,则返回“女”。
三、Datedif函数:计算年龄。
方法:
在目标单元格中输入公式:=DATEDIF(D3,TODAY(),"y")。
解读:
1、Datedif函数为系统隐藏函数。其作用为按照指定的统计方式计算两个日期之间的差值。其中“y”为“年”,暨计算两个日期相差的年份。
2、语法结构:=Datedif(开始日期,结束日期,统计方式)。
3、示例中用Today()函数替代“结束日期”。
四、Edate函数:计算退休年龄。
方法:
在目标单元格中输入公式:=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、一般方法(错误方法)。
方法:
在目标单元格中输入公式:=IF(COUNTIF(C$3:C$9,C3)>1,"重复","")。
解读:
1、从单元格中的身份证号码中可以看出并不重复,而且公式也没有错误,但返回的结果为“重复”,Why???
2、在Excel中,数值的有效位数为15位,暨超过15位的值被视为0处理,而单元格中的身份证号只有后3位不同,被视为0处理,所以返回的结果为“重复”。
2、正确做法:添加“*”。
方法:
在目标单元格中输入公式:=IF(COUNTIF(C$3:C$9,C3&"*")>1,"重复","")。
解读:
对两个公式进行比较,发现只是在计数条件的后面添加了“*”,其实在Excel中,“*”被称为通配符,加上通配符的作用就是将当前的数值内容强制转换为文本,然后进行对比得到正确的结果。
六、If+Countif:防止重复。
方法:
1、选定目标数据源,【数据】-【数据验证】。
2、选择额【允许】中的【自定义】,并在【公式】中输入:=COUNTIF(C$3:C$9,C3&"*")=1。
3、完善警告信息!
结束语: 此文从提取出生年月、性别、计算年龄及退休年龄,防止身份证号重复等方面做了详细的介绍,是职场办公人员必备的技能之一哦!
作者:Excel函数公式 来源:知乎