Excel在高校学籍信息管理工作中的应用

(天津电子信息职业技术学院，天津 300350)

高校学生学籍管理非常重要。以下将通过对新生进行电子注册的工作步骤，谈谈Excel在管理高校学生学籍信息工作中的应用，即如何运用Excel降低学籍信息管理的错误率，如何高效准确地利用Excel完成学籍管理的各项数据的注册上传工作。

一、Excel工具及常用函数介绍

Microsoft Excel是办公自动化中非常重要的软件，Excel是具备数据计算和自动处理的功能，它又有别于数据库的表，数据库的表文件虽然具有较高的安全性、数据规范性，可以使用命令语句进行各种计算和操作等优点，但是数据库的表的灵活性、随意的复制粘贴可远远不及Excel。另外，Excel的操作数据功能可以与数据库的命令语言相媲美，同样可以进行数据匹配、数据比较、数据统计、数据检索等各种数据操作。下面我们以天津电子信息职业技术学院在学籍管理中经常使用的Excel功能函数为例，作以下简单介绍。

1. COUNTIF函数

功能：对指定区域中符合指定条件的单元格计数

格式：COUNTIF(range，criteria)

参数说明：参数range为需要计算的非空单元格的范围。参数条件可以参照计算条件，作为数值、表达式或文本形式。

2. VLOOKUP函数

功能：按单元格列查找(即垂直查找)，最终返回该列查找关键字匹配源表查找关键字所对应的列的值

格式: VLOOKUP (查找关键字lookup_ value、源表及源表引用区域table_ array、源表列号col _index_ number、匹配精度range_ lookup)

参数描述: lookup_ value是目标数据表的查找关键字，与数据源数据表table_ array中的键匹配，数据源数据表中的键位于表的第一列，查找关键字如果匹配成功，可以在目标数据表的输入表达式单元格中显示该数据。参数range_ lookup为查找参考关键字之间的匹配精度，如果为0或false是指完全匹配，为1或true是指模糊匹配。

3. EXACT函数

功能：检测两个字符串是否完全相同

格式：EXACT(text1,text2)

参数描述: text1和text2是需要比较的文本字符串的两个形式参数。如果值完全相同，则函数返回true值。否则，返回false。

二、Excel操作中需要注意的细节

除了常用Excel函数之外，还要注意Excel操作环节中的两个细节：

1.单元格文本类型内容的去空格操作

单元格文本中出现空格的原因：在Excel的各种公式中，参数一般都是单元格中的数据，有文本类型、数值类型和日期类型等，其中单元格的文本类型比较常用，因此出现含空格文本的情况较多，大大影响Excel函数的计算结果。那么文本单元格出现空格的原因是什么呢？一种情况是由于这些数据有可能是其他数据源转换过来的，如姓名字段是由SQL SERVER数据库转换过来的，而数据库中的姓名字段的字符长度设置的很长，如是VARCHAR(50)，由于在转换过程中软件语言没有进行去空格的算法的情况下，转换成的Excel的姓名字段也会是50个字符长度，但我国公民的姓名一般都是两三个字，因此该单元格中的文本就会出现大量的空格。还有一种情况就是人工输入的原因，还比如说某学生姓名为两个字，有的学生管理的工作人员习惯将姓名的中间加一个空格，或者在输入姓名的过程中由于误操作在姓名后面又输入了一个空格，这样产生的姓名数据作为Excel函数参数时就会出现匹配不上的现象，造成错误结果。因此在使用Excel函数之前有必要对这些文本单元格参数进行去空格操作。去空格操作的步骤为：选中作为参数的单元格列，点击“查找和选择”，选择“替换”弹出“查找和替换”表单，“查找内容”选项输入一个空格，“替换为”选项不输入任何内容，点击“全部替换”即可将选中的单元格列中的文本中的空格全部去掉。

2.对含有公式格式的单元格进行去公式格式的处理

单元格利用Excel函数后，单元格内容虽然已经是Excel函数的结果，但公式格式还保存在单元格中，有些函数比如VLOOKUP会有外部数据的链接，会对Excel表的其他操作造成不必要的麻烦，因此需要对含有公式格式的单元格进行去公式格式的处理。

