请问如何统计每个班里各个成绩有几个A，几个B,几个C

llp0206

请问如何统计年级成绩表里每个班里各个成绩有几个A，几个B,几个C？[url=]ABC2.zip[/url]

年级成绩

统计表

inrever

1. =SUM((OFFSET($A$2:$A$297,,MATCH(IF(OFFSET(M$2,-1,)<>"",OFFSET(M$2,-1,),IF(OFFSET(M$2,-1,-1)<>"",OFFSET(M$2,-1,-1),OFFSET(M$2,-1,-2))),$B$1:$J$1,))=LEFT(M$2,1))*($A$2:$A$297=$L3))

复制代码

3190496160

ABC2.rar (128.51 KB, 下载次数: 7)

2024-8-24 10:40 上传

点击文件名下载附件

3190496160

后面几个科目，自己依样画葫芦吧

杨杨1991315

M3=SUM(($A$2:$A$297=$L3)*($B$1:$J$1=LOOKUP("座",$M$1:M$1))*(IF(LOOKUP("座",$M$1:M$1)="总成绩",$B$2:$B$297,$B$2:$J$297)=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(M$2,"等个数","")," ",""),"个数","")))
Ctrl+shift+ENTER，结束

zjnfxxh

..................

nie414006

先把表头格式刷刷到空白处，把表头去合并单元格并填充，然后用刷的空白处再格式刷刷一下表头

arnosue

1. =SUMPRODUCT((LOOKUP("做",$M$1:M$1)=$B$1:$J$1)*($A$2:$A$297=$L3)*(LEFT(M$2,1)=$B$2:$J$297))

复制代码

红框中名字要改成一样的

菜狗一只

1. 总成绩=COUNTA(FILTER($B$2:$B$297,(($A$2:$A$297=OFFSET($L$2,ROW(A1),0))*($B$2:$B$297=LEFT(M$2,1)))))

复制代码

1. 各科目=LET(Y,INDEX($A$1:$J$297,,MATCH(P$1,$A$1:$J$1,0)),SUM(IF(FILTER(Y,($A$1:$A$297=OFFSET($L$2,ROW(A1),0)))=TRIM(P$2),1,"")))

复制代码

为方便处理，对统计表格进行了修改

nie414006