Excel中对身份证号码的处理

Excel中对身份证号码的处理

作者：萧巍

来源：《现代交际》2017年第15期

摘要：在工作学习生活中，很多时候要在Excel工作表中输入身份证号码。在输入身份证号码时，经常会出现输入的字符位数不符，输入的数字自动转换为科学计数法形式等问题。本文对身份证号码的位数设置、输入身份证号码、提取出生年份性别等操作进行探讨，从而阐明Excel中对身份证号码的处理。

关键词：Excel 身份证号码数据有效性函数

中图分类号：G634文献标识码：A文章编号：1009-5349（2017）15-0191-01

在工作学习生活中，很多时候要在Excel工作表中输入身份证号码。在输入身份证号码时，经常会出现输入的字符位数不符，输入的数字自动转换为科学计数法形式等问题。本文对身份证号码的位数设置、输入身份证号码、提取出生年份性别等操作进行探讨，从而阐明Excel中对身份证号码的处理。

一、身份证号码字符位数的限定

身份证号码一般是15位或者是18位，现如今的新版身份证一般是18位字符。在输入时，很容易多输入或少输入字符。有什么办法限定其输入的位数呢？可以使用数据有效性进行限定（本文就18位身份证号码进行阐述，之后的操作也就18位身份证号码阐述）。选定要输入身份证号码的单元格，执行“数据——数据有效性”命令，弹出数据有效性对话框，选择“设置”选项卡，在“允许”列表框中选择“文本长度”，在“数据”列表框中选择“等于”，在“长度”文本框中输入18，单击“确定”即可。见图1。

图1

设置好后，若输入不等于18位的身份证号码，就会弹出“输入值非法”对话框，禁止不符合要求的身份证号的输入。

二、身份证号码的输入

在输入身份证号码时，比如输入362101************，回车确认后会变为362101E+17。这是因为在单元格内输入的数字字符的位数超过11个字符时，Excel会将它转换为科学计数法来处理，即转换为362101E+17，其含义是362101乘以10的17次方。那么，遇到这种情况怎么解决呢？如何输入才能得到我们需要的身份证号码呢？有五种方法。第一种方法是在输入身份证号码时在其前面加上一个英文的单引号。其作用就是将该身份证号码的属性设置为“文本”属性。即可让身份证号码完全显示出来。第二种方法是将要输入身份证号码的单元格设置为

“文本”格式，再输入身份证号码，则身份证号码不会变为科学计数法形式。第三种方法是选中要输入身份证号的单元格，执行“数据——数据有效性”命令，选择“数字”选项卡，在“分类”列表框中选择“自定义”选项，在“类型”列表框中选择“@”，单击“确定”。然后在该单元格中输入身份证号码即可。第四种方法操作同第三种方法，在”类型”文本框中输入18个零。第五种方法是在需要输入身份证号码的单元格中输入“ =‘身份证号码’”，这样身份证号码就会正常显示，而不会变为科学计数。第五种方法其实是将身份证号码表示成公式的值。

三、提取身份证号码中的日期

在办公室工作当中，很多时候要获取员工的出生日期。而手头没有现成的出生日期数据，但员工一般都会留下身份证号码，可以用MID函数将身份证号码中的出生日期提取出来。假设在D2单元格有一个身份证号码362101************，可以在任意单元格输入公式“=MID （B2，7，8）”，意思是在该身份证号码中提取从左边数第7个字符后的8个字符，而18位身份证号码的第7个字符后的8个字符正好是身份证号码主人的出生日期。这个公式的结果是19870126，即1987年1月26日。

四、提取身份证号码中的性别

18位身份证号中倒数第二位数字如果是奇数则为代表身份证号的主人是男性，如果是偶数则代表其主人是女性。将身份证号码中代表主人的性别提取出来分别要使用到If函数、Mod 函数和Mid函数。假设在D2单元格有一个身份证号码362101************，可以在任意单元格输入公式“=IF（MOD（MID（D2，17，1），2）=0，‘女’，‘男’）”。其含义是取出倒数第二位的数，然后跟2取余数，若余数为0则为女性，若不是则为男性。

五、结语

身份证号码位数的限定要使用到数据有效性。输入身份证号码可以把其设置为文本格式，或者把其表示为一个公式的值。提取身份证号码中的日期和性别要使用到相关函数。灵活地处理身份证号码可以给我们的工作和学习带来便利。

参考文献：

[1]曾广雄，吴秀英.计算机应用基础项目化教程[M].西安电子科技大学出版社，2013.

责任编辑：于蕾