評定獎項 依比例 (14696)

J2=LOOKUP(RANK(B2,B:B)/(COUNTA(A:A)-1),SUBTOTAL(9,OFFSET(F$1,,,{1;2;3;4}))%,E$2:E$5)

K2=LOOKUP(RANK(B2,B:B)-1,SUBTOTAL(9,OFFSET(G$1,,,{1;2;3;4})),E$2:E$5)

無用表列=LOOKUP(B2,PERCENTILE(B$2:B$28,{0,40,70,90}%),{"鼓励奖","三等奖","二等奖","一等奖"})