时间:2022-06-12 06:43:19
摘 要:本文在介绍身份证号码组成的基础上,从身份证号码的输入、出生年份的提取、性别的判断这三个步骤阐述了如何在Excel中运用公式函数对身份证号码中的出生年份进行提取,并判断性别。
关键词:身份证号码;函数;出生年份;性别
在工作中,我们常要对身份证号码中的出生年份进行提取,并对性别进行判断,在Excel中可以运用函数进行处理。首先我们来了解身份证号码的组成:旧的身份证号码有15位,新的身份证号码有18位。新增第7、第8、第18三位。排列顺序从左至右依次为:6位数字地址码,8位数字出生日期码,3位数字顺序码和1位数字校验码。其中前两位表示省、自治区或直辖市,第3、第4两位表示所在的市,第5、第6两位表示所在的县区;第7~14位表示出生年月日;第15位至第17位为顺序码,第17位表示性别,一般男的用奇数表示,女的用偶数表示;第18位表示校验码。
一、身份证号码的输入
要对身份证号码进行处理,首先要输入身份证号码。当输入的数字超过11位时,Excel会自动默认为科学记数法。如果我们直接输入身份证号码,Excel会自动转换为科学记数法,所以我们要以文本的方式输入数字。可以在输入数字之前把所要输入数字的单元格设置为文本格式,操作如下:选取要输入数字的单元格,选择“格式”菜单,选择“单元格”命令,然后选择“数字”选项卡,选择“文本”一项,确定。或者在输入的数字前加“’”,Excel会自动把后面的数字以文本输入。
二、出生年份的提取
以身份证号码是18位为例子,数据如上图录入,在A2输入出生年月公式为=MID(C2,7,4),返回值为1980。函数MID是从文字中某一指定起点位值开始,返回指定字符长度的文字串。上面的公式是指C2中从第7位字符开始,取4位数字,所以返回值为1980。
如果身份证号码是15位,则身份证号码变为340524800101001,那出生年月公式为=19&MID(C2,7,2),其中&是文本运算符,起到把文本连接起来的作用,MID(C2,7,2)指从C2中第7位开始取2位数字,返回值为80,19&80,为“1980”。
当一个表格中既含有15位又含有18位数字的身份证号码时,我们可以先对身份证号码的数字长度作一个判断,所用公式为:
=LEN(C2):
结果返回的是C2单元格的数字的位数。我们可以把以上公式综合,无论身份证号码是15位还是18位数字,都可以用以下公式来提取出生年份:
=IF(LEN(C2)=18, MID(C2,7,4), 19&MID(C2,7,2)))
以此类推,提取出生年月的公式为:
=IF(LEN(C2)=18, MID(C2,7,6), 19&MID(C2,7,4)))
……
三、性别的判断
性别的提取相对复杂点,以18位身份证作为例子,其第17位数字表示性别,奇数为男性,偶数为女性。首先我们要通过公式提取第17位数,再通过函数判断是奇数还是偶数,最后通过奇偶数的结果判断性别。仍以上述例子为例,我们一步步来操作:
(1)先提取第17位数字:
=MID(C2,17,1)
这个例子返回值为1。
(2)判断第17位数是奇数还是偶数:
=MOD(MID(C2,17,1),2)
MOD函数返回两数相除的余数,如=MOD(5,2)所指的是返回5除以2的余数,返回值为1。我们可以通过除以2的余数来判断奇偶数,余数为1,为奇数;余数为0,则为偶数。
这个例子17位数为“1”,1除以2余数为1,所以判断的结果为“奇数”。
(3)根据奇偶结果来判断性别。
用IF函数,如果余数为0,返回结果为“女”,余数不是0,返回结果为“男”。
这个例子所判断的结果为“奇数”,所以返回结果为“男”,本例子所用的身份证号码是一个男性的号码。
综合以上,我们可以直接在B2输入公式:
=IF(MOD(MID(C2,17,1),2)=0,"女","男")
如果身份证号码是15位数字,由于第15位是表示性别的,所以公式为:
=IF(MOD(MID(C2,15,1),2)=0,"女","男")
我们把公式综合,无论身份证号码是15位还是18位数字,都可以用以下公式来判断性别:
=IF(LEN(C2)=18,IF(MOD(MID(C2,17,1),2)=0,"女","男"),IF(MOD( MID(C2,15,1),2)=0,"女","男")