excel提成公式

Question

0到10000=15%
10001到15000=20%
15001到20000=25%
20001到25000=30%
25001到30000=35%
30001到40000=40%
40000以上=45%
能不能把每个阶段分开算然后总的加起来。

Answer 1

a1=金额
b1=提成
B1栏输入公式
=A1*IF(A1<=10000,0.15,IF(A1<=15000,0.2,IF(A1<=20000,0.25,IF(A1<=25000,0.3,IF(A1<=30000,0.35,IF(A1<=40000,0.4,0.45))))))

Answer 2

A列 B
1 0到10000 =if(a1>=0&a1<=10000,0,a1*0.15)
2 10001到15000 =IF(A2>=10001&A2<=15000,0,A1*0.2)
3 15001到20000 =IF(A3>=15001&A3<=20000,0,A1*0.25)
4 20001到25000 =IF(A4>=20001&A4<=25000,0,A1*0.3)
5 25001到30000 =IF(A5>=25001&A5<=30000,0,A1*0.35)
6 30001到40000 =IF(A6>=30001&A6<=40000,0,A1*0.4)
7 40000以上 =IF(A7>40000,0,A1*0.45)
8 =SUM(B1:B7)
我花了半小时测试好的 ，符合你的要求 ，一定要把分给我！

Answer 3

=(LOOKUP(-A1%%,-{1000,4,3,2.5,2,1.5,1},{7,6,5,4,3,2,1})+2)*5%

分段累加公式:
=LOOKUP(-A1%%,-{1000,4,3,2.5,2,1.5,1},{110,70,52.5,37.5,25,15,0}*100)+(A1-LOOKUP(A1%%,{0,1,1.5,2,2.5,3,4})*10000)*(LOOKUP(-A1%%,-{1000,4,3,2.5,2,1.5,1},{7,6,5,4,3,2,1})+2)*5%本回答被提问者采纳

Answer 4

怎么用Excel计算提成？这个视频告诉你！