用Excel分析统计成绩
作为一个老师，统计分析成绩是必不可少的。而且这一两年来我都承担着年级里成绩统计的任务，因此对Excel就不得不更多研究了。虽然对于Excel来说，我会用的可能还只是皮毛，不过，就是这一点皮毛，我也觉得有必要做一个总结收藏。以下所提到的数组公式在输入公式之后要以CTRL+SHIFT+ENTER三键组合结束公式。
在看下文之前，先说明一下。
首先，我的工作表的班级列都是只有1，2，3，4之类的数字。另外，为了方便我写公式，我将各个要用到的区域进行了名称定义，即对每一个要用到公式里的区域起一个名字。我的工作表中第一行是标题行，从第二行开始就是所有的学生数据了。在这里，我的所有数据区域是$A$1:$J$407，选中这个区域（可以先选定A1单元格，然后按Ctrl+A即可）然后单击菜单，“插入”→“名称”→“指定”（或按组合键），在“指定名称”对话框中，勾选“首行”复选框，如图所示，单击“确定”按钮。
排名：年级内排名，班内排名
1、成绩进行年级内排名（H
年级排名是放在J列的，因此我可以选中J2单元格，输入以下公式：=RANK(H2,总分)
2名的，那么下一个名次就会从第4名开始，没有第3名，有些人称这种排名方式为美国式排名。相应的也有中国式排名，不管并列的有几个人名次都按顺序给下去，如虽有两个第2名的，但下一个排名仍然是第3名。如果要进行中国式排名，可以有几种公式：
COUNTIF，FREQUENCY，MATCH
如上表中，用到了四种公式进行中国式排名，以下公式前三个用的都是数组公式。
（1）COUNTIF公式：
=SUM(IF(A$2:A$11>A2,1/COUNTIF(A$2:A$11,A$2:A$11)))+1
（2）FREQUENCY公式：
=SUM(--(FREQUENCY(A$2:A$11,IF(A$2:A$11>=A2,A$2:A$11))>0))
（3）MATCH公式：
=SUM(--IF(A$2:A$11>=A2,MATCH(A$2:A$11,A$2:A$11,)=ROW($2:$11)-1))
（4）SUMPRODUCT公式，这个公式实际上跟第一个SUM公式差不多，只是用了SUMPRODUCT公式之后就可以不是数组公式了。
=SUMPRODUCT((A$2:A$11>A2)(1/COUNTIF(A$2:A$11,A$2:A$11)))+1
2、成绩进行班内排名
I列是用来输入班名次的，可以选择I2单元格，输入公式并填充至每一行。
数组公式：=SUM((班级=$A2)(总分>$H2))+1
如果将SUM改成SUMPRODUCT，则是普通公式，结果一样。
注：
??“班级=$A2”用来判断班级，相等的返回1，不相等的返回0；
??1，小于或等于自身成绩的返回0；
??SUM()用数组公式或SUMPRODUCT()公式即可让相对应的数值进行相乘之后累1就得到这个人的名次。
二、计算各班各科平均分
1、=SUMIF(班级,row(a1),总分)/SUM((班级=row(a1))(总分<>0))
??数组公式；
??ROW(A1)返回A11；
?????SUMIF()先判断班级列每个数据是否等于row(a1)=1，如果是，返回相应同一行
??SUM()先判断班级列是否为row(a1)=11，否则返回0；再判0（这是为了排除空值，忽略成绩为空的学生），1，对应相乘之后累加则得到参加考试，即总分成绩不为01班总人数。
2、=SUMIF(班级,ROW(A1),总分)/SUMPRODUCT((班级=ROW(A1))(总分<>0))
??这是一个普通公式；
??SUMIF()用来计算班级=ROW(A1)=10的个数（即1班参加考试的人数）。
3、=AVERAGE(IF((班级=row(a1))(总分<>0),总分))
??数组公式；
??在AVERAGE里加入IF筛选出班级=1的数据，如果总分成绩=0，则返回Fasle，AVERAGE这个函数会自动忽略Fasle值，如果总分成绩不为0，
三、统计各班语文及格、不及格的人数
1、及格人数：
一般公式：=sumproduct((班级=ROW(A1))(语文>=60))
2、不及格人数：
一般公式：=sumproduct((班级=ROW(A1))(语文<60)(语文<>0))
3、及格率：
一般公式：=sumproduct((班级=ROW(A1))(语文>=60))/sumproduct((班级=ROW(A1))(语文<>0))
??sum((班级=ROW(A1))(语文>=60))为统计160分以上（包括60分）的人数，(班级=ROW(A1))判断班级列是否等于row(a1)，即是否等于1，是则返1，否则返回0；(语文>=60)判断成绩列是否大于等于60，是则返回1，否则0；判断之后将各个对应数据相乘之后再累加起来。
??(语文<60)(语文<>0)为统计成绩小于60(语文<>0)用来判断成绩是否不等于0。
如要统计总分各个分数段的人数，可以用第三点介绍的公式，如用
=sumproduct((班级=ROW(A1))(总分>=300)(总分<400))
300以上（包括300），400
另外还可以用统计函数FREQUENCY来计算各个分数段的人数，注意这里需要将分数段上限给出，如(750,900]则写900.请看下例。
FRENQUENCY
格式：=FREQUENCY(数组区域,区间上限区域)
在使用的时候必须先给出分数段上限这一列，如上表中的A2:A11，再选中要显示D2:D7，按住F2，输入公式=FREQUENCY(A2:A11,D2:D7)
CTRL+SHIFT+ENTER结束公式，则结果就会显示出来。
五、找出各班各科最高分、最低分
最高分：=SUMPRODUCT(LARGE((班级=1)(语文),1))
数组公式：
=MAX(IF(班级=1,语文))
=MIN(IF((班级=1)(语文<>0),语文)
False值，才会被MIN函数忽略。
六、筛选出全年级总分前120名
这里有多种作法，我可以在所有成绩输入完毕之后用排序或自动筛选，再将总分前10名的学生的数据复制过来。如果是直接用总分从大到小排序或在自动筛选里按总分排序，那么为了排序后公式不会错乱，班级名次必须用的是上面所提到的公式。
在这里，我使用的是数组公式，这样即使在成绩数据里修改了成绩，筛选出来的数据也会相应更新。在上表A3单元格输入公式如下：
=INDEX(成绩,MATCH(LARGE(四科总分-ROW(四科总分)/10000,ROW(A1)),四科总
这个公式很长，里面用到了两个查询函数INDEX和MATCH，还有一个统计函数LARGE。在对公式说明之前，我想先说明一下这三个函数的用法。??MATCH(lookup_value,lookup_array,[match_type])，按照[match_type]参数在lookup_array中寻找值lookup_value，并且返回lookup_valuelookup_array中的位置（如果是在一列中寻找，就返回数据所在的行号；同理，如果是在一行中寻找，则会返回数据所在的列号）。
[match_type]可以为-1，01.选择1，MATCHlookup_value的最大值，数据区域必须先按升序排列；选择-1的时候,查找大于或等于lookup_value的最小值，数据区域必须先以降序排列；选择0，则查找等于lookup_value
??LARGE(array,k)，LARGEarray内第k个最大值；
??INDEX(array,row_num,column_num)，arrayrow_num和列号column_num至少需要有一个。INDEX是返回所给的行号和列号交叉处的值。
公式说明：
??这是一个数组公式，输入公式后按CTRL+SHIFT+Enter即可。
??“成绩”这个名称是定义给所有的成绩数据区域。如下图，我将区域$A$2:$J$407定义名称为“成绩”。
??公式格式是：INDEX(成绩,MATCH(),COLUMN(A1))，MATCH()里面使用LARGE()ROW(A1)=1个最大值，并返回这个最大值在成绩表里面的行号；INDEX表示显示成绩表里这一行，第A列的数据。
??LARGE(四科总分-ROW(四科总分)/10000,ROW(A1))，这部分公式的意思是找出ROW(A1)=1个最大值，因为总分中难免会有相等的成绩，但用MATCH函数的时候MATCH只会寻找到相等分数里面的第一个，其余都不再显示，因此在每个总分后面减去本身行号的1/10000，
??MATCH(LARGE(…),四科总分-ROW(四科总分)/10000,0)，在这个公式里，LARGE计算出来的，寻找区域为四科总分-ROW(四科总分)/10000（注意LARGE函数是在什么区域寻找，MATCH函数就得在同一个区域里匹配数据），可选参数为0，即在区域中寻找等于lookup_value
??INDEX(成绩,MATCH(...),COLUMN(A1))，数据区域为整个成绩块，MATCH1个最大值所在的行号为15，则INDEX15行，COLUMN(A1)即A列的单元格，并且将单元格的内容显示出来，如下图中显示的是8。将公式填充到J315行，A到J列的数据依次显示出来。至此我们已经将第一名的同学数据筛选出来了，将公式向下填充即可得出前十名的同学的数据。
??COLUMN(A1)是返回查找到的数据所在行的a
至此，基本上要用到的成绩分析函数都已经包括在内。
|
|
