[查找與引用函數——Excel函數的應用]

06-18

查找與引用函數——Excel函數的應用2013-10-21 11:06 | 來源: www.excellm.com | 作者: Excel聯盟| 點擊: 5145次

1.查找與引用函數簡介

查找與引用Excel函數是統計和分析中經常要用到的函數。下面介紹幾種常用的查找與引用函數。

CHOOSE 函數

函數功能：返回參數列表中的值。

語法形式：

CHOOSE (index_num, valuel, value2 , ...)

參數說明：index_num用於指明待選參數序號的參數值，其必須是1～29的數字或者是包含數字1~29的公式或單兀格引用。valuel, value2, ...為1～29個參數。

例如輸入公式「=CHOOSE(2，Al，B2，C3)」，將返回單元格B2內的值。

LOOKUP函數（向量形式）

函數功能：在單行區域或單列區域（稱為「向量」）中查找值，然後返回第二個單行區域或單列區域中相同位置的值。

語法形式：

LOOKUP(lookup_ value,lookup_vector,result_ vector)

參數說明：lookup_value為函數LOOKUP在第一個向量中所要查找的值，lookup_vector為只包含一行或一列的區域，result_vector為只包含一行或一列的區域。

例如設置如圖所示的表格,在單元格C8中輸入公式「=LOOKUP("0ff」，B3:B6,C3:C6)」,即在第一個單元格區域「B3:B6」中查找到「日霜」，然後根據其所在行數返回第二個單元格區域「C3:C6」上同一行上的數值（即價格）。

VLOOKUP 函數

函數功能：搜索表格數組區域首列中滿足條件的元素，確定待檢索單元格在區域中的行序號，再進一步返回選定單元格的值。

語法形式：

VLOOKUP(lookup_value,table_array,co l_index_num,range_lookup)

參數說明：lookup_value為需要在表格數組中第一列中查找的數值，table_array為需要從中查找數據的數據表，col_index_num為table_array 中待返回的匹配值的序列號，range_lookup為邏輯值。

MATCH函數

函數功能：返回在指定方式下與指定數值匹配的數組中元素的相應位置。

語法形式：

MATCH(lookup_value,lookup_array,mat ch_type)

參數說明：lookup_value為需要在數據表中查找的數值;lookup_array為包含所要查找的數值的連續單元格區域，應為數組或數組引用； match_type 為數字_1、0 或 1, match_type指明如何在lookup_array中查找。

COLUMN 函數

函數功能：返回給定引用的列標。

語法形式：COLUMN (reference)

參數說明：reference為需要得到其列標的單元格或單元格區域。

例如輸入公式「=C0LUMN(B3)」，則返回B 列的列標「2」。

ROW函數

函數功能：返回引用的行號。

語法形式：ROW (reference)

參數說明：reference為需要得到其行號的單元格或單元格區域。

例如輸入公式「=ROW(B3)」，則返回第3行的行號「3」。

INDEX函數（引用形式）

函數INDEX有數組形式和弓I用形式兩種，這裡主要介紹引用形式。

函數功能：返回引用中指定單元格的引用。

語法形式：

INDEX(reference,row_numfcolumn_rmm, area_num)

參數說明：reference為對一個或多個單元格區域的引用，如果引用的區域只包含一行或一列，相應的參數row_num或column_num則為可選項，例如對於單行區域的引用可以使用函數 INDEX(reference,row_num) ； row_num 為引用中某行的行序號；column_num為引用中某列的列序號；area_num為選擇引用中的一個區域，返回該區域中row_num和column_num的交叉區域。例如參數 reference S(A1:B4，D1:E4，G1:H4)，裡面有 3個區域，那麼就需要參數area_num來指定選擇哪個區域，按照順序第一個區域序號為1，第二個為2，依次類推，如果省略area_num，則使用區域1。例如設置如下圖所示的表格，在單元格 B6中輸入了公式「=INDEX(A1:C3,2,1)」，返回值為「紅球」。由於只有一個區域，因此省略了參數 area_num 。

OFFSET 函數

函數功能：以指定的引用為參照系，通過給定的偏移量得到新的引用。

語法形式：

OFFSET(reference,rows,cols,height,w idth)

參數說明：reference是偏移量參照系的引用區域，rows是相對於偏移量參照系的左上角單元格上（下）偏移的行數，cols是相對於偏移量參照系的左上角單元格左（右）偏移的列數，height 和width分別是要返回的引用區域的行數和列數。

例如設置如圖所示的表格，在單元格A8中輸入公式「=SUM(OFFSET(B3:C5, —1,0，3，3))」，返回值為30，即數值15、7和8相加的合計數。

2.査找與引用函數的應用

上一小節介紹了查找與引用函數的基礎知識，本小節通過一個實例介紹查找與引用函數的具體應用。

⑴打開本小節的原始文件，切換到工作表「員工檔案」，選中單元格區域「B3:B12」，然後選擇【插入】 >【名稱】 >【定義】菜單項。

⑵隨即打開【定義名稱】對話框，在【在當前工作簿中的名稱】文本框中輸入「姓名」，然後依次單擊【添加（A）】I和【確定】按鈕。

⑶切換到工作表「員工查詢表」，選中單元格區域「C3:C10」，然後選擇【數據】 >【有效性】菜單項

⑷隨即打開【數據有效性】對話框，切換到【設置】選項卡，在【有效性條件】組合框中的【允許】下拉列表中選擇【序列】選項，在【來源】文本框中輸入「=姓名」，然後單擊【確定】按鈕。

⑸返回工作表區域，選中「選擇員工」列中的任意一個單元格，在其右側都會出現一個下箭頭按鈕，單擊此按鈕，從彈出的下拉列表中選擇所需的員工姓名即可，這裡選擇【紀藍】選項。

⑹顯示「銷售區域」。選中單元格D3，輸入公式「=INDEX(員工檔案!C3:C12,MATCH(C3,員工檔案!B3:B12,0))」，即先利用MATCH函數查找所選擇的員工在工作表「員工檔案」中所處的位置，然後返回另一個單元格區域「C3:C12」同一位置（行）上的值，按下【Enter】鍵即可顯示出計算結果。

⑺為了使輸入的公式更簡單，同樣可以定義一個「獎金錶」名稱。切換到工作表「獎金計算」，選中單元格區域「B3J12」，然後選擇【插入】 >【名稱】 >【定義】菜單項。

⑻隨即打開【定義名稱】對話框，在【在當前工作簿中的名稱】文本框中輸入「獎金錶」，然後依次單擊【添加（A）和【確定】按鈕。

⑼切換到工作表「員工查詢表」中，選中單元格 E3,輸入公式「=VLOOKUP（C3，獎金錶，8，0）「，即根據」姓名「在「獎金錶」中查找並返回「獎金」數。按下【Enter】鍵即可顯示出計算結果。

⑽實現自動編號。選中單元格B3，輸入公式「=(R0W()-2)」，按下【Enter】鍵即可顯示出計算結果。

⑾使用自動填充功能將單元格B3中的公式填充至單元格B10，結果如圖所示。

Excel聯盟溫馨提示：

在不選擇員工的情況下，如果將單元格D3或E3中的公式向下填充複製時會顯示錯誤值"#N/A"。如果不想顯示錯誤值，可以修改一下公式，例如將單元格E3中的公式修改為"=IF(ISERROR (VLOOKUP(C3, 獎金錶，8，0))，""，VLOOKUP(C3,獎金錶，8，0))」，即在存在錯誤的情況下返回空值」"。

上一篇：日期與時間函數——Excel函數的應用下一篇：財務函數——Excel函數的應用相關推薦其他函數——Excel函數的應財務函數——Excel函數的應日期與時間函數——Excel函文本函數——Excel函數的應統計函數——Excel函數的應邏輯函數——Excel 函數的應本文轉載於Excel聯盟：http://www.excellm.com/-原文鏈接：http://www.excellm.com/excel/2003/1133.html