Question

excel 统计某列数据前20%并显示优秀

工作中的实用问题：G列为考核分数，F列为考核等次，需要在F列直接显示“优秀”或“称职”，确定依据是G列排名在前面20%的人为“优秀”，剩下80%为“称职”。请问如何设置函数？

Answer 1

=IF(G$1:G$100>=large(G$1:G$100,20%*count(G$1:G$100)),"优秀","称职")
1:100的区域视你表格的数据自己定。

Answer 2

=IF(G1>=PERCENTILE($G$1:$G$100,0.8),"优秀","称职")

Answer 3

f2=IF(RANK(G2,$G:$G)/COUNTIF($G:$G,<>"")<=0.2,"优秀","称职")
下拉即可

Answer 4

=IF(RANK(G1,$G$1:$G$10,)<=ROUND(COUNTA(G:G)*0.2,),"优秀","合格")

Answer 5

F1=IF(G1>=PERCENTILE($G$1:$G$100,0.8),"优秀","称职")
下拉即可。