VLOOKUP是如何運行的

引言:在Excel中,VLOOKUP函數應該是最受關注的函數之一了。關於VLOOKUP函數,在網上有數不清的討論和文章。這裡介紹的是DAVE BRUNS編寫的一篇有代表性的文章《23things you should know about VLOOKUP》,我對其進行了一些整理,供大家進一步理解和運用VLOOKUP函數時參考。
當你想從表中提取信息時,Excel的VLOOKUP函數是一個很好的解決方案。從表中動態查找和獲取信息的能力給許多用戶帶來了全新的改變,你到處都可以發現VLOOKUP。

儘管VLOOKUP相對容易使用,但也容易出錯。其中一個原因是VLOOKUP有一個主要的設計缺陷,默認情況下,假定你認為的是近似匹配,但這可能並不是你的本意。因此,會導致看起來結果正常但實際上是錯誤的。

1.VLOOKUP是如何運行的

VLOOKUP是一個查找函數,可以獲取表中的數據。在VLOOKUP中的「V」代表垂直,意味著表中的數據必須垂直地排列,即數據在行中。

如果你有一個結構良好的表,信息垂直排列,左邊有一列可以用來匹配查找的數據,那麼可以使用VLOOKUP。

VLOOKUP要求表結構化,在左側列中顯示查找值,在右側任意列中顯示想要獲取的數據(結果值)。當使用VLOOKUP時,假設表中的每列都從左側(查找列)列始編號。要從特定列中獲取值,簡單地提供合適的數字作為「列索引」即可。在下面的例子中,查找電子郵件地址,因此使用數字4作為列索引:

VLOOKUP是如何運行的

圖1

在圖1所示的表中,員工ID位於左側第1列,電子郵件地址在右側的第4列。

要使用VLOOKUP,要提供4個參數:

要查找的值(lookup_value)

組成表的單元格區域(table_array)

要獲取的結果所在的列編號(column_index)

匹配模式(range_lookup,TRUE=近似匹配,FALSE=精確匹配)

2.VLOOKUP僅向右查找

可能VLOOKUP最大的局限是僅能向右查找來獲取數據。這意味著VLOOKUP僅能獲取表中第一列右側列的數據。當查找值在第一列(最左側列)時,這個限制沒有多大意義,因為所有其他列已經在右側。但是,如果查找列在表裡的某個位置,則只能從該列右側的列中查找值。還必須為VLOOKUP提供一個以查找列開始的更小的表。

VLOOKUP僅向右查找

圖2

使用INDEX和MATCH代替VLOOKUP可以克服這個局限。

3.VLOOKUP總是查找第一個匹配值

如果查找列包含重複值,那麼VLOOKUP將僅匹配找到的第1個值。如果表中的第1列沒有重複值,這顯然不是問題。但是,如果第1列包含重複值,那麼VLOOKUP將僅匹配第1個值。例如,使用VLOOKUP查找名字,雖然表中有兩個「Janet」,但VLOOKUP僅匹配第1個:

VLOOKUP總是查找第一個匹配值

圖3

4.VLOOKUP不區分大小寫

查找值時,VLOOKUP不會處理大寫和小寫文本差異。對於VLOOKUP,產品代碼「PQRF」與「pqrf」相同。下面的示例中,查找大寫的「JANET」,但VLOOKUP不會區分大小寫,因此簡單地匹配「Janet」,因為這是找到的第1個匹配:

4.VLOOKUP不區分大小寫

圖4

5.VLOOKUP有兩種匹配模式

VLOOKUP有兩種操作模式:完全匹配和近似匹配。大多數情況下,可能想使用VLOOKUP的完全匹配模式。當你想要基於某種唯一鍵(例如,基於產品代碼的產品信息或者基於電影名稱的電影數據)查找信息時,這是有意義的:

5.VLOOKUP有兩種匹配模式

圖5

在單元格H6中的公式基於完全匹配電影名稱來查找年:

=VLOOKUP(H4,B5:E9,2,FALSE)

