基于Excel的考试系统设计与实现

基于Excel的考试系统设计与实现广东省佛山市南海区石门中学狮山校区刘世能邮编：528225 摘要：对于Excel办公软件，随着版本的升级，其功能也越来越强大，针对考试系统高技术高要求的情况，我们探讨了用Excel设计考试系统，实现了试卷制作简便，分发评判容易，统计快速的考试功能，可低技术、简便、灵活地解决教学中的课外作业考查、考试评价调查等问题，有一定的实用价值。

关键词：考试系统，Excel正文：当前有很多计算机考试系统，既有用各种语言开发的单机版软件，也有用Asp等开发的网络版系统，单机版软件适合学生自测，但一般无成绩导出汇集统计功能，因而不适合班级统一测试与成绩分析；网络版考试系统需要服务器等高技术支持，对网络带宽有一定要求且一般用于局域网，因此无法也不方便各科推广使用。

随着信息技术的普及，学校师生已经基本掌握office系统办公软件的操作，特别Excel作为老师统计成绩、管理成绩的好帮手，又是学生学习的实用工具，因此大家对它非常熟练。

下面笔者探讨了用Excel作为考试系统的设计与研究平台，实现了试卷制作简便，评判容易，统计快速的考试功能，而且技术含量低，操作简便、灵活，非常适合家庭作业考查或课堂测试等。

一、基于Excel的考试系统设计与实现以课堂测试为例，包括基本的单项选择题、多项选择题、判断题、填空题、简答题等，下面详细介绍制作思路与主要技术。

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

信息流程与功能设计如下：主要技术要点：1．输入设计：与传统试卷一样，Excel试卷包括试卷标题、试卷说明、试卷正文（试题），另外还有班级、姓名、学号、解答处、分数等填写项。

试卷内容输入与排版无特殊之处，输入解题信息如采用直接在相应单元格中输入，则会增加学生解答要求，如答案字母的大小写、空格等均会影响学生成绩，对此我们采用了数据序列与窗体控件进行选择设计，尽量减少操作对成绩的影响，下面谈谈两种选择输入的设计：数据序列：在输入单元格中单击“数据”菜单选择“有效性（L）…”，在允许中选择序列，在来源中设计要选择的信息，设置与结果如下图一、图二。

这种方法在班级信息、单项选择题与判断题答案输入中显得直观、易操作，只须单击选择项便可输入答题信息。

窗体控件：打开“视图”菜单工具栏下的窗体工具，首先单击分组框工具，待鼠标变成十字光标后在相应题上画出一个矩形框，并输入相应题号（本处第4题），同样在该题选项左边画出选项按钮，为了美观删去文字，最后界面如图三，然后双击选项按钮，在设置控件格式中设置本组选项按钮链接到选择状态单元格$G$8中，如图四所示，当单击选项A、B、C、D前选项按钮时，单元格$G$8会显示相应序号1、2、3、4，未选择为0；复选框的设计基本同选项按钮，但每一个复选框均需链接到一个单元格中，如图五所示，当单击复选框A时，复选框A对应单元格会显示TRUE，否则显示FALSE，因此这种设计方法需用4个单元格表示选择状态，复选框用于多项选择题的设计比较合适，符合网页式的选择模式，选项按钮用于单项选择题、判断题的设计，选择答案时只须单击控制即可，非常方便。

2．试题评判与计算总分：首先根据输入的答案，用IF函数进行判断，函数格式为：IF(Logical,Value_if_true,Value_if_false)，此处Logical为输入答案单元格与答案所在单元格比较，如果条件为真，在批改状态中显示“正确”，否则显示“错误”，对多项选择题，如果少选也给分的话，在Value_if_false中可以用嵌套本函数，然后根据批改状态给出得分：全对满分，少选且无错误选项得一半分，只要有一个错误则无分。

在单选、多选、判断评评改中，由于输入方法不同，相应批改方法也不同，下面分情况讨论：数据序列：在作答情况单元格中，单击下拉箭头直接获得选项，再读取答案文件相应单元格的标准答案，通过比较可判断正误。

批改状态参考公式为：IF(G5=[daa.xls]Sheet1!B4,"正确",IF(G5="请选择","未答","错误"))。

