分类

软件教程

Excel函数从身份证号码中提取性别,籍贯,出生日期,年龄等有用信息方法

作者:WinWin7   来源:本站整理   日期:2019-06-11

在知道身份证号码的前提下,我们可以从身份证号码中提取有用的信息,如性别,籍贯,出生日期,年龄等,在EXCEL中可以使用函数来提取,大大的减少工作量,不知道如何操作的朋友可以一起来学习一下。
 

1814A-SFXXTQ-1

以上身份证号为随机生成,如有雷同纯属巧合。

 

1. 计算性别

身份证号的第17位是性别标识,其中偶数代指女性,奇数代指男性。如图1,在B2单元格输入“=IF(MOD(MID(A2,17,1),2),"男","女")”并向下填充,即可获得每个号码所对应的性别。

2. 计算年龄

年龄的最大特点是它会随着时间而变化,为了计算当前的年龄,我们引入TODAY、YEARFRAC等函数。其原理是计算出出生日期距今天的时间,将这个时间的单位转换为年之后,再进行取整操作。公式也不复杂,在D2单元格输入“=INT(YEARFRAC(DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2)),TODAY(),3))”就能轻松完成。

3. 计算籍贯

身份证号码的前6位是地址码,我们可以通过查询GB/T2260所规定的行政区划代码来获取籍贯。制作一个名为“地址表”的行政区划代码空表,表中数据可通过中华人民共和国民政部网站或政府其他网站获取,复制相关数据并粘贴到“地址表”中即可(图2)。

1814A-SFXXTQ-2

在个人信息表中使用VLOOKUP函数便可以快速地输出地址。在E2单元格输入公式“=VLOOKUP(LEFT(A2,6),地址表!$A$1:$B$3465,2,FALSE)”并填充即可。

小提示:在用VLOOKUP函数匹配数字符号时,最好将所有数字统一转换为文本格式。其方法如下:如图2,选中A列数据,按下Ctrl + C复制,点击“数据”菜单下的“分列”按钮,在弹出的对话框内依次两次点击“下一步”,然后选择“文本”并点击“完成”(图3)。

1814A-SFXXTQ-3

扩展阅读:计算身份证校验码

校验码是身份证中最后一位数字,用于检测该身份证号是否合法。它是根据前面十七位数字码,按照ISO 7064:1983.MOD 11-2校验码计算出来的。只需在F2单元格输入公式“=IF(CODE(VLOOKUP(MOD((MID(A2,1,1)*7+MID(A2,2,1)*9+MID(A2,3,1)*10+MID(A2,4,1)*5+MID(A2,5,1)*8+MID(A2,6,1)*4+MID(A2,7,1)*2+MID(A2,8,1)*1+MID(A2,9,1)*6+MID(A2,10,1)*3+MID(A2,11,1)*7+MID(A2,12,1)*9+MID(A2,13,1)*10+MID(A2,14,1)*5+MID(A2,15,1)*8+MID(A2,16,1)*4+MID(A2,17,1)*2),11),{0,1;1,0;2,"X";3,9;4,8;5,7;6,6;7,5;8,4;9,3;10,2},2,FALSE))=CODE((RIGHT(A2,1))),"正确","错误")”。若身份证号符合规则,则显示“正确”,否则显示“错误”。

相关文章

推荐下载