函数公式在EXCEL中的排课应用

函数公式在Excel排课中的应用 【摘要】 在同一个工作簿中，包含了三个工作，即“总课表”、“班课表”、“教师课表”，总课表由手工编排，班课表和教师个人课表可以通过总课表自动生成，在这里，用到了函数“HLOOKUP”、 “IF”和逻辑运算符“&”，从而体现了电子表格对数据处理的强大功能。 【关键词】 函数——HLOOKUP，函数——IF、运算符——“&”，绝对引用（$）和相对引用。 “排课”是教导工作的一个重要环节，要排好课，特别是科学地安排好每一节课，是搞好学校教学工作的前提。可是对于一所九年制义务学校，既有小学又有初中，初中教师有上小学课的，小学教师也有上初中课的，而且小学和初中的上课时间不同步，初中第四节才是小学的第一节，这样的课程编排一般的排课软件是不能实现的，以前传统的手工排课工作量大，若一个环节出现问题，就会牵扯其他环节，有时甚至会前功尽弃。为此，就排课问题，我作了大量的尝试，在具体的操作演练过程中，我发现了采用Excel排课收到了事半功倍的效果。它有比手工排课不可取代的优点，Excel对于数据的处理有着强大的功能，使我从大量的重复的工作中得到了解脱。下面就具体的操作过程作系统的浅谈，希望能与大家共勉。   首先，启动Excel2003，新建一个电子表格工作簿，然后将工作表“sheet1、sheet2和sheet3”，分别命名为“总课表”、“班课表”和“教师课表”，最后保存一下，在文件名一栏中输入文件的名称为“课程表”，然后在保存位置选择保存文件的路径（如D盘），点击“保存”按钮即可。 一、建立总课表 打开“课程表”工作簿，在“总课表”工作表中建立一张空白总课表，并将各班级的课程内容输入到相应的单元中。总课表中的A列代节次，每一节课由3部分组成，即课程、教师、班级代码，具体的说，教师所在单元格的上一单元格就是该教师所任的课程，下一单元格就是该教师所任课程的班级，如下图所示。 二、班级课程表的设置 切换到“班课表”中，制作一张空白班级课程表，并设置好字体、字号、添加边框等。（如图2所示）。 1、在B2单元格建立班级下拉列表，当我们查询某班的课程时，只要点击一下B2单元格，这时就会在B2单元格的右下角出现符号“”，点击此符号就会弹出一个下拉列表，选择列表中的班级代码，即可显示该班的课程。下拉列表如何做成的呢？下面就跟我来一起尝试一下吧。 执行“数据→有效性”命令，打开“数据有效性”对话框(如下图)，在“设置”标签下，按“允许”右侧的下拉按钮，在随后弹出的快捷菜单中，选择“序列”选项，然后在下面“来源”方框中输入各班级的代码(如“101，201，202，301，302…”等)，确定返回。注意：各代码之间请用英文状态下的逗号隔开。这样，班级下拉列表就制作好了。 接下来的工作就是如何从“总课表”中提取各班级一星期的的课程啦。具体操作步骤如下： 1、在班课表的B4单元格（即星期一的第一节课程所在的单元格）中输入公式“=HLOOKUP($B$2,总课表!$B$2:$S$20,2,FALSE)”。公式的含义是：根据班课表B2单元格中的内容（班级名称），在 “总课表”B2至S20单元格区域（存放星期一所有班级课程内容的区域）查找，找到后返回对应的第2行（用数字2表示）对应单元格的内容（即星期一第一节课程）。同样在C4、D4、E4、F4单元格中分别输入相同的公式，只要把B4单元格公式中的“$B$2:$S$20”分别改为“$T$2:$AK$20”、“$AL$2:$BC$20”、“$BD$2:$BU$20”、“$BV$2:$CM$20”就行了（即分别为星期一至星期五所在的区域）。这里所改动的公式实际上就是改变了所引用的总课表中的区域。比如，星期一和星期二各班的课程都不一样，所以存放星期一和星期二课程的区域就会发生改变。 2、同时选中班课表的B4、 C4、D4、E4、F4单元格，用复制柄向下复制到最后一节课F15，现在将B5：B15、C5：C15、D5：D15、E5：E15、F5：F15单元格区域内公式中末尾的“2” 按照从上到下的顺序分别改为3、5、6、8、9、11、12、14、15、17、18、20就可以了（其中的数字3、6、9、12、15、18、为总课表的科目所在的行，5、8、11、14、17、20为总课表班级所在的行）。这样，一张完整的班课表就诞生了。如果要我们查看某班级的课程，就点击B2单元格，在下拉列表中选择相应班级的代码，该班级的课程表就展现在我们眼前了。 三、教师课表的设置 切换到“教师课表”中，制作一张空白个人课表，并设置好字体、字号、添加边框等。（如图下所示）。 同样在B2单元格中建立全校教师的下拉列表（便于查询和打印教师的课程表）。接下来就让我们一起来体验其中的奥妙吧！ 1、建立一个助表（帮助生成教师个人课表）。将总课表工作表的内容全部复制，粘贴在一张空白工作表中，建立一个副本，将副本命名为“助表”。然后除A列及第1和第2行外，清除其它列、行的所有内容，如图所示 ： 2、在B3、B4、B5单元格中分别输入公式“=IF(总课表!B4=教师课表!$B$2,总课表!B3,"")”、“=IF(总课表!B5=教师课表!$B$2,总课表!B4,"")”、“=IF(总课表!B4=教师课表!$B$2,总课表!B5,"")”。其公式（B3单元格中的公式）的含义是“如果总课表中B4单元格中的内容与教师个人课表B2单元格中的内容相同，则显示总课表B3单元格的内容”。同时选中助表B3、B4、B5单元格，用复制柄填充至本列第后一节课的的位置（在本例中为B20）时松开鼠标，不要点击其它单元格，再次按住填充柄向右复制完整张助表的表格区域（在本例中到CM20），这样，教师工作表中B2单元格中的这位教师的课程就被从总课表中提取出来分别放到了与总课表位置相同的助表的相应单元格中。现在我们只要把这些助表中的零乱的课程放到教师个人课表的指定位置就行了，如何才能实现这个愿望呢？请接着往下看吧。 3、分别删除“助表”中的第4、7、10、13、16、19行（即教师所在行，如上图阴影行就是要删除的行）。 4、切换到“教师课表”工作表中来， 一个教师的某一节课是不应该发生冲突的，就拿星期一的第一节课程来说吧，我们只需要将星期一各个班的第一节课程所在的位置合并到教师课表中星期一的第一节课程的位置就可以了。 （1）在教师个人课表的B4、中输入公式“=助表!B3&助表!C3&助表!D3&助表!E3&助表!F3&助表!G3&助表!H3&助表!I3&助表!J3&助表!K3&助表!L3&助表!M3&助表!N3&助表!O3&助表!P3&助表!Q3&助表! R3&助表!S3” （即把总课表中星期一所有班级的第一节课程位置放在教师个人课表中星期一的第一节课程的位置） （2）在C4中输入公式“=助表!T3&助表!U3&助表!V3&助表!W3&助表!X3&助表!Y3&助表!Z3&助表!AA3&助表!AB3&助表!AC3&助表!AD3&助表!AE3&助表!AF3&助表!AG3&助表!AH3&助表!AI3&3助表!AJ3& 助表! AK3” （即把总课表中星期二所有班级的第一节课程位置放在教师个人课表中星期二的第一节课程的位置） （3）在D4中输入公式“=助表!AL3&助表!AM3&助表!AN3&助表!AO3&助表!AP3&助表!AQ3&助表!AR3&助表!AS3&助表!AT3&助表!AU3&助表!AV3&助表!AW3&助表!AX3&助表!AY3&助表!AZ3&助表! BA3&助表! BB3&助表!BC3” （即把总课表中星期三所有班级的第一节课程位置放在教师个人课表中星期三的第一节课程的位置） （4）在E4中输入公式“=助表!BD3&助表!BE3&助表!BF3&助表!BG3&助表!BH3&助表!BI3&助表!BJ3&助表!BK3&助表!BL3&助表!BM3&助表!BN3&助表!BO3&助表!BP3&助表!BQ3&助表!BR3&助表! BS3&助表! BT3&助表!BU3” （即把总课表中星期四所有班级的第一节课程位置放在教师个人课表中星期四的第一节课程的位置） （5）在F4中输入公式“=助表!BV3&助表!BW3&助表!BX3&助表!BY3&助表!BZ3&助表!CA3&助表!CB3&助表!CC3&助表!CD3&助表!CE3&助表!CF3&助表!CG3&助表!CH3&助表!CI3&助表!CJ3&助表! CK3&助表! CL3&助表!CM3” （即把总课表中星期五所有班级的第一节课程位置放在教师个人课表中星期五的第一节课程的位置） 5、同时选中B4：F4单元格区域（教师个人课表中星期一至星期五第一节课程区域），用复制柄向下填充至教师个人课表最后一节F15（在本例中到总课表的CM20），这样某个老师一星期的所有课程就放到了课表中的指定位置了。 现在只要我们点击教师课表工作表中的B2单元格，选择一位教师，该教师的课程表就马上呈现在我们眼前了。 如果我们要在总课表中增加班级的课程，只需把公式作相应的修改即可。具体修改的方法如下： 1、修改班课表星期一至星期五中各自对总课表的引用区域，可以用查找和替换功能进行修改。 2、将上图“助表”中的B3：B20单元格区域向右重新复制一遍。 3、分别将教师课表星期一至星期五的第一节课程中的公式增加合并区域，重新向下复制一遍即可。 另外，采用这种半自动化的Excel排课方式为今后的工作带来极大的方便，当我们在总课表中任意修改课程内容时，相应的班级课表和教师个人课表的内容也随之变动，从而避免了以前那些不厌其烦的重复工作。在以后排课中，只要将总课表的内容稍作修改就可以自动生成新的课程表。为今后的工作带来了很大的方便，既经济，又实惠。