VLOOKUP函數用法及具體範例

VLOOKUP函數用法及具體範例

Excel 中的 VLOOKUP 函數在尋找資料時非常有用。以下是您需要了解的有關在 Excel 中使用 VLOOKUP 函數的資訊。

您想要檢查 Excel 電子表格中的特定值並找到有關它的資訊。在這種情況下,運行 VLOOKUP 可以節省大量精力。這是在 Microsoft Excel 中執行垂直查詢的最佳方法之一。本文將告訴您Microsoft Excel中VLOOKUP函數的意義、用法和說明範例。

如何使用VLOOKUP函數

什麼是VLOOKUP函數?

VLOOKUP函數是Excel中的資料搜尋函數。我假設您有 Excel 背景,並且可以使用 SUM、AVERAGE 和 TODAY 等基本函數。VLOOKUP 最常見的函數之一是資料函數,這意味著它對資料庫表或更簡單地對項目列表進行操作。該列表有很多種。您可以建立一個有關公司人員、產品類別、客戶清單或其他任何內容的表格。以下是A公司在市場上銷售的產品清單:

VLOOKUP函數用法及具體範例

資料庫表通常具有每個產品的識別碼。在上面的資料表中,特殊的識別標誌是「物品代碼」欄。注意:為了能夠使用VLOOKUP函數,資料表必須有一列包含識別標誌,例如代碼或ID,且必須是第一列,如上表。

VLOOKUP 可能是 Excel 中最著名的函數,但有好的也有壞的原因。從好的方面來說,VLOOKUP 函數易於使用並且可以做一些非常有用的事情。特別是對於新用戶來說,觀看 VLOOKUP 函數掃描表、找到匹配項並返回準確的結果是非常令人興奮的。熟練使用VLOOKUP函數是從Excel初學者到熟練用戶的必備技能。

不利的一面是,VLOOKUP 函數有限制且具有危險的預設值。與 INDEX 和 MATCH(或 XLOOKUP)不同,VLOOKUP 函數需要一個完整的表,其中第一列包含查找值。這使得使用具有多個條件的 VLOOKUP 函數變得困難。此外,VLOOKUP 的預設匹配行為很容易得到不正確的結果。不過別擔心!成功使用VLOOKUP函數的關鍵是掌握基礎知識。

VLOOKUP 有 4 個參數:lookup_value、table_array、column_index_num 和 range_lookup。Lookup_value 是要尋找的值,table_array 是要搜尋的垂直資料範圍。table_array的第一列必須包含要搜尋的查找值。參數column_index_num是要檢索的值的列號,其中table_array的第一列是第1列。

最後,range_lookup 控制尋找匹配項的行為。如果 range_lookup 為 TRUE,則 VLOOKUP 函數執行近似匹配。如果 range_lookup 為 FALSE,VLOOKUP 函數將執行精確比對。重要的是,range_lookup是可選的,預設為TRUE,因此VLOOKUP預設會執行近似匹配。

Excel中VLOOKUP函數的公式

Excel中的VLOOKUP函數的公式如下:

=VLOOKUP(查找值,表數組,col_index_num ,[範圍查找] )

在那裡:

  • VLOOKUP:是函數名
  • 粗體參數為必填項。
  • lookup_value:用於尋找的值
  • table_array:包含要尋找的值的表,輸入絕對值,前面帶$符號,例如:$A$3:$E$40。
  • col_index_num:包含 table_array 上尋找值的列的順序。例如表$A$3:$E$40中,B列包含要找的值,那麼這裡的col_index_num將為2;表$C$3:$F$40,E列包含查找值,則此處的col_index_num為3。
  • range_lookup:是搜尋範圍,TRUE相當於1(相對查找),FALSE相當於0(絕對查找)。公式中並不總是需要此參數。

只有在 table_array 中要搜尋的值已按順序(升序或降序或按字母順序)排列時才能套用相對搜尋。對於此類表,您可以使用相對查找,這類似於使用無限IF 函數。對於無法搜尋或未排序的值,使用絕對搜尋來尋找精確值。

使用 VLOOKUP 函數的影片教學

範例 1: VLOOKUP 函數評估學生和教職員的評分

