我太难了，请各位老师帮忙来个公式

bentenwu

我太难了，请各位老师帮忙来个公式，谢谢各位了。说明在表中。

liyanghu

数组公式供参考
D4=IFERROR(MATCH(RANK(B4,B$4:B$19),SMALL(IF(FREQUENCY(RANK(B$4:B$19,B$4:B$19),ROW($1:$16))>0,ROW($1:$16)),{3,2,1}),)*10^3,IFERROR(-MATCH(RANK(B4,B$4:B$19),LARGE(IF(FREQUENCY(RANK(B$4:B$19,B$4:B$19),ROW($1:$16))>0,ROW($1:$16)),{3,2,1}),)*10^3,))

liyanghu

刚才没有考虑到标准值和最低要求
重新做一个数组大火车

D3=IFERROR(LOOKUP(MATCH(C4,SMALL(IF(FREQUENCY(((B$4:B$19>B$1)*(B$4:B$19)>0)*RANK(B$4:B$19,B$4:B$19),RANK(B$4:B$19,B$4:B$19))>0,RANK(B$4:B$19,B$4:B$19)),ROW($1:$3)),),ROW($1:$3),{3,2,1})*10^3,IFERROR(-LOOKUP(MATCH(C4,LARGE(IF(FREQUENCY(((B$4:B$19<B$2)*(B$4:B$19)>0)*RANK(B$4:B$19,B$4:B$19),RANK(B$4:B$19,B$4:B$19))>0,RANK(B$4:B$19,B$4:B$19)),ROW($1:$3)),),ROW($1:$3),{3,2,1})*10^3,))

leomor

D4=IF(AND(C4=MIN($C:$C),B4>$B$1),"3000",IF(AND(C4=MIN($C:$C)+1,B4>$B$1),"2000",IF(AND(C4=MIN($C:$C)+2,B4>$B$1),"1000",IF(AND(C4=MAX(C:C),B4<$B$2),"-3000",IF(AND(C4=MAX(C:C)-1,B4<$B$2),"-2000",IF(AND(C4=MAX(C:C)-2,B4<$B$2),"-1000"))))))*1

bentenwu

liyanghu 发表于 2020-1-21 12:07
刚才没有考虑到标准值和最低要求
重新做一个数组大火车

谢谢，好玄的公式。有没有简单点的

bentenwu

leomor 发表于 2020-1-21 13:50
D4=IF(AND(C4=MIN($C:$C),B4>$B$1),"3000",IF(AND(C4=MIN($C:$C)+1,B4>$B$1),"2000",IF(AND(C4=MIN($C:$C)+ ...

这个公式简单些，好理解，但是对排名相同的没有考虑，不过应该不错了

liyanghu

bentenwu 发表于 2020-1-21 14:55
谢谢，好玄的公式。有没有简单点的

不要看着长久觉得头晕，其实拆开也挺简单的

这个求前三奖励的

LOOKUP(MATCH(C4,SMALL(IF(FREQUENCY(((B$4:B$19>B$1)*(B$4:B$19)>0)*RANK(B$4:B$19,B$4:B$19),RANK(B$4:B$19,B$4:B$19))>0,RANK(B$4:B$19,B$4:B$19)),ROW($1:$3)),),ROW($1:$3),{3,2,1})*10^3

这个求后3惩罚的

-LOOKUP(MATCH(C4,LARGE(IF(FREQUENCY(((B$4:B$19<B$2)*(B$4:B$19)>0)*RANK(B$4:B$19,B$4:B$19),RANK(B$4:B$19,B$4:B$19))>0,RANK(B$4:B$19,B$4:B$19)),ROW($1:$3)),),ROW($1:$3),{3,2,1})*10^3

oldshuijing

=IF(OR(B4>$B$1,B4<$B$2),IFERROR(VLOOKUP(SUMPRODUCT(($B$4:$B$19>=B4)/COUNTIF($B$4:$B$19,$B$4:$B$19)),{1,3000;2,2000;3,1000},2,0),0)-IFERROR(VLOOKUP(SUMPRODUCT(($B$4:$B$19<=B4)/COUNTIF($B$4:$B$19,$B$4:$B$19)),{1,3000;2,2000;3,1000},2,0),0),"")     这个公式是先把排名按中国式排名算  这样符合扣款或者奖励要求 同名次的扣或奖一样   要公式短的话  把你的排名替换成中国式排名  即
SUMPRODUCT(($B$4:$B$19>=B4)/COUNTIF($B$4:$B$19,$B$4:$B$19)


然后考核结果公式变为：=IF(OR(B4>$B$1,B4<$B$2),IFERROR(VLOOKUP(C4,{1,3000;2,2000;3,1000},2,0),0)-IFERROR(VLOOKUP(16-C4,{1,3000;2,2000;3,1000},2,0),0),"")   下拉

hcm19522

D4:D19{=SUM(FREQUENCY(C4,IF({1;0},SMALL(IF(MATCH(C$4:C$19,C$4:C$19,)=ROW($1:$16),C$4:C$19),{1,2,3}),LARGE(IF(MATCH(C$4:C$19,C$4:C$19,)=ROW($1:$16),C$4:C$19),{3,2,1})-1))*{3;2;1;0;-1;-2;-3})*1000*IFERROR(LOOKUP(B4,IF({1,0},B$2,B$1))=B$1,1)


前3名不考慮重複=SUM(FREQUENCY(C4,IF({1;0},{1,2,3},LARGE(IF(MATCH(C$4:C$19,C$4:C$19,)=ROW($1:$16),C$4:C$19),{3,2,1})-1))*{3;2;1;0;-1;-2;-3})*1000*IFERROR(LOOKUP(B4,IF({1,0},B$2,B$1))=B$1,1)

liyanghu

我太难了，想了好久，终于简化了一点。典型的技术不够长度凑

=IFERROR(MATCH(C4,SMALL(IF(FREQUENCY((B$4:B$19>B$1)*(C$4:C$19),(B$4:B$19>B$1)*(C$4:C$19)),(C$4:C$19)),{3,2,1}),)*10^3,-MATCH(C4,LARGE(IF(FREQUENCY((B$4:B$19<B$2)*(C$4:C$19),(B$4:B$19<B$2)*(C$4:C$19)),(C$4:C$19)),{3,2,1}),)*10^3)