張玉葉 馬春清

摘 要： 為減輕教師組卷的工作負(fù)擔(dān)及提高工作效率，目前已經(jīng)出現(xiàn)很多題庫(kù)管理系統(tǒng)或在線考試系統(tǒng)。這些系統(tǒng)有的是花很大投入開(kāi)發(fā)的專(zhuān)業(yè)軟件；有的只能在計(jì)算機(jī)上考試，無(wú)法生成紙質(zhì)試卷，不能滿足學(xué)校的要求。根據(jù)目前多數(shù)學(xué)校的實(shí)際情況，介紹了一個(gè)利用Excel VBA開(kāi)發(fā)的試卷自動(dòng)生成系統(tǒng)。該系統(tǒng)具有良好的通用性和可擴(kuò)展性，同時(shí)因系統(tǒng)直接借助于Excel本身強(qiáng)大的功能，開(kāi)發(fā)投入相對(duì)較少，且無(wú)須安裝，使用簡(jiǎn)單方便，能夠適合各類(lèi)學(xué)校的多數(shù)課程。

關(guān)鍵詞： Excel； VBA； 試卷自動(dòng)生成系統(tǒng)； 題庫(kù)

中圖分類(lèi)號(hào)：TP312 文獻(xiàn)標(biāo)志碼：A 文章編號(hào)：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 引言

為評(píng)價(jià)和考察學(xué)生對(duì)知識(shí)的掌握，學(xué)校常常要進(jìn)行各種各樣的測(cè)驗(yàn)或考試。不論是測(cè)驗(yàn)還是考試都需要選題組卷。傳統(tǒng)的考試往往是由教師臨時(shí)組織試卷，當(dāng)考試或測(cè)驗(yàn)比較頻繁時(shí)則教師的工作負(fù)擔(dān)較重且工作效率不高，有諸多不便。為了減輕教師組卷的工作負(fù)擔(dān)，提高工作效率，同時(shí)也利于實(shí)現(xiàn)教考分離，本文介紹了一種基于Excel的輕松自由構(gòu)造題庫(kù)、隨機(jī)選題組卷的方法。

1 設(shè)計(jì)思路

Excel本身具有強(qiáng)大的功能，且現(xiàn)在多數(shù)老師都能較熟練使用Excel軟件。所以本系統(tǒng)借助于Excel本身強(qiáng)大的數(shù)據(jù)管理功能配合VBA編程來(lái)實(shí)現(xiàn)。

2 系統(tǒng)簡(jiǎn)介

系統(tǒng)由兩大模塊構(gòu)成，題庫(kù)管理模塊和試卷生成模塊。

2.1 題庫(kù)管理模塊

本模塊主要用于題庫(kù)的管理。翻閱眾多的試卷我們可以看出不同課程的試卷其包含的題型、題型數(shù)目都是不一樣的。為使系統(tǒng)具有通用性及可擴(kuò)充性，本系統(tǒng)的題庫(kù)并沒(méi)有預(yù)先設(shè)定好題型數(shù)，也沒(méi)有建立專(zhuān)門(mén)的數(shù)據(jù)庫(kù)，而是采用了Excel本身的功能來(lái)實(shí)現(xiàn)數(shù)據(jù)庫(kù)和數(shù)據(jù)表。數(shù)據(jù)庫(kù)對(duì)應(yīng)的就是Excel的工作薄，數(shù)據(jù)表對(duì)應(yīng)的就是Excel的工作表。用一張工作表來(lái)存放一種題型，用一個(gè)工作薄來(lái)存放一門(mén)課程的題庫(kù)。Excel中一個(gè)工作薄可有255個(gè)工作表，也即可以包含255種不同的題型，一張工作表（Excel2007）中最多可以有1048576行×16384列，工作表中的一行用來(lái)存放一條題目，也即一種題型最多可以有1048576條題目，對(duì)于一般的課程，顯然足夠用。

