2017年3月23日 星期四

Excel-找出一群數列之第1,23,大值的儲存格位址(SUMPRODUCT,LARGE)

網友根據前一篇文章:Excel-找出一群數列之最大值/最小值的儲存格位址(SUMPRODUCT),延伸問到如何找出一群數列之第1,23,大值的儲存格位址?
參考下圖,假設其中的數字均沒有重覆,使用三個不同色彩分別標示第 1,2,3 大的值。
Excel-找出一群數列之第1,23,大值的儲存格位址(SUMPRODUCT,LARGE)

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

2. 找出第 2 大值的儲存格位置
儲存格H5:=ADDRESS(SUMPRODUCT((A1:F17=LARGE(A1:F17,2))*ROW(A1:F17)),SUMPRODUCT((A1:F17=LARGE(A1:F17,2))*COLUMN(A1:F17)))
將 LARGE 函數的參數改成 2。

3. 找出第 3 大值的儲存格位置
儲存格H8:=ADDRESS(SUMPRODUCT((A1:F17=LARGE(A1:F17,3))*ROW(A1:F17)),SUMPRODUCT((A1:F17=LARGE(A1:F17,3))*COLUMN(A1:F17)))
將 LARGE 函數的參數改成 3。

沒有留言:

張貼留言

檢視其他文章

好康東東