廣告贊助

網友根據前一篇文章: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。

文章標籤
創作者介紹

學不完.教不停.用不盡

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()