2016年7月10日 星期日

Excel-反推數值所在的儲存格(ADDRESS,ROW,COLUMN,陣列公式)

在 Excel 的工作表中,有時會用到要在資料清單中反推數值所在的儲存格。如下圖,要如何找出儲存格L1的內容對應儲存格A1:J10中相符者的儲存格位址?
Excel-在資料清單中反推數值所在的儲存格(ADDRESS,ROW,COLUMN,陣列公式)

【公式設計與解析】
假設儲存格A1:J10的內容具唯一性,不會重覆。
儲存格L4:{=ADDRESS(SUM((A1:J10=L1)*ROW(1:10)),SUM((A1:J10=L1)*
COLUMN(A:J)),1)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動輸入「{}」。

(1) SUM((A1:J10=L1)*ROW(1:10))
在陣列公式中判斷儲存格A1:J10中和儲存格L1內容相同者,傳回 TRUE/FALSE 陣列。
其中 100 個傳回值中只有一個 TRUE,有 99 個 FALSE
在陣列公式中 ROW(1:10)={1,2,3,4,5,6,7,8,9,10}。
以上兩式相乘時,TRUE/FALSE 會變為 1/0,其結果會有一個不是0,而有99個0。
最後,再以 SUM 加總這 100 個數字,結果即為符合者的『列號』。

(2) SUM((A1:J10=L1)*COLUMN(A:J))
在陣列公式中判斷儲存格A1:J10中和儲存格L1內容相同者,傳回 TRUE/FALSE 陣列。
其中 100 個傳回值中只有一個 TRUE,有 99 個 FALSE
在陣列公式中 COLUMN(A:J)={1,2,3,4,5,6,7,8,9,10}。
以上兩式相乘時,TRUE/FALSE 會變為 1/0,其結果會有一個不是0,而有99個0。
最後,再以 SUM 加總這 100 個數字,結果即為符合者的『欄號』。

將第(1)式和第(2)式代入 ADDRESS 函數即可顯示儲存格位址。

沒有留言:

張貼留言

檢視其他文章

好康東東