excel分班统计成绩
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
用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),655 36),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 最后结果如下图：