excel手动排课表公式（教你升级Excel排课表）
excel手动排课表公式（教你升级Excel排课表）排课模版功能的优化班课排课（兼容一对一）目录拥有固定工位的一对一排课可变工位的一对一排课
在培训机构的教学管理中，安排课表是每学期的必要工作之一。
排课管理对于一个培训机构来说十分重要，却不是一件容易的事情。一年四个学期，每到学期开始前的一段时间，教务老师就要废寝忘食，披星戴月，不断收集学生和老师的资料，经过反复的修改才可能制备出一份合格的课表。这还没完，学期开始之后，随着一对一（一对多）排课数量的增加以及一些突发情况的增多例如学生临时调课等，排课管理就变得更加复杂。
阻碍教务老师顺利排课的最大因素就是“在排课的过程中，需要查阅并且记忆大量的课程信息，确保安排的课程不会出现冲突”。也就是说冲突检测是排课管理中最耗费精力的问题。
下面我就从以下三个场景出发，从易到难地讲述如何制作带有“自动排课冲突检测”能力的excel排课表。
目录
拥有固定工位的一对一排课
可变工位的一对一排课
班课排课（兼容一对一）
排课模版功能的优化
拥有固定工位的一对一排课一对一机构，即课程中只包含一位老师和一位学生。固定工位则表示机构中每个老师都有其专属的教室，换句话说，教室数目与老师数目一致，那么某个老师是否空闲就等价于他的专属教室是否空闲。
基本定义搞清楚之后，我们就可以进行实操了。
首先需要做一个模板，上文我们指出，排课管理中需要考虑的因素包括“时间”“教室”“老师”和“学生”。
而在固定工位的条件下，“教室”和“老师”的数量实际上是一致的。因此在固定工位一对一排课的模板中我们只需要考虑“老师”，而且用“老师”来代替“教室”。
为了使排课的表格尽量的小，我们选择突出“时间”以及“老师”两种因素。将“时间”作为横坐标，以课时时长为单位（本文姑且按照课时时长为一小时来计算，13:00为最后一节课，由于咱们教务排课基本上按照一周的课表进行，只需要在表格的横向进行相应的扩展或者拆分成七个sheet即可），“老师”作为纵坐标，从上到下列出机构的老师姓名。如下表所示：
模板中的每一个空格都表示一节一对一课程，模板中任何空白的格子都可以用来添加新的课程。如果有学生来机构报了某老师在某时间的课程，这时，我们便可以在相应的位置输入学生的姓名，注意要确保一个学生在每一列中至多出现一次。为了便于下面的讲解，我们随意填写了下面的表格，如下表所示：
原则上，我们只需要获取到待排课学生的空闲时间，然后再去标记该学生在该时间段是否可以被排课就可以了。
首先，我们需要在模板之外的空白位置填写待排课学生的名字。例如“小白”。
然后，在框架表格中添加一行“是否可以排课”的标记。例如8：00那一列由于小白已经在上赵文清老师的课，那么B2位置就应该显示“不可排课”，但是9：00那一列，小白没有课，因此C2位置应该显实“可以排课”。
我们的目标就是要让第二行能够自动显示“不可排课”或者“可以排课”。那怎么做呢？
我们可以根据这样的常识来进行判断——任何一个时间只要出现过待排课学生的名字（小白），那么这一个时间就不能再用来排课。换句话说，我们要让Excel自动统计出每一列中出现小白的次数，只要出现次数≥1次，那么就不能排课。
因此，我们点击B2格子，然后将下面一段公式直接粘到B2处：
=IF(COUNTIF(B3:B10000 $J$1) > 0 "不可排课" "可以排课")
注：输入公式的整体解释是在B列的第3行到第10000行中寻找，如果有内容和格子J1的内容一致，那么就显示“不可排课”，否则显示“可以排课”。
1、J1格填写的是待排课的学生姓名，相应的，在模板之外的任何位置都可以填写待排课的学生姓名，只需在公式的相应位置改成相应的形式即可。例如将待排课学生姓名填写在K2位置，那么在B2处则需填写：=IF(COUNTIF(B3:B10000 $K$2) > 0 "不可排课" "可以排课")
2、10000泛指机构老师的数目，一般机构都不会超过这个数值。也可以自定义数值，但不要低于机构实际的老师数量。
完成后，就会看到B2自动出现了“不可排课”4个字。
最后，我们将公式复制到其它时间上（其它列）。
方法是，鼠标点击格子B2，移动鼠标到B2右下角，当鼠标变成“黑十字”型时，按住鼠标左键往右边拖拽就可以将公式自动复制到其它列中。（下文类似步骤不再赘述）
可变工位的一对一排课实际情况下，很多机构都会有大学生兼职老师。而且机构的校长、主管往往也是“兼职”老师，需要上课。对于他们而言，由于每周在课堂上的时间较少，机构一般不会给安排固定的教室。产生的结果就是1对1教室的数目往往少于老师数目，排课时就必须要确保课程有对应上课的教室。
为此，除了拥有固定工位的排课表格所需考虑的因素之外，空闲“教室”这个影响因素就变得尤为突出。
此时，模板的纵坐标应换成“教室”。如下表所示：
与固定工位的模板类似，可变工位的排课模板中每一个位置都可以填入一个老师和一个学生。此时，判断是否可以排课，我们需要同时考虑学生的空闲时间和老师的空闲时间。也就是说，某一列中，只有当老师和学生的名字都没有出现过的时候，那么这一列才可以排课。
首先，我们需要在模板之外的空白位置填写待排课学生和待排课老师的名字。例如J1填写“小明”，J2填写“钱文华”。
然后，在框架表格中添加一行“是否可以排课”的标记。如下表所示：
整个公式采用的逻辑和固定工位的一对一排法是类似的，只需要稍加修改即可。我们需要先统计出每一列中，待排课老师出现的次数和待排课学生出现的次数，然后将这两个数字相加，只要结果≥1，那么就不能排课。
例如8:00那一列由于小白已经在上赵文清老师的课，小明就不能上赵老师的课了，那么B2位置就应该显示“不可排课”。11:00均未出现小明和赵文清老师的姓名，所以E2位置应该显示“可以排课”。
因此，我们点击B2格子，然后将下面一段公式直接粘到B2处：
=IF(COUNTIF(B3:B10000 "*"&$J$1&"*") COUNTIF(B3:B10000 "*"&$J$2&"*") > 0 "不可排课" "可以排课")
注：J1格填写的是待排课的学生姓名，J2格填写的是待排课的老师姓名。相应的，在模板之外的任何位置都可以填写待排课的学生姓名和待排课的老师姓名，只需在公式的相应位置改成相应的形式即可。例如将待排课学生姓名小明填写在I3位置，待排课老师姓名填写在J4位置，那么在B2处需填写：
=IF(COUNTIF(B3:B10000 "*"&$I$3&"*") COUNTIF(B3:B10000 "*"&$J$4&"*") > 0 "不可排课" "可以排课")
完成后，就会看到B2格自动出现了“不可排课”4个字。
最后将公式复制到所有的时间上，整个排课表就制作完成了。
不同于一对一排课，班课排课一般在学期开始之前进行，所以主要考虑的因素是“教室”和“老师”的空闲时间。
但是排课的时候也不能完全避免班内学生的因素，例如：
1、某一些班级联报的情况比较多，如果放置在同一时间段，就会降低联报的比率。
2、学期中某些班级需要进行调整，调整的过程中就需要考虑班中学生的空闲时间。
3、机构主营业务既包含班课也包含1对1，在学期中就会比较频繁的排课。
第一种情况只能通过教务老师的经验进行处理，excel不能解决这类问题，但是后面这两种情况则是可以通过excel自动判断冲突的。
使用excel对小班进行排课，采用的排课框架表格和可变工位的一对一排课类似，但有所补充。一个小班的学生数目在30人左右，如果将学生姓名逐一都填写在表格中，编辑和查找都会很麻烦。
因此，模板中的每一个格子只填写一个班级的名称，并且预留一个空位显示出该班级和待排课班级是否冲突，如下表所示。
每个班级的任课老师和学生信息，我们存储在另外一张名为“班级记录”的表中。
在排课之前，需要首先将班级信息录入到“班级记录”中。第一列填写班级的名称，之后从第二列开始，每一列填写任课老师或者班内的学生姓名。如高二数学基础班，任课老师名赵文清，学生姓名分别是小明，小红和小黑。如下表所示：
这样我们就将班课的排课模板制作完成了，保存好班级信息之后，我们紧接着就需要用excel表格统计出冲突的课程。
一个很直观的想法就是，两个班如果有共同的老师或者共同的学生，那么这两个班的上课时间一定不能发生重叠。如果上课时间发生了重叠，那么就意味着这两门课就是有冲突的。另外，在模板中，每一列的课程都是在同一时间进行。因此，我们要确保每一列中的任何一个班级都不能和待排课班级冲突。
我们要编写的冲突检测公式也是按照刚刚的思路来实现，分成如下的两步流程：
第一步，检查每一个已经排好的班级是否与待排课班级存在冲突。
首先，我们要根据模板中班级的名字，在“班级记录”表格中定位这个班的老师和学生。然后，我们把老师和学生的名字进行一一对比，统计出相同的名字出现的次数。如果相同名字出现的次数≥1，就说明有班级冲突的情况发生。
我们选择格子C3，并直接录入公式：
=IFERROR(IF(SUM(COUNTIF(INDIRECT("班级记录!A"&MATCH(B3 班级记录!$A$1:$A$100 0)&":AZ"&MATCH(B3 班级记录!$A$1:$A$100 0)) INDIRECT("班级记录!A"&MATCH($P$1 班级记录!$A$1:$A$100 0)&":AZ"&MATCH($P$1 班级记录!$A$1:$A$100 0)))) > 0 "冲突" "不冲突") "")
注：
1、结束公式输入时需同时按下“CTRL SHIFT ENTER”。
2、该公式适用于学生数量低于50的小班情况，对于学生数量超过50的情况并不适用。
编写好公式之后，需要将公式复制到其它的框中，第一步就完成了。
第二步，检查完每一个已经排好的班级是否与待排课班级存在冲突之后，由这些信息就可以推测出哪些时间可以用来排课。某一个时间可以排课当且仅当这个时间所在的列不存在任何一个冲突。
因此，在格子B2处我们直接输入公式：
=IF(COUNTIF(C3:C10000 "冲突") > 0 "不能排课" "可以排课")
最后将公式复制到所有的时间上，整个班课的排课表就制作完成了。
排课模版功能的优化表头固定——防止页面滚动后第一、二行以及第一列的内容消失
考虑到实际情况下，机构的老师的数目会比较多，会占用很大一部分界面，为了防止页面上下滚动后第一、二行的内容（排课情况）消失，就需要固定顶端两行。
同样的，排课的时候一般都会直接考虑一周的课表，页面左右滚动寻找排课时间的时候会导致第一列消失（老师姓名），因此，还需要将第一列固定。
我们采用的方法如下：
选中B3，然后选择“视图”-> "冻结窗格" ->"冻结拆分窗格"。
注：选中第N行第M列，则前N-1行和M-1列均被冻结。上表所示选中B3并冻结拆分窗格，则第一、二行以及A列窗格都被冻结。
模版保存——防止表格被错误篡改
Excel中的公式，在表格编辑的过程中，容易因为误操作而被更改。而且，在更改之后也比较难以发现。为此，我们需要在做好排课表模板之后，设置一个权限。对于没有权限的教务老师，只能用来排课，不能对公式进行编辑。
首先，Ctrl A选中整个表格。点击鼠标右键，点选“设置单元格格式”并点击。
然后在弹出框中，点击“保护”。就会看到“锁定”和“隐藏”两项。
我们去掉默认的“锁定”上面的勾，再点击“确定”。
鼠标左键选中所有包含公式的表格，点击鼠标右键，选择“设置单元格格式”
在弹出框中，选择保护，再在“锁定”位置打钩，单击“确定”
点击“审阅”，选择“保护工作表”，设置一个密码。那么这时候没有密码的用户只允许编辑“未锁定的单元格”。
番茄博士说
在机构不断发展壮大的过程中，排课管理会变得越来越复杂。如果说既要廉价又要达到一定的排课效果，往往优先选择excel进行排课。
排课管理中需要考虑的因素包括“时间”“教室”“老师”和“学生”。
全职机构的一对一排课，在excel排课管理中要强调“时间”和“老师”这两个因素；
含兼职机构的一对一排课，则因为教室数量少于老师数量，需要把“教室”当成主要因素来考虑，在excel排课管理中要强调“时间”和“教室”这两个因素；
班课机构的排课，在excel排课管理中，既要强调“时间”“教室”两种因素，同时也不能忽略“老师”和“学生”对排课的影响。
排课管理尽管很耗费精力，但是只要花一天左右的时间将模板表格制作完成并保存，之后再进行排课时，将会节省大量的时间。
群内答疑：
9月16日（周五）上午10:00—11:00，
番茄博士将在校长运营圈的各微信群和QQ群中，进行关于本篇文章的群内答疑，欢迎大家参与excel排课管理的工作讨论哦。
校长运营圈微信群请让班主任拉你入群，
班主任个人xzbzr123
校长运营圈QQ群群号：578195357
最后祝各位校长中秋节快乐！
更多教育机构办学实操干货，请关注校长运营圈公众号（yaohaotixy），也可以私聊我哦（xzyyqhjj）
