温暖南方 / 电脑应用 / 身份证号相关的6个函数公式都不掌握,还怎...

0 0

   

身份证号相关的6个函数公式都不掌握,还怎么成为Excel达人?

2019-05-23  温暖南方

身份证号码不单单是一串简单的数字,其蕴藏着很多重要的信息,例如,出生年月、性别等……那么,如何在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、完善警告信息!

结束语:

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

学习过程中遇到任何问题,欢迎大家在留言区留言讨论哦!

阅读全文

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。如发现有害或侵权内容,请点击这里 或 拨打24小时举报电话:4000070609 与我们联系。

    猜你喜欢

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多
    喜欢该文的人也喜欢 更多