您当前的位置:首页 > 文本与office

和身份证有关的函数公式,你还不掌握?那就真的Out了!

时间:11-09来源:作者:点击数:

关于身份证,每位亲并不陌生,不就是18位的字符串吗?其实,并非如此,身份证号码中包含着大量的秘密,今天,小编带大家逐一解析。

一、身份证技巧——判断重复

函数:IF+Countif

思路:利用Countif函数计算指定列中身份证号码的个数,然后利用IF函数判断Countif函数的返回值,如果>1,则表示重复,否则就是不重复。

方法:

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

解读:

在Excel中,数值的精度为15位,而身份证号码为18位,所以在利用公式=IF(COUNTIF(C$3:C$12,C3)>1,"重复","")计算时,Countif函数只统计前15位的值,如果身份证号码中前15位的值相同,则返回“重复”,这明显和实际情况是不相符的,所以必须在Countif函数的第二个参数,即“条件”的后天添加“*”(星号),将数值型的值强制转化为文本型,达到比较的目的。此坑,你是否也踩过?欢迎在留言区留言讨论哦!

二、身份证技巧——计算年龄

函数:Datedif+Today(+Text+Mid)

思路:常规思维中,年龄就是今年的年份减去出生的年份,其实也没有错,但是不够精准,因为日期除了具体的年份之外,还有月份和日的影响。

方法:

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

解读:

1、计算年龄时除了年份之外,还要考虑月份,所以建议使用Datedif函数,而不是用当前的年份减去出生年份。

2、第二个参数为什么用Today()函数,而不用固定的日期值?年龄时随着日期的变化而变化的,并不是固定不变的,所以用Today()函数获取最新的当前日期,计算最新的年龄值。

3、如果基础数据表中没有出生年月列,可以用Text+MId函数直接在身份证号码中进行提取。

三、身份证技巧——判断性别

函数:IF+Mod+Mid。

思路:学习小学数学知识后,就已经知道,身份证号码中的第17位代表性别,如果为奇数,则为男性,否则为女性。所以利用Mid函数提取第17位的值,然后利用Mod函数求余数,最后用IF函数判断,如果余数为1,则为男性,否则为女性。

方法:

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

四、身份证号技巧——提取出生年月

函数:Text+Mid

思路:身份证号码中从第7位开始,长度为8的字符串代表出生年月,所以可以用Mid函数进行提取,但如果不进行处理,提取的就是8位数字,所以要用Text函数将其处理成日期格式。

方法:

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

五、身份证技巧——退休时间

函数:Edate

思路:退休年龄就是当你达到年龄到达指定的岁数时,进行退休,如果男60,女50岁退休,就是在出生日期的基础上加上60(720个月)年或50年(600个月)。

方法:

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

解读:

如果为男性,公式:=EDATE(D3,MOD(MID(C3,17,1),2)*120+600)就是=EDATE(D3,1*120+600);否则为=EDATE(D3,0*120+600),即在出生日期的后面加上720个月(60岁)或者600个月(50岁)即可。

方便获取更多学习、工作、生活信息请关注本站微信公众号城东书院 微信服务号城东书院 微信订阅号
推荐内容
相关内容
栏目更新
栏目热门