中学教育excel分班统计成绩VIP免费

用excel分班统计成绩如下图，sheet1中有1000多条记录，现在要在sheet2中统计出各班各科的人平均分。1、先将班级和各科名称复制到sheet2，2、在sheet2的班级上（d2）插入名称，定义名称为x，引用中输入：=OFFSET(Sheet1!$d$2,,,COUNTA(Sheet1!$d:$d)-1)3、在sheet2的班级下面（a2）单元格输入公式：=IF(ROW()-1>COUNTA(X),"",INDEX(Sheet1!d:d,SMALL(IF(MATCH(X,X,)=ROW(X)-1,ROW(X),65536),ROW(1:1)))&"")输入后要按Ctrl＋Shift＋Enter组合键锁定数组公式。A2单元格的公式输入好以后，再往下拖a2单元格的填充柄，直至所有班级全部显示出来。4、在sheet2的班级列后再插入一列，取名“各班人数”，先计算各班人数。计算公式如下：=IF($A2<>"",SUMPRODUCT((Sheet1!$D$2:Sheet1!$D$2000=$A2)*1),"")5、各科的计算公式：语文：=IF($a2<>"",SUMPRODUCT((sheet1!$d$2:sheet1!$d$2000=$a2)*sheet1!$f$2:$f$2000),"")/b2数学：=IF($a2<>"",SUMPRODUCT((sheet1!$d$2:sheet1!$d$2000=$a2)*sheet1!$g$2:$g$2000),"")/b2英语：=IF($a2<>"",SUMPRODUCT((sheet1!$d$2:sheet1!$d$2000=$a2)*sheet1!$h$2:$h$2000),"")/b2政治：=IF($a2<>"",SUMPRODUCT((sheet1!$d$2:sheet1!$d$2000=$a2)*sheet1!$i$2:$i$2000),"")/b2历史：=IF($a2<>"",SUMPRODUCT((sheet1!$d$2:sheet1!$d$2000=$a2)*sheet1!$j$2:$j$2000),"")/b2地理：=IF($a2<>"",SUMPRODUCT((sheet1!$d$2:sheet1!$d$2000=$a2)*sheet1!$k$2:$k$2000),"")/b2生物：=IF($a2<>"",SUMPRODUCT((sheet1!$d$2:sheet1!$d$2000=$a2)*sheet1!$l$2:$l$2000),"")/b2物理：=IF($a2<>"",SUMPRODUCT((sheet1!$d$2:sheet1!$d$2000=$a2)*sheet1!$m$2:$m$2000),"")/b2化学：=IF($a2<>"",SUMPRODUCT((sheet1!$d$2:sheet1!$d$2000=$a2)*sheet1!$n$2:$n$2000),"")/b2体育：=IF($a2<>"",SUMPRODUCT((sheet1!$d$2:sheet1!$d$2000=$a2)*sheet1!$o$2:$o$2000),"")/b2健康：=IF($a2<>"",SUMPRODUCT((sheet1!$d$2:sheet1!$d$2000=$a2)*sheet1!$p$2:$p$2000),"")/b2音乐：=IF($a2<>"",SUMPRODUCT((sheet1!$d$2:sheet1!$d$2000=$a2)*sheet1!$q$2:$q$2000),"")/b2美术：=IF($a2<>"",SUMPRODUCT((sheet1!$d$2:sheet1!$d$2000=$a2)*sheet1!$r$2:$r$2000),"")/b2信息：=IF($a2<>"",SUMPRODUCT((sheet1!$d$2:sheet1!$d$2000=$a2)*sheet1!$s$2:$s$2000),"")/b2总分:：=IF($a2<>"",SUMPRODUCT((sheet1!$d$2:sheet1!$d$2000=$a2)*sheet1!$t$2:$t$2000),"")/b2最后结果如下图：