基于Excel VBA的講課競賽抽簽系統設計與實現

周志軍，竇志強，朱春曉

（中國人民解放軍信息工程大學　訓練部考試中心，鄭州 450001）

基于Excel VBA的講課競賽抽簽系統設計與實現

周志軍，竇志強，朱春曉

（中國人民解放軍信息工程大學訓練部考試中心，鄭州 450001）

通過組織講課競賽等形式不斷提高青年教師教學能力，是高等院校確保人才培養質量穩步提升的重要保證。在高校講課競賽等活動中，需要以公正、快捷、直觀的方式對參加人員、講課題目進行抽簽，文章通過實例介紹基于Excel VBA設計講課競賽抽簽系統的實現方法。

VBA；講課競賽；抽簽系統；洗牌算法

1　引言

在高等院校中，提高青年教師教學能力是不斷提升人才培養質量的重要保證。組織以老帶新、崗位練兵、講課競賽等形式的教學實踐活動成為促進青年教師提高教學能力的有效手段。在講課競賽活動中，如何保證分組抽簽環節的公開、公正、透明，成為參賽人員關注的焦點。由于通用抽簽軟件無法滿足特定的抽簽規則，對抽簽數據格式和內容的具體需求存在差異，在解放軍信息工程大學校2014年組織的青年教員講課競賽活動中，為了高效、公正、透明地進行抽簽，自行開發了講課競賽抽簽系統。在全年度的競賽活動中，抽簽系統發揮了重要作用。

2　需求分析

在比賽前，按照競賽規則，全校各教研室所有符合條件的青年教員均列入參賽備選人員名單，每人準備數個講課題目。按照學科類別，將所有教研室分為4個大組，全年組織多輪次講課比賽，每輪次從每組人員中抽取相同數量的選手參賽，講課題目和出場順序隨機確定。同時，為了確保青年教員廣泛參與，需要實現全年每個教研室青年教員參賽比例和覆蓋面基本相當。在此規則下，抽簽系統既要保證公平、公開，又須符合各項比例要求。系統需要實現以下功能：

（1）按組別抽選參賽選手和講課題目。

（2）按教研室人員比例進行抽選。

（3）參賽人員和講課題目隨機選定。

（4）抽中人員出場順序隨機確定。

（5）抽選結果填入表格即時打印。

（6）生成各組參賽選手基本信息表用于講課競賽工作用表。

3　設計思路

VBA是Visual Basic的一種宏語言，是微軟開發出來在其桌面應用程序中執行通用的自動化（OLE）任務的編程語言。主要能用來擴展 Windows的應用程序功能，特別是 Microsoft Office軟件。也可說是一種應用程序視覺化的Basic腳本。Office軟件中的Word、Excel、Access、Powerpoint都可以利用VBA使這些軟件的應用效率更高。VBA不但繼承了VB的開發機制，而且VBA還具有與VB相似的語言結構，它們的集成開發環境IDE也幾乎相同。

由于教員和選題信息是借助Excel來完成，因此，在 Excel文件中嵌入VBA程序即可直接實現隨機抽簽。

在數據準備階段，需將所有教員、分組、課程、選題等信息匯總在一個統一的Excel表格中。在每次抽選時根據教研室所在組別確定選題抽選范圍，利用隨機函數在抽選范圍內隨機確定選手和題目。在抽選時需檢測所抽選教員是否已在之前被選中過，不能重復抽選同一教員。對檢測通過的教員，標記當前教員本次已被抽選，避免后續抽選過程中被重復抽選。為被選中教員利用隨機函數確定出場順序，抽選完成之后保存并顯示抽選結果。利用打印按鈕對抽選結果信息表進行打印，各教學單位抽簽代表簽字確認，并同步將各組參賽選手基本信息和選題信息從總表中提取出來，生成參賽選手基本信息表用于講課競賽工作用表。

系統程序邏輯結構圖如圖1所示。

圖1程序邏輯結構

4　軟件實現

4.1程序界面

