巧用ExcelVBA設(shè)計(jì)高效便捷的計(jì)時(shí)評(píng)分系統(tǒng)

焦麗萍+袁勤

摘要：本文巧用EXCEL強(qiáng)大的計(jì)算功能，設(shè)計(jì)了簡單、高效、便捷的計(jì)時(shí)評(píng)分系統(tǒng)，并介紹了計(jì)時(shí)動(dòng)態(tài)顯示、計(jì)時(shí)評(píng)分聯(lián)動(dòng)等主要功能模塊的實(shí)現(xiàn)方法。

關(guān)鍵詞：VBA；計(jì)時(shí)評(píng)分系統(tǒng)；設(shè)計(jì)

中圖分類號(hào)：TP311 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：1009-3044（2017）34-0201-02

Abstract： This paper uses EXCEL's powerful computing function， designs a simple， efficient and convenient timing and marking system， and introduces the Implementation method ofits main function modules， such as timing dynamic display， the linkage of timing and scoring and so on.

Key words： VBA；Timing and Marking system； Design

競賽評(píng)比是當(dāng)前貫徹能力素質(zhì)教育教學(xué)中不可或缺的教學(xué)環(huán)節(jié)。與之相適應(yīng)，借助信息化手段，實(shí)施客觀、公正、高效、便捷的現(xiàn)場計(jì)時(shí)、評(píng)分對(duì)競賽活動(dòng)效果起到非常重要的作用[1]。雖然目前已有利用C、C#、ASP等語言編寫了專門的競賽評(píng)分系統(tǒng)，但由于競賽中評(píng)委數(shù)量易變、評(píng)分規(guī)則靈活，往往給程序的修改、打包、安裝及維護(hù)帶來了諸多麻煩[2]。因此，巧用EXCEL的強(qiáng)大計(jì)算功能，結(jié)合簡單的VBA技術(shù)，自制簡單、高效、便捷的計(jì)時(shí)評(píng)分系統(tǒng)顯得尤為重要。

1 計(jì)時(shí)評(píng)分系統(tǒng)基本要求

該系統(tǒng)除具備現(xiàn)場計(jì)時(shí)、評(píng)分的功能外，使用時(shí)通常為彰顯客觀公正，往往用大屏幕現(xiàn)場公布，因此，還應(yīng)具有一定的全屏視覺效果。另外，在使用時(shí)還應(yīng)考慮系統(tǒng)的人性化設(shè)計(jì)，如：當(dāng)前選擇的選手進(jìn)行現(xiàn)場計(jì)時(shí)時(shí)，應(yīng)在計(jì)時(shí)屏顯示其姓名，計(jì)時(shí)結(jié)束時(shí)應(yīng)自動(dòng)獲取其用時(shí)信息并填寫至對(duì)應(yīng)的“比賽用時(shí)”一欄，若評(píng)分規(guī)則中有超時(shí)扣分項(xiàng)，還應(yīng)自動(dòng)計(jì)算出用時(shí)扣分分值等等。

2 計(jì)時(shí)評(píng)分系統(tǒng)的整體結(jié)構(gòu)

系統(tǒng)根據(jù)基本要求來看，應(yīng)包括三個(gè)界面，即計(jì)時(shí)界面、評(píng)分界面以及用于存儲(chǔ)系統(tǒng)比賽用時(shí)、超時(shí)界定、記分時(shí)是否去最值等比賽規(guī)則及基本信息的設(shè)置界面，而且后期需進(jìn)行VBA編程，因此，可建一個(gè)啟用宏的Excel工作簿文件，內(nèi)含三個(gè)工作表，工作表名分別為“計(jì)時(shí)”、“評(píng)分”、“規(guī)則”。為使系統(tǒng)具有更好的視覺效果可制作一張背景圖，置于各工作表中。

“計(jì)時(shí)”工作表主要用于動(dòng)態(tài)顯示時(shí)鐘，并具有計(jì)時(shí)功能。界面設(shè)計(jì)可通過添加ActiveX控件來實(shí)現(xiàn)。具體如圖1所示。

“評(píng)分”工作表主要用于具體成績詳情及當(dāng)前排名的顯示，界面如圖2所示，具體功能主要是借助Excel強(qiáng)大的If（）、Sum（）、Rank（）函數(shù)、公式及自動(dòng)填充功能來實(shí)現(xiàn)。

