2012年2月1日 星期三

Excel-查詢數列最大值的位置

在 Excel 的一個資料表中,如果想要查詢最大值、對應的編號及儲存格位址,該如何處理?(參考下圖)

因為資料會不斷的輸入,所以將B欄定義一個名稱,方便程式說明:

選取B欄,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:數值。

(1) 求最大值

儲存格D2:=MAX(數值)

(2) 求最大值所在的位址

儲存格F2:=ADDRESS(MATCH(MAX(數值),數值,0)+1,1,4)

MATCH(MAX(數值),數值,0)+1:求得最大值(第一個)在B欄中的位置。

ADDRESS(MATCH(MAX(數值),數值,0)+1,1,4):利用 ADDRESS 函數,代入欄和列的號碼,對應一個儲存格位址,參數4代表以相對位址表示。

(3) 求最大值對應的編號

儲存格E2:=INDIRECT(F2,TRUE)

利用 INDIRECT 函數將儲存格位址轉換為儲存格內容。

 

【補充資料】

詳細函數說明請參閱微軟網站:

INDIRECThttp://office.microsoft.com/zh-tw/excel-help/HP010342609.aspx

INDIRECT:傳回文字串所指定的參照位址。

語法:INDIRECT(ref_text,[a1])

ref_text:單一儲存格的參照位址,其中包含A1欄名列號表示法、R1C1欄名列號表示法、定義為參照位址的名稱,或定義為字串的儲存格參照位址。

a1:指定ref_text儲存格中所包含參照位址類型的邏輯值。

 

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

 

ADDRESShttp://office.microsoft.com/zh-tw/excel-help/HP010342163.aspx

在已知指定列和欄號下,取得工作表中儲存格的位址。

語法:ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

row_num:指定要用在儲存格參照中之列號的數值。

column_num:指定要用在儲存格參照中之欄號的數值。

abs_num:可省略。指定要傳回之參照類型的數值。傳回此參照類型如下:

1或省略:絕對儲存格參照;2:列:絕對;欄:相對;3:列:相對;欄:絕對;4:相對參照

 

沒有留言:

張貼留言

檢視其他文章

好康東東