基于Excel的試卷自動生成系統(tǒng)的設(shè)計與實現(xiàn)

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

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

中圖分類號：TP312 文獻(xiàn)標(biāo)志碼：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 teacher’s 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 引言

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

1 設(shè)計思路

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

2 系統(tǒng)簡介

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

2.1 題庫管理模塊

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

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

2.2 試卷生成模塊

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

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

圖1 題型選擇操作界面

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

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

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

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

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

3.1 題庫管理模塊

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

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

Private Sub Workbook_NewSheet（ByVal Sh As Object）

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

Sh.Cells（1，1）=\"題號\"

Sh.Cells（1，2）=\"題目內(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 試卷生成模塊

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

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

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所示）。此界面實現(xiàn)的關(guān)鍵技術(shù)有三點：一是如何自動顯示出用戶選中的題型及此題型包含的總題目數(shù)量；二是“題量設(shè)置”按鈕的實現(xiàn)；三是“生成試卷”按鈕的實現(xiàn)。

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

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

3.2.2 題量設(shè)置

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

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

t=Val（InputBox（\"請輸入\" 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ù)輸入錯誤，請重新輸入！輸入數(shù)據(jù)范圍應(yīng)在1-\"

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

t=Val（InputBox（\"請輸入\" 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ù)量后，單擊按鈕“生成試卷”時系統(tǒng)將會根據(jù)用戶的設(shè)置自動隨機(jī)選題組卷。這里的組卷策略選用了較簡單的隨機(jī)出題，因此可直接利用Excel本身提供的rand（）函數(shù)來為每道題目產(chǎn)生一個隨機(jī)數(shù)，然后根據(jù)隨機(jī)數(shù)大小排序，選取前n（用戶設(shè)置的題目數(shù)量）條題目即可。為防止在進(jìn)行排序時改變原工作表中題目的次序，在此利用了一個臨時工作薄來暫放用戶選中的題型（也即相應(yīng)的工作表），當(dāng)試卷生成后再將此臨時工作薄刪除。

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

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

Dim total As Integer， selnum As Integer

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

Dim t As Integer

Dim wb As Workbook

Set wb=Workbooks.Add

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

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列用來放隨機(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ù)制到剪貼板中，每行包括題號、題目內(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ù)制過來，復(fù)制值，保證了題號從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 結(jié)束語

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

參考文獻(xiàn)：

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

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

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

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

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