但是,如果不匹配唯一ID,而是查找「最佳匹配」或者「最佳類別」,則需要使用近似匹配。例如,可能要根據重量查找郵資,根據收入查找稅率,根據每月銷售額查找佣金率。在這些情況下,可能無法在表中找到精確的查找值,相反,想要VLOOKUP來為提供的查找值獲得最佳匹配。

圖6

在單元格D5中的公式使用近似匹配獲取正確的佣金率:

=VLOOKUP(C5,$G$5:$H$10,2,TRUE)

6.注意:VLOOKUP默認情形下使用近似匹配

第4個參數,稱之為「range_ lookup」控制著VLOOKUP的完全和近似匹配。

對於完全匹配,使用FALSE或者0。對於近似匹配,設置range_lookup為TRUE或1:

=VLOOKUP(value,table,column,TRUE) //近似匹配

=VLOOKUP(value,table,column,FALSE) //完全匹配

然而,第4個參數range_lookup是可選的,默認值是TRUE,這意味著VLOOKUP默認情形下進行近似匹配。當進行近似匹配時,VLOOKUP假定表已排序並執行二分查找。在二分查找時,如果VLOOKUP找到完全匹配的值,則從該行返回一個值。但是,如果VLOOKUP遇到大於查找值的值,將從前一行返回一個值。

這種默認設置非常危險,因為許多人無意中將VLOOKUP保留在默認模式下,當表未排序時可能導致錯誤的結果。

為了避免這個問題,確保在想要完全匹配時使用FALSE或0作為第4個參數。

7.你可以強迫VLOOKUP執行完全匹配

要強迫VLOOKUP找到完全匹配,確保設置第4個參數(range_lookup)為FALSE或0。下面兩個公式等價:

=VLOOKUP(value,table,column,FALSE)

=VLOOKUP(value,table,column,0)

在完全匹配模式下,當VLOOKUP不能找到值時,返回#N/A。清楚地表明沒有在表中找到值。

8.你可以告訴VLOOKUP執行近似匹配

要使用VLOOKUP的近似匹配模式,忽略第4個參數(range_lookup)或者設置其為TRUE或1。下面3個公式等價:

=VLOOKUP(value,table,column)

=VLOOKUP(value,table,column,1)

=VLOOKUP(value,table,column,TRUE)

推薦總是顯式設置range_lookup參數,即使VLOOKUP不需要。這樣,你總能明顯地看到你期望的匹配模式。

9.對於近似匹配,數據必須排序

如果使用近似匹配模式,那麼數據必須根據查找值按升序排序。否則,可能得到的是錯誤結果。同時注意,有時文本數據可能看起來已排序,雖然實際上並沒有排序。

10.VLOOKUP能夠合併不同表中的數據

VLOOKUP的常見使用示例是連接來自兩個或多個表中的數據。例如,可能在一個表中有訂單數據,在另一個表中是客戶數據,想要將一些客戶數據合併到訂單表中進行分析:

10.VLOOKUP能夠合併不同表中的數據

圖7

由於客戶Id在兩個表中都存在,可以在VLOOKUP中使用這個值來提取數據,只需配置VLOOKUP使用表1中的Id值,表2中相應的列索引和數據。在上例中,使用兩個VLOOKUP公式,一個提取客戶名稱,另一個提取客戶狀態。

圖8

11.VLOOKUP能夠識別或分類數據

如果需要將任意類別應用於數據記錄,那麼可以使用VLOOKUP輕鬆完成此操作,方法是使用擔當「鍵」的表來賦值分類。

一個經典的例子是基於分數來賦值成績:

11.VLOOKUP能夠識別或分類數據

圖9

本例中,VLOOKUP設置為近似匹配,因此表按升序進行排列是重要的。

然而,也可以使用VLOOKUP來賦值任意類別。在下面的例子中,使用VLOOKUP來為每個部門計算一個組,使用了定義分組的小表(稱為「key」)。

