查找文科第40名学生的总成绩(CTRL+SHIFT+ENTER加大括号)
=LARGE(IF(总成绩!A2:A3000="文科",总成绩!G2:G3000,0),40)
I类全校文前40名
=FIND("前",B3)
=FIND("名",B3)
=RIGHT(B3,LEN(B3)-FIND("前",B3)) 40名
=LEFT(RIGHT(B3,LEN(B3)-FIND("前",B3)),LEN(RIGHT(B3,LEN(B3)-FIND("前",B3)))-1) 40
=LARGE(IF(总成绩!$A$2:$A$3000="文科",总成绩!$P$2:$P$3000,0),LEFT(RIGHT(B3,LEN(B3)-FIND("前",B3)),LEN(RIGHT(B3,LEN(B3)-FIND("前",B3)))-1))