在之前的七篇文章中,我已經對函數vlookup的用法進行了深入淺出的講解,相信看過的朋友們一定對函數vlookup的用法已經爛熟於心,今天我們還將繼續以函數vlookup的基本用法為基礎,但是講解的案例將更加有深度。
在上一篇中,我將函數vlookup與函數match、index組合函數進行了對比,發先函數vlookup用法在查找匹配方面有一定的限制,而函數match、index組合函數的應用更具靈活性(對於函數match和函數index的基本操作方法感興趣的朋友可以看完該篇文章之後參考文章當函數vlookup無能為力時,函數match和index未嘗不是更好的選擇)
上述七篇文章中的案例都有共同點,基於某列數據運用函數vlookup去尋找另一列數據,總而言之,都是去匹配單列數據。現在我們試想一下,如果現在給了我們一列數據,但是卻讓我們去匹配多列數據,我們該怎樣解決這樣的問題呢?
上述問題就是我們今天要講解的實例,所以接下來我們就直接進入實例講解階段。
實例:我們現在有這樣一個excel工作表,裡面包含兩張表。第一張表是一個數據源表,裡面包括了客戶ID、公司名稱、聯繫人姓名、地址和聯繫人頭銜五項內容,並附有相關的數據,第二張表的內容有四項,分別是客戶ID、公司名稱、聯繫人姓名和地址,其中客戶ID為已知內容,而公司名稱、聯繫人姓名和地址為未知內容,現在我們的任務就是根據第一張表中的數據源和第二張表的客戶ID,運用函數vlookup將公司名稱、聯繫人姓名和地址這三項匹配出來。excel工作表具體如下所示:
在這裡,我給大家推薦兩種方法來解決這樣的問題。
方法一:分別在H2單元格,I2單元格和J2單元格中,也能用函數vlookup得出相應的結果,然後運用填充柄的拖拽功能得到所有要進行匹配的單元格。
具體操作方法如下:首先我們在H2單元格,I2單元格和J2單元格中依次輸入「=VLOOKUP(G2,$A$1:$E$16,2,0)」、「=VLOOKUP(G2,$A$1:$E$16,3,0)」、「=VLOOKUP(G2,$A$1:$E$16,4,0)」,然後我們按回車鍵,就能分別得到客戶ID為「BERGS」所對應的公司名稱、聯繫人姓名和地址,接著我們選中H2單元格,I2單元格和J2單元格,然後通過填充柄拖拽的方式向下拖拽,我們就能到其他客戶ID所對應的公司名稱、聯繫人姓名和地址。具體操作可參考下圖:
方法評價:上述方法將函數vlookup的基本用法與填充柄的拖拽功能結合,解決了現有問題,但是卻還是有很大的局限性。試想一下,這裡我們要匹配三項數據,結果我們寫了三個公式,如果匹配100項數據,恐怕我們沒耐心再寫100個公式了。所以我們接下來還是看看更為便利的方法二吧!
方法二:這裡我們只需要在H2單元格填上合適的函數式,然後使用填充柄向左、向下拖拽,這樣就能得出所有的結果了。但是在這過程中我們會遭遇兩大難題:怎樣對第一參數進行混合引用?怎樣確定第三參數?接下來我們邊做表解決問題。
首先,我們將H2單元格的答案做出來。在H2單元格輸入「=VLOOKUP(G2,$A$1:$E$16,2,0)」,然後回車鍵即可。這時我們按照以往的經驗,我們知道接下來如果向下拖拽,結果仍然不會出錯,所以關鍵問題在於怎樣保證向左拖拽也不會出錯。
我們選中H2單元格,向左拖動一格,看看結果是什麼?
結果為#NA,具體函數式是「=VLOOKUP(H2,$A$1:$E$16,2,0)」,從這個函數式,我們可以看出兩點錯誤,首先第一參數應該是「G2」,而不是「H2」,其次第三參數應該是「3」,而不是「2」。
首先我們來解決第一參數帶來的問題,可能有人會說改成$G$2(絕對引用)即可,這樣做確實解決了向左拖拽帶來的問題,但是也會導致向下拖拽時出錯,所以這裡要運用到混合引用來解決問題,將「G2」改寫成「$G2」,將列鎖定即可。
現在我們來結果第二個問題,怎樣讓第三參數也隨著填充柄的拖拽而不斷變化呢?我們從函數式「=VLOOKUP(H2,$A$1:$E$16,2,0)」中可以看出在函數vlookup光填入數字的話,是不會隨著填充柄的拖拽而不斷變化的,所以還是要藉助函數的功能。
這裡我推薦使用函數column,其基本語法形式是COLUMN(reference),具體我們可以看以下三個例子:「=COLUMN()」會得到公式所在的列;「=COLUMN(A10)」會得到結果「1」,因為A列是第一列;「=COLUMN(C3:D10)」會得到引用中的第一列的列號,即「3」。這裡我們要運用的是「=COLUMN()」。
這裡我們在H2單元格輸入「=COLUMN()」時,會得到「8」,因為H列是第八列,但是這裡的第三參數應該是「2」,所以第三參數的具體形式應該是「=COLUMN()-6」,這時要填入H2單元格的函數式也就變為」=VLOOKUP($G2,$A$1:$E$16,COLUMN()-6,0)「。當向左拖拽時,第一參數G2不變,第三參數」COLUMN()-6「隨之遞增;當向下拖拽時,第一參數隨之相應的改變,第三參數」COLUMN()-6「不變,這樣的函數式就滿足所有的要求了。
具體做法整理:首先我們在H2單元格輸入「=VLOOKUP($G2,$A$1:$E$16,COLUMN()-6,0)」,然後我們按回車鍵,就能分別得到客戶ID為「BERGS」所對應的公司名稱,接著我們選中H2單元格向左拖拽,就能得到客戶ID為「BERGS」所對應聯繫人姓名和地址。最後我們選中H2單元格,I2單元格和J2單元格,然後通過填充柄拖拽的方式向下拖拽,我們就能到其他客戶ID所對應的公司名稱、聯繫人姓名和地址。具體操作可參考下圖:
總結:1.首先我們要非常熟練使用函數vlookup的的基本操作方法,這裡大家感興趣可以參考文章萬千數據迷人眼,函數vlookup助你來挑選!
2.excel中單元格內容的相對引用、絕對引用和混合引用問題一定分清楚,可以參考文章excel關於絕對引用和混合引用的巧妙使用
3.要對函數column的基本用法有所了解。
今天的分享到此結束,感興趣的朋友可以點讚關注哦!