(完整版)EXCEL做考试成绩分析

自己动手，用 EXCEL 做一个考试成绩分析应用小软件 河南省沁阳市第二学 王东庆 柴红伟 长期以来，在每次考完试后，不仅要去处理学生的成绩，还要计算和分析教师的成绩。这是一件非常烦琐和无可 奈何的事情。如果只是对学生的总分、名次、各科的平均分进行统计的话，用 EXCEL 去完成这项工作是一件非 常容易的事情，然而许多学校还要对任课教师进行成绩统计，依据就是该任课教师所教该学科的“三率和” 。“三 率和”是指将某一学科的平均分 +及格率（及格人数 /参加考试的人数 *100 ）+优秀率（优秀人数 /参考人数 *100 ）。 实际上我们用 EXCEL 是可以轻松做到的。只需将学生的考试成绩按照一定的格式录入，那么 EXCEL 就可以自

动去完成计算三率和的工作。下面我们通过一个实例来看一下。

比如录入初一年级某次考试成绩：

录 入 结 束 后 ， 利 用 EXCEL 自 带 的 一 些 函 数 ， 就 能 方 便 地 把 结 果 统 计 出 来 。 如 图 ：

在开始学习之前，大家有必要了解一些 EXCLE 函数方面的知识。公式和函数是 Excel 最基本、最重要的应用工 具，是 Excel 的核心，因此，应对公式和函数熟练掌握，才能在实际应用中得心应手。

1．数组公式的输入

数组公式的输入步骤如下： （ 1）选定单元格或单元格区域。如果数组公式将返回一个结果，单击需要输入数组公 式的单元格；如果数

组公式将返回多个结果，则要选定需要输入数组公式的单元格区域。

（2）输入数组公式。

（3）同时按“ Crtl+Shift+Enter ”组合键，则 Excel 自动在公式的两边加上大括号 { } 。 特别要注意的是，第（ 3）步相当重要，只有输入公式后同时按“ Crtl+Shift+Enter ”组合键，系统才会把公式视

为一个数组公式。否则，如果只按 Enter 键，则输入的只是一个简单的公式，也只在选中的单元格区域的第 1个

单元格显示出一个计算结果。

2．条件求和 SUMIF 函数

SUMIF 函数的功能是根据指定条件对若干单元格求和，公式为 :

=SUMIF（range,criteria,sum_range）

式中 range—用于条件判断的单元格区域 , 只有当 range 中的相应单元格满足条件时，才对 sum_range 中的单元 格求和。如果省略

sum_range,则直接对 range 中的单元格求和。

3. AVERAGE 函数

AVERAGE 函数的功能是计算给定参数的算术平均值。公式为

= AVERAGE （参数 1，参数 2，⋯，参数 N ）

4.COUNT 函数和 COUNTIF 函数

COUNT 函数的功能是计算给定区域内数值型参数的数目。公式为

= COUNT （参数 1，参数 2，⋯，参数 N ）

COUNTIF 函数的功能是计算给定区域内满足特定条件的单元格的数目。公式为

= COUNTIF （ range， criteria ）

式中 range—需要计算其中满足条件的单元格数目的单元格区域；

criteria —确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。

COUNT 函数和 COUNTIF 函数在数据汇总统计分析中是非常有用的函数。 有了这些基础知识以后，下面，笔者就将这一过程详细展现给大家。

首先，新建一个 EXCEL 文档，将文档的第一个工作表 sheet1重命名为“成绩” ，将第二个工作表 sheet2 重命名

为“统计”。

回到成绩工作表中，从第一个单元格开始，依次输入年级、班级、姓名、考号、语文、数学、英语、政治、历史、 地理、生物、总分等。然后单击菜单栏中的“插入”－“名称”―“定义名称” ，在定义栏中输入“年级” ，然后 单击添加。如图所示：

再将引用位置中的“ =成绩!$A$2 ”，改写为“ =成绩!$A$2:$A$1000 ”,它的意思是说定义“年级”这个名称是由 A

列的第 2 行开始到第 1000 行结束，在这个区域有效（也可以根据自己的实际情况进行定义 ,但一定要保证所定义

的名称范围超出数据输入的范围，其它的定义和它是类似的） 。“年级”定义结束后，我们采用同样的方法去定义 “班级”这个名称。单击 B2 单元格，单击“插入”－－“名称”―“定义名称” 。这时由于“班级”这一列是 在 B 列上，因此需将它的引用位置

