EXCEL VLOOKUP用法與函數範例教學

你是否常常在工作上需要在眾多的資料中找出某項產品的特定數值,或者是你需要查詢班上每人的成績平均數,但是卻又不知道要使用甚麼樣的函數,才能幫助你迅速地解決這些工作上的要求?

EXCEL 的 VLOOKUP 函數能夠幫助你解決這些問題。利用 VLOOKUP 函數,你可以快速地在各個資料表格中找到相對應的項目,不用擔心因人為疏失而選取到錯誤的內容。

那麼 VLOOKUP 是什麼樣的函數,又如何運作呢?

VLOOKUP 的函數運作原理,是從最左側欄數開始往下往右尋找到資料表內對應的項目。

從示意圖表就能看出它的搜尋原理就如同一個 L 字型的邏輯,一開始從最左側的資訊依序往下尋找,再往右開始找到你所要的資訊並回傳為最終結果。

這裡要特別注意的是 VLOOKUP 函數是從最左欄開始查詢,所以如果你要查詢的數值不在欄位的最左側,而是從中間的欄位開始查詢的話,你就需要透過其他方式來處理,像是使用 INDEX、MATCH 函數才能正確顯示你所需要的項目。

本篇文章將為你詳細介紹 VLOOKUP 函數的使用說明與實際用法,讓你透過它更快的從資料中篩選自己需要的結果。

 

VLOOKUP 函數使用說明

1. VLOOKUP 函數寫法

=VLOOKUP{lookup_value,table_array,col_index_num,[range_lookup]}

也就相當於

=VLOOKUP{查詢項目,資料查詢範圍,資料內欄位號碼,是否精準比對}

從函數內可以看見 VLOOKUP 函數內擁有4種參數,以下則是相關函數的說明。

  1. 「lookup_value」查詢項目:此參數就是你打算查詢的項目與依據,假如你想要找到某位同學的成績,那麼這位同學的名稱就是你的查詢項目,VLOOKUP 函數會以此查詢對應的數值與項目。
  2. 「table_array」資料查詢範圍:設定查詢時對應的表格範圍,函數將會從你選取的範圍內找到所對應的查詢項目。
  3. 「col_index_num」資料內欄位號碼:以資料查詢範圍內的最左側為第1欄,數字往右開始遞增,當你查詢時就會依據你所需要的項目來回傳指定欄位的資料。
  4. 「[range_lookup]」 是否精準比對:查詢資料時,查詢項目在查詢範圍內對應的項目是否要完全相同。

例如使用分數區間表來表示分數的話,設定為大致符合,那麼查詢時的分數即使沒有完全與區間表格相同,仍將會依照遞增排序回傳數值,函數內預設 “Ture” 是大致符合,”False” 則是完全相同。

接下來我們將會以微軟網頁版(Microsoft 365)的免費線上 EXCEL 表格做為示範操作。

並以圖中的考試成績分數表為範例,實際說明函數的各步驟將會如何達成我們所需要的結果。

2. 設定查詢數值

首先你需要將你所要查詢的數值輸入表格內,這樣函數才能依據你所要查詢的數值,對應到表格範圍內進行搜尋,像是下圖內,我們想要依照座號來做各種成績資訊的查詢,那麼就得先輸入想查詢的座號來做為根據。

 

3. 設定 VLOOKUP 函數

接下來假設你想要找到第二項資料的訊息,也就是各個座號對應的姓名的話,你需要將滑鼠點在 C4 儲存格,也就是姓名右側的空格。

再來當你想要插入 VLOOKUP 函數時,你可以從 EXCEL 的左上方功能列找到「插入」,點擊插入後你可以在左上角看見「函數」並點擊,這時就會看到 EXCEL 讓你選擇你想使用甚麼樣的函數,同樣地你也可以直接按下函數的符號就可以馬上插入預設的函數。

