如题所述
假如A列为学号,B列为姓名,C列为成绩,那么可以在D列中用公式:
D2=INDEX(B:B,SMALL(IF(RANK($C$2:$C$50,$C$2:$C$50)<=10,ROW($2:$50),4^8),ROW(1:1)))&""
完成后按Ctrl+Shift+回车键结束,往下拉即可。
或:
D2=IF(ROW()<=11,INDEX(B:B,SMALL(IF($C$2:$C$50=LARGE($C$2:$C$50,ROW(1:1)),ROW($2:$50),4^8),1))&"","")
完成后按Ctrl+Shift+回车键结束,往下拉。
D2=INDEX(B:B,SMALL(IF(RANK($C$2:$C$50,$C$2:$C$50)<=10,ROW($2:$50),4^8),ROW(1:1)))&""
完成后按Ctrl+Shift+回车键结束,往下拉即可。
或:
D2=IF(ROW()<=11,INDEX(B:B,SMALL(IF($C$2:$C$50=LARGE($C$2:$C$50,ROW(1:1)),ROW($2:$50),4^8),1))&"","")
完成后按Ctrl+Shift+回车键结束,往下拉。
温馨提示:答案为网友推荐,仅供参考