EXCEL在多数据成绩统计中的应用

☆周晓敏

（常山县实验小学，浙江常山 324200）

课任教师、教务主任、教研员等教学工作人员常常要进行学生成绩的统计与分析，有时要统计几十、几百、几千甚至上万名学生成绩，而传统的纸质加人工统计的方式，不仅效率低下,而且容易出错。EXCEL是微软办公套装软件的一个重要组件，广泛地应用于学生成绩统计等众多领域。本文将以EXCEL 2007为例，以统计某县学校、班级学生成绩为例来介绍EXCEL在多数据成绩统计中的应用，相信能够对同行在统计成绩方面提供帮助。

一、考试人数统计

1.单条件考试人数统计

要统计分析数据，少不了统计人数。例如：要统计某县小学三年级语文全县各学校考试人数，因全县学生的数据量非常大，每个学校考试人数从一百余人到近千人不等，如果仅仅使用COUNT函数分别对每个学校进行统计，不仅效率低下而且劳形苦心。这时，如果使用COUNTIF函数按照学校来统计考试人数，就会简化、方便很多，具体步骤如下。

（1）根据工作表中的数据建立好统计内容。选中H2单元格，在编辑栏中输入公式：=COUNTIF（B2:B10,G2）,按回车键，即可计算出“城东小学”实考人数。

（提示：为方便显示，例子只列举了有限条数的记录，以下均同。B2:B10表示需要统计的满足条件的单元格区域，G2表示指定的统计条件。）

（2）选中H2单元格，向下复制公式，即可计算出其它学校的考试人数（如图1）。

图1

2.双条件考试人数统计

如果要统计各班考试人数，既要满足学校匹配和班级匹配两个条件，这时使用COUNTIFS函数则较为方便，COUNTIFS函数是EXCEL 2007以后版本新增的多条件统计函数。统计班级考试人数可以使用COUN⁃TIFS函数按如下方法设置求解公式。

（1）根据工作表中的数据建立好统计内容。选中I2单元格，在编辑栏中输入公式：=COUNTIFS（B2:B10,G2,C2:C10,H2）,按回车键，即可计算出“城东小学1班”的实考人数。

（提示：B2:B10表示需要满足的第一个条件单元格区域，G2表示第一个判断条件。C2:C10表示需要满足的第二个条件单元格区域，H2表示第二个判断条件。）

（2）选中I2单元格，向下复制公式，即可计算出各所学校相应的考试人数（如图2）。

图2

二、平均分统计

1.单条件平均分统计

学校学科平均分是评价各学校该学科成绩的一个重要指标，统计学校学生平均分则可以通过AVERAGE函数来计算。而计算对应学校学生的平均分，可以使用AVERAGE函数按如下方法设置求解公式。

（1）选中H2单元格，在编辑栏中输入公式：=AVER⁃AGE(IF(B2:B10=G2,E2:E10))，按“CTRL+SHIFT+EN⁃TER”组合键锁定数组公式，即可计算出“城东小学”学生平均成绩。

（提示：因要通过这个单一的公式，执行多个输入的操作并产生多个结果，所以，要通过“CTRL+SHIFT+ENTER”组合键锁定数组公式。按下“CTRL+SHIFT+ENTER”后，公式变成｛=AVERAGE(IF(B2:B10=G2,E2:E10))｝，这里要注意“{}”不能手工键入，必须按组合键由系统自动产生。）

（2）选中H2单元格，将公式向下填充，即可分别求出其它学校的平均成绩（如图3）。

图3

2.双条件平均数统计

统计班级平均分既要满足学校相同和班级相同两个条件。对于同时满足多个条件的数据求平均值，可以利用AVERAGE函数添加多个条件进行统计。现在利用AVERAGE函数统计各所学校各个班级考生平均分，设置方法如下。

（1）选中I2单元格，在编辑栏中输入公式：=AVER⁃AGE(IF((B2:B10=G2)*(C2:C10=H2),E2:E10))，按“CTRL+SHIFT+ENTER”组合键锁定数组公式，公式变成｛=AV⁃ERAGE(IF((B2:B10=G2)*(C2:C10=H2),E2:E10))｝即可计算出“城东小学1班”学生平均成绩。

（提示：公式中“*”表示将各条件进行相联。）

（2）选中I2单元格，将公式向下填充，即可分别求出各所学校相应班级的平均成绩（如图4）。

图4

三、及格率、优秀率统计

1.双条件及格率、优秀率统计

要统计各学校及格率和优秀率，首先要统计各学校的及格人数与优秀人数，可以通过COUNTIFS函数按照“学校”和“>=60”两个条件来统计，统计方法如下。