接下來選擇 VLOOKUP 函數,而函數需要輸入之前介紹的四項參數,而四項參數的設定當然可以用手動輸入特定數值,但是這樣做的話查詢其他項目時就無法一一對應更動後的數值,因此在輸入公式的時候,請點擊你要依據的儲存格,這樣就可以直接依照特定的儲存格來做為查詢依據。

像是第一項參數首先以點擊儲存格 C3 做為查詢項目,點選完後請使用逗點隔開參數。

接下來第二項參數則是範圍的選取,使用滑鼠左鍵將想查詢的資料範圍圈選出來。

如圖內 E3-J13 的格子,那麼函數就會依據這個範圍來查找相對應的資料,接下來同樣使用逗點隔開參數後。

第三項參數我們需要設定資料範圍內,函數搜尋時的對應欄位,像「姓名」在資料範圍內就是由最左邊算起第2欄位,因此我們這裡輸入數字2即可。

最後第四項參數則是設定是否使用精準比對,而在先前設定的資料範圍內,因為我們使用的查詢項目能夠精準的對應到搜尋範圍內的資料,而 EXCEL 內預設 FALSE 為完全相同,TRUE 為大致符合,所以這裡的函數寫入設定為 FALSE 即可。

那麼我們接下來透過重複以上的步驟,並且修改欄位編號,我們就能得到如同下表的最終結果。

可以看到當你自由切換座號時,就會跟著隨之變換數字,而成績因為還沒設定好所以目前預設是為數字0。

那麼我們假如要利用右邊的分數區間表,以總平均分數產生出對應到的「成績」,同樣地可以選取 K3 儲存格後插入 VLOOKUP 函數。

參數方面因為要抓取總平均分數為查詢項目,因此我們將設定 J3 儲存格為第一項參數,資料查詢範圍我們則以 M3 拉至 N8 的表格為第二項參數的範圍設定。

接下來成績在資料範圍內為第2欄,所以第三項參數設定為2,第四項參數因為總平均分數來說,無法精準的對應到成績的分數區間,所以輸入 TRUE 讓 EXCEL 自動大致符合即可。

而這邊有個特別要注意的地方就是,EXCEL 是採用向下遞增的順序做比對,所以如果原本的參照區間沒有設定好的話,就會顯示出不正確的對應值,所以使用大致符合的功能時,要記得調整區間的對應表為遞增。

以上設定好的話就會變成如圖的配置。

 

4. 絕對參照

那麼像是 EXCEL 還有一個向下複製的功能,因此當你上述的步驟設定好函數之後,你可能想透過下拉右下角來「相對參照」複製公式,但是當你下拉之後可能就會發現發生這樣的情況。

你會發現到往下到第4格的成績變成無法判斷的號碼,這其實是因為當時設定參數的時候,沒有用絕對參照的位置去設定,這樣的話當你往下拉來複製公式時,你原先所選取的資料範圍也會跟著資料往下移動,導致函數在搜尋設定的範圍時出現抓取錯誤的問題。

就會變成如圖中紅色框內,抓取到下方的白色儲存格,而上方的區間有誤,是錯誤的資料搜尋範圍。

這時候我們就可以使用「絕對參照」來使得函數內的參數選取範圍是固定的,你可以透過對參數選取範圍,以圖中的 M3:N8 為例,選取後按下 F4 即可使所選範圍出現$符號,就能使選取範圍固定不會跟著移動。

但因為只有固定資料查詢範圍,所以其他各項參數的數值依然可以透過 EXCEL「相對參照」的功能來往下複製公式,最終就可以如圖獲得想要的成績結果了。

 

結論

熟練使用EXCEL的各種技巧,能夠對平時文書工作上節省了許多的時間,並且能夠幫助你消除掉許多人為失誤的因素,讓你的錯誤率顯著降低。

善用這個VLOOKUP函數的實用技巧,就能使你在搜尋特定的相關資料時,再也不用煩惱無法處理龐大表格資訊的問題了。

不過即使看完了上述的說明,也要多實際嘗試幾次不同的操作,對各種函數的牢牢掌握,才能達到不論是工作或是其他業務上都能得心應手。