|
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))
好啦，以上就是今天分享的內容。 |
|
|
