基于VBA+Excel函數(shù)的輔助排考模板的設(shè)計(jì)與實(shí)現(xiàn)

馬海軍　祁淑梅

摘要：介紹一款基于Excel VBA+函數(shù)的排考模板，用VBA編程和函數(shù)以及Excel深度功能實(shí)現(xiàn).其特點(diǎn)是在大家熟悉的辦公軟件基礎(chǔ)上，做出一個(gè)輔助排考模板，該模板可在排考過程中進(jìn)行科目、監(jiān)考教師的沖突提示提醒，對排考情況進(jìn)行匯總分析，大大提高排考效率，功能方便實(shí)用，可供教務(wù)排考人員參考。

關(guān)鍵詞：Excel；VBA；輔助排考；模板；設(shè)計(jì)

中圖分類號：TP393 文獻(xiàn)標(biāo)識碼：A 文章編號：1009-3044（2018）27-0104-04

考試排考是每個(gè)學(xué)校期中期末必須做的一件事情，對于中小學(xué)這項(xiàng)工作往往由教務(wù)處負(fù)責(zé)，對于職業(yè)院校這項(xiàng)工作往往要各系的教學(xué)干事來完成，在實(shí)際操作中，我們發(fā)現(xiàn)考試排考最容易出現(xiàn)的就是漏掉考試科目和監(jiān)考人員沖突這兩大問題。對于有教務(wù)系統(tǒng)的學(xué)校來講，現(xiàn)在的教務(wù)系統(tǒng)對于排考完全可以做到全自動操作，輕點(diǎn)鼠標(biāo)幾次，一個(gè)監(jiān)考表就做出來了，但是排出來的結(jié)果實(shí)事求是的講，要優(yōu)化的地方太多，比如同一人在各場次監(jiān)考的時(shí)間間隔，能不能連排、同一人監(jiān)考的總場數(shù)、那些教師不能監(jiān)考那些科目，誰和誰不能排在一起等等情況，可以說因素很多，讓計(jì)算機(jī)完全自動地排出符合各方面要求的、經(jīng)過優(yōu)化的監(jiān)考表，不是一件容易的事情，筆者認(rèn)為需要綜合利用人工智能、數(shù)學(xué)計(jì)算、大數(shù)據(jù)等方面的知識，目前教務(wù)系統(tǒng)下的排考系統(tǒng)肯定還做不到，這是其一；其二有些學(xué)校壓根沒有排考的系統(tǒng)，據(jù)調(diào)查在我區(qū)中小學(xué)擁有完備教務(wù)系統(tǒng)的學(xué)校真還不多，擁有排考系統(tǒng)的學(xué)校就更加少了。讓一個(gè)本來就很忙的教務(wù)管理者或者教學(xué)干事手工排考肯定費(fèi)時(shí)費(fèi)力，效率不高，錯(cuò)誤率高那是自然的。如何幫助教務(wù)工作者提高排考工作效率，降低排考錯(cuò)誤率，筆者在排考模板的設(shè)計(jì)和實(shí)現(xiàn)上做了一些探索。

需要聲明的是筆者不是專業(yè)的計(jì)算機(jī)軟件開發(fā)人員，本次介紹的輔助排考模板不是開發(fā)一款龐大的、完全自動的專用排考軟件，而是利用現(xiàn)有的大家都熟悉的辦公軟件為平臺，做了一個(gè)實(shí)用的計(jì)算機(jī)輔助排考模板，是讓計(jì)算機(jī)做一部分輔助工作，而不是全部工作。大家知道排考肯定得用Excel，用Word就有點(diǎn)勉為其難了。本模板是筆者在學(xué)校從事教務(wù)管理工作，在排考過程中逐漸摸索學(xué)習(xí)，結(jié)合VBA和Excel函數(shù)公式以及現(xiàn)有基本功能結(jié)合完成的。

1 本模板設(shè)計(jì)思路

本模板最終的目標(biāo)是形成一張比較優(yōu)化的監(jiān)考表，其中不能出現(xiàn)漏考和沖突的問題，為了解決這類問題，模板的原始表格就是三張監(jiān)考表空表，第一張為科目安排表，專門用來填寫所有要考試的科目、班級/考場；第二張為監(jiān)考教師安排表，專門用來安排監(jiān)考教師；第三章為最終的監(jiān)考表，是第一、二張表的匯總表，三張表考試時(shí)間、表頭等信息公用，這三張表都設(shè)置了自動檢查沖突和點(diǎn)選一鍵查詢功能。

