EXCEL函数在成绩分析中的全面应用.pdfVIP

EXCEL函数在成绩分析中的 全面应用 口 薛志峰 学校每学期都有各类考试，以此来分析学情。考后 COUNTA函数就可以保证在全班考生成绩完整的情况下 成绩的细致分析工作十分重要，不仅反映了学生近段时 计算出不含 “最后5名”的平均分。假设某班成绩表 已 间的学习情况，也为发放学生奖学金和考核任课教师的 经按总分从高到低作降序排列，姓名在C列区域为C3： 教学效果提供了重要依据 。 C88，语文成绩仍在D列区域为D3：D88，则公式为：=A VERAGE(OFFSET(D$3 (COUNTA($C$3：$C$88)-5)，))。 特定条件的平均分计算 公式说明：COUNTA函数计算姓名列不为空值的 1．芦 ERAGE与IF结合解决0值问题 个数即全班考生总人数，再减5 (去掉最后5名)，利用 我们都知道，计算平均分一般使用AVERAGE函 OFFSET函数以D3单元格为基点引用除最后5名学生的 数就可以实现，如果考生参加考试考分为0，单独使用 语文成绩的区域即D3：D83，最后利用AVERAGE函数计 AVERAGE函数计算平均分时0值将参与计算 ，影响班 算该区域的平均分即不含 “最后5名”考生的平均分。 级平均分值 。当不需要计入0值即排除0分计算全班平 公式中姓名列使用绝对引用，同样，D$3单元格使用混 均分时，结合AVERAGE函数与IF条件语句构建数组公 合引用，使公式可以向右 自动填充完成其他科 目成绩 式就可 以实现。假设工作表 中某班语文成绩在D列区 的同样计算。当然，根据实际情况也不仅仅只限最后5 域为D3：D88，则数组公式为：{=AVERAGE(IF((D$3： 名，只需对公式稍作修改即可完成。 D$88)0，D$3：D$88))}。 公式说明 ：利用IF语句判断区域大于0值数据，条 及格率与优秀率计算 件为真时返回相应区域数值 ，条件为假时则返回逻辑 1．一般情况下的及格率和优秀率计算标准及方法 值FALSE，最后利用AVERAGE函数对生成的数组进行 及格率的计算方法是：满分为150分的学科成绩大 平均值计算。注意逻辑值FALSE不参与平均值计算， 于等于90分为及格，满分为100分的学科成绩大于等于 数组公式中的一对大括号不能手工输入 ，必须通过按 60分为及格。及格率就是及格人数除以总人数所得数值 “Ctrl+Shift+Enter”组合键实现输入 。公式中使用混合 的百分 比。 引用实现向右 自动填充，从而完成其他列科 目平均分计 优秀率的计算方法是：满分为150分的学科成绩大 算 ，增加了公式的灵活性 。 于等于120分为优秀，满分为100分的学科成绩大于等于 2．AVERAGE与OFFSET~I1C0UNTA函数结合解决 80分为优秀。优秀率就是优秀人数 占总人数的百分比。 排除 “最后5名”考生成绩 问题 一 般情况下的及格率和优秀率结合COUNTIF函数 由于班级排名最后5位学生的成绩对班级整体的 和COUNTA函数就可以完成计算。同上，仍以计算语 平均分影响较大 ，就是俗话说的 “拖班级后腿”，有 文学科及格率为例 ，及格率公式为 ：=COUNTIF(D3： 时我们需要排除此种因素，只计算去掉排名最后的5位 D88，”=90”)／COUNTA(D3：D88)，其他科 目的及格率和 学生各科成绩后的班级平均分。逐一删除各班最后5名