Excel 函数公式在中小学排课中的应用研究.docx
《Excel 函数公式在中小学排课中的应用研究.docx》由会员分享，可在线阅读，更多相关《Excel 函数公式在中小学排课中的应用研究.docx（21页珍藏版）》请在冰点文库上搜索。
Excel函数公式在中小学排课中的应用研究
Excel函数公式在中小学排课中的应用研究
中小学教导处工作中非常重要的一项任务便是排课。
现在有许多这样的排课软件，但大多需要付费，使用上非常不便。
传统的手工排课工作量大，若一个环节出现问题，就会牵扯其他环节，有时甚至会前功尽弃。
为此，我尝试运用Excel电子表格进行辅助排课，在实践中收到了不错的效果。
下面就具体过程作系统的说明，希望对大家有所帮助。
一、准备工作。
1.软硬件准备。
EXCEL（2003或以上）电子表格，或WPS，能正常启动以上软件的电脑设备。
2.了解常用函数与公式的用法。
例如IF（真假判断）、CONCATENATE（数据引用）、OR（或者）、
AND（并且）、VLOOKUP（列查找）、COUNTIF（条件统计）、INDEX+MATCH
（行列交叉引用）、SUM（求和）、SUMIF（条件求和）等函数；学会数据有效性和条件格式的设置方法；了解相对应用和绝对应用的作用等，掌握这些基本用法在排课过程中都会发挥意想不到的效果。
3.打开EXCEL电子表格，新建工作簿，添加工作表，并依次命名为“1信息录入”、“2任课设置”、“3总课程表”、“4课表检验”、
“5年级课表”、“6教师课表”。
二、工作表的具体设置。
当工作表命名以后，要逐一进行设置，由于工作表的数据会相互引用，所以在设置过程中一定要做到准确、真实，数据格式一致。
（一）“1信息录入”工作表。
1.信息录入工作表是所有工作表的基础，其数据的可靠、清楚对后面的排课、检验、提取将会牵一发而动全身，所以在设置前要进行
全面的调查，以保证输入模式统一，修改方便，为以后的循环使用提供保证。
以下表为例（限定使用班级数50，教师数200），设置了学校、上课人数、学期、各班标准课时、总课时、教师、开设的学科、班级及班主任、各科课时等。
见图1
2.在“土黄色”区域输入数据，在以下区域设置“数据有效性”。
“是否上课”列的设置：
选择C5单元格，点击数据菜单，打开有效性按钮，数据来源为“是，否，其他”。
设置完毕后向下复制至
C204.然后根据需要依次设置是否上课。
“班主任”列的设置：
选择J5单元格，数据来源为=$B$5:
$B$204，即教师列的数据。
然后向下复制至J54。
最后选择设置各班班主任。
3.在以下单元格输入公式。
“L2”单元格输入公式：
=COUNTIF（C5:
C204,"是"），此处运用了条件统计公式，用于统计C5:
C204中包含“是”的个数，即为上课人数。
“AD2”单元格输入公式：
=SUM（AD5:
AD54），用于统计AD5:
AD54
中数据的和，即为总课时。
4.在AD5:
AD54区域设置条件格式。
选择“开始”菜单，打开“条件格式”按钮，在新建规则中选择
“只为包含以下内容的单元格设置格式”选项，设置单元格值等于
“AA2”时背景色显示为黄色，最后确定预览，此时凡是数据等于34
的背景为黄色。
这样的设置便于鲜明快捷的掌握数据输入是否正确。
5.保护和锁定工作表。
锁定不活动单元格：
全选工作表，在单元格格式中选择保护，在
“锁定”和“隐藏”选项上打钩，确定。
然后按住“CTRL”键，选择
活动单元格，即表格中的“黄色”区域，在单元格格式保护中取消“锁定”和“隐藏”前的钩，最后确定。
保护工作表：
在“审阅”菜单，打开“保护工作表”按钮，设置密码，最后确定。
此时，在工作表的活动单元格中可输入数据，其他单元格处于锁定状态。
（二）“2任课设置”工作表。
1.任课设置工作表用于计算教师个人的“标准课时”，并对教师个人的工作量进行计划，作好任课情况的统计，即就是表格中的“计划课时”，为编排总课表提供服务。
“土黄色”区域为活动单元格，可自由输入信息，详见图2：
2.在“蓝绿色”区域输入公式。
“A5:
C203”区域：
在A5单元格输入公式：
=CONCATENATE（'1信息录入'!
A5），其余单元格复制粘贴。
此公式用于引用“1信息录入”工作表中的数据，也可用于将多个单元格数据合并为一个单元格。
“D5:
D203”是否为班主任区域：
在D5单元格输入公式：
=IF（ISERROR（LOOKUP（1,0/（'1信息录入'!
$J$5:
$J$54=B5）,'1信息录入
'!
$I$5:
$I$54））,"",LOOKUP（1,0/（'1信息录入'!
$J$5:
$J$54=B5）,'1信息录入
'!
$I$5:
$I$54）），此公式中运用“LOOKUP”的逆向查找功能，在信息录入工作表中查找班主任老师所对应的班级，若出现错误时用“IF”和
“ISERROR”函数进行真假判断，若为“假”显示为空白，否则显示为对应的班级。
注意公式中的绝对应用符号“$”不可忽视。
最后D5至D203单元格复制此公式即可。
“E5:
E203”班主任折算课时区域：
在E5单元格输入公式：
=IF（OR（B5="",D5=""）,"0",$E$4），此公式中“OR”指“或者”的意思，
意义为如果满足B5=””或D5=””中的任何一个条件，都显示为“0”，否则显示为“E4”单元格的内容。
以下单元格复制即可。
“H5:
H203”标准课时区域：
在H5单元格输入公式：
=IF（B5="","",IF（C5="是",（$E$204+$G$204+'1信息录入'!
$AD$2）/'1信息
录入'!
$L$2-$E5-$G5,""）），此公式用于计算标准课时，计算方式为总课时+班主任总课时+兼职总课时，然后除以上课人数，最后减去班主任课时或兼职课时。
其余单元格复制即可。
“IF:
I203”计划课时区域：
在I5单元格输入公式：
=IF（$B5="","",SUMIF（$M$5:
$N$54,$B5,$N$5:
$N$54）+SUMIF（$O$5:
$P$54,$B5,$P$5:
$P$54）+SUMIF（$Q$5:
$R$54,$B5,$R$5:
$R$54）+SUMIF（$S$5:
$T$54,$B5,$T$5:
$T$54）+SUMIF（$U$5:
$V$54,$B5,$V$5:
$V$54）+SUMIF（$W$5:
$X$54,$B5,$X$5:
$X$54）+SUMIF（$Y$5:
$Z$54,$B5,$Z$5:
$Z$54）+SUMIF（$AA$5:
$AB$54,$B5,$AB$5:
$AB$54）+SUMIF（$AC$5:
$AD$54,$B5,$AD$5:
$AD$54）+SUMIF（$AE$5:
$AF$54,$B5,$AF$5:
$AF$54）+SUMIF（$AG$5:
$AH$54,$B5,$AH$5:
$AH$54）+SUMIF（$AI$5:
$AJ$54,$B5,$AJ$5:
$AJ$54）+SUMIF（$AK$5:
$AL$54,$B5,$AL$5:
$AL$54）+SUMIF（$AM$5:
$AN$54,$B5,$AN$5:
$AN$54）+SUMIF（$AO$5:
$AP$54,$B5,$AP$5:
$AP$54）+SUMIF（$AQ
$5:
$AR$54,$B5,$AR$5:
$AR$54）），此公式中使用“SUMIF”条件求和公
式，用于统计教师任教不同科目的课时数总和。
其余单元格复制即可。
“J5:
J203”差值区域：
在J5单元格输入公式：
=IF（B5="","",IF（C5="是",I5-H5,""）），此公式中使用了IF的多层嵌套功能，其意义为，当B5单元格为空白时显示为空，当C5单元格显示“是”时，显示为I5-H5，否则为空。
其余单元格复制填充。
“K5:
K203”标识码区域：
在K5单元格输入公式：
=IF（J5<-1,"",IF（J5>1,"","√"）），此公式用法同上，用于判断“差值”是否在-1和1之间，如果是显示为“√”，否则显示为空。
这种设计便于及时掌握教师工作量是否达到标准工作量。
其余单元格复制填充。
“L5:
L203”年级区域：
在L5单元格输入：
=CONCATENATE（'1信息录入'!
I5），直接引用信息录入工作表中的年级信息。
“N、P、R、T、V、X、Z、AB、AD、AF、AH、AJ、AL、AN、AP、
AR”列课时区域：
在N5单元格输入=IF（$L5="","",INDEX（'1信息录入
'!
$I$4:
$AC$54,MATCH（$L5,'1信息录入'!
$I$4:
$I$54,0）,MATCH（M$3,'1信
息录入'!
$I$4:
$AC$4,0））），意义说明，先进行真假判断，若L5单元格为空，则显示为空，否则根据INDEX函数，引用信息录入表中行列交叉单元格的数据，即不同年级不同科目的课时数。
其他单元格复制填充。
“M3:
AQ3”课程行输入：
M3单元格输入：
=VLOOKUP（COLUMN（）/2-5,'1信息录入'!
$E$5:
$F$24,2），此公式中运用了COLUMN（）列号引用公式，然后根据VLOOKUP函数引用信息录入表中设置的课程。
例如，M3单元格所在列数COLUMN（）值为13，然后除以2，所得商减去5结果是1，然后根据VLOOKUP查找信息录入表中序号为1的第2列所对应的课程。
其他单元格复制此公式。
“AK2”标识码单元格输入公式=IF（I204=H204,"√","×"），其意义为，当“标准课时数”等于“计划课时数”时显示为“√”，否则显示为“×”。
此公式用于快速判断课程是否全部安排，为排课者提供方便。
使用中只有当标识码显示为“√”时，才能进入下一环节的设置，切记。
3.设置数据有效性。
在F2单元格打开数据有效性按钮，数据来源为B3:
B203。
4.设置条件格式。
选择数据区域B5:
B203，在开始菜单中打开条件格式按钮，选择“只为包含以下内容的单元格设置格式”，当单元格值等F2时，背景色显示为“红色”。
这样的设置便于快速查看教师的具体任课安排。
5.保护工作表。
当工作表背景和公式输入完毕后，对部分单元格公式进行锁定，以防他人随意更改，造成排课的混乱。
方法如下：
全选整个工作表，在单元格格式保护的“锁定”和“隐藏”前打钩，确定。
然后选择活动的单元格，如“F2”单元格和表格中的“兼职工作列”和“教师列”，取消格式保护中“锁定”和“隐藏”前的钩，确定。
最后选择审阅菜单，点击“保护工作表”按钮，输入管理密码。
（三）“3总课程表”工作表。
1.总课程表是整个排课系统的核心。
当信息录入和任课设置输入完毕后，即可进行总课程表的编排。
首次使用工作量较大，以后将会十分方便，只须轻点鼠标，对个别内容进行简单处理即可完成总课表。
具体设置见图3。
2.写入表头和输入公式。
（1）“课节”行中数字的说明：
例如11，指星期一第一节，25
指星期二第五节，以此类推。
（2）“B3”单元格：
=CONCATENATE（'1信息录入'!
C2）
（3）“L3”单元格：
=CONCATENATE（'1信息录入'!
O2）
（4）“AG1”单元格，此处数据来源为“课表检验”工作表，公式同上。
（5）“A7:
A106”区域：
A7单元格写入公式：
=IF（MOD（ROW（）,2）=1,VLOOKUP（（ROW（）+1）/2-3,'1信息录入
'!
$H$5:
$I$54,2）,"教师"），此公式中ROW（）用于引于当前单元格所在的行号，MOD（ROW（）,2），用于计算当前单元格所在行号除以2后的余数，整个公式中，如果余数为1，则引用“信息录入”表中设置的年级，否则显示为“教师”。
其余单元格复制此公式进行填充。
（6）“教师”所在行区域：
在“B8”单元格输入公式
=IF（B7="","",INDEX（'2任课设置'!
$L$3:
$AR$54,MATCH（$A7,'2任课设置
'!
$L$3:
$L$54,0）,MATCH（B7,'2任课设置'!
$L$3:
$AR$3,0）））。
此公式中运用
INDEX+MATCH公式，用于提取“任课设置”工作表中“七年级1班”与“11”交叉单元格的数据，即七年级1班在星期一第一节课的教师。
其余单元格复制此公式填充。
（7）“计划课时”列区域：
在AL7单元格输入=IF（OR（$B7="",$A7="教师"）,"",INDEX（'1信息录入'!
$I$4:
$AC$54,MATCH（$A7,'1信息录入
'!
$I$4:
$I$54,0）,MATCH（$B$1,'1信息录入'!
$I$4:
$AC$4,0））），此公式中先
进行真假判断，如果B7=空白或A7=教师，则显示为空。
否则从信息录入工作表中提取“七年级1班”和“任意课程”交叉单元格的数据，作为计划课时数。
其余单元格复制此公式予以填充。
另外，此表中的
B1为活动单元格，可以点开下拉菜单随意选择不同课程。
T1亦为活动单元格，用于选择任意教师，下文会讲到。
（8）“实际课时”列区域：
例如在AM7单元格输入公式
=COUNTIF（B7:
AJ7,$B$1），用于统计本行课程中包含B1课程的个数。
其中B7:
AJ7为相对应用，即为活动区域，$B$1为绝对应用，请注意。
其他单元格复制填充。
3.在B1、T1单元格，各班课程单元格设置下拉菜单样式。
（1）在总课程表右侧空白处添加“课程”列和“教师”列，利用
CONCATENATE函数，引用“信息录入”工作表中的所有“课程”和
“教师”。
（2）在B1单元格设置数据有效性，数据来源为刚才设置的“课程列”，背景为绿色。
在T1单元格设置数据有效性，数据来源为“教师列”，背景为蓝色。
设置完毕预览效果。
（3）在各班课程行设置数据有效性，例如B7单元格，数据来源为“课程列”，背景色为“土黄色”，其余班级和区域复制此单元格。
4.设置“备注”行信息。
在B6中写入：
=IF（COUNTIF（B7:
B106,$T$1）>=2,"重",COUNTIF（B7:
B106,$T$1）），意义说
明：
先判断所在列中的任意“教师”数是否大于等于2，如果有则显示“重”，否则显示统计的个数。
“B6:
AJ6”区域其他单元格复制此公式填充。
“备注”区域为教师是否存在重课现象进行提示。
5.设置条件格式。
（1）选择“B6:
AJ6”，点击条件格式按钮，只为包含以下内容的单元格设置格式，当单元格值为“重”时，背景设置为“红色”。
（2）选择“B7:
AJ106”，点击条件格式按钮，只为包含“B1”单元格内容的设置背景为“绿色”，为包含“T1”单元格内容的设置背景为“蓝色”。
（3）选择“AL7:
AM7”，设置条件格式为值为“唯一”时，背景色为“粉红色”，以下区域选择格式刷，应用此设置。
6.在“总课程表”下面空白处新建附表1，见图4。
此表数据为下一工作表“课表检验”，“是否重课”列提供服务。
（1）“A111:
A310”区域（姓名列）：
在A111输入公式
=CONCATENATE（'1信息录入'!
B5），以下复制此公式填充。
（2）“B111:
B310”区域写入公式：
在B111输入
=IF（$A111="","",COUNTIF（B$7:
B$106,$A111）），此公式中用CLUNTIF
函数统计教师在当节课中的课时数。
其他区域复制此公式填充。
（3）“AK111:
AK310”区域：
在“AK111”中写入公式
=IF（COUNTIF（B111:
AJ111,">=2"）>=1,"重",""），说明：
当本行数据中存在
大于等于2时显示为“重”，否则显示为空，用于判断教师是否存在重课现象。
其余复制此公式填充。
7.在“总课程表”右侧空白处新建附表2，见图5。
此表数据为下一工作表“课表检验”中的“实际课时”行提供服务。
（1）“AR7:
AR106”区域写入公式：
例如AR7单元格，
=IF（MOD（ROW（）,2）=1,VLOOKUP（（ROW（）+1）/2-3,'1信息录入
'!
$H$5:
$I$54,2）,"教师"），此公式同“总课程表”第1列。
（2）“AS5:
BM5”区域写入公式：
例如AS5单元格，
=VLOOKUP（COLUMN（）-44,'1信息录入'!
$E$5:
$F$24,2），此公式用来引用
“信息录入”工作表中的“课程”。
（3）“AS7:
BM106”区域写入公式，例如AS7单元格，
=COUNTIF（$B7:
$AJ7,AS$5），此公式用来统计“总课表”中同一年级课程的个数。
其他复制填充。
8.保护工作表。
（1）全选工作表，设置单元格格式，在保护菜单的“隐藏”和“锁定”前打钩，然后确定。
（2）选择活动单元格和区域。
例如B1、T1、各年级的课程“行”，设置单元格格式，取消“隐藏”和“锁定”前的钩即可。
（3）打开“审阅”菜单下的“保护工作表”按钮，设置管理密码。
（四）“4课表检验”工作表。
1.“课表检验”为总课程表的编排提供服务。
在此工作表中可以直观的查看每个教师是否存在重课现象、每天的课时数多少、总课时数是否达到计划课时数、全校是否存在空课现象，最后自动判断排课是否成功。
模板见图6。
2.写入公式。
（1）“A4:
B203”区域：
先在A4单元格输入：
=CONCATENATE（'1信息录入'!
A5），此公式用于引用“信息录入表”中的序号。
其他复制此公式填充，引用教师。
（2）“C4:
C203”区域：
先在C4单元格输入：
=IF（B4="","",VLOOKUP（B4,'2任课设置'!
$B$5:
$I$203,8,0）），其意义为先
进行判断，当B4格为空时显示空白，否则根据列查找公式VLOOKUP，查找“任课设置”表中B5:
I203区域第8列对应的数据，即计划课时数。
以下单元格复制填充。
（3）“D4:
H203”区域：
“D4”公式：
=IF（B4="","",COUNTIF（'3总课程表'!
$B$4:
$H$106,$B4））
意义为统计“总课程表”中B4:
H106区域，B4对应“教师”的个数。
“E4”公式=IF（B4="","",COUNTIF（'3总课程表'!
$I$4:
$O$106,$B4））
“F4”公式=IF（B4="","",COUNTIF（'3总课程表'!
$P$4:
$V$106,$B4））
“G4”公式=IF（B4="","",COUNTIF（'3总课程表'!
$W$4:
$AC$106,$B4））
“H4”公式=IF（B4="","",COUNTIF（'3总课程表'!
$AD$4:
$AJ$106,$B4））
以上公式意义相同，其余各列复制此公式填充。
（4）“I4:
I203”区域：
“I4”公式=SUM（D4:
H4），计算D:
H列之和。
（5）“J4:
J203”区域，在J4单元格输入：
=VLOOKUP（B4,'3总课程表'!
$A$110:
$AK$309,37,0），意义为根据列查找公式VLOOKUP，查找
“总课程表”下面“附表”中“A110:
AK306”区域第37列。
其他单元格复制此公式填充。
此时可以隐藏“总课程表”下侧的附表。
（6）“K4:
K203”区域：
在“K4”输入=IF（B4="","",IF（AND（C4=I4,J4=""）,"
√","错误"）），其意义为先进行判断，当B4为空时显示为空，当C4=I4
且J4为空时，显示为“√”，否则显示为“错误”。
此公式中运用了
IF嵌套功能，用于判断每个教师排课是否正确。
以下单元格复制此公式填充。
（7）“M4:
M203”区域：
“M4”输入=VLOOKUP（（ROW（）-1）/3,'1
信息录入'!
$H$5:
$I$54,2），其意义为先引用所在单元格的行号，然后根据列查找公式，查找信息录入表中对应的年级。
以下复制填充。
（8）“O4:
AH6”区域：
“教师”行，“O4”单元格输入=IF（$M4=0,"",INDEX（'2任课设置
'!
$L$3:
$AR$54,MATCH（$M4,'2任课设置'!
$L$3:
$L$54,0）,MATCH（O$3,'2
任课设置'!
$L$3:
$AR$3,0）））
“计划课时”行，“O5”单元格输入=IF（$M4=0,"",INDEX（'1信息录
入'!
$I$4:
$AC$54,MATCH（$M4,'1信息录入'!
$I$4:
$I$54,0）,MATCH（O$3,'1
信息录入'!
$I$4:
$AC$4,0）））
“实际课时”行，“O6”单元格输入=IF（$M4=0,"",INDEX（'3总课程表'!
$AR$5:
$BM$106,MATCH（$M4,'3总课程表
'!
$AR$5:
$AR$106,0）,MATCH（O$3,'3总课程表'!
$AR$5:
$BM$5,0）））。
以上
公式中运用了多公式嵌套方法，先进行判断真假，当M4格为空时，显示空，否则根据INDEX+MATCH公式查找“总课程表”右侧附表中行列交叉单元格的值。
先复制此公式填充同一行单元格，然后同时复制三行单元格公式，填充到以下单元格。
此时可以隐藏“总课程表”右侧的统计表。
（9）“C204:
K204”区域。
C204:
I204输入SUM求和公式。
在J204输入：
=COUNTIF（J4:
J203,"重"），用于统计J列中“重”的个数。
在K204输入：
=COUNTIF（K4:
K203,"错误"），用于统计K列中“错误”的个数。
见图7
（10）“D1”单元格，即排课标识。
写入公式，
=IF（AND（C204=I204,J204=0,K204=0）,"排课成功","失败"），此公式中AND
表示“并且”之意，可理解为，当同时满足C204=I204,J204=0,K204=0
三个条件时，显示为“排课成功”，否则显示为“失败”。
3.设置条件格式。
（1）选择J4:
J203，打开开始菜单中的“条件格式”，设置当单元格内容包含“重”时，背景颜色为“黄色”。
（2）选择K4:
K204,打开开始菜单中的“条件格式”，设置当单元格内容包含“错误”时背景颜色为“红色”。
（3）选择O5:
O6，设置当单元格内容为“唯一”值时背景为“玫红色”，其他“计划课时”和“实际课时”单元格利用格式刷应用这一设置。
（4）选择D4:
H4，设置条件格式，当单元格值大于等于4时，背景色为“淡红色”，其目的是为了及时提醒，保证每位老师每天的工作量基本持平，避免课时集中在某天。
4.保护工作表。
“课表检验”工作表无手动输入的信息，全选后，打开单元格格式设置，在保护中的“隐藏”和“锁定”前面打钩。
然后选择“审阅”菜单，在保护工作表中设置管理密码。
（五）“5年级课表”工作表。
1.本工作表用来从“总课程表”中自动提取“年级课表”。
其模板见图8。
2.在课程表模板右侧空白处新建“年级列”和“课节表”见图9。
（1）在“年级”列I5单元格，输入=CONCATENATE（'1信息录入'!
I5），从信息录入工作表中提取“年级”。
以下单元格复制填充。
（2）在“J6:
N20”区域，输入课节数字，例如“11”指星期一第
1节，“21”指星期二第1节，“45指星期四第5节，以此类推。
如
图9所示。
3.在B3单元格设置下拉菜单。
打开“数据有效性”按钮，有效性条件为“序列”来源为H4:
H24。
单元格背景为“黄色”。
此时打开B3单元格下拉菜单，可任意选择
“年级”。
4.在年级课程表写入公式。
（1）在C2单元格写入公式=CONCATENATE（'1信息录入'!
O2），自动引用信息录入表中的学期。
（2）“课程”单元格。
例如星期一第1节课的课程，在B6中写入：
=INDEX（'3总课程表'!
$A$5:
$AJ$106,MATCH（$B$3,'3总课程表
'!
$A$5:
$A$106,0）,MATCH（I6,'3总课程表'!
$A$5:
$AJ$5,
升级会员 