用EXCEL制作考试成绩统计模板

成绩统计模板

用EXCEL制作考试成绩统计模板

乐亭县新戴河初级中学 李文金 邮编 063600

学校每学期大大小小的考试都要组织多次，比如：月考、期中、期末等，每次考完之后都要对成绩进行统计，计算各科的平均分、及格率和优良率等等。每次统计都要付出很大的脑力与体力，可以说是一项比较繁重的劳动。为了从这种繁重的劳动中走出来，提高工作的效率，我用EXCEL制作了成绩统计模板。现以制作八年级成绩统计模板为例将制作过程写出来与大家一起分享。

一、根据实际，定制工作表。

新建一个EXCEL工作薄，根据班的多少插入工作表，假如有10个班，就插入12个工作表，其中前2个命名为总表和分表，其它10

个命名为1~10（班号）。如图1

图1

二、在总表和分表中设计统计表

1、首先设计分表

首先在分表中设计三个表格：平均分、优良率和及格率，

如图2。

2、关联科目

为了防止“张冠李戴”，保证科目与成绩相对应，利用

公式关联科目。如关联“语文”科目：在此表的B2单元格

中输入“='1'!D2”，即可实现关联。用同样的方法关联其它

科目及另外两表中的科目。

3、为“平均分”表格定制公式

还是以“语文”为例，通过上面的例子我们知道该科目

对应“1”工作表的D列，所以我们在B4单元格中输入以

图2 下公式：“=AVERAGE('1'!D3:D66)”，这样我们就在“平均

分表格”中得到了“八一班”的“语文平均分”。在平均数

函数中由于空值忽略不计，因此在输入求值范围时，应与各班中人数最多的人数一致或大于最多人数。这样才能保证不会出现遗漏的情况。

上面我们只是得到了“1”班的语文平均分，为了得到其他班的平均分，我利用填充柄将公工填充到“B5:B13”，却出现了这样的情况：“=AVERAGE('1'!D4:D67)”“=AVERAGE('1'!D5:D68)”“=AVERAGE('1'!D6:D69)”“=AVERAGE('1'!D7:D70)”“=AVERAGE('1'!D8:D71)”“=AVERAGE('1'!D9:D72)”“=AVERAGE('1'!D10:D73)”

“=AVERAGE('1'!D11:D74)”

“ =AVERAGE('1'!D12:D75)”也就

是说对应的工作表没有改变，而求

值范围却发生了改变，这是我们不

想要的。我是这样处理的：首先选

中B5：B13这些单元格，利用替换

的方法把求值范围先改正过来，在图3

“查找内容文本框中输入d*:d* ，

在替换值文本框中输入d3:d66，点击全部替换按钮。（如图3）然后再分别把“1”改成对应的工作表即可。用同样的方法定制其他科目的公式。这样“平均分”表格定制公式就完成了。

4、为优良（秀）率表格定制公式 ①为了操作简便，在“分表”中我首先利用公式统计了各班的参考人数：在“八一班”对应参考人数

成绩统计模板

单元格B19中输入公式“=COUNTA('1'!C3:C66)”，该公式计算出了“八一班”，“姓名列，C3:C66区域”非空单元格的个数，即参考人数，参照第3步利用填充公式及替换方法统计出其它班的参考人数。

②在“分表”中“八一班”的“语文”平均分单元格C19中输入以下公式：=COUNTIF('1'!D3:D66,">=96")/B19。这个公式分为两部分，一部分是利用COUNTIF函数计算给定区域内满足特定条件的单元格的数目，即优良人数。另一部分是用统计出的优良人数除以上步的参考人数即优良率。注意：在这里96分是按总分的80%来计算的，如果满分是100分，则这个数值就是80；当然如果你校有不同的标准，按标准修改就行了。其它学科的优良率也是参照第3步利用填充公式及替换方法，可以节约我们的很多时间。

5、为及格率表格定制公式

统计及格率仿照优良率来定制公式来进行，只要将120替换成72或将80替换成60就可以了。

6、在总表中关联统计数据

在分表中我们已经完成了平均分、优良率和及格率的统计，接下来我们只要将这些数据放在一个表——总表中就大功告成了，总表如图4所示。

①关联科目

在这个表格中仍然需要关联科目，

如在C3单元格输入“=分表!B2”。

②关联数据

以平均分数据为例，在B4单元格

输入 “=分表!B4”则将“分表”中“八

一班”的“语文”的“平均分”关联到

了总表之中，接下来直接利用填充柄就

可以通过填充得到其它各班的语文平均

分。用同样的方法可以关联其它单元格

数据。

三、导入各班成绩

完成了前二步，模板就算制作完成

图4 了，每次考试结束——录入——核对等

工作完成之后，我们只要将成绩表按“班

级”进行筛选，将各班成绩复制到“模板”对应的工作表中，在总表中就会自动出现成绩统计数据了。有了成绩统计模板后，可以说大大提高了工作效率，可谓“一劳永逸”了。

四、应注意问题

由于每次考试各科的总分可能会有所不同，在使用模板时应注意及时修改及格率、优良率及总平均分、总优良率公式中的分数值，以保证统计数据的正确。