各班前幾名 取出 (11407)

E2:E21=COUNTIFS(B:B,B2,D:D,">"&D2)+1

J2:L2 下拉 {=IFERROR(INDEX(B:B,RIGHT(SMALL(IF(($E$2:$E$21>=$H$3)*($E$2:$E$21<=$H$4),LEFT($B$2:$B$21)/1%%+$E$2:$E$21/1%+ROW($2:$21)),ROW(A1)),2)),"")

無利用E列名次 (慢){=IFERROR(INDEX(B:B,RIGHT(SMALL(IF((COUNTIFS($B:$B,$B$2:$B$21,$D:$D,"<"&$D$2:$D$21)+1>=$H$3)*(COUNTIFS($B:$B,$B$2:$B$21,$D:$D,">"&$D$2:$D$21)+1<=$H$4),LEFT($B$2:$B$21)/1%%+COUNTIFS($B:$B,$B$2:$B$21,$D:$D,">"&$D$2:$D$21)/1%+ROW($2:$21)),ROW(A1)),2)),"")