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

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