Excel及其函数在学科成绩统计与日常生活中的应用

Excel 及其函数在学科成绩统计与日常生活中的应用及其函数在学科成绩统计与日常生活中的应用 Excel 俗称电子表格 是集表格 计算和数据库为一身的优秀软件 其数据处理的核 心是函数和数据库 本文仅介绍用 Excel2KX 进行成绩统计和一些日常统计的函数 一 考试成绩的统计一 考试成绩的统计 一 对班学生成绩的总体评估统计 一 对班学生成绩的总体评估统计 1 假设 E4 E53 单元格存放某班学生的语文考试成绩 若要计算该科的总分 人平 分 及格人数 及格率 优秀人数 优秀率 最高 低 分 分段统计等 1 参考人数统计 参考人数统计 若要将参考人数放在 E54 单元格中 只需选定该单元格并插入 计数函数 COUNT 并选定要计数的单元格区间 E4 E53 或 输入 公式 COUNT E4 E53 回车即可 2 学科总分的统计 学科总分的统计 若要将该科的总分存放在 E55 单元格中 只需选定该单元格并插入 求和函数 SUM 并选定要计数的单元格区间 E4 E53 或 输入 公式 SUM E4 E53 回车 后即可计算出该科总分 注 在 E55 单元格只显示计算结果 如要修改该公式 只需选定该 单元格 在编辑栏内即可修改 3 学科平均分的统计 学科平均分的统计 若要将该科人平分存放在 E56 单元格中 选定该单元格插入平均值函数 AVERAGE 并选定求值区间 E4 E53 或输入公式 AVERAGE E4 E53 回车后即可计算出人平分 注 数组或单元格引用中的文字 逻辑值或空白单元格将被忽略 但单元格中的 0 则参与运算 3 学科及格人数统计 学科及格人数统计 若要将该科的及格人数存放在 E57 单元格中 选定该单元格插入计算某个区域内满足 指定条件的单元格数目函数的条件函数 COUNTIF 插入函数的方法是单击 插入 菜单中的 插入函数 F 命令 弹出一个 插入函数 对话框 在其中的 选择函 数 下的列表框中选定选定 COUNTIF 函数 单击 确定 弹出一个 函数参数 指 定对话框 在其中的 Range 右边矫形框中输入统计的区间 E4 E53 或 单击矫形框右 端的压缩图标 再在工作表中选择统计区间 E4 E53 再单击 函数参数 压缩框中右端 的扩展图标 返回扩展的 函数参数 对话框中 再单击对话框中的 Criteria 右边的 矩形框 并键入及格条件 60 最后单击 确定 即可 也可以手动编辑输入公式 及条件 COUNTIF E4 E53 60 回车 即可计算出该科的及格人数 空白 单元格 文字 逻辑值 错误值均被忽略 4 学科及格率统计 学科及格率统计 若要将该科的及格率存放在 E58 中 选定该单元格并输入公式 COUNTIF E4 E53 60 COUNT E4 E53 回车 COUNT 为计数函数 用 法是 计数函数 加上 计数区间 即可计算出该科的及格率 其中 COUNT E4 E53 是计算 E4 E53 区间内单元格中的实际参考人数 空白单元格将被忽略 5 学科优生人数统计 学科优生人数统计 若要统计学科优生人数与学科及格人数统计类似 如 将该科的优秀人数存放在 E59 单元 格中 选定该单元格插入条件函数 COUNTIF 方法与统计及格人数一样 只 是函数参数对对话框中 Criteria 右边的矩形框中的函数参数要修改为 80 优分标 准 6 学科优分率统计 学科优分率统计 学科优生百分率的统计与学科及格率的统计相同 如 将该科的优秀率存放在 E60 单 元格中 选定该单元格并输入公式 COUNTIF E4 E53 80 COUNT E4 E53 这与及格率公式的编辑只是参数 60 变成了 80 而已 求优秀率 求优秀率 例如 B2 B50 存放的是初一一班的语文期末考试成绩 B2 B500 存放的是 初一全年级语文考试的成绩 如果规定全年级 20 的学生为优秀 那么初一一班语文的优 秀率应该这样计算 COUNTIF B2 B50 LARGE B2 B500 INT 0 2 COUNT B2 B500 COUNT B2 B50 其中 LARGE B2 B500 INT 0 2 COUNT B2 B500 所求的是全年级语文分数前 20 中最 低的一个同学的分数 COUNTIF B2 B50 LARGE B2 B500 INT 0 2 COUNT B2 B500 则是求出了初一一 班语文高于或等于这个同学分数的人数 最后再除以初一一班的总人数 COUNT B2 B50 所得就是初一一班的语文优秀率 如果想求其他班其他科目的优秀率 道理都是一样 7 学科最高分的查询统计 学科最高分的查询统计 若要查询统计学科最高分 则可利用 MAX 最大值 函数 如将 E4 E53 区域中存 放着的全班 50 名学生语文科的考试成绩 将其学科最高分 最低分别存放在 E61 和 E62 单元格中 则可选中 E61 单元格 插入 最大值 函数 MAX 并指定统计数据的区域 E4 E53 或在编辑栏输入公式 MAX E4 E53 回车后即可计算出其中的最高分 8 学科最低分的查询统计 学科最低分的查询统计 如将 E4 E53 区域中存放着的全班 50 名学生语文科的考试成绩 将其学科最低分别 存放在 E62 单元格中 则可选中 E62 单元格 插入 最小值 函数 MIN 并指定统计 数据的区域 E4 E53 或在编辑栏输入公式 MIN E4 E53 回车后即可计算出其 中的最低分 9 若在 F4 F53 G4 G53 K4 K53 区域中分别存放数学 英语 计 算机 音 体 美等学科成绩 则只需选定 E54 E62 区域 移动空心十字光标至单元格 右下角 待光标变成小实心十字光标时 按住鼠标左键沿行拖动控制手柄至 K 列即可 放 开鼠标左键后 则各科的科总分 人平分 及格人数 及格率 优秀人数 优秀率 最高 分 最低分等立即计算出来 2 班学生成绩的分段统计 若要将学生成绩按分数段进行统计 则可利用 FREQUENCY 函数 以一列垂直数组及 对该数组设定进行频率颁布计算的分段点计算出一组数据的频率颁布 假设 E4 E53 共 有 50 个学生的语文科测试成绩 那么可在 B66 B70 单元格分别输入 59 69 79 89 100 则表示统计 59 包括 59 分以下 60 69 70 79 80 89 90 100 分数段的成绩个数 若要将计算结果存入 C66 C70 则选中 C66 C70 单元格区域 在编辑栏内输入公 式 FREQUENCY E4 E53 B 66 B 70 注 公式中区域 E4 E53 是用 来计算频率的数组区域 班学生学科成绩的单元区域 公式中区域 B 70 B 70 是对所计算频率数组 班学生学科成绩进行频率计算的分段界点的数组区域的绝对引用 输完后让光标停在公式末尾 按下 SHIFT CTRL 键敲回车 这是生成数组公式的关键环 节 Excel 会自动在公式两侧加上大括号 公式成为 FREQUENCY E4 E53 B 66 B 70 则区间 C66 C70 各单元格中立刻计算出 对应各分数段中成绩的个数 二 对班中学生个体成绩的评估统计 二 对班中学生个体成绩的评估统计 1 学生个体总成绩的统计 学生个体总成绩的统计 若在工作表中 E4 K53 区域存放某班 50 个学生各科的成绩 而 E4 K4 E5 K5 E53 K53 中分别各个学生语文 数学 美术等各科成绩记录 则需在 L 列中的 L4 L53 区间单元格中分别存放各学生的各门学科成绩总分 则先选定 L4 单元格插入求和函数 并选定求和区域回车或手动输入公式 E4 K4 回车即可计算出该生各科的总分 然后选定 L4 单元格 并将鼠标指针指向选择框右下角 的控制柄并沿列拖动鼠标至 L53 即可算出全班其余学生各科的总分 若某生未考试 但不 希望在其总分单元格填入 0 只需将公式修改为 IF ISNUMBER E4 L4 SUM E4 L4 回车 单元格中会填入空格而不会出现 0 2 学生个体班级名次的统计 学生个体班级名次的统计 若要将该班学生的班级排名计算出来 但不希望打乱学生原来按学号的顺序 则可利 用 RANK 函数轻松完成 该函数的最大优点是 可以处理重名次 可使数值相同的单元格 计算出的名次相同 如上例在 L4 L53 区域中存放某班学生的总分 要将学生在班级排名 的结果放在 N4 N53 区域中 只需在 N4 中输入公式 RANK L4 L 4 L 53 回车 即可计算出该生在班级中所排名次 然后沿列拖动复制 即将全班学生的排名计算出来 当有并列重复名次时 并列名次后缺少名次数等于名次并列重复数 如果某些学生未参加考试 又不希望在其存放名次的单元格内显示出错信息 如 VALUE 则可将公式修改为 IF ISNUMBER L4 RANK L4 L 4 L 53 在计算的过程中我们需要注意两点 首先当 RANK 函数中的 Number 不是一个数时 其返回值为 VALUE 影响美观 另外 Excel 有时将空白单元格当成是数值 0 处理 造成所有成绩空缺者都是最后一名 看上去也很不舒服 此时 可将上面的公式 RANK E2 E 2 E 50 改为 IF ISNUMBER E2 RANK E2 e 2 E 50 其含义是 先判断 E2 单元格里面有没有数值 如果有则计算名次 没有则空白 其次当使用 RANK 函数计算名次时 相同分数算出的名次也相同 这会造成后续名次的空缺 但这并不影响 我们的工作 同样的道理 我们也可以算出一个学生的总分在年级内的名次以及各科的班 名次和年级名次 但是必须注意参与计算的数字单元格区域不一样 3 学生个体等级评定成绩的统计 学生个体等级评定成绩的统计 如学校采用等级评定考试成绩 若标准为 考试各科平均分数高于或等于 85 分为 A 等 考试分数低于 85 分且高于或等于 70 分为 B 等 考试分数低于 70 分且高于或等于 60 分为 C 等 考试分数低于 60 分为 D 等 没有参加考试的不划等级 假设平均分数存放在 M 列的 M4 M53 区域 计算结果存入 O 列 O4 O53 区域 则用 Excel 计算等第的公式如下 IF M4 85 A IF M4 70 B IF M4 60 C IF ISNUMBE R M4 D IF ISBLANK M4 公式中的等级代码为大写时显示大写 小 写时显示小写 其余字母不区分大小写 这是一个 IF 函数嵌套公式 式中第二个 IF 语句是第 一个 IF 语句的参数 第三个 IF 语句则是第二个 IF 语句的参数 以此类推 如果第一个逻 辑判断表达式 M4 85 为 TRUE 真 则 O4 单元格式被填入 A 如果为 FALSE 假 则计算第二个 IF 语句 IF M4 70 以此类推直至计算结束 其中 ISNUMBER 函数 在 M4 为空时返回 FALSE 假 接着执行最后一个 IF 语句 否则在 O4 单元格中填入 D ISBL