基于Excel的试卷自动生成系统的设计与实现

张玉叶 马春清

摘 要： 为减轻教师组卷的工作负担及提高工作效率，目前已经出现很多题库管理系统或在线考试系统。这些系统有的是花很大投入开发的专业软件；有的只能在计算机上考试，无法生成纸质试卷，不能满足学校的要求。根据目前多数学校的实际情况，介绍了一个利用Excel VBA开发的试卷自动生成系统。该系统具有良好的通用性和可扩展性，同时因系统直接借助于Excel本身强大的功能，开发投入相对较少，且无须安装，使用简单方便，能够适合各类学校的多数课程。

关键词： Excel； VBA； 试卷自动生成系统； 题库

中图分类号：TP312 文献标志码：A 文章编号：1006-8228（2013）02-25-03

Design and implementation of automatic generating system of exam paper based on Excel

Zhang Yuye1， Ma Chunqing2

（1. Dept. of computer， Jinan Vocational College， Jinan， Shandong 250014， China； 2. Jinan Information Engineering School）

Abstract： To reduce the teachers workload in organizing exam papers and to improve working efficiency， there has been a lot of test questions database management system or online examination system. But either these systems are expensive professional software or can only be done on computer and do not generate exam papers. In a word， they do not meet the requirements of schools. Considering the actual situation of most schools， an automatic generating system of exam papers developed by Excel VBA is introduced in this paper. This system is shown to have good universality and expansibility. Because the system directly uses the powerful function of Excel itself， the investment in development is relatively small. The system does not need to install and is easy to use. It is suitable for most courses in all types of schools. The design and realization procedure of the system are described.

Key words： Excel； VBA； automatic generating system of exam papers； test questions database

0 引言

为评价和考察学生对知识的掌握，学校常常要进行各种各样的测验或考试。不论是测验还是考试都需要选题组卷。传统的考试往往是由教师临时组织试卷，当考试或测验比较频繁时则教师的工作负担较重且工作效率不高，有诸多不便。为了减轻教师组卷的工作负担，提高工作效率，同时也利于实现教考分离，本文介绍了一种基于Excel的轻松自由构造题库、随机选题组卷的方法。

1 设计思路

Excel本身具有强大的功能，且现在多数老师都能较熟练使用Excel软件。所以本系统借助于Excel本身强大的数据管理功能配合VBA编程来实现。

2 系统简介

系统由两大模块构成，题库管理模块和试卷生成模块。

2.1 题库管理模块

本模块主要用于题库的管理。翻阅众多的试卷我们可以看出不同课程的试卷其包含的题型、题型数目都是不一样的。为使系统具有通用性及可扩充性，本系统的题库并没有预先设定好题型数，也没有建立专门的数据库，而是采用了Excel本身的功能来实现数据库和数据表。数据库对应的就是Excel的工作薄，数据表对应的就是Excel的工作表。用一张工作表来存放一种题型，用一个工作薄来存放一门课程的题库。Excel中一个工作薄可有255个工作表，也即可以包含255种不同的题型，一张工作表（Excel2007）中最多可以有1048576行×16384列，工作表中的一行用来存放一条题目，也即一种题型最多可以有1048576条题目，对于一般的课程，显然足够用。

在此模块中，用户可轻松方便地管理自己的题库。题库的管理主要包括题型和题目的管理。题型的管理主要有题型的插入、删除、修改等基本操作；题目的管理主要有题目的插入、删除、修改、查找等基本操作。在本系统中，对题库的管理最终都是通过对工作表的操作来实现的。题型的插入、删除、改名等功能实际上对应的就是工作表的插入、删除和重命名等操作。题目的插入、删除、修改和查找等功能就是对工作表中的单元格、行或列的操作。因此只要熟悉Excel的常用基本操作就能很方便地进行题库的维护。

2.2 试卷生成模块

本模块主要用于自动选题组成用户所需的试卷及相应的试卷答案。试卷生成模块的操作主要有两步，第一步选择题型，第二步根据选定的题型确定每种题型的数量，然后系统就会根据用户选中的题型及每种题型的数量自动生成所需要的试卷及相应的试卷答案。

第一步：题型的选择，操作界面如图1所示。在此模块中，系统会自动检测题库中的所有题型，然后将其显示在“现有题型”列表框中。使用时用户先从“现有题型”列表框中选择本试卷所需的题型，选中的题型就会出现在“选中题型”列表框中，此时单击“下一步”按钮进入第二步。

图1 题型选择操作界面

