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

用Excel处理学生信息中“身份证号码”研究 　　摘 要 根据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(10MOD(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单元格输入下面的