怎么提取出正确的班级，求两个班的平均分

DFWZP

一个英语老师带两个班的，我需要统计两个班的参考人数和平均分。班级名称如下图录入，我不想使用辅助列，用的left和right函数提取班级，
批量填充的时候，1-9班没有问题，但是到10班以后，由于字符多了一位，
就会出错，请高手指点，只想用函数，批量下拉，不想一个个改。 成绩 积分.zip (158.17 KB, 下载次数: 27)

2021-10-23 16:24 上传

点击文件名下载附件

E风卍行H

首先，两个班的总平均分不能直接用两个班各自的平均分相加再除以2，之前的公式逻辑是错的。
可以参考以下公式：
D3单元格公式：
=IFERROR(IF(FIND(CHAR(10),A3),
COUNTIFS(成绩!$C$3:$C$9994,LEFT(A3,FIND(CHAR(10),A3)-1),成绩!$H$3:$H$9994,">=0")+
COUNTIFS(成绩!$C$3:$C$9994,MID(A3,FIND(CHAR(10),A3)+1,3),成绩!$H$3:$H$9994,">=0")),
COUNTIFS(成绩!$C$3:$C$9994,A3,成绩!$H$3:$H$9994,">=0"))

E3单元格公式：
=IFERROR(IF(FIND(CHAR(10),A3),
SUMIF(成绩!$C$3:$C$9994,TRIM(LEFT(SUBSTITUTE(A3,CHAR(10),REPT(" ",2)),3)),成绩!$H$3:$H$9994)
+SUMIF(成绩!$C$3:$C$9994,TRIM(RIGHT(SUBSTITUTE(A3,CHAR(10),REPT(" ",2)),3)),成绩!$H$3:$H$9994)),
SUMIF(成绩!$C$3:$C$9994,A3,成绩!$H$3:$H$9994))/D3

三秋之叶

=AVERAGE(AVERAGEIF(成绩!C:C,CLEAN(MIDB(A3,{1,5},{4,5})),成绩!$H:$H))

E风卍行H

三秋之叶 发表于 2021-10-23 17:22
=AVERAGE(AVERAGEIF(成绩!C:C,CLEAN(MIDB(A3,{1,5},{4,5})),成绩!$H:$H))

MIDB(A3,{1,5},{4,5}) 这个思路厉害，学习了，谢谢！
不过这样求平均是不是有问题？比如1班3个同学分别1，2，3，平均分为2；2班2个同学分别6，8，平均分为7。如果按这种算法，平均分就是（2+7）/2=4.5，但实际平均分应该是 （1+2+3+6+8）/5=4。

E风卍行H

用上大神的数组公式就简单多了：
D3：=SUM(COUNTIFS(成绩!$C$3:$C$9994,CLEAN(MIDB(A3,{1,5},{4,5})),成绩!$H$3:$H$9994,">=0"))
E3：=SUM(SUMIF(成绩!$C$3:$C$9994,CLEAN(MIDB(A3,{1,5},{4,5})),成绩!$H$3:$H$9994))/D3

jisijie630

D3=SUM(COUNTIF(成绩!C:C,SUBSTITUTE(MIDB(A3,{1,5},{4,5}),CHAR(10),)))
E3=SUM(SUMIF(成绩!C:C,TRIM(MID(SUBSTITUTE(A3,CHAR(10),REPT(" ",9)),9*{1,2}-8,9)),成绩!H:H))/D3

jisijie630

三秋之叶 发表于 2021-10-23 17:22
=AVERAGE(AVERAGEIF(成绩!C:C,CLEAN(MIDB(A3,{1,5},{4,5})),成绩!$H:$H))

两个班的平均成绩再平均不对吧？

三秋之叶

jisijie630 发表于 2021-10-23 18:52
两个班的平均成绩再平均不对吧？

你说的很有道理。我仅仅是对楼主公式进行修改，没有你考虑这么细致周全，其实换个角度，揣测一下楼主的意思，既然老师都是代课两个【班级】，求两个【班级】的平均分，也未尝不可，个人愚见

jisijie630

三秋之叶 发表于 2021-10-23 19:04
你说的很有道理。我仅仅是对楼主公式进行修改，没有你考虑这么细致周全，其实换个角度，揣测一下楼主的意 ...

如果两个班的人数相等可以，不相等会出错。

三秋之叶

jisijie630 发表于 2021-10-23 19:11
如果两个班的人数相等可以，不相等会出错。

请把【班级】看成一个整体来理解，类似于每个老师带2个学生，只是为了统计的方便，在实际中很少把两个班级的所有人混合在一起统计的。你讲的我完全理解，是严格意义上的数学平均