Excel函數應用之查詢與引用函數(下)

四、HLOOKUP、LOOKUP、MATCH、VLOOKUP

1、 LOOKUP函數與MATCH函數

LOOKUP函數可以返迴向量（單行區域或單列區域）或數組中的數值。此系列函數用於在表格或數值數組的首行查找指定的數值，並由此返回表格或數組當前列中指定行處的數值。當比較值位於數據表的首行，並且要查找下面給定行中的數據時，使用函數 HLOOKUP。當比較值位於要進行數據查找的左邊一列時，使用函數 VLOOKUP。

如果需要找出匹配元素的位置而不是匹配元素本身，則應該使用函數 MATCH 而不是函數 LOOKUP。MATCH函數用來返回在指定方式下與指定數值匹配的數組中元素的相應位置。從以上分析可知，查找函數的功能，一是按搜索條件，返回被搜索區域內數據的一個數據值；二是按搜索條件，返回被搜索區域內某一數據所在的位置值。利用這兩大功能，不僅能實現數據的查詢，而且也能解決如"定級"之類的實際問題。

2、 LOOKUP用於返迴向量（單行區域或單列區域）或數組中的數值。

函數 LOOKUP 有兩種語法形式：向量和數組。

（1） 向量形式

函數 LOOKUP 的向量形式是在單行區域或單列區域（向量）中查找數值，然後返回第二個單行區域或單列區域中相同位置的數值。

其基本語法形式為LOOKUP(lookup_value,lookup_vector,result_vector)

Lookup_value為函數 LOOKUP 在第一個向量中所要查找的數值。Lookup_value 可以為數字、文本、邏輯值或包含數值的名稱或引用。

Lookup_vector為只包含一行或一列的區域。Lookup_vector 的數值可以為文本、數字或邏輯值。

需要注意的是Lookup_vector 的數值必須按升序排序：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE；否則，函數 LOOKUP 不能返回正確的結果。文本不區分大小寫。

Result_vector 只包含一行或一列的區域，其大小必須與 lookup_vector 相同。

如果函數 LOOKUP 找不到 lookup_value，則查找 lookup_vector 中小於或等於 lookup_value 的最大數值。

如果 lookup_value 小於 lookup_vector 中的最小值，函數 LOOKUP 返回錯誤值 #N/A。

示例詳見圖3

（2） 數組形式

函數 LOOKUP 的數組形式在數組的第一行或第一列查找指定的數值，然後返回數組的最後一行或最後一列中相同位置的數值。通常情況下，最好使用函數 HLOOKUP 或函數 VLOOKUP 來替代函數 LOOKUP 的數組形式。函數 LOOKUP 的這種形式主要用於與其他電子表格兼容。關於LOOKUP的數組形式的用法在此不再贅述，感興趣的可以參看Excel的幫助。

3、 HLOOKUP與VLOOKUP

HLOOKUP用於在表格或數值數組的首行查找指定的數值，並由此返回表格或數組當前列中指定行處的數值。

VLOOKUP用於在表格或數值數組的首列查找指定的數值，並由此返回表格或數組當前行中指定列處的數值。

當比較值位於數據表的首行，並且要查找下面給定行中的數據時，請使用函數 HLOOKUP。

當比較值位於要進行數據查找的左邊一列時，請使用函數 VLOOKUP。

語法形式為：

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

其中，Lookup_value表示要查找的值，它必須位於自定義查找區域的最左列。Lookup_value 可以為數值、引用或文字串。

Table_array查找的區域，用於查找數據的區域，上面的查找值必須位於這個區域的最左列。可以使用對區域或區域名稱的引用。

Row_index_num為 table_array 中待返回的匹配值的行序號。Row_index_num 為 1 時，返回 table_array 第一行的數值，row_index_num 為 2 時，返回 table_array 第二行的數值，以此類推。

Col_index_num為相對列號。最左列為1，其右邊一列為2，依此類推.

Range_lookup為一邏輯值，指明函數 HLOOKUP 查找時是精確匹配，還是近似匹配。

下面詳細介紹一下VLOOKUP函數的應用。

簡言之，VLOOKUP函數可以根據搜索區域內最左列的值，去查找區域內其它列的數據，並返回該列的數據，對於字母來說，搜索時不分大小寫。所以，函數VLOOKUP的查找可以達到兩種目的：一是精確的查找。二是近似的查找。下面分別說明。

（1） 精確查找--根據區域最左列的值，對其它列的數據進行精確的查找

示例：創建工資表與工資條

首先建立員工工資表

然後，根據工資表創建各個員工的工資條，此工資條為應用Vlookup函數建立。以員工Sandy（編號A001）的工資條創建為例說明。

第一步，拷貝標題欄

第二步，在編號處（A21）寫入A001

第三步，在姓名（B21）創建公式

=VLOOKUP($A21,$A$3:$H$12,2,FALSE)

語法解釋：在$A$3:$H$12範圍內（即工資表中）精確找出與A21單元格相符的行，並將該行中第二列的內容計入單元格中。

第四步，以此類推，在隨後的單元格中寫入相應的公式。

