基于Excel VBA智能排课显屏系统

张丹丹

摘要：该系统针对目前高职院校机房排版的烦琐性，出于自主研发、缩减教学成本、减少改善工作流程等探讨，研发出机房课表显屏系统。该显屏系统的研究主要分为三块：课表查询及显屏卡、排课表、显屏软硬件配合。本系统基于excel及其VBA应用，对排课、课表导入、查询、打印、显屏功能做开发。使用到excel中万能数组公式制作排课表、VBAApplication.OnTime、ActiveWindow.ScrollColumn等函数制作自动翻页显屏、vlookup（）制作显屏表、。本系统已投入使用一学期，效果显著，受到使用者及师生的广泛好评。

关键词：Excel排课;显屏;VBAApplication.OnTime;ActiveWindow.ScrollColumn;数组公式

中图分类号：G434        文献标识码：A

文章编号：1009-3044（2019）28-0234-02

1 课题研究背景及特点

高职院校机房排课有其特殊性，每日安排均会涉及课程、人数、节次等限制，导致机房使用的多变性。目前通过调研我市高校仍大部分采用手工排课及排版显示。其中排课易“撞车“;而排版过程中，学校也多提供白板，将每日课程用白板笔写入白板或购买磁性贴牌，供师生查看每日课表，这样操作不仅烦琐，而且易错。目前本人所在高职院二级学院计算机机房按专业分三类：计算机基础类（公共类）、计算机网络和软件专业类、数控技术相关类，总共涉及15个机房，每类分5个机房，人数大致为40、50、60、90不等，一学期的机房总课时将近6000课时，100多个行政班级、使用人数将近3000人。这样计算日均有60课时，对于每日排课、排版都较为烦琐，于是我使用excel制作了显屏查询卡，和相应的排课系统表。完成后，我不仅减小排课思考过程，而且还可以快速显示和打印每日排课表，方便快捷、简单易用、排版上无出错率、查看清晰、提高了工作效率，真正实现办公自动化，改善传统的工作流程，缩减教学成本。

2 排课模块

2.1模块论述

在《排课表》中我设计了机房选项下拉菜单，从以往手动排课经验来看，经常会出现机房冲突的情况，在这张排课表中，每日每节次，机房号只对应一次选择机会，避免了机房冲突的可能。在具体实现中，我采用万能数组公式：index（small（if（row（）））），制作出下拉菜单选项仅能选择一次的功能。具体我设计了几个辅助列：<下拉机房>、<星期节次机房>（包含周一至周五1-8节课）、<剩余机房>（包含周一至周五1-8节课）、<剩余数量>（包含周一至周五1-8节课）。在<星期节次机房>列，我分列填入周一至周五1-8节课和每个机房号合并的内容，如：一12 2C509，代表周一1-2节2C509机房号。在对应的<剩余机房>列输入如下公式：

我把上述公式稍作翻译：=right（index（机房源列，small（if（countif（下拉机房列，机房源列）=0，row（机房源列），4^8），row（A1）））&””，5），按ctrl+shift+enter产生数组公式，在这里可以使用普通公式，如果使用普通if（）会产生空格，这样下拉菜单中也包含空格，而无法产生连续的数组形式的数据。这是一个使用万能公式制作的典型的一次性下拉选项，但在排课表中，机房随星期、节次不断重复使用，除了以上辅助列、数组公式产生作用外，本人还在<数据有效性>中输入如下公式：=IF（$A$2="一1、2"，OFFSET（$H$2，0，0，$J$2，1），OFFSET（$I$2，0，0，$K$2，1）），这样可以根据每日节次不断重复使用机房。

2.2公式论述