第二步：题型数量的设置，操作界面如图2所示。在此界面中，系统会自动将上一步用户选中的题型及这种题型在题库中共有多少条题目显示出来，用户单击按钮“设置题量”会弹出相应的输入框（操作界面如图3所示）要求用户输入每种题型的数量，用户根据需要输入每种题型所需要的题目数量。当所有题型的数量都设置完毕后系统自动将所有设置好的内容显示出来（操作界面如图4所示），此时用户单击“生成试卷”按钮，系统就会根据用户的设置随机选取相应的题型中的题目生成一份试卷及相应的试卷答案。生成的试卷和试卷答案各放在一张工作表中，工作表的名字分别设为“试卷”和“试卷答案”。如果对生成的试卷不太满意，还可以手工进行调整，这样就可以轻松地实现自动选题和手工选题。

图2 题型数量设置操作界面

图3 题量设置输入界面

图4 题量设置完成界面

3 系统实现的关键技术

3.1 题库管理模块

在此模块中，一种题型用一张工作表存放，且工作表的名字以“题”结尾，如“选择题”。为了便于用户理解和使用，系统提供了一个名为“样题“的工作表作为样例。同时为了便于自动选题组卷，对每张工作表的结构都要事先设置好。目前本系统实现的只是随机选题，组卷策略较为简单。每张工作表只需包含“题号、题目内容、题目答案”3列就可，以后根据需要还可以方便地扩充，如复杂的组卷策略往往还需要综合考虑题目所占的分值、题目的难度、题目的分布等，这时可在后面增加如“题目分值、难度系数、知识点”等列。为方便用户使用及保证表结构的一致性，工作表的结构由VBA编码实现，当用户每添加一个新表时自动设置好结构。同时考虑到用户会对题目进行插入、删除等操作，如用户输入一题号，则在进行插入、删除时题号可能会不连续，不利于后面的随机选题，因此题号一列事先填充好公式，用户只需根据需要拖动填充柄向下复制即可，这样就保证了题号的连续性。

相应的代码如下：

Private Sub Workbook_NewSheet（ByVal Sh As Object）

'每新建一个工作表时，将表结构也即表头自动设好

Sh.Cells（1，1）="题号"

Sh.Cells（1，2）="题目内容"

Sh.Cells（1，3）="题目答案"

Sh.Cells（2，1）.Formula="=row（）-1"

With ActiveWindow '将首行冻结

.SplitColumn=0

.SplitRow=1

End With

ActiveWindow.FreezePanes=True

End Sub

3.2 试卷生成模块

试卷生成模块的实现主要有两步。

第一步为题型选择（操作界面如图1所示）。此界面的实现主要利用了两个列表框，将一个列表框中的列表项移到另一个列表框中，这个的实现较简单，不再赘述。关键是如何将题库中所有题型自动显示在列表框中。实现思路：题库管理模块中规定一种题型用一张工作表存放，且工作表的名字都以“题”结尾。因此将题库中所有题型自动显示在列表框中实际上就是将当前工作薄中所有以“题”结尾的工作表的名称显示在列表框中。具体代码如下：

Private Sub UserForm_Initialize（）

Dim i As Integer

For i=1 To Worksheets.Count

If Trim（Worksheets（i）.Name） Like "*题" And

Trim（Worksheets（i）.Name） <> "样题" Then

ListBox1.AddItem Worksheets（i）.Name

End If

Next

End Sub

第二步为题量设置（操作界面如图2所示）。此界面实现的关键技术有三点：一是如何自动显示出用户选中的题型及此题型包含的总题目数量；二是“题量设置”按钮的实现；三是“生成试卷”按钮的实现。

3.2.1 自动显示题型及题型总数量

在此利用一全局动态数组来存放用户选中的题型。至于如何获取此题型的总题目数量，实际上就是获取工作表中连续单元的行数再减去表头所占的行数即是此题型的总题目数量，也即ActiveCell.CurrentRegion.Rows.Count -1。

3.2.2 题量设置

用户单击按钮“设置题量”会弹出相应的输入框（如图3所示）要求用户输入每种题型的数量，选择的题目数量应该大于0且小于本题型已有的题目总数量，为方便用户输入和防止输入错误，系统在输入对话框中给出了当前题型所有的题目总数量，且提供了判断机制，当用户输入的数据不在此范围时，系统会给出相应的错误提示，这样就保证了系统的健壮性。

判断用户输入数据是否合法的代码如下：

t=Val（InputBox（"请输入" & tt（i， 1） & "的数量

（1-" & tt（i， 2） & "）"， "输入数据"））

'数组tt（i，j）中存放的是用户选中的题型及此题型包含的题目总数量

flag=True

Do While flag

If t<1 Or t>tt（i，2） Then

MsgBox "数据输入错误，请重新输入！输入数据范围应在1-"

& tt（i， 2） & "之间"， _vbOKOnly+vbExclamation， "error"