在此模塊中，用戶可輕松方便地管理自己的題庫(kù)。題庫(kù)的管理主要包括題型和題目的管理。題型的管理主要有題型的插入、刪除、修改等基本操作；題目的管理主要有題目的插入、刪除、修改、查找等基本操作。在本系統(tǒng)中，對(duì)題庫(kù)的管理最終都是通過(guò)對(duì)工作表的操作來(lái)實(shí)現(xiàn)的。題型的插入、刪除、改名等功能實(shí)際上對(duì)應(yīng)的就是工作表的插入、刪除和重命名等操作。題目的插入、刪除、修改和查找等功能就是對(duì)工作表中的單元格、行或列的操作。因此只要熟悉Excel的常用基本操作就能很方便地進(jìn)行題庫(kù)的維護(hù)。

2.2 試卷生成模塊

本模塊主要用于自動(dòng)選題組成用戶所需的試卷及相應(yīng)的試卷答案。試卷生成模塊的操作主要有兩步，第一步選擇題型，第二步根據(jù)選定的題型確定每種題型的數(shù)量，然后系統(tǒng)就會(huì)根據(jù)用戶選中的題型及每種題型的數(shù)量自動(dòng)生成所需要的試卷及相應(yīng)的試卷答案。

第一步：題型的選擇，操作界面如圖1所示。在此模塊中，系統(tǒng)會(huì)自動(dòng)檢測(cè)題庫(kù)中的所有題型，然后將其顯示在“現(xiàn)有題型”列表框中。使用時(shí)用戶先從“現(xiàn)有題型”列表框中選擇本試卷所需的題型，選中的題型就會(huì)出現(xiàn)在“選中題型”列表框中，此時(shí)單擊“下一步”按鈕進(jìn)入第二步。

圖1 題型選擇操作界面

第二步：題型數(shù)量的設(shè)置，操作界面如圖2所示。在此界面中，系統(tǒng)會(huì)自動(dòng)將上一步用戶選中的題型及這種題型在題庫(kù)中共有多少條題目顯示出來(lái)，用戶單擊按鈕“設(shè)置題量”會(huì)彈出相應(yīng)的輸入框（操作界面如圖3所示）要求用戶輸入每種題型的數(shù)量，用戶根據(jù)需要輸入每種題型所需要的題目數(shù)量。當(dāng)所有題型的數(shù)量都設(shè)置完畢后系統(tǒng)自動(dòng)將所有設(shè)置好的內(nèi)容顯示出來(lái)（操作界面如圖4所示），此時(shí)用戶單擊“生成試卷”按鈕，系統(tǒng)就會(huì)根據(jù)用戶的設(shè)置隨機(jī)選取相應(yīng)的題型中的題目生成一份試卷及相應(yīng)的試卷答案。生成的試卷和試卷答案各放在一張工作表中，工作表的名字分別設(shè)為“試卷”和“試卷答案”。如果對(duì)生成的試卷不太滿意，還可以手工進(jìn)行調(diào)整，這樣就可以輕松地實(shí)現(xiàn)自動(dòng)選題和手工選題。

圖2 題型數(shù)量設(shè)置操作界面

圖3 題量設(shè)置輸入界面

圖4 題量設(shè)置完成界面

3 系統(tǒng)實(shí)現(xiàn)的關(guān)鍵技術(shù)

3.1 題庫(kù)管理模塊

在此模塊中，一種題型用一張工作表存放，且工作表的名字以“題”結(jié)尾，如“選擇題”。為了便于用戶理解和使用，系統(tǒng)提供了一個(gè)名為“樣題“的工作表作為樣例。同時(shí)為了便于自動(dòng)選題組卷，對(duì)每張工作表的結(jié)構(gòu)都要事先設(shè)置好。目前本系統(tǒng)實(shí)現(xiàn)的只是隨機(jī)選題，組卷策略較為簡(jiǎn)單。每張工作表只需包含“題號(hào)、題目?jī)?nèi)容、題目答案”3列就可，以后根據(jù)需要還可以方便地?cái)U(kuò)充，如復(fù)雜的組卷策略往往還需要綜合考慮題目所占的分值、題目的難度、題目的分布等，這時(shí)可在后面增加如“題目分值、難度系數(shù)、知識(shí)點(diǎn)”等列。為方便用戶使用及保證表結(jié)構(gòu)的一致性，工作表的結(jié)構(gòu)由VBA編碼實(shí)現(xiàn)，當(dāng)用戶每添加一個(gè)新表時(shí)自動(dòng)設(shè)置好結(jié)構(gòu)。同時(shí)考慮到用戶會(huì)對(duì)題目進(jìn)行插入、刪除等操作，如用戶輸入一題號(hào)，則在進(jìn)行插入、刪除時(shí)題號(hào)可能會(huì)不連續(xù)，不利于后面的隨機(jī)選題，因此題號(hào)一列事先填充好公式，用戶只需根據(jù)需要拖動(dòng)填充柄向下復(fù)制即可，這樣就保證了題號(hào)的連續(xù)性。

