浅谈教学质量分析中巧用EXCEL实现分类统计
摘 要：数据统计工作在学校教学质量分析过程中是不可缺少的一个环节，数据统计的准确性会直接影响学校领导的决策与思路。当数据统计跨越几个学校许多班级时，如何方便快捷地做到数据处理的准确性、时效性，以提高工作效率?笔者经过了以下一些尝试，现记录如下与大家一起分享。
关键词：学生成绩 质量分析 Excel 分类统计
一、设计背景
学生成绩的统计分析工作由来已久，一般都是一所学校内部分析为校长作决策之用。当数据统计分析扩大范围，涉及几个学校较多班级时，纯手工的成绩统计分析工作就变得复杂而繁多了。成绩统计的内容可分为：分校分年级分班分科平均分及总分平均分、单校分班分科平均分及总分平均分、分校分数段人数统计、前百名学生情况等。为了方便数据的统计与分析，可利用大家常见的办公软件Excel来实现，一个工作簿可以有多个工作表，默认为3个sheet，可以根据需要增减工作表。设计此工作簿的目的是为达到录入学生成绩后立即能得到所有的统计数据，可以减少重复而繁杂的统计工作，更能减少因为误操作引起的错误数据，提高工作效率和准确性。本文以实现统计各校各年级各班各科及总分平均分和各分数段人数的统计为例，叙述了Excel软件的功能应用。
二、思路构建
假设甲校有七、八、九三个年级，每个年级两个班，乙校有七、八、九三个年级，每个年级三个班，丙校有七、八、九三个年级，每个年级四个班，以一校一年级段为单位(方便学校试场的安排以及成绩的录入)，放在一个sheet中。制作工作表如下图1：
为实现统计各校各年级各班各科及总分平均分和各校各年级段分数段的分布情况，制作工作表如下图2：
“甲七”工作表结构如下图3-1、3-2：
图3说明：
假设考试科目为语文、数学、英语、科学等四门，其他年级段工作表的设置依次类推。
字段设置：①学校，用于不同学校之间的对比;②班级，数字格式，用于实现分班成绩的统计;③考号，用于考试的编排和成绩的录入校对。
统计项设：①各科及总分的合计，为方便计算所有学校的平均分，以免减少误差;②各科及总分的平均分，即本校年级段平均分;③分班各科及总分的平均分;④学生总数，亦为方便计算所有学校的平均分，以免减少误差。
“各校各年级各班各科总分平均分”工作表设置如下图4：
八年级、九年级的统计依次类推。
“各校各年级总分的分数段”工作表设置如下图5-1、5-2： 各分数段的分数值设置可以根据各科总分值进行合理调整。
三、过程细化
1、常用函数
充分利用EXCEL公式函数的功能以实现各类数据的统计，是选择EXCEL软件的最初目的.。常用函数有求和函数“SUM( )”、平均值函数“AVERAGE( )”、计数函数 “COUNT( )”、最大值函数“MAX( )”等等。如“甲七”工作表中在计算每个学生总分时用到了求和函数，设置I3单元格的值为 “=SUM(E3:H3)”，结果显示为431(见图3-1)。
2、函数嵌套
EXCEL单元格中很多默认的数字格式在实际应用中都需要进行适当处理。如“甲七”工作表中的E24单元格计算甲校七年级语文成绩的平均分，设置E24的值为 “=ROUND(AVERAGE(E3:E22),2)”，结果显示为94.3(见图3-2)，在用AVERAGE函数进行平均值计算时默认的小数位数不止2位，一般在统计成绩时计算到2位小数即可，因此利用函数“ROUND( )”按指定位数“2”对数值进行“四舍五入”。
3、巧用数组函数
在“甲七”工作表中记录着甲校七年级所有学生的成绩，现在要得到各班的各科平均分，进行教学质量的分析，为了让计算机能自动筛选并统计出结果，字段设计时增加了“班级”这一项，数字格式，有两个目的，一是用来分班统计，二是用来进行学生人数的统计。在E25单元格中计算七1班语文成绩的平均分，设置 E25的值为 “=ROUND(SUMPRODUCT(($B$3:$B$22=1)*E$3:E$22)/COUNTIF($B$3:$B$22,1),2)”，结果显示为90.4(见图3-2)，其中用到了数组函数“SUMPRODUCT()”，当参数“($B$3:$B$22=1)”为逻辑“真TRUE”时，对 E3：E22区域的成绩累计求和，从而实现七1班语文成绩的求和统计，若是统计2班时则参数可修改为“($B$3:$B$22=2)”。
4、绝对引用和相对引用
上述E25单元格公式中使用了“$”绝对引用，目的在于公式拖动时不产生变化，因为这些数据区域是固定的，在参数“E$3:E$22”(成绩区域)的E 前没有使用“$”拖动公式时就会随公式位置变化相对引用。参数“COUNTIF($B$3:$B$22,1)”实现统计1班的学生人数时，区域是固定的，因此使用“$”;同理，统计2班的学生人数可改为“COUNTIF($B$3:$B$22,2)”。
5、表间关联
为了把三个学校的平均分以及各班的平均分情况汇总分析，在“各校各年级各班各科总分平均分”工作中关联各校各年段工作表计算好的数据，如“各校各年级各班各科总分平均分”工作表中B3单元格的值设为“=甲七!E24”，就可以同步关联“甲七”工作表E24单元格的数值94.3(见图4)，C3、……、F3依次类推，同样将B6单元格的值关联“乙七”工作表中相应的单元格，其他单元格也依次类推，这样我们就得到了图4所示的各校各年级各班各科及总分平均分的成绩汇总表。
6、综合应用
分数段人数的统计与对比能很好地反映不同学校之间的成绩差异，毋庸置疑也可对一个学校不同班级之间进行对比，这是众多学校领导分析教学质量情况时采用的一个数据量化的方法。文中“各校各年级总分的分数段”工作表要将三个学校三个年级各分数段的人数进行统计，作横向比较，将G3单元格中的值设置为“=COUNTIF(甲七!$I$3:$I$22,"=430")”，结果显示为5(见图5-1)，表明甲校七年级学生中成绩达到430及以上的人数为5个，在G3单元格的公式中用到了统计函数、绝对引用和表间关联。