用Excel制作考试系统（示例之二）

二、设计考试系统

1. 创建题库

① 启动Excel 2007 后，双击工作表标签“ sheet1 ” 并将其更名为“ 单选题”。

② 在“ 单选题” 工作表的A2 单元格中输入公式“ = ROW() - 1&"." ”，用以自动生成序号，在D2 单元格中输入公式“ = IF(C2="","",RAND()) ”。注意：在单元格中输入任何公式、函数时，所有的标点符号以及运算符号、斜线等均应在英文标点符号状态下输入，否则不被认可。③ 选中A2:D2 区域并拖动其填充柄竖直向下复制到其他单元格。至于复制多少行，可由自己确定，够用就行了，并且以后还可以再复制，如图1 所示。

图1

④ 按住Ctrl 键并用鼠标拖动“ 单选题” 工作表标签复制出两份，分别重命名为“ 多选题” 和“ 判断题”。

2. 答题卷制作

① 建立“ 试卷” 工作表的简单方法是将标签“ sheet2 ” 更名为“ 试卷” 就可以了。

② 按需要设置试题量，并输入单选题、多选题、判断题三个大标题，多选题需注明“ 错选、漏选、多选均不得分” 字样，并留出相应的试题位置。比如设计单选题30 个(所占单元格区域是A5:C34)，多选题40 个(所占单元格区域是A36:C75)，判断题30个(单元格区域是A77:C106)。

③ 在功能区打开“ 插入” 选项组，单击“ 形状” 选项，在下拉列表中选择“ 自选图形” → “ 基本形状” → “ 棱台”，移动鼠标到A1 单元格按住鼠标左键画出一个按钮。选中按钮后在名称框中输入“ 出题按钮” 四个字后回车，把它命名为“ 出题按钮”。

④ 右击刚才绘制的出题按钮并选择“ 添加文字” 快捷菜单命令，然后在按钮上输入“ 出题” 两个字。

⑤ 按照同样的方法再设计一个按钮“ 评分按钮”，添加文字“ 评分”，如图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(),0) ” 并复制到区域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 ”，如图3 所示。

注意：不输入引号，并且其中的逗号为英文标点状态的格式。

图3

② 切换到“ 出错警告” 选项卡，选中“ 输入无效数据时显示警告” 复选项，再在“ 错误信息” 编辑框中输入出错时的提示信息为“ 只能输入A 、B 、C 、D 中的一项或多项，其他任何字符均为非法字符。”，选择“ 样式” 为“ 停止”，单击“ 确定” 完成设置，如图4 所示。

图4

③ 同样对多选题的D36:D75 区域，判断题的D77:D106 区域设置数据有效性，只是多选题的“ 来源” 要换成“ A,B,C,D,AB,AC,AD,BC,BD,CD,ABC,ABD,ACD,BCD,ABCD ”，判断题的“ 来源” 要换成“ √,× ”。当然“ 出错警告” 下的“ 出错信息” 也要作相应修 改。这里要着重强调的是多选题的每一个答案，一定要说明必须按字母ABCD 的顺序输入多选项，否则无法正确评分，如“ ACD ” 不能输成“ CAD ” 或者“ CDA ” 或者“ DAC ” 或者“ DCA ”。标准答案也是一样的要求。如果感觉到多选题要输入这么一大串有效性设置太麻烦，那么不设置有效性的选项也可以，只是要在多选题的要求中注明“ 不按字母顺序输入答案的不给分” 就行了。

5. 验证考生准考证号

① 建一个“ 考生名单” 工作表。在A 、B 、C 列分别输入序号、准考证号、考生姓名，其中序号和准考证号的输入可采用序列填充的方法完成。② 选中B 列，在名称框中输入“ 考号”，回车确定，把B 列命名为准考证号，如图5 所示。

图5

③ 回到“ 试卷” 工作表 中，选中要输入准考证号的D2 单格，执行菜单命令“ 数据/ 有效性”，在“ 设置” 选项卡的“ 允许” 下拉列表中选择“ 序列”，取消“ 提供下拉箭头” 复选项，在“ 来源” 文本框中输入公式“ = INDIRECT(" 考号") ”。切换到“ 出错警告” 选项卡，选中“ 输入无效数据时显示警告” 复选框，并输入“ 错误信息” 为“ 你输入的准考证号并不存在，请重新输入”，并设置样式为“ 停止”。最后单击“ 确定” 按钮。

④在F3单元格中输入公式“=IF(D2="","",VLOOKUP(D2, 考生名单! B:C,2,FALSE))”，这个公式会在输入准考证号后自动显示考生姓名。

6. 实现随机抽题

① 右击“ 出题” 按钮，单击“ 指定宏” 快捷菜单命令(若指向按钮中间右击，则不能指定宏），在弹出的对话框中输入宏名为“ 出题”，如图6所示。

图6    指定宏

② 单击对话框右侧的“ 新建” 按钮，在弹出的代码编辑窗口中输入如图7 所示的代码。

图7  输入代码

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

注意：试卷中的题目总是显示各题库最前面的若干题，而在此对各题库按随机数排序后，最前面的若干题自然是随机的，也就实现了随机抽题。

7. 记录成绩

① 在功能区中打开“ 开始” 选项组，单击“ 插入” 选项，再选择列表中的“ 工作表” 选项，新建一个工作表，并将其标签名修改为“ 成绩记录”。

② 在“ 成绩记录” 表的A2 单元格中输入公式“ = NOW() ”。

③ 选中A 列并右击，单击快捷菜单的“ 设置单元格格式” 命令，然后在弹出的对话框中设置日期格式以显示当前日期时间，如图8 所示。

图8   设置单元格格式

④ 在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 单元格，在功能区中打开“ 视图” 选项组，单击“ 冻结空格” 下拉按钮并选择“ 冻结拆分空格” 选项，以始终显示前3 行的标题和按钮。

② 分别右击C 、E 列的列标签，并选择快捷菜单中的“ 隐藏” 命令。③ 将答题区以外的单元格区域设置成某种填充色以示区别，将“ 评分” 按钮重叠到“ 出题” 按钮上。

④ 选中D5:D34 的单元格区域，右击后选择快捷菜单命令“ 设置单元格格式”，在“ 保护” 选项卡中取消“ 锁定” 复选框的选中状态，对于同样需要填写的D2 ：D34 、D36 ：D75 、D77 ：D106 区域也同样取消“ 锁定” 设置。

⑤ 在功能区中打开“ 审阅” 选项组，选择“ 保护工作表” 选项，弹出该对话框。在其中“ 允许此工作表所有用户进行” 列表中只选中“ 选定未锁定单元格” 和“ 设置行格式” 两个复选框，再输入密码，如图9 所示，单击“ 确定” 按钮退出，这样就把工作表保护起来了，考生就无法修改试题等相应单元格。单击“ 评分” 按钮以隐藏所有题目，同时显示“ 出题” 按钮。

  图9    保护工作表

⑥ 保存文件并退出，至此，一个比较实用的考试系统就设计完成了