浅谈Excel函数公式在中小学排课中的应用.docx

1、浅谈Excel函数公式在中小学排课中的应用2014年市现代教育技术参评论文浅谈Excel函数公式在中小学排课中的应用【摘要】在中小学教导处工作中，排课是很重要的一项工作。当我们手工编排好总课表后，还需要从总课表分离出班级课表和教师个人课表，这是一项繁琐且非常容易出错的事。运用EXCEL电子表格函数的强大功能，可以轻松的实现从总课表中提取出班级课表和教师个人课表。而且当总课表有所调整后，班级课表和教师个人课表能及时更新，大大提高了排课的工作效率。【关键词】Excel函数公式，中小学排课，课程表中小学教导处工作中非常重要的一项任务便是排课。要排好课，特别是科学地安排好每一节课，是搞好学校教学工作的

2、前提。可是对于一所九年一贯制的学校，既有小学又有初中，初中教师有兼上小学的课，小学教师也兼有上初中的课，而且小学和初中的上课时间不同步，初中第二节才是小学的第一节，这样的课程编排一般的排课软件是不能实现的。传统的手工排课工作量大，若一个环节出现问题，就会牵扯其他环节，有时甚至会前功尽弃。为此，就排课问题，我作了大量的尝试，在具体的操作演练过程中，我发现了采用Excel排课收到了事半功倍的效果。它有比手工排课不可取代的优点，Excel对于数据的处理有着强大的功能，使我从大量的重复的工作中得到了解脱。下面就具体的操作过程作系统的浅谈，希望能与大家共勉。首先，启动Excel 2010，新建一个电子表

3、格工作簿，然后将工作表“sheet1、sheet2和sheet3”，分别重命名为“总课表”、“班级课表”和“教师个人课表”，最后保存。一、建立总课表 打开“课程表”工作簿，在“总课表”工作表中建立一张空白总课表，并将各班级的课程内容和任课教师姓名输入到相应的单元格中。总课表中的A列代表午别，B列代表节次，由于初中一天7节课，小学一天6节课，在此统一设置成7节课。表示每节课的单元格由2部分组成，即上部分是教师姓名、下部分是课程名称。如图1所示。图1二、班级课表的设置切换到“班级课表”中，制作一张空白班级课程表，并设置好行高、列宽、字体、字号、添加边框等。（如图2所示）。图21、在B2单元格建立班

4、级下拉列表，当我们查询某班的课程时，只要点击一下B2单元格，这时就会在B2单元格的右下角出现下拉符号，点击此符号就会弹出一个下拉列表，选择列表中的班级，即可显示该班的课程。制作该下拉列表的方法如下：选中B2单元格，点击“数据数据有效性”命令，打开“数据有效性”对话框(如图3)，在“设置”标签下，按“允许”右侧的下拉按钮，在弹出的快捷菜单中，选择“序列”选项，然后在下面“来源”方框中输入各班级的代码，如一(1)班,二(1)班,三(1)班,四(1)班,五(1)班,六(1)班,七(1)班,八(1)班,九(1)班。注意：各班级之间要用英文状态下的逗号隔开。也可以输入：“=总课表!C3:K3”，以此来引

5、用总课表中的C3至K3单元格。至此，班级下拉列表制作完成。图3接下来的工作就是如何从“总课表”中提取各班级一星期的课程。具体操作步骤如下：在班级课表的C4单元格（即星期一第一节课所在的单元格）中输入公式：=HLOOKUP(B2,总课表!C3:K17,3,FALSE)。公式的含义是：根据班级课表B2单元格中的内容（班级名称），在总课表C3至K17单元格区域（即星期一所有班级的课程内容的区域）查找C4单元格中输入的班级，找到后返回对应的第3行（用数字3表示）对应单元格的内容（即该班星期一第一节的课程）。公式中的FALSE表示查找时要精确匹配。要查找该班级星期二第一节的课程，只要将引用的单元格区域替