1） 根據(jù)人才培養(yǎng)方案，先將要考試的科目粘貼到第一個(gè)表科目安排表模板的科目場數(shù)匯總下的科目欄，然后將所有要考試的班級/教室信息粘貼到班級/教室欄，那些班考什么科目對于教務(wù)管理人員或者教學(xué)干事來講，這是很清楚的，即便不清楚的話，可以從人才培養(yǎng)方案的電子版中直接粘貼過來，再者分散到系，每個(gè)班每學(xué)期開的課也不多，出錯(cuò)率不高，最后把要考的科目粘貼到對應(yīng)的考試時(shí)間和班級/教室下。（圖1）

2） 完成考試時(shí)間和班級/教室的安排后，就要安排對應(yīng)的監(jiān)考教師，只要將擬安排的教考教師名單粘貼到第二個(gè)表監(jiān)考教師安排表模板的監(jiān)考場數(shù)匯總下的監(jiān)考教師欄，然后將監(jiān)考教師復(fù)制粘貼到指定的要監(jiān)考的位置。（圖2）也可以隨時(shí)切換到第三個(gè)表查看最終效果（圖3）。

經(jīng)過這兩步，所有的安排就結(jié)束了，其中可以隨意更換監(jiān)考教師，監(jiān)考排考情況數(shù)據(jù)一目了然，可以幫助安排人員調(diào)整監(jiān)考安排。

2 設(shè)計(jì)的實(shí)現(xiàn)

2.1 一鍵查詢的實(shí)現(xiàn)

只要鼠標(biāo)選到那個(gè)單元格，那個(gè)單元格相同的部分都會突出顯示，這個(gè)功能針對考試科目、監(jiān)考教師、監(jiān)考場數(shù)設(shè)置，可以清楚地看到該科目排考情況和教師的安排情況，對于三張表都適用，VBA代碼如下：

釋義：定義當(dāng)前工作表活動單元格的名稱為XM，然后設(shè)置條件格式，當(dāng)單元格值等于XM時(shí)突出顯示顏色，只要鼠標(biāo)點(diǎn)到哪里，和鼠標(biāo)所選當(dāng)前單元格文字相同的其他單元格都會突出顯示，一目了然。為了防止當(dāng)單元格為空時(shí)，其他為空的單元格區(qū)域也出現(xiàn)突出顯示的情況，必須定義當(dāng)單元格值為0時(shí)，不設(shè)置單元格格式。

如圖4所示。

2.2 右鍵功能重新定義

本模板設(shè)計(jì)簡單，思路清晰，說起來很容易，但是實(shí)現(xiàn)起來卻很費(fèi)周折，為了提高效率，粘貼復(fù)制的操作必須至粘貼數(shù)值，不能粘貼所有格式，一旦粘貼所有格式，統(tǒng)計(jì)匯總、一鍵查詢等都會因格式問題受到影響，為了解決這一問題，必須重新定義鼠標(biāo)右鍵功能，VBA代碼如下：

釋義：名稱為aaa的模塊，模塊的意思為選擇性粘貼；只粘貼值；跳過空白單元格 ，不轉(zhuǎn)置。

需要說明的是可以用鼠標(biāo)右鍵粘貼、復(fù)制，也可以用鍵盤Ctrl+C，Ctrl+V粘貼復(fù)制，效果是一樣的。粘貼的是數(shù)值，復(fù)制的也是數(shù)值，拖曳移動對格式?jīng)]有影響。如果鼠標(biāo)能設(shè)置組合鍵的話，粘貼復(fù)制很快的。

2.3 沖突檢測，實(shí)時(shí)提醒的設(shè)置

本模板最大的特點(diǎn)就是沖突實(shí)時(shí)提醒，比如安排考試科目，同一個(gè)班級或者考場已經(jīng)排過的考試科目，不能再次安排，一旦再次安排，立即突出顯示；安排監(jiān)考教師，同一時(shí)間段安排過一次，就不能再次安排，一旦安排，立即突出顯示，這種突出顯示只有在沖突解決后才會自動消失，這樣一來就不會出現(xiàn)考試科目和監(jiān)考教師的沖突。這是怎么做到的呢，其實(shí)利用的是條件格式，第一個(gè)表，科目每行都設(shè)置條件格式，當(dāng)出現(xiàn)重復(fù)值，立即突出顯示；同樣，第二個(gè)表也設(shè)置條件格式，監(jiān)考教師每列出現(xiàn)重復(fù)值，立即突出顯示。需要注意的是，必須絕對引用。

2.4 跨表提示，實(shí)時(shí)提醒的實(shí)現(xiàn)

