在 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 函數將儲存格位址轉換為儲存格內容。
【補充資料】
詳細函數說明請參閱微軟網站：
INDIRECT：http://office.microsoft.com/zh-tw/excel-help/HP010342609.aspx
|
INDIRECT：傳回文字串所指定的參照位址。 |
|
語法：INDIRECT(ref_text,[a1]) ref_text：單一儲存格的參照位址，其中包含A1欄名列號表示法、R1C1欄名列號表示法、定義為參照位址的名稱，或定義為字串的儲存格參照位址。 a1:指定ref_text儲存格中所包含參照位址類型的邏輯值。 |
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 引數內的值必須以遞減次序排序。 |
ADDRESS：http://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：相對參照 |
*****
您好，我照上面的函數操作遇到兩個問題想請教您： 1.MATCH的數值範圍，因為我是抓"不同工作表"的範圍值，因此公式無法成立，有其他的函數可以替代嗎? 2.INDIRECT是不是無法轉換中文呢? 因為我的儲存格是中文值，INDIRECT卻無法表現 煩請不吝指導，非常謝謝您
(1) 在MATCH函數中使用不同工作表是可以的，例如： 在工作表1中的儲存格公式：=MATCH(A1,工作表2!A1:A9,0) (2) INDIRECT 函數是為了將文字轉換為真實的位址，例如儲存格B1：A1:B2，而公式INDIRECT(B1)，即轉換為儲存格A1:B2。如果是儲存格中是中文字，應該是一個已定義的『名稱』，你要先檢查是否已定義好名稱。
您好，首先非常感謝您的解答，還是有些疑問想請教： 1.MATCH之部分是指，在填入lookup_value或lookup_array時，它的資料包含多個工作表。 例如：我輸入１班與２班的考試成績，分別輸入在工作表１(1班)跟工作表２(2班)，但比方我要抓最大數字的成績，指的是１班與２班～兩班全部之中最大的值，但有查過MATCH是只能抓一個工作表的，這樣該如何解決呢？ 2.我大概懂您的意思，但不知道該如何實作？就以剛剛上面提到的例子，我要抓一個最大的數字成績，用ADDRESS找到儲存格後，再用INDIRECT顯示那位同學的名字，請問該如何實作？(解決中文無法顯示的問題) 非常謝謝您的指導！ 順道一提，您的部落格非常棒，裡面有很多非常實用的excel教學，謝謝您分享許多這麼棒的教學！
(1) MATCH函數只能用在同一欄／列，否則會出現#N/A錯誤。如果你的原始資料分散在二個工作表，或許可以使用一個輔助工作表將二個工作表內容整合在一起。 (2) 舉一個實例來表現INDIRECT的做法： MATCH(A5,A1:A16,0)：利用A5找到在A1:A16中的第幾個(例如傳回：5) ADDRESS(MATCH(A5,A1:A16,0),1)：傳回$A$5 INDIRECT(ADDRESS(MATCH(A5,A1:A16,0),1))：傳回A5中的內容
版主您好 版主您的例子是資料往下延伸的,如是資料往右延伸的,我設欄位名稱如何設,我設的結果 求最大值出現#REF, 即無法執行,且尋找最大值欄位是不連續的,如何出現是那個欄位是最大值呢?謝謝
請參考：http://isvincent.pixnet.net/blog/post/36728424
版主您好 我就是看您的這個例子做不出來所提出之疑問,您的例子資料是往下編號,我的例子的資料是從a1是品號,b1是一月,c1 是二月,...m1是十二月,o1是今年平均,p1是去年平均,要做的是b1至m1與o1及p1比誰最高,列出數字及欄位名稱,可是因版主的例子是從b欄尋找,我依版主教的方法找不出來,從定義名稱就有問題了,才想再請教版主,謝謝
這篇 http://isvincent.pixnet.net/blog/post/47445732 的後半段，就是以列來找尋最大值和最小值。
*****
您好，我在設定更表去統計人數時，遇到設定公式的問題想請教， 例如 A1是設定年份，A2是設定月份 B1至B42是日一二三四五六日的編排(固定不變) C1至C42會因應星期，而對應不同日子。 (1月份1號，可能在星期一，而2月份的1號可能在星期二) D1至D42就是上班人數統計。 E1是統計整個月份上班的人數，每次也需要手數去更改範圍。(這只是例子，因為涉及不同更期的人數及不同部門，現在想簡化的詢問) 問題是︰日期會變動，但統計時我想問能因應日子變動，而公式也跟著變動？ 例如A1我填2018，而A2我填1，即2018年1月。 那1/1將會出現在C1， 而如果A2我填2，即2018年2月 那1/2將會出現在C4。 那E1的公式能否由=SUM(C1:C32) 變成=SUM(C4:C32) 又或是2018年4月， 公式會變成=SUM(C7:C37)? 我試過以公式ADDRESS去找到每月不同1號的位置是在哪兒，可惜的是EXCEL看不懂。所以想請問有其他方法。會因應日期而改變公式裡的範圍，謝謝幫忙。
您好： 想請問，如果我想要篩出不同範圍，譬如說A4~A10的最大值，A8~A19的最大值，同時A1~A20中亦有相同的數值，導致會篩到不是我範圍內要的最大最小值，請問有什麼方法可以改公式嗎? 謝謝您了!
您好: 想請問以下問題, 舉例: A B C D E F G 進貨價 N/A 2 3 4 請問F欄: 怎麼篩選出 (A) (D) (E) 的最小值 (要避開N/A值, 要篩選的欄位並非在一個區域而是分別不同欄位) 請問G欄: 要怎麼呼應F欄帶出了的最小值去對應供應商名稱 比如正確答案為 F=2, G=B 感謝
你好, 我有個excel表如下: 班別 姓名 A B C D E F 1A 陳明 6 8 1 2 5 7 1B 張權 3 7 9 1 4 6 我想找出每個學生 1。 最高分數的項目 2。第二高分數的項目 3。第三高分數的項目 即 顯示結果 係: 1。 最高分數的項目(陳明: 項目B ; 陳權: 項目C) 2。第二高分數的項目(陳明: 項目F ; 陳權: 項目B) 3。第三高分數的項目(陳明: 項目A ; 陳權: 項目F) 請問可以有公式可以計算到以上結果嗎？ 謝謝
您好 若數列中有相同大小的最大值，要如何找出兩者的相對位置?謝謝