6、换成“总课表！L3至T17”，即在D4单元格输入公式：=HLOOKUP(B2,总课表!L3:T17,3,FALSE)。依此类推，星期三的第一节课，即在E4单元格输入公式：=HLOOKUP(B2,总课表!U3:AC17,3,FALSE)；星期四的第一节课，即在F4单元格输入公式：=HLOOKUP(B2,总课表!AD3:AL17,3,FALSE)；星期五的第一节课，即在G4单元格输入公式：=HLOOKUP(B2,总课表!AM3:AU17,3,FALSE)。同样在C5单元格（即星期一的第二节课所在的单元格）中输入公式“=HLOOKUP(B2,总课表!C3:K17,5,FALSE)”，即将原公式中的3

7、改成5，即返回了该班级的星期一的第二节课。同样第三节课，只要将该数字改成7，第四节课只要将该数字改成9。依此类推。同样：星期二的第二节课，即在D5单元格输入公式：=HLOOKUP(B2,总课表!L3:T17,5,FALSE)；星期三的第二节课，即在E5单元格输入公式：=HLOOKUP(B2,总课表!U3:AC17,5,FALSE)；星期四的第二节课，即在F5单元格输入公式：=HLOOKUP(B2,总课表!AD3:AL17,5,FALSE)；星期五的第二节课，即在G5单元格输入公式：=HLOOKUP(B2,总课表!AM3:AU17,5,FALSE)。第三节课至第七节课，依此类推。至此，从总课表中

8、提取班级课表已全部完成，如图4。图4三、教师个人课表的设置切换到“教师个人课表”中，制作一张空白个人课表，并设置好字体、字号、行高、列宽、添加边框等。（如图5所示）。图5为了便于查询和打印教师个人的课程表，同样可以利用数据有效性在B2单元格中建立全校教师的下拉列表。具体步骤参照上文中班级课表下拉菜单的制作。在此例中，数据来源中应输入全部教师的姓名，如：孔丽珠,陈云雷,方立君,孔文华,蔡亚丽,李琪,李志明,廖光查,卢庆斌,牟纪连,黄旭凯,牟良良,钱成丹,徐妙法,杨青青,叶传淼,叶康生,叶士福,吴乐娜,张志国。中间也需用英文半角的“,”隔开。如图6图6在C4单元格（某教师星期一的第一节课）输入公式

