| 标题 | 基于VBA+Excel函数的辅助排考模板的设计与实现 |
| 范文 | 马海军 祁淑梅 摘要：介绍一款基于Excel VBA+函数的排考模板，用VBA编程和函数以及Excel深度功能实现.其特点是在大家熟悉的办公软件基础上，做出一个辅助排考模板，该模板可在排考过程中进行科目、监考教师的冲突提示提醒，对排考情况进行汇总分析，大大提高排考效率，功能方便實用，可供教务排考人员参考。 关键词：Excel；VBA；辅助排考；模板；设计 中图分类号：TP393 文献标识码：A 文章编号：1009-3044（2018）27-0104-04 考试排考是每个学校期中期末必须做的一件事情，对于中小学这项工作往往由教务处负责，对于职业院校这项工作往往要各系的教学干事来完成，在实际操作中，我们发现考试排考最容易出现的就是漏掉考试科目和监考人员冲突这两大问题。对于有教务系统的学校来讲，现在的教务系统对于排考完全可以做到全自动操作，轻点鼠标几次，一个监考表就做出来了，但是排出来的结果实事求是的讲，要优化的地方太多，比如同一人在各场次监考的时间间隔，能不能连排、同一人监考的总场数、那些教师不能监考那些科目，谁和谁不能排在一起等等情况，可以说因素很多，让计算机完全自动地排出符合各方面要求的、经过优化的监考表，不是一件容易的事情，笔者认为需要综合利用人工智能、数学计算、大数据等方面的知识，目前教务系统下的排考系统肯定还做不到，这是其一；其二有些学校压根没有排考的系统，据调查在我区中小学拥有完备教务系统的学校真还不多，拥有排考系统的学校就更加少了。让一个本来就很忙的教务管理者或者教学干事手工排考肯定费时费力，效率不高，错误率高那是自然的。如何帮助教务工作者提高排考工作效率，降低排考错误率，笔者在排考模板的设计和实现上做了一些探索。 需要声明的是笔者不是专业的计算机软件开发人员，本次介绍的辅助排考模板不是开发一款庞大的、完全自动的专用排考软件，而是利用现有的大家都熟悉的办公软件为平台，做了一个实用的计算机辅助排考模板，是让计算机做一部分辅助工作，而不是全部工作。大家知道排考肯定得用Excel，用Word就有点勉为其难了。本模板是笔者在学校从事教务管理工作，在排考过程中逐渐摸索学习，结合VBA和Excel函数公式以及现有基本功能结合完成的。 1 本模板设计思路 本模板最终的目标是形成一张比较优化的监考表，其中不能出现漏考和冲突的问题，为了解决这类问题，模板的原始表格就是三张监考表空表，第一张为科目安排表，专门用来填写所有要考试的科目、班级/考场；第二张为监考教师安排表，专门用来安排监考教师；第三章为最终的监考表，是第一、二张表的汇总表，三张表考试时间、表头等信息公用，这三张表都设置了自动检查冲突和点选一键查询功能。 1） 根据人才培养方案，先将要考试的科目粘贴到第一个表科目安排表模板的科目场数汇总下的科目栏，然后将所有要考试的班级/教室信息粘贴到班级/教室栏，那些班考什么科目对于教务管理人员或者教学干事来讲，这是很清楚的，即便不清楚的话，可以从人才培养方案的电子版中直接粘贴过来，再者分散到系，每个班每学期开的课也不多，出错率不高，最后把要考的科目粘贴到对应的考试时间和班级/教室下。（图1） 2） 完成考试时间和班级/教室的安排后，就要安排对应的监考教师，只要将拟安排的教考教师名单粘贴到第二个表监考教师安排表模板的监考场数汇总下的监考教师栏，然后将监考教师复制粘贴到指定的要监考的位置。（图2）也可以随时切换到第三个表查看最终效果（图3）。 经过这两步，所有的安排就结束了，其中可以随意更换监考教师，监考排考情况数据一目了然，可以帮助安排人员调整监考安排。 2 设计的实现 2.1 一键查询的实现 只要鼠标选到那个单元格，那个单元格相同的部分都会突出显示，这个功能针对考试科目、监考教师、监考场数设置，可以清楚地看到该科目排考情况和教师的安排情况，对于三张表都适用，VBA代码如下： 释义：定义当前工作表活动单元格的名称为XM，然后设置条件格式，当单元格值等于XM时突出显示颜色，只要鼠标点到哪里，和鼠标所选当前单元格文字相同的其他单元格都会突出显示，一目了然。为了防止当单元格为空时，其他为空的单元格区域也出现突出显示的情况，必须定义当单元格值为0时，不设置单元格格式。 如图4所示。 2.2 右键功能重新定义 本模板设计简单，思路清晰，说起来很容易，但是实现起来却很费周折，为了提高效率，粘贴复制的操作必须至粘贴数值，不能粘贴所有格式，一旦粘贴所有格式，统计汇总、一键查询等都会因格式问题受到影响，为了解决这一问题，必须重新定义鼠标右键功能，VBA代码如下： 释义：名称为aaa的模块，模块的意思为选择性粘贴；只粘贴值；跳过空白单元格 ，不转置。 需要说明的是可以用鼠标右键粘贴、复制，也可以用键盘Ctrl+C，Ctrl+V粘贴复制，效果是一样的。粘贴的是数值，复制的也是数值，拖曳移动对格式没有影响。如果鼠标能设置组合键的话，粘贴复制很快的。 2.3 冲突检测，实时提醒的设置 本模板最大的特点就是冲突实时提醒，比如安排考试科目，同一个班级或者考场已经排过的考试科目，不能再次安排，一旦再次安排，立即突出显示；安排监考教师，同一时间段安排过一次，就不能再次安排，一旦安排，立即突出显示，这种突出显示只有在冲突解决后才会自动消失，这样一来就不会出现考试科目和监考教师的冲突。这是怎么做到的呢，其实利用的是条件格式，第一个表，科目每行都设置条件格式，当出现重复值，立即突出显示；同样，第二个表也设置条件格式，监考教师每列出现重复值，立即突出显示。需要注意的是，必须绝对引用。 2.4 跨表提示，实时提醒的实现 本模板原则上先在第一个表填班级或者考试教室，然后安排考试科目，再在第二个表安排监考教师，问题来了，第一个表中没有安排考试科目的时间段或者班级在第二个表中就不需要安排监考教师，换言之，只有第一个表中安排了考试科目对应的第二个表才可以安排监考教师，安排监考教师必须依附于已安排考试科目，这两者有一一对应的关系。我们利用条件格式和COUNTA函数来实现. 公式示例：（'1.排科目'！C8为科目表中的科目数据，对应于教师表中的C12） =COUNTA（'1.排科目'！C8）+COUNTA（C12）=1 设置单元格突出显示 =COUNTA（'1.排科目'！C8）+COUNTA（C12）=2 不設置单元格格式 条件格式类型：使用公式确定要设置格式的单元格 公式释义：判断第一个表C8单元格和第二个表C12单元格是否包含数据，若有一个为空则值等于1，满足条件突出显示；若两个单元格都包含数据，则值等于2，不设置格式，也就是说科目和监考教师都选了，无需强调。若两个单元格都无数据，则值等于0，由于没有定义，所以也默认不设置格式，也就是说科目和监考教师都没内容，自然属于正常情况，无需强调。这样一来，只要有考试科目的单元格，对应的监考教师单元格就会突出显示；没有考试科目的单元格，对应的监考教师单元格一旦安排了监考教师也会突出显示，表明有问题存在，若第二张表无突出显示提示，则说明第一张表所有科目的考试和班级都安排了监考教师，这样的设计可以达到提示提醒作用。 2.5 监考场数和科目场数自动汇总的实现 监考场数和科目场数自动汇总基于Excel函数和公式的应用，以Y5单元格（X5科目的安排场数）为例，公式示例如下： =IF（X5=0，""，COUNTIF（$C$1：$V$25，X5）） 释义：绝对引用，if函数，解决了两个问题：若科目名称（X5）为空，场数肯定为0，则不显示；若科目（X5）有名称，则触发计数函数COUNTIF，在指定区域$C$1：$V$25查询X5的个数。 2.6 监考排考数据汇总的实现 监考排考数据汇总可以帮助排考者合理排考，以免给个别教师安排的场数太多或者因个人疏忽失误等原因导致安排了监考教师名单以外的教师参与了监考，造成无人监考等情况的发生，并且突出显示安排场数最多的教师姓名，已明确排考的个别情况和整体情况。这些功能的实现主要用到的是函数和公式。如下示例： 是否有监考名单以外的教师参与排考 =IF（SUM（'2.排教师'！$Y$5：$Y$46，'2.排教师'！$AA$5：$AA$46，'2.排教师'！$AC$5：$AC$46）=COUNTA（'2.排教师'！$C$5：$V$46），"没有"，"有"） 释义：绝对引用，if函数，如果监考教师监考场数汇总值和监考表中教师的个数相等，则没有是否有监考名单以外的教师参与排考，否则，肯定有。这个功能的设计主要是对安排的监考教师进行二次检查纠错，确保排考和数据统计不出问题，这类问题常见于操作者没有从监考教师名单中复制教师姓名，直接在监考表中输入和监考教师名单不符的教师名称所致，直接输入正确，则不会影响。 安排场次最多的教师人数（本例中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函数，在三列监考教师的排考场数中找最大值的和，如果和大于1，说明最大值有两个以上，则显示最大值个数，否则最大值只有一个，显示为1 安排场次最多的教师突出显示（本例中AH2为最大值）应用条件格式突出显示实现： 规则类型：只为包含以下内容的单元格设置格式 单元格值=IF（$AH$2=0，FALSE，$AH$2） 监考教师名单人数 =COUNTA（'2.排教师'！$X$5：$X$46，'2.排教师'！$Z$5：$Z$46，'2.排教师'！$AB$5：$AB$46） 实际参与监考人数 =COUNTIF（'2.排教师'！Y5：Y46，">0"）+COUNTIF（'2.排教师'！AA5：AA46，">0"）+COUNTIF（'2.排教师'！AC5：AC46，">0"） 其他数据统计由于相对简单，不再赘述。 3 排考模板的稳定性安全措施 因为是排考的模板，用户只能对指定数据做粘贴、复制、删除、拖曳的操作，为了模板的稳定性不能也不允许更改模板的结构和一些不能修改的内容。 对于汇总的数据或者通过函数计算出来的数据以及一些公用数据选定后再设置工作表保护；为了防止人为删除模板下的三个工作表，设置工作簿保护窗体和结构；为了防止人为删除或者修改模板下VBA代码，设置VBAProject密码保护，通过各种保护确保模板稳定。 4 模板的使用局限性分析 本模板经过几个学校教学干事的使用，反响挺好，他们都觉得模板能够大大提高排考工作效率，分步排考能从根本上杜绝漏掉考试科目、漏监考教师以及监考教师安排冲突的现象发生，排考数据汇总能极大的地高排考的合理度，让排考者及时了解排考的整体情况等等。但作为辅助排考模板，它还存在着很大的局限性，比如不能根据班级考试学生人数进行考场的自动分配、不能设置条件自动将考试科目名单中的科目填充到科目表中、不能设置条件自动将教师名单中的教师填充到监考表中，这些工作都得排考人员手工完成。从某种意义上讲，模板升级的空间很小， 5 结束语 提出的基于VBA+ Excel函数公式的辅助排考模板的设计，对熟悉Excel办公环境的工作人员来讲，容易理解便于操作。实际操作过程中还需注意：格式、公式的相对引用和绝对引用，在粘贴公式和其他条件格式的时候一定要注意，还有条件格式所应用的范围。监考教师姓名、考试科目名称中的空格都应去除，防止因为空格不同造成数据检测错误，导致冲突检测不准确。 本文提及的是一种思路和方法，所设计和实现的模板仅用于辅助排考之用，当然还有其他办法也能够达到这样的目的，或许其他方法比本文提及的方法更加科学、简单、有效，希望本文能有抛砖引玉的作用。 参考文献： [1] Excel Home.别怕，Excel函数其实很简单[M].北京：人民邮电出版社，2016. [2] Excel Home.Excel 2010 VBA实战技巧精粹[M]. 北京：人民邮电出版社，2015. [3] 罗刚君.Excel VBA程序开发自学宝典[M]. 北京：电子工业出版社，2014. [通联编辑：光文玲] |
| 随便看 |
|
科学优质学术资源、百科知识分享平台，免费提供知识科普、生活经验分享、中外学术论文、各类范文、学术文献、教学资料、学术期刊、会议、报纸、杂志、工具书等各类资源检索、在线阅读和软件app下载服务。