全部 各学校 各班级 分年级排名 (15112)

H3=SUM(D3:G3)

I3:I19=SUMPRODUCT((LEFT(B$3:B$19)=LEFT(B3))*(MMULT(D$3:G$19,{1;1;1;1})>SUM(D3:G3)))+1

J3:J19=SUMPRODUCT((A$3:A$19=A3)*(LEFT(B$3:B$19)=LEFT(B3))*(MMULT(D$3:G$19,{1;1;1;1})>SUM(D3:G3)))+1

K3:K19=SUMPRODUCT((A$3:A$19=A3)*(B$3:B$19=B3)*(MMULT(D$3:G$19,{1;1;1;1})>SUM(D3:G3)))+1

M3:O19{=SUM(IF(IF(I$2=$I$2,1,$A$3:$A$19=$A3)*IF(I$2=$K$2,RIGHT($B$3:$B$19)=RIGHT($B3),1)*(LEFT($B$3:$B$19)=LEFT($B3)),(MMULT($D$3:$G$19,{1;1;1;1})>SUM($D3:$G3))*1))+1