巧用EXCEL公式进行成绩统计与分析

巧用 EXCEL 公式进行成绩统计与分析 成绩的统计与分析是学校教学中的一个重要环节 通过对学生 每次考试的成绩的合理分析 可以及时了解学生在学习过程中出现 的问题和老师在教学中存在的不足 进而及时改正 很多老师在教 学管理中或多或少的使用 EXCEL 进行成绩的统计与分析 然而由 于对 EXCEL 使用不够深入 不能正确合理的使用公式 往往也要 花费很大的精力 其实只要熟练地掌握以下几个公式 就可以大大 地提高工作的效率 下面就以我在实践中得出的一点经验为例 讲 解一下学生成绩的统计与分析一般过程 以期与大家交流与分享 一 成绩的录入 1 单科成绩录入 现在很多学校都是全年级各班混在一起考试 以防考试改卷中的不正当竞争 考前全年级统一进行考场和座号的 安排 每个考场里分布几个甚至十几个班的学生 考完试卷按考场 装订阅卷 所以成绩录入时应按考场和考号顺序录入 这样增加了 透明度 公平合理 如表 1 巧用 EXCEL 公式进行成绩统计与分析 2 成绩汇总 把各个备课组送交上的成绩表 通过按学号排 序 全年级按班以及班里学号的顺序将成绩汇总成一张总表 如表 2 巧用 EXCEL 公式进行成绩统计与分析 二 成绩分析 1 排名统计 很多学校都是把每次考试成绩来做为每个科任老 师教学能力的重要指标 统计每个科任老师所教科目在全年级各名 次段中占有的人数比例是必不可少的内容 用 RANK D28 D 28 D 32 求出每个学生的各科成绩在全年级 的排名 注意 符号在该函数中的作用 是不能省略的 然后统 计每个科目在每个名次段的人数 假设在 D 列是一个班的语文成绩 的排名我们可以采用 EXCEL 公式 COUNTIF 成绩表 D28 D32 130 COUNTIF 成绩 表 C28 C32 140 可以轻松获得成绩在 130 140 的人数 三 查询技巧 许多学校为了鼓励一些家庭困难的学生 根据考试成绩对品学 兼优的学生进行一些奖励和表扬措施 学校为这些学生建立了电子 档案 这就要求我们查找相关学生的学习成绩 以往很多人都是通 过 EXCEL 提供的查找功能 查找到该学生的学号或姓名 然后复 制该同学的成绩 这就大大增加了我们的工作量 其实可以通过公 式 顺利的完成这项工作 假设全年级成绩表中的区域大小为 A1 DV1209 即包含所有学生的成绩 我们在奖励一表的 C30 单 元格里输入公式 IF ISERROR VLOOKUP A30 成绩表 A 1 DV 1209 2 FALSE VLOOKUP A30 成绩表 A 1 DV 1209 2 FALSE 即可求得该学生的语文成绩 下面详细说明 上述公式中各项的作用 公式的意思是以单元格 A30 作为查询内容 查找在成绩表中 A1 DV1209 区域中的值等于 A30 的行的第二列的 值 如果查不到则显示为空 否则显示该单元格的值 当然我们也 可以灵活改动 为 无此人 等 这个函数较为复杂 是一个公式 的牵套 下面我们对每个公式进行详细说明 1 ISERROR 函数的主要作用其实就是判断一个单元格是否是 错误提示 语法是 ISERROR 单元格 如果一个单元格出错 那 么 ISERROR 的返回结果将是 TRUE 否则就是 FALSE 当然 单 独使用这个函数还仍然无法达到我们隐藏错误提示的目的 下面就 要给大家介绍完成这项任务的另一员大将 IF 函数 2 IF 函数的主要作用也很简单 它可以根据条件来返回不同的 数值 语法是 IF 条件 当条件为真时的值 当条件为假时的值 比如我们将公式设为 IF A1 0 通过 拦截 那么在工作时 Excel 会自动判断 A1 单元格的值 如果它大于 0 那么当前单元格 便会显示出 通过 两字 反之如果 A1 单元格的值小于或等于 0 那么当前单元格便会显示出 拦截 两个字 这样 通过将 IF 和 ISERROR 两个函数配合使用 就可以先让 ISERROR 判断目标单元 格是否显示错误 如果是 那么 ISERROR 就返回一个真值给 IF 而 IF 函数再根据这个真值在当前单元格显示一个空白 比如 来盖住原来的出错提示 而如果目标单元格的值不是错误 那么直 接显示原来的公式就可以了 3 VLOOKUP lookup table array col index num range lookup 1 Lookup 为需要在数据表第一列中查找的数值 Lookup 可以为数值 引用或文字串 2 Col index num 为 table array 中待返回的匹配值的列序号 Col index num 为 1 时 返回 table array 第一列中的数值 col index num 为 2 返回 table array 第二列中的数值 以此类推 如果 col index num 小于 1 函数 VLOOKUP 返回错误值 如果 col index num 大于 table array 的列数 函数 VLOOKUP 返回错误值 REF 3 Range lookup 为一逻辑值 指明函数 VLOOKUP 返回时是精确 匹配还是近似匹配 如果为 TRUE 或省略 则返回近似匹配值 也 就是说 如果找不到精确匹配值 则返回小于 lookup 的最大数值 如果 range 为 FALSE 函数 V