用Excel处理学生信息中“身份证号码”的研究

2012-04-29袁敏锐

读写算·素质教育论坛 2012年7期

袁敏锐

摘要根据Excel数据信息，针对身份证号码在录入后显示不正常、录入难免有错误以及通过身份证号码实现减少数据录入量，本文介绍了相对应的解决办法。

关键词数据信息身份证号码数据有效性设置研究

中图分类号：G71文献标识码：A文章编号：1002-7661（2012）07-0006-02

目前，身份证号码在数据信息里随处可见，而且处理不好问题不少。如单位小张每年9月份都要负责至少两三千人的新生入学的学生信息数据的汇总，并上传到网络系统里；而各班班主任录入学生信息时难免有错误，其中如果学生身份证号录入有误，就不能正常上传，还得返回去让各班主任重新核对，再交回来，再重新上传。这样来来回回的折腾，费时费力不说，主要是影响工作的进度，无法向领导交差。那么有什么方法可以避免这种错误，提高工作效率呢？大家可能会想要求各班主任在录入数据时认认真真地录入不就没事了，但是稍不留意错误就会出现，不要说录入一个班几十个人的信息了，有时我们自己写自己的手机号或者身份证号都有可能出现错误。但是如果能在Excel表格中录入身份证号的时候多一个提醒功能，相信出错的机会就少之又少了，同时也就可以做到及时发现错误并及时更正。在Excel表格中简单的添加一些功能，就能方便判断录入的身份证号是否存在录入错误（如：录入的身份证号位数是多了或少了）或者是否合法，检验出身份证号的有效性以及合法性。

一、利用Excel设置检验身份证号码的有效性及合法性

利用Excel公式对身份证号码的录入进行准确性检验，确保身份证号录入的准确性；可以检验出15位的身份证号码录入时是否是15位，18位身份证号码（目前绝大多数为二代身份证了，即都是18位号）的，其最后一位是检验码，它是根据身份证号前17位数字依照规则计算出来的（这里不作说明），其值0～9或X。一般情况下只要有一位数字输入错误，依照规则计算后就会与第18位数字不符。当然不排除按错误号码计算后恰好与检验码相符的情况，但这种情况出现的可能性较低。所以利用下面介绍的设置功能，录入18位身份证号码时除了可以检测出录入位数的对错外，同时还可以检验其是否合法，及时在录入时提醒录入者，大大提高身份证号录入的准确率。

（一）利用Excel数据有效性设置自动弹出提示框提醒

在录入身份证号码前，先设置要录入身份证号码的所在列为文本格式，否则在Excel单元格中输入的数字位数超过11位后，会变成科学计数的形式，如录入“123456789012345678”（外面双引号不要录入），18位数会显示成“1.23457E+17”，双击进去看到的数字变成“123456789012345000”后三位数字全变成了“000”是错误的，如图1所示。

图1

1．设置身份证号码所在列的单元格格式为“文本”格式

选中身份证号码所在的列“列头”（本文中为C列）。如图2所示，然后点击“鼠标”右键→选择“设置单元格格式”选项→“单元格格式”窗口→选择“数字”选项卡→在“分类”栏中→选择“文本”选项→然后点击“确定”按钮。这样该列的所有单元格格式全部设置为文本格式，再来录入身份证号码就不会出现（如图1）错误的情况。

图2

2．设置要录入身份证号码的所在列（C列）的有效性

设置要录入身份证号码的所在列（C列）的有效性，让其能在录入身份证号码不正确的时候及时提示。

第1步：选定“C列”列头，菜单：数据→有效性。（如图3）

图3

第2步：当打开“数据有效性窗口”后，选择“设置”选项卡→允许→选择“自定义”。公式引用的位置，把下面的公式输入（或者把公式复制）进去：（如图4）

=OR(LEN(C1)=15，IF(LEN(C1)=18，MID("10X98765432"，MOD(SUM(MID(C1，ROW(INDIRECT("1:17"))，1)*2^(18-ROW(INDIRECT("1:17"))))，11)+1，1)=RIGHT(C1)))

图4

第3步：公式输入完后，选择“出错警告”选项卡→样式→选择“信息”；“标题”下面的内容，输入提醒文字如“注意”；“出错信息” 下面的内容：输入“请您再次确认，您刚刚录入的‘身份证号码是否准确！”等类型的文字，最后点“确定”（如图5），数据有效性设置就完成了。这时录入身份证号码如果出错，马上就会自动弹出提醒对话框，提醒录入人员重新再核对，避免了身份证号出错了还不知道的情况。

图5

（二）在身份证号码所在列旁，增加一列用于设置文字提醒

如在D列设置，D1单元格输入“身份证号码对错”作表头，在D2单元格输入下面的公式：

