一个简单的公式，解决按不同等级自动计算提成的难题

最近推送的几篇文章：

·  正  ·  文  ·  来  ·  啦  ·

痛点
经常有读者朋友问：
要根据金额大小所处的等级来计算提成或奖金，要用什么公式？如下图：
这种需求，只需将数据表整理一下，整理后的奖金系数表如下图A1:B6单元格所示。A列综合KPI从小到大升序排序。然后使用VLOOKUP的近似匹配模式（第四参数为1）就可以了。C10单元格的公式：
=VLOOKUP(B10,$A$2:$B$6,2,1)
解释：

  • VLOOKUP为1时使用近似匹配模式，这种模式下，如果没找到查找值，会返回小于等于查找值的最大值。

注意：使用此模式，要求查找区域（A2:A6）升序排列。
比如本案例的C10单元格公式，在A2:A6单元格区域查找88%，肯定找不到，那么就返回小于它并且最接近它的数值，所对应的奖金系数。小于88%的值有0、10%、30%、70%，最接近它的值为70%，所以本查找公式为返回70%所对应的奖金系数0.8。

  • VLOOKUP第四参数为0时使用精确匹配模式。只有找到完全等于查找值（第一参数）时，才会返回对应结果。

比如，如果将上面的公式第四参数改为0，使用的就是精确匹配模式，只有C11单元格会返回结果（在A2:A6单元格区域中有70%这个数值），其他单元格的公式结果为#N/A，表示未找到查找值。
本案例中，10%的综合KPI的系数为1.2，70%综合KPI系数为0.8，那如果希望将这些区间临界点列入下一档（也就是10%为1.5，70%为1），该如何计算呢。这时，我们只需将查找值减去一个很小的数值（比如万分之一）即可。公式：
=VLOOKUP(B10-1%%,$A$2:$B$6,2,1)
上面的公式在KPI为0时，会查找不到相应的值，结果为#N/A，
将公式补充完善一下：
=VLOOKUP(MAX(B10-1%%,0),$A$2:$B$6,2,1)
如果用的是365最新版，还可以使用XLOOKUP，在未找到查找值时直接返回指定值（第四参数）：
=XLOOKUP(B10-1%%,A2:A6,B2:B6,1.5,-1,1)

如果，你对函数公式一窍不通

如果，你掌握Excel函数不到十个
如果，你看到Excel函数公式就头痛发作
如果，你学习Excel函数始终无法得其门而入

赞 (0)

相关推荐