9、：=IF(COUNTIF(总课表!C4:K5,B2)=1,HLOOKUP(B2,IF(1;0,总课表!C4:K5,总课表!C3:K3),2,0)&HLOOKUP(B2,总课表!C4:K5,2,0),IF(COUNTIF(总课表!C4:K5,B2)1,出错,)。该公式的含义解释如下：1、IF函数可执行真假值判断，根据逻辑计算的真假值，返回不同结果。语法为IF(logical_test,value_if_true,value_if_false)，Logical_test表示计算结果为TRUE或FALSE的任意值或表达式，Value_if_true logical_test为TRUE时返回的值，Va

10、lue_if_false logical_test 为FALSE时返回的值。在本例中需对B2单元格输入的教师在“总课表!C4:K5”（即所有班级星期一第一节课的单元格区域）的查找计数进行判断，如果计数等于1，则需继续查找该节课程和任课班级；如果大于1，则表示该教师在本节课因任教两个班级而造成冲突，需提示出错；如果是0，则表示该教师在本节是没有课的，需在本节课的单元格C4内显示为空。2、COUNTIF(总课表!C4:K5,B2)=1，对本工作表B2单元格输入的教师在“总课表!C4:K5”（即所有班级星期一第一节课的区域）的进行查找计数。如果是1，则表示该教师在本节有课；如果大于1，则表示该教师在

11、本节课需任教两个以上班级；如果是0，则表示该教师在本节是没有课。3、HLOOKUP(B2,总课表!C4:K5,2,0)，该公式的含义是在“总课表!C4:K5”（即全部班级星期一第一节）中查找B2单元格输入的教师姓名，查找到后返回该教师对应的往下第二行的内容，即该教师该节的课程。数字0表示精确匹配，与上文中的FALSE参数为同一含义。4、IF(1;0,总课表!C4:K5,总课表!C3:K3)，由于HLOOKUP函数只能实现从上往下查找，所以在本例中需将查找范围的数组进行一下变换。该公式是将“总课表!C4:K5”和“总课表!C3:K3”重新组合成一个新的两行多列的数组，该公式返回的内容是：（如图7

12、）图7该数组第一行数据来源于“总课表!C4:K5”，第二行数据来源于“总课表!C3:K3”。5、HLOOKUP(B2,IF(1;0,总课表!C4:K5,总课表!C3:K3),2,0)，该公式的含义是在“IF(1;0,总课表!C4:K5,总课表!C3:K3)”组成的数组中查找B2单元格输入的教师姓名，查找到后返回该教师对应的往下第二行的内容，即该教师本节课所任教的班级。6、HLOOKUP(B2,IF(1;0,总课表!C4:K5,总课表!C3:K3),2,0)&HLOOKUP(B2,总课表!C4:K5,2,0)，“&”符号是EXCEL中的连接符，在本例中利用该符号可以将前后两个HLOOKUP查得的

13、结果连接起来。基于以上公式原理，在星期一的第二节课，即C5单元格，可以输入“=IF(COUNTIF(总课表!C6:K7,B2)=1,HLOOKUP(B2,IF(1;0,总课表!C6:K7,总课表!C3:K3),2,0)&HLOOKUP(B2,总课表!C6:K7,2,0),IF(COUNTIF(总课表!C6:K7,B2)1,出错,)”，与C4单元格的公式的不同之处就是将查找的范围由原来的“总课表!C4:K5”替换成“总课表!C6:K7”，即替换成了表示全部班级星期一第二节的单元格区域。依此类推，星期一的第三节课，即C6单元格，可以输入“=IF(COUNTIF(总课表!C8:K9,B2)=1,HL

14、OOKUP(B2,IF(1;0,总课表!C8:K9,总课表!C3:K3),2,0)&HLOOKUP(B2,总课表!C8:K9,2,0),IF(COUNTIF(总课表!C8:K9,B2)1,出错,)”；星期二的第一节课，即D4单元格，可以输入“=IF(COUNTIF(总课表!L4:T5,B2)=1,HLOOKUP(B2,IF(1;0,总课表!L4:T5,总课表!C3:K3),2,0)&HLOOKUP(B2,总课表!L4:T5,2,0),IF(COUNTIF(总课表!L4:T5,B2)1,出错,)”；星期二的第二节课，即D5单元格，可以输入“=IF(COUNTIF(总课表!L6:T7,B2)=1,

15、HLOOKUP(B2,IF(1;0,总课表!L6:T7,总课表!C3:K3),2,0)&HLOOKUP(B2,总课表!L6:T7,2,0),IF(COUNTIF(总课表!L6:T7,B2)1,出错,)”。待课表中全部单元格填充完毕后，只要我们点击教师个人课表中的B2单元格，选择一位教师，该教师的课表就马上就能呈现出来了。如图8图8四、调试与运用1、假如总课表中出现杨青青老师重复的功课，如图9，则该教师个人课表中该节课将会提示出错，如图10。图9图102、如果需要对部分教师的功课进行调动，只要在总课表作出修改就行了。班级课表与教师个人课表会自动更新。3、排总课表时可以实时查看班级课表与教师个人课表，对班级课程或者教师个人课程过于集中可以及时在总课表里进行调整。4、如需查看某位老师的日课表，只需教师个人课表中选择该位教师即可查看，避免了手动翻看纸质课表带来的大量麻烦。采用这种半自动化的Excel排课方式后，为教导处的排课工作带来极大的方便。Excel函数公式还有更为强大的功能等待着我们去开发。本文仅使用了Excel最为皮毛的公式应用，假如能更加细致地开发应用，必将为今后的工作带来了很大的方便，既经济，又实惠。【参考文献】1 韩小良，excel企业管理应用案例精萃，电子工业出版社，20072 李洋， excel函数、图表与数据分析应用实例，清华大学出版社，2007