“=成绩!$B$2”改写成“=成绩!$B$2:$B$1000 ”。采用类似的方法依次定义 “姓 名”、“考号”、“语文”、“数学”、“英语”、“政治”、“历史”、“地理”、“生物”、“总分”，注意要将它们的引用位 置写正确！ 这样，在“成绩”工作表中我们所要做的准备工作就基本完成了。现在就可以在此“成绩”工作表中进行学生成 绩的录入工作，学生的成绩也可以是从其它的 EXCEL

文档中复制－粘贴过来，但是顺序必须要和“成绩”表中 的顺序保持一致。

在打开“统计”这个工作表。单击“插入―名称－定义” ，你会发现，在“成绩”工作表中定义的名称，在“统 计”这个工作表中也可以显示出来。我们在“统计”工作表中建立如图所示的一张表：

注意，第 15 行的 06 表示这个年级是 06 级。

单击 B5 这个单元格，在公式编辑栏中输入“ =COUNTIF（ 班级 ,A5） ”，它的意思是要统计我们定义的名称“班级” 当中的 A5

这个班（即 06p01 班）的参加考试人数。接着同时按下 ctrl+shift+enter 这三个键，生成一个计算班级 参加考试人数的数组公式。

接着再单击 C5 这个单元格，在公式编辑栏中输入“ =AVERAGE（IF（（ 班级=A5）,语文 ））”，它的意思是说要计算 A5 这个班的语文平均分，同时按下 ctrl+shift+enter 这三个键，生成一个数组公式。

单击 D5 这个单元格，在公式编辑栏中输入“ =SUM（（ 班级 =A5）*（ 语文 >=60））/COUNTIF（ 班级 ,A5）*100 ”，意思是

说要统计 A5 这个班级当中语文成绩大于 60 分（及格分数是自己定义） ，也就是说统计该班的语文科的及格人数， 然后除以这个班实际参加考试的人数再乘以 100，就得出了 A5 这个班的及格率。注意“＝ SUM（（班级=A5）*（ 语

文>=60）） ”中的这个 * ，表示的是逻辑运算中的“与” 。而 COUNTIF（ 班级 ,A5）*100 ”中的 *表示乘的意思，这两个 * 的含义是不一样的。

同理，单击 E5 这个单元格，在公式编辑栏中输入“ =SUM（（ 班级 =A5）*（ 语文 >=80））/COUNTIF（ 班级 ,A5）*100 ”，便 可以得出这个班的语文优秀率 （实际上优秀的分数也是自己根据实际情况进行定义的） 。同时按下 ctrl+shift+enter 这三个键，生成一个数组公式。

单击 F5这个单元格，在公式栏中输入“＝ C5+D5+E5 ”,就得出了三率和。

然后再用鼠标拖拽选中 B5 到 F5这些连续的单击格，向下填充到第 14 行。如图：

接下来要做的工作是， 单击 B15 这个单击格， 在公式栏中输入 “=COUNTIF（ 年级 ,A15） ”，同时按下 ctrl+shift+enter 这三个键，生成一个数组公式。单击 C15 单元格，在公式栏中输入“ =AVERAGE（IF（（ 年级 =A15）, 语文）） ”，同时 按下

ctrl+shift+enter 这三个键，生成一个数组公式。单击 D15 单元格，在公式栏中输入“ =SUM（（ 年级 =A15）*（ 语 文＞=60））/B15*100 ”，同时按下 ctrl+shift+enter 这三个键， 生成一个数组公式。 单击 E15单元格， 在公式栏中输入 “=SUM（（ 年级=A15）*（ 语文＞=80））/B15*100 ”，同时按下 ctrl+shift+enter 这三个键，生成一个数组公式。单击 F15 单元格，在公式栏中输入“＝ D15+E15+C15 ”。

接着我们要做的是各分数段人数的统计工作。

单击 H5 单元格，在公式栏中输入“ =MAX（IF（（ 班级=A5）,语文））”，它的意思是统计出该班的最高分。单击 I5 这

个单元格，在公式栏中输入“ =MIN（IF（（ 班级=A5）, 语文）） ”，同时按下 ctrl+shift+enter 这三个键，生成一个数组公 式。单击 J5这个单击格， 在公式栏中输入 “=SUM（（班级=A5）*（ 语文＜=59）） ”，同时按下 ctrl+shift+enter 这三个键， 生成一个数组公式。单击 K5 单元格后，在公式栏中输入“ =SUM（（班级=A5）*（ 语文＞=60）*（ 语文＜=79）） ”，同时按 下

ctrl+shift+enter 这三个键，生成一个数组公式。单击 L5 单元格，在公式栏中输入“ =SUM（（ 班级 =A5）*（ 语 文＞=80）*（ 语文 ＜=100）） ”，同时按下 ctrl+shift+enter 这三个键，生成一个数组公式。

然后用鼠标拖拽选中 H5 到 L5 ，向下进行填充。如图：