|
楼主 |
发表于 2011-5-25 19:44
|
显示全部楼层
刚才又找到一个介绍,感觉更明白些,希望自己能够尽快学到有用的东西,提高自己,也分享给感兴趣的人,共勉!
要在表格中查找某一行的数据,然后返回其他列的信息,很多人都选择用VLOOKUP。 不过VLOOKUP还是有一定限制的,比如查找值必须在第一列,只能有一个条件等。
如果你需要查找的数据不在第一列,或有多于一个条件,要使用VLOOKUP,只能改变表格的排列,或在第一列增加辅助列。
实际上我们可以通过LOOKUP查找,这样,既不需要查找列在第一行,也可以做多条件查找:
同时符合所有条件: LOOKUP(1,1/((条件1)*(条件2)*(条件3)*...),结果范围)
只需符合一个条件: LOOKUP(1,1/sign((条件1)+(条件2)+(条件3)+...),结果范围)
A B
1 编号 项目
2 1001 A
3 1002 B
4 1003 C
比如说我们要在右表中找出书名是“A”的书号。
如果通过VLOOKUP,我们要么把A,B列互换,要么在A列前面加一列等于B列数据。
用LOOKUP查找就不用更改表格的格式,我们可以直接用公式:
=LOOKUP(1,1/($B$2:$B$4="A"),$A$2:$A$4)
LOOKUP的语法是:LOOKUP(查找值,查找数组,结果数组), 上面的公式就是说在1/($B$2:$B$4="A")里找到“1”,返回对应$A$2:$A$4的值。
我们在下面的表格分析一下数组1/($B$2:$B$4="A"):
A B 数组中个元素公式 数组中个元素结果
1 编号 项目
2 1001 A =1/($B$2="A")=1/1 1
3 1002 B =1/($B$3="A")=1/0 #DIV/0!
4 1003 C =1/($B$4="A")=1/0 #DIV/0!
实际上,查找数组只能返回“1”或“#DIV/0!”。如果符合条件(="A"),就返回“1”;不符合条件,就出现错误“#DIV/0!”。 在这个数组查找“1”,就会返回符合条件的第1个数组元素(第2行)的“1001”了。
要做多条件查询,只需要把条件都放在第一个数组的分母就可以了。
比如,我们要在表格中找出分类是“甲”,项目是“B”的编号:
=LOOKUP(1,1/(($B$2:$B$6="甲")*($C$2:$C$6="B")),$A$2:$A$6)
如果我们要在表格中找出分类是“甲”或者项目是“B”的编号:
=LOOKUP(1,1/sign(($B$2:$B$6="甲")+($C$2:$C$6="B")),$A$2:$A$6)
A B C =1/
(($B$2:$B$6="甲")
*($C$2:$C$6="B")) =1/
sign(($B$2:$B$6="甲")
+($C$2:$C$6="B"))
1 编号 分类 项目
2 1001 甲 A =1/(($B$2="甲")
*($C$2="B"))
=1/(1*0)=1/0
=#DIV/0! =1/sign(($B$2="甲")
+($C$2="B"))
=1/sign(1+0)=1/1
=1
3 1002 甲 B =1/(($B$3="甲")
*($C$3="B"))
=1/(1*1)=1/1
=1 =1/sign(($B$2="甲")
+($C$2="B"))
=1/sign(1+1)=1/1
=1
4 1003 甲 C =1/(($B$4="甲")
*($C$4="B"))
=1/(1*0)=1/0
=#DIV/0! =1/sign(($B$2="甲")
+($C$2="B"))
=1/sign(1+0)=1/1
=1
5 1004 乙 A =1/(($B$5="甲")
*($C$5="B"))
=1/(0*0)=1/0
=#DIV/0! =1/sign(($B$2="甲")
+($C$2="B"))
=1/sign(0+0)=1/0
=#DIV/0!
6 1005 乙 B =1/(($B$6="甲")
*($C$6="B"))
=1/(0*1)=1/0
=#DIV/0! =1/sign(($B$2="甲")
+($C$2="B"))
=1/sign(0+1)=1/1
=1
使用上面的公式查找,符合条件的应该只有1项,如果多于1项,结果就会变得不可预测。所以查找前最好先用SUMPRODUCT计算符合条件的个数(参考多条件加总和多条件计数)
另外可以用下面的公式返回最后一个记录:
同时符合所有条件: LOOKUP(2,1/((条件1)*(条件2)*(条件3)*...),结果范围)
只需符合一个条件: LOOKUP(2,1/sign((条件1)+(条件2)+(条件3)+...),结果范围)
由于在查找数组中只可能出现1和#DIV/0!,要找2肯定是找不到的,所以会返回最接近2又小于2的最后一个数,就是数组里最后一个“1”,这样等于是返回符合条件的最后一行。(参考查找符合条件的最后一笔交易)
原文地址是:http://wenwen.soso.com/z/q259668813.htm |
|