Excel+Word打造随机出卷系统.doc

Excel+Word打造随机出卷系统自动出卷的软件其实并不少见，不过得花钱去买。如果能按个人的使用习惯和需要，自己定制一套自动出卷系统，除了可省钱外应该也挺有趣的吧。在此介绍一种用Excel 2003和Word 2003自制随机出卷系统的方法，以供参考。基本原理1. 随机排序。在题号后增加一列，并用RAND()填写随机数，以随机数为关键字进行排序，就可以实现随机排序了。此外，每次排序随机数都会改变，从而得到不同的随机顺序。2. 随机抽题。对题号进行随机排序，则前面n题就等于是随机抽取的了，把这n题复制出来即可实现随机抽题。3. 限制范围。题号用公式判断，当不属于指定的单元范围时题号为空，相应的随机数也为空。升序排序后随机数为空的行不会在前面的n题中了，自然不参与出题。4. AB卷生成。对已选出的题目进行升序随机排序即可得到不同顺序的B卷。填空、选择、问答等题型的随机数应依次增加1、2、3，这样排序时不同题型就不会串位了。用Excel 2003设计系统1主界面设置建立“出卷”工作表（如图1所示），在F1输入=NOW()，并设置为日期格式，在B1输入“=SUM(B2:B4)”。选中F列右击选择“设置单元格格式”在“对齐”选项卡中勾选“自动换行”。顺便为A:C区域设置填充色以突显需填写的B2:B4和B7:B8。2建立题库建立“填空”工作表（如图2所示）。在A2输入“=IF(AND(D8=出卷!$B$7),ROW()-1,)”，当所属单元在出卷工作表B8、B7之间时显示题号为行数减1否则为空。选中A2:A1000按Ctrl+D键进行填充。然后在F2输入“=填空&ROW()-1，G2输入=填空!A2，H2输入=IF(G2=,RAND()+1)”，再选中F2:H1000，按Ctrl+D键向下填充。选中A:H在名称框输入“填空”回车，把A:H命名为“填空”。按住Ctrl键拖动“填空”工作表标签复制出另一工作表，重命名为“选择”工作表，选中A:H命名为“选择”。把F2、G2公式中的“填空”改成“选择”，H2改成“=IF(G2=,RAND()+2)”，然后选中F2:H2双击填充柄进行填充，即可制作出“选择”工作表。同样做出“问答”工作表，注意：H2的随机数要再加1变成“=IF(G2=,RAND()+3)”。对整列排序较费时，在此按题数为1000计以节省排序时间，若想要5000题则本文中全部的1000都换成5000即可。G2的公式“=填空!A2”中工作表名不能省略否则无法排序。H2公式中的随机数RAND()+1、2、3的差距可适当调大（如：+5、+10、+15）以便将来在中间插入新题型。技巧：在“名称框”中输入：“A2:A1000”回车即可快速选中这一区域。3 随机抽题返回“出卷”工作表，用“自选图形/基本形状/棱台”画一个按钮，选中此按钮输入“出卷”二字。右击“出卷”按钮选择“设置自选图形格式”，在“属性”中选中“大小位置均固定”选项。再次右击选择“指定宏”，然后单击“新建”按钮，在Visual Basic右侧窗口编辑如下代码：Sub 自选图形1_单击()Range(D4:E128).ClearContents清除D4:E128中数据Range(D4).Select选中D4X = Range(B2).Value指定X=B2的数值Sheets(填空).Select切换到填空工作表Range(F2:H1000).Sort Key1:=Range(H2), Order1:=xlAscending对F2:H1000区域按H列随机数升序排序Range(F2:G & X + 1).Copy复制排序后前X条的题型、题号Sheets(出卷).Select返回出卷工作表ActiveCell.PasteSpecial Paste:=xlPasteValues以数值方式粘贴ActiveCell.Offset(X, 0).Range(A1).Select选中当前单元格下移X行的单元格X = Range(B3).ValueSheets(选择).SelectRange(F2:H1000).Sort Key1:=Range(H2), Order1:=xlAscendingRange(F2:G & X + 1).CopySheets(出卷).SelectActiveCell.PasteSpecial Paste:=xlPasteValuesActiveCell.Offset(X, 0).Range(A1).SelectX = Range(B4).ValueSheets(问答).SelectRange(F2:H1000).Sort Key1:=Range(H2), Order1:=xlAscendingRange(F2:G & X + 1).CopySheets(出卷).SelectActiveCell.PasteSpecial Paste:=xlPasteValuesActiveCell.Offset(X, 0).Range(A1).SelectRange(F4:F128).Rows.AutoFit设置F4:F128为最适合行高，以便显示全部题目Range(F2).SelectEnd Sub中间的三段代码几乎完全一样，是对填空、选择、问答题号的排序复制。只要编辑第一段代码，选中复制成三份后把代码中的“填空”换相应的选择、问答，再把首行“X = Range(B2).Value”中的B2改为相应的单元格编号即可。至此，我们已经实现了题号的自动抽取，下期我们将介绍如何按抽出的题号显示题目保存、AB卷制作及试卷与标准答案的自动生成。图1 建立“出卷”工作表 图2 建立“填空”工作表 在“ExcelWord打造随机出卷系统（上）”中我们介绍了随机出卷系统的原理和自动抽题的实现，这一期我们来完成后期的试卷保存、AB卷和自动生成试卷格式的设置。显示题目在“出卷”工作表的F4输入“=IF(E4=,MID(D4,3,3)&.&VLOOKUP(E4,INDIRECT(LEFT(D4,2),2,FALSE)”、G4输入“=IF(E4=,MID(D4,3,3)&.&VLOOKUP(E4,INDIRECT(LEFT(D4,2),3,FALSE)”、H4输入“=IF(E4=,VLOOKUP(E4,INDIRECT(LEFT(D4,2),4,FALSE)”，在I4输入“=IF(E4=,VLOOKUP(E4,INDIRECT(LEFT(D4,2),8,FALSE)”，然后选中F4:I4拖动填充柄向下填充至第128行。现在单击【出卷】按钮，随机抽取的试题和答案就会显示出来（如图1）。图1注：INDIRECTLEFTD42表示以D4单元格中前两个字所命名的区域，对应各题库工作表中命名的AH区域。MIDD433表示在前面加上D4单元格中第3位起3位内的数字和“”作为题号，显示为“1”、“2”、“3”试卷保存建立“记录”工作表（如图2），在D1输入“题目1”用右键拖动填充柄到DX1单元格，在弹出菜单中选择“以序列方式”，在DY1输入“答案1”同样以序列方式填充到IS1单元格。在A2、B2、C2分别输入“=出卷!F1”、“=出卷!F2”、“=出卷!B1”以显示相应数据，并选中A列设置为日期格式。图2在D4单元格输入公式“=出卷!$F4”，向下拖动填充柄填充至D128，选中D4:D128复制，再选中D2单击“编辑选择性粘贴”，选中“转置”选项，确定后D2:DX2就会对应显示出卷工作表F4:F128单元格。同样在D4输入“=出卷!G4”向下填充到128单元格后，以转置方式选择粘贴到DY2单元格，使DY2:IS2对应出卷工作表G4:G128单元格。然后删除D4:D128中的公式。现在出卷工作表中选出的题目、答案和数据都将即时显示到“记录”工作表的第2行。建议把第2行隐藏起来以防止误操作，也比较美观。返回“出卷”工作表中再用棱台画一个按钮，添加文字“保存”，设置“大小位置均固定”。再右击按钮选择“指定宏”，单击“新建”，编辑如下代码：Sub 自选图形2_单击() Sheets(记录).Rows(3:3).Insert Shift:=xlDown 在记录工作表第3行前插入一行 Sheets(记录).Rows(2:2).Copy 复制记录工作表第2行 Sheets(记录).Range(A3).PasteSpecial Paste:=xlPasteValues 以数值方式在记录工作表A3粘贴 End Sub在此我们是把整张试卷的题目保存在同一行中，Excel表格中最多只有256列，其中有将近一半要用来保存答案，所以总题数无法超过125题。若把答案和题目分别保存在不同行（或工作表）中则题数可增至250，需编辑的宏也差不多，只是在Word中调用时会比较麻烦，也不方便管理，大家可根据自己的实际需要决定。AB卷生成在“出卷”工作表中再画一个按钮，添加文字为“AB卷”，设置“大小位置均固定”。右击按钮选择“指定宏”，单击“新建”，编辑代码如下：Sub 自选图形3_单击() Range(E4:I128).Sort Key1:=Range(I4), Order1:=xlAscendingEnd Sub这串代码只是简单地对E4:I128区域升序排列而已，由于E4:I128区域的随机数随时都在改变，所以每次排序都会形成不同的随机顺序，多排序几次就可以生成B卷、C卷等等。完成以上几步设置后把文件保存为“出卷系统.xls”备用。建议在保存前对一些不需要进行操作修改的区域设置锁定并启用工作表保护以防误操作。用Word定制试卷1.打开Word 2003，右击工具栏选中“邮件合并”，单击“邮件合并”工具栏的“打开数据源”按钮，选择打开“出卷系统.xls”。在“选择表格”窗口中勾选“数据首行包含列标题”选项，双击选择“记录$”。2.单击“邮件合并”工具栏的“插入域”按钮，逐一双击插入列表中的试卷名称、总题数和题目1125的域，然后在每个域后回车使其独占一行，并输入填空题、选择题、问答题等大题目（如图3）。至于密封线、版面设计等已有不少文章介绍过，就不再多说了。图3技巧：当光标在域前时按Delete键再按键可把光标直接移到这个域后面，然后回车换行。这方法要比用鼠标定位快得多。3.按Ctrl+回车键另起一页，同样插入答案1125的域，设置成标准答案卷（如图4）。图44.单击“邮件合并”工具栏的“查看合并数据”按钮，没数据的域和后面的回车会自动消失。最后把文件保存为“试卷及答案.doc”，在此我们是按例子中5、6、5的题数进行设计说明的。若题数改变只要拖动调整试卷中各大题目的位置即可。此外为了方便调用，不妨在“出卷系统.xls”的出卷工作表中添加一个指向“试卷及答案.doc”的文件链接以便直接打开。到此，一套完整的自动出卷系统已经完成了。下一期我们将介绍这套系统的题库维护、出卷、查询等使用技巧和按个人需要改善系统的方法。前面两期我们已经完成了随机出卷系统的设计，这期我们来看看该系统的使用方法与维护技巧。题库维护打开“出卷系统.xls”，在填空、选择、问答等工作表中直接输入题目、标准答案和所属单元或者直接删除不要的题目即可。操作时要注意以下几点：1. 题目中留待填写的填空位应使用下划线字符“_”插入，应使用Shift+减号键，而不能用空格加下划线格式。2. 问答题要先用换行增加足够的空行，为回答预留位置，而且每一个空行中至少要输入一个空格否则在Word中会只剩下一个空行。按Alt+回车键可在单元格中换行。3.选择题要先在B2单元格中输入一个空行以及A、B、C、D选项和用于定位的空格，拖动填充柄复制到下面各单元格中，再逐一输入题目和选项内容，这样做比较快（如图1）。只要不输入所属单元那些空行就不会参与选题，所以不用担心影响出题。4.题库中的题目可采取插入、删除行的方式进行删除或增加，不过以插入行的方式增加题目时要注意同时把上面一行的公式复制下来。如果你只是暂时不使用某题，而不想彻底删除它，那么把A列该题题号中的公式删除就可以让它不参与出题，下次需要用时再把上面一行的公式复制下来即可。需要画图的几何、化学题等学科不适合用这方法，因为绘制的图形无法和单元格一起被引用。不过我们可以考虑先把画好的图添加到剪贴画库中（或Word的自动图文集），然后把图片名放在题目中，等在Word中生成试卷后再按图名找出相应图形插入，当然这样做就没那么方便了。生成试卷1 抽题打开“出卷系统.xls”，在“出卷”工作表中输入出卷范围的起、止单元（B7、B8）和各种题目的题数（B2、B3、B4）。单击出卷按钮，就会按指定的出卷范围和题数随机抽取各种题目显示在F列，G列则显示答案（如图2）。注：当有单元格处于文字输入编辑状态时是无法运行宏的，所以在输入题数等数值后要先回车或单击一下其他单元格，再点击出卷按钮才会动作。2 修改若觉得哪一题不顺眼的话，可以直接到相应的题库中寻找所需题目，把题号输入出卷工作表中相应题目左侧的题号单元格（D列）中，回车后就可以换成新的题目。确定满意后，在F2单元格输入试卷名称，单击工作表中的保存按钮把它添加到记录中。注：使用“窗口”菜单中的“新建窗口”和“并排比较”可以让你同时查看两个工作表。建议对题库按所属单元（D列）升序排列以方便手动找题。3 生成B卷单击AB卷按钮，即可得到另一份排序不同的B卷，修改一下试卷名称，再保存就行了。4 生成试卷保存一下“出卷系统.xls”，打开“试卷及答案.doc”文件，在“邮件合并”工具栏的“定位记录”中输入“2”，回车，显示出来的就是刚保存的试题（如图3）。如果题数有变动的话还要再拖动调整一下各大题目的位置和其中的记分标准。题目都自动编号了很容易看出要如何调整。还可以适当增加、删除问答题下的空行以调整试卷页数，注意删除的空行在刷新域后会自动恢复。然后就可以单击打印按钮把试卷和标准答案一起打印出来了。如果有制作AB卷的话，只要把“定位记录”换成“3”，就可以再打印出另一份B卷。注：如果你不想保存制作的试卷，那么选题后不要按工作表中的保存按钮，直接保存“出卷系统xls”文件，打开“试卷及答案doc”文件定位到记录1也一样可以直接打印出来，而这张试卷记录将会在你下次出卷时自动被消除。试卷记录查询打开“试卷及答案.doc”，单击“下一记录”即可依次显示以前的各份试卷记录。单击“邮件合并”工具栏的“收件人”按钮，在弹出的“邮件合并收件人”窗口中可对记录进行排序、筛选，排序、筛选方法写在窗口上就不细说了（如图4），我只提醒一点，选择“高级”可进行更详细的筛选