2017年3月23日 星期四

Excel-找出一群數列之最大值/最小值的儲存格位址(SUMPRODUCT)

有人想要找出在 Excel 工作表中一群數列之最大值和最小值的儲存格位址,該如何處理?
在以下的圖表中,假設其中的數字均沒有重覆,要來出最大值和最小值的儲存格位址。
Excel-找出一群數列之最大值/最小值的儲存格位址(SUMPRODUCT)

【公式設計與解析】
1. 找出最大值的儲存格位置
儲存格H2:=ADDRESS(SUMPRODUCT((A1:F17=MAX(A1:F17))*ROW(A1:F17)),
SUMPRODUCT((A1:F17=MAX(A1:F17))*COLUMN(A1:F17)))
(1) SUMPRODUCT((A1:F17=MAX(A1:F17))*ROW(A1:F17))
找出最大值的列號。ROW 函數會傳回儲存格的列號。
(2) SUMPRODUCT((A1:F17=MAX(A1:F17))*COLUMN(A1:F17))
找出最大值的欄數。COLUMN 函數會傳回儲存格的欄數。
(3) ADDRESS(第(1)式,第(2)式))
將第(1)式和第(2)式代入 ADDRESS 函數即會傳回位址。

2. 找出最小值的儲存格位置
儲存格H5:=ADDRESS(SUMPRODUCT((A4:F20=MIN(A4:F20))*ROW(A4:F20)),
SUMPRODUCT((A4:F20=MIN(A4:F20))*COLUMN(A4:F20)))
原理同1.,公式的差異在於最大值使用 MAX 函數,最小值使用 MIN 函數。

沒有留言:

張貼留言

檢視其他文章

好康東東