选项按钮：由于选中选项按钮时，显示结果为数字，如0，1，2等，不是标准答案中所示的ABCD，所以必须用IF函数在作答单元格中把选择状态中的数字转换为相应的ABCD或对错，参考公式为：IF(J8=0,"请选择",IF(J8=1,"A",IF(J8=2,"B",IF(J8=3,"C","D"))))。

批改状态判断同上。

复选框：如图六示，复选框选中与不选有两种状态：TRUE与FALSE，要让它与ABCD联系起来，除了设置选择状态单元格外，还须设置转换状态单元格，通过IF函数把两种状态TRUE、FALSE分别转换为相应的ABCD或空串，参考公式为：IF(J10=TRUE,"A","")，其他BCD转换单元格公式类似。

然后在作答情况单元格中通过字符串连接函数把四个转换状态单元格内容顺序连接起来，参考公式为：CONCATENATE(N10,O10,P10,Q10)。

最后批改正误，如果只是全对与错的话，则判断较为简单，参考公式同数据序列；如果少选也要给分的话，那情况较为复杂，下面给出一种参考设计：首先在答案文件中把答案按A、B、C、D 四个单元格顺序逐个填写，没有的不填即为空串，如图七示。

然后批改时先对未答与全对情况判断，接着排除错误情况，最后剩下的就是半对情况。

批改状态参考公式为：IF(AND(N10="",O10="",P10="",Q10=""),"未答",IF(AND(N10=[daa.xls]Sheet1!B9,O10=[daa.xls]Sheet1!C9,P10=[daa.xls]Sheet1!D 9,Q10=[daa.xls]Sheet1!E9),"全对",IF(OR(N10>[daa.xls]Sheet1!B9,O10>[daa.xls]Sheet1!C9,P10>[daa.xls]Sheet1!D9, Q10>[daa.xls]Sheet1!E9),"错误","半对")))文中涉及的填空题，只要在作答情况单元格直接输入即可，批改方法基本数据序列；至于简答题，由于情况比较复杂，没有办法用智能判断，只能人工批改。

计算总分：数据序列、填空、选项按钮得分判断参考公式化：IF(H5="正确",2,0)（注：2为每题分值），多项选择题得分判断参考公式为：IF(H10="全对",4,IF(H10="半对",2,IF(H10="未答",0,IF(H10="错误",0))))（注：4为每题分值），根据试卷界面设计，由于题型标题占几行单元格，因此分数需要分段统计，参考公式为：SUM(I5:I8,I10:I11,I13:I15)。

成绩统计：学生做完题后，以试卷名称&班级&学号作文件名保存，这样方便在统计文件中读取答卷文件单元格信息，从而将班级、姓名、学号、分数、作答情况、主观题作答情况等信息读入成绩统计表中相应单元格，参考公式：[lx211.xls]Sheet1!$H$2。

由于此处变化的是试卷文件名lx211.xls，因此无法用拖动复制公式法完成其他同学的答题信息录入，只能在复制完公式后，利用查找-替换方式逐个修改公式内容，如图八示，从而快速完成信息输入。

信息输入完毕，就可以按照标准化考试模式完成试卷分析与评价。

针对每次课堂测试需要读取太多的文件，对老师来说也是非常麻烦的事，对此我们偿试结合VB程序编写了一个读取程序，大大加快了信息的录入。

参考代码如下：Dim x As Integer '写入文件的行号变量Dim s As String '试卷文件名变量x = 1Set xlApp1 = CreateObject("Excel.Application") '创建EXCEL对象Set xlbook1 = xlApp1.Workbooks.Open("c:\b.xls") '打开已经存在的EXCEL工件簿文件，此处B.XLS为空文件。