假設您有一份學生成績單如下:

名字和姓氏 平均分數 分類
第一的 阮黃英 9.1  
2 陳文英 8.3  
3 阮光榮 9.5  
4 陳洪光 8.6  
5 杜清化 5.0  
6 黎紅玉 4.5  
7 段青雲 7.2  
8 吳玉碧 0.0  
9 黃秋濤 6.6  
丁明德 8.7  

排名規則表如下:

評級規定
0 虛弱的
5.5 中等的
7 相當
8.5 好的

現在我們將使用 VLOOKUP 函數輸入學生的評分。你會注意到排名表是按照從低到高(從弱到好)的順序排列的,所以在這種情況下我們可以使用相對搜尋。

在 Excel 中,這 2 個表格如下所示:

VLOOKUP函數用法及具體範例

這裡,要偵測的值位於 C 列,從第 6 行開始。搜尋範圍為 $A$18:$B$21,包含偵測值的列的順序為 2。

在儲存格 D6 中,輸入公式:=VLOOKUP($C6,$A$18:$B$21,2,1)。這是一個相對搜尋公式,如果您願意(或因為排名未排序),您可以透過在公式中新增0 來執行絕對搜索,如下所示: =VLOOKUP($C6 ,$A$18:$B$21,2 , 0)。按 Enter 鍵。

VLOOKUP函數用法及具體範例

點擊D6單元格,右下角出現一個小方塊,點擊它並向下拖曳表格以複製其餘學生的評分公式。

VLOOKUP函數用法及具體範例

當時我們使用VLOOKUP函數對學生的學業成績進行分類的結果如下:

VLOOKUP函數用法及具體範例

您是否想知道為什麼 $ 必須在 C6 之前使用?$ 將使 C 列保持固定,僅當您將公式拖曳到表格中時才更改行。還有 $A$18:$B$21 可以幫助修復排名規則表,使其在拖曳公式時不會改變。

範例2: VLOOKUP函數絕對搜尋以取得數據

假設您有一個員工資料表,儲存員工代碼、全名和職位。另一個表格儲存員工代碼、家鄉和教育程度。現在您想填寫每位員工的家鄉資訊和教育程度,該怎麼辦?

假設您有一張員工和員工家鄉的表,如下所示:

VLOOKUP函數用法及具體範例

現在您要填寫員工的家鄉資料。在儲存格 D4 中,輸入絕對搜尋公式,如下所示:=Vlookup($A4,$A$16:$C$25,2,0)

VLOOKUP函數用法及具體範例

然後按 Enter 鍵。按一下儲存格 D4 角落上出現的小方塊,並將其向下拖曳到表格中,以將公式複製給其他員工。

VLOOKUP函數用法及具體範例

要填寫員工的資格信息,請在儲存格 E4 中輸入絕對搜尋公式:=VLOOKUP($A4,$A$16:$C$25,3,0)

VLOOKUP函數用法及具體範例

你繼續按Enter鍵並向下滾動將公式複製到剩餘的員工中,我們將得到以下結果:

VLOOKUP函數用法及具體範例

範例3:使用VLOOKUP擷取數據

繼續範例2的資料集,我們現在將找到3名員工的家鄉:Nguyen Hoang Anh、Tran Van Anh和Nguyen Quang Vinh。我已將其提取到新表 F15:G18 中。

我將在表 A3:E13 上進行此搜索,填寫我的家鄉和資格後。現在,在儲存格 G16 中輸入以下絕對搜尋公式:=VLOOKUP($F16,$B$3:$E$13,3,0) > 按 Enter。

複製剩餘 2 名員工的公式,我們得到以下結果:

VLOOKUP函數用法及具體範例

請注意,在此範例中,檢測值位於 B 列中,因此檢測區域是從 B 列計算的,而不是從 A 列計算的。

範例 4:在 2 個不同的 Excel 工作表上使用 VLOOKUP 函數

回到範例2中的資料集,填寫完員工的資歷和家鄉後,我們將這張表命名為QTM。

VLOOKUP函數用法及具體範例

在電子表格的另一張名為 QTM1 的工作表中,您需要取得有關員工的資格和職位(員工的安排順序已更改)的資訊。這時您會看到 VLOOKUP 函數的真正威力。

