教师备考宝典(21)：试卷篇——标准题库也能DIY
软件世界
微机室现在成了教师朋友们组织考试的好地方。在第19期我们一起用Word制作了一份电子试卷，不过在使用中，我们会发现它的两点不足：一是每次考试都要重新出题，二是批改试卷的过程非常繁琐。第20期给大家介绍了一款可以录入题库、自动生成试卷的软件，可以减轻教师出题的工作量，不过批改试卷的工作仍然繁重。下面小编给大家介绍一种用Excel制作标准题库的方法，它可以自动生成随机试卷，批改时也只需点一下按钮，非常方便。
一、题库需求分析
1.本题库主要适用于已连成局域网的电脑教室，在同一个试题库的支持下，每台学生机都可以随机地从题库中抽取试题，以保证每次生成的试卷都不完全相同。
2.学生考完试后，只须简单操作就可以马上知道自己的标准题总得分、每题的得分情况及参考答案，教师则能自动扫描出参考学生的姓名及总分。
3.该系统主要由三个文件组成，其中tk.xls是题库，st.xls是试题文件，tj.xls是统计文件，这三个文件都存放在同一目录下。要针对不同的年级和科目，只须更换题库tk.xls。
4.本题库内容以标准题为主，可以包含主观题，但是不能自动评分。
二、创建题库
1.准备工作
新建一个名为tk.xls的Excel文件，把工作表名分别改为Dan、Duo和Pan，分别用于存放单选题、多选题和判断题。依次单击“视图→工具栏→窗体”，打开“窗体工具栏”。
2.创建单选题库
①规划单元格
进入工作表Dan，按照图1的格式规划单元格，调整行高、行宽，B6单元格设为靠右、靠上对齐，C6单元格设为靠左、靠上对齐。
②插入窗体域
在B7单元格中插入一个“选项”按钮，右击之，选择“编辑文字”，将它的默认名“选择按钮X”改为A。在B8～B10单元格中插入“选项”按钮，分别改名为B、C、D。再插入分组框将A、B、C、D“选项”按钮框住，去掉默认名。
提示：建议将每个单选框的宽、高设为等于或小于所在的单元格，分组框的宽、高设为与单元区域B7:B10相同。分组框必须将四个“选项”按钮完全框住，否则会出现两个或两个以上的选项可以被同时选中的情况。
③建立链接
右击任一选项按钮，选择“设置控件格式→控制”，在“单元格链接”后的文本框内输入E6。然后在F6单元格中输入函数“=CHOOSE(E6,"A","B","C","D")”，则在单击选项按钮时F6单元格中会自动填入A、B、C、D，能更加直观地反映输入情况。
提示：输入函数时，最好用函数面板，若要手动输入，请将输入法转换到英文状态。另外，表面上看，可以用IF函数来替换CHOOSE函数，但在Excel 97中将单元格锁定并保护起来后，用IF函数会报“无法写入单元格”的错误，而在Excel 2002/2003中，虽然不会报错，但是用IF函数时可能要用到多层嵌套，在考试时也可能出现莫名其妙的错误。
按图2所示输入其他内容，单击选项按钮A，输入该题的正确答案A。
④同理，设置其余各题，本例中共57道。
3.创建多选题库
①进入工作表Duo，单元格的规划与单项选择题相同，如图2所示。
②在B7单元格中插入复选框并改名为A，再将它链接到F7单元格，单击选中这个复选框时，F7中会自动填入“TRUE”，再次单击以取消对复选框的选中时，F7单元格中会自动填入“FALSE”。
同理，在B8～B10单元格中插入复选框B、C、D，再分别链接到F8～F10单元格。输入本题的正确答案，对应的F7～F10单元格会自动填入TRUE。
同理设置其余多选题，共19道。
4.创建判断题库
进入工作表Pan，将B6单元格设为靠右、靠下对齐，输入题号1。在B6中插入一个复选框，去掉其默认名称“复选框X”，再将它链接到F6。同理，对其他18道题进行设置(图3)。
三、创建考试题
1.试卷外形设计
保持刚才设置好的tk.xls文件处于打开状态，然后在同一个文件夹内新建一个工作簿，命名为st.xls，并将其中一个工作表改名为kaoti(图4)。把该工作表复制一份并更名为chakan，以备后用。
在B6单元格中输入公式“=CHOOSE(1,INT(RAND()*10+1))”，右击之，从快捷菜单中选择“复制”，再右击B7单元格，选择“选择性粘贴→数值”，把B6中产生的一个1～10之间的随机正整数复制到B7。
在D5单元格中输入函数“=CHOOSE(1,SUM(H10:H221))”，以统计总分。
提示：在设置st.xls时如果不打开tk.xls，则输入公式或函数时要用带有路径的引用形式。在考试时tk.xls不须处于打开状态。
2.设置单选题
为了增加试题的随机性，笔者采用了分段扫描的方法。先设置第1～10题，再设置第11～20题，依此类推。
①在C10单元格中输入公式“=CHOOSE($B$7,[tk.xls]dan！C6, [tk.xls]dan!C11,[tk.xls]dan!C16,[tk.xls]dan!C21,[tk.xls]dan!C26,[tk.xls]dan!C31,[tk.xls]dan!C36,[tk.xls]dan!C41,[tk.xls]dan!C46,[tk.xls]dan!C51)”，这个公式的作用是：根据B7中的值来确定从题库文件tk.xls的dan工作表下抽取哪一个单元格的内容填入C10单元格。比如，B7内的值等于3时，就把[tk.xls]dan!C16中的内容抽取过来，[tk.xls]dan!C16中的内容是题库文件单项选择题的第3题的题干。依此类推。
提示：公式中$B$7是绝对引用，不能用相对引用或混合引用。
②用填充柄将C10中的内容复制到C11、C12、C13、C14、F10和G10单元格中，抽取与题干对应选项群(C11～C14)、参考答案(F10)、每题给分(G10)等信息。
③在B11～B14单元格中插入选项按钮A、B、C、D，再插入一分组框将它们框住。再按照创建单选题库的方法，将选项按钮链接到E10。在D10单元格中输入公式“=CHOOSE(1,IF(E10=1,"A",IF(E10=2,"B",IF(E10=3,"C",IF(E10=4,"D","没做")))))”。作用是把E10中的1、2、3、4等转化为更直观的A、B、C、D，写入D10单元格并判断学生是否做了此题。
④在H10单元格中输入公式“=CHOOSE(1,IF(D10=F10,G10,0))”用于判断D10中学生的输入是否与F10中的参考答案相同，如相同则把G10中的每题给分值填入H10，否则填入0分。用同样的方法设置其余9题
⑤设置第15、20……55行行高与第10行行高相同，其余行高与第11行行高相同。选中B10：H14区域，把鼠标移到该区域右下角的填充柄上，按住鼠标左键向下拖动至第59行为止。
至此，系统已能把试题库中单选题的第1～19题随机抽取出来，填入第10～59行作为考试题的第1～10题。如B7的值为4，则把第4～13题抽出来，如B7的值为8，则把第8～17题抽出来……
⑥同理，把试题库中单选题的第20～38题随机抽取出来，填入第60～109行作为考试题的第11～20题。用同样方法可以设置考试题的第21～30题。
3.设置多选题
①在C161单元格中输入公式“=CHOOSE($B$7,[tk.xls]duo！C6,[tk.xls]duo!C11,[tk.xls]duo!C16,[tk.xls]duo!C21,[tk.xls]duo!C26,[tk.xls]duo!C31,[tk.xls]duo!C36,[tk.xls]duo!C41,[tk.xls]duo!C46, [tk.xls]duo!C51)”，与设置单选题第1题类似，复制此单元格到F162～F165、G161单元格。
②在B162～B165中插入复选框A、B、C、D并分别使其链接到E162～E165单元格。在D161中输入公式“=CHOOSE(1,IF(AND(E162=0,E163=0,E164=0,E165=0),"没做",0))”。在D162中输入公式“=CHOOSE(1,E162)”并通过自动填充复制到D163～D165单元格。
③在H161中输入公式“=CHOOSE(1,IF(AND(E162=F162,E163=F163,E164=F164,E165=F165),G161,IF(AND(NOT(AND(E162=F162,E163=F163,E164=F164,E165=F165)),OR(E162=TRUE,E163=TRUE,E164=TRUE,E165=TRUE),NOT(OR((E162-F162)>0,(E163-F163)>0,(E164-F164)>0,(E165-F165)>0))),1,0)))”，这个函数的作用是：判断学生答案对错情况。如全对则把G161中的每题给分值写入H161单元格；如少选但无错选，则给1分；有错选或没做则给0分。
其余题目的设置仿照上面的步骤。
4.设置判断题
在B212中插入一个复选框，去掉默认名称，将它链接到E212。在D212中输入公式“=CHOOSE(1,IF(E212=TRUE,"TRUE",IF(E212=FALSE,"FALSE","没做")))”。这步的作用是判断学生是否做过第1道判断题。
在C212中输入公式“=CHOOSE($B$7,[tk.xls]pan!C6,[tk.xls]pan!C7,[tk.xls]pan!C8,[tk.xls]pan!C9,[tk.xls]pan!C10,[tk.xls]pan!C11,[tk.xls]pan!C12,[tk.xls]pan!C13,[tk.xls]pan!C14,[tk.xls]pan!C15)”。将C212中的内容复制到F212、G212中。这步的作用是从题库中抽取试题。
在H212中输入函数“=CHOOSE(1,IF(E212=F212,G212,0))”。这步的作用是判断学生的输入是否与参考答案相同，如相同，则将G212中的每题给分值填入H212中，否则填入0分。
同理，设置其余9题。
5.查看成绩、参考答案的设置
打开前面复制出来的工作表chakan，用CHOOSE函数把工作表kaoti中的内容对应地映射过来。例如，在C10单元格中就应输入“=CHOOSE(1,kaoti!C10)”。
6.录制相关的宏
录制宏时一定要计划好，有些什么操作，不能有任何多余的动作──哪怕是单击一下单元格。因为任何操作都会被“忠实”地记录到宏代码中。
①录制与开始考试相关的宏。在工作簿st.xls的kaoti工作表下，单击“工具→宏→录制新宏”，在“宏名”下输入“auto_open”(以此为名的宏能在打开工作表时自动运行)，确定后就可开始录制了。该宏包括以下操作：
选中D3，按Delete键清除其中的内容；清除E11～E221中的内容；在E212～E221中输入0；右击B6，选“复制”，再右击B7，选择“选择性粘贴→数值”；从“工具”菜单栏下的“视图”选项卡中清除“工作表标签”前的钩。
以上操作完成后，单击“工具→宏→停止录制”。这个宏的作用是：清除前一考生输入的所有内容。为B7单元格赋一个值，以便随机抽取试题。隐藏工作表标签，以防学生打开st.xls下的工作表chakan。
②在工作表kaoti中录制名为“结束考试”的宏。该宏包括以下操作：
单击工作表标签chakan，转到chakan工作表；将文件存盘。
提示：由于上一步中已隐藏了工作表标签，做这步前应先将它显示出来。
这个宏的作用是将记录了学生输入信息及参考答案的工作表chakan设为活动，让学生看到相应的信息。由于工作表标签处于隐藏状态，学生是无法用常规方法打开工作表kaoti再进行答题的。
用窗体工具栏在工作表kaoti中插入一个“按钮”，将其更名为“结束考试&查看成绩”，把刚录制的宏指定给它。
③创建一个名为auto_close的宏，以auto_close为名的宏可在工作表关闭时自动运行。单击“工具→宏”，在宏名下输入Auto_close，再单击“创建”，即可打开宏代码编辑框，输入以下代码：
Sub auto_close()
Sheets("chakan").Visible = True
Sheets("kaoti").Visible = True
ActiveWorkbook.Save
End Sub
这个宏的作用是：关闭Excel时，自动将工作表kaoti和chakan显示出来，并自动存盘。
7.工作表的初始化设置
①锁定工作表
恢复工作表的标签显示(因刚录制宏时已关闭)，选择kaoti工作表，右击B7，选择“设置单元格格式”，在“保护”选项卡下取消“锁定”前的钩，解除对B7的锁定。同理，解除对E10、E15……E155；E162～E165、E167～E170……E207～E210；E212～E221等单元格的锁定。隐藏第5、6、7列，E、F、G、H行。
这样做的目的是：对工作表进行保护后，学生就只能在没有锁定的单元格中输入内容，不能对其他单元格进行任何更改。因为工作表打开时要自动运行auto_open宏，这个宏中包含了向B7单元格复制内容的操作，也必须解除对B7的锁定，不然，它的内容得不到更新，也就达不到随机抽题的目的。
②保护工作表
选中I9单元格，单击“窗口→冻结窗口”。这样做的目的是：不管学生怎样拖动滚动条，该工作表I9左上角的单元格区域处于恒显示状态，保证了图5中的考题名称、“结束考试&查看成绩”等信息始终能显示出来。
单击“工具→保护→保护工作表”，按提示输入密码后将该工作表保护起来。
提示：在默认状态下，工作表中的单元格都处于锁定状态。
转到chakan工作表，只隐藏E列，5、6、7行。选中I9单元格后，冻结窗口，加密码保护工作表。由于这个工作表是供学生查看成绩及参考答案用的，不能隐藏F、G、H等行。也不能解除对任何单元格的锁定，也就是说，学生在单击“结束考试&查看成绩”按钮转到这个工作表后，只能看，不能做任何修改。
提示：对另一个Excel文件tk.xls也可进行类似的设置，但考虑到tk.xls是给教师看的，本例中没有这样设置。
四、实战
1.基本设置
现以在笔者学校机房的设置为例进行说明，其他学校机房可参照执行。
机房有服务器一台(Windows NT)，教师机一台(Windows 98，能登录服务器)，无盘工作站6列5排共30台,编号为11……15，21……25……61……65。服务器上的文件夹pwin95s、rpl95、cai分别被映射为工作站的F、G、H盘，从工作站上能向后两文件夹中存取文件。把st.xls、tk.xls拷贝到服务器的cai目录(即工作站的H盘)下的任一新建目录下，将st.xls复制30份，将复制的文件分别命名为st11.xls、st12.xls……st15.xls、st21.xls……st61.xls……st65.xls，分别为每一个试题文件在对应的工作站的桌面上创建快捷方式，以便学生能双击打开试题文件。
2.扫描学生的考分及姓名
在同一个文件夹下新建一个Excel文件tj.xls来扫描工作站上考生的姓名和总分，这与上面的从tk.xls 中提取试题内容到st.xls相似。比如要把打开st11.xls做题的考生的姓名、总分扫描出来记入tj.xls的B2、C2单元格中，则在B2单元格中输入公式“CHOOSE(1,[st11.xls]kaoti!$D$3)”，在C2单元格中输入“=CHOOSE(1,[st11.xls]kaoti!$D$5)”即可。
3.使用
学生在工作站上双击试题文件的快捷方式，就可以打开试题文件，做完题后，单击“结束考试&查看成绩”按钮即可查看得分及参考答案，系统会在宏的支持下自动存盘。教师打开tj.xls就可扫描到已交卷学生的姓名及总分。