IF函数多条件判断,满足第一个条件可以得出第一个答案,如果第一个条码不满怎么继续进行下组数据判断计划
IF函数多条件判断,如果满足第一个条件可以得出第一个答案,如果第一个条码不满怎么继续进行下组数据判断计划。=IF(AND(Q7>=100%,Q8>=100%,T8>=50%),H8*0.08,IF(AND(Q7>=100%,Q8>=100%,40%<=T8<50%),H8*0.07,IF(AND(Q7>=100%,Q8>=100%,30%<=T8<40%),H8*0.06,IF(AND(Q7<100%,Q8>=100%,T8>=50%),H8*0.07,IF(AND(Q7<100%,Q8>=100%,40%<=T8<50%),H8*0.06,IF(AND(Q7<100%,Q8>=100%,30%<=T8<40%),H8*0.05,0)))))
请大神指点。
注意:excel是不能识别这样的的不等式判定:
40%<=T8<50%
必须修改为: T8>=40%,T8<50%
所以你的公式中多处需要修改:
=IF(AND(Q7>=100%,Q8>=100%,T8>=50%),H8*0.08,IF(AND(Q7>=100%,Q8>=100%,40%<=T8<50%),H8*0.07,IF(AND(Q7>=100%,Q8>=100%,30%<=T8<40%),H8*0.06,IF(AND(Q7<100%,Q8>=100%,T8>=50%),H8*0.07,IF(AND(Q7<100%,Q8>=100%,40%<=T8<50%),H8*0.06,IF(AND(Q7<100%,Q8>=100%,30%<=T8<40%),H8*0.05,0)))))
公式中加深字体有四处,修改一下就可以了
结合LOOKUP():
=IF(Q8<1,0,IF(Q7>=100%,H8*LOOKUP(T8,{0,0.3,0.4,0.5},{0,0.06,0.07,0.08}),H8*LOOKUP(T8,{0,0.3,0.4,0.5},{0,0.05,0.06,0.07})))