VLOOKUP函數用法及具體範例

若要尋找有關員工「資格」的數據,請在工作表 QTM1 的儲存格 C4 中輸入公式:=VLOOKUP($B4,QTM!$B$3:$E$13,4,0)。

VLOOKUP函數用法及具體範例

在那裡:

  • B4 是包含用於偵測的值的欄位。
  • QTM!是包含要偵測的值的表格的工作表名稱。在工作表名稱後面加上 !
  • $B$3:$E$13 是包含查找值的表格和包含該表的工作表(QTM)。
  • 4 是「資格」欄的序號,根據 QTM 表上的「全名」欄計算。
  • 0為絕對檢測。

按 Enter 鍵,然後複製表中所有剩餘員工的公式,我們得到以下結果:

VLOOKUP函數用法及具體範例

若要尋找員工的「職位」數據,請在工作表 QTM1 的儲存格 D4 中輸入公式:=VLOOKUP($B4,QTM!$B$3:$E$13,2,0),然後按 Enter。

VLOOKUP函數用法及具體範例

複製剩餘員工的公式,我們得到以下結果:

VLOOKUP函數用法及具體範例

VLOOKUP函數有什麼用?

首先,我們需要找到「我們使用 VLOOKUP 函數做什麼?」這個問題的確切答案。

VLOOKUP 函數用於支援根據可用識別碼在資料欄位或清單中尋找資訊。

例如,將帶有產品代碼的 VLOOKUP 函數插入到另一個電子表格中將顯示與該代碼對應的產品資訊。這些資訊可以是描述、價格、庫存數量,具體取決於您編寫的食譜的內容。

需要找的資訊量越小,寫VLOOKUP函數就越困難。通常,您會在可重複使用工作表中使用此函數作為範本。每次輸入適當的產品代碼時,系統都會檢索有關對應產品的所有必要資訊。

關於 VLOOKUP 函數的注意事項

以下是有關 Excel 中的 VLOOKUP 函數需要記住的重要事項清單:

  • 當省略 range_lookup 時,VLOOKUP 將允許非精確匹配,但如果可用,仍將使用精確匹配。
  • 這個函數最大的限制是它總是取右邊的值。此函數將從表中第一列右側的列中檢索資料。
  • 如果查找列包含重複值,VLOOKUP 函數將只符合第一個值。
  • 此函數不區分大小寫。
  • 假設工作表中有一個現有的 VLOOKUP 公式。在這種情況下,如果向表中插入列,公式可能會中斷,因為列索引值是硬編碼的,在插入或刪除列時不會自動變更。
  • VLOOKUP 允許使用通配符,例如星號 (*) 或問號 (?)。
  • 假設在表中您正在使用一個包含作為文字輸入的數字的函數。如果您只是從表中的列中獲取數字作為文本,那並不重要。但如果表格的第一列包含以文字輸入的數字,則#N/A! 如果查找值也不是文本,則會顯示。
  • 錯誤#N/A!如果 VLOOKUP 函數未找到與提供的 Lookup_value 相符的值,則會發生此情況。
  • 錯誤#REF!如果出現以下情況,就會發生:
    • col_index_num 參數大於提供的 table_array 中的列數
    • 公式嘗試引用不存在的儲存格。
  • 錯誤#VALUE!如果出現以下情況,就會發生:
    • col_index_num 參數小於 1 或未辨識為數值
    • range_lookup 參數不會被辨識為邏輯值 TRUE 或 FALSE 之一。

Excel 中 VLOOKUP 與 XLOOKUP 函式之間的主要差異

查找表

X查找

預設完全匹配

傳回尋找資料右側的值

傳回尋找資料左側的值

傳回多個值

查找列需要排序

僅近似匹配

僅二分查找

從上到下搜尋

心從下到上

二分查找

自訂查找值未找到訊息

尋找通配符

精確搜尋

近似搜尋傳回下一個較小的值

近似搜尋傳回下一個較大的值

如果查找值未排序,可能會傳回錯誤值

Y - 近似值

N - 近似值,

Y - 二進位

新增列來尋找表格可能會破壞公式

