曹绍夔捉怪：Excel中求优分率、及格率及各分数段人数

2020年11月20日发(作者：叶齐)
Excel中求优分率、及格率及各分数段人数

每次考试，我们都要对考试情况作 分析，所分析的项目包括有参考班级，参考人数、平
均分，合格人数、合格率、优秀人数、优秀率，最高 分、最低分以及各分数段所占的人数：
100分人数、80～99 分人数、60～79 人数、30～59 人数、30 分以下人数。这个统计比较
复杂，且工作量大，如果用人工计算，不但 效率低，且出错机率大，而用EXCEL 函数可以
很好解决这个问题。

我们可采用以下方法：新建空白Excel 工作簿，将自动建立的Sheet1、Sheet2工作表
重命名为成绩分析表、各科考试成绩。 其中各科考试成绩工作表用于输入各科成绩。


(一)设计各科成绩工作表
在各科考试成绩工作表第一行中输入分析名称，如“** 中学2004～2005 学年上期各< br>科成绩分析表”，在第二行从A1单元格开始顺序输入记录字段名：班级、学号、姓名、政
治、计 算机、数学、英语、操作基础、电子技术。

假设全级学生有695 人，所以从第3行到第698 行用来输入所有学生各科的成绩。

(二)设计成绩分析工作表
下面就以计算机科分析表设计为例进行描述，其他科的分析相似操作。

1.设计分析表
将A1：I1 合并并居中，输入“** 中学2004～2005 学年第一学期成绩分析表”。用同
样的方法将A2：I2 合并并居中，输入“科目：计算机”其他文字按表格内容输入即可。