VLOOKUP

圖10

12.絕對引用使VLOOKUP更具可移植性

在打算從表中獲取多於1列的信息的情形下,或者需要複製和粘貼VLOOKUP時,可以通過對查找值和表數組使用絕對引用來節省時間。這可讓你複製公式,然後僅改變列索引編號以使用相同的查找來從不同列中獲取值。

下面的示例中,因為查找值和表數組是絕對引用,所以可以跨列複製公式,然後按需要回來修改列索引。

12.绝对引用使VLOOKUP更具可移植性

圖11

13.命名區域使VLOOKUP更容易閱讀(並且更可移植)

絕對單元格區域相當難看,因此可以通過使用命名區域代替絕對引用使VLOOKUP公式更簡潔易讀。

在圖11中,命名輸入單元格為「id」,命名表中的數據為「datas」,可以編寫公式:

13.命名區域使VLOOKUP更容易閱讀

圖12

不僅公式易讀,而且更具可移植性,因為命名區域自動為絕對引用。

14.插入列可能中斷現有的VLOOKUP公式

如果工作表中已經存在VLOOKUP公式,那麼在表中插入列時可能中斷公式。這是因為當插入或刪除列時,硬編碼的列索引值不會自動更改。

本示例中,當在Year和Rank之間插入新列後,查找Rank和Sales被中斷,而Year工作正常,因為其所在列在插入列的左側,沒有受到影響:

14.插入列可能中斷現有的VLOOKUP公式

圖13

為了避免這種問題,可以使用下文描述的技巧計算列索引號。

15.可以使用ROW或COLUMN計算列索引號

如果不想在複製公式後還要對公式進行編輯,那麼可以使用ROW或COLUMN來生成動態的列索引號。如果從連續列中獲取數據,這個技巧可讓你設置一個VLOOKUP公式,然後將其複製而無需進行任何修改。

本例中,使用COLUMN函數生成動態的列索引號。在單元格C3中,COLUMN函數返回當前列的列號3,將其減去1得到表中列的索引號,然後向右複製該公式:

15.可以使用ROW或COLUMN計算列索引號

圖14

所有的公式都是相同的,不需要任何編輯。使用的公式如下:

=VLOOKUP(ids,datax,COLUMN()-1,0)

16.VLOOKUP+MATCH用於完全動態的列索引

將上面介紹的技巧更進一步,可以使用MATCH來查找表中列的位置,返回完全動態的列索引號。

有時這被稱作雙向查找,因為正沿著行和列的方向查找。

一個示例是查找銷售人員在指定月份的銷售量,或者查找從指定的供應商特定產品的價格。例如,假設有銷售人員每月的銷售量:

16.VLOOKUP+MATCH用於完全動態的列索引

圖15

VLOOKUP可以容易地查找銷售人員,但是沒有辦法自動處理月名。技巧是使用MATCH函數代替靜態列索引號。

圖16

注意,我們給出的匹配區域包括表中所有列,以便同步VLOOKUP中使用的列號。

=VLOOKUP(H2,saledata,MATCH(H3,months,0),0)

17.VLOOKUP允許使用通配符進行部分匹配

任何時候在完全匹配模式下使用VLOOKUP時,都可以在查找值中使用通配符。這看起來違反直覺,但是通配符讓你根據部分匹配進行精確匹配。

Excel提供了2個通配符:星號(*)匹配1個或多個字符,問號(?)匹配1個字符。

例如,可以直接在單元格中輸入星號,然後引用它作為VLOOKUP中的查找值。在圖17中,被命名為「val」的單元格H3中輸入「Mon*」,將導致VLOOKUP匹配名字「Monet」。

17.VLOOKUP允許使用通配符進行部分匹配

圖17

本例中的公式為:

=VLOOKUP(val,dataname,1,0)

如果你願意,可以調整VLOOKUP公式使用內置的通配符,如下圖18所示,簡單地連接單元格H3中的值和通配符。

VLOOKUP

