Question

excel 公式问题？

想实现一个公式,是用于计算佣金提成的.制定规则如下:销售5000台设备(含),每台提成3元;销售超5001-12000台设备(含),每台提成5元;销售超12001-19999台设备(含),每台提成8元;销售超20000台以上,每台提成10元;想实现:假设一个员工销售18000台,那么提成应该分5000台+12000台+1000台的算法来算提成,就是D4,E4,F4,G4的公式要怎么设置

Answer 1

按照18000台，提成是5000+12000+1000来算，结果如下（空白处字体颜色白色）：

【上图的详细公式】

D3：=IF($C3>5000,5000,C3)

E3：=IF(C3-D3>0,IF(C3-D3>12000,12000,C3-D3),0)

F3：=IF(C3>SUM(D3:E3),IF(C3-SUM(D3:E3)>19999,19999,C3-SUM(D3:E3)), 0)

G3：=IF(F3=0,0,IF(F3<19999,0,C3-SUM(D3:F3)))

【美化（个人喜好）】

1. 选中数据区域，点开始菜单选项卡 -> 条件 -> 新建规则

2. 使用公式确定

Answer 2

你若是要直接得到提成，在D4单元格输入公式：
=SUMPRODUCT(TEXT(C4-{0,5000,12000,20000},"0;!0")*{3,2,3,2})
复制并下拉即可

追问

这里为什么是A3,而不是用C4呢,出来的金额也不对啊

追答

输入公式：
=SUM(TEXT(C4-{0,5000,12000,20000},"0;!0")*{3,2,3,2})

Answer 3

对于阶梯销售提成的计提，建议采用以下计算方式：

如果销售量在C3单元格，那么输入以下公式，直接得到最终销售提成。

=SUM(TEXT(C3-{0,5,12,20}*10^3,"0;!0")*{3,2,3,2})

详见附图示例

Answer 4

D3=IF($C3>=E$2,E$2-D$2,MAX(0,$C3-D$2))

向右填充到G 向下填充至数据末尾

I3=SUM($D$1:$G$1*D3:G3)  三键数组型输入

或者 =SUMPRODUCT($D$1:$G$1*D3:G3) 这个正常输入就行

追问

张三的金额是对的,但是李四是买18000台,对应的提成应该是5000*3+12000*5+1000*8=83000元;王武的金额是对的,但是到韩流的金额又不对了,韩流对应的提成应该是5000*3+12000*5+8000*8=139000元,求大神帮忙啊

追答

看来对李四的算法你有看法
李四应该是 5000+7000+6000
你的12000*5 是基于第一次的5000又重复计算了两次
当然 前提是你认可张三的算法 那就说明我没有理解错误

追问

对的,我的12000是要扣除了5000的基础上在算的,即李四买了18000台,满足了18000-5000=13000台(大于0),那么就可以用5000*3;再用13000-12000=1000台,那么也满足了5001-12000之间的条件,就可以用12000*5;假设李四只销售有11000,首先5000台是满足条件的,剩下的6000台刚好在5001-12000之间,那么也是用6000*5

追答

我的12000是要扣除了5000的基础上在算的,

那就是 后边的 12000-20000也是在扣除完5000 再扣除完12000之后重新计算的?

追问

是的,即5001-12000是要扣除了5000的;12001-19999是扣除了5000和12000的;20000是扣除了5000,12000和19999;
而且对应的金额是0-5000,是3元,5001-12000,是5元,12001-19999是8元,20000以上都是10元

追答

看下这里边哪个计算方式不对 我可以按你的要求修改一下~~~

追问

就是当销售量达到13000台以上是,我的理解是,剔除5000之后,应该直接用剩下的8000台乘以5,因为剩余的8000台是在5001-12000这个区间里面的

追答

哈哈哈 其实你是有误区的 按你的想法 6000 去掉5000之后 剩1000 应该乘3了么?
就是 6000 = 5000*3 + 1000*3 明显是不合常理的 对么?
正常的规律是 13000 = 5000+ 7000(这个是12000档)+1000
你觉得呢?

Answer 5

销售超12001-19999台设备(含),每台提成8元;

这里应该是

销售超12001-20000台设备(含),每台提成8元;

D4=IF(C4>=5000,5000,C4)*3

E4=IF(C4>=12000,12000-5000,IF(C4<5000,0,C4-5000))*5

F4=IF(C4>=19999,19999-12000,IF(C4<12000,0,C4-12000))*8

G4=IF(C4>=20000,C4-20000,0)*10