如何在交叉查詢中使用VLOOKUP?看完就懂!
?
作者:阿碩來源:部落窩教育發(fā)布時間:2021-04-25 09:19:20點擊:3675
編按:
VLOOKUP作為查詢界天王一樣的存在,在每一個EXCELER的心中都有舉足輕重的份量。而要學(xué)會如何使用VLOOKUP,首先我們一定要理解它,然后在一個具體的二維交叉表中應(yīng)用它。比如,小E下面要和大家講的例子??赐暌院?,相信大家都能輕松了解和掌握VLOOKUP的基本套路……
有小伙伴問了這樣一個問題:我們公司生產(chǎn)多種產(chǎn)品,在不同的城市銷售,每一種產(chǎn)品在不同的城市中銷售價格是不一樣的。產(chǎn)品的報價表是一個行列交叉的二維區(qū)域,請問如何在這個二維交叉表中匹配出不同產(chǎn)品在不同城市中的銷售價格?
第一步:分析數(shù)據(jù)情況
這個小伙伴所在的公司一共生產(chǎn)四種產(chǎn)品,分別為產(chǎn)品A、產(chǎn)品B、產(chǎn)品C、產(chǎn)品D。這四種產(chǎn)品同時在北京、上海、廣州、天津這四個城市銷售,但是在不同的城市中,銷售價格不盡相同。具體的報價表是下圖中的E1:I5區(qū)域,其中,行字段代表的是城市,列字段代表的是產(chǎn)品,F2:I5區(qū)域中的數(shù)據(jù)是產(chǎn)品的銷售價格。
在本例中,查詢條件有兩個,分別是城市和產(chǎn)品,要查詢的字段為銷售價格。因此,大家可以把它理解成一個雙條件查詢問題。
觀察一下兩個字段:
產(chǎn)品這一字段,在二維報價表中是保存在同一列(E列)的不同行,是縱向的;而城市這一字段,在二維報價表中是保存在同一行(第1行)的不同列,是橫向的。
第二步:思考函數(shù)建構(gòu)
函數(shù)建構(gòu):
①熟悉VLOOKUP函數(shù)的小伙伴一定知道,它的功能是縱向查找,就是對同一列不同行中的數(shù)據(jù)進行查找,所以,產(chǎn)品這一字段可以用來作為VLOOKUP函數(shù)的第一參數(shù)。
②因為產(chǎn)品在二維報價表中保存在E列,銷售價格保存在F列至I列,根據(jù)VLOOKUP函數(shù)的使用原理,查詢區(qū)域應(yīng)為E:I。
③VLOOKUP函數(shù)的第四參數(shù),一般為0,代表精確查找。所以,在本例中,第四參數(shù)即設(shè)置為0。
此時,已經(jīng)大致可以勾勒出一個VLOOKUP函數(shù)了:
=VLOOKUP(B2,E:I,?,0)
現(xiàn)在,問題的關(guān)鍵只在于如何設(shè)置VLOOKUP函數(shù)的第三參數(shù)。那么,第三參數(shù)應(yīng)該是什么樣的?
第三步:第三參數(shù)的分析
如果查詢的城市是北京,它在E:I區(qū)域中處于第2列;如果查詢的城市是上海,它在E:I區(qū)域中處于第3列;如果查詢的城市是廣州,它在E:I區(qū)域中處于第4列;如果第一個查詢條件是天津,它在E:I區(qū)域中處于第5列。那么,在Excel中,該如何得到2、3、4、5呢?
這個時候,大家就需要請出VLOOKU函數(shù)的最佳搭檔——MATCH函數(shù)了。
MATCH函數(shù)的作用是在某一個區(qū)域中,找出查找值所在的位置。MATCH函數(shù)的語法是:=MATCH(lookup_value,lookup_array,[MATCH_type])。小伙伴們可以在咱們公眾號中搜索一下“MATCH”,就可以找到很多有關(guān)它的文章哦!
大家可以看到,城市字段在二維報價表中是保存在F1:I1區(qū)域的。分析可知:對于產(chǎn)品A,要查詢的城市是北京,對應(yīng)的是F1:I1區(qū)域中的第1列,應(yīng)返回1;對于產(chǎn)品B,要查詢的城市是上海,對應(yīng)的是F1:I1區(qū)域中的第2列,應(yīng)返回2;對于產(chǎn)品C,要查詢的城市是廣州,對應(yīng)的是F1:I1區(qū)域中的第3列,應(yīng)返回3;對于產(chǎn)品D,要查詢的城市是天津,對應(yīng)的是F1:I1區(qū)域中的第4列,應(yīng)返回4。
因此,把D列作為輔助列,在D2中輸入“=MATCH(A2,$F$1:$I$1,0)”,然后向下復(fù)制填充公式,得出的值就是1、2、3、4了。
注意:城市字段在A列中是縱向排列的,而在F1:I1區(qū)域中是橫向排列的,這說明MATCH函數(shù)不受行列方向的限制。
接下來,為了得到2、3、4、5,要在MATCH公式的值上加1。最后,在C2中輸入“=VLOOKUP(B2,E:I,MATCH(A2,$F$1:$I$1,0)+1,0)”,然后向下復(fù)制填充公式,就可以得到預(yù)期效果啦!如下圖所示。
總結(jié):
小伙伴們,一定要厘清本題的思路哦!VLOOKUP函數(shù)的查詢對象是產(chǎn)品,查詢區(qū)域是E:I區(qū)域;MATCH函數(shù)的查詢對象是城市,查詢區(qū)域是F1:I1,MATCH函數(shù)是作為VLOOKUP函數(shù)的第三參數(shù)來參與運算的。你GET到了嗎?
另外,把C2中的函數(shù)公式寫成“=VLOOKUP(B2,E:I,MATCH(A2,$E$1:$I$1,0),0)”,也是OK的。快來思考一下其中的邏輯吧!
本文配套的練習(xí)課件請加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學(xué)習(xí)Excel:
相關(guān)推薦:
查詢界黑馬——MAX,竟讓查詢之王VLOOKUP也甘拜下風(fēng)?
VLOOKUP函數(shù)的第三個參數(shù)被這樣操作可以自動獲?。?/font>
版權(quán)申明:
本文作者阿碩;同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!