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

最近推送的几篇文章：

• 如何用公式返回第一个（最后一个）非空单元格的值，建议收藏备用
  

• QuickLook：不用打开文件，也能轻松预览文件内容

• 十款漂亮的字体下载，可免费商用，做PPT必备

• 【管理会计】如何用Excel建立预测模型，进行财务预测
  

• 【技巧】Excel中艾特号@用法技巧总结，第二个技巧太惊艳了
  

• 百度盘下载很慢？不存在的，不花钱从60K提速到2.5M

·  正  ·  文  ·  来  ·  啦  ·

痛点

 

经常有读者朋友问：

要根据金额大小所处的等级来计算提成或奖金，要用什么公式？如下图：

这种需求，只需将数据表整理一下，整理后的奖金系数表如下图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函数始终无法得其门而入