2011年6月15日 星期三

Excel-查表練習(INDEX,MATCH,OFFSET)

在 Excel 中,針對各班班級幹部的資料表(如下圖),來練習INDEX、MATCH、OFFSET函數。

如果要產生下圖的查詢結果,該如何處理?其中班級名稱和幹部的名稱要用下拉式清單來挑選,而姓名則是以公式來查詢。

(1) 定義以下三個名稱

班級:儲存格A2:A29;幹部:儲存格B1:H1;資料:儲存格A1:H29。

(2) 設定儲存格的資料驗證

設定J欄中的儲存格之資料驗證為:「儲存格允許:清單;來源:=班級」。

設定K欄中的儲存格之資料驗證為:「儲存格允許:清單;來源:=幹部」。

(3) 查表找出姓名,試著練習以下三種方式來查表。

<A> 儲存格L2:=INDEX(資料,MATCH(J2,班級,0)+1,MATCH(K2,幹部,0)+1)

這是利用水平和垂直的交叉點來找到資料。

image

Y = MATCH(J2,班級,0)+1:找到指定的班級名稱在第幾列(數值)。

X = MATCH(K2,幹部,0)+1:找到指定的幹部名稱在第幾欄(數值)。

儲存格L2:=INDEX(資料,Y, X),Y和X的交會點即是所要的資料(Z)。

<B> 儲存格L2:=OFFSET($A$1,MATCH(J2,班級,0),MATCH(K2,幹部,0))

列偏移量 = MATCH(J2,班級,0):找到指定的班級名稱在第幾列(數值)。

欄偏移量 = MATCH(K2,幹部,0):找到指定的幹部名稱在第幾欄(數值)。

儲存格L2:=OFFSET($A$1,列偏移量,欄偏移量)

<C> 儲存格L2:=VLOOKUP(J2,資料,MATCH(K2,幹部,0)+1,0)

X = MATCH(K2,幹部,0)+1:找到指定的幹部名稱在第幾欄(數值)。

儲存格L2:=VLOOKUP(J2,資料,X,0)

 

相關函數說明,請參閱微軟網站說明:

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

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

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

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

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

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

 

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 必須是正數。

 

VLOOKUPhttp://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應該要尋找完全符合還是大約符合的值。

沒有留言:

張貼留言

好康東東