用EXCEL制作模拟考试系统.pdf

1. 建立题库 打开 Excel 2003 ，建立“单选题”工作表( 如图 1) ，在 A2输入公式 =ROW()-1&“.“ 以自动生成序 号，在 D2输入=IF(C2=“,“,RAND()，选中 A2:D2 拖动其填充柄向下复制到其他单元格，大概有几 百行也就够了，剩下的需要时再复制。按住Ctrl键拖动工作表标签复制出两份，分别重命名为多选 题、判断题。 图 1 2. 设计试卷 建立“试卷”工作表，你可以按需要设置试卷，并输入单选题、多选题、判断题三个大题目，多 选题需注明错、漏、多选均不得分。并留出相应的题目位置，单选题30 题(A5:C34) ，多选题 40(A36:C75) ，判断题 30 题(A77:C106) 。 用“自选图形 / 基本形状”中的棱台画一个按钮，选中在名称框中输入“出题按钮”4字回车， 把它命名为“出题按钮”。再右击此按钮选择“添加文字”，在按钮上输入“出题”2字。同样再画 一个按钮，把它命名为“评分按钮”，添加文字“评分”。( 如图 2) 图 2 3. 显示题目 在 A5输入公式 =INDIRECT(“单选题 !R“&ROW()-3&“C“&COLUMN(),0)， 选中 A5复制，再选中 A5:C34 进行粘贴。同样在A36输入公式 =INDIRECT(“多选题 !R“&ROW()-34&“C“&COLUMN(),0) 并复制到 A36:C75，在 A77输入公式 =INDIRECT(“判断题 !R“&ROW()-75&“C“&COLUMN() 并复制到 A77:C106。在 E4 单元格输入公式 =IF(D4=C4,1,0) ， 这公式用来判断如果D4中的答案与 C4的标准答案一致就得1 分否 则为 0分。 A5 的公式表示对单选题工作表中同列、行数差3 的单元格 (A2) 的引用，公式中使用了R1C1格式 的单元格编号，其中3、34、75 是由第一题所在的行数减2 得来。这公式实现的是绝对位置引用，即 不管在单选题工作表中进行插入、删除行或排序，A5显示的始终都是你从单选题A2中直接看到的内 容。因此 A5:C34、A36:C75、A77:C106三个区域将分别与相应题库中最前面的30(或 40)题的内容保 持一致。 4. 答案限制 选中 D5:D34单击“数据 / 有效性”，在“允许”中选择“系列”， 选中“提供下拉箭头”复选项， 在“来源”中输入“ A,B,C,D”，不含引号且其中逗号为半角格式(如图 1)。切换到“出错警告”选 项卡，输入错误信息为“只能输入A、B、C、D中的一项，其他任何符号或空格均为非法。”，选中 “输入无效数据时显示警告”复选项，选择样式为“停止”(如图2) ，确定完成设置。 图 1 图 2 同样对多选题D36:D75，判断题 D77:D106设置数据有效性，只是“来源”要分别换成 “A,B,C,D,B,AC,AD,BC,BD,CD,ABC,ACD,BCD,ABCD”和“ , ”。 “出错警告”也要做相应修改，特 别是多选题一定要说明必须按ABCD 的顺序输入多选项，否则无法正确评分，若觉得多选题要输入这 一大串有效性设置太麻烦， 不设置有效性也是可以， 你只要在题目中注明不按顺序的不给分不就行了。 5. 准考证号验证 建一个“考生名单”工作表，在A、B、C列分别输入序号、准考证号、考生姓名，选中B列在名 称框中输入“考号”，回车确定把B列命名为考号 ( 如图 3)。回到“试卷”工作表选中要输入准考证 号的 D2单格，单击“数据 / 有效性”，在“允许”中选择“系列”，取消“提供下拉箭头”复选项， 在“来源”中输入 =INDIRECT(“考号 “) 。切换到“出错警告”选项卡，输入错误信息为“你输入的准 考证号并不存在，请重新输入”，选中“输入无效数据时显示警告”，并设置样式为“停止”。在 F3 单元格输入公式 =IF(D2=“,“,VLOOKUP(D2, 考生名单 !B:C,2,FALSE)，这公式会在输入准考证号 后自动显示考生姓名。 6. 随机抽题 右击“出题”按钮， 选择“指定宏”， 在弹出窗口中， 输入宏名为“出题”， 单击“新建”按钮， 编辑如下代码 : Sub 出题 () If Range(“D2“).Value = 0 Then 提示 = MsgBox(“ 请先输入准考证号 “) GoTo 重来 End If 如果 D2为空则弹出提示要求输入准考证号并直接跳到后面的“重来: ”处。 Sheets(“ 单选题 “).Range(“A:D“).Sort Key1:=Sheets(“单选题 “).Range(“D2“), Order1:=xlAscending, Header:=xlGuess Sheets(“ 多选题 “).Range(“A:D“).Sort Key1:=Sheets(“多选题 “).Range(“D2“), Order1:=xlAscending, Header:=xlGuess Sheets(“ 判断题 “).Range(“A:D“).Sort Key1:=Sheets(“判断题 “).Range(“D2“), Order1:=xlAscending, Header:=xlGuess 这三行分别是对单选、多选和判断题工作表的A:D 区域按 D列随机数升序排序。 Columns(“B:B“).Rows.AutoFit 对 B列设置最适合行高，以完整显示题目。 Range(“D5“).Select 定位到 D5以便输入答案 ActiveSheet.Shapes(“出题按钮 “).Visible = False 隐藏出题按钮以防重复选题 ActiveSheet.Shapes(“评分按钮 “).Visible = True 显示评分按钮 重来: End Sub 试卷中的题目总是显示各题库最前面的N题，而在此对各题库按随机数进行随机排序后最前面的 N题自然是随机的，也就相当于是随机抽题了。 7. 成绩记录表 新建一个“成绩记录”工作表(如图 4) ，在 A2输入公式 =NOW() ，并选中 A列右击从“设置单元 格格式”中设置为日期格式以显示当前时间。B2、 C2分别输入 =试卷!D2 和=试卷!F2 以显示相应数据。 D2输入公式 =SUM(E2:G2) 对后面三个得分进行合计，E2、F2、G2则分别输入公式 =SUM( 试卷!E5:E34) 、 =SUM( 试卷 !E36:E75) 、=SUM( 试卷!E77:E106) 分别对相应题型得分进行合计。 8. 评分和记录保存 右击“评分”按钮，选择“指定宏”，输入宏名“评分”，单击“新建”按钮，编辑如下代码: Sub 评分 () Sheets(“ 成绩记录 “).Rows(“3:3“).Insert Shift:=xlDown 在成绩记录工作表第3 行前插入一行 Sheets(“ 成绩记录 “).Rows(“2:2“).Copy 复制成绩记录工作表第2 行 Sheets(“ 成绩记录 “).Range(“A3“).PasteSpecial Paste:=xlPasteValues 以数值方式粘贴到成绩记录工作表A3 得分 = MsgBox(“ 最终得分 “ & Range(“成绩记录 !D2“).Value & “其中: 单选题 “ & Range(“ 成绩 记录!E2“).Value & “、多选题 “ & Range(“成绩记录 !F2“).Value &“、判断题 “ & Range(“ 成绩记 录!G2“).Value &, , “评分结果 “) 弹出信息框显示成绩记录!D2、E2、F2、G2的数据，标题为 “评分结果 “ Range(“D2,D5:D34,D36:D75,D77:D106“).ClearContents 清除试卷工作表中输入的内容 Rows(“3:106“).RowHeight = 0 设置题目区行高为0，以隐藏所有题目 ActiveSheet.Shapes(“出题按钮 “).Visible = True ActiveSheet.Shapes(“评分按钮 “).Visible = False 显示出题按钮 , 隐藏评分按钮 ActiveWorkbook.Save 保存 End Sub 9. 其他设置 选中 A4单击“窗口 / 冻结空格”以始终显示1:3 行的标题和按钮。右击C 、E列选择隐藏。为答 题区以外单元格设置填充色以示区别，把评分按钮重叠到出题按钮上( 如图 1) 。 选中 D5:D34右击选择“设置单元格格式”，在“保护”选项卡中单击取消“锁定”选项，对于同样 需要添写的 D2,D36:D75,D77:D106 区也同样取消锁定设置。然后单击“工具 / 保护/ 保护工作表”， 在 中间“允许此工作表所有用户进行”列表中只选中“选定未锁定单元格”和“设置行格式”两个选 项，输入密码 (如图 2)，单击确定把工作表保护起来，这样考生就无法修改试题等相应单元格了。单 击评分以隐藏所有题目，同时显示出题按钮，把文件保存为“模拟考试.xls ”，退出。 图 2 系统维护 1. 资料输入 直接在前面做好的单选题、多选题、判断题工作表中输入题目和答案即可，按Alt+ 回车键换行， 用空格进行水平定位。答案输入时注意不要有多余字符，特别是多选题一定要按ABCD 的顺序输入多 选项，否则无法正确评分，建议参照一4 步操作对 C列单元格设置有效性。若以后还想用到当前的题 目顺序可在最后面增加一列(E 列) 填充序列号，需要时按此列排序即可。在“考生记录”工作表中输 入姓名和准考证号，这可以直接从相关工作表中复制过来。 2. 隐藏工作表 把除试卷外的其他工作表隐藏起来以免考生看到。单击“工具/ 宏/Visual Basic编辑器”，在 弹出窗口左侧选中要隐藏的工作表名，如:Sheet4( 判断题 ) ，在属性窗口中把Visible项的值设置为 0-xlsheetHidden(如图 3)即可隐藏该工作表，重复这一步把除试卷外的其他工作表全部隐藏。以后 如需显示只要按这操作把Visible改回 -1-xlsheetVisible即可。若看不到属性窗口可按F4键显示。 图 3 为防止学生修改，还得右击VBAProject( 模拟考试 .xls)选择“ VBAProject 属性”，在“保护”选项 卡中选中“查看时锁定工程”，并输入密码(如图 4) ，确定保存文档退出。这样以后没有密码就别想 修改这些内容了。 图 4 如何考试 打开“模拟考试 .xls ”就只有一张“试卷”工作表，先输入准考证号回车确认，正确的话就显示 姓名否则会要求你重输。单击出题按钮即可显示题目，同时出题按钮会变成评分按钮。考生可在D列 选中相应单元格直接输入答案或单击单元格后的下拉按钮从列表中选择输入(如图 5)。题目做完后， 单击“评分”按钮即可显示最后得分，确定后自动保存，并回复到最初状态。 图 5 小提示 叩开 Excel 的安全门 按照上面方法制作的模拟考试系统在运行时有一个问题，Excel 2003的默认设置会把宏的安全 性设置为高，表现为运行时出现“安全性提示”对话框。解决方法是，打开 Excel 文档，依次单击“工 具宏安全性”，选择“中”并“确定”，关闭并重新打开Excel 文档后，选择“启用宏”即可。 系统修改 在电脑上能直接处理的题型就只有这三种，所以能改的也只有各种题的数量而已。这只要三步即 可: 1. 撤消工作表保护，在相应题目中选中N行，右击选择“复制”再选择“插入复制单元格”，如 果要减少则右击选择“删除”就OK 。 2. 选中 B:D 列右击选择“取消隐藏”，按一.3 中介绍的规则修改复制二、三大题各题目区域的 公式。