|
Excel——让排课更省心 易礼云 Excel——让排课更省心 I 前言 课程编制是教务处每学期一项令人头疼的工作，复杂而繁琐， 时间紧任务重。其中必须要解决的问题是课程冲突，否则教学工 作无法正常开展。最难解决的问题是课程编排的合理性，因为对 不同学校，不同的教师，对'合理'的定义是不一样的，一个理 想的课程表应该是能满足学校、教师的大部分'合理'要求。排 课时要考虑如下因素：教学资源情况，如有几个微机室？几个音 乐室？一节课同时能容纳几个班上体育课？；学科分布及均衡性， 毕竟还是要讲究科学性的。 以前老师们用原始的方法手工编排：一般是先在电脑上制作 并打印出一张总课表的空表，再打印一张教师工作安排表用来对 照，然后在空白课表上写写画画，期间，还要不断的调整，一支 铅笔涂来擦去。这还不算完，在排好总课表后,还要从总课表中提 取班级课表和教师个人课表,工作量大不说,而且容易出错。排课 的同时还要在旁边统计课程数据，如 1 班的语文排了几节？还差 几节？3 班的数学排了„„，伏案数小时才可收工，费时费力； 现在虽有专业的排课软件，但用软件排课基本不能令人满意：普 遍存在排课条件设置复杂，难以操作等缺点，最重要的是排出的 课合理性差。 若您没有排课软件，又不想回到从前，那么 excel 便是您最 佳的选择。excel 的强大威力来自于它的公式，条件格式和数据有 效性。本人在这方面作了一些尝试，设计了一个简单实用的排课 Excel——让排课更省心 II 模板，用人的思维（手工排课）加上电脑的'监控'（教师、教 室冲突检查），使排课变得更简单、省心，工作量降低不说，效 率大为提高。排好课后，教师个人课表、班级课表就自动生成了， 直接打印出来就可分发给老师和班主任，如果课程还有变动，教 师个人课表和班级课表也会自动跟踪，充分显示出 Excel 的智能 性，现写出来供大家参考。 简要说明：我用的 Excel 版本是 2010 版，读者要看懂这本小 册子，需要具备一些 Excel 的基本知识，并应当了解 Excel 的公式 与函数基础才能跟上我的节奏。为了让读者真正从中受益，我会 在后面的文字中，凡用到公式的地方都会有一些简短的说明，当 然也包括一些 Excel 技巧。就是说，当您看完这本小册子的时候， 不仅仅是在排课方面有收获，我更希望您的 Excel 水平有提高， 这才是我写这些文字的初衷。所有的截图都是用 QQ 聊天软件的 截图功能制作。 我们学校是一所全日制县级初中，原来三个年级分别都有 16 个教学班，虽然现在班级减少了，但我的这个课表仍然没有删除 多出来的班级，如果觉得碍眼，可以将多余的班级隐藏，并不影 响表格的运行。当然如果您所在的学校班级与我这个表差别太多， 也可以将多余的班级删除，不过，删除后公式会作一些调整来适 应新表，才能使表格不发生错误。目前我校的规模是：七年级 14 个班，八年级 13 个班，九年级 15 个班。按我校的惯例及教育局 规定，每周工作 5 天，每天上、下午各 4 节课，但周五的下午只 有 2 节，全周共 38 节课，每周一上午的第一节课全校均为班会 课，本模板的设计不包含早读课及晚自习。 Excel——让排课更省心 III 目录 前言 .............................................................................................. I 目录 ............................................................................................. 3 第 1 章 工作安排 ................................................................. 1 1.1 概述 ................................................................................ 1 1.2 工作安排 ........................................................................ 2 第 2 章 总课表 ..................................................................... 5 1.1 排课区 ............................................................................ 5 1.2 任课教师区 .................................................................... 6 1.3 统计区 ............................................................................ 8 第 3 章 教师课表 ............................................................... 11 3.1 教师个人课表 .............................................................. 11 3.2 制作多个教师个人课表 .............................................. 15 Excel——让排课更省心 IV 第 4 章 班课表 ................................................................... 17 第 5 章 年级课表 ............................................................... 21 第 6 章 教师课表数据 ........................................................ 23 1.1 主文档的制作 ............................................................... 23 1.2 课程名称 ....................................................................... 24 1.3 任课班级 ....................................................................... 26 1.4 邮件合并 ....................................................................... 27 第 7 章 班课表数据............................................................ 31 1.1 课程名称 ....................................................................... 31 1.2 任课教师姓名 ............................................................... 32 附 1：截图目录 ......................................................................... 35 附 2：公式目录 ......................................................................... 37 附 3 EXCEL 常用技巧集 .............................................................. 41 1.3 移动及选定单元格 ....................................................... 41 Excel——让排课更省心 V 1.4 复制和粘贴 .................................................................. 43 1.5 数据录入 ...................................................................... 44 1.6 数据排序 ...................................................................... 45 1.7 表格打印 ...................................................................... 46 1 第1章 工作安排 1.1 概述 我将整个课表制作成一个工作薄文件（xlsx 文件），它包含的 工作表数目由您的需要来定，我这里包含 7 个工作表（图 1-1）。 课表制作好后，删除工作安排里的数据（注意只删除数据，不删 除格式）及总课表里的排课数据，另存为一个模板文件（xltx 文 件），以后就可以在它的基础上导入工作安排，然后再进行人工 排课，其它的就会自动生成，是不是觉得很方便呢？ 图 1-1 工作表列表 首先，新建一个 excel 工作薄，默认有 3 个工作表，再插入 4 个表，共 7 张工作表，分别命名为：安排，总课表，教师课表， 班课表、年级课表、教师课表数据及班课表数据（图 1-1）。每个 表的制作及作用，后面会分别进行说明。这样准备工作就算完成 了，下面依顺序进行简要说明。 '安排'表是学校对老师的工作安排，从这个表里可以看出 哪个老师上哪些班的什么课，这是制作这个工作薄的基础，是由 开学前行政会决定的。在它的基础上，我们来制作全校'总课表'， 这个靠的是教务人员的智慧，表格只能作一些辅助，比如出现课 Excel——让排课更省心 2 程冲突时用颜色显示（提醒），课程排得合理与否，这个表是关 键。有了'总课表'，其它表就是用 Excel 的函数来抽取总课表中 的信息并按要求制作成您希望的格式，比如教师课表、班课表以 及年级课表（因为我们学校班级较多，所以分年级制作课表；您 可视情况而定）。最右边两个表则是为了批量打印而制作的，主 要是利用 Word 的邮件合并功能，在 Word 里将教师课程表和班 级课程表设计好，利用'邮件合并'功能，提取该工作中的数据， 最后合并成一个完全的 Word 文件，便于批量打印；如果您校的 班级少，这两个表就不必制作了，直接在该工作薄的'教师课表' 和'班课表'中一张一张地打印即可。下面就先从第一张表说起。 1.2 工作安排 参照图 1-2 制作表格并输入您校老师的任课情况。 图 1-2'安排'表 第 2 行标注颜色是为了区分不同的年级，715、716 是空白（我 第 1 章 工作安排 3 校今年七年级只有 14 个班，多余的班级编号未删除），您可以不 用理会，当然也可以将其隐藏。为了能一眼就看出哪些老师是跨 年级上课，哪些老师是跨学科任课，需要在 Excel 的'条件格式' 里进行设置，具体方法如下： 选中$B$3:$Q$17，在'开始'选项里，单击'样式'组里的 '条件格式→新建规则'，在弹出来的'新建格式规则'对话框 里选择最后一项'使用公式确定要设置格式的单元格'，然后输 入公式： =COUNTIF($R$3:$AW$17,B3) 设置好格式后点确定（我设置的单元格格式是'单元格背景 填充，颜色是橙黄色'，您可以根据自己的喜好进行设置）。公式 说明：COUNTIF 函数用于条件计数，第一个参数是条件范围，第 二个参数是条件。该公式的第一参数$R$3:$AW$17 是八、九年级 的范围，意思是：在八、九年级范围内，统计七年级范围内的任 课教师姓名，如果不为 0，则表示跨年级任课，应用格式，相应 单元格显示有背景色；如果为 0 则条件不满足，正常显示。 用类似的方法设置跨学科的任课老师，方法是：选中 $B$3:$AW$16，在条件格式的公式里输入如下公式： =COUNTIF($B4:$AW$17,B3) 同样设置好格式后点确定（我设置的单元格格式是'单元格 背景填充，颜色是绿色'，您可以根据自己的喜好进行设置）。公 Excel——让排课更省心 4 式说明：该公式的第一参数$B4:$AW$17 是除（数据区）第 1 行 （事实上是表格的第 3 行）外的其它所有行，特别要注意是混合 引用，这样就可以统计出以下各行中是否包含有第 1 行的任课老 师（不同的行，意味着不同的学科，参看第 1 列），如果有，则 表示跨学科任教（跨行就是跨学科），应用条件格式（单元格背 景显示绿色）；如果统计结果为 0，则表示不符合条件，单元格正 常显示。这样，这个表格就算完成了，为了使以后的一些统计（如 绩效）更方便，我还在这个表格的其余部分输入了一些公式，当 然这个不是必须的。如我在表格的下面 A20 单元格里输入了一个 统计任课教师数（不重复）的公式 {=SUM(1/COUNTIF(teachers,teachers&''))-1} 这是一个数组公式，公式输完后要按 Ctrl+Shift+Enter 三个键， 其中的花括号是自动生成的，手工输入无效，teachers 是我为这 个表的数据区定义的名称，=$B$3:$AW$17。另外还在 BA2 单元格 里输入公式 =OFFSET($B$3,INT((ROW()-2)/48),MOD(ROW()-2,48)) 将这个公式复制到 BA721 并将其转化成数值（复制→选择性 粘贴→数值），然后在'数据'选项里，单击'数据工具'里的 '删除重复项'工具，留下唯一值，再将 0 值删除，就得到了任 课教师的不重复列表。按上面的方法，也可以分别列出七、八、 九三个年级的任课教师不重复列表。 5 第2章 总课表 本章介绍'总课表'的编制，表格分成三个区：一是排课区 域（$A$2:$AX$41），其右侧是任课教师姓名区域（$AY$3:$CT$41）， 而下边则是统计区（$B$42:$AX$59）。排课区用于安排课程，每 当排了一节课，任课教师区就会显示该节课的任课教师（用公式 来设定），这两个区要一一对应，就是说，排课区多大，任课教 师区也是多大，为防止出错，也在该区域上面写上对应的班级。 下面的统计区用于统计各班、各学科已经排了几节课，是否已排 满，或者是否已超过应当安排的节数，总课时是否已超出等，下 面分别说明。 2.1 排课区 如图 2-1，横向为班级，第 1 列为星期，第 2 列为节次，中 间为人工排的课。 图 2-1 总课表 Excel——让排课更省心 6 为方便排课，可以应用 Excel 的数据有效性，将排课区的数 据作一些限定，方法如下： 选定整个排课的数据区（$A$3:$AX$41），在'数据'选项里， 单击'数据工具'的'数据有效性'，选择'数据有效性'，在弹 出的'数据有效性'对话框里，'允许'下选择'序列'，'来源' 里输入'=语文,数学,英语,思品,历史,地理,生物,物理,化学,体育,音 乐,美术,阳光,信息,英活'①，然后单击确定完成设置。这样，当 你在排课的时候就多了一种选择：既可以直接从键盘敲入，又可 以用下拉选单选择输入，如下图所示。 图 2-2 下拉选单输入 2.2 任课教师区 紧邻排课区的右侧对应位置，设计任课教师姓名区。设计思 ①本栏里输入您校实际课程。 第 2 章 总课表 7 路是：当左侧排课区的某节课排定后，通过公式在右侧显示出该 堂课的任课老师姓名，这个区域设计的目的是为防止课程冲突作 准备。如 AY3 单元格的公式如下： =IFERROR(VLOOKUP(C3,arrangement,COLUMN()-49,0),'') 这是一个函数嵌套公式，它由三个函数构成，其中，IFERROR （函数,''），意思是当括号内的函数值出现错误时，显示为'空'； 不出现错误时，显示函数本身的结果。其作用是避免当函数出现 错误时显示错符号。第二个函数 VLOOKUP 用于查找，在 arrangement 里查找 C3（课程名称）的值（在这里是'班会'）， 找到后，显示查找区域里第 column()-49 列的值（在这里就是班 会课的任课教师姓名）。函数 column()用于显示活动单元格的列数， 参数为空，column()-49②的值要与 Arrangement 区域的值对应。 Arrangement 是一名称，它代表安排表里的数据区，即：=安 排!$A$2:$AW$19。当然你也可以不用名称，而直接用数据区域来 代入，效果是一样的，用名称的目的是简化公式，让公式看起来 既具有可读性，又显得简洁。 有了任课教师姓名区，现在回过头来设计课程冲突颜色显示。 选中排课区，即$C$3:$AX$41，在条件格式的'公式'里输入如 下公式并设置颜色（我这里设定的是红色字体）： ② 这里之所以是减 49，是根据表的结构决定的，如果您的表的行列数 与这里的表不一样，就要调整这个数的值来适应您的表。 Excel——让排课更省心 8 =COUNTIF($AY3:$CT3,AY3)>1 注意单元格的引用是混合引用。 设计思路：一个老师在同一天同一节课，只能上一个班的课， 如果同一天同一节课安排了某个老师超过 1 节课的情况出现，就 将该课程用红色显示出来提示排课者，这样，排课老师马上就可 以发现问题，以便及时纠正。从'总课表'看出，同一天同一节 课，其实就对应'总课表'的一行，所以上面公式里第一个参数 用的是行相对引用，第二个参数要用相对引用。该公式的意思是： 在任课教师姓名区$AY3:$CT3 中统计 AY3 单元格中老师是否重复 （结果>1 就是重复），如果是就用颜色显示，如果否就正常显示。 2.3 统计区 统计区（$B$42:$AX$59）是用来统计已排节数的区域，如图 2.3，左侧的学科由'安排'表复制而来，上面的班级与本表第 2 行的班级对应。在 C43 单元格输入公式 =COUNTIF(C$3:C$41,$B43) 这个公式统计 701 班（七年级 1 班）全周'语文'课（$B43 单元格的值）的已排节数，注意是混合引用。将该公式复制到 C43:AX58，统计区的设计就算完成。当然您还可以进一步优化这 个表，比如，用条件格式将不等于规定节数的单元格用颜色显示 出来，这里就不多说了。 第 2 章 总课表 9 图 2-3 统计区部分截图 至此，整个'总课表'的编辑就结束了。 11 第3章 教师课表 3.1 教师个人课表 当'总课表'设计完成，并且课程已排好，那么本章介绍的 '教师课表'便是将'总课表'的数据抽取出来，按照个人喜欢 的形式制作成单个课程表，如图图 3-1。 图 3-1 教师个人课表 Excel——让排课更省心 12 这个表共有 11 行，7 列，其中，第 1 行为标题，第 2 行为教 师姓名及日期，第 3 至第 11 行为课程表主体，每节课既显示课 程名称，也显示班级名称。在 B4 单元格里输入公式： =IFERROR(INDEX(总课表!$C3:$AX3,MATCH($B$2,总课 表!$AY3:$CT3,0))&CHAR(10)&HLOOKUP($B$2,总课 表!$AY3:$CT$42,44-ROW(),0),'') 公式说明：第一个函数 IFERROR(函数,'')，前面已经说过，用 来将函数出错时，不显示错误而显示空白；公式其余部分被分成 两个部分，以 CHAR(10)①为分界点，前面部分用来显示课程名称， 后面部分用来显示班级名称。第二个函数 INDEX（范围②,列数） 用来搜索出由第 2 参数'列数'确定的课程名称。第三函数 MATCH($B$2③,总课表!$AY3:$CT3④,0)用来匹配$B$2 单元格里的那 个教师，在'总课表'任课教师区的第 1 行里排在第几列？这个 值也就是前面 INDEX（范围,列数）函数的第 2 个参数。 ① 该函数为一回车符，需要将该单元格格式设置为'自动换行'。 ②范围：总课表!$C3:$AX3，即'总课表'排课区的第 1 行，也就是星期 一的第一节课，正好与'教师课表'星期一的第一节课对应。 ③ 为教师姓名单元格，为绝对引用。 ④总课表!$AY3:$CT3 为'总课表'的任课教师姓名区的第 1 行，与排课 区的第 1 行对应。 第 3 章 教师课表 13 再来看第二部分是如何显示出班级名称来的。 函数 HLOOKUP($B$2,范围⑤,行数,0)，用来在'总课表'的任 课教师区查找该教师（$B$2 单元格确定的教师），并由第 3 参数 '行数⑥'来确定最终显示的值，这个值就是'总课表'第 42 行 显示的班级数值。第 4 个参数 0，说明是精确查找，否则为模糊 查找。 现将 B4 单元格里的公式复制到 B11，那么星期一的课程表就 制作好了；依据同样的原理制作其他星期的课程，现在列出星期 二第 1 节课（C4 单元格）的公式： =IFERROR(INDEX(总课表!$C11:$AX11,MATCH($B$2,总课 表!$AY11:$CT11,0))&CHAR(10)&HLOOKUP($B$2,总课 表!$AY11:$CT$42,36-ROW(),0),'') 看起来好像还是那么复杂，其实只要第 1 个公式琢磨透了， 这个公式就是一样的了，也不必重新输入，那该怎样操作呢？方 法是：将 B11 单元格里的公式复制到 B12，选中 B12 单元格，将 公式编辑栏里的内容复制并粘贴到 C4 单元格并稍作修改⑦即成。 ⑤ 此处的范围指：总课表!$AY3:$CT$42，对照 Excel 的课程表来看，此 范围其实就是'总课表'任课教师区。 ⑥ 此'行数'为：44-ROW()，看得出，越往下，该值递减。 ⑦ 修改的部分仅限于该公式的最后部分，将原来的 44-row()，修改为 Excel——让排课更省心 14 同样的道理可列出星期三第 1 节课（D4 单元格）的公式： =IFERROR(INDEX(总课表!$C19:$AX19,MATCH($B$2,总课 表!$AY19:$CT19,0))&CHAR(10)&HLOOKUP($B$2,总课 表!$AY19:$CT$42,28-ROW(),0),'') 需修改的部分参照上面。星期四第 1 节课（E4 单元格）的公 式： =IFERROR(INDEX(总课表!$C27:$AX27,MATCH($B$2,总课 表!$AY27:$CT27,0))&CHAR(10)&HLOOKUP($B$2,总课 表!$AY27:$CT$42,20-ROW(),0),'') 星期五第 1 节课（F4 单元格）的公式： =IFERROR(INDEX(总课表!$C35:$AX35,MATCH($B$2,总课 表!$AY35:$CT35,0))&CHAR(10)&HLOOKUP($B$2,总课 表!$AY35:$CT$42,12-ROW(),0),'') 由此看出，只要搞清楚了原理，这个表的制作也不是太难， 关键是第 1 个公式，其它的只需复制并稍作修改即可。 这样，这个表就算完成了，最后就是进行页面设置，使它能 36-row()，就可以了，是不是很简单呢？ 第 3 章 教师课表 15 够在一张 A4 纸（当然也可以是 B5 纸）上完全显示。有了这个表， 只要在$B$2 单元格里输入教师姓名，那么他的全周课程就全部显 示出来，直接打印出来发给该教师就行了，缺点是打印完一位老 师，就要在$B$2 单元格里重新输入另一个老师的姓名，才能打印 第二位老师的课程表。如果您校教师人数不多，这样也不是太麻 烦；如果像我们学校一样，有一百多老师，那效率就太低了，我 们暂且将这个问题搁置，后面有办法解决它。 3.2 制作多个教师个人课表 为了调课的方便，以这个表为基础，再制作两个一样的表， 但由于位置变了，所以公式也需要作一些改变，不过，有了第一 个表作基础，这两个表中的公式可以如法炮制出来。具体方法是： 先复制第一个表，将它粘贴到右侧适当位置，然后将公式中的 $B$2 替换成$J$2⑧就可以了，还有一点不同的是，为了方便调课， 在该表中设计了一个电话号码，以便需要调课的老师协商，也就 是在$L$2 单元格输入了如下公式： =IFERROR(VLOOKUP($J$2,电话,2,0),'') 公式中的'电话'为一名称，其实就是'电话列表'区域， 如果没有查找到该教师的电话，则显示为'空'，避免出现错误 ⑧ $J$2 为该表教师姓名所在的单元格。 Excel——让排课更省心 16 符号。这个公式虽然简单，但用处很大，还希望您能够逐渐熟悉 它。 用同样的方法再制作出一个'教师个人课表'来，然后还需 要制作出一个班级课表并放在适当地方，这个表的制作方法稍后 说明。其实只要有第一个表就行了，后面的三个表都不是必须， 嫌麻烦的话，可以不做，一个表足够了。 17 第4章 班课表 这张表是要打印出来贴在班上的，同样是从'总课表'中通 过公式将数据抽取出来，填在您制作好的班级课程表中，它大致 应该长成图 4-1 的样子 图 4-1 班级课表 先将页面按 A4 或 B5 纸张设置好（因为最后是要打印出来的）， Excel——让排课更省心 18 然后将表格的骨架做出来，最后来写公式。 这个表的班级选择在$H$1，可以手工直接在该单元格中输入 班级，如果不喜欢这样，那就设置条件格式，然后用鼠标来选择。 具体方法是：先将'安排'表中的$B$2:$AW$2 区域定义为名称 （我这里叫），然后在数据有效性设置的'允许'下选'序列'， 在'来源'后填入：=classes。 $B$2 单元格用来显示班级名称，公式如下： =HLOOKUP($H$1,安排!A2:$AW$19,18,0) 这个公式前面遇到过，就不多解释了。 $C$2 单元格显示该班班主任姓名，公式如下： =HLOOKUP($H$1,arrangement,17,0) 这个公式与$B$2 单元格里的公式基本相同，只是这里不是单 元格引用，而是用了名称，arrangement=安排!$A$2:$AW$19，其 它就没什么了。 下面来看星期一第 1 节课（单元格 B4）的公式： =HLOOKUP($H$1,排课,ROW()-2+8*(COLUMN()-2),0)&CHAR(10) &CHAR(10)&HLOOKUP($H$1,任课教师, ROW()-2+8*(COLUMN()-2),0) 第 4 章 班课表 19 这公式看起来比较长，也比较复杂一点，粗一看可能会把您 吓跑，但仔细研究一下也没出现新函数，都是之前见过的，由于 公式嵌套会使得您望而生畏。如果搞清楚公式中的每个函数以及 它的参数特点，就不难理解了。下面作一些说明： 先说两个名称，一是：排课=总课表!$C$2:$AX$42，其实就是 '总课表'的排课区；二是：任课教师=总课表!$AY$2:$CT$40， 它是'总课表'的任课教师区。整个公式由两部分构成，以 CHAR(10)①函数为分界点，第一部分显示课程名称，由 HLOOKUP($H$1,排课,ROW()-2+8*(COLUMN()-2),0)决定；第二部分 显示该课程任课教师，由 HLOOKUP($H$1,任课教师, ROW()-2+8*(COLUMN()-2),0)决定。如果像这样将这个长公式分解 为两部分，是不是就将公式变短了呢？ 第一部分思路是，用 HLOOKUP 函数，在'排课'区域去查 找班级名称（$H$1 单元格所代表的值），找到后，返回该区域的 第 ROW()②-2+8*(COLUMN()③-2)④行对应值，这便是该节课的课程 ① CHAR(10)函数其实是一个换行符，用两个这样的函数，是为了让行间 距大一些，用一个也可以。 ② 返回该单元格（B4）的行数。 ③ 返回该单元格（B4）的列数。 ④ 整个 ROW()-2+8*(COLUMN()-2 的值是凑出来的。当然，规律也是有的。 比如 8*(COLUMN()-2，为什么是这个样子的呢？去看看'总课表'的排课区 就知道了，原来每隔 8 行（即 8 节课）就是一个轮回，因此，当星期二的第 Excel——让排课更省心 20 名称了。 第二部分跟第一部分是完全类似的，自己试着去理解一下吧。 中间用两个 CHAR(10)函数连接起来，构成两行，同时两行还有间 隔，这样看起来是不是要美观点呢。有了这个公式后，只需要将 复制到全表就行了，复制后不作任何修改，这个表就基本完成了。 为了让班级课表更完善，我绘制了三个较宽的矩形，并分别 输入文字，用来提示'课间操'、'午餐'和'眼保健操'，适当 调整三个矩形框的尺寸，使可使得矩形框与整个课表比较协调。 由于矩形框要占据一定的位置，所以它所占据的那一行的高度要 调整得高些，作一些格式上的修饰，使表看起来更协调就可以了。 可能有人会问，为什么要用矩形框而不让它单独占一行？也 不是不可以，我之前就是这么做的，但是，带来的据点是：它下 面一行的公式要作修正，个人觉得麻烦，于是就上了这样的手段， 好处很明显：只需一个公式，然后复制就完成，完全不需调整。 懂得这个表的制作原理，那么先前在'教师课表'那一章里 留下的悬念，自然也就明白了。 1 节课来临时，就已经过去 8 行了。 21 第5章 年级课表 我校班级较多，所谓'总课表'其实是分年级打印的，所以 才有这样的名称。如果学校规模不大，也可以将三个年级的课表 制作在一张 A4 纸上，从而设计出真正的总课表来。 年级课表分年级制作成三个表，如果打印的话，就是三页， 每个年级一占页，三个表的结构完全一样，但公式是有区别的， 因为每个表所处的行的位置不一样，但都可以用同样的原理去理 解，一旦理解了，再来分析公式就不难了。现在，先将年级课表 的样子做出来，然后写出第一个公式，也就是 B4 单元格（七年 级 1 班星期一第 1 节课）里的公式： =HLOOKUP(C$3,排课,ROW()-2+8*INT((COLUMN()-3)/16),0) 如果表格横向放得下的话（我这里放不下，将星期四和星期 五两天的课折行显示），直接将这个公式复制到整个年级就完成 了，但是，由于星期四和星期五的课表提到了下面去显示，使得 公式到这里后要作一些调整。调整后 C14 单元格（星期四第 1 节 课）里的公式就变成如下的形式： =HLOOKUP(C$3,排课,ROW()+12+8*INT((COLUMN()-3)/16),0) 自己去理解一下应该没有问题，将这个公式复制到星期四和 星期五就行了。 Excel——让排课更省心 22 依据同样的原理就可把八、九年级的课表制作出来。我这里 仅提供几个公式。C25 单元格（八年级 1 班星期一第 1 节课）的 公式如下： =HLOOKUP(C$24,排课,ROW()-23+8*INT((COLUMN()-3)/16),0) C35 单元格（八年级 1 班星期四第 1 节课）的公式如下： =HLOOKUP(C$34,排课,ROW()-9+8*INT((COLUMN()-3)/16),0) C46 单元格（九年级 1 班星期一第 1 节课）的公式如下： =HLOOKUP(C$45,排课,ROW()-44+8*INT((COLUMN()-3)/16),0) C56 单元格（九年级 1 班星期四第 1 节课）的公式如下： =HLOOKUP(C$55,排课,ROW()-30+8*INT((COLUMN()-3)/16),0) 这样，年级课表也制作好了，这个是要打印来发给年级组长， 同时也送交学校领导。 23 第6章 教师课表数据 前面已经说过，当您校班级较多时，用'教师课表'一张一 张地打印来发给任课教师是比较麻烦的，因为每打印一张课表， 就要重新输入一次教师姓名，为解决这个问题，我想到了 Word 的邮件合并功能。 6.1 主文档的制作 新建一个 Word 文件，保存为'教师课表'，在这个文件里， 先设计出课表的样子，如图 6-1 图 6-1 Word 邮件合并主文档 Excel——让排课更省心 24 6.2 课程名称 回到'教师课表数据'工作表来，这个表的第 1 列是所有任 课教师姓名（不重复），这个名单列表来自'安排'表，参考第 2 页的第 1 章 1.2 节末尾部分，这份名单是按学科排列的；您也 可以按年级来排列，那就得分别列出七、八、九年级的任课教师 名单列表，然后再将这三个列表连接起来形成一个全校教师列表； 当然，得到教师列表后，再来按拼音排序也是可以的，就看您喜 欢了，目的是将来打印出来后按什么顺序发放比较方便，您就按 什么顺序来排列这份名单。 再来看第 1 行，它由两部分构成，从 B 列到 AM 列列出了全 周 38 节课的标题（如周一 1 表示星期一第 1 节课„„），后面部 分 AN 列到 BY 列，也列出了全周 38 节课的班级名称标题。最好 没把您搞晕，也就是说，前面部分用来存放课程名称，后面部分 用来存放是哪个班。下面来说公式，整个表比较大，公式也比较 复杂，但再复杂也不过只有两个公式而已，剩下的工作就是复制 这两个公式。先来看 B2 单元格里的公式： =IFERROR(INDEX(INDIRECT('总课 表!$C'&COLUMN()+1&':$Ax'&COLUMN()+1),MATCH($A2,INDIRECT( '总课表!$ay'&COLUMN()+1&':$ct'&COLUMN()+1),0)),'') 看出来了哈，的确很复杂。先把第 1 层外套剥离，将公式变 短，也就是去掉 IFERROR(余下的公式,'')，因为这个部分前面已经 说过了，它的作用无非是当公式出现错误的时候，不将错误符号 第 6 章 教师课表数据 25 显示出来（从而使该单元格显示空白），不出错的时候，就显示 '余下的公式'的内容。接着我们来看'余下的公式'如何运作。 '余下的公式'的主体是 index 函数，您肯定应该先搞清楚 这个函数的参数，无非就三个参数，从左至右依次是：搜索数据 的区域（或范围）、显示区域的第几行、显示区域的第几列。在 这里省去了行数，那就只有两个参数了，前面部分为搜索范围， 后面部分为列数，按这个思路去理解这个公式。这里又多出一个 函数 INDIRECT 来，它的作用是将它括号内的参数变成'引用'。 这个公式的思路是：在'总课表'的排课区的第 1 行（'总课 表!$C3:$Ax3'）去搜索课程名称，究竟是这一行的哪一列，由第 2 个参数决定；张 2 个参数公式的范围与第 1 个参数的范围有点类 似，可以参照来看。第 2 个参数的意思是在'总课表'的'任课 教师'区（即'总课表!$ay3:$ct3'）中来匹配第 1 列中的教师姓名 （如郑霞霞），看看这个教师在'任课教师'区中的第 1 行的位 置，用这个位置数来确定第 1 个参数所确定范围的具体位置；如 果这一行里没有找到第 1 列中的那个教师，那就显示空白。 图 6-2 '教师课表数据'的课程名称截图 Excel——让排课更省心 26 下面先试图理解第 1 部分（搜索范围），从公式看出，该区 域内的函数为 COLUMN()，说明这个范围只与列有关，因为不同 的列，就意味着不同的节次。这个公式在往下复制的时候，第 1 个参数不会变，但在往右复制的时候，由于 COLUMN()的值会变， 所以引用的区域就会跟着变，从而区分出不同的节次所搜索的范 围来。 公式的第 2 部分用了 MATCH 函数，它有三个参数，分别是 匹配的值$A2，匹配的范围 INDIRECT('总课 表!$ay'&COLUMN()+1&':$ct'&COLUMN()+1)和精确匹配（0 值）， 如果理解了第 1 部分的范围，这个范围是一样的，只是区域不同 而已，第 1 部分引用了排课区，第 2 部分的范围引用了任课教师 区，如此而已。 6.3 任课班级 接下来看看 AN2 单元格的公式，它的作用是：当第 1 列第一 位教师周一第 1 节有课时，显示是哪个班的课（即显示班级名称）， 如果没有课，则显示为空，用的是 HLOOKUP 函数，前面有过介 绍，这里略过。 第 6 章 教师课表数据 27 图 6-3 '老师课表数据'的班级名称截图 因为这个表太大（宽度是 77 列：全周课程节数 38×2；高度 是 163 行：我校任课教师人数），所以截图分成了两部分，理解 公式时参考这两个截图。 6.4 邮件合并 好了，表格制作完了，将其保存好并关闭。下面该说说'邮 件合并'的事儿了。还记得本章一开头制作的那个主文档吗？参 看第 23 页图 6-1，下面的工作就是要把刚才制作好的'邮件合并 数据'里的内容插入到主文档的表格中，具体方法是①： 返回到主文档的界面，选择'邮件'标签，在'开始邮件合 并'栏目里单击'开始邮件合并'的'信函'，接着再单击'选 择收件人'中的'使用现有列表'，在弹出的对话框中找到'课 表'工作簿，单击'打开'，再点'确定'，再在弹出的'选择表 ① 下面的操作均是在 Word 中进行。 Excel——让排课更省心 28 格'对话框中点击'教师课表数据'后点'确定'，这样就将该 主文档与 Excel 工作簿（课表）联系起来了。接下来的工作虽比 较繁琐，却是必须，而且没有捷径，硬着头皮点吧。 先将光标放在'教师：'后面，然后单击'插入合并域'，选 择'教师'（这样就把 Excel 工作簿中第 1 行名称为'教师'的那 列数据插入到此）；再将光标移至星期一第 1 节课的位置，单击 '插入合并域'，选择'周一 1'（这样就把 Excel 工作簿中第 1 行名称为'周一 1'的那列数据插入到此）；按驾车换一行，继续 点击'插入合并域'，选择'M1'（这样就把 Excel 工作簿中第 1 行名称为'M1'的那列数据插入到此）„„依此类推，将主文档 课程表的每一个格子填满，插入合并域的时候需要注意：周一 1 要与 M1 对应，同样周三 4 要与 W4 对应，否则最后制作出的课 程表会出错。完成后看到的样子应该与图 6-4 相似。 图 6-4 已经将数据插入到主文档后的课表 第 6 章 教师课表数据 29 这一步完了，就可以看看成果了，点击'预览结果'，主文 档课程表立即就变了样，成了具体的老师和具体的班级了。您还 可以继续点'下一条'来预览第 2 位老师的课程，觉得基本上没 错了，就可以点击'完成并合并'下的'编辑单个文档'，再点 '确定'，现在只看见屏幕闪烁，只需一会儿功夫，162（我校教 师人数）页的文档就出现了，这个文档中的每一页，对应一位教 师的课程表，将其保存起来，随时可以打印。这期间如果课程有 变动，即 Excel 工作簿作了调整，那么，当您再次打开主文档（注 意是主文档，不是刚才保存的那份文档！）时，会出现图 6-5 所 示的提示： 图 6-5 更新提示 点击'是'按钮（更新数据）后，新的数据就会出现在该主 文档中，这个时候再点'完成并合并'下的'编辑单个文档'， 再点'确定'，又产生一份新的文档，将文档打印出来便是更改 后的教师课表了。 特别说明：邮件合并产生的新文档是分节的，也就是说，每 Excel——让排课更省心 30 一页其实是一节②，如果您不是要打印全部文档，比如您只想打 印第 5 位教师的课表，那么您就应该在'打印'对话框的'页数' 那一栏输入's5'③而不是直接输入 5。如果您不喜欢这样，也有 办法，那就是用'查找-替换'功能，将'节'的符号全部替换为 '空'，即将'节'删除，就变成普通的页面了④。 ② 您可以从左下角的状态栏看出。 ③ 这里的 s 就是英语'section（节）'打头的字母。 ④ 这样，整篇文档只有 1 节 31 第7章 班课表数据 这一章的功能与前一章相似，思路相同，不同之处是，前一 章是通过'邮件合并'功能，批量打印'教师课表'，而本章也 是通过'邮件合并'功能，批量打印'班课表'。所以本章的介 绍可能比较简略。 7.1 课程名称 同样，先来说说'班课表数据'这个表格，它包含第 1 列（班 级名称①）和第 1 行，第 1 行由两部分构成，从 E 列到 AP 列列出 了全周 38 节课的标题，后面部分 AQ 列到 CB 列，列出了全周 38 节课的任课教师名称标题。也就是说，前面部分用来存放课程名 称，后面部分用来存放是哪个老师上的那节课。下面来说公式， 整个表比较大，公式却不复杂。先来看 D2 单元格里的公式： =HLOOKUP($A2,arrangement,17,0) 这公式简单哦，HLOOKUP 函数的第 2 参数用了名称而不是范 围引用，其它没什么可解释的。这一列用来将各班班主任姓名列 ① 其实我校现在只有 42 个班，但我保留了几个空白班级名称，所以， 总共有 48 个班级名称。 Excel——让排课更省心 32 出，再来看 E2 单元格的公式： =OFFSET(总课表!$C$2,COLUMN()-4,ROW()-2,) 这个公式用到一个新函数 offset，它是将'总课表'的$C$2 按'行'和'列'进行移位而得。 7.2 任课教师姓名 同样 AQ2 单元格的公式具有类似的样子： =OFFSET(总课表!$AY$2,COLUMN()-42,ROW()-2,) 它是将'总课表'的$AY$2 按'行'和'列'进行移位而得。 前一公式得到课程名称，后一公式得到该课程的任课教师，最后 复制这两个公式即完成全表制作。 后面的'邮件合并'与前一章类似，先制作出一个主文档， 如图 7-1 所示。 第 7 章 班课表数据 33 图 7-1 班级课表主文档 然后在'邮件'标签中，点击'开始邮件合并'的'信函'， 接着再单击'选择收件人'中的'使用现有列表'，在弹出的对 话框中找到'课表'工作簿，单击'打开'，再点'确定'，再在 弹出的'选择表格'对话框中点击'班课表数据'后点'确定'， 这样就将该主文档与 Excel 工作簿（课表）联系起来了。接下来 的工作 便与前一章相同，不厌其烦地点击'插入合并域'，直至 完成，完成后的班课表应该跟图 7-2 差不多。 Excel——让排课更省心 34 图 7-2 合并后的班级课表主文档 后面的操作就不再多说了，最后完成合并，并编辑新文档， 确定后，就得到了一个 42 页②的新文档，将其保存，以后可以随 时调出并打印。 至此，全部工作簿编辑完毕。 ② 对应 42 个班 附 1 截图目录 35 附1 截图目录 图 1-1 工作表列表 1 图 1-2'安排'表 2 图 2-1 总课表 5 图 2-2 下拉选单输入 6 图 2-3 统计区部分截图 9 图 3-1 教师个人课表 11 图 4-1 班级课表 17 图 6-1 Word 邮件合并主文档 23 图 6-2 '教师课表数据'的课程名称截图 25 图 6-3 '老师课表数据'的班级名称截图 27 图 6-4 已经将数据插入到主文档后的课表 28 图 6-5 29 Excel——让排课更省心 36 图 7-1 班级课表主文档 33 图 7-2 合并后的班级课表主文档 34 附 2 公式目录 37 附2 公式目录 =COUNTIF($R$3:$AW$17,B3) ....................................................... 3 =COUNTIF($B4:$AW$17,B3) ......................................................... 3 {=SUM(1/COUNTIF(TEACHERS,TEACHERS&''))-1} ........................ 4 =OFFSET($B$3,INT((ROW()-2)/48),MOD(ROW()-2,48)) ................. 4 =IFERROR(VLOOKUP(C3,ARRANGEMENT,COLUMN()-49,0),'')...... 7 =COUNTIF($AY3:$CT3,AY3)>1 ....................................................... 8 =COUNTIF(C$3:C$41,$B43) ........................................................... 8 =IFERROR(INDEX(总课表!$C3:$AX3,MATCH($B$2,总课 表!$AY3:$CT3,0))&CHAR(10)&HLOOKUP($B$2,总课 表!$AY3:$CT$42,44-ROW(),0),'') ....................................................... 12 =IFERROR(INDEX(总课表!$C11:$AX11,MATCH($B$2,总课 表!$AY11:$CT11,0))&CHAR(10)&HLOOKUP($B$2,总课 表!$AY11:$CT$42,36-ROW(),0),'') ..................................................... 13 =IFERROR(INDEX(总课表!$C19:$AX19,MATCH($B$2,总课 Excel——让排课更省心 38 表!$AY19:$CT19,0))&CHAR(10)&HLOOKUP($B$2,总课 表!$AY19:$CT$42,28-ROW(),0),'') ..................................................... 14 =IFERROR(INDEX(总课表!$C27:$AX27,MATCH($B$2,总课 表!$AY27:$CT27,0))&CHAR(10)&HLOOKUP($B$2,总课 表!$AY27:$CT$42,20-ROW(),0),'') ..................................................... 14 =IFERROR(INDEX(总课表!$C35:$AX35,MATCH($B$2,总课 表!$AY35:$CT35,0))&CHAR(10)&HLOOKUP($B$2,总课 表!$AY35:$CT$42,12-ROW(),0),'') ..................................................... 14 =IFERROR(VLOOKUP($J$2,电话,2,0),'')...................................... 15 =HLOOKUP($H$1,安排!A2:$AW$19,18,0) ................................... 18 =HLOOKUP($H$1,ARRANGEMENT,17,0) ..................................... 18 =HLOOKUP($H$1,排课,ROW()-2+8*(COLUMN()-2),0)&CHAR(10) &CHAR(10)&HLOOKUP($H$1,任课教师, ROW()-2+8*(COLUMN()-2),0) .......................................................................................................... 18 =HLOOKUP(C$3,排课,ROW()-2+8*INT((COLUMN()-3)/16),0) ...... 21 =HLOOKUP(C$3,排课,ROW()+12+8*INT((COLUMN()-3)/16),0) ... 21 =HLOOKUP(C$24,排课,ROW()-23+8*INT((COLUMN()-3)/16),0) .. 22 附 2 公式目录 39 =HLOOKUP(C$34,排课,ROW()-9+8*INT((COLUMN()-3)/16),0) .... 22 =HLOOKUP(C$45,排课,ROW()-44+8*INT((COLUMN()-3)/16),0) .. 22 =HLOOKUP(C$55,排课,ROW()-30+8*INT((COLUMN()-3)/16),0) .. 22 =IFERROR(INDEX(INDIRECT('总课 表!$C'&COLUMN()+1&':$AX'&COLUMN()+1),MATCH($A2,INDIRECT(' 总课表!$AY'&COLUMN()+1&':$CT'&COLUMN()+1),0)),'') ............... 24 =HLOOKUP($A2,ARRANGEMENT,17,0) ....................................... 31 =OFFSET(总课表!$C$2,COLUMN()-4,ROW()-2,) .......................... 32 =OFFSET(总课表!$AY$2,COLUMN()-42,ROW()-2,) ...................... 32 附 3 Excel 常用技巧集 41 附3 Excel 常用技巧集 A. 移动及选定单元格 我们在操作 Excel 工作表的时候，经常会定位活动单元格， 或将活动单元格移动到想去的地方，以便输入数据或公式。好像 移动单元格的操作有点太简单而不值得说？非也。如果您掌握了 一些技巧，会大大提高您的效率。 先来说最简单或最容易理解的方法：直接在键盘上移动四个 方向键①；或直接用鼠标点击您想去的单元格；再就是单击'名 称框'（编辑栏的左侧）后输入您想去的单元格（或区域）后回 车，就可定位到您的目的单元格了②。 如果您是在已经输入了数据的矩形区域中③，这时移动单元 ① 按一次，移动一格；按住不松手，它会一直移动，直到你松手或已到 表格尽头为止。 ② 此方法也可以输入单元格区域。 ③ 要求数据区域没有空白，也就是说，每个单元格必须要有数据，不能 为空。 Excel——让排课更省心 42 格就太方便了，快捷键是： Ctrl+方向键（英语输入状态下） 可以迅速回到数据区域的四个边线上而不管数据区有多大。 如果在以上操作的基础上，再配合 Shift 键，那么快速移动就变成 了快速选择④，这个组合键真是太神奇了，请千万记住并熟练应 用： Ctrl+Shift+方向键 强烈建议用键盘操作而非鼠标，包括您在操作其它应用程序 或 Windows 的时候！也许您已经找到规律了：单独按方向键的时 候，移动单元格是比较慢的；如果加一个 Ctrl 键，速度一下就提 起来了；如果再加上 Shift 键，原来的移动就变成了边移动边选择。 顺便提一下，当您的数据区域比较大的时候，以下几个快捷键肯 定是有用的： Home：回到该行行首 Ctrl+Home：回到 A1单元格 ④ 指从当前单元格一直选取到数据区的末尾，具体选取的是哪个区域， 得由您按的是方向键的上、下、左、右来决定。 B 复制和粘贴 43 Ctrl+end：回到数据数据区的右下角 Page up：往上翻一屏 Page down：往下翻一屏 Alt+page up：往左翻一屏 Alt+page down：往右翻一屏 B. 复制和粘贴 复制和粘贴有值得说的吗？无论是数据，还是公式，不过就 是先单击要复制的单元格（或区域），点击'复制'，然后再单击 目标单元格后点'粘贴'吗？对的，还可以用 ctrl+C 和 Ctrl+V 快 捷键，再就是用鼠标操作：选择要复制的单元格，将鼠标移到该 单元格右下角并呈现实心'+'符号时，往下拖动（或往右拖动）。 以上都是正确的方法，也经常被大家所采用。我这里要说的 是另一种快捷键，当想将数据（或公式）往右边复制时，只需选 中该单元格和右边的目标单元格（区域）后按： Ctrl+R：往右复制（R：right） 如果想将数据（或公式）往下复制时，只需选中该单元格和 Excel——让排课更省心 44 下面的目标单元格（区域）后按： Ctrl+D：往下复制（D：Down） C. 数据录入 数据录入时，需要说明的是，搞清楚数据类型，尤其是貌似 是数值，但却是文本的那一类，比如电话号码、身份证号等，输 入这类文本型数据时，先要将单元格格式设置成'文本'，然后 再输入，或者在输入时，先在英文状态下按'''号（该符号不会 显示）后再输入。如果在未设置格式的情况下，已经输入了如身 份证号码（18 位），那么该单元格就会将该号码的后三位显示为 '0'⑤，此时，如果您再将该单元格格式设置为'文本'，excel 也无回天之力，解决的办法只有重新输入。默认情况下可以通过 单元格的对齐方式来识别数据类型：数值左对齐，文本右对齐。 另外，除了在'选项→高级'的'按 Enter 键后移动所选内 容'的'方向'里选择向下或向右，满足您自己的喜好外，要想 提高输入速度，还可以采用以下办法：先选定输入范围，然后再 输入，每输完一个单元格按一次'Tab'键。 ⑤ 这是因为该单元格将其识别为数值了，而每个单元格最多只能存储 15 位数据，多出的部分以 0 代替。 D 数据排序 45 有效性设置也可帮助您在输入时出错，如：限定学生成绩的 输入范围为 0-100 分，可在'数据有效性'设置中，将允许值设 为整数，输入最大值和最小值后'确定'，这样，当您输入的数 值不在这个范围中的时候，excel 就会停止并提醒您。又如：您需 要在一列中输入身份证号码，要求不重复（重复时提醒），输入 前同样'数据有效性'中设置，选'自定义'，然后在'公式' 中输入： =countif($a$1:$a$100,a1)=1 如果已经输入了数据，要检查有没有重复值（有的话用颜色 标识出来），此时可选定该数据区域，然后在'条件格式'的'仅 对唯一值或重复值设置格式'中进行设置。 D. 数据排序 数值的排序，无非就是升序或者降序，很简单，但有一点很 重要，那就是不能有合并单元格。文本的排序同样可以是升序或 者降序，同时还可以用'自定义序列'来排序，这就给我们提供 了很大的方便。比如，我想按：一等奖、二等奖、三等奖的顺序 来排，默认情况下是按第一个字拼音的首字母在 26 个字母中的 顺序来排序，这显示不符合我的想法，那就只能定义一个新序列， 然后按新定义的这个新序列来排序才能得到想要的结果。类似的 还有，将本单位的名单按您想要的顺序来排等，方法都相同。 Excel——让排课更省心 46 E. 表格打印 一般情况下，往 excel 里输入数据是有要求的：第 1 行为字 段行，以后各行为数据，并且中间的数据不要有空白，也不要有 合并单元格，这样，您在排序以及以后的其它操作中才不会出错。 但是这样一来，当您在打印这个表格时，会出现无抬头的现 象，我的解决办法是用页眉来当抬头，但在表格中看不直观，只 有在打印预览中才可见。不管它，设置好标题行重复，直接打印 即可。有时，可能只需要打印表格的部分内容：既可以选定这部 分内容后打印（选择打印选定区域），也可以将这部分内容选定 后，在'页面布局'中设置打印区域，然后再打印就没问题了。 最后的说明：这个 Word 文档，设计成 A4 纸，全部打印好后， 在中缝订上钉子同，从中间对折后便成为一本小册子，携带方便。 其页面布局，可从'页面设置'对话框里查看。样式的定义可从 '开始'选项卡→样式组中查看。 |
|
|
