EXCEL中IF函数多层嵌套问题

Question

要实现的功能为“当A=500或600时，B<30,返回5.5，B>40,返回11，否则返回7.5;当A=800时，B<30,返回18.5,否则返回22;当A=1000或1200时，B<30,返回22，B>45,返回45，30<=B>=38,返回30，否则返回37；当A=1400时，B<30,返回37,否则返回55;”不太懂函数，用IF语句写了一长串，中间超过7层用“&”连接，返回结果总是像这样“false37false”，哪位好心的人帮忙指导一下

Answer 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))

Answer 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)))))

这公式可费脑子了~~

Answer 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)))))

Answer 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嵌套的限制了本回答被网友采纳

Answer 5

你可以分几步来做，你的参数比较多