关键词：Excel;自动化阅卷;函数
微软公司开发的电子表格软件——Microsoft Excel，具有功能非常强大的内置函数库，可以完成数学和三角函数、统计、财务等各种运算。用Excel制作具备自动化阅卷功能的电子试卷有很多方法，笔者就综合利用函数方式制作电子试卷的过程作简要描述。
一、试卷功能及特点
1、该试卷主要针对选择、填空及判断题型进行设计，主观题不适用；
2、该试卷需要配合机房教学管理软件，将试卷进行自动化发送和收集；
3、保护试卷结构并限定答题区，只允许学生在规定区域答题；
4、老师打开考卷后只需在规定单元格中输入预设的“阅卷密码”，就可实现自动阅卷和计分；
5、可以在Excel中手动输入试题，或用Word软件将制作好的试卷导入Excel工作表中。
二、试卷结构设计
电子试卷由试卷头部、答题区、试题区（单选题、多选题、填空题及判断题）三部分构成。试卷制作完成后，效果如图1所示。
.png)
三、试卷制作
新建Excel文件,重命名为“电子试卷.xlsx”。将sheet1工作表重命名为“试卷”，在该工作表中录入试卷表头、答题区及试卷内容。试卷的字体、字号、颜色、边框线等内容可根据需要进行设置。
（一）试卷表头
在第一行相应位置输入试卷头信息。包括：班级、姓名、学号、阅卷密码、总成绩及考试时间。为了让学生更容易看到这些重要信息，笔者用黄色底纹和黑色线条进行了标识。
说明：
1、因为填写“班级”和“姓名”时可能内容比较多，所以最好合并多个单元格。
2、“阅卷密码”应输入在L1单元格中，且要等到阅卷时才能输入。阅卷密码可随意设置，但出于安全性考虑，可将字母、数字及标点符号综合应用。如lswx_0501等。
3、设置“总成绩”的计算公式：=IF(L1="lswx_0501",SUM(B6:U6,B10:K10,
B14:U14,B18:K18),"")。该公式的作用是：当阅卷密码输入正确后，就自动计算总成绩；若阅卷密码没有输入或输入错误，则不计算总成绩。
（二）答题区
将不同题型的试题输入到设定的单元格区域中。参照图1进行设置。具体设置如下：
1、单选题
假设单选题有20道题，每题1.5分，共计30分。
注意:填写答案时，请填写小写字母。
在第三行中对应位置输入题号，在第四行输入正确答案，将第五行对应位置留作考生填写答案，在第六行进行“得分”判定。
在B6单元格中输入公式：=IF($L$1="lswx_0501",IF(B4=B5,1.5,0),"")，然后选定B6单元格，拖动填充柄，向右自动填充公式至U6单元格。该公式的作用是：当L1单元格中的内容为设定的阅卷密码时，判断考生输入的答案与设定的正确答案是否一致，若一致，得1.5分，若不一致，得0分；若L1单元格中内容不是阅卷密码，则不进行得分判定。
标准答案不能让考生看到，需要将该行设置为隐藏状态。具体方法是：选定第四行，然后单击“开始”选项卡，在“单元格”命令组中，点击“单元格”命令的向下三角形按钮，从弹出菜单中单击“隐藏和取消隐藏”命令列表中的“隐藏行”命令。
2、多选题
假设多选题有10道题，每题4分，共计40分。
多选题的得分规则是：所有选项均填写小写字母；若考生不作答或答案有错误选项，得0分；答案全部选对得4分；答案不全对时，选对几个就得几分。如正确答案为ABCD，考生只选择了ACD，则得3分。
在第七、八行分别输入题号和标准答案，第九行对应单元格用于考生填写答案，第十行用于判定每道多选题的得分。其中“正确答案”这一行可参照之前的设置，将该其隐藏。
在多选题对应单元格中输入“得分”公式。如在B10中输入公式：
=IF($L$1="lswx_0501",IF(B9="",0,IF(AND(ISNUMBER(FIND(MID(B9,{1,2,3,4},1),B8))),IF(LEN(B9)=LEN(B8),4,SUM(IF(AND(ISNUMBER(FIND("a",B9)),ISNUMBER(FIND("a",B8))),1,0),IF(AND(ISNUMBER(FIND("b",B9)),ISNUMBER(FIND("b",B8))),1,0),IF(AND(ISNUMBER(FIND("c",B9)),ISNUMBER(FIND("c",B8))),1,0),IF(AND(ISNUMBER(FIND("d",B9)),ISNUMBER(FIND("d",B8))),1,0))),0)),"")。
该公式的作用是：
（1）如果在L1中没有输入密码或密码输入不正确，则不进行得分判定；
（2）如果在L1中密码输入正确，则进行得分的判定。首先，如果考生不作答，即B9单元格为空，则得0分；如果B9不为空，则利用MID函数，将考生填写的答案依次取出一个字母与正确答案进行判定，利用FIND、ISNUMBER和AND函数可以判定，如果考生的答案都在正确答案中，且考生填写的答案与标准答案长度相同，则答案正确，得4分；否则，如果考生选择的答案有部分不在标准答案不同，则选择有错，得0分；最后，当考生选择的答案只有部分是正确的，则用四个IF语句判定考生选对答案的个数，将其对应的得分相加就是最后得分。
3、填空题
假设填空题有20个空，每空1分，共计20分。
在第十一行、十二行分别输入填空题的编号和标准答案，设定第十三行用于考生填写答案，第十四行用函数进行得分判定。其中标准答案这一行需要进行隐藏设置，考生得分由公式判定。
如在B14单元格中输入公式：=IF($L$1="lswx_0501",IF(B12=B13,1,0),"")。该公式的功能参照单选题的判定。
4、判断题
假设判断题有10道题，每题1分，共计10分。
在第十五行、十六行中分别输入判断题的编号和标准答案，将标准答案隐藏。将第十七行设置为考生填写答案区域，在第十八行对应单元格总输入公式，用于判定考生得分。
如在B18单元格中输入公式：=IF($L$1="lswx_0501",IF(B16=B17,1,0),"")。
该公式的功能参照单选题的判定。
设置好答题区内容后，为了方便考生填写答案，需要将试卷头和答题区进行冻结设置。具体方法是：用鼠标在行号上进行拖动选择，将第一行至第第十八行选中，然后在“视图”选项卡的“窗口”命令组中单击“冻结窗格”命令，选中弹出下拉列表中的“冻结窗格”命令。
（三）试卷内容
试卷内容的制作有两种方法。第一种方法是直接在Excel单元格中输入，第二种方法是先用Word软件制作好电子试卷，然后将其导入到Excel中。
鉴于Word软件的通用性与强大的文档排版功能，笔者在此推荐用大家使用第二种方法。用Word对试卷内容进行排版时,要求每道选择题的题目占1行,4个选项各占1行,共需要5行。另外，还需注明每种题型的题量、分值及得分规则。
试卷内容输入完毕后，将Word文档导入Excel工作表。操作过程为：
打开“电子试卷.XLSX”文件，将光标定位到试卷答题区下方，选定好导入位置（即某单元格）后，如A20单元格，将Word文档中的试题复制后粘贴到该位置。注意，粘贴时要选择“匹配目标格式”方式。此时，试题内容很规规范的显示在A20：A198单元格区域中，试题导入完成。
为了避免考生答题时，因误操作破坏试卷内容，最好将试卷结构及内容进行保护设置。将试卷头和考生答题区域设置为允许考生操作权限，其他区域设置为限制考生编辑。
保护工作的操作步骤是：
第一步，设置允许学生编辑的区域。
单击“视图”选项卡，在“保护”命令组中，单击“允许编辑区域”命令。在弹出的对话框中，单击“新建”命令，在“修改区域”对话框中，将“标题”更改为“考生编辑区”，在“引用单元格”框中输入允许编辑的单元格区域，然后单击“确定”按钮。本文中的试卷头和考生答题区所在单元格区域是：=$B$1,$E$1,$H$1,$L$1,$B$5:$U$5,$B$9:$K$9,$B$13:$U$13,$B$17:$K$17。
第二步，保护试卷设置。
单击“视图”选项卡，在“保护”命令组中，单击“保护工作表”命令。
四、使用电子试卷
在机房中，配合多媒体教学软件（如苏亚星多媒体教学软件）将电子试卷发送到学生机端，待学生完成测试后，将试卷收回，并分班级保存在不同的文件夹中。
老师只需要将每位同学的试卷打开，正确输入阅卷密码，试卷就自动完成批阅。如此重复操作，就可以快速批阅所有同学的试卷。最后，老师将所有同学的成绩记录在成绩册即可。
参考文献：
[1]周南岳,黄国兴.计算机应用基础（Windows 7+Office 2010）第3版[M].北京：高等教育出版社，2014.
[2]高万萍，吴玉萍.计算机应用基础（Windows 7+Office 2010）[M].北京：清华大学出版社，2013.
论文作者:杜大林
论文发表刊物:《教育学文摘》2019年第9月17期
论文发表时间:2020/4/2
标签：试卷论文; 单元格论文; 考生论文; 答案论文; 公式论文; 得分论文; 命令论文; 《教育学文摘》2019年第9月17期论文;