EXCEL在全国中等职业学校学生管理信息系统中的运用

刘沈

摘要：针对全国中等职业学校学生管理信息系统中数据模板检测功能的不足，本研究探讨使用Excel内置函数来检查有关字段，解决了数据补录及照片文件批量更名等问题，避免了等待服务器端反馈错误的环节，促进了学籍信息管理的自动化和信息化，有助于学籍管理工作效率的提高。

关键词：Excel；函数；学籍管理

中图分类号：TP311 文献标识码：A 文章编号：1009-3044（2015）29-0112-02

The Application of EXCEL in the Management Information System of Middle Vocational School Students in China

LIU Shen

（Yangzhou Business School of Business Administration， Yangzhou 225127， China）

Abstract： Aiming at the shortage of the secondary vocational school student management information system data template detection function， this study explore using Excel's built-in function to check the field， to solve the problems of additional data recording and photo file batch rename， etc.， to avoid waiting for server feedback error link， promote the automation and information technology in the management of student information， help to improve the student management work efficiency.

Key words： Excel； function； school management

为全面实现全国学生学籍“一人一号、终身使用”，杜绝跨教育阶段学籍重复，新版全国中等职业学校学生管理信息系统已于2014年12月28日在江苏正式上线。该系统针对新生信息录入、在校生信息维护等功能均依托于EXCEL来完成，并提供了“招生导入模板”、“新生数据批量处理”、“在校生档案管理数据批量处理”等一系列的EXCEL模板，基于模板内的VBA宏，用户可实现对学生数据的批量生成与检查。尽管系统提供的模板已经较好地提高了工作效率，避免了一些可能会出现的人工错误，但在实际操作中仍有不少可以改进的空间。笔者结合自身从事学籍管理工作的经验，以学生数据导入、数据补录、照片文件命名等实际问题为例，介绍EXCEL部分函数的功能与应用。

1 EXCEL在学生数据处理中的应用

1.1 检查行政区划码在字典中是否存在

全国中等职业学校学生管理信息系统（以下简称全国中职系统）在录入新生学籍信息时，先要通过“招生导入模板”来完成招生信息管理模块，该模板中有两个必填字段“户口所在地行政区划码”与“生源地行政区划码”，可以通过模板中的“批量自动生成”按钮来根据身份证号前6位实现自动填充。然而笔者发现在点击模板中的“开始检查”按钮之后，EXCEL并不会检查自动生成的行政区划码是否在字典表内存在，当用户在“数据校验通过，可以上传”的对话框弹出后上传该模板至系统时，系统会在服务器端对数据再度进行处理，将没有错误的新生数据导入系统，错误的数据系统会生成错误文件，用户下载后可查看具体错误信息。然而服务器端对用户导入的数据处理并不是实时的，而是需要等待5分钟后方可查看处理结果，并且绝大多数的错误都是“系统不存在该户口所在地行政区划码”，即由身份证号自动生成的行政区划码在模板的字典表内并不存在，这时就需要用户在更改行政区划码后再度上传，再在5分钟后查看处理结果。如果事先可以对行政区划码进行检查，就可以避免上述的重复性劳动。因而在“招生导入模板”的字典表内，笔者新增了一列，用MID和FIND函数取出字典表内行政区划码的数字。如图1所示，M2单元格内的公式填写如下：=MID（A2，FIND（"（"，A2）+1，12），用自动填充柄完成下拉填充。然后到模板的学生基础信息表内新增一列，在Z4单元格输入公式：=VLOOKUP（I4，字典！M：M，1，0），如果结果显示为“#N/A”，则表明该生通过身份证号自动生成的行政区划码在系统内不存在，此时可用筛选功能将所有此类学生筛选出来，并逐一处理后再上传。

1.2 借助公式高效完成数据补录工作

在新版的全国中职系统正式上线后，江苏省各院校需对系统内数据进行完善，对学生的生源地行政区划码、户口所在地行政区划码、民族等字段完成补录工作。民族字段可以依托于VLOOKUP函数在现有的省中职系统所导出的学生数据表中进行查找，但是行政区划码却只能通过其生源地名称、户口所在地名称在模板内的字典表内进行查找。字典表内户籍地址行政区划码的格式为名称后跟括号，括号内为代码，例如：扬州市广陵区（321002000000））。笔者根据此特点在补录模板中新增了生源地行政区、户口行政区两列，通过VLOOKUP函数在省系统导出的数据表内找到每名学生对应的地区名称，在M4单元格内输入公式：=IF（ISERROR（MID（LOOKUP（1，0/FIND（K4，字典！$A：$A），字典！$A：$A），LEN（LOOKUP（1，0/FIND（K4，字典！$A：$A），字典！$A：$A））-12，12）），""，MID（LOOKUP（1，0/FIND（K4，字典！$A：$A），字典！$A：$A），LEN（LOOKUP（1，0/FIND（K4，字典！$A：$A），字典！$A：$A））-12，12）），EXCEL便会完成名称与代码的匹配，结果如图3所示。同理，在N4单元格只需将上述公式内的K4更换为L4即可，在完成M列与N列的自动填充后，选中两列数据复制，并在原处选择粘贴“值”，完成此步后删除K列与L列，即可导入全国中职系统，完成数据补录工作。