（2） 近似的查找--根據定義區域最左列的值，對其它列數據進行不精確值的查找

示例：按照項目總額不同提取相應比例的獎金

第一步，建立一個項目總額與獎金比例的對照表，如圖6所示。項目總額的數字均為大於情況。即項目總額在0~5000元時，獎金比例為1%，以此類推。

第二步 假定某項目的項目總額為13000元，在B11格中輸入公式

=VLOOKUP(A11,$A$4:$B$8,2,TRUE)

即可求得具體的獎金比例為5%，如圖7。

4、 MATCH函數

MATCH函數有兩方面的功能，兩種操作都返回一個位置值。

一是確定區域中的一個值在一列中的準確位置，這種精確的查詢與列表是否排序無關。

二是確定一個給定值位於已排序列表中的位置，這不需要準確的匹配.

語法結構為：MATCH(lookup_value,lookup_array,match_type)　

lookup_value為要搜索的值。

lookup_array：要查找的區域(必須是一行或一列)。

match_type：匹配形式，有0、1和－1三種選擇："0"表示一個準確的搜索。"1"表示搜索小於或等於查換值的最大值，查找區域必須為升序排列。"－1"表示搜索大於或等於查找值的最小值，查找區域必須降序排開。以上的搜索，如果沒有匹配值，則返回＃N/A。

五、HYPERLINK

所謂HYPERLINK，也就是創建快捷方式，以打開文檔或網路驅動器，甚至INTERNET地址。通俗地講，就是在某個單元格中輸入此函數之後，可以到您想去的任何位置。在某個Excel文檔中，也許您需要引用別的Excel文檔或Word文檔等等，其步驟和方法是這樣的：

（1）選中您要輸入此函數的單元格，比如B6。

（2）單擊常用工具欄中的"粘貼函數"圖標，將出現"粘貼函數"對話框，在"函數分類"框中選擇"常用"，在"函數名"框中選擇HYPERLINK，此時在對話框的底部將出現該函數的簡短解釋。

（3）單擊"確定"後將彈出HYPERLINK函數參數設置對話框。

（4）在"Link_location"中鍵入要鏈接的文件或INTERNET地址，比如："c:my documentsExcel函數.doc"；在"Friendly_name"中鍵入"Excel函數"（這裡是假設我們要打開的文檔位於c:my documents下的文件"Excel函數.doc"）。

（5）單擊"確定"回到您正編輯的Excel文檔，此時再單擊B6單元格就可立即打開用Word編輯的會議紀要文檔。

HYPERLINK函數用於創建各種快捷方式，比如打開文檔或網路驅動器，跳轉到某個網址等。說得誇大一點，在某個單元格中輸入此函數之後，可以跳到我們想去的任何位置。　　

六、其他（CHOOSE、TRANSPOSE）

1、CHOOSE函數

函數CHOOSE可以使用 index_num 返回數值參數清單中的數值。使用函數 CHOOSE 可以基於索引號返回多達 29 個待選數值中的任一數值。

語法形式為：CHOOSE(index_num,value1,value2,...)

Index_num用以指明待選參數序號的參數值。Index_num 必須為 1 到 29 之間的數字、或者是包含數字 1 到 29 的公式或單元格引用。

Value1,value2,... 為 1 到 29 個數值參數，函數 CHOOSE 基於 index_num，從中選擇一個數值或執行相應的操作。參數可以為數字、單元格引用，已定義的名稱、公式、函數或文本。

2、TRANSPOSE函數

TRANSPOSE用於返回區域的轉置。函數 TRANSPOSE 必須在某個區域中以數組公式的形式輸入，該區域的行數和列數分別與 array 的列數和行數相同。使用函數 TRANSPOSE 可以改變工作表或宏表中數組的垂直或水平走向。

語法形式為TRANSPOSE(array)

Array為需要進行轉置的數組或工作表中的單元格區域。所謂數組的轉置就是，將數組的第一行作為新數組的第一列，數組的第二行作為新數組的第二列，以此類推。

示例，將原來為橫向排列的業績錶轉置為縱向排列。

第一步，由於需要轉置的為多個單元格形式，因此需要以數組公式的方法輸入公式。故首先選定需轉置的範圍。此處我們設定轉置後存放的範圍為A9.B14.

第二步，單擊常用工具欄中的"粘貼函數"圖標，將出現"粘貼函數"對話框，在"函數分類"框中選擇"查找與引用函數"框中選擇TRANSPOSE，此時在對話框的底部將出現該函數的簡短解釋。單擊"確定"後將彈出TRANSPOSE函數參數設置對話框。

第三步，選擇數組的範圍即A2.F3

第四步，由於此處是以數組公式輸入，因此需要按 CRTL+SHIFT+ENTER 組合鍵來確定為數組公式，此時會在公式中顯示"{}"。隨即轉置成功，如圖10所示。

以上我們介紹了Excel的查找與引用函數，此類函數的靈活應用對於減少重複數據的錄入是大有裨益的。此處只做了些拋磚引玉的示例，相信大家會在實際運用中想出更具實用性的應用方法