【引用】EXCEL函數VLOOKUP如何引用其他工作簿

06-15

2011-02-16 22:26:57|分類：其它軟體 |標籤：vlookupxlsexcel |字型大小大中小訂閱

本文引用自秋天的麥兜《EXCEL函數VLOOKUP如何引用其他工作簿》

　　在工作及生活中，

經常會遇到這樣的需求：在一個數據列表中查找是否包含指定的若干數據項，並根據查找結果返回另外一項數據。例如：我們已經有下圖所示一張表，其中記錄了梁山108將的基本信息，文件名稱為梁山108將名單.XLS，表名為 Sheet1，存儲在 D:Test 目錄下（我們叫它表1）。

　　現在，我們又拿到了一個在征方臘等戰役中陣亡的梁山好漢名單（我們叫它表2），但該名單中沒有各位好漢的「綽號」，讓人感覺有點遺憾。如何才能方便地把表1中「綽號」信息補充到表2中呢？當然你可以查一查表1敲進去，那樣我可就只好去睡覺啦！這個方法只適合幾十個數據項以下，如果有成千上百就不適用啦

　　此時，本小子特別鍾情於用VLOOKUP函數，為便於演示其用法，我們先把表1中的「姓名」與「綽號」兩列數據複製到表2中放在D和E兩列（我們只要在這兩列數據中查找即可，如果需要其他信息請比照執行

）。然後在B2單元格輸入下述公式：

　　這裡的公式 =VLOOKUP(A2,D:E,2,FALSE) 的意思是說：在D:E這兩列（暫稱之為搜索區域）中查找A2單元格中的值「鮑旭」，找到後把搜索區域中與「鮑旭」在同一行的第2列數值（即「喪門神」）返回並顯示在B2單元格中（詳細用法請參見本文後面的註解）；如果在搜索區域中未找到指定的數值（即姓名），EXCEL會返回一個錯誤值：#N/A。如下圖紅色所示的「晁蓋」，因為宋江要做老大，而晁天王無論在政治還是黑心上均鬥不過江哥，不僅無端被「借刀殺」，就連個座次也沒排上，千古奇冤啊

　　這樣，我們就基本解決了開頭提出的問題。在數據量較大、為保持表格整齊或者數據不允許拷來拷去時怎麼辦？當然我們可以把搜索區域的信息複製到同一個工作簿中的一張新表上，但最直接的辦法還是在上述公式中引用其他工作簿中的相關區域，此例為：梁山108將名單.XLS 中表名為 Sheet1 的A:B兩列。現在我們把剛剛複製到表2中的D:E兩列信息刪掉，上述公式自然會由於找不到相關的搜索區域而報錯顯示為「#REF!」，只要把公式中這個位置的信息換成表1中的A:B區域就行啦。首先要打開梁山108將名單.XLS，然後如下圖所示選中公式中的錯誤信息（即用滑鼠塗黑）：

　　切換到梁山108將名單.XLS（ALT+TAB或用滑鼠都可以），選中A:B兩列，此時公式中的錯誤信息即被自動替換。

　　鍵入回車，該公式就修改完畢，關閉梁山108將名單.XLS，把公式複製到所有單元格，正確的公式如下圖：

　　在此可以看到，在EXCEL公式中引用其他工作簿的格式為："目錄[文件名.xls]表名"!區域，注意兩個單引號是必須的，文件名稱兩邊要用[ ]括起來。但這種格式的引用將隨著文件所在的目錄名稱長度而激增，閱讀及使用均不方便，出錯後也不容易查找。還好，EXCEL也允許我們給這樣的區域自定義一個名稱，並且在公式中直接使用。如下圖選擇菜單「插入」-「名稱」-「定義」：

　　打開「定義名稱」對話框，在引用位置中輸入正確的區域表達式，在名稱中輸入一個好記的名稱，如 List ，點擊確定之後EXCEL就把該區域表達式記憶下來，並且給它一個名字 List ，之後想用到這個區域時只要輸入這個名稱就行啦。

　　如下圖所示，是分別使用區域名稱及定義名稱的方式比較，其結果完全一致。另外，對於前面我們提到過的可憐人物「晁蓋」，由於不在108將中，所以找不到其綽號而出錯啦。為了保持表格的美觀，我們常常也需要對這種情況進行處理，以便讓其不顯示那些看上去彆扭的錯誤信息。在此，小子給出了使用另外兩個函數ISERR及IF來解決此類問題的方法之一。

　　公式中的出現的List就是我們在上一步中定義的名稱，它實際指代了那個長長的區域名稱："D:Test[梁山108將名單.xls]Sheet1"!$A:$B。

　　註：VLOOKUP函數用法（摘抄自Microsoft Excel 幫助文檔）：

　　在表格數組的首列查找值，並由此返回表格數組當前行中其他列的值。VLOOKUP 中的 V 表示垂直方向。當比較值位於需要查找的數據左邊的一列時，可以使用 VLOOKUP，而不用 HLOOKUP。　　=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

　　參數詳解：

Lookup_value　為需要在表格數組（數組:用於建立可生成多個結果或可對在行和列中排列的一組參數進行運算的單個公式。數組區域共用一個公式；數組常量是用作參數的一組常量。）第一列中查找的數值。Lookup_value 可以為數值或引用。若 lookup_value 小於 table_array 第一列中的最小值，VLOOKUP 將返回錯誤值 #N/A。

Table_array　為兩列或多列數據。請使用對區域的引用或區域名稱。table_array 第一列中的值是由 lookup_value 搜索的值。這些值可以是文本、數字或邏輯值。不區分大小寫。

Col_index_num　為 table_array 中待返回的匹配值的列序號。Col_index_num 為 1 時，返回 table_array 第一列中的數值；Col_index_num 為 2，返回 table_array 第二列中的數值，以此類推。如果 col_index_num ：　　小於 1，VLOOKUP 返回錯誤值 #VALUE!。　　大於 table_array 的列數，VLOOKUP 返回錯誤值 #REF!。

Range_lookup　為邏輯值，指定希望 VLOOKUP 查找精確的匹配值還是近似匹配值：如果為 TRUE 或省略，則返回精確匹配值或近似匹配值。也就是說，如果找不到精確匹配值，則返回小於 lookup_value 的最大數值。table_array 第一列中的值必須以升序排序；否則 VLOOKUP 可能無法返回正確的值。可以選擇「數據」菜單上的「排序」命令，再選擇「遞增」，將這些值按升序排序。有關詳細信息，請參閱默認排序次序。如果為 FALSE，VLOOKUP 將只尋找精確匹配值。在此情況下，table_array 第一列的值不需要排序。如果 table_array 第一列中有兩個或多個值與 lookup_value 匹配，則使用第一個找到的值。如果找不到精確匹配值，則返回錯誤值 #N/A。