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

張丹丹

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

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

中圖分類號(hào)：G434? ? ? ? 文獻(xiàn)標(biāo)識(shí)碼：A

文章編號(hào)：1009-3044（2019）28-0234-02

1 課題研究背景及特點(diǎn)

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

2 排課模塊

2.1模塊論述

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

我把上述公式稍作翻譯：=right（index（機(jī)房源列，small（if（countif（下拉機(jī)房列，機(jī)房源列）=0，row（機(jī)房源列），4^8），row（A1）））&””，5），按ctrl+shift+enter產(chǎn)生數(shù)組公式，在這里可以使用普通公式，如果使用普通if（）會(huì)產(chǎn)生空格，這樣下拉菜單中也包含空格，而無(wú)法產(chǎn)生連續(xù)的數(shù)組形式的數(shù)據(jù)。這是一個(gè)使用萬(wàn)能公式制作的典型的一次性下拉選項(xiàng)，但在排課表中，機(jī)房隨星期、節(jié)次不斷重復(fù)使用，除了以上輔助列、數(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é)次不斷重復(fù)使用機(jī)房。

2.2公式論述

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

3 翻頁(yè)模塊

3.1翻頁(yè)模塊論述

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

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

3.2翻頁(yè)代碼論述

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

4 Excel查屏模塊

4.1查屏模塊論述

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

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

4.2查屏公式論述

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

參考文獻(xiàn)：

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

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

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

[4] 新英.EXCEL VBA在教育管理中的應(yīng)用[J].智庫(kù)時(shí)代，2019（6）.

[5] 無(wú)私為大家.VBA定時(shí)器說(shuō)明書(shū)[DB].[2010-05-14].https：//www.docin.com/p-54707081.html.

[6] 瑞德空間.Excel與VBA程序設(shè)計(jì)[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ù)庫(kù)[DB].[2010-08-06].https：//www.docin.com/p-69051033.html.

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