Set xlSheet1 = xlbook1.Worksheets("Sheet1") '设置活动工作表DoSet xlApp = CreateObject("Excel.Application") '创建EXCEL对象s = "c:\lx30" + CStr(x) + ".xls"Set xlBook = xlApp.Workbooks.Open(s) '打开学生考试EXCEL工件簿文件xlApp.Visible = False '设置EXCEL对象不可见（或可见）Set xlSheet = xlBook.Worksheets("Sheet1") '设置活动工作表'将学生文件第2行第2列即b2单元格内容写入b.xls文件sheet1工作表的第x行,第2列)xlSheet1.Cells(x, 2) = xlSheet.Cells(2, 2) '班级信息xlSheet1.Cells(x, 3) = xlSheet.Cells(2, 3) '学号信息... '此处需要根据试卷情况设置xlBook.Close (True) '关闭工作簿x = x + 1xlApp.Quit '结束EXCEL对象Set xlApp = Nothing '释放xlApp对象Loop While x < 61 '此处班级人数61人,如果不同请自行设置xlApp1.Quit '结束EXCEL对象Set xlApp1 = Nothing '释放xlApp对象(新版读取程序代码)Dim xlApp As Excel.ApplicationDim xlApp1 As Excel.ApplicationDim xlBook As Excel.WorkbookDim xlbook1 As Excel.WorkbookDim xlSheet As Excel.WorksheetDim xlSheet1 As Excel.WorksheetPrivate Sub Command1_Click()Dim a As StringDim x, y As IntegerDim s As Stringx = 1Dim fs, f, f1, fcSet fs = CreateObject("Scripting.FileSystemObject")Set f = fs.GetFolder(Trim(Text1.Text))Set fc = f.FilesSet xlApp1 = CreateObject("Excel.Application") '创建EXCEL对象Set xlbook1 = xlApp1.Workbooks.Open("d:\b.xls") '打开已经存在的EXCEL工件簿文件Set xlSheet1 = xlbook1.Worksheets("Sheet1") '设置活动工作表xlApp1.Visible = False '设置EXCEL对象可见（或不可见）xlApp1.AskToUpdateLinks = FalseFor Each f1 In fcSet xlApp = CreateObject("Excel.Application") '创建EXCEL对象s = temp = Right(s, 3)If temp = "xls" Then's = "c:\lx30" + CStr(x) + ".xls"'MsgBox xSet xlBook = xlApp.Workbooks.Open(Trim(Text1.Text) + "\" & s) '打开已经存在的EXCEL工件簿文件xlApp.Visible = False '设置EXCEL对象可见（或不可见）xlApp.AskToUpdateLinks = FalseSet xlSheet = xlBook.Worksheets("Sheet1") '设置活动工作表'xlSheet.Cells(2, 2) = 11 '给单元格（row,col）赋值'xlSheet.PrintOut '打印工作表a = xlSheet.Cells(2, 2)b = xlSheet.Cells(2, 4)c = xlSheet.Cells(2, 6)d = xlSheet.Cells(2, 8)y = Len(s)s = Left(s, y - 4)y = Len(s)s = Right(s, y - 4)xlSheet1.Cells(x, 1) = axlSheet1.Cells(x, 2) = bxlSheet1.Cells(x, 3) = cxlSheet1.Cells(x, 4) = dxlSheet1.Cells(x, 5) = s'xlSheet1.Cells(x, 1) = sx = x + 1xlBook.Close (True) '关闭工作簿'MsgBox s'x = x + 1'MsgBox axlApp.Quit '结束EXCEL对象xlApp.Quit '结束EXCEL对象Set xlApp = Nothing '释放xlApp对象End IfNextxlApp1.Quit '结束EXCEL对象Set xlApp1 = Nothing '释放xlApp对象'xlBook.RunAutoMacros (xlAutoOpen) '运行EXCEL启动宏'xlBook.RunAutoMacros (xlAutoClose) '运行EXCEL关闭宏MsgBox "成功读取成绩，请到D盘把b.xls移走"End Sub二、Excel考试系统的几点应用经验1．设计试卷时，在选定选项按钮、复选框的时候最好先对它点击右键然后再点击左键，不然很难选中；对于选项按钮与数据序列的使用，在单选、判断的设计中用数据序列比较简单而又高效，用选项按钮设计虽然较复杂，但与多选题的复选框一起使用时会比较统一，因此可根据情况选用；要善用公式复制等操作复制或自动填充信息，提高制作效率，如窗体、作答情况、批改状态等信息；为减少监考难度，可打乱题目顺序设计A卷、B卷、C卷。