excel分班统计成绩.doc

用excel分班统计成绩如下图，sheet1中有1000多条记录，现在要在sheet2中统计出各班各科的人平均分。1、先将班级和各科名称复制到sheet2，2、在sheet2的班级上（d2）插入名称，定义名称为x，引用中输入：=OFFSET(Sheet1!$d$2,COUNTA(Sheet1!$d:$d)-1)3、在sheet2的班级下面（a2）单元格输入公式：=IF(ROW()-1COUNTA(X),INDEX(Sheet1!d:d,SMALL(IF(MATCH(X,X,)=ROW(X)-1,ROW(X),65536),ROW(1:1)&)输入后要按CtrlShiftEnter组合键锁定数组公式。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总分:：=I