内容发布更新时间 : 2024/12/28 21:11:55星期一 下面是文章的全部内容请认真阅读。

两个公式搞定班级、教师课程表

在编制完总课表后，教务科还要打印各个班级课表，教师课程表分发给各位班主任和教师，传统的方法是对照总课表抄抄写写，有没有比较高效的技巧？

启动Excel 2007，打开“课程表.xlsx”文件，切换到“总课表”工作表，共14个班级，每节课都有对应的课程和教师（图1）。

一、制作班级课程表

1、准备工作

建立两个工作表，分别是“班主任”工作表（图2）和“班级课表”工作表（图3），设置好“班级课表”工作表的行高、列宽、字体、字号、对齐和边框，此外还要对页面布局进行必要的设置。

选中“班主任”工作表A2: A15单元格区域，在名称框输入“banji”（不包含引号）按回车键确认，这样就对各个班级定义了一个名称。

切换到“班级课表”工作表，单击E2单元格，输入公式：=IF(C2=\班主任!$A$2:$B$15,2,0))

2、设置数据有效性

单击C2单元格，切换到“数据”选项卡，在“数据工具”组中单击“数据有效性”图标，弹出“数据有效性”对话框。在“设置”选项中，“允许”下拉列表中选择“序列”，输入来源为= banji，单击“确定”按钮完成设置。接下来的工作就是如何从“总课表”工作表中提取每一个班级的课程及教师啦。

3、一个公式提取课程和教师

单击D4单元格，输入公式=IF($C$2=\总课表!$C$3:$BT$15,ROW(2:2),MATCH(班级课表!D$3,总课表!$C$2:$BT$2,0)+MATCH($C$2,总课表!$C$3:$P$3,0)-1))。

或者=IF($C$2=\总课表!$C$3:$BT$15,ROW(A2),MATCH($C$2,总课表!$C$3:$BT$3,0)+(COLUMN(D:D)-4)*14))，数字14表示14个班级。

单击D4单元格，将填充柄右拉下拉拖到H15单元格即可。

OK，到此算是大功告成了，现在单击C2单元格，在下拉列表中选择相应班级，该班级的班主任、课程及上课的教师一览无余，再也不需要对照“总课表”逐一抄写各个班级的课程表了，是不是很方便呢？

二、制作教师个人课程表 1、准备工作

建立两个工作表，分别是“教师清单”工作表（图4）和“班级课表”工作表（图5）

如何通过“教师”工作表取得不重复的教师名单？较简单的方法是把“教师”工作表的所有上课教师名单复制到另一个工作表，放在一列，再单击“数据”选项卡的“删除重复项”图标或通过“高级筛选”来实现。

选中“班主任”工作表A2: A54单元格区域，在名称框输入“qingdan”（不包含引号）按回车键确认，这样就对各个班级定义了一个名称。

2、设置数据有效性

单击C2单元格，切换到“数据”选项卡，在“数据工具”组中单击“数据有效性”图标，弹出“数据有效性”对话框。在“设置”选项中，“允许”下拉列表中选择“序列”，输入来源为=qingdan，单击“确定”按钮完成设置。

3、一个公式提取课程和班级

单击D4单元格，输入公式=IF(COUNTIF(OFFSET(总课表!$B$3,MATCH($B4,总课表!$B$4:$B$15,)+1,MATCH(D$3,总课表!$C$2:$BT$2,),1,14),$C$2)=0,\DEX(OFFSET(总课表!$B$3,MATCH($B4,总课表!$B$4:$B$15,),MATCH(D$3,总课表!$C$2:$BT$2,),1,14),MATCH($C$2,OFFSET(总课表!$B$3,MATCH($B4,总课表!$B$4:$B$15,)+1,MATCH(D$3,总课表!$C$2:$BT$2,),1,14),))&CHAR(10)&INDEX(总课表!$C$3:$P$3,MATCH($C$2,OFFSET(总课表!$B$3,MATCH($B4,总课表!$B$4:$B$15,)+1,MATCH(D$3,总课表!$C$2:$BT$2,),1,14),)))

单击D4单元格，将填充柄右拉下拉拖到H9单元格即可。

使用CHAR(10)换行符，必须设置单元格格式”的“对齐”中勾选了“自动换行”才能正常显示。