excel查询与引用函数应用实例讲解-——引用总课表数据自动生成教师课表和班级课表.doc

EXCEL查询与引用函数应用实例讲解-引用总课表数据自动生成教师课表和班级课表 EXCEL查询与引用函数应用实例讲解引用总课表数据自动生成教师课表和班级课表 摘要：关键词： 目标任务： 教导处拿来一份手工做排好的课程表，需要输入电脑，再打印总课表、教师个人任课表和各个班级课程表。 工作思路： 用EXEL来处理。总课表结构如图1（局部），第一行为星期一至星期五，第二行为班级名，共17个班，依次为一（1）班至五（4）班，班级名用简称，第三行以下为课程科目及任课教师，任课教师也用简称。 图1 可以从总课表中复制出班级课表，但要生成教师课表就不好复制了，复制不是好办法。我试着用HLKUP、VLKUP和ATH函数实现了自动生成教师课表和班级课表。 设计过程： 现将做法介绍如下。 第一步：将总课表工作表命名为“总课表”，并在第二行插入一行，用于给班级编序，输入1至17，星期一至星期五都这样（这一行隐藏了）。这一步是因为HLKUP要求第1行中的数值必须按升序排列。总课表中每节课有四个“属性”（星期，节次，任课教师，所属班级），星期和班级由列来区分（如A-S列为星期一的课程，列为一1班星期一的课程），节次由行来区分，任课教师放于对应课程下一行。（参看图1） 第二步：建立班级课表工作表，设计好课表格式，内容先为空。如图2左侧是生成后的一（2）班的班级课表。 图2 第三步：建立教师课表工作表，设计好课表格式，内容先为空，如图2右侧是生成后赵H海老师的课表。 第四步：设计班级课表引用公式，使之能根据选择班级而自动生成班级课表。这个过程较复杂，分述如下： 1制作班级数据信息列表。进入“班级课表”工作表，先在A22，B22，22单元格输入“班级简记”、“班级名”，“班级序号”，然后在A23：39区域内输入班级信息。如“一1”、“一（1）班”、“1”为一（1）班的信息。 2制作班级选择下拉列表。在A1单元格设计班级选择输入下拉列表。方法如下： 选中A1，单击数据有效性，在对话框中“设置”选项卡下“允许”选“序列”，然后在“”下输入“=$A$23:$A$39”或选择单元格，表示以A23：A39单元格内数据作为A1的输入源，即班级只能用选择输入，因为总课表中用的班级名是简称，这样做可以防止输入无效的班级简称。 3制作班级课表班级名称显示。将B1：F1合并，用以显示班级名，输入公式“=VLKUP($A$1,A23:39,LUN(B23),0) & 课表”，当A1选择“一1”时，这里显示“一（1）班课表”。 上述函数VLKUP功能说明：从区域A23:39中查找首列中包含单元格$A$1数据的行，再从B列查找对应行的数据，返回该值。例如$A$1数据为“二1”，则从区域A23:39首列中找到是第27行，再从B列查找第27行数据，即B27是“二（1）班”。 当然，由于班级简称与班级名称之间有简单对应关系，也可以用字符串函数来实现，将“二1”替换成“二（1）班课表”。 4制作班级课表课程与任课教师显示。用到HLKUP和ATH组合的应用。以星期一第一节为例，单元格中输入公式“=HLKUP(ATH($A$1,总课表!$3:$S$3,0),总课表!$2:$S$17,R(),0)”。其意义为：先通过ATH函数从总课表$3:$S$3中找出班级课表$A$1所示班级的位置（列号），再从总课表$2:$S$17中查当前行（此处因为总课表区域$2:$S$17中的对应行序刚好跟班级课表的当前行序相同，所以用R函数来返回行位置）对应列的课程，返回该值。 同一天的七节课（包括任课教师名），可以复制公式完成。此时会发现，当某节课未安排时，课程单元格和教师单元格会出现标志“0”。（到第六步再处理） 至此，班级课表已制作完毕。要显示某班的课表，只要从A1单元格选择班级简称，然后自动生成该班的课表。 第五步：设计教师课表引用公式，使之能根据选择教师而自动生成教师课表。这个过程跟制作班级课表类似，详述如下： 1制作教师数据信息列表。进入“教师课表”工作表，先在A22，B22，22，D22单元格输入“简记”、“教师姓名”、“应排节数”、“已排节数”，然后在A23：B64区域内输入教师信息（本例共42位教师，教师姓名都经过处理）。23：64为教师应排课节数，依次输入。D23：D64为统计总课表中已排课的数量，通过公式计算，如D23的公式为“=UNTIF(总课表!$A$4:$Q$17,A23)”，其意义为从总课表区域$A$4:$Q$17中查找A23的教