相應(yīng)的代碼如下：

Private Sub Workbook_NewSheet（ByVal Sh As Object）

'每新建一個(gè)工作表時(shí)，將表結(jié)構(gòu)也即表頭自動(dòng)設(shè)好

Sh.Cells（1，1）="題號(hào)"

Sh.Cells（1，2）="題目?jī)?nèi)容"

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

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

With ActiveWindow '將首行凍結(jié)

.SplitColumn=0

.SplitRow=1

End With

ActiveWindow.FreezePanes=True

End Sub

3.2 試卷生成模塊

試卷生成模塊的實(shí)現(xiàn)主要有兩步。

第一步為題型選擇（操作界面如圖1所示）。此界面的實(shí)現(xiàn)主要利用了兩個(gè)列表框，將一個(gè)列表框中的列表項(xiàng)移到另一個(gè)列表框中，這個(gè)的實(shí)現(xiàn)較簡(jiǎn)單，不再贅述。關(guān)鍵是如何將題庫(kù)中所有題型自動(dòng)顯示在列表框中。實(shí)現(xiàn)思路：題庫(kù)管理模塊中規(guī)定一種題型用一張工作表存放，且工作表的名字都以“題”結(jié)尾。因此將題庫(kù)中所有題型自動(dòng)顯示在列表框中實(shí)際上就是將當(dāng)前工作薄中所有以“題”結(jié)尾的工作表的名稱(chēng)顯示在列表框中。具體代碼如下：

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

第二步為題量設(shè)置（操作界面如圖2所示）。此界面實(shí)現(xiàn)的關(guān)鍵技術(shù)有三點(diǎn)：一是如何自動(dòng)顯示出用戶選中的題型及此題型包含的總題目數(shù)量；二是“題量設(shè)置”按鈕的實(shí)現(xiàn)；三是“生成試卷”按鈕的實(shí)現(xiàn)。

3.2.1 自動(dòng)顯示題型及題型總數(shù)量

在此利用一全局動(dòng)態(tài)數(shù)組來(lái)存放用戶選中的題型。至于如何獲取此題型的總題目數(shù)量，實(shí)際上就是獲取工作表中連續(xù)單元的行數(shù)再減去表頭所占的行數(shù)即是此題型的總題目數(shù)量，也即ActiveCell.CurrentRegion.Rows.Count -1。

3.2.2 題量設(shè)置

用戶單擊按鈕“設(shè)置題量”會(huì)彈出相應(yīng)的輸入框（如圖3所示）要求用戶輸入每種題型的數(shù)量，選擇的題目數(shù)量應(yīng)該大于0且小于本題型已有的題目總數(shù)量，為方便用戶輸入和防止輸入錯(cuò)誤，系統(tǒng)在輸入對(duì)話框中給出了當(dāng)前題型所有的題目總數(shù)量，且提供了判斷機(jī)制，當(dāng)用戶輸入的數(shù)據(jù)不在此范圍時(shí)，系統(tǒng)會(huì)給出相應(yīng)的錯(cuò)誤提示，這樣就保證了系統(tǒng)的健壯性。

判斷用戶輸入數(shù)據(jù)是否合法的代碼如下：

t=Val（InputBox（"請(qǐng)輸入" & tt（i， 1） & "的數(shù)量

（1-" & tt（i， 2） & "）"， "輸入數(shù)據(jù)"））

'數(shù)組tt（i，j）中存放的是用戶選中的題型及此題型包含的題目總數(shù)量

flag=True

Do While flag

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

MsgBox "數(shù)據(jù)輸入錯(cuò)誤，請(qǐng)重新輸入！輸入數(shù)據(jù)范圍應(yīng)在1-"

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

t=Val（InputBox（"請(qǐng)輸入" & tt（i， 1） & "的數(shù)量（1-" & tt（i， 2）

