學不完．教不停．用不盡

本篇文章是前二篇文章的延伸：

Excel-查詢指定字串所在的儲存格位址(MATCH,ADDRESS,IFERROR)

Excel-查詢指定字串所在的儲存格位址(MATCH,ADDRESS,FIND,OFFSET)

如下圖，如何在 Excel 的工作表中，找出分區各自的最大值所在的儲存格位址？

在下圖中，可以指定每一個分區有幾列，並且於I欄中標示每列的最大值（每列有７個儲存格數值）。當找出每一區的最大值（儲存格L3:L15）後，再找出其儲存格位址（儲存格M4:O15）。

【公式設計與解析】

1. 產生序號

儲存格K4：=((ROW(1:1)-1)*$L$1+1)&"-"&ROW(1:1)*$L$1

複製儲存格K4，貼至儲存格K4:K15。

ROW(1:1)向下複製公式時，ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。

本例中，當儲存格L6為 6 時，會依序產生1-6、7-12、13-18、…。

2. 找出每區最大值

儲存格L4：=MAX(OFFSET($B$2,(ROW(1:1)-1)*$L$1,0,$L$1,7))

(1) (ROW(1:1)-1)*$L$1

當儲存格L3為3時，公式向下複製時會依序產生 0, 3, 6, 9, …。（作為 OFFSET 函數參數中的相對起始列）

(2) OFFSET($B$2,(ROW(1:1)-1)*$L$1,0,$L$1,7)

根據儲存格L1的數值，透過 OFFSET 函數產生每一區的儲存格範圍。

(3) 再由 MAX 函數取得該範圍中的最大值。（題目有預設每一區中的數值不重覆）

3. 找出最大值的列號

儲存格M4：=MATCH(L4,OFFSET($I$2,(ROW(1:1)-1)*$L$1,0,$L$1,1),0)+$L$1*
(ROW(1:1)-1)+1

(1) OFFSET($I$2,(ROW(1:1)-1)*$L$1,0,$L$1,1)

利用 OFFSET 函數找到每一區的儲存格範圍。（例如：序號1-3區為儲存格B2:H4、序號4-6區為儲存格B5:H7）

(2) MATCH(L4,第(1)式,0)

利用 MATCH 函數找到最大值位於第(1)式傳回的儲存格範圍中的第幾個。

(3) 第(2)式+$L$1*(ROW(1:1)-1)+1

找出各區最大值在I欄中的列號。（本例在第 1 區中，最大值是在第 2 列。）

4. 找出最大值的欄號

儲存格N4：=MATCH(L4,OFFSET($B$1,M4-1,0,1,7),0)+1

(1) OFFSET($B$1,M4-1,0,1,7)

根據儲存格M4所提供的最大值在I欄中的列號，透過 OFFSET 函數取得該列的儲存格範圍。（在下圖的範例中第 1 區傳回儲存格B4:H4）

(2) MATCH(L4,OFFSET($B$1,M4-1,0,1,7),0)+1

利用 MATCH 函數取得最大值的位置是 4，所以欄號是4+1=5。（因為資料由B欄開始）

5. 產生各區最大值儲存格位址

儲存格O4：=ADDRESS(M4,N4,4)

使用 ADDRESS 函數將第(3)式和第(4)式取得的列和欄參數代入，即可得各區最大值的位址。

只要改變佔儲存格L1的數值，即可改變分區的列數。

【延伸學習】

如果你想要像本例中可以自動標示每一區中的最大值，可以在設定格化的條件中做以下的設定。

1. 選取儲存格B2:H298，設定格式化的條件。

2. 設定格式化的條件

規則類型 :使用公式來決定要格式哪些儲存格

條件公式：=B2=MAX(OFFSET($B$2,(INT((ROW(1:1)-1)/$L$1))*$L$1,0,$L$1,7))

格式：紅色前景色彩