ChaSfz 巧用Excel:利用身份证号码 提取个人信息

巧用Excel:利用身份证号码 提取个人信息

更新时间:2010/8/3 9:15:37

操作技巧
巧用Excel:利用身份证号码 提取个人信息

一、分析身份证号码
其实,身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的,无论是15位还是18位的身份证号码,其中都保存了相关的个人信息.
15位身份证号码:第7、8位为出生年份(两位数),第9、10位为出生月份,第11、12位代表出生日期,第15位代表性别,奇数为男,偶数为女.
18位身份证号码:第7、8、9、10位为出生年份(四位数),第11、第12位为出生月份,第13、14位代表出生日期,第17位代表性别,奇数为男,偶数为女.
例如,某员工的身份证号码(15位)是320521720807024,那么表示1972年8月7日出生,性别为女.如果能想办法从这些身份证号码中将上述个人信息提取出来,不仅快速简便,而且不容易出错,核对时也只需要对身份证号码进行检查,肯定可以大大提高工作效率.

二、提取个人信息
这里,我们需要使用IF、LEN、MOD、MID、DATE等函数从身份证号码中提取个人信息.如图1所示,其中员工的身份证号码信息已输入完毕(C列),出生年月信息填写在D列,性别信息填写在B列.
1. 提取出生年月信息
由于上交报表时只需要填写出生年月,不需要填写出生日期,因此这里我们只需要关心身份证号码的相应部位即可,即显示为"7208"这样的信息.在D2单元格中输入公式"=IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))",其中:
LEN(C2)=15:检查C2单元格中字符串的字符数目,本例的含义是检查身份证号码的长度是否是15位.
MID(C2,7,4):从C2单元格中字符串的第7位开始提取四位数字,本例中表示提取15位身份证号码的第7、8、9、10位数字.
MID(C2,9,4):从C2单元格中字符串的第9位开始提取四位数字,本例中表示提取18位身份证号码的第9、10、11、12位数字.
IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4)):IF是一个逻辑判断函数,表示如果C2单元格是15位,则提取第7位开始的四位数字,如果不是15位则提取自第9位开始的四位数字.
如果需要显示为"70年12月"这样的格式,请使用DATE格式,并在"单元格格式→日期"中进行设置.
2. 提取性别信息
由于报表中各位员工的序号编排是按照上级核定的编制进行的,因此不可能按照男、女固定的顺序进行编排,如果一个一个手工输入的话,既麻烦又容易出错
例如性别信息统一在B列填写,可以在B2单元格中输入公式"=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,"男","女")",其中:
LEN(C2)=15:检查身份证号码的长度是否是15位.
MID(C2,15,1):如果身份证号码的长度是15位,那么提取第15位的数字.
MID(C2,17,1):如果身份证号码的长度不是15位,即18位身份证号码,那么应该提取第17位的数字.
MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2):用于得到给出数字除以指定数字后的余数,本例表示对提出来的数值除以2以后所得到的余数.
IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,"男","女"):如果除以2以后的余数是1,那么B2单元格显示为"男",否则显示为"女".(Leland注:据判断,奇数为男,偶数为女.0和X根据算法求出性别.)
回车确认后,即可在B2单元格显示正确的性别信息,接下来就是选中填充柄直接拖曳.如图2所示,现在这份报表无论是提取信息或是核对,都方便多了!