本例中,在VLOOKUP函數里將查找值和星號連接:

=VLOOKUP(val &”*”,dataname,1,0)

注意,小心使用通配符和VLOOKUP。雖然提供了一個容易的辦法創建「偷懶的匹配」,但是也容易找到錯誤的匹配。

18.可以捕獲#N/A錯誤並顯示友好的消息

在完全匹配模式,當沒有找到匹配時VLOOKUP將顯示#N/A錯誤。一方面,這是有用的,因為明確告訴你在查找表中沒有匹配。然而,#N/A看起來不是很友好,因此有幾種方法可以捕獲這個錯誤並顯示為其他內容。

一旦開始使用VLOOKUP,肯定會遇到#N/A錯誤。當VLOOKUP不能找到匹配項時,發生這種錯誤。

這是有用的錯誤,因為VLOOKUP清楚地告訴你不能夠找到查找值。本例中,「Latte」在表中不存在,因此VLOOKUP拋出#N/A錯誤。

18.可以捕獲#N/A錯誤並顯示友好的消息

圖19

本例中的公式是完全標準的完全匹配:

=VLOOKUP(E4,datab,2,0)

然而,#N/A看起來不是非常友好,因此你可能想要捕獲這個錯誤並顯示更友好的消息。

最容易的方法是將VLOOKUP放在IFERROR函數里面。IFERROR允許捕獲任意錯誤並返回你選擇的結果。要捕獲錯誤並顯示「沒有找到」消息來代替該錯誤,可以將原來的公式簡單地放置在IFERROR裡面並設置你想要的結果:

圖20

如果找到了查找值,那麼沒有錯誤發生並且VLOOKUP函數返回正常的結果。下面是公式:

=IFERROR(VLOOKUP(E4,datab,2,0),”沒有找到”)

19.數字作為文本可能導致匹配錯誤

有時,在VLOOKUP中使用的表可能包含以文本形式輸入的數字。如果只是將數字作為文本從表中檢索,則無關緊要。但是,如果表中的第1列包含以文本形式輸入的數字,而查找值不是文本,則會出現#N/A錯誤。

下面的例子中,planet表的ids是以文本形式輸入的數字,由於查找值是數字3,因而導致VLOOKUP返回錯誤:

19.數字作為文本可能導致匹配錯誤

圖21

要解決這個問題,需要確保查找值和表中的第1列都是相同的數據類型(都是數字或者都是文本)。

一種方法是將查找列中的值轉換為數字。然而,如果不容易控制源表格,也可以調整VLOOKUP公式來轉換查找值為文本,如下面所示通過在查找值後連接」」:

=VLOOKUP(idn &””,planets,2,0)

圖22

如果無法確定何時會有數字,何時有文本,那麼可以通過在IFERROR中放置VLOOKUP來處理這兩種情況:

=IFERROR(VLOOKUP(idn,planets,2,0),VLOOKUP(idn& “”,planets,2,0))

20.可以使用VLOOKUP來替換嵌套的IF語句

VLOOKUP最有趣的用法之一是替換嵌套的IF語句。如果你曾經構建過一系列嵌套的IF語句,知道它們工作正常,但它們需要很多括號,也必須注意嵌套的順序,以免引入邏輯錯誤。

例如,嵌套的IF語句的常見用法是根據分數來確定成績。下面的示例中,可以看到使用嵌套的IF語句構建的公式可以實現。

20.可以使用VLOOKUP來替換嵌套的IF語句

圖23

完整的IF嵌套公式如下:

=IF(C5<64,”F”,IF(C5<73,”D”,IF(C5<85,”C”,IF(C5<95,”B”,”A”))))

公式工作正常,但是要注意,邏輯關係和實際分數都直接編寫進公式。如果因任何原因修改了分數,需要仔細更新公式,然後將其複製到整個表中。

相比之下,VLOOKUP可以使用一個簡單的公式來賦值成績。所需要做的是確保為VLOOKUP設置好成績表,即按分數排序,且包含括號來處理所有分數。

