用EXCEL制作模拟考试系统教学提纲.doc

1.建立题库打开Excel 2003，建立“单选题”工作表(如图1)，在A2输入公式=ROW()-1&.以自动生成序号，在D2输入=IF(C2=,RAND()，选中A2:D2拖动其填充柄向下复制到其他单元格，大概有几百行也就够了，剩下的需要时再复制。按住Ctrl键拖动工作表标签复制出两份，分别重命名为多选题、判断题。图12.设计试卷建立“试卷”工作表，你可以按需要设置试卷，并输入单选题、多选题、判断题三个大题目，多选题需注明错、漏、多选均不得分。并留出相应的题目位置，单选题30题(A5:C34)，多选题40(A36:C75)，判断题30题(A77:C106)。用“自选图形/基本形状”中的棱台画一个按钮，选中在名称框中输入“出题按钮”4字回车，把它命名为“出题按钮”。再右击此按钮选择“添加文字”，在按钮上输入“出题”2字。同样再画一个按钮，把它命名为“评分按钮”，添加文字“评分”。(如图2)图23.显示题目在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:=xlGuessSheets(多选题).Range(A:D).Sort Key1:=Sheets(多选题).Range(D2), Order1:=xlAscending, Header:=xlGuessSheets(判断题).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 = TrueActiveSheet.Shapes(评分按钮).Visible = False显示出题按钮,隐藏评分按钮ActiveWorkbook.Save保存End Sub9.其他设置选中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中介绍的规则修改复制二、三大题各题目区域的公式。然后重新隐藏C列和保护工作表。分数记录中的公式会自动调整可不必修改。3.修改一.8评分的宏代码中最后Range(D2,D5:D34,D36:D75,D77:D106).ClearContentsRows(3:106).RowHeight = 0中的区域范围即可。本文中的两个宏代码其实最主要部分就只是排序、设置行高、插入行、复制、选择性粘贴等基本操作而已，如果你不喜欢编代码，那么大可使用录制宏直接录制相应操作，基本的出题和评分功能都同样可以实现，只是会少一些提示窗口和按钮限制而已。