483 |
通過IF和VLOOKUP函數實現雙條件多條件查找的方法 |
||||
示範檔 |
無 |
範例檔 |
483.XLSX |
結果檔 |
無 |
在Excel中,通過VLOOKUP函數可以查找到資料並傳回資料。不僅能跨工作表查找,同時,更能跨活頁薄查找。但是,VLOOKUP函數一般情況下,只能實現單條件查找的功能。
如果想通過VLOOKUP函數來實現雙條件查找或多條件的查找並傳回值,那麼,只需要加上IF({1,0}就可以實現。
下面,就一起來看看IF({1,0}和VLOOKUP函數的經典結合使用例子吧。
要實現的功能是,根據「工作表1」工作表中的產品名稱和數量,找到「工作表2」工作表中相對應的產品名稱和數量,並獲取對應的價格,然後自動填充到工作表1的C欄。實現此功能,就涉及到兩個條件了,兩個條件都必須同時滿足。
如下圖,是「工作表1」工作表的資料,三欄分別存放的是產品名稱、數量和價格。
上圖是一張購買產品的表,其中,購買產品的列資料,可能存在重複。
現在,再來看「工作表2」工作表。
上圖是固定好的不存在任何重複資料的產品單價表。因為每種舒跑飲料對應的箱數是不相同的,如果要找舒跑飲料的單價,那麼,要求名稱是舒跑飲料,同時還要對應於數量,這就是條件。
現在,我們在「工作表1」工作表中的A欄輸入舒跑飲料,在B欄輸入數量,然後,利用公式自動從「工作表2」工作表中獲取相對應的價格。這樣就免去了輸入的麻煩。
公式比較複雜,因為難於理解,先看下圖吧,是公式的應用實例。
下面,將給大家大體介紹公式是如何理解的。比如C2儲存格的公式為:
{=VLOOKUP(A2&B2,IF({1,0},工作表2!A2:A10&工作表2!B2:B10,工作表2!C2:C10),2)}
請注意,如上圖所示的公式是陣列公式,輸入的方法是,先輸入
=VLOOKUP(A2&B2,IF({1,0},工作表2!A2:A10&工作表2!B2:B10,工作表2!C2:C10),2)之後,再按Ctrl + Shift + Enter複合鍵,才會出現大括弧。大括弧是通過複合鍵按出的,不是通過鍵盤輸入的。
公式解釋:
VLOOKUP函數,使用中文語法,可以這樣來理解。
VLOOKUP(查找值,在哪裡找,找到了傳回第幾欄的資料,邏輯值)。
A2&B2相當於要查找的值。等同於A2和B2兩個內容連接起來所構成的結果。所以為A2&B2,理解為A2加上B2的意思。
IF({1,0},工作表2!A2:A10&工作表2!B2:B10,工作表2!C2:C10)相當於要查找的數據。
2代表傳回第二欄的資料。
False是邏輯值,表示要查找最接近的值。
使用VLOOKUP函數單條件查找的方法
②IF({1,0}的解釋
IF({1,0},工作表2!A2:A10&工作表2!B2:B10,工作表2!C2:C10)相當於VLOOKUP函數中的查找資料的範圍。
由於本例子的功能是,根據「工作表1」工作表的A欄資料和B欄資料,兩個條件,去「工作表2」工作表中查找首先找到對應的AB兩欄的資料,如果一致,就傳回C欄的單價。
因此,資料查找範圍也必須是「工作表2」工作表中的AB兩欄,這樣才能被找到,由於查找資料的條件是A2&B2兩個儲存格的內容,但是此二儲存格又是獨立的,因此,要想構造查找範圍,也必須把「工作表2」工作表中的AB兩欄結合起來,那就構成了工作表2!A2:A10&工作表2!B2:B10。
工作表2!A2:A10&工作表2!B2:B10:相當於AB兩欄資料組成一列資料。
那麼,前面的IF({1,0}代表什麼意思呢?
IF({1,0},相當於IF({True,False},用來構造查找範圍的資料的。最後的工作表2!$C$2:$C$12也是資料範圍。
現在,整個IF({1,0},工作表2!A2:A10&工作表2!B2:B10,工作表2!C2:C10)範圍,就形成了一個陣列,裡面存放兩欄資料。
第一欄是工作表2AB兩欄資料的結合,第二欄資料是工作表2!$C$2:$C$12。
{=VLOOKUP(A2&B2,IF({1,0},工作表2!A2:A10&工作表2!B2:B10,工作表2!C2:C10),2)}公式中的數字2,代表的是傳回資料範圍中的第二欄資料。結果剛好就是工作表2的C欄,即第三欄。因為在IF({1,0}公式中,工作表2中的AB兩欄,已經被合併成為一欄了,所以,工作表2中的第三欄C欄,自然就成為序列2的欄編號了,所以,完整的公式中,紅色的2代表的就是要傳回第幾列的資料。
上面的完整的公式,可以使用如下兩種公式來替代:
=VLOOKUP(A2&B2,CHOOSE({1,2},工作表2!$A$2:$A$10&工作表2!$B$2:$B$10,工作表2!$C$2:$C$10),2,FALSE)
=VLOOKUP(A2&B2,IF({TRUE,FALSE},工作表2!$A$2:$A$10&工作表2!$B$2:$B$10,工作表2!$C$2:$C$10)
部落格相關範例
2019.09.27 |
|
2019.09.20 |
2208Excel函數用法-COUNT、COUNTIF、IF、IFERROR、MATCH、MID、OFFSET、RIGHT、VLOOKUP |
2019.08.17 |
|
2018.12.14 |
|
2018.08.18 |
|
2018.08.17 |
|
2018.08.16 |
|
2018.04.25 |
|
2017.09.15 |
|
2017.06.23 |
|
2017.06.21 |
|
2017.06.17 |
|
2017.06.01 |
|
2017.05.25 |
|
2017.05.24 |
|
2017.05.10 |
|
2017.05.08 |
|
2017.05.02 |
|
2017.04.29 |
|
2017.04.28 |