单元格去公式的步骤：选中需要去公式的单元格列，右键点击“复制”，再右键“粘贴选项”，选择“值”即可将该列单元格的公式格式去除，只保留数值结果。

三、Excel与Visual FoxPro(VFP)文件的数据交换方法介绍

Visual FoxPro是微软开发的数据库开发软件，简称VFP，其优势是对数据的管理，它拥有自己的数据库和数据库语言。在教育部学籍学历信息管理平台的三项注册业务中，上传和下载的文件都是VFP的表文件(dbf文件)，可见dbf在学籍管理工作中的重要性。但在数据组织和匹配方面，dbf文件的灵活性远不及Excel，因此在学籍信息管理工作中，Excel文件与dbf表之间数据的相互转换工作是必不可少的，一般Excel用来组织注册数据，然后再转换成dbf文件进行教育部学籍学历信息网的电子注册。

另外dbf文件和Excel文件都有数据导出功能，并且可以选择数据导出的格式，需要注意的是dbf文件和Excel文件要保持数据字段顺序的一致性。

四、Excel在高校学籍管理中的应用

教务系统新生报到数据核准后，笔者的经验是在上传新生注册数据之前和教育部录取库的数据进行线下匹配和字段比对工作，以保证上传数据到学籍学历信息平台不会出现诸多的错误提示，保证新生电子注册工作高效准确。那么新生电子注册的线下比对和注册上传是如何进行的呢？笔者总结了以下步骤，以保证新生电子注册工作的顺利进行：

1.准备教育部录取库数据(dbf文件)

打开教育部录取库，如LQK_2018_10，文件名代表本学院2018级普通高等学校的录取数据，一般包含秋季、春季和三二分段等高考类型，文件类型为dbf，即VFP类型数据表。

2.导出教育部录取库的Excel文件

导出打开VFP的命令输入框Command，输入命令：按copy to 录取库18.xls type XL5将相同结构的XLS文件复制到DBF文件的同一文件夹下，录取库18.xls为转换成XLS的文件名，type XL5是指转出的XLS文件为Microsoft Excel5.0格式。打开此文件，将SFZH字段剪切到最左列(A列)。

3.准备新生报到数据

打开学院教务系统新生报到数据(导出的XLS表，表名为2018级教务系统在校生名单. XLS)，新增一列单元格，列名为“教育部姓名”。

4.新生报到数据的查重操作

在以往的新生数据中发现过一个身份证号出现了两条考生数据，后来经确认是因为该考生参加春季高考后又参加了秋季高考，故有两个考生号，而报到数据中是按考生号作为关键字段，所以考试院传来的招生数据有可能会出现重复数据。数据的查重使用Excel的COUNTIF函数：

第一步：将教务系统新生报到数据的身份证号一列拷到一个新的sheet表中，并放置最左列，光标点中其中任意单元格，使用Excel的高级数据过滤功能，可以将此列中的身份证号去掉重复数据后复制到其他单元格中，以便统计数据使用(图1略)，其中$A$1:$A$3546为高级筛选的列表区域，$A$1为条件区域，Sheet2!$C$1为结果要复制到的单元格。

第二步：使用COUNTIF函数。在此例中，条件参数是18位身份证号，函数的功能是统计条件参数在统计范围内的出现次数。在E2单元格中输入公式= COUNTIF ($C:$C，D2 &“*”)，确认以后即可将条件为D2单元格的身份证号的值在单元格$C:$C区域中的个数统计出来，其中$C:$C为统计区域，D2单元格为去重复的身份证号。统计个数大于1的说明该身份证号在单元格$C:$C区域中不止一个，再根据该数据查明原因，将重复数据删除。

5.使用Excel的垂直查找函数VLOOKUP从教育部录取文件中抓取数据