本模板原則上先在第一個(gè)表填班級或者考試教室，然后安排考試科目，再在第二個(gè)表安排監(jiān)考教師，問題來了，第一個(gè)表中沒有安排考試科目的時(shí)間段或者班級在第二個(gè)表中就不需要安排監(jiān)考教師，換言之，只有第一個(gè)表中安排了考試科目對應(yīng)的第二個(gè)表才可以安排監(jiān)考教師，安排監(jiān)考教師必須依附于已安排考試科目，這兩者有一一對應(yīng)的關(guān)系。我們利用條件格式和COUNTA函數(shù)來實(shí)現(xiàn).

公式示例：（'1.排科目'！C8為科目表中的科目數(shù)據(jù)，對應(yīng)于教師表中的C12）

=COUNTA（'1.排科目'！C8）+COUNTA（C12）=1 設(shè)置單元格突出顯示

=COUNTA（'1.排科目'！C8）+COUNTA（C12）=2 不設(shè)置單元格格式

條件格式類型：使用公式確定要設(shè)置格式的單元格

公式釋義：判斷第一個(gè)表C8單元格和第二個(gè)表C12單元格是否包含數(shù)據(jù)，若有一個(gè)為空則值等于1，滿足條件突出顯示；若兩個(gè)單元格都包含數(shù)據(jù)，則值等于2，不設(shè)置格式，也就是說科目和監(jiān)考教師都選了，無需強(qiáng)調(diào)。若兩個(gè)單元格都無數(shù)據(jù)，則值等于0，由于沒有定義，所以也默認(rèn)不設(shè)置格式，也就是說科目和監(jiān)考教師都沒內(nèi)容，自然屬于正常情況，無需強(qiáng)調(diào)。這樣一來，只要有考試科目的單元格，對應(yīng)的監(jiān)考教師單元格就會突出顯示；沒有考試科目的單元格，對應(yīng)的監(jiān)考教師單元格一旦安排了監(jiān)考教師也會突出顯示，表明有問題存在，若第二張表無突出顯示提示，則說明第一張表所有科目的考試和班級都安排了監(jiān)考教師，這樣的設(shè)計(jì)可以達(dá)到提示提醒作用。

2.5 監(jiān)考場數(shù)和科目場數(shù)自動匯總的實(shí)現(xiàn)

監(jiān)考場數(shù)和科目場數(shù)自動匯總基于Excel函數(shù)和公式的應(yīng)用，以Y5單元格（X5科目的安排場數(shù)）為例，公式示例如下：

=IF（X5=0，""，COUNTIF（$C$1：$V$25，X5））

釋義：絕對引用，if函數(shù)，解決了兩個(gè)問題：若科目名稱（X5）為空，場數(shù)肯定為0，則不顯示；若科目（X5）有名稱，則觸發(fā)計(jì)數(shù)函數(shù)COUNTIF，在指定區(qū)域$C$1：$V$25查詢X5的個(gè)數(shù)。

2.6 監(jiān)考排考數(shù)據(jù)匯總的實(shí)現(xiàn)

監(jiān)考排考數(shù)據(jù)匯總可以幫助排考者合理排考，以免給個(gè)別教師安排的場數(shù)太多或者因個(gè)人疏忽失誤等原因?qū)е掳才帕吮O(jiān)考教師名單以外的教師參與了監(jiān)考，造成無人監(jiān)考等情況的發(fā)生，并且突出顯示安排場數(shù)最多的教師姓名，已明確排考的個(gè)別情況和整體情況。這些功能的實(shí)現(xiàn)主要用到的是函數(shù)和公式。如下示例：

是否有監(jiān)考名單以外的教師參與排考

=IF（SUM（'2.排教師'！$Y$5：$Y$46，'2.排教師'！$AA$5：$AA$46，'2.排教師'！$AC$5：$AC$46）=COUNTA（'2.排教師'！$C$5：$V$46），"沒有"，"有"）

釋義：絕對引用，if函數(shù)，如果監(jiān)考教師監(jiān)考場數(shù)匯總值和監(jiān)考表中教師的個(gè)數(shù)相等，則沒有是否有監(jiān)考名單以外的教師參與排考，否則，肯定有。這個(gè)功能的設(shè)計(jì)主要是對安排的監(jiān)考教師進(jìn)行二次檢查糾錯(cuò)，確保排考和數(shù)據(jù)統(tǒng)計(jì)不出問題，這類問題常見于操作者沒有從監(jiān)考教師名單中復(fù)制教師姓名，直接在監(jiān)考表中輸入和監(jiān)考教師名單不符的教師名稱所致，直接輸入正確，則不會影響。

安排場次最多的教師人數(shù)（本例中AH2為最大值）