2.求科各班参考人数
我们在这里可采用COUNTIF函数，COUNTIF 函数用于计算给 定区域内满足特定条件的单
元格的数目，所以在C4单元格中输入“=COUNTIF(各科考试成绩! $$A$$3: 各科考试成
绩!$$A$$698, 即可求出107班的参考人数。然后选中C4 单元格，拖动C4 单元
格右下角的小“十”字图标至C7 单元格(复制公式)，这样，就能自动得到C4 至C7 单元
格的公式。最后在C8 单元格中输入“=SUM(C4:C7)”，用于分析全年级参考总人数。


3.求科各班总分
我们在这里可采用SUMIF 函数，SUMIF 函数功能是根据指定条件对若干单元格求和，
在D 4 单元格中输入“=SUMIF(各科考试成绩!$$A$$3: 各科考试成绩!$$A$$698,各科
考试成绩!$$H$$3:各科考试成绩!$$H$$698) ”，该单元格公式作用为：计算107 班计算机科总
分。然后对D4 至D7 单元格进行向下自动填 充操作，就可以自动得到D5至D7单元格的公
式。最后在D8单元格中输入“=SUM(D4：D7) ，求出全年级总分。

4.求各班平均分
在E4 中输入“=D4C4”，算出107 班的平均分，然后用拖动方法对E4 对E7 单元格
进行向下自 动真充操作，就可以自动得到E5至E7单元格的公式，最后在E8单元格中输入
“=Average( D4：D7)，求出全年级平均分。

5.求各班合格人数
我们在这里可采用数组公式进行多重标准单元计算。首先在F4 单元格中输入
“=SUM((各科考试成绩!$$A$$2: 各科考试成绩!$$A$$698=A4)*( 各科考试成绩!$$H$$3: 各科考
试成绩!$$H$$698,>=60))”，按下SHIFT＋CTR L键敲回车，Excel会自动在公式两侧加上大括
号,生成数组公式。然后对F4至F7单元格进行向 下自动填充操作。最后在F8 中输入
“=SUM(F4:F7)”，求出全级及格人数。


6.求各班合格率
我们可在G4 单元格中输入“=IF(C4=0,0,F4C4);100”，为避免分母为0的错误，用
了IF 函 数。公式的作用为：若107班参考人数不为0，则合格率为合格人数除以参考人数
后乘以100。然后 对G4 至G7 单元格进行向下自动填充操作。然后在G8 中输入
Average(G4:G7),算出全年级的平均合格率。

7.求各班优生人数
一般对分数高于或等于80 分的学生称为优生。这个问题跟求合格率类似， 首先在H4
单元格中输入“=SUM((各科考试成绩!$$A$$2: 各科考试成绩!$$A$$698=A4)*( 各科考试成绩!
$$ H $$ 3 : 各科考试成绩!$$ H$$698,>=80))”。然后对H4至H7单元格进行向下自动填充操作。
最后在H8单元格中输 入“=SUM(H4:H7)”，以求得全年级优生人数。

8.求各班优生率
跟求合格率类似，首先在I4单元格中输入“=IF(C4=0,0,H4C4);100”，为避免分母为0 的错误，用了IF 函数。公式的作用为：若107班参考人数不为0，则优生率为优生人数
除 以参考人数后乘以100。然后对I4 至I7 单元格进行向下自动填充操作。最后在I8中输
入“= average(I4:I7)”算出全年级的平均优生率。

9.求各班最高分
求最高分可用MAX函数，这个函数的作用是求出参数中的最大值。所以在C11中输入
“ =MAX(IF(各科考试成绩!$$A$$3: 各科考试成绩!$$A$$698=A4,各科考试成绩!$$H$$3:各科考试
成绩!$$H$$698))” 按下SHIFT ＋CTRL键敲回车，生成数组公式。即可算出107班计算机科
中的最高分。然后对 C12至C14单元格进行向下自动填充操作。最后在C15单元格中输入
“=MAX(C11:C14 )”，以求得全年级的最高分。

10.求各班最低分
最低分的算法与最高 分的算法很相似，只是函数名不同，求最低分可用MIN函数，这个
函数的作用是求出参数中的最小值。 所以在D 11 中输入“=MI N(IF( 各科考试成绩!$$A$$3:
各科考试成绩!$$A$$698=A4：各科考试成绩!$$H$$3: 各科考试成绩!$$H$$698))” 按下SHIFT＋
CTRL键敲回车，生成数组公式。即可算出107班计算机科中的最低分。然后对D 12 至D14 单
元格进行向下自动填充操作。最后在H15单元格中输入“=MIN(D11:D1 4)”，以求得全年级
的最低分。

11.求100 分的人数
计算分数段人数仍可用数组的方法，如计算100分可用人数可在E11 中输入
“=SUM((各科考试成绩!$$A$$2: 各科考试成绩!$$A$$698=A4)*(各科考试成绩! $$ H $$ 3 : 各科
考试成绩!$$H$$698,=100))”，按下SHIFT＋ CTRL 键敲回车，然后对E12至E14 单元格进行
向下自动填充操作。然后在E15单元格中输入“=SU M(E11:E14)”，以求得全年级100 分的
人数。

12.求80～99 分数段人数
求计算机科各班80～99 分数段人数，将用到三个条件进行 标准单元计算。首先在F11
中输入=SUM((各科考试成绩!$$A$$2:各科考试成绩!$$A$$69 8=A4); (各科考试成绩!$$H$$3: 各科
考试成绩!$$H$$698,<100))*(各科考试成绩!$$H$$3: 各科考试成绩!$$H$$6 98,>=80)按下SHIFT
＋CTRL键敲回车，生成数组公式。然后对F12至F14单元格进 行向下自动填充操作。最后
在F15 单元格中输入“=SUM(F11:F14)”，求出全年级80～99 分数段人数。

13.求60～79 分数段人数
求计算机科各班60～79分数段人数，类似上述方法，只改变条件即可，首先在G11 中
输入=SUM((各科考试成绩!$$A$$2:各科考试成绩!$$A$$698=A4); (各科考试成绩!$$H$$3: 各科考
试成绩!$$H$$698,<79))*(各科考试成绩!$$H$$3: 各科考试成绩!$$H$$698,>=60)按下SHIFT ＋
CTRL 键敲回车，生成数组公式。然后对F12 至F14单元格进行向下自动填充操作。最后在
G15单元格 中输入“=SUM(G11:G14)”，求出全年级60～79 分数段人数。

同样的操作，可以分别分析各班计算机科共它各分数段人数和总分各分数段人数。