2017年8月6日 星期日

Excel-查詢最大值的位址(ADDRESS,MATCH,INDIRECT)

在 Excel 中,如何將同一欄的資料中找出最大值/最小值所在的儲存格位址,或是將同一列的資料中找出最大值/最小值儲存格位址?
《資料在一欄》
Excel-查詢最大值的位址(ADDRESS,MATCH,INDIRECT)
【公式設計與解析】
選取B1:B21,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:數值。
1. 最大值位置
儲存格F2:=ADDRESS(MATCH(MAX(數值),數值,0)+1,1)
(1) MAX(數值)
利用 MAX 函數找出數值陣列的最大值。
(2) MATCH(MAX(數值),數值,0)
利用 MATCH 函數找出最大值的位置(傳回一個數值)。
(3) ADDRESS(MATCH(MAX(數值),數值,0)+1,1)
利用 ADDRESS 函數根據第(2)式傳回的位置找出其儲存格位址。
2. 最大值內容
儲存格E2:=INDIRECT(F2)
利用 INDIRECT 函數將儲存格F2的內容轉換為儲存格位置並取得內容。
3. 最小值位置
儲存格F5:=ADDRESS(MATCH(MIN(數值),數值,0)+1,1)
4. 最小值內容
儲存格E2:=INDIRECT(F5)

《資料在一列》
Excel-查詢最大值的位址(ADDRESS,MATCH,INDIRECT)
【公式設計與解析】
選取B2:N2,按 Ctrl+Shift+F3 鍵,勾選「最左欄」,定義名稱:數值。
儲存格C2:=ADDRESS(1,MATCH(MAX(數值),數值,0)+1)
儲存格B2:=INDIRECT(C5)
儲存格G2:=ADDRESS(1,MATCH(MIN(數值),數值,0)+1)
儲存格F2:=INDIRECT(G5)
原理同《資料在一欄》,注意 ADDRESS 函數中的參數差異

沒有留言:

張貼留言

好康東東