Question

excel中if函数嵌套公式

一个大前提是销售完成率B1达60%（含）以上，提成额发放：月实际销售额A1>=70，系数1,.8%；50=<A1<70，1.7%，30=<A1<50,1.6%；23=<A1<30,1.5%；18=<A1<23,1.4%；15=<A1<18,1.3%；12=<A1<15,1.2%；10=<A1<12,1.1%；A1<10,1%；低于60%提成为0。 多层嵌套，各位高人求帮忙，谢谢！

Answer 1

excel2003中if函数嵌套使用方法

Answer 2

IF(B1>=0.6,IF(A1>=0.7,0.018,IF(A1>=0.5,0.017,IF(A1>=0.3,0.016,IF(A1>=0.23,0.015,IF(A1>=0.18,0.14,IF(A1>=15,0.013,IF(A1>=12,0.012,IF(A1>=0.1,0.11,0.1)))))))),0)

追问

我也是这么写的，嵌套层数太多了，用不了

Answer 3

=LOOKUP(A1, {0,10,12,15,18,23,30,50,70}, {"1%","1.1%","1.2%","1.3%","1.4%","1.5%","1.6%","1.7%","1.8%"})

Answer 4

输入以下公式
=IF(B1<60%,0,LOOKUP(A1,{0,10,12,15,18,23,30,50,70},{1,1.1,1.2,1.3,1.4,1.5,1.6,1.7,1.8}*1%))

追问

我套进去用不对诶，算出来的数据不正确的，数值很小，是不是哪里不对啊？

追答

忘记乘以销售额了
输入以下公式
=IF(B1<60%,0,LOOKUP(A1,{0,10,12,15,18,23,30,50,70},{1,1.1,1.2,1.3,1.4,1.5,1.6,1.7,1.8}*1%*A1))

Answer 5

IF(B1>=60%,LOOKUP(A1,{0,10,12,15,18,23,30,50,70},{1,1.1,1.2,1.3,1.4,1.5,1.6,1.7,1.8})%,0)