当前位置:首页 > 计算机 > 计算机应用 >

EXCEL查询与引用函数应用实例讲解 ——引用总课表数据自动生成教

分享到:
作者:管理员。 TAGS:引用,数据,自动,讲解,生成,应用,
   目标任务: 教导处拿来一份手工做排好的课程表,需要输入电脑,再打印总课表、教师个人任课表和各个班级课程表。 工作思路: 用EXCEL来处理。总课表结构如图1(局部),第一行为星期一至星期五,第二行为班级名,共17个班,依次为一(1)班至五(4)班,班级名用简称,第三行以下为课程科目及任课教
师,任课教师也用简称。

图1
  可以从总课表中复制出班级课表,但要生成教师课表就不好复制了,复制不是好办法。我试着用HLOOKUP、VLOOKUP和MATCH函数实现了自动生成教师课表和班级课表。
  设计过程:
  现将做法介绍如下。
  第一步:将总课表工作表命名为“总课表”,并在第二行插入一行,用于给班级编序,输入1至17,星期一至星期五都这样(这一行隐藏了)。这一步是因为HLOOKUP要求第1行中的数值必须按升序排列。总课表中每节课有四个“属性”——(星期,节次,任课教师,所属班级),星期和班级由列来区分(如A-S列为星期一的课程,C列为一1班星期一的课程),节次由行来区分,任课教师放于对应课程下一行。(参看图1)
  第二步:建立班级课表工作表,设计好课表格式,内容先为空。如图2左侧是生成后的一(2)班的班级课表。
 
  图2
  第三步:建立教师课表工作表,设计好课表格式,内容先为空,如图2右侧是生成后赵H海老师的课表。
  第四步:设计班级课表引用公式,使之能根据选择班级而自动生成班级课表。这个过程较复杂,分述如下:
  1.制作班级数据信息列表。进入“班级课表”工作表,先在A22,B22,C22单元格输入“班级简记”、“班级名”,“班级序号”,然后在A23:C39区域内输入班级信息。如“一1”、“一(1)班”、“1”为一(1)班的信息。
  2.制作班级选择下拉列表。在A1单元格设计班级选择输入下拉列表。方法如下:
  选中A1,单击[数据][有效性],在对话框中“设置”选项卡下“允许”选“序列”,然后在“来源”下输入“=$A$23:$A$39”或选择单元格,表示以A23:A39单元格内数据作为A1的输入源,即班级只能用选择输入,因为总课表中用的班级名是简称,这样做可以防止输入无效的班级简称。
  3.制作班级课表班级名称显示。将B1:F1合并,用以显示班级名,输入公式“=VLOOKUP($A$1,A23:C39,COLUMN(B23),0) & \"\"课表\"\"”,当A1选择“一1”时,这里显示“一(1)班课
[论文网 lunwen.nangxue.com]表”。
  上述函数VLOOKUP功能说明:从区域A23:C39中查找首列中包含单元格$A$1数据的行,再从B列查找对应行的数据,返回该值。例如$A$1数据为“二1”,则从区域A23:C39首列中找到是第27行,再从B列查找第27行数据,即B27是“二(1)班”。
  当然,由于班级简称与班级名称之间有简单对应关系,也可以用字符串函数来实现,将“二1”替换成“二(1)班课表”。
  4.制作班级课表课程与任课教师显示。用到HLOOKUP和MATCH组合的应用。以星期一第一节为例,单元格中输入公式“=HLOOKUP(MATCH($A$1,总课表!$C$3:$S$3,0),总课表!$C$2:$S$17,ROW(),0)”。其意义为:先通过MATCH函数从总课表$C$3:$S$3中找出班级课表$A$1所示班级的位置(列号),再从总课表$C$2:$S$17中查当前行(此处因为总课表区域$C$2:$S$17中的对应行序刚好跟班级课表的当前行序相同,所以用ROW函数来返回行位置)对应列的课程,返回该值。
  同一天的七节课(包括任课教师名),可以复制公式完成。此时会发现,当某节课未安排时,课程单元格和教师单元格会出现标志“0”。(到第六步再处理)
  至此,班级课表已制作完毕。要显示某班的课表,只要从A1单元格选择班级简称,然后自动生成该班的课表。
  第五步:设计教师课表引用公式,使之能根据选择教师而自动生成教师课表。这个过程跟制作班级课表类似,详述如下:
  1.制作教师数据信息列表。进入“教师课表”工作表,先在A22,B22,C22,D22单元格输入“简记”、“教师姓名”、“应排节数”、“已排节数”,然后在A23:B64区域内输入教师信息(本例共42位教师,教师姓名都经过处理)。C23:C64为教师应排课节数,依次输入。D23:D64为统计总课表中已排课的数量,通过公式计算,如D23的公式为“=COUNTIF(总课表!$A$4:$CQ$17,A23)”,其意义为从总课表区域$A$4:$CQ$17中查找A23的教师简称的数量。COUNTIF为条件计数函数。
  2.制作教师选择下拉列表。在A1单元格设计教师选择输入下拉列表。方法如下:
  选中A1,单击[数据][有效性],在
  • 共2页:
  • 上一页
  • 1
  • 2
  • 下一页