Question

excel中怎么计算阶梯提成？

0-150 提成0.1
151-300 提成0.15
301-500 提成0.18
大于500 提成0.2
比如：总业务是：700. 那么就是150*0.1+150*0.15+200*0.18+200*0.2

Answer 1

新建辅助列D，值为A列数字差乘以对应B列。

B11=IF(A11>500,SUM(C6:C8)+(A11-500)*0.2,IF(A11>300,SUM(C6:C7)+(A11-300)*0.18,IF(A11>150,C6+(A11-150)*0.15,A11*0.1)))

B11为数值单元格

Answer 2

假设值在a3=MIN(150,A3)*0.1+MIN(150,MAX(A3-150,0))*0.15+MIN(200,MAX(A3-300,0))*0.18+MAX(A3-500,0)*0.2

Answer 3

=MAX(A1*{0.1,0.15,0.18,0.2}-{0,7.5,16.5,26.5})

Answer 4

在B1中输入或复制粘贴下列公式
=IF(A1>500,150*0.1+150*0.15+200*0.18+(A1-500)*0.2,IF(A1>300,150*0.1+150*0.15+(A1-300)*0.18,IF(A1>150,150*0.1+(A1-150)*0.15,A1*0.1)))
下拉填充

Answer 5

=LOOKUP(,0/(A1-{0,150,300,500}>=0),{0,15,37.5,73.5})-LOOKUP(,({0,150,300,500}-A1)*{0.1,0.15,0.18,0.2})