以上所述万能数组公式首先使用countif（）对比<机房源>列与<下拉菜单>列，显示出<机房源>列中机房号出现0次的机房，这里已产生了一串数组，再巧妙的使用small（）参数row（A1） ，且相对于参数4^8即65536，将数据行号较小的一一提取，最后使用index（）在<机房源>列返回数据。因为需想让排课老师更清晰的选择排课机房，我最后使用right（）仅提取机房号。<偏移数量>也是至关重要的，结合countblank（）计算剩余选项的数量。在数据有效性中，我使用if（）、offset（）选择性偏移对应的有效数据，即排出下拉菜单中空格，又根据星期节次进行选项重复使用。在此，不得不提offset（），参数严丝合缝的完善下拉菜单，如参数从坐标开始，此例中不偏不倚，即偏移行列为0，但偏移度却选择<剩余数量>。

3 翻页模块

3.1翻页模块论述

该模块我设置了两个Activex命令按钮，一个翻页功能，一个结束功能。点击翻页按钮后，我可以设置每隔6秒滚动至excel 表的下一个窗口，可在三个窗口间无限循环播放排课表，直至我单击结束按钮。

进入VBE界面后，將结束按钮的caption设置为“结束”。翻页按钮的caption设置为“6”。在资源工程窗口双击sheet1，选择翻页的click（），在里面输入click（）的事件，如结束按钮标题更改成“播放中”，翻页标题改成“0”，ActiveWindow.ScrollColumn = 1，滚动至第一列，接着引用start（）过程，最后新建一个模块，创建三个过程Sub endtime（）、Sub start（）、Sub times（）。过程代码如下图：

3.2翻页代码论述

在这段代码中，主要使用Application.OnTime Now + TimeValue（"00：00：05"）， "times"来循环播放机房排课表。代码中时间是每隔五秒执行  sub times（），这里使用OnTime定时器可以高效的实现循环操作。在Sub times（）过程中，先使用if判定ActiveSheet.结束按钮对象的caption属性是否为“结束”，如果是，则将ActiveWindow对象调用ScrollColumn属性，设置滚动列至第一列;接着调用val判断ActiveSheet.翻页的Caption标题值是否12，是则让Caption属性变为0，滚动至第一列，并且跳转到 sub start（）;否则，ActiveSheet.翻页的Caption即为6，改情况下，就将Caption标题值加6，同时赋值给ActiveWindow.ScrollColumn，滚动列至此值。最后在Sub endtime（）中调用Application.OnTime Now + TimeValue（"00：00：00"）， "times"， ， False，不执行任何过程以此结束操作。

4 Excel查屏模块

4.1查屏模块论述

该查屏模块主要支持排课表数据导入、提供日课表查询、快捷打印等功能。主要使用EXCEL查找函数vlookup（）实现。查询显屏涉及三条件：日期、节次、实验室名、但因該函数查找条件唯一，故将三条件合而为一，数据关联表分别是：《显示屏》和《课表导入》。其中《显示屏》结构如图所示：

从图中可见实验室名称、节次、日期等表格字段的排列，本人在每列底下分别将对应得实验室及节次、日期进行合并形成唯一的查询参数，同时在《课表导入》首列同样将该表中相同三字段进行合并，这样形成两边关联的关键值。然后回到《显示屏》表中，对每个机房排版单元格写入或复制vlookup（）公式，例如某个日期2C509实验室1-2节课单元格的上课班级公式为：

4.2查屏公式论述

Vlookup（） 有三个参数：查找值、查找范围、查找模式。两张表需挑选出唯一关键字作为查找值，其中参数<查找值>位于写入公式的数据表、而<查找范围>位于关联的另一张数据表，同时注意<查找范围>所在表的<查找值>必须置于该表首列，最后查找模式选择<精确匹配>。根据导入的课表数据可自动生成供师生查看的机房安排表，还可根据日期自动查询当日的机房排课情况，设置自动打印区域方便快捷打印。

参考文献：

[1] 刘永富.Office VBA开发经典[M].北京：清华大学出版社，2019.

[2] 伍云辉.Excel VBA典型实例大全[M].北京：电子工业出版社，2008.

[3] 许小荣.Excel VBA语法与应用手册[M].北京：电子工业出版社，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与数据库[DB].[2010-08-06].https：//www.docin.com/p-69051033.html.

【通联编辑：王力】