基于Excel VBA的講課競(jìng)賽抽簽系統(tǒng)設(shè)計(jì)與實(shí)現(xiàn)

周志軍，竇志強(qiáng)，朱春曉

（中國(guó)人民解放軍信息工程大學(xué)　訓(xùn)練部考試中心，鄭州 450001）

基于Excel VBA的講課競(jìng)賽抽簽系統(tǒng)設(shè)計(jì)與實(shí)現(xiàn)

周志軍，竇志強(qiáng)，朱春曉

（中國(guó)人民解放軍信息工程大學(xué)訓(xùn)練部考試中心，鄭州 450001）

通過(guò)組織講課競(jìng)賽等形式不斷提高青年教師教學(xué)能力，是高等院校確保人才培養(yǎng)質(zhì)量穩(wěn)步提升的重要保證。在高校講課競(jìng)賽等活動(dòng)中，需要以公正、快捷、直觀的方式對(duì)參加人員、講課題目進(jìn)行抽簽，文章通過(guò)實(shí)例介紹基于Excel VBA設(shè)計(jì)講課競(jìng)賽抽簽系統(tǒng)的實(shí)現(xiàn)方法。

VBA；講課競(jìng)賽；抽簽系統(tǒng)；洗牌算法

1　引言

在高等院校中，提高青年教師教學(xué)能力是不斷提升人才培養(yǎng)質(zhì)量的重要保證。組織以老帶新、崗位練兵、講課競(jìng)賽等形式的教學(xué)實(shí)踐活動(dòng)成為促進(jìn)青年教師提高教學(xué)能力的有效手段。在講課競(jìng)賽活動(dòng)中，如何保證分組抽簽環(huán)節(jié)的公開(kāi)、公正、透明，成為參賽人員關(guān)注的焦點(diǎn)。由于通用抽簽軟件無(wú)法滿足特定的抽簽規(guī)則，對(duì)抽簽數(shù)據(jù)格式和內(nèi)容的具體需求存在差異，在解放軍信息工程大學(xué)校2014年組織的青年教員講課競(jìng)賽活動(dòng)中，為了高效、公正、透明地進(jìn)行抽簽，自行開(kāi)發(fā)了講課競(jìng)賽抽簽系統(tǒng)。在全年度的競(jìng)賽活動(dòng)中，抽簽系統(tǒng)發(fā)揮了重要作用。

2　需求分析

在比賽前，按照競(jìng)賽規(guī)則，全校各教研室所有符合條件的青年教員均列入?yún)①悅溥x人員名單，每人準(zhǔn)備數(shù)個(gè)講課題目。按照學(xué)科類(lèi)別，將所有教研室分為4個(gè)大組，全年組織多輪次講課比賽，每輪次從每組人員中抽取相同數(shù)量的選手參賽，講課題目和出場(chǎng)順序隨機(jī)確定。同時(shí)，為了確保青年教員廣泛參與，需要實(shí)現(xiàn)全年每個(gè)教研室青年教員參賽比例和覆蓋面基本相當(dāng)。在此規(guī)則下，抽簽系統(tǒng)既要保證公平、公開(kāi)，又須符合各項(xiàng)比例要求。系統(tǒng)需要實(shí)現(xiàn)以下功能：

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

（2）按教研室人員比例進(jìn)行抽選。

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

（4）抽中人員出場(chǎng)順序隨機(jī)確定。

（5）抽選結(jié)果填入表格即時(shí)打印。

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

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

VBA是Visual Basic的一種宏語(yǔ)言，是微軟開(kāi)發(fā)出來(lái)在其桌面應(yīng)用程序中執(zhí)行通用的自動(dòng)化（OLE）任務(wù)的編程語(yǔ)言。主要能用來(lái)擴(kuò)展 Windows的應(yīng)用程序功能，特別是 Microsoft Office軟件。也可說(shuō)是一種應(yīng)用程序視覺(jué)化的Basic腳本。Office軟件中的Word、Excel、Access、Powerpoint都可以利用VBA使這些軟件的應(yīng)用效率更高。VBA不但繼承了VB的開(kāi)發(fā)機(jī)制，而且VBA還具有與VB相似的語(yǔ)言結(jié)構(gòu)，它們的集成開(kāi)發(fā)環(huán)境IDE也幾乎相同。

由于教員和選題信息是借助Excel來(lái)完成，因此，在 Excel文件中嵌入VBA程序即可直接實(shí)現(xiàn)隨機(jī)抽簽。

在數(shù)據(jù)準(zhǔn)備階段，需將所有教員、分組、課程、選題等信息匯總在一個(gè)統(tǒng)一的Excel表格中。在每次抽選時(shí)根據(jù)教研室所在組別確定選題抽選范圍，利用隨機(jī)函數(shù)在抽選范圍內(nèi)隨機(jī)確定選手和題目。在抽選時(shí)需檢測(cè)所抽選教員是否已在之前被選中過(guò)，不能重復(fù)抽選同一教員。對(duì)檢測(cè)通過(guò)的教員，標(biāo)記當(dāng)前教員本次已被抽選，避免后續(xù)抽選過(guò)程中被重復(fù)抽選。為被選中教員利用隨機(jī)函數(shù)確定出場(chǎng)順序，抽選完成之后保存并顯示抽選結(jié)果。利用打印按鈕對(duì)抽選結(jié)果信息表進(jìn)行打印，各教學(xué)單位抽簽代表簽字確認(rèn)，并同步將各組參賽選手基本信息和選題信息從總表中提取出來(lái)，生成參賽選手基本信息表用于講課競(jìng)賽工作用表。

系統(tǒng)程序邏輯結(jié)構(gòu)圖如圖1所示。

