Excel里常用的查找函数有五个:VLOOKUP、INDEX、OFFSET、LOOKUP、INDIRECT。 而查找的场景大致可以分成四类: ●单条件查找 ●多条件查找 ●一对多查找 ●多对多查找 今天就来为大家整理一期查找公式大全,遇到对应的情况,直接挑合适的公式套用即可! 1. 单条件查找(从左向右查找) 例如,要找到指定订单ID所对应的地址,首选公式为=VLOOKUP(D3,A:B,2,0) 使用其他查找函数的公式分别为: =INDEX(B:B,MATCH(D3,A:A,0)) =OFFSET($B$1,MATCH(D3,A:A,0)-1,) =LOOKUP(1,0/(A:A=D3),B:B) =INDIRECT("B"&MATCH(D3,A:A,)) 叨叨两句:虽然五个公式可以得到同样的结果,但具体原理各有不同。解决这类问题建议大家使用VLOOKUP,但是VLOOKUP有个限制就是查找条件必须在查找区域的首列,也就是从左向右查找。如果是从右向左又该如何做呢?看下面这个例子。 2. 单条件查找(从右向左查找) 例如按照指定的地址查找对应的订单ID,上述五个公式都需要做修改。 公式分别为: =VLOOKUP(D3,IF({1,0},B:B,A:A),2,0) =INDEX(A:A,MATCH(D3,B:B,0)) =OFFSET($A$1,MATCH(D3,B:B,0)-1,) =LOOKUP(1,0/(B:B=D3),A:A) =INDIRECT("a"&MATCH(D3,B:B,)) 大家对比一下就会发现,只有VLOOKUP的变化是最大的,用IF函数构建了一个数组,而其他四个公式基本一样。 除了以上说的两种单条件查找,日常用得比较多的还有多条件查找。 3. 多条件查找 例如通过客户ID和商品名称两个条件来查找运货商,还是用上述五个查找函数来对比看下公式。 公式分别为: =VLOOKUP(E3&F3,IF({1,0},A:A&B:B,C:C),2,0) =INDEX(C:C,MATCH(E3&F3,A:A&B:B,0)) =OFFSET($C$1,MATCH(E3&F3,A:A&B:B,0)-1,) =LOOKUP(1,0/((A:A=E3)*(B:B=F3)),C:C) =INDIRECT("c"&MATCH(E3&F3,A:A&B:B,)) 叨叨两句:多条件查找,除LOOKUP的原理不同之外,其他四个函数都是利用&将条件进行合并,其本质与单条件并无不同。但是合并过程中涉及到了数组计算,非365版本的用户输入公式后,需要按Ctrl、shift和回车键。
注意:以上的单条件查找和多条件查找,返回的结果都是唯一的,如果返回结果是多项的话,对应的问题就变成了一对多查找和多对多查找。 这两类问题使用公式解决都比较麻烦,当然如果你用的是最新版Excel的话,可以用新版特有的函数去处理,下面还是分情况来进行介绍。 4. 一对多查找 例如要查找出指定运货商的所有订单ID,就需要用到一对多查找的公式,非365版本可以使用公式: =IFERROR(INDEX($B$2:$B$19,SMALL(IF($A$2:$A$19=$D$2,ROW($1:$18),99),ROW(A1))),"") 结果如图所示。 如果你使用的是365版本的Excel,这个问题就比较容易了,直接使用公式=FILTER($B$2:$B$19,$A$2:$A$19=G2)即可,结果如图所示。 以上是一对多的两个公式,多对多的公式就更复杂了。 5. 多对多查找 按照城市和运货商查找对应的订单ID,非365版本使用公式: =IFERROR(INDEX($C$2:$C$19,SMALL(IF($A$2:$A$19&$B$2:$B$19=$E$2&$F$2,ROW($1:$18),99),ROW(B1))),"") 365版本使用公式=FILTER($C$2:$C$19,($A$2:$A$19=$E$2)*($B$2:$B$19=F2)) 好啦,以上就是今天分享的内容。 |
|