利用Vlookup函数实现阶梯型销售提成的计算

Question

如题所述

Answer 1

前两天给一家公司培训完Excel课程之后，有一位学员在课程结束后问了我一个问题。

怎样实现阶梯型销售提成的计算。何为阶梯型销售提成？如下表所示的效果：

假设销售员张三本月的销售业绩为10万，则其中8万的部分按1.5%计算提成，即80000*1.5%=1200，10万-8万也即2万的部分按2.0%计算提成，即20000*2.0%=400，因此本月张三的销售提成金额为1600元。

这种阶梯型计算的典型案例还有个人所得税，也是有类似的计算阶梯模型。

那要如何实现这个计算需求呢？首先将提成计算的阶梯数据更换成如下图所示的效果。

这个表格中的B列为销售额的起始范围，D列为要扣除的部分，其中D2单元格为0值，这个部分不用扣除，而D3单元格的公式为 =(C3-C2)*B3+D2 。

接下来我们来到销售人员的业绩表中，利用VLOOKUP函数的区间查询功能来实现阶梯型销售提成计算。我们先来认识一下VLOOKUP函数。

条件查询： =VLOOKUP（查询值，数据源，结果在数据源的第几列，0/1）

最后一个参数是查询类型， 0为精确查询，1为区间查询。

=VLOOKUP(B2,参数表!B:C,2,1)*B2-VLOOKUP(B2,参数表!B:D,3,1)

首先我们利用VLOOKUP函数，根据销售额用区间查询出计提标准。

=VLOOKUP(B2,参数表!B:C,2,1)

如销售额是10万，那计提标准为2.5%，用这个标准2.5%*10万=2500，再利用VLOOKUP的区间查询，查询出这个业绩范围的扣除数。

=VLOOKUP(B2,参数表!B:D,3,1)

10万的扣除数为900，则张三的销售提成为2500-900=1600。实现了阶梯计算提成的需求。

再比如王五的销售额是13万，按阶梯计算规则，他的销售提成为：

8万*1.5%=1200

2万*2.0%=400

2万*2.5%=500

1万*3.0%=300

合计：2400

而在上图中可以看到王五的销售提成就是2400，这样就实现阶梯型销售提成的计算了。