多个if 套用

多个IF套用

小王表哥

2010-08-16

11349    

Excel 2010    Excel 2007    Excel 2003   

公式和函数应用-其他

?

IF的简单应用很多人都会，语句也相对比较直接：

IF(条件测试,条件满足时时返回的值,条件不满足时返回的值)

不过要是有很多不同的条件，需要多个IF的套用，公式看上去就会很复杂。 事实上，Excel 也限制了一个单元格里最多使用12个IF的套用。

如果你的条件是针对同一个单元格的匹配或比较，你就可以考虑使用VLOOKUP和LOOKUP来简化你的公式了。

完全匹配

比如说：

数值	结果
D 列 = 0	0
D 列 = 100	1
D 列 = 200	5
D 列 = 300	10

我们在 Excel 的 A、 B 两列列出这个关系。

	A	B	C	D	E	E 列公式
1	0	0		100	1	=VLOOKUP(D1,$A$1:$B$4,2,0)
2	100	1		300	10	=VLOOKUP(D2,$A$1:$B$4,2,0)
3	200	5		0	0	=VLOOKUP(D3,$A$1:$B$4,2,0)
4	300	10		20	#N/A	=VLOOKUP(D4,$A$1:$B$4,2,0)

上面VLOOKUP的公式，实际上就是找出 D 列的值在整个范围里是在哪一行，返回对应的第二个数据。最后一个 0 是指完全匹配（等于 FALSE)。 通过VLOOKUP，你要增加多少个条件都很简单。

如果条件不多，又不想用另外两列列条件，可以直接用数组替代查找范围。如 E1 的公式就可以用下面的公式替代：

=VLOOKUP(D1,{0,0;100,1;200,5;300,10},2,0)

在公式里把整个查找范围以 { } 将范围括起，每一行以分号（；）区分，每一行里的不同数据以逗号（，）区分，结果会完全一样。

除了数字之外，不管是查找值还是查找结果，都可以直接使用文本，如：

	A	B	C	D	E	E 列公式
1	苹果	水果		苹果	水果	=VLOOKUP(D1,$A$1:$B$4,2,0)
2	香蕉	水果		萝卜	蔬菜	=VLOOKUP(D2,$A$1:$B$4,2,0)
3	萝卜	蔬菜		香蕉	水果	=VLOOKUP(D3,$A$1:$B$4,2,0)
4	青椒	蔬菜		番茄	#N/A	=VLOOKUP(D4,$A$1:$B$4,2,0)

用数组公式同样可以用文本，如： =VLOOKUP(E1,{"苹果","水果";"香蕉","水果";"萝卜","蔬菜";"青椒","蔬菜"},2,0)

在上面的例子可以看见，如果查找值没在查找范围里，会返回错误 #N/A。 要避免报错，在 2007 版加上IFERROR，在 2003 版加上IF(ISNA(...，如：

=IFERROR(VLOOKUP(D1,$A$1:$B$4,2,0),"没找到对应值")

=IF(ISNA(VLOOKUP(D1,$A$1:$B$4,2,0)),"没找到对应值",VLOOKUP(D1,$A$1:$B$4,2,0))

数据比较（大于等于一定值）

比如说：

数值	结果
100 〉 D 列 ≥ 0	0
200 〉 D 列 ≥ 100	1
300 〉 D 列 ≥ 200	5
D 列 ≥ 300	10

同样，我们在 A、 B 两列列出这个关系（必须是从小到大）。

	A	B	C	D	E	E 列公式
1	0	0		200	5	=VLOOKUP(D1,$A$1:$B$4,2)
2	100	1		310	10	=VLOOKUP(D2,$A$1:$B$4,2)
3	200	5		150	1	=VLOOKUP(D3,$A$1:$B$4,2)
4	300	10		-1	#N/A	=VLOOKUP(D4,$A$1:$B$4,2)

要决定查找值是否大于等于一定值，实际上就是在VLOOKUP公式中把完全匹配的要求去掉。 这公式同样可以直接用数组，如：

=VLOOKUP(D1,{0,0;100,1;200,5;300,10},2)

除了用VLOOKUP，也可以用LOOKUP函数：

=LOOKUP(D1,$A$1:$A$4,$B$1:$B$4)

=LOOKUP(D1,{0,100,200,300},{0,1,5,10})

分别就是VLOOKUP里是整个范围既包括条件，也包括结果；LOOKUP是把条件跟结果分开。 如果用数组，VLOOKUP是一个条件紧接着一个结果，然后再列下一个条件和结果； LOOKUP是先用一个数组列所有条件，再用另一个数组列结果。

另外，如果查找值小于范围的最低值就会返回错误。要避免报错，除了用上面的方法之外，还可以增加一个非常小的数作为条件，如：

=VLOOKUP(D1,{-9E+99,"范围外";0,0;100,1;200,5;300,10},2)

=LOOKUP(D1,{-9E+99,0,100,200,300},{"范围外",0,1,5,10})

数据比较（小于等于一定值）

这种数据比较常用，比如我们个人所得税的累进税率就属于这种数据比较。 可是在 Excel 函数中，不管用的是LOOKUP还是VLOOKUP，只要找到相应的值，就会马上返回到下一个结果。所以要做 ≤ 会比较困难。

比如说：

数值	结果
D 列 ≤ 0	0
0 〈 D 列 ≤ 100	1
100 〈 D 列 ≤ 200	5
200 〈 D 列 ≤ 300	10

不管有几位小数，夸张一点，就变成：

数值	结果
0 〉 D 列 - 1E-10 ≥ -9E+99	0
100 〉 D 列 - 1E-10 ≥ 0	1
200 〉 D 列 - 1E-10 ≥ 100	5
300 〉 D 列 - 1E-10 ≥ 200	10
D 列 - 1E-10 ≥ 300	范围外

折衷的做法是改变你的条件。如果A1是固定只有一个小数位，可以把条件改成：

数值	结果
0 〉 D 列 - 0.01 ≥ -10	0
100 〉 D 列 - 0.01 ≥ 0	1
200 〉 D 列 - 0.01 ≥ 100	5
300 〉 D 列 - 0.01 ≥ 200	10
D 列 - 0.01 ≥ 300	范围外

这样一看，就跟上面 ≥ 的情况一样了。用了 1E-10 而没用 1E-99 是因为 Excel 本身计算时使用小数位位数的限制。

	A	B	C	D	E	E 列公式
1	-9E+99	0		200	5	=VLOOKUP(D1-1E-10,$A$1:$B$5,2)
2	0	1		310	范围外	=VLOOKUP(D2-1E-10,$A$1:$B$5,2)
3	100	5		50	1	=VLOOKUP(D3-1E-10,$A$1:$B$5,2)
4	200	10		-1	0	=VLOOKUP(D4-1E-10,$A$1:$B$5,2)
5	300	范围外

用数组公式就是：

=VLOOKUP(D1-1E-10,{-9E+99,0;0,1;100,5;200,10;300,"范围外"},2)

=LOOKUP(D1-1E-10,{-9E+99,0,100,200,300},{0,1,5,10,"范围外"})