思路:判断A部门奖金时,先查看相临是否有相同排名的:分别判断当4个(3个,2个)并列排名时返回的百分数。如:
B5中输入:=IF(AND(B3=C3,C3=D3,D3=E3),0.25*A5,IF(AND(B3=C3,C3=D3),0.3*A5,IF(B3=C3,0.35*A5,0.4*A5)))
C5中输入:=IF(AND(B3=C3,C3=D3,D3=E3),0.25*A5,IF(AND(C3=D3,D3=E3),0.2*A5,IF(AND(B3=C3,C3=D3),0.3*A5,IF(B3=C3,0.35*A5,IF(C3=D3,0.25*A5,0.3*A5)))))
D5中输入:=IF(AND(B3=C3,C3=D3,D3=E3),0.25*A5,IF(AND(C3=D3,D3=E3),0.2*A5,IF(AND(B3=C3,C3=D3),0.3*A5,IF(C3=D3,0.25*A5,IF(D3=E3,0.15*A5,0.2*A5)))))
E5中输入:=IF(AND(B3=C3,C3=D3,D3=E3),0.25*A5,IF(AND(C3=D3,D3=E3),0.2*A5,IF(D3=E3,0.15*A5,0.1*A5)))
其它部门类推。但如果考虑到排名并非按降序排列,那就更复杂了;先按排名排序上式是可以得出结果的。
温馨提示:答案为网友推荐,仅供参考