1.3 运用公式提取监护人联系电话

全国中职系统内需要填写学生的监护人电话，该字段不可超过20个字符，只能为手机号码或固定电话，且若为固定电话，前面需要加上区号。由于学生在报名时，学校招办会录入学生的基本信息，其中包含了家长联系电话，很多班级在填写全国中职系统数据模板时，直接复制了招办报名信息表内的联系电话列内的内容，但是招办在填写联系电话时往往至少登记一个固定电话与一个手机号码，中间用顿号隔开，且固定电话在前，扬州大市的还略去了区号。这样造成模板中的监护人电话这列超过了20个字符，无法导入系统。笔者在监护人电话所在的P列右侧新增一列，在该列数据所在的第一个单元格Q4内输入公式：=IF（ISERROR（FIND（"、"，P4）），P4，MID（P4，FIND（"、"，P4）+1，12）），这样便会取出顿号后面的手机号码，在完成Q列自动填充后再将该列数据复制，在P列选择粘贴“值”，再删除Q列，即对监护人电话完成符合系统要求的修改。

1.4 检查数据填写的完整性

在上传学生信息至系统时，会发现有些通过模板内“批量自动生成”按钮填写的数据会发生缺漏的情况，导致部分数据无法上传。为了避免在上传后才发现此类问题，笔者通过COUNTA函数来完成数据完整性的检验。以招生导入模板为例，在最后一列的Z4单元格输入公式：=IF（COUNTA（$A4：$Y4）=COUNTA（$A$3：$Y$3），""，"存在缺项"），统计当前行中的字段填写个数，若非空单元格个数与第三行中的行标题个数相同则返回空值，表明数据填写完整，如果有缺漏则返回“存在缺项”，再去对相关数据进行完善。

2 EXCEL在照片文件处理中的应用

在完成全国中职系统内学生数据上报的工作后，还需要在系统内上传学生的照片。所有的照片文件都必须以在校生的身份证号命名。根据以往的流程，是请各班主任将以身份证号命名的学生电子照片统一交至教务处，再由学籍管理员上传至系统。然而笔者发现各班级在用学生身份证号命名照片文件时很容易产生错误，比如漏了一位或者数字前后颠倒等等，对这些错误进行纠正会耗费大量的时间与精力。因而笔者要求各班在交照片时均以学号+姓名的方式命名，将照片统一放在一个文件夹内。之后笔者利用全国中职系统内的导出功能，将新生数据导出，仅保留学号、姓名、身份证号三列，然后在D2单元格输入公式：=A2&B2，用填充柄自动填充D列，再在E2单元格内输入公式：="ren "&D2&".jpg "&C2&".jpg"，并用填充柄自动填充E列（如图4）。在照片所在文件夹内新建一记事本文件，并将E列数据复制到记事本内，另存为扩展名为BAT的文件之后，在文件夹内运行该BAT文件，将会自动将所有照片文件用身份证号码完成批量更名操作。此时照片文件名称会与系统内身份证号完全一致，在导入过程中便不会出现任何错误。

3 结束语

本研究基于对EXCEL的部分函数应用，实现对全国中职系统的数据模板部分优化，补充了对相关字段的检查，避免了等待服务器检查反馈错误信息的环节，通过对照片文件的批量重命名，保证了文件名的正确性，较好地提高了工作效率，也为从事学籍管理相关工作的人员能够从繁杂的数据处理与核对中解放出来提供了一定的帮助。

参考文献：

[1] 刘艳慧.EXCEL函数在学生信息管理中的应用[J].价值工程，2014（6）：195-196.

[2] 李仙花.浅议Excel函数在学籍管理中的应用[J].职业，2014（3）：141-142.

[3] 肖章林，梁爽.Excel在学生管理工作中的应用[J].电脑知识与技术，2014（34）：8179-8193.

[4] 韩辉珍.Excel在学生成绩管理中的应用[J].中国科技信息，2008（12）：39-41.