Excel中IF函数中的条件写法

excel在办公中运用的比较多，但是用函数，特别是IF函数那是特别的少。接到一个任务，需要输出毛利率，已知的列有 进货价和毛重。需要根据不同价格区间 不同重量算出不同的毛利率。具体如下表：

进货价	重量与毛利率的关系
	重量	小于等于重量的毛利率	大于重量的毛利率
X <=150	500	20%	19%
150 < X =<200	800	18%	17%
200 < X =<250	900	16%	15%
250 < X =<700	1000	14%	13%
700 < X =<950	1100	12%	11%
950 < X =<1023	1200	10%	9%
1023 < X =<1920	1300	10%	8%
1920 < X =<2366	1400	8%	7%
2366 < X =<3500	1500	7%	4%
3500 < X =<5000	1600	6%	4%
5000 < X =<5800	1600	5%	4%
5800 < X	不校验	4%

拿到就霹雳吧啦的写下了一段IF函数

=IF(A2<=150,IF(B2<=500,0.2,0.19),
(IF(150<A2<200,IF(B2<800,0.18,0.17),(
IF(200<A2<=250,IF(B2<900,0.16,0.15),(
IF(250<A2<700,IF(B2<1000,0.14,0.13),(
IF(700<A2<950,IF(B2<1100,0.12,0.11),(
IF(950<A2<1023,IF(B2<1200,0.10,0.09),(
IF(1023<A2<1920,IF(B2<1300,0.10,0.08),(
IF(1920<A2<2366,IF(B2<1400,0.08,0.07),(
IF(2366<A2<3500,IF(B2<1500,0.07,0.04),(
IF(3500<A2<5000,IF(B2<1600,0.06,0.04),(
IF(5000<A2<5800,IF(B2<1600,0.05,0.04),0.04)))))))))))))))))))))

算出来的结果傻眼了。怎么价格大于150的毛利率都是0.04呢？？？

深思一下原因，原来IF中的条件不能采用 5000<A2<5800 这种格式，正确的格式是"=AND(A2>50,A2<=500)"

或者把上述的IF改写为：

=IF(A2<=150,IF(B2<=500,0.2,0.19),
(IF(A2<200,IF(B2<800,0.18,0.17),(
IF(A2<=250,IF(B2<900,0.16,0.15),(
IF(A2<700,IF(B2<1000,0.14,0.13),(
IF(A2<950,IF(B2<1100,0.12,0.11),(
IF(A2<1023,IF(B2<1200,0.10,0.09),(
IF(A2<1920,IF(B2<1300,0.10,0.08),(
IF(A2<2366,IF(B2<1400,0.08,0.07),(
IF(A2<3500,IF(B2<1500,0.07,0.04),(
IF(A2<5000,IF(B2<1600,0.06,0.04),(
IF(A2<5800,IF(B2<1600,0.05,0.04),0.04)))))))))))))))))))))

也是可以的。