圖1程序邏輯結(jié)構(gòu)

4　軟件實(shí)現(xiàn)

4.1程序界面

由于系統(tǒng)功能單一而明確，界面設(shè)計(jì)應(yīng)立足實(shí)用，盡量簡(jiǎn)潔。在系統(tǒng)界面上設(shè)置必備的選項(xiàng)和按鈕。選項(xiàng)用于選擇抽選組別，按鈕分別設(shè)置“抽簽”、“重置”、“確定”、“打印”。“抽簽”按鈕用于實(shí)現(xiàn)抽簽功能；“重置”按鈕用于重置各項(xiàng)參數(shù)，使抽簽操作可重復(fù)；“確定”按鈕用于確認(rèn)抽簽結(jié)果，將抽簽結(jié)果匯總至表格，并提取抽中的教員和題目信息填入?yún)①愡x手基本信息表；“打印”按鈕用于打印抽簽結(jié)果表格，供抽簽人員現(xiàn)場(chǎng)確認(rèn)使用。在按鈕下方設(shè)置文本域，用于實(shí)時(shí)顯示各組抽簽結(jié)果，界面如圖2所示。

圖2系統(tǒng)界面

4.2主要功能實(shí)現(xiàn)

抽簽的核心在于隨機(jī)抽取教員和題目、隨機(jī)確定出場(chǎng)順序。由于教員、課程、選題等信息匯總在一個(gè)Excel表中，隨機(jī)抽取教員和題目即可通過(guò)隨機(jī)選擇表格行號(hào)來(lái)實(shí)現(xiàn)。使用Randomize函數(shù)隨機(jī)選擇行號(hào)的實(shí)現(xiàn)是很簡(jiǎn)單的，關(guān)鍵點(diǎn)在于確定選擇范圍。不同教員歸屬于不同的教研室，不同教研室分布在不同的組中。使抽選結(jié)果符合抽選規(guī)則的過(guò)程便轉(zhuǎn)化為使抽選范圍符合競(jìng)賽規(guī)則的過(guò)程。為了合理確定抽選范圍，只需將備選信息匯總表依次按照分組、教研室、教員、課程、選題的順序排序，便可按照抽選要求確定一個(gè)連續(xù)的行號(hào)范圍作為抽選范圍賦值給邊界變量。

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

Dim i As Integer

totalT=0＇備選教研室選題總行數(shù)

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

For i=2 To totalRow＇從匯總表計(jì)算備選教研室jID抽選行號(hào)范圍

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

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

confirmRange

Randomize

iRnd=Int（Rnd*totalT+startRow）＇所抽選行號(hào)

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

選題信息抽選完畢后，需要對(duì)出場(chǎng)順序進(jìn)行隨機(jī)，若直接使用隨機(jī)數(shù)產(chǎn)生出場(chǎng)序號(hào)，可能會(huì)出現(xiàn)出場(chǎng)序號(hào)重復(fù)的情形。因此，采用洗牌算法對(duì)出場(chǎng)順序數(shù)組進(jìn)行隨機(jī)的方法實(shí)現(xiàn)。

Private Sub shuffle（）＇洗牌算法，隨機(jī)出場(chǎng)順序

Dim tmp As Integer

Dim i As Integer

Dim j As Integer

For i=1 To gpTotal＇gpTotal為本組參賽人數(shù)

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

對(duì)出場(chǎng)順序隨機(jī)洗牌之后將數(shù)組tOrder（）的值順序賦予tInfo（x，y）的第一維變量，再將tInfo（x，y）按照第一維變量值的順序輸出第二維變量的內(nèi)容，即得到隨機(jī)出場(chǎng)順序后的抽簽結(jié)果。

5　結(jié)論

本程序經(jīng)過(guò)反復(fù)的修改和調(diào)試，運(yùn)行結(jié)果達(dá)到了系統(tǒng)設(shè)計(jì)的目標(biāo)。以Excel作為數(shù)據(jù)源，既便于基礎(chǔ)數(shù)據(jù)整理，又不需進(jìn)行數(shù)據(jù)轉(zhuǎn)換，開(kāi)發(fā)調(diào)試效率可大大提高，運(yùn)行效果直觀明了。VBA語(yǔ)法簡(jiǎn)單易學(xué)，基于Excel的VBA程序設(shè)計(jì)代碼簡(jiǎn)潔、算法易懂，利用簡(jiǎn)單實(shí)用的工具便可高效快捷地實(shí)現(xiàn)開(kāi)發(fā)需求。使用VBA可以為Excel應(yīng)用程序開(kāi)發(fā)很多新的功能或增強(qiáng)已有的功能，也使得用戶對(duì)各種數(shù)據(jù)的處理更加得心應(yīng)手，大大提高工作效率。實(shí)踐證明，利用Excel軟件和Excel VBA編程相結(jié)合，可高效解決很多日常工作中遇到的常見(jiàn)數(shù)據(jù)處理問(wèn)題。

主要參考文獻(xiàn)

［1］魏汪洋.Excel VBA語(yǔ)法速查手冊(cè)［M］.北京：化學(xué)工業(yè)出版社，2011.

［2］孫紅梅.巧用VBA編程實(shí)現(xiàn)電大畢業(yè)生照片的批量查找［J］.福建廣播電視大學(xué)學(xué)報(bào)，2014（1）：85-88.

［3］門(mén)珮玉.利用VBA編程搭建賽事自動(dòng)計(jì)分系統(tǒng)［J］.中國(guó)管理信息化，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-），男，河北石家莊人，解放軍信息工程大學(xué)訓(xùn)練部考試中心講師，主要研究方向：考務(wù)管理、考試?yán)碚摗?/p>