《excel分班统计成绩》由会员分享,可在线阅读,更多相关《excel分班统计成绩(2页珍藏版)》请在金锄头文库上搜索。
1、用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组合键锁定数组
2、公式。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
3、$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
4、)*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:sh
5、eet1!$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最后结果如下图: