用Excel函数实现业绩评价的方法
发布网友
发布时间：2024-06-01 07:36
共1个回答
热心网友
时间：2024-06-01 22:53
先看下方案例表格
要求根据完成率实现自动排名和分值计算。
01
绩效排名
数据排名不是排序，也不需要手工填写。
在Excel软件中有个计算排名的函数RANK。
RANK的语法结构：
RANK(计算数据，数据范围，升降序参数)
参数为0或者不填，是降序;其他数值是升序。
所以在E2单元格中输入公式
=RANK(D2,$D$2:$D$6)
并快速填充到E列其他单元格即可。
需要说明的是
RANK函数遇到重复数据
排名按 1,2,2,4这种方式进行
不是中国式排名1,2,2,3的方式。
02
全域线性插值评价
评价说明：
完成率60%-100%线性计9分
分别对应6-15分值
区间外按同比例计算
可以使用公式直接计算
在E2单元格中输入公式
=6+(D2-0.6)/0.4*9
具体不做解释
也可以使用TREND函数做插值计算
TREND函数的语法结构：
TREND(Y值区间，X值区间，X计算值)
数据区间用大括号{ }括住，中间是分号，表示数组范围。当然也可以引用具体单元格数据。
在E2单元格中输入公式
=TREND({6;15},{0.6;1},D2)
03
区间线性插值评价
评价说明：
完成率60%起评，6分为起始分值
60%-100%线性计9分
分别对应6-15分值
低于60%，按0分计算
高于100%，按15分计算
只有60%-100%区间是按线性插值计算，其他区间是固定值。
可以用IF嵌套函数计算
=IF(D21,15,6+(D2-0.6)/0.4*9))
或者使用IF+TREND函数
=IF(D21,15,TREND({6;15},{0.6;1},D2)))
TREND函数常常根据历史数据做预测。案例中为了简化只设定了一个线性区间，如有多个线性区间，要用OFFSET+MATCH+TREND组合函数自动计算，有兴趣的伙伴可以深入研究。
04
区间阶梯评价
评价说明：
阶梯计算分值
0≤完成率<60%，得0分;
60%≤完成率<70%，得6分;
70%≤完成率<80%，得8分;
80%≤完成率<90%，得10分;
90%≤完成率<100%，得12分;
完成率≥100%，得15分。
阶梯评价也是常用的评价方法，可以计算数值，也可以做ABCD等级评价。个人所得税的税率计算就是用的阶梯计算方法，一些企业销售佣金的计算也是采取这种方法。
因为区间较多，可以在一个区域范围，设置区间和对应分值。要注意区间升序排列，以及区间与分值的对应关系。
可以用VLOOKUP函数的模糊查找功能。VLOOKUP函数最后一个参数为0代表精确查找，参数为1代表模糊查找。具体VLOOKUP用法不再详细解释了。
在E2单元格输入公式：
=VLOOKUP(D2,$G$1:$H$6,2,1)
也可以用多层IF做嵌套判断，相对麻烦不做推荐。
