基于Excel VBA智能排課顯屏系統(tǒng)

張丹丹

摘要：該系統(tǒng)針對目前高職院校機房排版的煩瑣性，出于自主研發(fā)、縮減教學成本、減少改善工作流程等探討，研發(fā)出機房課表顯屏系統(tǒng)。該顯屏系統(tǒng)的研究主要分為三塊：課表查詢及顯屏卡、排課表、顯屏軟硬件配合。本系統(tǒng)基于excel及其VBA應用，對排課、課表導入、查詢、打印、顯屏功能做開發(fā)。使用到excel中萬能數(shù)組公式制作排課表、VBAApplication.OnTime、ActiveWindow.ScrollColumn等函數(shù)制作自動翻頁顯屏、vlookup（）制作顯屏表、。本系統(tǒng)已投入使用一學期，效果顯著，受到使用者及師生的廣泛好評。

關鍵詞：Excel排課;顯屏;VBAApplication.OnTime;ActiveWindow.ScrollColumn;數(shù)組公式

中圖分類號：G434? ? ? ? 文獻標識碼：A

文章編號：1009-3044（2019）28-0234-02

1 課題研究背景及特點

高職院校機房排課有其特殊性，每日安排均會涉及課程、人數(shù)、節(jié)次等限制，導致機房使用的多變性。目前通過調(diào)研我市高校仍大部分采用手工排課及排版顯示。其中排課易“撞車“;而排版過程中，學校也多提供白板，將每日課程用白板筆寫入白板或購買磁性貼牌，供師生查看每日課表，這樣操作不僅煩瑣，而且易錯。目前本人所在高職院二級學院計算機機房按專業(yè)分三類：計算機基礎類（公共類）、計算機網(wǎng)絡和軟件專業(yè)類、數(shù)控技術相關類，總共涉及15個機房，每類分5個機房，人數(shù)大致為40、50、60、90不等，一學期的機房總課時將近6000課時，100多個行政班級、使用人數(shù)將近3000人。這樣計算日均有60課時，對于每日排課、排版都較為煩瑣，于是我使用excel制作了顯屏查詢卡，和相應的排課系統(tǒng)表。完成后，我不僅減小排課思考過程，而且還可以快速顯示和打印每日排課表，方便快捷、簡單易用、排版上無出錯率、查看清晰、提高了工作效率，真正實現(xiàn)辦公自動化，改善傳統(tǒng)的工作流程，縮減教學成本。

2 排課模塊

2.1模塊論述

在《排課表》中我設計了機房選項下拉菜單，從以往手動排課經(jīng)驗來看，經(jīng)常會出現(xiàn)機房沖突的情況，在這張排課表中，每日每節(jié)次，機房號只對應一次選擇機會，避免了機房沖突的可能。在具體實現(xiàn)中，我采用萬能數(shù)組公式：index（small（if（row（）））），制作出下拉菜單選項僅能選擇一次的功能。具體我設計了幾個輔助列：<下拉機房>、<星期節(jié)次機房>（包含周一至周五1-8節(jié)課）、<剩余機房>（包含周一至周五1-8節(jié)課）、<剩余數(shù)量>（包含周一至周五1-8節(jié)課）。在<星期節(jié)次機房>列，我分列填入周一至周五1-8節(jié)課和每個機房號合并的內(nèi)容，如：一12 2C509，代表周一1-2節(jié)2C509機房號。在對應的<剩余機房>列輸入如下公式：

我把上述公式稍作翻譯：=right（index（機房源列，small（if（countif（下拉機房列，機房源列）=0，row（機房源列），4^8），row（A1）））&””，5），按ctrl+shift+enter產(chǎn)生數(shù)組公式，在這里可以使用普通公式，如果使用普通if（）會產(chǎn)生空格，這樣下拉菜單中也包含空格，而無法產(chǎn)生連續(xù)的數(shù)組形式的數(shù)據(jù)。這是一個使用萬能公式制作的典型的一次性下拉選項，但在排課表中，機房隨星期、節(jié)次不斷重復使用，除了以上輔助列、數(shù)組公式產(chǎn)生作用外，本人還在<數(shù)據(jù)有效性>中輸入如下公式：=IF（$A$2="一1、2"，OFFSET（$H$2，0，0，$J$2，1），OFFSET（$I$2，0，0，$K$2，1）），這樣可以根據(jù)每日節(jié)次不斷重復使用機房。

2.2公式論述

以上所述萬能數(shù)組公式首先使用countif（）對比<機房源>列與<下拉菜單>列，顯示出<機房源>列中機房號出現(xiàn)0次的機房，這里已產(chǎn)生了一串數(shù)組，再巧妙的使用small（）參數(shù)row（A1） ，且相對于參數(shù)4^8即65536，將數(shù)據(jù)行號較小的一一提取，最后使用index（）在<機房源>列返回數(shù)據(jù)。因為需想讓排課老師更清晰的選擇排課機房，我最后使用right（）僅提取機房號。<偏移數(shù)量>也是至關重要的，結合countblank（）計算剩余選項的數(shù)量。在數(shù)據(jù)有效性中，我使用if（）、offset（）選擇性偏移對應的有效數(shù)據(jù)，即排出下拉菜單中空格，又根據(jù)星期節(jié)次進行選項重復使用。在此，不得不提offset（），參數(shù)嚴絲合縫的完善下拉菜單，如參數(shù)從坐標開始，此例中不偏不倚，即偏移行列為0，但偏移度卻選擇<剩余數(shù)量>。

