用EXCEL制作模拟考试系统

1 建立题库 打开 Excel 2010 建立 模拟考试系统 出题测试版 打开此工作簿 建立 单选题 工作 表 如图 1 在 A2 输入公式 ROW 1 以自动生成序号 在 D2 输入 IF C2 RAND 选 中 A2 D2 拖动向下填充几百行 按住 Ctrl 键单击 单选题 工作表标签拖动复制出两份 分别重 命名为 多选题 如图 2 判断题 如图 3 分别输入题目和答案 按 Alt 回车键换行 请注意以下提示 1 以上 3 个工作表 在 B 列输入单选题题目 样式随意 但一道题只能占一个单元格 在 C 列输入对应考题的答案 答案输入时注意不要有多余字符 2 多选题 工作表的答案必须按 ABCD 的顺序填写 例如答案 ABD 不能写 ADB BAD BDA DAB DBA 答错 少答和多答均不得分 判断题 工作表的答案只能有两种 本 题按 正确 和 错误 作为答案 其他形式如 对 和 错 和 也可以 3 题库中题目数量根据实际情况而定 多多益善 本例中题库数目为单选题 60 道 多选题 50 道 判断题 45 道 图 1 图 2 图 3 2 建立考生名单 新建 考生名单 工作表 在 A B C 列分别输入序号 准考证号 考生姓名 选中 B 列在名 称框中输入 考号 回车确定把 B 列命名为考号 如图 4 完成后可在名称管理器内看到名字为 考号 的名称 引用位置为 考生名单 工作表的 B 列 图 4 3 设计试卷 建立 试卷 工作表 按需要设置试卷 序号生成方式如题库序号 单选题 30 题 A5 C34 多 选题 40 A36 C75 判断题 30 题 A77 C106 在 B4 输入 单选题 每题 1 分 在 B35 输入 多选题 每题 1 分 错 漏 多选均不得分 在 B76 输入 判断题 每题 1 分 用 自选图形 基本形状 中的棱台画一个按钮 选中在名称框中输入 出题按钮 4 字回车 把它命名为 出题按钮 再右击此按钮选择 添加文字 在按钮上输入 出题 2 字 同样再 画一个按钮 把它命名为 评分按钮 添加文字 评分 如图 5 图 5 4 显示题目 如图 6 在 B5 输入公式 INDIRECT 单选题 R ROW 3 C COLUMN 0 复制到 B5 C34 可拖动 A5 填充 在 B36 输入公式 INDIRECT 多选题 R ROW 34 C COLUMN 0 并复制到 B36 C75 在 B77 输入公式 INDIRECT 判断题 R ROW 75 C COLUMN 0 并复制到 B77 C106 在 E5 单元格输入公式 IF D5 0 IF D5 C5 G 2 0 向下填充到 E34 这公式用来判断如 果 D5 中的答案与 C4 的标准答案一致就得 1 分否则为 0 分 其中 G2 单元格内数值为 1 同理 在 E36 单元格输入公式 IF D36 0 IF D36 C36 H 2 0 向下填充到 E75 在 E77 单元格输入公式 IF D77 0 IF D77 C77 I 2 0 向下填充到 E106 可通过更改 G2 H2 I2 单元格的数值来 更改分值 B5 的公式表示对单选题工作表中同列 行数差 3 的单元格 B2 的引用 公式中使用了 R1C1 格 式的单元格编号 其中 3 34 75 是由三种题型的第一题所在的行号减 2 得来 这公式实现的是绝 对位置引用 即不管在单选题工作表中进行插入 删除行或排序 B5 显示的始终都是你从单选题 B2 中直接看到的内容 因此 B5 C34 B36 C75 B77 C106 三个区域将分别与相应题库中最前面的 30 或 40 题的内容保持一致 图 6 5 答案限制 选中单选题答题区 D5 D34 单击 数据 有效性 快捷键 ALT D L 在 允许 中选择 序 列 选中 提供下拉箭头 复选项 在 来源 中输入 A B C D 不含引号且其中逗号为半角 格式 如图 7 切换到 出错警告 选项卡 输入错误信息为 只能输入 A B C D 中的一项 其 他任何符号或空格均为非法 选中 输入无效数据时显示警告 复选项 选择样式为 停止 如图 8 确定完成设置 图 7 图 8 同样对多选题答题区 D36 D75 判断题答题区 D77 D106 设置数据有效性 只是 来源 要分别 换成 A B C D B AC AD BC BD CD ABC ACD BCD ABCD 和 正确 错误 多选题的 出错警告 一定要改成 必须按 ABCD 的顺序输入多选项 否则无法正确评分 如图 9 到如图 11 图 9 图 10 图 11 6 准考证号验证 选中 试卷 工作表中要输入准考证号的 D2 单元格 单击 数据 有效性 在 允许 中选 择 系列 取消 提供下拉箭头 复选项 在 来源 中输入 INDIRECT 考号 如图 12 切换到 出错警告 选项卡 输入错误信息为 你输入的准考证号并不存在 请重新输入 选中 输入无效数据时显示警告 并设置样式为 停止 如图 13 图 12 图 13 在 F2 单元格输入公式 IF D2 VLOOKUP D2 考生名单 B C 2 FALSE 在 D2 单元格输 入准考证号后会自动显示考生姓名 如图 14 图 14 7 随机抽题 右击 出题 按钮 选择 指定宏 在弹出窗口中 输入宏名为 出题 位置选择当前工 作簿 单击 新建 按钮 复制如下代码粘贴到框内 结果如图 15 保存时将文件另存为启用宏的 xlsm 格式 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 图 15 试卷中的题目总是显示各题库最前面的 N 题 而在此对各题库按随机数进行随机排序后最前面 的 N 题自然是随机的 也就相当于是随机抽题了 8 成绩记录表 新建 成绩记录 工作表 如图 16 在 A2 输入公式 NOW 并选中 A 列右击从 设置单元格 格式 中设置为日期格式以显示当前时间 在 B2 输入公式 试卷 D2 和 C2 输入公式 试卷 F2 以显示 相应数据 D2 输入公式 SUM E2 G2 对后面三个得分进行合计 E2 F2 G2 则分别输入公式 SUM 试 卷 E5 E34 SUM 试卷 E36 E75 SUM 试卷 E77 E106 分别对相应题型得分进行合计 图 16 9 评分和记录保存 右击 评分 按钮 选择 指定宏 输入宏名 评分 位置选择当前工作簿 单击 新建 按钮 复制如下代码粘贴到框内 结果如图 17 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 清除试卷工作表中输入的内容 Sheets 试卷 Rows 3 500 RowHeight 0 Sheets 成绩记录 Rows 2 2 RowHeight 0 设置题目区行高为 0 以隐藏所有题目 ActiveSheet Shapes 出题按钮 Visible True ActiveSheet Shapes 评分按钮 Visible False 显示出题按钮 隐藏评分按钮 ActiveWorkbook Save 保存 End Sub 图 17 10 其他设置 选中 A4 单击 窗口 冻结空格 以始终显示 1 3 行的标题和按钮 右击 C E 列选择隐藏 为答 题区以外单元格设置填充色以示区别 把评分按钮重叠到出题按钮上 如图 18 图 18 选中 D5 D34 右击选择 设置单元格格式 在 保护 选项卡中单击取消 锁定 选项 对于 同样需要添写的 D2 D36 D75 D77 D106 区也同样取消锁定设置 如图 19 然后单击 工具 保护 保护工作表 在中间 允许此工作表所有用户进行 列表中只选中 选定未锁定单元格 和 设 置行格式 两个选项 输入密码 如图 20 单击确定把工作表保护起来 这样考生就无法修改试题 等相应单元格了 单击评分以隐藏所有题目 同时显示出题按钮 把文件另存为 模拟考试系统 考 生测试版 xlsm 退出 如图 19 如图 20 系统维护 1 隐藏工作表 选中除 试卷 工作表除外的其他工作表 右击选择隐藏 以免考生看到 如图 21 图 21 2 保护 VBA 代码 右击 试卷 工作表 选择 查看代码 选中 VBAProject 模拟考试系统 考生测试版 xlsm 右击 选择 VBAProject 属性 在 保护 选项卡中选中 查看时锁定工程 并输入密码 如 图 22 确定 保存文档后退出 关闭文档后再重新打开 查看代码需输入密码 这样任何人没有 密码就别想修改这些代码了 图 22 3 保护工作簿 为防止考生修改 在 审阅 选项卡中选中 保护工作簿 勾选 结构 和 窗口 选项 输入密码后确定 如图 23 完成以上三步后 在没有密码的前提下 考生只能输入准考证号和进行答题 不能取消隐藏工 作表 取消隐藏某列或某行 查看和修改 VBA 代码 图 23 如何考试 打开 模拟考试系统 考生测试版 xlsm 就只有一张 试卷 工作表 先输入准考证号回车确 认 正确的话就显示姓名 否则会要求你重输 单击出题按钮即可显示题目 同时出题按钮会变成 评分按钮 考生可在 D 列选中相应单元格直接输入答案或单击下拉按钮从列表中选择输入 如图 24 到图 26 题目做完后 单击 评分 按钮即可显示最后得分 确定后自动保存 并回复到最初状 态 图 24 图 25 图 26 系统修改 在电脑上能直接处理的题型就只有这三种 所以能改的也只有各种题的数量而已 步骤如下 1 撤消工作表保护 在相应题目中选中 N 行 右击选择 复制 再选择 插入复制单元格 如果要减少则右击选择 删除 就 OK 2 选中 试卷 工作表 B D 列 右击选择 取消隐藏 按之前讲的公式修改复制各题型区域 的公式 3 然后重新隐藏 C 列和保护工作表 分数记录中的公式会自动调整可不必修改