2013年2月1日 星期五

Excel-查表練習(MATCH, OFFSET)

網友問到:在 Excel 中有一個如下圖中的資料表,如果想要根據編號來找出組別、位置和結果,該如何處理?

【準備工作】

選取儲存格A1:B18,按一下 Ctrl+Shift+F3 鍵,定義名稱:組別、名稱。

【輸入公式】

(1) 找出結果

儲存格B22:=INDEX(OFFSET($A$2,MATCH(A22,組別,0)-1,1,COUNTIF(組別,A22)),MATCH($B$20,OFFSET($A$2,MATCH(A22,組別,0)-1,$B$20+1,COUNTIF(組別,A22)),1),)

COUNTIF(組別,A22):計算一個組別的儲存格個數。

MATCH(A22,組別,0)-1:找出要搜尋各個組別的第一個之儲存格位置。

OFFSET($A$2,MATCH(A22,組別,0)-1,$B$20+1,COUNTIF(組別,A22)):求得各組的儲存格範圍。

再利用 INDEX 函數以查表方式求得結果中的儲存格內容。

 

(2) 找出位置

儲存格C22:=ADDRESS(MATCH(C22,結果,0)+1,$B$20+2,4)

透過已求得的內容,使用 ADDRESS 函數轉換為儲存格位址的欄名列號。

複製儲存格B22:C22,貼至儲存格B22:C24。

 

【補充資料】

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

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

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

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

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

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

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

 

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

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 引數內的值必須以遞減次序排序。

 

沒有留言:

張貼留言

檢視其他文章

好康東東