Y - 是

N-

看更多:

了解如何使用 VLookup 後,您還需要了解常見錯誤以及如何解決問題。

在 Excel 中使用 VLOOKUP 函數時常見的錯誤

查找值位於錯誤的列中

使用 VLOOKUP 公式時最常見的錯誤之一是該函數僅傳回值 #N/A。當它找不到您要求 VLOOKUP 查找的查找值時,就會發生此錯誤。

在某些情況下,您可以使用 XLOOKUP 函數代替 VLOOKUP,但是,此錯誤只能在 VLOOKUP 函數中輕鬆修正。

如何解決該問題:

VLOOKUP沒有找到想要的值的原因可能是因為該值不在表中。但是,如果它返回所有 #N/A 值,查找值是文本,那麼很可能是因為它只能從左到右讀取。

在 Excel 中為 VLOOKUP 建立表格時,請確保將查找值放置在要傳回的值的左側。最簡單的方法是將其放置在表格的第一列中。

VLOOKUP函數用法及具體範例

現在公式將傳回一個值:

VLOOKUP函數用法及具體範例

整體而言,VLOOKUP 是在 Microsoft Excel 中更快查詢資料的好方法。儘管 VLOOKUP 查詢在列上垂直運行並具有一些其他限制,但 Microsoft 不斷更新 Excel 的查找功能以擴展其適用性。例如,HLOOKUP、XLOOKUP...可以幫助您從許多不同的方向找到資料。QuanTriMang.com將在後續的文章中繼續為您提供如何使用該功能的資訊。


Excel中的IFERROR函數、公式及用法

Excel中的IFERROR函數、公式及用法

學習如何在 Excel 中使用非常常用的 IFERROR 函數,提升您的數據處理效率。

如何在 Excel 中使用 VSTACK 和 HSTACK 函數

如何在 Excel 中使用 VSTACK 和 HSTACK 函數

想知道如何在 Excel 中堆疊和附加資料嗎?讓我們一起學習如何使用 Microsoft Excel 中的 VSTACK 和 HSTACK 函數!

SUBTOTAL 函數:公式以及如何在 Excel 中使用 SUBTOTAL 函數

SUBTOTAL 函數:公式以及如何在 Excel 中使用 SUBTOTAL 函數

了解 Excel 中的 SUBTOTAL 函數及其用途,讓計算更簡單有效!

Excel中的除法函數:MOD函數(求餘數)與QUOTIENT函數(求整數部分)

Excel中的除法函數:MOD函數(求餘數)與QUOTIENT函數(求整數部分)

MOD函數是Excel中除法求餘的函數。學習如何輕鬆使用MOD函數和QUOTIENT函數來計算餘數和整數部分,提升您的Excel技能。

在 Excel 上玩“拼圖”

在 Excel 上玩“拼圖”

Reddit 用戶 MehmetSalihKoten 在 Microsoft Excel 中創建了俄羅斯方塊的全功能版本。

如何使用Excel中的轉換功能

如何使用Excel中的轉換功能

要轉換 Excel 中的測量單位,我們將使用 Convert 函數。

Excel中HLOOKUP函數的使用方法

Excel中HLOOKUP函數的使用方法

HLOOKUP 函數是什麼?如何在Excel中使用HLOOKUP函數?讓我們來和 LuckyTemplates 一起找出答案吧!

如何在 Excel 中使用 VALUE 函數

如何在 Excel 中使用 VALUE 函數

Excel中的VALUE函數是什麼?Excel中的數值公式是什麼?讓我們透過 LuckyTemplates.com 來了解一下吧!

Excel EOMONTH函數,EOMONTH函數如何使用

Excel EOMONTH函數,EOMONTH函數如何使用

Excel中的EOMONTH函數用於顯示給定月份的最後一天,實作非常簡單。然後,您將根據我們輸入的數據獲得特定月份的最後一天。

如何在 Excel VBA 中使用 do-while 循環

如何在 Excel VBA 中使用 do-while 循環

想要在 Excel VBA 中自動執行重複任務嗎?因此,讓我們學習如何使用 Do-While 迴圈來實作一系列重複操作,直到滿足條件。