公司按地区人员的得分，各地区取前三分之一的人评为优秀，如何设置公式？

sandays

1. =LET(a,FILTER(C$2:C$564,A$2:A$564=A2),IF(C2>=LARGE(a,CEILING(ROWS(a)/3,1)),"优",""))

复制代码

家有囡囡

=MAP(A2:A564,LAMBDA(x,LET(f,SORT(FILTER(B2:C564,A2:A564=x),2,-1),REPT("优",OR(TAKE(f,ROUNDUP(ROWS(f)/3,0))=OFFSET(x,,1,,2))))))
一键出，是分地区按人名和分数一起比对

=MAP(A2:A564,LAMBDA(x,LET(f,SORT(FILTER(C2:C564,A2:A564=x),,-1),REPT("优",OR(TAKE(f,ROUNDUP(ROWS(f)/3,0))=OFFSET(x,,2))))))
一键出，只分地区按分数进行比对，分数相同会同时评优

mjm04

=IF(SUM(N($B2=LET(x,SORT(FILTER($B$2:$C$564,$A$2:$A$564=A2),2,-1),DROP(TAKE(x,TRUNC(ROWS(x)/3,0)),,-1))))=1,"优","")

你是那块小饼干

=LET(_T,SORT(FILTER($C$2:$C$564,$A$2:$A$564=A2),,-1),IF(MATCH(C2,_T,0)<=ROUNDUP(ROWS(_T)/3,0),"优",""))

你是那块小饼干

1. =LET(_T,SORT(FILTER($C$2:$C$564,$A$2:$A$564=A2),,-1),REPT("优",MATCH(C2,_T,0)<=ROUNDUP(ROWS(_T)/3,0)))

复制代码

mykitami

D2公式
=LET(all,SORT(FILTER($B$2:$C$999,$A$2:$A$999=A2),2,-1),REPT("优",OR(B2=TAKE(TAKE(all,,1),ROUNDUP(ROWS(all)/3,0)))))

nqwl

=LET(x,IF(($A$2:$A$564=A2),$C$2:$C$564,""),IF(C2>=LARGE(x,ROUNDUP(COUNT(x)/3,0)),"优",""))

或
=LET(x,FILTER($C$2:$C$564,$A$2:$A$564=A2),IF(C2>=LARGE(x,ROUNDUP(COUNT(x)/3,0)),"优",""))

nqwl

=IF(COUNTIFS(A:A,A2,C:C,">="&C2)<=CEILING(COUNTIF(A:A,A2)/3,1),"优","")

一杯绿茶来了

=IF(C2>=LARGE(IF($A$2:$A$564=A2,$C$2:$C$564),CEILING(COUNTIF(A:A,A2)/3,1)),"优","")