“規(guī)則”工作表主要用于存儲(chǔ)系統(tǒng)基礎(chǔ)數(shù)據(jù)。如標(biāo)準(zhǔn)用時(shí)、超時(shí)上限、超時(shí)下限、每分鐘扣分量、計(jì)算平均分時(shí)是否去最值等信息，該表為常規(guī)的二維表，主要針對(duì)開發(fā)人員使用，當(dāng)系統(tǒng)制作完畢可將其隱藏。

3 主要功能模塊的實(shí)現(xiàn)

3.1計(jì)時(shí)器動(dòng)態(tài)的顯示

即在計(jì)時(shí)工作表中，當(dāng)單擊“計(jì)時(shí)開始”按鈕，系統(tǒng)將記錄當(dāng)前用時(shí)的計(jì)時(shí)標(biāo)簽的標(biāo)題屬性每間隔1秒，變化一次，以達(dá)到動(dòng)態(tài)顯示的目的。當(dāng)單擊“計(jì)時(shí)結(jié)束”按鈕，停止變化和計(jì)時(shí)。在VB中利用計(jì)時(shí)器控件很容易實(shí)現(xiàn)該功能，但在Excel 的ActiveX控件集中并無計(jì)時(shí)器控件，但可利用Excel VBA中Application.OnTime方法，該方法可安排一個(gè)過程在將來的特定時(shí)間運(yùn)行。這里的特定時(shí)間既可以是具體指定的某個(gè)時(shí)間，也可以是指定的一段時(shí)間之后。具體實(shí)現(xiàn)時(shí)可將該功能事件置于VBA公共模塊中來實(shí)現(xiàn)，事件定義完畢，在計(jì)時(shí)工作表中只需在單擊相應(yīng)按鈕中調(diào)用即可。

模塊中，用于動(dòng)態(tài)顯示選手用時(shí)事件GoTime（）及停止動(dòng)態(tài)顯示事件StopTime（）的具體代碼如圖3所示。

在計(jì)時(shí)工作表中，創(chuàng)建“開始計(jì)時(shí)”按鈕的單擊事件，事件中調(diào)用GoTime（）事件；并創(chuàng)建“停止計(jì)時(shí)”按鈕的單擊事件，事件中調(diào)用StopTime（）事件。

3.2 當(dāng)前選手在“計(jì)時(shí)”、“評(píng)分”工作表中的聯(lián)動(dòng)設(shè)計(jì)

所謂“計(jì)時(shí)”、“評(píng)分”工作表的聯(lián)動(dòng)設(shè)計(jì)是指，當(dāng)在“評(píng)分”工作表中，選擇了某位選手后，當(dāng)跳轉(zhuǎn)至“計(jì)時(shí)”工作表時(shí)，在計(jì)時(shí)工作表中會(huì)顯示當(dāng)前被選選手的姓名，當(dāng)單擊“計(jì)時(shí)結(jié)束”按鈕，此選手的比賽用時(shí)及超時(shí)扣分項(xiàng)，系統(tǒng)會(huì)自動(dòng)計(jì)算出來，并填寫在“評(píng)分”表中該選手相應(yīng)的信息欄中。若“評(píng)分”工作表的當(dāng)前活動(dòng)單元格尚未定位在選手姓名一欄，則系統(tǒng)會(huì)給出信息提示，計(jì)時(shí)結(jié)束后，不會(huì)填寫上述信息。

由于選手姓名、比賽用時(shí)以及當(dāng)前選手所在行號(hào)等信息需在2個(gè)不同工作表間相互調(diào)用，故可定義相應(yīng)的全局變量來輔助實(shí)現(xiàn)；首先利用“評(píng)分”工作表的SelectionChange（）事件監(jiān)測當(dāng)前活動(dòng)單元格的行號(hào)和列號(hào)，并存于全局變量中；其次利用“評(píng)分”工作表的Deactivate（）（）事件來監(jiān)測用戶是否選擇了選手，若已選選手，則顯示姓名，若未選擇則彈出提示信息，如圖2所示工作表，其具體代碼如圖4所示：

當(dāng)在“計(jì)時(shí)”工作表中，單擊計(jì)時(shí)結(jié)束按鈕后，繼續(xù)利用全局變量進(jìn)行用時(shí)計(jì)算和超時(shí)處理。具體代碼如圖5所示。

3.3 根據(jù)評(píng)分規(guī)則的智能化設(shè)置

