學不完．教不停．用不盡

在 Excel 的工作表中，有時會用到要在資料清單中反推數值所在的儲存格。如下圖，要如何找出儲存格L1的內容對應儲存格A1:J10中相符者的儲存格位址？

 

【公式設計與解析】

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