EXCEL中IF函数多层嵌套问题

2025-04-08 10:39:31

推荐回答（4个）

回答1：

=IF(OR(A1=500,A1=600),IF(B1<30,5.5,IF(B1>40,11,7.5)),IF(A1=800,IF(B1<30,18.5,22),IF(OR(A1=1000,A1=1200),IF(B1<30,22,IF(B1>45,45,IF(AND(B1>=30,B1<=38),30,37))),IF(A1=1400,IF(B1<30,37,55)))))
如果不支持7层嵌套，可以用CHOOSE函数
=CHOOSE(OR(A1=500,A1=600)*1+(A1=800)*2+OR(A1=1000,A1=1200)*3+(A1=1400)*4,IF(B1<30,5.5,IF(B1>40,11,7.5)),IF(B1<30,18.5,22),IF(B1<30,22,IF(B1>45,45,IF(AND(B1>=30,B1<=38),30,37))),IF(B1<30,37,55))

回答2：

=IF(OR(A1=500,A1=600),IF(B1>40,11,IF(B1<30,5.5,7.5)),IF(A1=800,IF(B1<30,18.5,22),IF(OR(A1=1000,A1=1200),IF(B1<30,22,IF(B1<=38,30,IF(B1>45,45,37))),IF(A1=1400,IF(B1<30,37,55)))))

这公式可费脑子了~~

回答3：

=IF(OR(A1=500,A1=600),IF(B1<30,5.5,IF(B1>40,11,7.5)),IF(A1=800,IF(B1<30,18.5,22),IF(OR(A1=1000,A1=1200),IF(B1<30,22,IF(AND(B1>=30,B1<=38),30,IF(B1>45,45,37))),IF(OR(A1=1000,A1=1200),IF(B1<30,22,IF(B1<=38,30,IF(B1>45,45,37))),IF(A1=1400,IF(B1<30,37,55),A1)))))

回答4：

=(--(a1=500)+--(a1=600))*((b1<30)*5.5+((b1>40)*5.5+(b1>=30)*)b1<=40)*7.5)+(a1=800)*((b1<30)*18.5)+(b1>=30)*22)+(--(a1=1000)+--a1=1200))*((b1<30)*22+(b1>=30)*(b1)<=38)*30+((b1>38)*(b1<=45)*37+(b1>45*45)+(a1=1400)*((b1<30)*37+(b1>=30)*55）
这样就受if嵌套的限制了