用Excel五步完成课程表编制

用Excel五步完成课程表编制知识点：利用Excel数据关联和规则公式完成指定数据排序应用环境：学校课程表编排、企业员工任务、值班安排等每个学期，学校教务处教师最头疼的一件工作就是编制课程表。一般学校的课程表至少包括给领导的全校总课程表、学生的班级课程表和教师用的课程表三种。三种课程表数据密切相关，修改任何一张课程表都将影响到另两张表格，因此编辑时很难兼顾。但是来自福建的陈老师就利用Excel2007轻松解决了这个问题，下面我们来看看他是怎么做的。1.创建工作表打开Excel2007新建一张“教师安排”工作表，存放各班的科任教师安排(图1)，这是编课程表前必须安排好的。再建立总课程表、班级课程总表、教师课程总表等。2.总课程表编制切换到“总课程表”工作表设计总课程表格，表中包括全部班级的课程安排和每节课的教师。选中B4，单击“数据”选项卡的“数据有效性”图标，在“数据有效性”窗口的允许下拉列表中选择“序列”，输入来源为“=教师安排!$A$3:$A$16”(不含引号)，确定完成设置。在B5输入公式=IFERROR(VLOOKUP(B4，教师安排!$A:$M，ROW(B4)/2，FALSE)，“”)，并设置填充色为浅蓝作为与学科行的区分。然后选中B4:B5进行复制，再选中B4:AJ27区域进行粘贴即可(图2)。现在选中B4单击下拉按钮选择学科，下面的B5单元格就会自动显示上课的教师名，其他单元格也是一样(如图2)。如此一来安排课程就简单多了吧，只要用鼠标单击选择即可。图1图23.总课程表限制提醒编制总课程表时总有各种附加条件限制，比如：一个教师不能同时上两班的同一节课，操场太小全校只能有两班同时上体育课，电脑室只有1间不能有两班同时上电脑课等等。要在排课中兼顾这些要求显然不容易。对此可设置条件格式，让它在违反限制时自动变色提示，事情就简单多了。选中B4:AJ27，在“开始”选项卡中单击“条件格式”选择“新建规则”，在“新建规则”窗口中选择规格类型为“使用公式确定要设置格式的单元格”，并输入公式=AND(COUNTIF(B:B，B4)1，MOD(ROW()，2)=1)(图3)。再单击“格式”按钮，在弹出窗口中设置字体颜色为红色。确定后，当同一节课中有两班出现同一老师同时，两班中这位老师的名字都会变成红色，你可以及时决定看要更换哪班的课程。图3同样再选中B4:AJ27设置“条件格式”，但输入的公式改成=AND(COUNTIF(B:B，B4)2，C1=“体育”)，文字颜色改成绿色。即可在同时上体育课超过2班时变成绿色。电脑课的设置公式则是=AND(COUNTIF(B:B，B4)1，C1=“电脑”)颜色改成蓝色。如果你还有课时等其他限制要求只要像这样继续叠加设置条件格式即可。注：必须从B4拖动到AJ27选中B4:AJ27，或先选中B4再按住Shift键单击AJ27进行选中才行。若你从AJ27拖动到B4选中，虽然选中区域相同但条件格式的公式就不同了，得把公式中的B全部改成AJ，B4改成AJ27。4.分离教师、班级课程再来就是要从总课程表中分离出班级、教师的课程了。切换到“班级课程总表”，在第1行输入表格列标题班级、周一1、周一2……周五5、周五7，在A2输入公式=OFFSET(总课程表!A$2，ROW()*2-2，)(图4)，选中A2复制再选中A2:AJ13进行粘贴，即可看到各班的课程总表。在“教师课程总表”工作表中同样设计好表格的行列标题，在B2输入公式=IFERROR(INDEX(总课程表!$A:$A，MATCH($A2，总课程表!B:B，0)-1)，“”)，选中B2复制再选中B2:AJ44进行粘贴(共43名教师)，即可自动显示所有老师的课程安排(图图4图55.用Word合并出课程表先把编制好的Excel工作簿保存为“课程表.XLSX”。打开Word2007，设计好一张课程表格。单击“邮件”选项卡的“选择收件人”选择“使用现有列表”，选择打开“课程表.XLSX”，在“选择表格”窗口中选择“班级课程总表$”。把输入光标定位标题的班级处，单击“插入合并域”选择“班级”，重复这操作在各单元格逐一插入对应的周一1、周一2……周五7合并域。在表格下回车插入一空行，再单击“规则”选择“下一记录”。选中课程表格和下面的“下一记录”进行复制，按班数粘贴出所有班级课程表(图6)，现在单击一下“预览结果”即可显示所有班级的课程表，再分栏排版一下就可以直接打印了。最后把文档保存为“班级课程表.DOCX”。同样制作一个“教师课程表.DOCX”文件，只是在“选择表格”窗口中要改选“教师课程总表$”，其他操作都一样就不细说了。图6OK，到此算是大功告成了。以后再需要编辑课程表你只要在总表中逐格单击选择学科完成总课程表的编制。排课完成后先保存“课程表.XLSX”，再打开“班级课程表.DOCX”和“教师课程表.DOCX”文件，当提示数据将被放入文档选择是否继续时，请单击“是”即可看到最新的课程表，你直接打印出来就行了。