hello大家好,我是小E,Vlookup函式大家並不陌生,Excel資料匹配神器,一對一精準匹配資料。
但是在某些情況下,單靠Vlookup函式很難實現資料查詢。
如下圖中,根據AB列的資料來源,匹配查找出深圳這個城市所有的訂單,由於
vlookup預設返回查詢到的第一個值
,僅用vlookup函式的話,無法返回全部的訂單。
下面是多條件匹配查詢,根據A-D列的資料來源,查詢同時
滿足城市、郵寄方式、商品分類三個條件
的銷售額情況。
上面兩種情況在僅靠vlookup函式很難完成,但是藉助輔助列,就可以輕鬆搞定。
一對多查詢
vlookup函式實現的是精準匹配,針對一列相同的內容,函式僅會返回第一個值;為了實現精準匹配,我們
可以把相同的內容變成不同的內容
。
首先我們在城市列前方插入兩列輔助列,在B2單元格內輸入公式:
注意相對引用與絕對引用
,公式下拉,可以發現,相同的城市被從小到大編號;
接著在A2單元格輸入公式:
這樣相同的城市變成了城市1、城市2,以此類推,
有了編號的加持,相同城市變成了不同城市
。
接著在G2單元格內輸入公式:
公式向後拖動,向下拖動,完成資料的匹配。
COLUMN(A1)返回1,
$
F2&1=”深圳1“
,相當於vlookup函式的引數一是”
深圳1
“;
公式向後拖動,引數一變成了$
F2&COLUMN(B1)
,
COLUMN(B1)返回2,
就等於”
深圳2
“;
以此類推,將每個城市的訂單匹配出來,這裡
在$F2在F前面加上$符號表示將F列絕對引用
,這樣公式在向後面拖動的過程中,引用的單元格不會發生變化。
為了防止後面城市資料匹配不到的情況,可以在公式前面加個
IFERROR
函式。
多對多查詢
多對多查詢,較為常用的方法就是建立輔助列,將
多個欄位變成一個欄位
。
下圖中,在表格前方插入一列,輸入公式:
直接將三個欄位變成了一個欄位,這樣在利用vlookup函式進行資料匹配。
接著在J2單元格內輸入公式:
vlookup引數一
G2&H2&I2
直接將三個欄位合併成了一個欄位;當然你也可以直接在單元格中建立一列輔助列。
這種方法比較簡單快捷,掌握了vlookup函式,搭配輔助列,輕鬆搞定。
當然,多對多查詢,不建立輔助列,直接用lookup函式可以搞定。
LOOKUP搞定多對多
在下圖J2單元格內輸入公式:
公式下拉,返回正確的結果,以為到位。
但是公式書寫較為繁瑣,還是推薦使用vlookup函式搭配輔助列的方式,lookup函式略做了解即可。
資料匹配的兩個小技巧,分享給大家,善用輔助列,可以解決很多小問題~
覺得內容還不錯的話,給我點個“在看”唄