关于业绩分级抽成的问题

unitedent

我们公司的业绩是采用当月业绩分级抽成的方式.例:1、每月业绩<=3000元的,业绩奖金为0；2、当月业绩超过3000元但小于或等于10000元的，业绩奖金为（10000-3000）*5%；3、当月业绩超过10000元但小于或等于15000元的，业绩奖金为（10000-3000）*5%+（15000-10000）*8%；4、当月业绩超过15000元但小于或等于20000元的，业绩奖金为（10000-3000）*5%+（15000-10000）*8%+（20000-15000）*10%；5、当月业绩超过20000元但小于或等于30000元的，业绩奖金为（10000-3000）*5%+（15000-10000）*8%+（20000-15000）*10%+（30000-20000）*15%；6、5、当月业绩超过30000元的，其超过的部分业绩奖金为18%。请问类似这样的提成方式，在excel当中如何实现自动计算。请高手帮忙！！！！

95888

=IF(A1<=3000,0,IF(AND(A1>3000,A1<=10000),(10000-3000)*0.05,IF(AND(A1>10000,A1<=15000),(10000-3000)*0.05+(15000-10000)*0.08,IF(AND(A1>15000,A1<=20000),(10000-3000)*0.05+(15000-10000)*0.08+(20000-15000)*0.1,IF(AND(A1>20000,A1<=30000),(10000-3000)*0.05+(15000-10000)*0.08+(20000-15000)*0.1+(30000-20000)*0.15,IF(A1>30000,A1*0.18))))))

95888

=IF(A1<=3000,0,IF(AND(A1>3000,A1<=10000),(10000-3000)*0.05,IF(AND(A1>10000,A1<=15000),(10000-3000)*0.05+(15000-10000)*0.08,IF(AND(A1>15000,A1<=20000),(10000-3000)*0.05+(15000-10000)*0.08+(20000-15000)*0.1,IF(AND(A1>20000,A1<=30000),(10000-3000)*0.05+(15000-10000)*0.08+(20000-15000)*0.1+(30000-20000)*0.15,IF(A1>30000,A1*0.18))))))

95888

=IF(A1<=3000,0,IF(AND(A1>3000,A1<=10000),(10000-3000)*0.05,IF(AND(A1>10000,A1<=15000),(10000-3000)*0.05+(15000-10000)*0.08,

95888

IF(A1<=3000,0,IF(AND(A1>3000,A1<=10000),(10000-3000)*0.05,IF(AND(A1>10000,A1<=15000),(10000-3000)*0.05+(15000-10000)*0.08,IF(AND(A1>15000,A1<=20000),(10000-3000)*0.05+(15000-10000)*0.08+(20000-15000)*0.1,IF(AND(A1>20000,A1<=30000),(10000-3000)*0.05+(15000-10000)*0.08+(20000-15000)*0.1+(30000-20000)*0.15,IF(A1>30000,A1*0.18))))))

95888

IF(A1&lt;=3000,0,IF(AND(A1&gt;3000,A1&lt;=10000),(10000-3000)*0.05,IF(AND(A1&gt;10000,A1&lt;=15000),(10000-3000)*0.05+(15000-10000)*0.08,IF(AND(A1&gt;15000,A1&lt;=20000),(10000-3000)*0.05+(15000-10000)*0.08+(20000-15000)*0.1,IF(AND(A1&gt;20000,A1&lt;=30000),(10000-3000)*0.05+(15000-10000)*0.08+(20000-15000)*0.1+(30000-20000)*0.15,IF(A1&gt;30000,A1*0.18))))))

unitedent

以下是引用[I]95888[/I]在2006-5-22 21:44:07的发言：[BR]IF(A1&lt;=3000,0,IF(AND(A1&gt;3000,A1&lt;=10000),(10000-3000)*0.05,IF(AND(A1&gt;10000,A1&lt;=15000),(10000-3000)*0.05+(15000-10000)*0.08,IF(AND(A1&gt;15000,A1&lt;=20000),(10000-3000)*0.05+(15000-10000)*0.08+(20000-15000)*0.1,IF(AND(A1&gt;20000,A1&lt;=30000),(10000-3000)*0.05+(15000-10000)*0.08+(20000-15000)*0.1+(30000-20000)*0.15,IF(A1&gt;30000,A1*0.18))))))

感谢95888,您刚才的公式引用的A1数据是个固定的数据.但通常它是个不确定的数据.比如当A1为12000时怎么解决

unitedent

IF(A1<=3000,0,IF(AND(A1>3000,A1<=10000),(A1-3000)*0.05,IF(AND(A1>10000,A1<=15000),(10000-3000)*0.05+(A1-10000)*0.08,IF(AND(A1>15000,A1<=20000),(10000-3000)*0.05+(15000-10000)*0.08+(A1-15000)*0.1,IF(AND(A1>20000,A1<=30000),(10000-3000)*0.05+(15000-10000)*0.08+(20000-15000)*0.1+(A1-20000)*0.15,IF(A1>30000,(10000-3000)*0.05+(15000-10000)*0.08+(20000-15000)*0.1+(30000-20000)*0.15+(A1-30000)*0.18))))))
同时感谢95888