=IF(IF(LEN(C2)=18，MID("10X98765432"，MOD(SUMPRODUCT(MID(C2，ROW(INDIRECT("1:17"))，1)*2^(18-ROW(INDIRECT("1:17"))))，11)+1，1)=RIGHT(C2)，IF(LEN(C2)=15，ISNUMBER(--TEXT(19&MID(C2，7，6)，"#-00-00"))))，"正确"，"错误")

输入完后按“Enter（回车）”，然后向下填充该公式即可。这样如果身份证号码录入对了，其对应的右边D列显示“正确”，不对了显示“错误”。

在此为了使“错误”显示得更显眼，还可以在D2单元格输入完公式后，设置一下D2单元格“条件格式”，再向下填充该公式。通过单元格“条件格式”，在显示“错误”的时候，字体变成红色同时加上底纹颜色以便更好的区别。

设置方法（步骤）：在D2单元格输入完上面的公式后，选中“D2” 单元格→选择菜单“格式”→选择“条件格式”选项→“条件1”选择“单元格数值”→选择“等于”→输入“=“错误””→选择“格式”按钮（在里面，选择“字体”选项卡→颜色选择“红色”→再选择“图案”选项卡→单元格底纹→选择一种底纹颜色“灰色”→点击“确定”按钮，退出）→再点击“条件格式”窗口上的“确定”按钮，完成设置。（如图6）

图6

再用D2单元格，向下填充公式就会连“条件格式”一起向下填充了。当录入的身份证号码出错时，其对应的D列上所在的单元格会显示出红色字体的“错误”两个字及该单元格被加上“灰色”底纹的效果，显得更显眼。（如图7）

图7

不过，当最后全部身份证号码录入完后，如果D列不要了，可以采用隐藏它或者直接删除都可以。

通过以上两种方法的设置，都可以在录入身份证号码出错时，马上提醒录入人员重新再核对身份证号码，避免了身份证号录入错误了还不知道的情况。这两种方法可以单独使用，也可以一起使用，至于使用哪种方法，可以根据自己的需要和习惯来定。

二、利用身份证号码提取相应内容，减轻信息输入量

正确而合法的身份证号码是每个公民唯一的身份代号，并且包含有公民的出生年月日、性别及最初编制该身份证号码的所在地区（目前一般为出生地）或者叫籍贯地区等信息。我们在录入信息的时候除了要求有姓名、身份证号码外，一般情况下还要有出生日期（或年龄）、性别等相关信息。如果录入的数据量大（人数多），把这些信息一条条的录入，不仅费时费力，同时出错也是在所难免的。但是如果我们只录入姓名及通过上面介绍的方法保证身份证号码的录入准确，再利用Excel函数通过其身份证号码提取其出生日期（或年龄）及性别，就可以大大减少信息的录入量及出生日期、性别的录入错误。

中国居民身份证号码是由一组特征组合码组成的，最初的一代身份证号码为15位，目前大部分都升级为二代身份证号码为18位，其编码规则为：

15位身份证号码：前6位数字为籍贯地区代码，第7~12位数字为出生日期代码，第13~15位数字为顺序码。18位身份证号码：前6位数字为籍贯地区代码，第7~14位数字为出生日期代码，第15~17位数字为顺序码，第18位数字为检验码。

其中３位数字顺序码，是为同一籍贯地区（代码）的同年同月同日出生人员编制的顺序号（如双包胎其身份证号码的区别），并且３位数字顺序码的最后一位数字（即15位的最后一位，18位的倒数第二位），是偶数的为女性，是奇数的为男性。本文中身份证号码所在列为C列，而且是从C2单元格开始录入，下面公式提到的C2都是此意思。

1．从身份证号码中提取出生日期或年龄

本文中提取“出生日期”放在E列，E2单元格开始，那就要先把E2单元格设置好要求的“日期”格式（按图2类似的设置方法操作），并把下面的公式输入E2单元格，并向下填充公式，即可得相应的出生日期。（如图8）

图8

=IF(C2<>""，TEXT((LEN(C2)=15)*19&MID(C2，7，6+(LEN(C2)=18)*2)，"#-00-00")+0，)

简化公式：=--TEXT((LEN(C2)=15)*19&MID(C2，7，6+(LEN(C2)=18)*2)，"#-00-00")

如果提取的出生日期格式（如：19950809）公式采用：

=IF(LEN(C2)=15，19&MID(C2，7，6)，"")&IF(LEN(C2)=18，MID(C2，7，8)，"")

简化后公式为：=IF(LEN(C2)=18， MID(C2，7，8)， 19&MID(C2，7，6))

2．从身份证号码中提取性别

本文中提取“性别”放在F列，F2单元格开始，那就要先把F2单元格设置好要求的“常规”格式（按图2类似的设置方法操作），并把下面的公式输入F2单元格，并向下填充公式，即可得相应的性别。（如图9）

图9

=IF(C2<>""，IF(MOD(MID(C2，IF(LEN(C2)=15，15，17)，1)，2)=1，"男"，"女")，)