t=Val（InputBox（"请输入" & tt（i， 1） & "的数量（1-" & tt（i， 2）

& "）"， "输入数据"））

flag=True

Else

flag=False

End If

Loop

3.2.3 生成试卷

当用户设置好相应的题型和题目数量后，单击按钮“生成试卷”时系统将会根据用户的设置自动随机选题组卷。这里的组卷策略选用了较简单的随机出题，因此可直接利用Excel本身提供的rand（）函数来为每道题目产生一个随机数，然后根据随机数大小排序，选取前n（用户设置的题目数量）条题目即可。为防止在进行排序时改变原工作表中题目的次序，在此利用了一个临时工作薄来暂放用户选中的题型（也即相应的工作表），当试卷生成后再将此临时工作薄删除。

随机选题的代码如下：

Dim i As Integer， j As Integer， k As Integer

Dim total As Integer， selnum As Integer

'total 用来暂放本题型的总数，selnum放选中的数量

Dim t As Integer

Dim wb As Workbook

Set wb=Workbooks.Add

'建一临时工作薄，用来暂放选中题型表中数据以避免进行排序时打乱原表中题目的次序。

t=1

For k=1 To UBound（choice）

total=choice（k，2）

selnum=choice（k，3）

For i=1 To ThisWorkbook.Worksheets.Count

If Trim（ThisWorkbook.Sheets（i）.Name）

Like Trim（choice（k，1）） Then

ThisWorkbook.Sheets（i）.Activate

ThisWorkbook.Sheets（i）.Copy before：=wb.Sheets（1）

wb.Sheets（1）.Range（Cells（2，11），Cells（total+1，11））

.Formula="=rand（）" '第K列用来放随机数

wb.Sheets（1）.Range（Cells（2，1），Cells（total+1，11））.Sort _

key1：=wb.Sheets（1）.Range（Cells（2，11），Cells（total+1，

11））， _order1：=xlAscending， Header：=xlGuess

'按第K列排序

wb.Sheets（1）.Range（Cells（2，1），Cells（selnum+1，3））.Copy

'将前selnum行复制到剪贴板中，每行包括题号、题目内容、答案3列

ThisWorkbook.Sheets（"试卷"）.Activate

ThisWorkbook.Sheets（"试卷"）.Cells（t，1）.Value

=choice（k，1） '题型名

ThisWorkbook.Sheets（"试卷"）.Cells（t，1）.Font.ColorIndex

=3 '设置颜色

ThisWorkbook.Sheets（"试卷"）.Range（Cells（t+1，1），

Cells（t+1， 3））.PasteSpecial_Paste：

=xlPasteValuesAndNumberFormats

'将剪贴板中内容复制过来，复制值，保证了题号从1开始

ThisWorkbook.Sheets（"答案"）.Activate

ThisWorkbook.Sheets（"答案"）.Cells（t，1）.Value=choice（k，1）

ThisWorkbook.Sheets（"答案"）.Cells（t，1）.Font.ColorIndex=3

ThisWorkbook.Sheets（"答案"）.Range（Cells（t+1，1），

Cells（t+1，3））.PasteSpecial_Paste：

=xlPasteValuesAndNumberFormats

ThisWorkbook.Sheets（"答案"）.Cells（1，1）.Select

t=t+selnum+1 '下一种题型的位置

End If

Next

Next

wb.Close savechanges：=False

Set wb=Nothing

4 结束语

本文介绍的基于Excel的试卷自动生成系统可以适用于不带图表和公式的任何课程，具有良好的通用性；题库管理方便，题型可根据课程的需要任意添加、删除或修改，具有良好的可扩展性。同时因本系统直接借助于Excel本身强大的功能，所以开发投入相对较少，且无须安装，对环境要求不高，使用简单方便。利用本系统不但能轻松方便地建设某门课程的题库，同时又能随时方便快捷地生成一份试卷或平时测验，既减轻了教师的工作量，又提高了其工作效率。该系统已用于我院一些基础课程题库的建设，使用效果良好。目前该系统还只能处理不带图表和公式的试卷，且组卷策略只选用了较简单的随机出题，具有一定的局限性。笔者将继续对此系统进行完善，争取采用更加科学合理的组卷策略，让系统能够适用于任何一门课程。

参考文献：

[1] 韩加国.Excel VBA从入门到精通[M].化学工业出版社，2010.

[2] 丁士锋.Excel VBA标准教程[M].化学工业出版社，2011.

[3] 张强.Excel 2007与VBA编程从入门到精通[M].电子工业出版社，2008.

[4] 张燕译.Excel VBA实战技巧精粹[M].人民邮电出版社，2007.

[5] 罗刚君.Excel VBA程序开发自学宝典[M].电子工业出版社，2011.