《巧用WPS表格数组公式分析班级学生成绩.docVIP

巧用WPS表格数组公式分析班级学生成绩 学校对于某年级各教学班的考试成绩分析，一般要统计每个班的参加考试人数、及格率、平均分（或每班前若干名学生的平均分）、学生个体在本教学班中的名次等，如果仅用WPS表格的内置函数计算，则需要一个班一个班的分块后计算，非常麻烦。笔者通过巧妙组合WPS表格的内置函数编写出数组公式，简捷方便地完成了以上各种特征量数计算（如图1所示）。 图1 班级学生成绩统计 1、每个班参加考试人数假设某年级有4个班，学生总数不大于260名，班号在A列（用01、02、03…表示各班号），从261行开始为汇总行。要统计的是语文学科（C列）的各班参加考试人数（每个班有得分的学生数，即非空格数），则在要计算各班参加考试人数的纵向连续的第一个单元格C262中输入公式：=SUM((A$2:A$260=0ROW(A1))*(C$2:C$260))按Ctrl+Shift+Enter组合键确认输入后，向下拖动填充到全部班级单元格C262 :C265，即可得到各班的该学科参加考试人数。上述公式为数组公式，在编辑栏输入这种公式后，必须按Ctrl+Shift+Enter组合键确认，确认后在公式两边自动加上花括号“{}”（如图2）。 图2 学科实考统计 公式解释：将班号列所在区域A$2:A$260为“某班”(0ROW(A1))和某学科考分列所在区域C$2:C$260为非空单元格()两个条件做乘法运算。当两个条件都成立时返回1（TRUE* TRUE=1），否则返回0（TRUE* FALSE=0、FALSE *TRUE =0、FALSE * FALSE＝0）。逻辑判断的结果组成0、1数组，通过函数SUM（）将所有的0、1值求和，得出该学科参加考试人数。2、每个班所有考生的总平均分假设E列为“总分”列，在要计算各班总平均分的纵向连续的第一个单元格E262中输入数组公式：=AVERAGE(IF(A$2:A$260=0ROW(A1),E$2:E$260))按Ctrl+Shift+Enter组合键确认后向下拖动填充到全部班级单元格E262 :E265，得到每个班所有考生的平均分（如图3）。 图3 每个班的平均分 公式解释：首先通过IF（）函数，在总分所在区域E$2:E$260中筛选满足条件为“某班”的一组数值，然后通过AVERAGE（）对所筛选的数值求平均值。3、每个班前若干名学生的平均分为了分层测试教学效果，往往按截取一部分学生的成绩作为样本计算。例如，计算各班前30%学生的平均分，需在F262单元格中输入数组公式如下：=AVERAGE(LARGE(IF($A$2:$A$260=0ROW(A1),E$2:E$260),ROW(INDIRECT(1:ROUND(COUNTIF(A$2:A$260,0ROW(A1))*0.3,0)))))然后将此公式向下拖动到F265单元格（如图4）。 图4 各班前30%平均分 公式解释：首先通过ROUND(COUNTIF(A$2:A$260,0ROW(A1))*0.3,0)（以下简称为X），计算前30%的人数有多少，并将其四舍五入到整数；再通过ROW(INDIRECT(1:X) （以下简称为Y） 由小到大数排列1到前30%的人数的若干个自然数（数组），由LARGE(IF($A$2:$A$260=0ROW(A1),E$2:E$260),Y)抽取总分所在区域E$2:E$260中某班前30%学生的得分（数组），最后通过AVERAGE（）求选取数值的平均分。4、每个班的学科及格率：假如语文学科（C列）及格分数为60分，则在要计算各班及格率的纵向连续的第一个单元格G262中输入数组公式：=SUM((A$2:A$260=0ROW(A1))*(C$2:C$260=60))/SUM((A$2:A$260=0ROW(A1))*(C$2:C$260))按Ctrl+Shift+Enter组合键确认后向下拖动填充到全部班级单元格G262:G265，得到各班该学科的及格率（如图5）。 图5 学科及格率 假如语文（C列）、数学（D列）中的学科及格分数分别为60分、72分，则计算每个班这两个学科都及格的人数的数组公式为：=SUM((A$2:A$260=0ROW(A1))*(C$2:C$260=60)*(D$2:D$260=72))5、每个学生在本班的名次一个学生在全年级的名次，可用函数RANK()求出，而该学生在本班内的名次，却没有现成的内置函数可用，我们仍然可以巧用数组公式来解决。在名次列的第一个学生成绩行所在单元格F2中输入数组公式：=SUM((A$2:A$260=A2)*(E$2:E$260E2),1)按Ctrl+Shift+En