一、成績等級公式
基礎用法：=IF(B2<60,"不合格",IF(B2<75,"及格",IF(B2<90,"良好","優秀")))
進階用法：=VLOOKUP(B2,{0,"不合格";60,"合格";75,"良好";90,"優秀"},2)
=LOOKUP(B2,{0,60,75,90},{"不合格";"合格";"良好";"優秀"})
=CHOOSE(MATCH(B2,{0,60,75,90},1),"不合格","合格","良好","優秀")
=INDEX({"不合格";"合格";"良好";"優秀"},MATCH(B2,{0,60,75,90},1))
二、成績排名
從大到小 =LARGE($B$2:$B$13,{1;2;3;4;5;6;7;8;9;10})
從小到大 =SMALL($B$2:$B$13,{1;2;3;4;5;6;7;8;9;10})
排名 =RANK(B2,$B$2:$B$13)
大於95學生姓名 =IF(ROW(A1)>COUNTIF($B:$B,"<95"),"",INDEX(A:A,SMALL(IF($B$2:$B$13<95,ROW($2:$13)),ROW(A1))))
3月份考試的學生名單： =IF(ROW(A2)>SUMPRODUCT(N(MONTH($C$2:$C$13)=3)),"",INDEX(A:A,SMALL(IF(MONTH($C$2:$C$13)=4,ROW($2:$13)),ROW(A2))))
分數段人數 =FREQUENCY($B$2:$B$13,{69.99;79.99;89.99})
分數最低三個人的姓名 =VLOOKUP(SMALL($B$2:$B$13,E2),CHOOSE({1,2},$B$2:$B$13,$A$2:$A$13),2,0)
列出成績最高人的姓名 =INDEX(A:A,MAX((MAX(B2:B14)=B2:B14)*ROW(B2:B14)))
閱讀更多 淡墨留餘香 的文章
