在 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)
這是利用水平和垂直的交叉點來找到資料。
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)
相關函數說明，請參閱微軟網站說明：
INDEX：http://office.microsoft.com/zh-tw/excel-help/HP010342608.aspx
|
INDEX：傳回表格或範圍內的某個值或值的參照。 |
|
語法：INDEX(array, row_num, [column_num]) array：儲存格範圍或陣列常數。 row_num：選取陣列中傳回值的列。 column_num：選取陣列中傳回值的欄。 |
MATCH：http://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 引數內的值必須以遞減次序排序。 |
OFFSET：http://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 必須是正數。 |
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_num：table_array 引數中必須傳回相符值的欄號。 range_lookup：這是一個邏輯值，用以指定VLOOKUP應該要尋找完全符合還是大約符合的值。 |
留言列表