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

精品论文 参考文献 中职资助工作中Excel 表格处理的相关问题及对策 笪河南省郑州市金融学校王鹏鹤 中职学校的学生资助工作中，经常会处理一些Excel 表格，我们经 常会遇到一些问题，比如身份证号的输入错误、查询资助学生是否有 重复、查询哪些学生享受助学金等，通过我不断地摸索和尝试，发现了 一些解决方法，分享给大家。 1 问题一：如何避免输入的身份证号出错 身份证号是Excel 表格中最重要也最容易出错的一个字段，但它 又是最关键的一个字段，正确地输入身份证号是重中之重。要避免身 份证号出错，我考虑从三个方面着手来解决。 1.1 设置单元格为“文本”。Excel 表格直接输入18 位身份证号，就会 出现变成含有字母E 的科学计数，或者后三位直接变成0 的情况，这 是因为Excel 将身份证号当成了数值来处理，而Excel 中数值的有效 位数最多为15 位，故后三位均变成了0。此时只需选择整列，设置单元 格为“文本”格式，而后再输入身份证号就可以了。 1.2 使用数据有效性来规范。数据有效性会对输入的值进行规范，我 们就利用数据有效性对身份证号的长度进行规范。先选择身份证号所 在的列，使用菜单“数据”rarr;“有效性”，弹出对话框，选择“文本长度”、 数据选择“等于”、长度输入18。这样在输入的值不是18 位时就会提示 出错。 1.3 利用公式来验证。身份证号的第18 位是一个校验码，我们可以根 据校验码的公式来验证身份证号输入是否有效，这里假设身份证号在 A 列，先插入一列B 列，在B4 中输入以下公式：=MID(10 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