Excel應用_給成績按比例劃分ABCD等的幾種方法

        假如學生姓名存在a3:a762中，某科成績存在g3:g762中，滿分150分，四個等次的劃分有兩種分法：

        一、分數從高到低算，總人數的第一個四分之一為A，第二個四分之一為B，第三個四分之一為C，第四個四分之一為D，將等次填入到H列。

          有幾個公式可以實現，下面只給出H3的公式，其他下拉即可。

          =LOOKUP(RANK(G3,G$3:G$762),COUNT($G:$G)*{0,0.25,0.5,0.75}+1,{"A","B","C","D"})

          =CHOOSE(MATCH(RANK(G3,G$3:G$762),COUNT($G:$G)*{0,0.25,0.5,0.75}+1),"A","B","C","D")

          =HLOOKUP(PERCENTRANK($G$3:$G$762,G3,2),{0,0.25,0.5,0.75;"D","C","B","A"},2)

          =IF(G3>PERCENTILE($G$3:$G$762,0.75),"A",IF(G3>PERCENTILE($G$3:$G$762,0.5),"B",IF(G3>PERCENTILE($G$3:$G$762,0.25),"C","D")))

          =CHAR(64+MATCH(RANK(G3,G$3:G$762),COUNT($G:$G)*{0,0.25,0.5,0.75}+1))

        二、分數大於等於150的0.9（135分及其以上）為A，小於150的0.9大於等於150的0.8（120分到134分）的為B，小於150的0.8大於等於150的0.6（90分到119分）的為C，小於150的0.6（89分及其以下）的為D，將等次填入J列。

         有幾個公式可以實現，下面只給出J3的公式，其他下拉即可。

=LOOKUP(G3,150*{0,0.6,0.8,0.9},{"D","C","B","A"})

=VLOOKUP(G3,{0,"D";90,"C";120,"B";135,"A"},2)

=HLOOKUP(G3,{0,90,120,135;"D","C","B","A"},2)

=CHOOSE(MATCH(G3,150*{0,0.6,0.8,0.9}),"D","C","B","A")

=HLOOKUP(G3,CHOOSE({1;2},{0,90,120,135},{"D","C","B","A"}),2)

=IF(G3>=135,"優",IF(G3>=120,"好",IF(G3>=60,"中","差")))