excel试卷分析系统：基于Excel的自动阅卷评分系统

基于Excel的自动阅卷评分系统

xxx

雅安职业技术学院 四川 雅安 625000

摘要：在教学中，教师一直在寻找一种环保、快速的考核评价方式来考察学生的成绩，但由于对编程的恐惧，只好选择传统的纸质考试方式。用Excel来制作自动阅卷评分系统，能大大的节约教师的阅卷、评分、统分时间，有效地提高工作效率和准确率。

关键字：计算机应用技术； Excel函数； 自动阅卷 Auto scoring system based on Excel

Luo Cuiqiong

Ya'an Vocational College Sichuan Ya'an 625000

Abstract: in the process of teaching, teachers are always looking for an environmental protection, rapid evaluation method to examine student achievement, but due to the programming of the fear, had to choose the traditional paper examination. To make use of Excel automatic marking system, can save teachers greatly marking, score, the time, effectively improve the work efficiency and accuracy. Keywords: computer application technology; Excel function; automatic marking

1 设计理由

作为一位高职院校的公共课教师，在每学期期末面对阅卷、分数统计、成绩分析时的庞大工作量，很多老师都感觉是一项既浪费时间又消耗脑力的工作，不仅工作效率低而且容易出错。运用电子表格把传统纸质考试的阅卷、统计分数和成绩分析变成可以自动阅卷、统计分数和成绩分析的方式，可以大大减轻老师们的工作量，从而极大的提高工作效率和准确率；同时也可以大大减轻学校教务处的印刷工作量，节约大量纸张，这样既经济又环保，还可以省掉保管试卷的档案室和相应的精力，降低办学成本。

2 系统设计思路

在Excel中制作好试卷，在指定单元格输入学生基本信息、作答情况，系统通过IF函数读取标准答案并判断正误和计分，学生试卷保存后交给老师，最后在成绩统计文件中读出所有学生得分或答题情况，计算总分、平均分，并可做成绩分析。

在设计自动阅卷评分系统的时候，在同一个Excel工作薄中新建两张工作表，一张工作表按照试卷的格式设计试卷，学生可以在试卷上答题，但不能改动试题；另一张工作表用来自动判断、统计学生答题的情况和分数、统计总分，这张工作表应隐藏，不能让学生看见。

2.1 试卷设计

（1）打开Excel文档，建立“试卷”工作表，合并A1：G1单元格，在A1单元格中输入试卷名称“雅安职业技术学院《计算机应用基础》普通专科公共必修课2013年期末测试试卷”并居中。在A2单元格输入“班级”，D2单元格输入“学号”，F2单元格输入“姓名” ( 合并B2：C2单元格，将B2，E2，G2单元格设置成蓝色底纹。)。在A3：G6单元格区域设置。

（2）填空题的设计，合并A7：G7，在单元格中输入“一、填空题（每空1分，共20分，请将正确答案填写在相应小题下一行红色文字“答题处”所在单元格内）”，合并A8：G8，在单元格中输入题目，例如：“1、在WINDOWS XP中，若系统长时间不响应用户的要求，为了结束该任务，应使用的快捷键组合是CTRL+ALT+（ ）。”在A9单元格中输入“答题”，并将单元格设置成蓝色底纹，按此方式设计10题。

（3）选择题的设计，合并A29：G29，在单元格中输入“二、选择题（单选题，每题2分，共50分。请在每小题右边的“选择答案处”内选择正确答案对应的大写字母）”合并A31：G31，在单元格中输入题目，例如：“2. 要在Excel2003单元格中输入任何分数并保留输入时的数字和数位，方法是：。”合并A32：C32，D32：G32，A33：C33，D33：G33，分别在这四个单元格中输入A、B、C、D四个选项。单击H列设置数据有效性，打开数据有效性对话框，在“设置”选项卡中，有效性条件允许中选择“序列”，来源中输入“选择答案处,A,B,C,D”（不含引号且其中逗号为半角格式），勾选“提供下拉箭头”，单击“确定”按钮完成设置。按此方法共设计25个选择题。

2.2 评分表的设计

（1）在同一个工作薄中建一个工作表命名为“评分表”，合并A1：F1单元格，在单元格中输入“雅安职业技术学院公共必修课《计算机应用基础》评分表”，分别在A2，E2单元格中输入“学号”、“姓名”，为了在评分表中能够自动获取考生在试卷中填写的信息，在B2单元格中输入“=试卷!E4”，在F2单元格中输入“=试卷!G4”。然后在A3：F4单元格区域中设置一个得分统计表，用于统计各题的分数和总分。

