Question

Excel中的IF嵌套函数，谢谢帮忙！

前提：目标完成率A1>=60%，才有提成。销售额B，基础量C，增长额D=B-C，D>=2000，3%;D>=3000,4%;D>=4000,5%;D>=5000,6%;D=C或D<2000，2%；即使增长额D为负数，只要A1>=60%，就按2%发提成。

Answer 1

=IF(D4<0.6,0,IF(D4>=0.6,C4*0.02,IF((C4-B4)>=2000,C4*0.03,IF((C4-B4)>=3000,C4*0.04,IF((C4-B4)>=4000,C4*0.05,IF((C4-B4)>=5000,C4*0.06,))))))

Answer 2

=if(A1>=60%,if(d>=5000,d*6%,if(d>=4000,d*5%,if(d>=3000,d*4%,if(d>=2000,d*3%,d*2%)))),"")。当A1<60%时在“”处补充操作就可以了

Answer 3

在E列中输入：=IF(A1<=60%,"无提成",IF(D1=C1,"2%",IF(D1<=2000,"2%",IF(D1<=3000,"3%",IF(D1<=4000,"4%",IF(D1<=5000,"5%","6%"))))))

Answer 4

=IF(A1>=0.6,LOOKUP(D1,{0,2000,3000,4000,5000},{0.02,0.03,0.04,0.05,0.06}),0)