用好身份证中的信息

2014-04-29 03:15沈海燕
电脑迷 2014年16期
关键词:身份证号号码身份证

沈海燕

我们知道,身份证号码包含着很多重要的个人信息,比如,性别、年龄、生日、所在地。在Excel2013中,我们可以借助函数方便地从身份证中提取相关信息。

小知识

中国人民共和国公民第二代身份证号码,按照顺序从左至右依次为:6位数字地址码、8位数字出生年月码、3位数字顺序码和1位数字检验码。

Tips

出生年月所在列的格式要设置为“日期”,选中所在列,右击选择“设置单元格格式”,在“数字”下选择“日期”,再选择一种日期的类型。

提取出生年月

身份证号码中,从第7位开始就是出生年月日,15位身份证号包含6位生日码,18位身份证号包含8位生日码,只要确定身份证号的位数,就可以使用Excel中的函数获取具体的性别。

将身份证号码输入Excel电子表格中,在生日一列中输入如下函数(如图1):

=TEXT(RIGHT(19&MID(B2,7,6+(LEN(B2)=18)*2),8),"0-00-00")+0

这里的B2表示身份证号码所在列,MID(B2,7,6+(LEN(B2)=18)*2)是判断身份证号码的位数,18位取8位,不然则提取6位,如果是6位则自动在前面添加19,最后通过通过text函数将8位日期文本转换成日期格式的数据。

如果身份证号是18位,那提取生日的公式就是:

=TEXT(MID(B2,7,8),"0-00-00")+0

判定个人性别

身份证号码中出生年月的后3位是顺序码,其中奇数表示男性,偶数表示女性,因此可以根据顺序码自动判定性别。

加入身份证号码从B2列开始,则公式应该这么写(如图2):

=IF(MOD(MID(B2,15+(LEN(B2)=18)*2,1),2),"男","女")

这里主要是通过MOD对顺序码进行判断,奇数自动判定为男性,偶数自动判定为女性。

获取真实年龄

我们还可以根据身份证号码获取真实年龄,这里可通过DATEDIF函数从身份证号码中获取真实年龄。

打开Excel文档,在年龄一列,输入如下函数(如图3):

=DATEDIF(TEXT(MID(B2,7,LEN(B2)/2-1),"0-00-00"),NOW(),"y")

识别所在地区

身份证的前6位是地址码,地址码中包含常住户口所在行政区代码,行政区域代码可以到网上搜索获取,比如,江苏省的行政区域代码就是32,北京市的行政区域代码是11,具体可参阅《国家行政区域的划分及省份代码》:http://url.cn/KdZ1TL。

在Excel中创建2个工作表,其中一个是个人信息,另外一个是区域代码(如图4),全选“区域代码”单元格,在“名称框”中输入Province,这样就可以通过身份中的行政代码判定所在省份。

切换到个人信息工作表,在省份所在列中输入(如图5):

=VLOOKUP(LEFT(B2,2),Province,2,0)

借助Province区域代码表,通过函数就能自动配对并识别判定所在省份。

升级老身份证

对于15位的身份证号码,我们可以根据一代身份证省级至二代身份证的规则和算法,将15位身份证号码升级为18位身份证号码。

打开电子表格,在18位身份证号码中输入以下函数(如图6):

=REPLACE(B2,7,,19)&MID("10X98765432",1+MOD(SUMPRODUCT(MID(REPLACE(B2,7,,19),ROW($1:$17),1)*MOD(2^(18-ROW($1:$17)),11)),11),1)

这样我们可以看到,新身份证号码会增加在日期增加19,在最后增加识别码,非常智能。

身份证号码包含的信息,与Excel中函数结合起来应用,可以满足我们更多需求,特别是在统计个人信息时,我们只要有身份证号码,就可以自动获取到她的性别、生日等,可以事半功倍地进行高效办公。

猜你喜欢
身份证号号码身份证
都有身份证
说号码 知颜色
辣椒也有身份证
一个号码,一个故事
老师情
猜出新号码
作品赏析(3)
趣说古人的“身份证”
《网印工业》关于作者投稿同时提供身份证号的通知
这个号码很吉祥