用Excel制作学生成绩汇总及等级评价模板

用 Excel 制作学生成绩汇总及等级评价模板 每当学期结束 需要汇总学生的各科考试成绩 教务处和班主任都会忙得头晕脑涨 而这一工作总是每个学校 每个年级乃至每个班级都要做的事情 成为对学校 班主任 任课教师 学生进行考核评定的重要参考资料 在这个过程中 不但要计算每名学生的考 试成绩的总分 并排出名次 还要统计每一学科的最高分 最低分 平均分 优秀率 及 格率等 在给学生填写成绩报告单时 只填等级 A B C D E 等 不填分数 用手 工计算起来 甚是麻烦 不过这些工作都可以让 Excel 替我们自动完成 我们所要做的 只是输入学生的各科考试分数 输入成绩时 还可以让多位教师同时进行 协同操作 因 而 复杂繁重的工作也可以变得轻松起来 具体步骤如下 这里仅以某校七年级两个班为 例 1 成绩分析表雏形 新建一个工作薄 插入工作表 使工作表个数要比班级数多出两张 其中一张是 年级 汇总 一张是 成绩分析 把各工作表标签依次改为 1 班 2 班 年级汇总 成绩分析 成绩分析表雏形如图 1 所示 并将其保存为 通用模板 方法是在 另存为 对话框中 单击保存类型下拉按钮 选择 通用模板 输入文件名 学生成绩汇总 后单击 保存 即 可 以后用的时候 只需打开 Excel 单击 文件 菜单 选择 新建 单击 本机上的模板 Excel 2003 选择 学生成绩汇总 即可 2 班级汇总工作表的制作 2 1 将 1 班第一行分别复制到 2 班 及 年级汇总 工作表中的第一行 单击 1 班工作表 标签 假设各班人数为 50 人 单击 j2 单元格 输入公式 IF D2 I2 SUM D2 I2 IF 函数功能介绍 执行真假值判断 根据你指定的条件进行逻辑判断的真假而返回不 同的结果 函数格式 IF logical test value if true value if false 其中 logical test 表示任 何一个可评价为 true 或 false 的数值或表达式 value if true 表示当 logical test 为 true 时的 返回值 如果忽略则返回 true if 函数最多可以嵌套七层 value if false 表示当 logical test 为 false 时的返回值 如果忽略则返回 false 该公式说明 IF 函数先判断条件 D2 I2 的真 假 为真时返回 sum d2 i2 即计算出各科成绩的总分 为假时返回为 空 即缺考的学生 成绩在总分栏显示为 空 下同 输入完成后按下 Shift Ctrl Enter 组合键 Excel 会自动在公式两端添加 选中 j2 单元格 将鼠标移到填充柄 活动单元格右下角的小方块 上 待鼠标指针变成黑色十字 箭头时 按下鼠标左键拖动鼠标向下填充到 j51 下文中有关单元格填充都指此方法 2 2 再单击 K2 单元格 输入公式 IF D2 I2 AVERAGE D2 I2 按下 Shift Ctrl Enter 组合键 这里 函数 AVERAGE 是计算各科的算术平均值 这 样 就得到了第一个学生的平均分了 再向下填充至 K51 单元格 2 3 单击 L2 单元格 输入如下公式 if j2 RANK J2 J 2 J 51 0 按下 Shift Ctrl Enter 组合键 这里 函数 RANK 是返回某数字在一列数字中相对于 其它数字的大小排位 数字区域要使用 绝对引用 再向下填充至 L51 单元格 至此 班 级汇总结束 将 1 班 工作表复制到 2 班 工作表中去即可 3 年级汇总工作表的制作 3 1 单击年级汇总工作表标签 单击 A2 单元格 输入 IF 1 班 A2 1 班 A2 按下 Shift Ctrl Enter 组合键 再向右填充到 K2 单元格 选中 A2 到 K2 单元格 按住填充 柄向下填充到 K51 单元格 单击 A52 单元格 输入 IF 2 班 A2 2 班 A2 按下 Shift Ctrl Enter 组合键 用同样的填充方法 一直填充到 K101 3 2 单击 L2 单元格 输入 if j2 RANK J2 J 2 J 101 0 同样 按下 Shift Ctrl Enter 组合键 再向下填充至 L101 即可 3 3 下面进行各科等级公式的编写 分 A B C D E 五个等级 A 级为 15 B 级为 25 C 级为 30 D 级为 25 E 级为 5 利用 IF LARGE COUNTIF COUNTA 等函数进行嵌套 可以完成这个要求 COUNTA 是 计数函数 也就是班级人数 COUNTIF 是计算某个区域中满足给定条件的单元格数目 LARGE 的本意是返回数据区域里第 K 个最大值 而 IF 函数进行逻辑判断 当条件为 true 真 时 返回第二个参数条件 当条件为 false 假 时 返回下一个条件继续判断 直至结束 在 年级汇总 工作表中的 M1 R1 单元格中依次输入 语文等级 数学等级 英语等级 科学等级 社会等级 政治等级 单击 M2 单元格 输入 IF D2 IF D2 LARGE D 2 D 101 COUNT D 2 D 101 15 A IF D2 LARGE D 2 D 101 C OUNT D 2 D 101 40 B IF D2 LARGE D 2 D 101 COUNT D 2 D 101 70 C IF D2 LARGE D 2 D 101 COUNT D 2 D 101 95 D IF D2 LARGE D 2 D 101 COU NT D 2 D 101 100 E 按下 Shift Ctrl Enter 组合键 将 M2 单元格中的公式向右填充至 R2 选中 M2 R2 区域 拖动填充柄向下填充至 R101 至此 年级汇总工作表制作完毕 如图 2 所示 4 成绩分析工作表的制作 年级成绩分析主要内容有 最高分 最低分 平均分 优秀率 及格率 E 级率等内 容 格式如图 3 4 1 最高分统计 在各科相应的单元格内分别输入 这里仅以 1 班 为例 下同 IF 年级汇总 D 2 D 101 MAX 年级汇总 D 2 D 101 IF 年级汇总 E 2 E 101 MAX 年级汇总 E 2 E 101 IF 年级汇总 F 2 F 101 MAX 年级汇总 F 2 F 101 4 2 最低分统计 复制最高分项目的相应单元格 只需将其中的 MAX 换成 MIN 即 可 可使用 替换 命令 4 3 平均分统计 在各科相应的单元格内分别输入 IF 年级汇总 D 2 D 101 AVERAGE 年级汇总 D 2 D 101 IF 年级汇总 E 2 E 101 AVERAGE 年级汇总 E 2 E 101 IF 年级汇总 E 2 E 101 AVERAGE 年级汇总 E 2 E 101 4 4 优秀率统计 在各科相应的单元格内分别输入 IF 年级汇总 D 2 D 101 COUNTIF 年级汇总 D 2 D 101 90 COUNTA 年级 汇总 D 2 D 101 100 IF 年级汇总 E 2 E 101 COUNTIF 年级汇总 E 2 E 101 90 COUNTA 年级 汇总 E 2 E 101 100 IF 年级汇总 F 2 F 101 COUNTIF 年级汇总 F 2 F 101 90 COUNTA 年级汇 总 F 2 F 101 100 4 5 及格率统计 在各科相应的单元格内分别输入 IF 年级汇总 D 2 D 101 COUNTIF 年级汇总 D 2 D 101 60 COUNTA 年级 汇总 D 2 D 101 100 IF 年级汇总 E 2 E 101 COUNTIF 年级汇总 E 2 E 101 60 COUNTA 年级 汇总 E 2 E 101 100 IF 年级汇总 F 2 F 101 COUNTIF 年级汇总 F 2 F 101 60 COUNTA 年级汇 总 F 2 F 101 100 4 6 A 级率统计 在相应的单元格内分别输入 IF 年级汇总 M 2 M 101 COUNTIF 年级汇总 M 2 M 101 A COUNTA 年级 汇总 M 2 M 101 100 IF 年级汇总 N 2 N 101 COUNTIF 年级汇总 N 2 N 101 A COUNTA 年级汇 总 N 2 N 101 100 IF 年级汇总 O 2 O 101 COUNTIF 年级汇总 O 2 O 101 A COUNTA 年级汇 总 O 2 O 101 100 4 7 E 级率统计 只需将 A 级率单元格中的内容复制到 E 级率相应的单元格中 将 A 改为 E 即可 这样 整个工作簿函数的编辑工作已经完成 5 后续工作 现在我们可以测试一下 将 1 班 2 班以往成绩复制到相应班级中去 看一下 年级汇 总 和 成绩分析 工作表中的数据生成是否正确 如有问题 可追综错误 查出原因 进行 修改 若无问题 单击保存即可 结果如图 4 所示 由于此工作簿用到了许多公式 稍不注意 可能造成误删或其他老师不注意弄错 就 很麻烦 因此 要对此工作表进行保护 选定 1 班 2 班的 A2 H51 单元格区域 单击格 式菜单中的 单元格 命令 单击 保护 选项卡 把 锁定 前面的 去掉 再选定 J K L 三列 单击格式菜单中的 单元格 命令 再单击 保护 选项卡 选中 锁定 和 隐藏 两项 单击 确定 退出 选定 年级汇总 和 成绩分析 两张工作表的全部单元格 同 样打开 保护 选项卡 选中 锁定 和 隐藏 两