|
利用 Excel 实现成绩的自动统计 每当学期结束，试卷评改完毕，我校都要对各个年级各科目成绩进行统计和分析，以便期末进行评先评优、成绩考核、奖惩等事宜，其中包括各科目的平均分、及格率、优生率等项目。以前通过手工计算，但效率低，工作量大，后来用一些成绩统计软件，工作轻松了，但不能很好的满足需要。 经过一段时间摸索，利用 Excel 函数功能，很好的解决了我校成绩统计的各种问题。下面我将就如何利用 Excel 实现成绩的自动统计进行简单的描述。 一、 需求分析 在各科成绩统计和分析的项目包括参考人数、平均分、及格人数、及格率、优生率、优生人数、各分数段以及每科的最高分，为了更好的作为资料保存，还要加上每班的班主任和任课教师。最终成绩统计表格 ( 语文科，其它类似 ) 如下图： 二、 总体设计 新建空白Excel工作簿，将自动建立的Sheet1、Sheet2和 Sheet3工作表重命名为成绩分析表、TSCJ和Teacher， 其中TSCJ工作表用于输入各科成绩，Teacher工作表用于输入教师信息。 （一）、定制TSCJ工作表 在TSCJ工作表第一行中输入统计名称，如“中心校2004-2005学年上期各科成绩统计”，在第二行从A1单元格开始顺序输入记录字段名：学号、姓名、考号、班级、语文、数学、外语、政治、历史、地理、生物和总分。 （二）、定制成绩统计和分析工作表 对成绩统计和分析工作表的定制多而复杂，下面就以语文科统计表定制为例进行描述，其它科的统计类比操作。 1、 定制语文科统计表基本框架 将单位格A1：N1合并，并在单位格中输入=”TSCJ!A 1” 得到统计名称。再合并单位格A2：G2，在单位格输入“= TSCJ!D 1” 得到统计科目，即“语文”。参考上图，定制其它单位格。 2、求语文科各班参考人数 首先在B4单元格中输入“=COUNTIF(TSCJ!$C$2: TSCJ!$C$1501,= & A4)”，其中COUNTIF函数用于计算给定区域内满足特定条件的单元格的数目，通过COUNTIF函数求出1班的参考人数。然后选中B4单元格，拖动B4单元格右下角的小“十”字图标至B7单元格（这种操作本文统称为向下自动填充操作），这样，就能自动得到B5至B7单元格的公式。最后在B8单元格中输入“=SUM(B4:B8)”，用于统计全年级参考总人数。 3、 求语文科各班平均分 首先在C4单元格中输入“=IF(B4=0,0,SUMIF(TSCJ!$C$2: TSCJ!$C$1501,= & A4, TSCJ!$E$2: TSCJ!$E$1501)/B4) ”，其中SUMIF函数功能是根据指定条件对若干单元格求和。为了避免分母为 0 的错误产生，单元格中用到了 IF 函数。该单元格公式可理解为：若 1 班参考人数不为 0 ，则把 1 班语文科总分除以参考人员得到平均分。然后对 C4 至 C7 单元格进行向下自动填充操作，就可以自动得到 C5 至 C7 单元格的公式。最后在 C8 单元格中输入“ =IF(B7=0,0,SUM( TSCJ !$D$2: TSCJ !$D$1501)/B7) ”，求出全年级平均分。 4 、求语文科各班及格人数 在求各班及格人数时，利用 COUNTIF 函数功能比较难满足设计要求，为了解决这种多条件的计算功能，使用了数组公式进行多重标准单元计算。首先在 D4 单元格中输入“ =SUM(( TSCJ !$C$2: TSCJ !$C$1501=A4)*( TSCJ !$E$2: TSCJ !$E$1501>=60)) ”，按下SHIFT＋CTRL键敲回车(这是生成数组公式的关键环节)，Excel会自动在公式两侧加上大括号,生成数组公式。然后对 D4 至 D7 单元格进行向下自动填充操作。最后在 D8 中输入“ =SUM(D4:D7) ”，求出全级及格人数。 5 、求语文科各班合格率 首先在E4单元格中输入“=IF(B4=0,0,D4/B4)* 100” ，为避免分母为O的错误，用了IF函数。公式可以理解为：若1班参考人数不为0，则合格率为合格人数除以参考人数后乘以100。然后对 E4 至 E7 单元格进行向下自动填充操作。 6 、求语文科各班优生人数 我校对分数高于或等于80分的学生称为优生。首先在 H4 单元格中输入“ =SUM(( TSCJ !$C$2: TSCJ !$C$1501=A4)*( TSCJ !$E$2: TSCJ !$E$1501>=80)) ”，按下SHIFT＋CTRL键敲回车(这是生成数组公式的关键环节)，Excel会自动在公式两侧加上大括号,生成数组公式。然后对 H4 至 H7 单元格进行向下自动填充操作。最后在H24单元格中输入“=SUM(H4:H7)”，以求得全年级优生人数。 7、求语文科各班优生率 首先在I4单元格中输入“=IF(B4=0,0,H4/B4)* 100” ，为避免分母为O的错误，用了IF函数。公式可以理解为：若1班参考人数不为0，则优生率为优生人数除以参考人数后乘以100。然后对 I4 至 I7 单元格进行向下自动填充操作。 8、求语文科各班小于分数段人数 在 J4 输入“ =SUM(( TSCJ !$C$2: TSCJ !$C$1501=A4)*( TSCJ !$E$2: TSCJ !$E$1501<60)) ”，按下SHIFT＋CTRL键敲回车，然后对 J4 至 J23 单元格进行向下自动填充操作。，然后在J24单元格中输入“=SUM(J4:J7)”，以求得全年级小于分数段人数。 9、求语文科各班60-70分数段人数 求语文科各班60-70分数段人数，将用到三个条件进行标准单元计算。首先在 K4 单 =SUM(( TSCJ !$C$2: TSCJ !$C$1501=A4)*( TSCJ !$E$2: TSCJ !$E$1501<70)*( TSCJ !$E$2: TSCJ !$E$1501>=60)) ”，按下SHIFT＋CTRL键敲回车，生成数组公式。然后对 K4 至 K7 单元格进行向下自动填充操作。最后在 K8 单元格中输入“ =SUM(K4:K7) ”，求出全年级 60-70 分数段人数。 同样的操作，可以分别统计各班语文科共它各分数段人数和总分各分数段人类。参考 8 、 9 进行。 10 、 求语文科各班最高分 在T4输入“ =MAX(IF(TSCJ!$C$2: TSCJ!$C$1501=A4, TSCJ!$D$2: TSCJ!$D$1501))” 按下SHIFT＋CTRL键敲回车，生成数组公式。然后对T4至T23单元格进行向下自动填充操作。最后在 T8 单元格中输入“ =MAX(T4:T7) ”，求出全级最高分。 （三）定制 Teacher 工作表 在 Teacher 第一行依次输入：班级、语文、数学、英语、政治、历史、地理、生物和班主任。 1、 求各科任课教师 在成绩统计工作表的 N4 单位格输入 ”=Teacher!B 2” , 然后选中N4单元格，拖动N4单元格右下角的小“十”字图标至N7单元格. 2、 求各班班主任 在成绩统计工作表的 N52 单位格输入 ”=Teacher!B 2” , 然后选中N52单元格，拖动N4单元格右下角的小“十”字图标至N55单元格. 三 保护成绩统计工作表 为了维护数据的安全性，防止非法用户善自有意或无意更改成绩数据，我们对成绩统计工作表进行加密保护。单击菜单“文件→工具→保护→保护工作表”，输入两次密码。以后如果想改动统计方法，必须输入密码才行。最后保存工作表，命名为“成绩自动统计Excel表”。 通过上述操作，当我们要进行学校成绩统计时，只要打开“成绩自动统计Excel表”，然后在TSCJ工作表和 Teacher 工作表中分别输入各科成绩和教师信息，而成绩对应的各种统计则默默地自动执行，不需要人的参与。 宜宾县龙池中心学校 刘万雄 Email:ybjkx98@ynmail.com Phone:13198324380 利用Excel实现成绩的自动统计
|
