今天有个财务朋友向我请教如何在Excel表里面写个
阶梯式销售提成公式,初步分析应该类似个税的计算模式。
他说他查询了很多资料,发现网上的都没法解决问题。我刚好有空,顺便就看了下,
需求如下:
1)月度提成=月度销售毛利*提成系数
2)提成系数采取阶梯制,即销售毛利按照各阶段核算提成,具体标准为:
销售毛利阶梯(万元) 0-5 5-15 15-30 30-50 50以上
提成系数 2% 3% 4% 5% 6%
整理规则是这样
X1+X2+X3+X4+X5=M(毛利);X1*2+X2*3+X3*4+X5*6)*1/100=N(提成)
可以用最原始的逻辑写法:几个节点的提成固定的
基本分段程序表示累加模式:
$y = 0;
if($x<=50000){
$y+= $x*2%
if($x<=150000 && $x>50000){
$y +=(x-50000)*3%
但是由于这个要用在Excel上用excel的公式计算:
这边有个问题就是A2单元的值是固定不变的,没有去掉上一次计算的值。
其实很简单,我们可以转换个思路,
销售毛利阶梯(万元) 0-5 5-15 15-30 30-50 50以上
提成系数 2% 3% 4% 5% 6%
这个其实可以理解成
销售毛利阶梯(万元) 0-5 5-15 15-30 30-50 50以上
提成系数 2% (2+1)% (2+1+1)% (2+1+..)%...这样就方便处理了
最终还有个大于50万的边界问题,我们设置为999999999,够用就行不能太多,多了可能会溢出。
有需要的同学可以参考下,应用的场景在分段计算:
fx==SUM(TEXT(A2-{0,50000,150000,300000,500000,9999999999},"0;!0")*{0.02,0.01,0.01,0.01,0.01,0.01})。