中职资助工作中Excel表格处理的相关问题及对策

王鹏鹤

笪河南省郑州市金融学校王鹏鹤

中职学校的学生资助工作中，经常会处理一些Excel表格，我们经常会遇到一些问题，比如身份证号的输入错误、查询资助学生是否有重复、查询哪些学生享受助学金等，通过我不断地摸索和尝试，发现了一些解决方法，分享给大家。

1问题一：如何避免输入的身份证号出错

身份证号是Excel表格中最重要也最容易出错的一个字段，但它又是最关键的一个字段，正确地输入身份证号是重中之重。要避免身份证号出错，我考虑从三个方面着手来解决。

1.1设置单元格为“文本”。Excel表格直接输入18位身份证号，就会出现变成含有字母E的科学计数，或者后三位直接变成0的情况，这是因为Excel将身份证号当成了数值来处理，而Excel中数值的有效位数最多为15位，故后三位均变成了0。此时只需选择整列，设置单元格为“文本”格式，而后再输入身份证号就可以了。

1.2使用数据有效性来规范。数据有效性会对输入的值进行规范，我们就利用数据有效性对身份证号的长度进行规范。先选择身份证号所在的列，使用菜单“数据”→“有效性”，弹出对话框，选择“文本长度”、数据选择“等于”、长度输入18。这样在输入的值不是18位时就会提示出错。

1.3利用公式来验证。身份证号的第18位是一个校验码，我们可以根据校验码的公式来验证身份证号输入是否有效，这里假设身份证号在A列，先插入一列B列，在B4中输入以下公式：=MID("10X98765432",MOD(SUMPRODUCT(MID(A4,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(A4))。该公式主要验证身份证号的校验码是否正确，如果公式的结果为TRUE，表示身份证号有效；如果结果为FALSE，表示身份证号无效。将此公式按列自动填充，全部验证无误后把B列删除即可。

2问题二：如何判断是否享受国家助学金

如何快速地判断某学生是否已通过助学金审查，也是经常遇到的问题，其实也就是从已通过名单中查找该生的身份证号，这只需用VLOOKUP函数就可以解决。

假设已通过审查的信息存放在工作表“助学金”中，其中A列为身份证号，B列为姓名，要判断某学生（身份证号A4）是否享受助学金，其函数如下：=VLOOKUP(A4,助学金!A:B,1,FALSE)。该函数表示在单元格区域“助学金!A:B”的首列（即助学金表的A列）查找是否有A4的信息，如果有，则返回单元格区域中第1列所对应的值（身份证号）；如果没有，会显示错误值“#N/A”。这里的FALSE表示精确匹配。为防止出现错误值，可增加一个错误判断函数ISERROR，公式如下：=IF(ISERROR(VLOOKUP(A4,助学金!A:A,1,FALSE))“,未享受”“,OK”)此公式中如果找不到A4的值，VLOOKUP函数会出错，返回“未享受”；如果能找到，则返回“OK”，表示享受助学金。再通过筛选，就可以轻松地找出享受助学金或是未享受助学金的学生了。

但这却无法判断学生姓名与身份证号是否一致，而VLOOKUP函数可以查询身份证号返回对应的学生姓名，函数如下：=VLOOKUP(A4,助学金!A:B,2,FALSE)和上面不同的是，查询还是在助学金表的首列，而返回的是第2列B列对应的值，即学生姓名。为更方便地看到结果，我们对返回姓名与姓名B4是否一致进行判断，公式如下：=IF(VLOOKUP(A4,助学金!A:B,2,FALSE)=B4,"OK",VLOOKUP(A4,助学金!A:B,2,FALSE))。此公式判断姓名如果一致，则返回“OK”；如果不一致，则返回助学金表中的学生姓名，如果找不到，则显示错误值“#N/A”。这里出现不一致情况的原因，要么是错别字或同音字，要么是有空格，需要在录入时多细心，避免出错，甚至张冠李戴。

3问题三：如何保证学生信息不重复上报

免学费报表中最容易出现重复问题，免学费包括农村经济困难、城市家庭困难、涉农专业等三种情况，这三个工作表中都不允许出现重复，这里的重复包括两种，一是工作表之间的重复，二是每个工作表内的重复。重复问题，其实还是查询，完全可以用VLOOKUP函数做，但更简单的是使用条件统计函数COUNTIF来统计出现的次数，根据次数来判断是否重复。

3.1工作表之间的重复问题。假设三个工作表分别为“农村免”、“城市免”、“涉农免”，并且身份证号都放在A列，要统计农村免表中的身份证号A4在城市免表中出现的次数，可在农村免表中输入下面的函数：=COUNTIF(城市免!A:A,LEFT(A4,18))。该函数表示在单元格区域"城市免!A:A"中统计等于LEFT(A4,18)的单元格出现的个数。如果在城市免表中没有出现，则返回值为0；如果有出现，则返回出现的次数。这里的LEFT(A4,18)是指仅查询A4的左边18位，而且函数会自动将身份证号当成文字来统计，而不是按数值统计。为了更清晰地表达，加上判断变成下面的公式：=IF(COUNTIF(城市免!A:A,LEFT(A4,18))>0,"重复","OK")如果在单元格区域"城市免!A:A"中LEFT(A4,18)值出现的次数大于0，则返回“重复”，否则返回“OK”，表示无重复。当然，我们需要在其它两个工作表中都进行统计。只有每个工作表统计的结果都是0，才能说明无重复。公式如下：=IF(COUNTIF(城市免!A:A,LEFT(A4,18))+COUNTIF(涉农免!A:A,LEFT(A4,18))>0,"重复","OK")

3.2工作表内的重复问题。这种情况其实和上面的差不多，只不过是在工作表内进行统计。函数如下：=COUNTIF(A:A,LEFT(A4,18))此函数的差别在于单元格区域变成了当前工作表的A列，而A4就在A列中，所以统计结果至少会有一个，超过一个就意味着工作表内有重复。加上判断后公式应该是这样的：=IF(COUNTIF(A:A,LEFT(A4,18))>1,"本表重复","OK")把两种重复结合起来，就可以做到不重复。当然，在查重前一定要保证所有工作表中的身份证号都是有效身份证号，否则查重是毫无意义的。

最后，特别指出的是，所有的公式都不是万能的，它只是我们的助手罢了，细心的工作态度和行之有效的工作方法才是最重要的，只有这样，我们的工作才会更高效、更准确、更完美。