（1）选中I2单元格，在编辑栏中输入公式：=COUN⁃TIFS(B2:B10,G2,E2:E10,“>=60”,按回车就可计算出及格人数。

（2）选中I2单元格，将公式向下填充，即可分别求出其它学校的及格人数（如图5）。

图5

（3）选中J2单元格，在编辑栏中输入公式：=I2/H2*100，按回车就可计算出及格率。选中J2单元格，将公式向下填充，即可分别求出其它学校的及格率（如图5）。

同理，可编辑公式求出各学校的优秀人数及优秀率（如图6）。

图6

2.多条件及格率、优秀率统计

班级及格人数、优秀人数可以利用COUNTIFS函数增加条件来统计，每个条件都需要有两个参数，一个是单元格选取，另一个就是判断条件。本例中求及格人数需要满足“学校”、“班级”和“>=60”三个条件。班级及格率按如下方法设置求解公式。

（1）选中J2单元格，在编辑栏中输入公式：=COUN⁃TIFS(B2:B10,G2,C2:C10,H2,E2:E10,">=60"),按回车就可计算出及格人数（如图7）。

（2）选中J2单元格，将公式向下填充，即可分别求出其它各学校各班级的及格人数。

（3）选中K2单元格，在编辑栏中输入公式：=J2/I2*100，按回车就可计算出及格率。选中K2单元格，将公式向下填充，即可求出其它学校各班级的及格率（如图7）。

图7

同理，可编辑公式求出各学校各班级的优秀人数及优秀率（如图8）。

图8

四、名次统计

当计算出各校的平均分、及格率、优秀率之后，可以计算出各校的综合指数，进而根据综合指数，利用RANK函数得出各校的名次。

（1）选中J2单元格，在编辑栏中输入公式：=E2+G2+I2，按回车键确定就可得到“城东小学”的学科综合成绩。

（2）选中J2单元格，将公式向下填充，即可分别求出其它学校的综合指数（如图9）。

图9

（3）选中K2单元格，在编辑栏中输入公式：=RANK(J2,$J$2:$J$10)，按回车确定，得到城东小学的名次。

（提示：因排序的范围J2:J10一定，所以，要绝对引用这个范围，输入公式选中J2:J10，按F4，即可绝对引用这个区域。）

（4）选中K2单元格，将公式向下填充，即可分别求出其它学校名次（如图10）。

图10

五、分段统计

1.单条件分数段统计

在成绩统计与分析中,经常会遇到要将成绩进行分段统计的情况，Excel中分段统计的方法有很多，而本文认为最方便好用的是用FREQUENCY函数来实行分段统计了。它可以只通过一条数组公式就轻松地统计出各分数段的人数分布。例如：要统计出E2:E10区域内相应分数段内的人数分布可以按照如下方法进行。

（1）在工作表中建立数据并输入所有要参与统计的分数段及每个分数段的最高临界数。

（2）用鼠标选择区域I2至I5，在编辑栏内输入公式：=FREQUENCY(E2:E10,H2:H5)。

（3）按“CTRL+SHIFT+ENTER”组合键产生数组公式“{=FREQUENCY(E2:E10,H2:H5)}”，完成后I2:I5显示的数字即各校相应分数段的人数（如图11）。

图11

2.双条件分数段统计

除了对全县成绩进行分数段统计外，有时还要对各校成绩进行分数段统计，这时可利用FREQUENCY函数增加IF条件，进行统计，操作步骤如下。

（1）在工作表中输入所有要参与统计的分数段、每个分数段的最高临界数及学校。

（2）用鼠标选择区域J2至J5，在编辑栏内输入公式：=FREQUENCY(IF($B$2:$B$10=J1,$E$2:$E$10),$H$2:$H$5)。按“CTRL+SHIFT+ENTER”组合键产生数组公式｛=FREQUENCY(IF($B$2:$B$10=J1,$E$2:$E$10),$H$2:$H$5)｝,即可求出城东小学学生各个分数段的相应人数。

（提示：输入公式=FREQUENCY(IF(B2:B10=J1,E2:E10),H2:H5)后，因考虑到公式中某些区域复制时不再变化，即需要使用绝对引用，选中相应的区域按F4，可变成上述公式。）

（3）选中J2：J5单元格，将公式向右填充，即可分别求出其它学校各分数段的人数。完成后K2:L5将显示如图12所示。

图12

实践证明，正确巧妙地使用EXCEL相关函数对统计多数据、多条件的学生成绩能起到事半功倍的作用。随着教育信息化的不断深入，学生的成绩统计与分析越来越电子化，掌握一些常用EXCEL的成绩统计技巧，会在成绩统计与分析的工作中如虎添翼。