評(píng)分規(guī)則的智能化設(shè)置是指在具體評(píng)分時(shí)，由于評(píng)分規(guī)則不同，有時(shí)計(jì)算評(píng)委平均分時(shí)，通常需要去最值（最高分、最低分），而有時(shí)則無需去最值；有時(shí)超時(shí)需要扣分，而有時(shí)超時(shí)不需要扣分，而且扣分的標(biāo)準(zhǔn)也各不相同，如果按常規(guī)方法去設(shè)計(jì)，這會(huì)為后續(xù)系統(tǒng)的更新、維護(hù)帶來很多不便，所以，根據(jù)這種情況，為增強(qiáng)系統(tǒng)的靈活性及適應(yīng)性，設(shè)計(jì)時(shí)可將上述評(píng)分規(guī)則進(jìn)一步抽象，在“規(guī)則”工作表中通過系統(tǒng)的“是否去最值”屬性、“超時(shí)是否扣分”屬性、“超時(shí)扣分標(biāo)準(zhǔn)”屬性來表示。而在“評(píng)分”工作表中進(jìn)行具體計(jì)算平均分、超時(shí)扣分、最終得分三項(xiàng)時(shí)對(duì)上述三個(gè)屬性值進(jìn)行外部引用，進(jìn)而實(shí)現(xiàn)了系統(tǒng)的智能化設(shè)計(jì)。

計(jì)算平均分公式（假設(shè)當(dāng)前有5個(gè)評(píng)委）：

=IF（規(guī)則！$B$6=0，SUM（D2：H2）/5，（SUM（D2：H2）-MAX（D2：H2）-MIN（D2：H2））/（5-2））

計(jì)算超時(shí)扣分，該值是在單擊“計(jì)時(shí)結(jié)束”按鈕時(shí)進(jìn)行計(jì)算，即在VBA編碼中進(jìn)行，其具體算法如圖5所示。

計(jì)算最后得分公式：=IF（評(píng)分！B7=1，I2-J2，I2）。

以上三步關(guān)鍵計(jì)算，利用IF條件函數(shù)，大大增強(qiáng)了系統(tǒng)的自適應(yīng)性。

3.4系統(tǒng)的保護(hù)及優(yōu)化

首先，為防止用戶隨意更改系統(tǒng)的結(jié)構(gòu)和數(shù)據(jù)造成不必要的人為錯(cuò)誤，可利用Excel工作簿、工作表的保護(hù)操作將“計(jì)時(shí)”工作表、“評(píng)分工作表”以及整個(gè)工作簿文件進(jìn)行保護(hù)。同時(shí)，為防止他人肆意更改VBA代碼也可將VBA工程加密。其次，為使系統(tǒng)達(dá)到更好的全屏顯示效果可利用視圖選項(xiàng)卡，將編輯欄隱藏，并以全屏的形式打開系統(tǒng)。當(dāng)然也可在VBA代碼中利用工作表的打開事件編寫代碼，使系統(tǒng)一打開就以全屏模式打開。其代碼如下：

4 結(jié)束語

借助VBA技術(shù)在Excel中設(shè)計(jì)實(shí)現(xiàn)計(jì)時(shí)評(píng)分系統(tǒng)，主要是借助了Excel強(qiáng)大的公式、函數(shù)等數(shù)據(jù)計(jì)算能力，利用簡單的VBA技術(shù)實(shí)現(xiàn)了比賽用時(shí)的動(dòng)態(tài)顯示功能，既有很好的視覺效果，同時(shí)又達(dá)到了評(píng)分計(jì)算即時(shí)、準(zhǔn)確的數(shù)據(jù)運(yùn)算功能，而且相對(duì)常規(guī)的計(jì)時(shí)評(píng)分系統(tǒng)而言，程序編寫技術(shù)簡單、系統(tǒng)后期的更新與維護(hù)都十分簡便，因此，可為教育工作助力提高教學(xué)效果。

參考文獻(xiàn)：

[1] 王繼文.自動(dòng)計(jì)時(shí)評(píng)分系統(tǒng)在智能樓宇實(shí)訓(xùn)臺(tái)中的應(yīng)用[J].科技期刊，2015（10）.

[2] 牛軍，王萍等.無線電子打分計(jì)分系統(tǒng)的設(shè)計(jì)與實(shí)現(xiàn)[J].南陽理工學(xué)院學(xué)報(bào)，2015（4）.endprint