5.1 教学效果量化评价表
案例背景
一个年级的某一学科一般来说会有几个教师同时进行教学，怎样比较他们的教学效果呢？仅仅用一次考试结果进行横向比较是不全面的。教学效果评价的主要指标之一是历次考试成绩的变化情况。
本案例中我们以某中学高一8个班的语文中考入学成绩和第一学期期末考试成绩为例分别计算名次和标准分，通过观察名次变化和标准分差两种不同角度来评价任课教师的教学效果。
关键技术点
要实现本案例中的功能，学员应该掌握以下EXCEL技术点。
●基础知识 图案
●函数应用 LOOKUP函数 ，IF函数 ，SUM函数， Rank函数
●综述 数组公式
最终效果展示
5.1.1教学效果量化评价（一）
Step 1创建工作簿、重名工作表
创建工作簿“教学效果量化评价表.xls”，然后将工作表“Sheet1”和“Sheet2”重命名为“成绩表“和”评价表“。
Step 2输入学生原始信息和考试成绩
①在工作表“成绩表“的单元格A1,B1,C1,D1分别输入字段名“班级“，”学号“，”中考“，”期末“。
②在单元格区域A2:D467输入全年级8个班466名学生的所在“班级“，”学号“以及中考和高一期末考试成绩.
Step3设计教学效果评价表
①在工作表“评价表“的单元格A1中输入标题”教学效果评价表（一）“，选中单元格区域A1:I1，单击常用工具栏”合并及居中“按钮。
②在单元格区域A2：H2中分别输入字段名“班级“，“学生数“，“任课教师“，“中考平均分“，“期末平均分“，“中考名次“，“期末名次“，“名次差“和”教学效果 “。
③在单元格区域A3:C10分别输入8个班的班级名称，学生人数和任课教师的姓名
④在单元格A11输入“年级平均分“，单元格A12输入“标准差”，分别选中单元格区域A11:C11和A12:C12，单击常用工具栏”合并及居中“按钮。
⑤选中单元格区域A1:I12，按组合键复制，单击单元格A15，按组合键粘贴，将标题行的“（一）“改为”（二）“，将“中考名次“，“期末名次“，“名次差“分别改为”中考标准分“，”期末标准分“和”标准分差“
Step4统计各班中考平均分和期末平均分
①在单元格D3中输入数组公式：“=SUM((成绩表!$A$2:$A$467=评价表!$A3)*成绩表!C$2:C$467)/评价表!$B3 “按组合键确认即可统计出1班中考平均分。
②选中单元格D3，向右拖曳右下角的填充柄至单元格E3即可完成1班
高一期末平均分的统计工作。
③选中单元格区域D3：E3，左键双击单元格E3右下角的填充柄即可完成
2至8班中考和高一期末平均分的统计。
Step5统计各班中考和高一期末平均分在全年级中的名次
①在单元格F3中输入公式：“=RANK(D3,D$3:D$10) “，按键确认即可得到1班中考平均分在全年级的名次。
②选中单元格F3，向右拖曳右下角的填充柄至单元格G3即可完成1班
高一期末平均分在全年级的名次的统计工作。
③选中单元格区域F3：G3，左键双击单元格G3右下角的填充柄即可完成
2至8班中考和高一期末平均分在全年级的名次的统计。
Step6统计各班中考和高一期末平均分的名次差
①在单元格H3中输入公式：“=F3-G3 “，按键确认。
②左键双击单元格H3右下角的填充柄即可完成2至8班“名次差“的统计。
Step7评价教学效果
①在单元格I3中输入公式：“=LOOKUP(H3,{-7,-3,0,1,4;"退步明显","退步","原位","进步","进步显著"})”，按键确认即可完成对1班教学效果的评价。
②左键双击单元格I3右下角的填充柄即可完成2至8班教学效果的评价。
5.1.2教学效果量化评价（二）
在4.6节我们已经学习了标准分的转换方法和应用，大家知道一般来说每次考试的百分制的成绩进行比较是不科学的，因为每次考试的难易程度不同，造成考试结果的平均分和标准差都不同，因此用不同考试的百分制成绩进行比较是不可取的，况且中考语文总分是120分，而高一期末语文考试的总分是100分，这样一来就更不具可比性。但是将其都转换成标注分Z分数后就可以进行比较了。
Step1导入各班中考和高一期末平均分
选中单元格区域D3:E10，按组合键复制，单击单元格D17，按组合键粘贴。
Step2统计年级中考和期末的“平均分”和各班平均分的“标准差”
①在单元格D25中输入公式：“=AVERAGE(D17:D24) “按键确认。
②在单元格D26中输入公式：“=STDEVP(D17:D24) ”按键确认。
③选中单元格区域D25:D26，向右拖曳单元格D26右下角的填充柄至单元格E26即可年级中考和期末的“平均分”和各班平均分的“标准差”的统计
Step3统计各班“中考标准分”和“期末标准分”
①在单元格F17中输入公式：“=(D17-D$25)/D$26” “按键确认即可得到1班中考平均分的标准分。
②向下拖曳单元格F17右下角的填充柄至单元格F24即可得到2班至8班中考平均分的标准分。
③选中单元格区域F17:F24，向右拖曳单元格F24右下角的填充柄至单元格G24即可得到1班至8班高一期末平均分的标准分。
Step4统计“标准分”的差值
①在单元格H17输入公式：“=G17-F17”，按键确认即可得到1班中考平均分的标准分和期末平均分的标准分的差值。
②左键双击单元格H17右下角的填充柄即可完成2班至8班中考平均分的标准分和期末平均分的标准分的差值的统计。
Step5评价教学效果
①在单元格I17输入公式“=IF(H17>0,"进步",IF(H17<0,"退步","原位"))”，按键确认即可完成对1班教学效果的评价。
②左键双击单元格I17右下角的填充柄即可完成2班至8班教学效果的评价。
Step6修饰美化评价表
删除第11行和第12行内容。
②按图25样式为表格设置边框和文字效果
至此教学效果评价全部完成，用两次考试平均分的“名次差”和平均分的标准分的差值从不同角度对教学效果进行评价，两种评价各有千秋，大家从评价结果可以观察出一些问题，大家也可以用自己任教班级的近两次考试成绩模仿进行类似教学效果评价。
关键知识点讲解
LOOKUP函数
函数名称：LOOKUP
主要功能：返回向量（单行区域或单列区域）或数组中的数值。函数 LOOKUP 有两种语法形式：向量和数组。函数 LOOKUP 的向量形式是在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值；函数 LOOKUP 的数组形式在数组的第一行或第一列查找指定的数值，然后返回数组的最后一行或最后一列中相同位置的数值。
向量为只包含一行或一列的区域。函数 LOOKUP 的向量形式是在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值。如果需要指定包含待查找数值的区域，则可以使用函数 LOOKUP 的这种形式。函数 LOOKUP 的另一种形式为自动在第一列或第一行中查找数值。
使用格式1：向量形式
LOOKUP(lookup_value,lookup_vector,result_vector)
参数说明：
Lookup_value 为函数 LOOKUP 在第一个向量中所要查找的数值。Lookup_value 可以为数字、文本、逻辑值或包含数值的名称或引用。
Lookup_vector 为只包含一行或一列的区域。Lookup_vector 的数值可以为文本、数字或逻辑值。
要点 Lookup_vector 的数值必须按升序排序：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE；否则，函数 LOOKUP 不能返回正确的结果。文本不区分大小写。
Result_vector 只包含一行或一列的区域，其大小必须与 lookup_vector 相同。
函数说明
●如果函数 LOOKUP 找不到 lookup_value，则查找 lookup_vector 中小于或等于 lookup_value 的最大数值。
●如果 lookup_value 小于 lookup_vector 中的最小值，函数 LOOKUP 返回错误值 #N/A。
应用示例
主要功能：函数 LOOKUP 的数组形式是在数组的第一行或第一列中查找指定数值，然后返回最后一行或最后一列中相同位置处的数值。如果需要查找的数值在数组的第一行或第一列，就可以使用函数 LOOKUP 的这种形式。当需要指定列或行的位置时，可以使用函数 LOOKUP 的其他形式。
提示
通常情况下，最好使用函数 HLOOKUP 或函数 VLOOKUP 来替代函数 LOOKUP 的数组形式。函数 LOOKUP 的这种形式主要用于与其他电子表格兼容。
使用格式2：数组形式
LOOKUP (lookup_value,array)
参数说明：
Lookup_value 为函数 LOOKUP 在数组中所要查找的数值。Lookup_value 可以为数字、文本、逻辑值或包含数值的名称或引用。
●如果函数 LOOKUP 找不到 lookup_value，则使用数组中小于或等于 lookup_value 的最大数值。
●如果 lookup_value 小于第一行或第一列（取决于数组的维数）的最小值，函数 LOOKUP 返回错误值 #N/A。
Array 为包含文本、数字或逻辑值的单元格区域，它的值用于与 lookup_value 进行比较。
函数说明
函数 LOOKUP 的数组形式与函数 HLOOKUP 和函数 VLOOKUP 非常相似。不同之处在于函数 HLOOKUP 在第一行查找 lookup_value，函数 VLOOKUP 在第一列查找，而函数 LOOKUP 则按照数组的维数查找。
●如果数组所包含的区域宽度大，高度小（即列数多于行数），函数 LOOKUP 在第一行查找 lookup_value。
●如果数组为正方形，或者所包含的区域高度大，宽度小（即行数多于列数），函数 LOOKUP 在第一列查找 lookup_value。
●函数 HLOOKUP 和函数 VLOOKUP 允许按行或按列索引，而函数 LOOKUP 总是选择行或列的最后一个数值。
要点 数组中的数值必须按升序排序：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE；否则，函数 LOOKUP 不能返回正确的结果。文本不区分大小写。
应用示例
|
