在 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 函數即可顯示儲存格位址。

arrow
arrow
    全站熱搜

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