VLOOKUP

圖24

此時,使用的公式為:

=VLOOKUP(C5,grade,2,TRUE)

這種方法的好處是,邏輯和分數都內置在成績指標表中。如果有任何改變,可以直接修改更新表,無需編輯公式,VLOOKUP公式將自動更新。

21.VLOOKUP可以僅處理單個條件

根據設計,VLOOKUP只能根據單個條件查找值,該條件作為查找值在表的第1列(查找列)中查找。

這意味著,諸如在「Accounting」中查找姓氏為「Smith」的員工,或者根據在單獨的列中的名字和姓氏來查找員工,都是不容易的。

然而,有辦法來克服這個局限。一種解決方法是創建輔助列,用來連接來自不同列的值來創建查找值,類似多條件。例如,想要查找員工的部門和組,但是名字和姓氏在不同的列中,怎樣實現同時查找呢?

21.VLOOKUP可以僅處理單個條件

圖25

首先,添加輔助列,將名字和姓氏連接在一起:

圖26

然後,讓VLOOKUP來使用包含了新列的表,將名字和姓氏連接作為查找值:

圖27

最後的VLOOKUP公式將輔助列作為查找列來查找名字和姓氏連在一起的值:

=VLOOKUP(C3&D3,name,4,0)

22.兩個VLOOKUPS比一個VLOOKUP更快

這可能看起來很瘋狂,但是當你有大量數據並需要執行完全匹配時,可以通過在公式中添加另一個VLOOKUP來加快VLOOKUP的速度!

背景:假設你有大量的訂單數據,例如超過了10000條記錄,並且正在使用VLOOKUP來基於訂單ID查找訂單總數。因此,使用的公式形式如下:

=VLOOKUP(order_id,order_data,5,FALSE)

公式最後的FALSE迫使VLOOKUP執行完全匹配。你需要完全匹配,因為有可能找不到訂單號。此時,完全匹配設置將導致VLOOKUP返回#N/A錯誤。

問題是完全匹配非常慢,因為Excel必須以線性方式遍歷所有值,直至找到匹配或者不匹配。

相反,近似匹配相當快,因為Excel能夠執行所謂的二分查找。

然而,二分查找的問題(VLOOKUP處於近似匹配模式)是當找不到值時,VLOOKUP可能返回錯誤的結果。更糟糕的是,結果可能看起來完全正常,因此很難發現錯誤。

解決方案是在近似匹配模式下使用VLOOKUP兩次。第1個實例簡單地檢查該值是否真的存在。如果存在,另一個VLOOKUP運行(同樣,在近似匹配模式)來獲取想要的數據。如果不是,可以返回你想要的任意值來指示沒有找到結果。

最後的公式形式如下:

=IF(VLOOKUP(order_id,order_data,1,TRUE)=order_id,VLOOKUP(order_id,order_data,5,FALSE),」Missing」)

注意,使用這個技巧時,數據必須已排序。這只是一種防止查找值丟失的方法,同時保持快速查找。

23.INDEX和MATCH組合能夠做VLOOKUP能夠做的任何事情,甚至更多

INDEX和MATCH組合與VLOOKUP孰優孰劣,在網上有許多爭論。

INDEX+MATCH可以完成VLOOKUP(和HLOOKUP)所能做的所有事情,並且更靈活,但也更複雜。因此,支持INDEX+MATCH的人會說,最好先學習INDEX和MATCH,因為最終會提供給你一個更好的工具集。

反對INDEX+MATCH的觀點是需要兩個函數,因此對用戶來說,學習和精通更複雜。

如果經常使用Excel,需要學習如何使用INDEX和MATCH,這是一個非常強大的組合。

但也應該學習VLOOKUP,因為經常會在很多工作表中發現VLOOKUP的使用。在直觀的情形下,VLOOKUP可以毫不費力地實現目的。

By:  23 things you should know about VLOOKUP

 

發佈回覆

你的電郵地址並不會被公開。