2014年7月1日 星期二

Excel-通訊錄查詢(INDEX,MATCH,OFFSET,VLOOKUP)

有網友問到:如果想要在一個 Excel 的通訊錄清單中,想要藉由下拉式清單來查詢名單中的資料,該如何處理呢?

大多數網友在使用 Excel 來查詢資料時,都會用到幾個常用的查詢函數,藉由這個例子再來練習相關的函數。

參閱下圖,學號是一個唯一值,就用學號來做為查詢的關鍵字。

參考以下步驟:

1. 選取儲存格A1:A25,按 Ctrl+Shift+F3 鍵,選取「頂端列」,定義名稱:學號。

2. 選取儲存格H2,選取[資料/資料工具/資料驗證]指令,設定:

儲存格內允許:清單:來源:「=學號」。

即設定儲存格中的資料清單在「學號」 的儲存格範圍。

當你選取儲存格H2的下拉式清單時,即可選取一個學號。

3. 在儲存格I2中輸入公式:(以下提供三種公式來練習)

(1) 使用 VLOOKUP 函數

儲存格I2:=VLOOKUP($H$2,$A$2:$F$25,COLUMN(B:B))

複製儲存格I2,貼至儲存格I2:M2

COLUMN(B:B)=2,向右複製會產生COLUMN(C:C)=3、COLUMN(D:D)=4、…。

(在此之2、3、4、…,是指第2欄、第3欄、第4欄、…)

相關函數詳細說明,請參考微軟網站:

VLOOKUP:http://office.microsoft.com/zh-tw/excel-help/HP010343011.aspx

VLOOKUP:用來搜尋儲存格範圍的第一欄,然後從範圍同一列的任何儲存格傳回一個值。

語法:VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value:在表格或範圍的第一欄中搜尋的值。

table_array:包含資料的儲存格範圍。可以使用範圍的參照,也可以使用範圍名稱。

col_index_numtable_array 引數中必須傳回相符值的欄號。

range_lookup:這是一個邏輯值,用以指定VLOOKUP應該要尋找完全符合還是大約符合的值。


 

(2) 使用 OFFSETMATCH 函數

儲存格I2:=OFFSET($A$1,MATCH($H$2,學號,0),COLUMN(A:A))

複製儲存格I2,貼至儲存格I2:M2

MATCH($H$2,學號,0):先使用 MATCH 函數,找到儲存格H2位於「學號」儲存格範圍中的第幾列。參數「0」為設定查詢學號必須完全一致者

再將這個結果代入 OFFSET 函數中,找出相對位置。

其中COLUMN(A:A)=1,向右複製會產生COLUMN(B:B)=2、COLUMN(C:C)=3、…。

(在此之2、3、4、…,是指位移2欄、位移3欄、位移4欄、…)

MATCHhttp://office.microsoft.com/zh-tw/excel-help/HP010342679.aspx

MATCH:搜尋某儲存格範圍內的指定項目,然後再傳回該項目在範圍內的相對位置。

語法:MATCH(lookup_value, lookup_array, [match_type])

lookup_value:在 lookup_array 中尋找比對的值。

lookup_array:要搜尋的儲存格範圍。

match_type:這是一個數字,其值有三種可能:(預設值為 1)

1 或省略:找到等於或僅次於 lookup_value 的值。lookup_array 引數內的值必須以遞增次序排列。

0:找第一個完全等於 lookup_value 的比較值。lookup_array 引數內的值可以依任意次序排列。

-1:找到等於或大於 lookup_value 的最小值。lookup_array 引數內的值必須以遞減次序排序。

 

OFFSEThttp://office.microsoft.com/zh-tw/excel-help/HP010342739.aspx

 

OFFSET 函數:傳回根據所指定的儲存格位址、列距及欄距而算出的參照位址。

語法:OFFSET(reference, rows, cols, [height], [width])

Reference:用以計算位移的起始參照位址。

Rows:左上角儲存格要往上或往下參照的列數。Rows可以是正數(表示在起始參照位址下方)或負數(表示在起始參照位址上方)

Cols:左上角儲存格要往左或往右參照的欄數。Cols 可以是正數(表示在起始參照位址右方)或負數(表示在起始參照位址左方)

Height:所傳回參照位址的高度 (以列數為單位)Height 必須是正數。

Width:所傳回參照位址的寬度 (以欄數為單位)Width 必須是正數。

 

(3) 使用 INDEXMATCH 函數

儲存格I2:=INDEX($A$2:$F$25,MATCH($H$2,學號,0),COLUMN(B:B))

仿 (2) 的使用觀念,藉由 INDEX 函數在儲存格A2:F25範圍中,找出某一列的使用資料。

 

 

INDEXhttp://office.microsoft.com/zh-tw/excel-help/HP010342608.aspx

 

INDEX:傳回表格或範圍內的某個值或值的參照。

語法:INDEX(array, row_num, [column_num])

Array:儲存格範圍或陣列常數。

Row_num:選取陣列中傳回值的列。

Column_num:選取陣列中傳回值的欄。

沒有留言:

張貼留言

檢視其他文章

好康東東