3 翻頁模塊

3.1翻頁模塊論述

該模塊我設置了兩個Activex命令按鈕，一個翻頁功能，一個結束功能。點擊翻頁按鈕后，我可以設置每隔6秒滾動至excel 表的下一個窗口，可在三個窗口間無限循環(huán)播放排課表，直至我單擊結束按鈕。

進入VBE界面后，將結束按鈕的caption設置為“結束”。翻頁按鈕的caption設置為“6”。在資源工程窗口雙擊sheet1，選擇翻頁的click（），在里面輸入click（）的事件，如結束按鈕標題更改成“播放中”，翻頁標題改成“0”，ActiveWindow.ScrollColumn = 1，滾動至第一列，接著引用start（）過程，最后新建一個模塊，創(chuàng)建三個過程Sub endtime（）、Sub start（）、Sub times（）。過程代碼如下圖：

3.2翻頁代碼論述

在這段代碼中，主要使用Application.OnTime Now + TimeValue（"00：00：05"）， "times"來循環(huán)播放機房排課表。代碼中時間是每隔五秒執(zhí)行? sub times（），這里使用OnTime定時器可以高效的實現(xiàn)循環(huán)操作。在Sub times（）過程中，先使用if判定ActiveSheet.結束按鈕對象的caption屬性是否為“結束”，如果是，則將ActiveWindow對象調(diào)用ScrollColumn屬性，設置滾動列至第一列;接著調(diào)用val判斷ActiveSheet.翻頁的Caption標題值是否12，是則讓Caption屬性變?yōu)?，滾動至第一列，并且跳轉到 sub start（）;否則，ActiveSheet.翻頁的Caption即為6，改情況下，就將Caption標題值加6，同時賦值給ActiveWindow.ScrollColumn，滾動列至此值。最后在Sub endtime（）中調(diào)用Application.OnTime Now + TimeValue（"00：00：00"）， "times"， ， False，不執(zhí)行任何過程以此結束操作。

4 Excel查屏模塊

4.1查屏模塊論述

該查屏模塊主要支持排課表數(shù)據(jù)導入、提供日課表查詢、快捷打印等功能。主要使用EXCEL查找函數(shù)vlookup（）實現(xiàn)。查詢顯屏涉及三條件：日期、節(jié)次、實驗室名、但因該函數(shù)查找條件唯一，故將三條件合而為一，數(shù)據(jù)關聯(lián)表分別是：《顯示屏》和《課表導入》。其中《顯示屏》結構如圖所示：

從圖中可見實驗室名稱、節(jié)次、日期等表格字段的排列，本人在每列底下分別將對應得實驗室及節(jié)次、日期進行合并形成唯一的查詢參數(shù)，同時在《課表導入》首列同樣將該表中相同三字段進行合并，這樣形成兩邊關聯(lián)的關鍵值。然后回到《顯示屏》表中，對每個機房排版單元格寫入或復制vlookup（）公式，例如某個日期2C509實驗室1-2節(jié)課單元格的上課班級公式為：

4.2查屏公式論述

Vlookup（） 有三個參數(shù)：查找值、查找范圍、查找模式。兩張表需挑選出唯一關鍵字作為查找值，其中參數(shù)<查找值>位于寫入公式的數(shù)據(jù)表、而<查找范圍>位于關聯(lián)的另一張數(shù)據(jù)表，同時注意<查找范圍>所在表的<查找值>必須置于該表首列，最后查找模式選擇<精確匹配>。根據(jù)導入的課表數(shù)據(jù)可自動生成供師生查看的機房安排表，還可根據(jù)日期自動查詢當日的機房排課情況，設置自動打印區(qū)域方便快捷打印。

參考文獻：

[1] 劉永富.Office VBA開發(fā)經(jīng)典[M].北京：清華大學出版社，2019.

[2] 伍云輝.Excel VBA典型實例大全[M].北京：電子工業(yè)出版社，2008.

[3] 許小榮.Excel VBA語法與應用手冊[M].北京：電子工業(yè)出版社，2010.

[4] 新英.EXCEL VBA在教育管理中的應用[J].智庫時代，2019（6）.

[5] 無私為大家.VBA定時器說明書[DB].[2010-05-14].https：//www.docin.com/p-54707081.html.

[6] 瑞德空間.Excel與VBA程序設計[DB].[2008-08-20]. https：//www.docin.com/p-818803.html.

[7] kuangjingxia.ExcelVBA編程教程[DB].[2010-10-11]. https：//www.docin.com/p-87219880.html.

[8] Whyte.Excel+VBA與數(shù)據(jù)庫[DB].[2010-08-06].https：//www.docin.com/p-69051033.html.

【通聯(lián)編輯：王力】