基于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 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 引言

為評價和考察學生對知識的掌握，學校常常要進行各種各樣的測驗或考試。不論是測驗還是考試都需要選題組卷。傳統的考試往往是由教師臨時組織試卷，當考試或測驗比較頻繁時則教師的工作負擔較重且工作效率不高，有諸多不便。為了減輕教師組卷的工作負擔，提高工作效率，同時也利于實現教考分離，本文介紹了一種基于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.