职场技能 “每高于平均值5%加0.1分，每低5%扣0.1分”，到底如何解？

在工作中我们遇到的根据领先度打分的问题，哪个函数可以实现？...



在实际工作中我们经常遇到根据领先度打分的问题，比如：

“平均值27.4%为0.5分，每高于平均值5%加0.1分，1分封顶。低于平均值扣分，每低5%扣0.1分，扣为0分为止。”

有哪个函数可以实现？诸如此类吧。

针对这个问题，我的操作步骤如下：把评分标准“均值27.4%为0.5分，每高于平均值5%加0.1分，1分封顶。低于平均值扣分，每低5%扣0.1分，扣为0分为止。”的计分梯度表先列出来（见图1）。

指标下限确定方法：

• 在H6单元格输入公式“=H7-5%”，然后鼠标移动到H6单元格右下角待光标变成实心的“+”时，按鼠标左键向上拖动填充H5到H2单元格。
• 在H8单元格输入公式“=H7+5%”,同样的方法向下拖拽填充到H12单元格。得到评分标准的评分梯度表：图2。
• “0分”的指标下限调整为0%。从而得到评分梯度最终表：图3。

• 评分梯度表(图3)可以理解为：指标大小在区间[0%，7.4%）内时得0分，指标大小在[7.4%,12.4)的区间内时得0.1分，依次类推。指标大小在区间[52.4%,100.0%]内时得1分。如图4所示。

还原问题的具体情景如图5所示：A列是评价对象，B列是评价对象指标的具体数值，根据评分规则在C列打分。

在C2单元格填入公式：

LOOKUP函数表达式1：=LOOKUP(B2，$H$2:$I$12)

LOOKUP函数表达式2：=LOOKUP(B2,{0,0.074,0.124,0.174,0.224,0.274,0.324,0.374,0.424,0.474,0.524},{0,0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8,0.9,1})

LOOKUP函数表达式3：=LOOKUP(B2,$H$2:$H$12,$I$2:$I$12)

将I2:I12的区域拖拽到K13:K23后，公式变为

LOOKUP函数表达式4：=LOOKUP(B2,$H$2:$H$12,$K$13:$K$23)。上述的LOOKUP公式的几个版本均适用，由此可以总结出LOOKUP公式的用法：

• 第1参数是判断的指标数值，第2、第3参数都是数组；
• 第2和第3参数可以是基本形式展现的数组，即“{a,b,c,d}”形式。也可以是区域数组的形式（$H$2:$H$12）。
• 指标数值在第2参数的数组中判定归属区间时，是向下包含的。如图4和图5所示：每一梯度都是大于等于指标下限，小于（不等于）上一梯度的指标下限。
• 在第1个函数表达式里，其实只有一个包含两列的数组区域，可以理解为第2和第3参数的数组区域合并了，本来两个区域就是相邻的嘛，写着也省事。这种情况下，LOOKUP函数会默认第一列是第2参数的数组区域，最后一列是第3参数的数组区域。
• 如果第3参数省略，而第2参数仅为1列的区域数组或者“{a,b,c,d}”形式的话，返回结果即为归属区间的下限值。

用法禁忌

• 第2参数数组里的数值必须是按从小到大排列的。
• 第2参数数组的最小值一定要小于或等于指标的取值范围的最小值。
• 第2参数数组里数值个数要小于等于第3参数的数组里数值的个数。

好啦，今天的技术交流就到这里了，新技能，你get了吗？

    关注 君友咨询