Excel中如何通过身份证来自动输入生日?
这个问题遇到好几回了,可是就是懒得找方法,一步一步的人工填.今天突然要填100多人的生日,差点没愁死我.好在前些日子在杂志上看过类似的应用,就在网上找了一找,哈哈,问题解决!方法如下:
如果身份证号在A2单元格中,在要输入生日的单元格编辑公式(不要两边的双引号):
"=IF(LEN(A2)=15,MID(A2,7,2)&-MID(A2,9,2)&-MID(A2,11,2),MID(A2,7,4)&-MID(A2,11,2)&-MID(A2,13,2))"
解释:首先是IF语句的判断,如果身份证是15位的,执行 MID(A2,7,2)&-MID(A2,9,2)&-MID(A2,11,2),如果不是,则执行 MID(A2,7,4)&-MID(A2,11,2)&-MID(A2,13,2).其中MID(A2,7,2)的的意思是,在A2单元格中的数据,从第7为开始取数值,只取两位.
Leland略有改动:统计工资的话,员工应该都是2000年前出生的,15位的年代加19;2000以后的新身份证都是18位的.
Leland例举图表(身份证号为自编的,仅供制表用,下同):
公式:ID号:A2起,B2=IF(LEN(A2)=15,19&MID(A2,7,2)&-MID(A2,9,2)&-MID(A2,11,2),MID(A2,7,4)&-MID(A2,11,2)&-MID(A2,13,2))
Q:如果有关于身份证号的地址码,出生日期码,顺序码,识别码,怎样在Excel中生成身份证号?(相关术语请参见下面"身份证号码的构成")
A:设地址码A2,出生日期码B2,顺序码C2,识别码D2,身份证号E2=A2&B2&C2&D2
查询身份证号的正则表达式为:\d{15}$|\d{18}$|\d{17}[xX]
(emeditor 中,需要单独一行,或在行末,才能用正则表达式查询出身份证号)
网易用脚本校正身份证号部分代码为:
function fIdQuery(){
var value = $("id").value;
if(!(/^\d{15}$|^\d{18}$|^\d{17}[xX]$/.test(value))){
alert("请输入15位或者18位身份证号码");
$("id").focus();
return false;
通过身份证号码查询户籍所在地、生日、性别的页面→网易百宝箱>小工具集:
大陆的身份证为18位,老的身份证是15位.
关于身份证第18是怎么计算的,原理如下:根据〖中华人民共和国国家标准 GB 11643-1999〗中有关公民身份号码的规定,公民身份号码是特征组合码,由十七位数字本体码和一位数字校验码组成.排列顺序从左至右依次为:六位数字地址码,八位数字出生日期码,三位数字顺序码和一位数字校验码.
地址码(身份证前六位)表示编码对象常住户口所在县(市、旗、区)的行政区划代码.(所有区域的编码可以到网站查询到最新的县及县以上的行政编码资料.)

生日期码(身份证第七位到第十四位)表示编码对象出生的年、月、日,其中年份用四位数字表示,年、月、日之间不用分隔符.例如:1981年05月11日就用19810511表示.
顺序码(身份证第十五位到十七位)为同一地址码所标识的区域范围内,对同年、月、日出生的人员编定的顺序号.其中第十七位奇数分给男性,偶数分给女性.
校验码(身份证最后一位)是根据前面十七位数字码,按照ISO 7064:1983.MOD 11-2校验码计算出来的检验码.
第十八位数字的计算方法为:
1.将前面的身份证号码17位数分别乘以不同的系数.从第一位到第十七位的系数分别为:7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2
2.将这17位数字和系数相乘的结果相加.
3.用加出来和除以11,看余数是多少?
4余数只可能有0 1 2 3 4 5 6 7 8 9 10这11个数字.其分别对应的最后一位身份证的号码为1 0 X 9 8 7 6 5 4 3 2.
5.通过上面得知如果余数是2,就会在身份证的第18位数字上出现罗马数字的Ⅹ.如果余数是10,身份证的最后一位号码就是2.
例如:某男性的身份证号码是34052419800101001X.我们要看看这个身份证是不是合法的身份证.
首先:我们得出,前17位的乘积和是189
然后:用189除以11得出的结果是17 + 2/11,也就是说余数是2.
最后:通过对应规则就可以知道余数2对应的数字是x.所以,这是一个合格的身份证号码.
备注说明:关于大陆身份证有的人会发现前几位为什么变化了.这主要出现在中国的重庆.原有的重庆人的身份证多数以51开头.以前隶属于四川的原因.但新办的身份证可能是50开头,原因是行政区划改变所致.中国各地的行政区划代码请参考国家统计局网站.

关于“巧用Excel:利用身份证号码 提取个人信息”的相关问答集锦

以下是与 巧用 身份证号码 个人信息 相关的几篇文章,希望对您有所帮助:

身份证号码 姓名 照片都是个人信息

员工的身份证号码(15位)是320521720807024,那么表示1972年8月7日出生,性别为女。如果能想办法从这些身份证号码中将上述个人信息提取出来,不仅快速简便,而且不容易出错,核对时也只需要对身份证号码进行检查,肯定可以大大提高工作效率。

什么是身份真假核查服务?

用户通过网络和手机通道向山西省身份证查询系统提供被查询人的身份证号码和姓名......

险企“资源共享”个人信息有隐患

为目的的滥用个人信息。另外,还有间接以营利为目的的情况,像前面的案例就是,你从没透露过信息的人也能打电话给你推销业务。我们都不清楚什么时候会“泄露”自己的个人信息,是你出生时的出生登记?还是你上学时填的报名表?还是你购房买车时提交的个人详细资料?所以要想真正保护好个人信息是很难的,我们更应该从管理的角度去看保护个人信息的问题。 个人信息的保护是一项非常难实现的工程,技术层面的东西到现在都存在争议。至于如何保护个人信息,建议国家还是有一个针对性的规制管理体系。首先,应当将对个人信息的保护上升到......

网站身份证查询 个人信息安全堪忧

上一篇:机动车驾驶证年公民身份证查询系统审规定
下一篇:大家的身份证号码上的生日是农历还是公历?

相关内容