自己动手_用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 语文 60 语文 80 语文 100 同时按下 ctrl shift enter 这三个键 生成 一个数组公式 然后用鼠标拖拽选中 H5 到 L5 向下进行填充 如图 然后单击 H15 这个单击格 在公式栏中输入 MAX IF 年级 A15 语文 同时按下 ctrl shift enter 这三个键 生成一个数组公式 单击 I15 这个单元格 在公式栏中输入 MIN IF 年级 A15 语文 同时按下 ctrl shift enter 这三个键 生成一个数组公式 单击 J15 单元格 在公式栏中输入 SUM 语文 60 语文 80 语文 100 同时按下 ctrl shift enter 这三个键 生成一个数组公式 到此为止