由于系統功能單一而明確，界面設計應立足實用，盡量簡潔。在系統界面上設置必備的選項和按鈕。選項用于選擇抽選組別，按鈕分別設置“抽簽”、“重置”、“確定”、“打印”。“抽簽”按鈕用于實現抽簽功能；“重置”按鈕用于重置各項參數，使抽簽操作可重復；“確定”按鈕用于確認抽簽結果，將抽簽結果匯總至表格，并提取抽中的教員和題目信息填入參賽選手基本信息表；“打印”按鈕用于打印抽簽結果表格，供抽簽人員現場確認使用。在按鈕下方設置文本域，用于實時顯示各組抽簽結果，界面如圖2所示。

圖2系統界面

4.2主要功能實現

抽簽的核心在于隨機抽取教員和題目、隨機確定出場順序。由于教員、課程、選題等信息匯總在一個Excel表中，隨機抽取教員和題目即可通過隨機選擇表格行號來實現。使用Randomize函數隨機選擇行號的實現是很簡單的，關鍵點在于確定選擇范圍。不同教員歸屬于不同的教研室，不同教研室分布在不同的組中。使抽選結果符合抽選規則的過程便轉化為使抽選范圍符合競賽規則的過程。為了合理確定抽選范圍，只需將備選信息匯總表依次按照分組、教研室、教員、課程、選題的順序排序，便可按照抽選要求確定一個連續的行號范圍作為抽選范圍賦值給邊界變量。

Private Sub confirmRange（）＇確定抽選范圍

Dim i As Integer

totalT=0＇備選教研室選題總行數

startRow=0＇備選教研室選題起始行

For i=2 To totalRow＇從匯總表計算備選教研室jID抽選行號范圍

If Cells（i，20）.Value=jID Then

totalT=totalT+1

If totalT=1 Then

startRow=i

Else

End If

Else

End If

Next i

End Sub

確定抽選范圍后，即可隨機抽選教員和選題：

confirmRange

Randomize

iRnd=Int（Rnd*totalT+startRow）＇所抽選行號

進行沖突檢測后，將iRnd行的教員、課程、選題信息等所需提取的信息賦予二維數組tInfo（x，y）的第二維變量。

選題信息抽選完畢后，需要對出場順序進行隨機，若直接使用隨機數產生出場序號，可能會出現出場序號重復的情形。因此，采用洗牌算法對出場順序數組進行隨機的方法實現。

Private Sub shuffle（）＇洗牌算法，隨機出場順序

Dim tmp As Integer

Dim i As Integer

Dim j As Integer

For i=1 To gpTotal＇gpTotal為本組參賽人數

tOrder（i）=i

Next i

Randomize

For i=gpTotal To 1 Step-1

j=Int（Rnd*i）+1

tmp=tOrder（j）

tOrder（j）=tOrder（i）

tOrder（i）=tmp

Next i

End Sub

對出場順序隨機洗牌之后將數組tOrder（）的值順序賦予tInfo（x，y）的第一維變量，再將tInfo（x，y）按照第一維變量值的順序輸出第二維變量的內容，即得到隨機出場順序后的抽簽結果。

5　結論

本程序經過反復的修改和調試，運行結果達到了系統設計的目標。以Excel作為數據源，既便于基礎數據整理，又不需進行數據轉換，開發調試效率可大大提高，運行效果直觀明了。VBA語法簡單易學，基于Excel的VBA程序設計代碼簡潔、算法易懂，利用簡單實用的工具便可高效快捷地實現開發需求。使用VBA可以為Excel應用程序開發很多新的功能或增強已有的功能，也使得用戶對各種數據的處理更加得心應手，大大提高工作效率。實踐證明，利用Excel軟件和Excel VBA編程相結合，可高效解決很多日常工作中遇到的常見數據處理問題。

主要參考文獻

［1］魏汪洋.Excel VBA語法速查手冊［M］.北京：化學工業出版社，2011.

［2］孫紅梅.巧用VBA編程實現電大畢業生照片的批量查找［J］.福建廣播電視大學學報，2014（1）：85-88.

［3］門珮玉.利用VBA編程搭建賽事自動計分系統［J］.中國管理信息化，2014，17（20）：87-89.

10.3969/j.issn.1673-0194.2016.11.112

TP317.3

A

1673-0194（2016）11-0187-04

2016-03-15

周志軍（1979-），男，河北石家莊人，解放軍信息工程大學訓練部考試中心講師，主要研究方向：考務管理、考試理論。