& "）"， "輸入數(shù)據(jù)"））

flag=True

Else

flag=False

End If

Loop

3.2.3 生成試卷

當(dāng)用戶設(shè)置好相應(yīng)的題型和題目數(shù)量后，單擊按鈕“生成試卷”時(shí)系統(tǒng)將會(huì)根據(jù)用戶的設(shè)置自動(dòng)隨機(jī)選題組卷。這里的組卷策略選用了較簡(jiǎn)單的隨機(jī)出題，因此可直接利用Excel本身提供的rand（）函數(shù)來(lái)為每道題目產(chǎn)生一個(gè)隨機(jī)數(shù)，然后根據(jù)隨機(jī)數(shù)大小排序，選取前n（用戶設(shè)置的題目數(shù)量）條題目即可。為防止在進(jìn)行排序時(shí)改變?cè)ぷ鞅碇蓄}目的次序，在此利用了一個(gè)臨時(shí)工作薄來(lái)暫放用戶選中的題型（也即相應(yīng)的工作表），當(dāng)試卷生成后再將此臨時(shí)工作薄刪除。

隨機(jī)選題的代碼如下：

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

Dim total As Integer， selnum As Integer

'total 用來(lái)暫放本題型的總數(shù)，selnum放選中的數(shù)量

Dim t As Integer

Dim wb As Workbook

Set wb=Workbooks.Add

'建一臨時(shí)工作薄，用來(lái)暫放選中題型表中數(shù)據(jù)以避免進(jìn)行排序時(shí)打亂原表中題目的次序。

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列用來(lái)放隨機(jī)數(shù)

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行復(fù)制到剪貼板中，每行包括題號(hào)、題目?jī)?nèi)容、答案3列

ThisWorkbook.Sheets（"試卷"）.Activate

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

=choice（k，1） '題型名

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

=3 '設(shè)置顏色

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

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

=xlPasteValuesAndNumberFormats

'將剪貼板中內(nèi)容復(fù)制過(guò)來(lái)，復(fù)制值，保證了題號(hào)從1開(kāi)始

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 結(jié)束語(yǔ)

本文介紹的基于Excel的試卷自動(dòng)生成系統(tǒng)可以適用于不帶圖表和公式的任何課程，具有良好的通用性；題庫(kù)管理方便，題型可根據(jù)課程的需要任意添加、刪除或修改，具有良好的可擴(kuò)展性。同時(shí)因本系統(tǒng)直接借助于Excel本身強(qiáng)大的功能，所以開(kāi)發(fā)投入相對(duì)較少，且無(wú)須安裝，對(duì)環(huán)境要求不高，使用簡(jiǎn)單方便。利用本系統(tǒng)不但能輕松方便地建設(shè)某門(mén)課程的題庫(kù)，同時(shí)又能隨時(shí)方便快捷地生成一份試卷或平時(shí)測(cè)驗(yàn)，既減輕了教師的工作量，又提高了其工作效率。該系統(tǒng)已用于我院一些基礎(chǔ)課程題庫(kù)的建設(shè)，使用效果良好。目前該系統(tǒng)還只能處理不帶圖表和公式的試卷，且組卷策略只選用了較簡(jiǎn)單的隨機(jī)出題，具有一定的局限性。筆者將繼續(xù)對(duì)此系統(tǒng)進(jìn)行完善，爭(zhēng)取采用更加科學(xué)合理的組卷策略，讓系統(tǒng)能夠適用于任何一門(mén)課程。

參考文獻(xiàn)：

[1] 韓加國(guó).Excel VBA從入門(mén)到精通[M].化學(xué)工業(yè)出版社，2010.

[2] 丁士鋒.Excel VBA標(biāo)準(zhǔn)教程[M].化學(xué)工業(yè)出版社，2011.

[3] 張強(qiáng).Excel 2007與VBA編程從入門(mén)到精通[M].電子工業(yè)出版社，2008.

[4] 張燕譯.Excel VBA實(shí)戰(zhàn)技巧精粹[M].人民郵電出版社，2007.

[5] 羅剛君.Excel VBA程序開(kāi)發(fā)自學(xué)寶典[M].電子工業(yè)出版社，2011.