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

2016-01-29 18:40 46 1 收藏

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

【 tulaoshi.com - 办公软件】

    办公室的小秦向我求教，说最近需要上报一份材料，这份材料是用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 输入身份证号码

    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单元格显示为“男”，否则显示为“女”。

    回车确认后，即可在B2单元格显示正确的性别信息，接下来就是选中填充柄直接拖曳。如图2所示，现在这份报表无论是提取信息或是核对，都方便多了！

图2 提取个人信息

来源:http://www.tulaoshi.com/n/20160129/1506772.html

上一篇：用WPS Office 2002轻松制作试卷卷头
下一篇：在基于Mozilla的浏览器中使用XMLHttpRequest对象

看过《巧用Excel：利用身份证号码提取个人信息》的人还看了以下文章更多>>

WPS移动版巧妙设置快速输入18位身份证号码

标签：电脑入门

财务工作人员经常接触Excel表格，阿mo也不例外。而且财务人员在制作工资表格，或人事部门在统计员工资料时需要输入身份证号码或银行账户等，数字无法完整显示的问题自然会影响工作效率。其实利用WPS移动版，打开或新建Excel表格进行编辑时，简单设置几下即可正常显示所有数字，看看阿mo是如何办到的吧! 首先阿mo要提醒大家，对于WPS移动版而...

12306提示非法的二代身份证号码怎么办

标签：生活常识

12306提示非法的二代身份证号码怎么办图老师阅读配图 12306提示非法的二代身份证号码怎么办 12306为了打击黄牛而上线了身份证核验系统，买票的时候输入身份证如果提示非法的二代身份证号码，不要惊慌，并不是你的户口成了非法的黑户了，只是没通过核验而已。下面图老师小编就告诉大家怎么解决。参考解决方法： ...

Excel2007怎么输入有效的身份证号码不让其变成科学计数法

标签：软件教程电脑应用 excel

在Excel中输入身份证号码是在存储员工或客户信息时经常会做的一件事。当把一长串身份证号码输进去后，就会发现身份证号码变成了科学计数法，并不是理想中的显示状态。针对这个问题，该怎么解决呢？如何才能输入长串的有效的身份证号码呢？下面与大家分享三种比较不错的方法，经测试非常可行。简要概述面对Excel中输入身份证号码变科学计数...

为什么在EXCL单元格中输入身份证号码不能显示完全

标签：电脑入门

处方1：少量数字转文本在输入数字前，先输入一个半角的引号，然后再输入数字，则输入的数字被Excel视为文本。处方2：将数字设置成文本格式在输入文本数字前，先将单元格设置成文本格式，方法是： 1.选择要设置格式的单元格。 2.在格式菜单上，单击单元格，再单击数字选项卡。 3.在分类列表中，单击文本，再单击确定。处方3：求TEXT()函...

利用Excel加载宏轻松提取身份证省份信息

标签：电脑入门

在建立企事业单位员工Excel表格过程中，员工的身份证号、所属省份、性别等信息都是必须要输入的。如果单位员工众多的话，相关个人信息的输入工作量无疑是相当大的。那么，我们能不能让繁琐的个人信息输入过程变得更轻松以提高自己的工作效率呢？答案当然是肯定的。因为只须利用一个Excel加载宏，我们就可以很方便地达到上述目的。先下载Exc...

查看更多精彩>>