（2）填空题得分表设计，由于每一道题中需要考生作答的空不一样，有的题可能只有一个空需要作答，有的题需要作答的空不止一个，这就要求我们在设计填空题得分表的时候需要考虑到有多个空需要作答的.情况，因此在设计得分表时，按照最多空数来设计表格。合并A5：F5单元格，在单元格中输入“一、填空题（每空1分，共20分）”并设置该单元格格式为字体：仿宋、字号：16号、对齐方式：左端对齐。在A6单元格中输入“题号”，在B6：F6中分别输入“分值一、分值二、分值三、分值四、分值五”，在A7：A21中填充序列1到15。

（3）选择题得分表设计，选择题相对于填空题来说就非常简单了，每道题只有A、B、C、D四个选项，其中只有一个正确答案，选择正确得2分，不正确就得0分。

3 自动阅卷评分的实现

3.1 填空题

由于填空题的答案有的不是唯一的，有的顺序是可以调换的，因此在评分设计上需要综合考虑到各种因素的存在。这里以第一个题目的设计为例进行详细的介绍。在“评分表”B7单元格中输入：“=IF(OR(试卷!A9="DELETE",试卷!A9="",试卷!A9="Delete",试卷!A9="DElete",试卷!A9="DELete",试卷!A9="DELEte",试卷!A9="DELETe",试卷!A9="deletE",试卷!A9="deleTE",试卷!A9="deletE",试卷!A9="delETE",试卷!A9="deLRTE",试卷!A9="dELETE"),1,0)”，应用了IF函数和OR函数的嵌套，意思是只要“试卷”工作表中A9单元格中的答案与他们中的其中一个相同，“评分表”工作表中B7单元格中的值就为“1”，否则为“0”。

3.2 选择题

在选择题的评分设计上，为了能够适用于不同的题目。我们可以通过如下几步来完成：

（1）在“评分表”工作表中任意的空白单元格区域设计一个“标准答案表”，我们在H16：I42单元格区域设置“标准答案表”。

（2）在“评分表”B18单元格中输入简单的IF函数“=if(试卷！H32=I18，2，0)”，意思是如果“试卷”工作表中H32单元格的内容与“评分表”I18单元格中内容相同，“评分表”B18单元格的值显示为“2”，否则显示为“0”。同理完成其他选择题的评分。

3.3 成绩的统计与显示

B4单元格用于统计显示填空题的分值，我们在B4单元格中输：“=SUM(B7:F14)”，该函数求和的结果就是填空题所得的分值。C4单元格用于统计显示选择题的分值，与填空题的统计方式相同，在C4单元格中输入：“=SUM(B18:B30,D18:D30,E18:E30)”。接下来就是要算出总分，在E4单元格中输入：“=SUM(B4:D4)”。这样就完成了试卷的自动阅卷和自动统分，极大的提高了数据处理的效率。

4 权限的设置

学生对试卷只有答题的权限，对工作表没有修改和设置的权限，还不能让学生看到“评分表”，因此需要对工作表进行保护设置。

（1）将“评分表”工作表中不需要学生填写作答的所有单元格选中，点击鼠标右键打开“设置单元格格式”对话框，选择“保护”选项卡，勾选“锁定”，然后确定。

（2）在菜单栏中单击“审阅”，单击“保护工作表”打开对话框，设置保护密码和所有用户的操作权限。

（3）首先选择“评分表”工作表单击鼠标右键，单击“隐藏”，然后，在菜单栏中单击“审阅”，单击“保护工作薄”选择“保护结构和窗体”，打开“保护结构和窗体”对话框，勾选“结构”和“窗体”，输入密码。

在本案例中只对填空题和选择题做了说明，当然对于判断题、简答题等题型也可以进行相应的设置。对简答题的评分可以采用模糊数学中的单项贴近度作为一个度量依据，有效的减少误判，以答案中的关键字、核心字词句作为参考标准来判断。

5 结束语

考试是教学评价的一个非常重要的组成部分，是一种非常重要的教学检验手段，主要是为了考察教师的教学质量和学生的学习效果。基于Excel的自动阅卷评分系统与传统的考试方式相比，节约了教育资源，减少了人力、物力、财力的消耗；实现了无纸化考试，自动阅卷评分，减轻老师负担。本系统在设计中只用到了一些简单的常用函数，整体设计很简单。最后，希望本文能够为大家在探索自动阅卷评分系统以及Excel的功能等起到一点点抛砖引玉的作用。

参考文献

[1]贲黎明l操作题自动阅卷系统的设计与实现[J].常熟理工学院学报，2008年08期

[2]刘玉英l公式和函数在成绩统计表中的应用[J].科技论坛，2008

[3]陈京.浅谈如何利用Excel提高教学管理的办公效率[J].中央民族大学学报，2005.03