前20%为高,用下面的公式实现:
=IF(COUNTIFS(C:C,C3,B:B,">="&B3)<COUNTIF(C:C,C3)*0.2,"高","")
其中COUNTIFS(C:C,C3,B:B,">="&B3)表示本团队比自己大的人数,结果为排名,COUNTIF(C:C,C3)表示本团队总人数,IF判断实现前20%为高,如下图:
后20%类似的实现,使用比自己低的人数来判断,最后嵌套得出最终公式:
=IF(COUNTIFS(C:C,C3,B:B,">="&B3)<COUNTIF(C:C,C3)*0.2,"高",
IF(COUNTIFS(C:C,C3,B:B,"<="&B3)<COUNTIF(C:C,C3)*0.2,"低",
"中"))
如下图: