生物教学中巧用Excel 2003实现成绩统计自动化

赵亚特

中学生物教育教学评价中经常会涉及到众多的考试，如周考、月考、期中考、期末考等。面对海量的成绩数据，生物老师如何准确快速地进行分析，从而找出教学中和班级管理中的问题，是一线生物教师比较苦恼的事情。Excel 2003作为中学老师比较熟悉的办公软件，虽经常接触，但很少会使用其函数统计功能。下面通过笔者在年级生物成绩统计分析中的摸索和经验，以一个年级（包含4个班）成绩数据分析为例来介绍如何使用Excel 2003做好成绩分析模板，实现对历次生物考试成绩轻松进行分析，利于生物教师找出各班的差异以及几次考试成绩的相对情况。

1 成绩统计分析工作簿模板的建立

建立成绩统计分析工作簿模板，命名为成绩统计分析模板，其中包含两张工作表，一张重命名为成绩登分表，另一张重命名为成绩分析表。

1.1 成绩登分表的建立

切换工作表至成绩登分表，在第一行建立列名，依次为班级、姓名、生物1、生物1排名、生物2、生物2排名等。科目对应列中为学生的原始成绩，如果缺考就在相应的单元格中输入“缺考”。科目排名列中使用排序函数=rank（），如生物1成绩在c列，生物1排名在d列，在d2单元格中则输入函数=rank（c2，c：c），如图1所示。然后将鼠标移至单元格右下角出现“+”时向下拖曳即可得到生物1成绩在全年级相应的排名，其他依次类推，至此成绩登分表就建立完毕。为了方便对成绩列和成绩排名列的引用，如对生物1成绩列进行定义，单击插入菜单，依次选择“名称”—“定义”，弹出对话框如图2所示。在“在当前工作簿中的名称”下面的框里键入生物1，在“引用位置”下面的框中点选框右端的选择按钮，然后点选生物1成绩所在列C，在空白框中显示“=成绩登分表！$C：$C”。由于第一行为标题行，所以将“=成绩登分表！$C：$C”修改为“=成绩登分表！$C2：$C20 000”，根据学生人数可以相应调整其中20 000这个数值。当然值越小，分析速度越快，可以将其设置为稍大于学生人数即可。

1.2 成绩分析表的建立

1.2.1 表头的建立

切换工作表至成绩分析表，首先在第一行键入表头，如“高2016级生物历次成绩分析表”，接下来在键入成绩分析指标，在这里建立分析指标如图3所示。其中的上线人数1和下面的30指的是取前30名，上线人数2和下面的112指的是取前112名，上线率则是用各班上线人数除以各班参考总人数。30和112可以根据要求手动进行更改，变异系数=标准差/平均数，变异系数越大，表明个体差异越大。为了制作一张表中根据需要选择显示历次生物成绩，需要建立下拉单元格，具体操作：① 在一定区域如图4输入科目和科目排名；② 点选图3中生物1所在的单元格点击菜单数据选择有效性，在允许中选择序列，在来源下面的框右端点击选择图4中的左列中生物数据所在区域，即可制作生物历次考试下拉列表，同理制作生物排名下拉列表。

1.2.2 统计指标中用到的函数

在多条件统计人数时常用到的函数“=SUMPRODUCT（）”。多个条件时，条件之间用*连接，如满足两个条件的计数函数“=SUMPRODUCT（（ ）*（ ））”，其中的条件为真时返回值为1，为假时返回值为0，所以只要有一个条件不满足，结果就为0。最后将所有记录的所有的结果相加，即可求出满足条件的所有记录的个数。如在B5单元格中输入函数“=SUMPRODUCT（（成绩登分表！$A$2：$A$20000=$A5）*（INDIRECT（$C$2）<=B$4））”，下拉填充即可得到其他班级的数据，上线人数2的公式编辑同理。

为了使得分析数据随着点选下拉列表的变化而同步变化，用到了一个间接引用函数“INDIRECT（ ）”，如“INDIRECT（$C$2）”的结果就是引用C2单元格中的值所代表的值，当C2显示为语文排名时，它执行的结果就是引用成绩登分表中语文排名中所定义的数据区域。

在F5单元格中输入及格人数统计函数=SUMPRODUCT（（理科！$A$2：$A$20000=$A23）*（INDIRECT（$B$20）>=$H$22））-SUMPRODUCT（（理科！$A$2：$A$20000=$A23）*（INDIRECT（$B$20）="缺考"）），其中由于只用前半部分函数会将“缺考”单元格判断为无穷大而包含在内，所以用后半部分公式将缺考单元格去掉。

为了按照班级统计最高分、最低分和标准差，使用了数组函数，如求最高分在I5单元格输入函数“=MAX（IF（成绩登分表！$A$2：$A$20 000=$A5，INDIRECT（$B$2）））”之后，同时按下“Ctrl+Shift+Enter”三键，就会在原有公式首尾出现“{ }”。同理在J5和K5单元格分别输入数组公式“=MIN（IF（成绩登分表！$A$2：$A$20000=$A5，INDIRECT（$B$2）））”和“=STDEV（IF（成绩登分表！$A$2：$A$20000=$A5，INDIRECT（$B$2）））”，分别求出最小值和标准差，鼠标向下拖曳可求出其他3个班的数据。

在H5单元格中输入平均分统计函数“=SUMIF（成绩登分表！$A$2：$A$20 000，$A5，INDIRECT（$B$2））/M5”，其中M5为各班的参考人数，鼠标向下拖曳可求出其他3个班的数据。

在M5单元格中输入计数函数“=SUMPRODUCT（（成绩登分表！$A$2：$A$20 000=$A5）*（INDIRECT（$B$2）<>“缺考”））”。

在N5单元格中输入计数函数“=COUNTIF（成绩登分表！$A：$A，$A5）”求出总人数，鼠标向下拖曳可求出其他3个班的数据。

2 成绩统计分析工作簿模板的使用

2.1 原始成绩的录入及缺考标记

在成绩登分表科目列中登入原始成绩后，排名列会更新排名情况，缺考可能是全部科目都缺考，也可能只有某一门或几门缺考，在缺考考生记录相应缺考科目及排名单元格中输入“缺考”作为标记。

2.2 自动化成绩统计分析

在成绩分析表中，可以根据需要更改名次，如取前20名，则将30更改为20，上线分数2的修改同理。及格人数下面的90是针对150分满分设置的，如果是100分满分，则将其改为60即可。

比较4个班生物1成绩情况，可以在B2单元格下拉列表中点选“生物1”，在C2单元格下拉列表中点选“生物1排名”，结果如图5所示。要比较生物2则相应点选“生物2”和“生物2排名”，稍等片刻会计算最终显示结果如图6。由于有重名次，所以取前30名，实际的结果有32人。

3 成绩统计分析工作簿模板的注意事项

3.1 成绩登分表的保护

在每次考试时，由于有的考生退学或者全部科目缺考，这时为了不显示这些记录只能选定相应内容进行清除内容操作，而不能删除相应行或列破坏表格，一旦表格被破坏，所有有关间接引用的函数计算结果都会报错。

3.2 成绩分析表的保护

成绩分析表建立好以后，由于公式众多，为了保证公式不被失误操作而修改，可以点选相应的公式区域，然后点击格式菜单选择单元格，切换到保护选项卡，点选“锁定”和“隐藏”。确定后，点击工具“菜单”选择“保护”—“工作表保护”，即可保护公式的正确性，从而多次重复使用。