=IF（（COUNTIF（Y5：Y46，AH2）+COUNTIF（AA5：AA46，AH2）+COUNTIF（AC5：AC46，AH2））>1， （COUNTIF（Y5：Y46，AH2）+COUNTIF（AA5：AA46，AH2）+COUNTIF（AC5：AC46，AH2）），"1"）

釋義：絕對引用，if函數(shù)，在三列監(jiān)考教師的排考場數(shù)中找最大值的和，如果和大于1，說明最大值有兩個(gè)以上，則顯示最大值個(gè)數(shù)，否則最大值只有一個(gè)，顯示為1

安排場次最多的教師突出顯示（本例中AH2為最大值）應(yīng)用條件格式突出顯示實(shí)現(xiàn)：

規(guī)則類型：只為包含以下內(nèi)容的單元格設(shè)置格式

單元格值=IF（$AH$2=0，F(xiàn)ALSE，$AH$2）

監(jiān)考教師名單人數(shù)

=COUNTA（'2.排教師'！$X$5：$X$46，'2.排教師'！$Z$5：$Z$46，'2.排教師'！$AB$5：$AB$46）

實(shí)際參與監(jiān)考人數(shù)

=COUNTIF（'2.排教師'！Y5：Y46，">0"）+COUNTIF（'2.排教師'！AA5：AA46，">0"）+COUNTIF（'2.排教師'！AC5：AC46，">0"）

其他數(shù)據(jù)統(tǒng)計(jì)由于相對簡單，不再贅述。

3 排考模板的穩(wěn)定性安全措施

因?yàn)槭桥趴嫉哪０?，用戶只能對指定?shù)據(jù)做粘貼、復(fù)制、刪除、拖曳的操作，為了模板的穩(wěn)定性不能也不允許更改模板的結(jié)構(gòu)和一些不能修改的內(nèi)容。

對于匯總的數(shù)據(jù)或者通過函數(shù)計(jì)算出來的數(shù)據(jù)以及一些公用數(shù)據(jù)選定后再設(shè)置工作表保護(hù)；為了防止人為刪除模板下的三個(gè)工作表，設(shè)置工作簿保護(hù)窗體和結(jié)構(gòu)；為了防止人為刪除或者修改模板下VBA代碼，設(shè)置VBAProject密碼保護(hù)，通過各種保護(hù)確保模板穩(wěn)定。

4 模板的使用局限性分析

本模板經(jīng)過幾個(gè)學(xué)校教學(xué)干事的使用，反響挺好，他們都覺得模板能夠大大提高排考工作效率，分步排考能從根本上杜絕漏掉考試科目、漏監(jiān)考教師以及監(jiān)考教師安排沖突的現(xiàn)象發(fā)生，排考數(shù)據(jù)匯總能極大的地高排考的合理度，讓排考者及時(shí)了解排考的整體情況等等。但作為輔助排考模板，它還存在著很大的局限性，比如不能根據(jù)班級考試學(xué)生人數(shù)進(jìn)行考場的自動分配、不能設(shè)置條件自動將考試科目名單中的科目填充到科目表中、不能設(shè)置條件自動將教師名單中的教師填充到監(jiān)考表中，這些工作都得排考人員手工完成。從某種意義上講，模板升級的空間很小，

5 結(jié)束語

提出的基于VBA+ Excel函數(shù)公式的輔助排考模板的設(shè)計(jì)，對熟悉Excel辦公環(huán)境的工作人員來講，容易理解便于操作。實(shí)際操作過程中還需注意：格式、公式的相對引用和絕對引用，在粘貼公式和其他條件格式的時(shí)候一定要注意，還有條件格式所應(yīng)用的范圍。監(jiān)考教師姓名、考試科目名稱中的空格都應(yīng)去除，防止因?yàn)榭崭癫煌斐蓴?shù)據(jù)檢測錯(cuò)誤，導(dǎo)致沖突檢測不準(zhǔn)確。

本文提及的是一種思路和方法，所設(shè)計(jì)和實(shí)現(xiàn)的模板僅用于輔助排考之用，當(dāng)然還有其他辦法也能夠達(dá)到這樣的目的，或許其他方法比本文提及的方法更加科學(xué)、簡單、有效，希望本文能有拋磚引玉的作用。

參考文獻(xiàn)：

[1] Excel Home.別怕，Excel函數(shù)其實(shí)很簡單[M].北京：人民郵電出版社，2016.

[2] Excel Home.Excel 2010 VBA實(shí)戰(zhàn)技巧精粹[M]. 北京：人民郵電出版社，2015.

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

[通聯(lián)編輯：光文玲]