VLOOKUP的常見用法,你知道幾種 | 商品管理
常見用法
例表1:精確查詢,用貨號查品名
公式解析:
$B19
為查詢條件,
$B$3:$D$6
為查詢區域,
3
為值在查詢範圍的列數,
0
表示精確匹配
例表2:模糊查詢,區分價格帶
公式解析:本例表公式中將最後一項的0改為了1,也就是將精確匹配的指令改為了模糊匹配,模糊匹配可以幫我們找到
最接近查詢條件但比它小的值
所在單元格的對應值。(例表中最接近369又比它小的值就只有300了,所以查詢結果就返回到了300對應的價格帶300~399)
例表3:萬用字元查詢,知道流水號查小類
公式解析:本例表中查詢條件用到了萬用字元
*
和連線符
&
,即提取B列單元格中字尾字元為0001的單元格對應第三列的值,公式自動返回到X0001對應的第三列的數值小衫。
例表4:多列匹配,用款號查大類、小類
公式解析:公式中的列數我們直接用COLUMNS來代替了,COLUMNS($B$2:D2)的意思是取自B2到D2的列數(3),利用列數拉動公式遞增,這樣寫的好處是
一個條件要引用連續或不連續多列時,不用一個個修改公式的列數
例表5:反向匹配,用品名查貨號
公式解析:公式中的查詢區域,我們一改平時的寫法,使用
IF(,E4:E9,B4:B9)
公式。這裡1和0不是實際意義上的數字,而是1相關於TRUE,0相當於FALSE,當為1時,它會返回IF的第二個引數(E列),為0時返回第二個引數(B列),這個公式
實際就是把兩組資料組合成一個新的區域資料{品名&款號}
,我們再用VLOOKUP從這個新資料區域中查詢到款號
例表6:多條件匹配,查詢A店X0005款的銷量
公式解析:本例表公式是上一表中公式的進階版,原理同上一表相同,都是用IF實現資料重組。不同的是,
重組的時候多加了一個連線符&,提前將查詢條件對應的兩列合併為一個數組條件
,然後用
IF(將合併的後的條件區域與值區域組合成一個新的資料查詢範圍,最後用&將兩個條件組合連線後VLOOKUP進行最後的查詢定位。
注意:因為本組公式有數組合並動作,視為陣列公式,所以必須用shift+ctril+enter結束!!!
例表7:查詢A店第一、二、三依次遞增次數的銷量
公式解析:本例表的原理和上一表多條件引用相同,只是多加了一個公式輔助列
(COUNIF)
和一個計數判斷
ROWS()
,第一步就是新增輔助列,目的是
判斷出在查詢範圍內查詢條件出現的遞增次數
,然後用
連線符&
把條件和出現次數連線為一個數組條件,然後用IF(將合併的後的條件區域與值區域組合成一個新的資料查詢範圍,最後用&將條件和ROWS(用法用columns,區別是ROWS是利用行數進行數值遞增)組合連線後VLOOKUP進行最後的查詢定位。
注意:因為本組公式有數組合並動作,視為陣列公式,所以必須用shift+ctril+enter結束!!!