打开2018级教务系统在校生名单.XLS，在C2单元格中输入公式：=VLOOKUP(A2,lqk18.XLS! $A:$D,4,0)回车，即可将教育部库的姓名数据匹配过来，其中A2、lqk18.XLS！$ A:$ D、4、0等分别是与VLOOKUP函数的相对应形式参数的实际参数。选中单元格C2右下角，将光标拖到表格的末端，则所有教育部录取库的姓名数据即可匹配到该表中，其中#N/A标记表示未匹配成功，这意味着教育部的录取数据库中不包含该身份证号信息(图2略)。

6.查看数据匹配情况

根据2018级教务系统中报到入学的在校生名单中匹配的教育部姓名，可以确定新生报到数据和教育部的匹配情况，报到入学的在校生名单中，没有匹配上的条目可能有以下几种情况：其一，身份证号信息不正确；其二，身份证号的单元格有空格，若是存在空格将空格去掉；其三，学生是往届生复学到本年级的。诸如上述情况需核实清楚后重新匹配，复学的学生信息需要删除。

7.精确比较教育部姓名和新生报到姓名的一致性

为确保匹配信息的准确性，需要教育部姓名和教务系统在校生名单中姓名的一致性。在教务系统在校生名单中增加一列“姓名比对”，在D2单元格中输入精确比较公式EXACT(text1,text2)，参数text1和text2分别选中C2和F2，回车即可比较出两个参数是否完全相同，结果完全相同返回TRUE值，否则返回FALSE值。选中D2单元格，拖动光标至文件尾，即可完成所有的教育部姓名和教务系统在校生姓名比对工作。该例中教务系统的姓名“王子建”和教育部录取库的姓名“王子健”不一致，所以比对结果为False，需要查明原因进行更正。

图3 姓名比对

图4 新生注册上传数据表STRU_XJZC.DBF

姓名字段匹配后即确定了新生报到人数和教育部录取库的比对条数，即新生电子注册的条目数，再用同样的方法将教育部录取库中的性别、专业、学制等字段信息匹配到教务系统在校生名单中来，再用EXACT函数一一比较其正确性。

8.准备新生电子注册的Excel模板及数据

从学籍学历信息网平台下载新生电子注册模板STRU_XJZC.DBF，打开该文件，在命令框输入命令：COPY TO新生注册.XLS TYPE XL5，即可在同一文件夹下创建一个新生注册.XLS。再打开新生注册.XLS，按照教育部学籍学历信息网新生注册的字段要求，将教务系统在校生名单中的数据分别复制粘贴到新生注册.XLS中，需要注意的是字段粘贴顺序要和教育部新生电子注册模板的字段顺序要求一致。全部字段复制粘贴后，确认一下总条目数，看一下是否和预期的注册数一致，确认无误后将第一行字段行删除，保存关闭新生注册.XLS。

9.Excel注册数据导入教育部电子注册模板(dbf文件)

方法1：打开STRU_XJZC.DBF，在命令框输入命令：APPEND FROM 新生注册.XLS TYPE XL5 SHEET “新生注册”，回车，关掉命令窗口或用BROWSE LAST命令即可看到导入过来的新生注册信息(如图3)。方法2：打开STRU_XJZC.DBF，点击table菜单，选择Append Records，弹出Append From表单，Type选择Microsoft Excel 5.0，From选择数据源数据表新生注册.XLS，sheet选择“新生注册”sheet表，点击ok也可将XLS数据导入DBF文件中。

10.电子注册数据上传

保存好STRU_XJZC.DBF文件(如图4)即可进行教育部学籍学历信息网新生电子注册上传工作。因为在此之前已经对新生报到条目及各字段进行了比对，所以在新生电子注册上传过程中很少出现错误提示，进而大大提高了新生电子注册的工作效率。

总之，以上所述即为新生电子注册的工作步骤，因为新生电子注册工作在教学管理中十分重要，所以学籍管理者既需要有认真负责的工作态度，而且还需掌握一定的计算机信息管理知识，方可把学籍注册工作做好。另外每年的学年电子注册和毕业生电子注册都可按照此方法进行操作，基本上可保证数据的准确上传注册，确保无遗漏数据的发生。