|
以下公式全为三键回车(公式复制后,点一下公式编辑栏的任意位置,先按住 ctrl、shift 两个键,然后敲enter键),下拉;
B4
=INDEX(年级!B$1:B$121,SMALL(IF((OFFSET(年级!$C$2:$C$121,,MATCH($F$2,年级!$D$1:$J$1,),)=C4)*(年级!$A$2:$A$121>=$B$2*100)*(年级!$A$2:$A$121<($B$2+1)*100),ROW($2:$121)),COUNTIF($C$3:C4,C4)))
C4
=LARGE(IF((年级!$A$2:$A$121>=$B$2*100)*(年级!$A$2:$A$121<($B$2+1)*100),OFFSET(年级!$C$2:$C$121,,MATCH($F$2,年级!$D$1:$J$1,),)),ROW(A1))
F4
=INDEX(年级!B$1:B$121,SMALL(IF((OFFSET(年级!$C$2:$C$121,,MATCH($F$2,年级!$D$1:$J$1,),)=G4)*(年级!$A$2:$A$121>=$B$2*100)*(年级!$A$2:$A$121<($B$2+1)*100),ROW($2:$121)),COUNTIF($G$3:G4,G4)+COUNTIF(C$4:C$34,G4)))
G4
=LARGE(IF((年级!$A$2:$A$121>=$B$2*100)*(年级!$A$2:$A$121<($B$2+1)*100),OFFSET(年级!$C$2:$C$121,,MATCH($F$2,年级!$D$1:$J$1,),)),ROW(A32))
|
|