2014年11月12日 星期三

Excel-由地名查詢所在縣市(OFFSET,COLUMN,陣列)

在 Excel 中的工作表,如果你已取得各縣市的區鄉鎮名稱,可以試著用來以地名查詢所在的縣市(參考下圖),這是個生活化的應用,我們來看看公式如何處理?

假設已經先將每個縣市中的區鄉鎮鎮名稱配置如上圖,本例的資料的儲存格範圍是D1:J31,設定以下公式:

儲存格B2:{=OFFSET($A$1,,MAX((LEFT($D$2:J$31,2)=A2)*COLUMN($D$1:$J$1))-1)}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,公式會自動產生「{}」。

LEFT($D$2:J$31,2)=A2:將儲存格範圍中的內容取左邊數來二個字,並且和儲存格A2比對是否相符,結果傳回 TRUE/FALSE 的陣列。

(LEFT($D$2:J$31,2)=A2)*COLUMN($D$1:$J$1):COLUMN($D$1:$J$1)要陣列公式中代表 {4, 5, 6, 7, 8, 9, 10} 陣列,把它和上式相乘,計算過程中 TRUE/FALSE 的陣列會轉換為 1/0 陣列。

MAX((LEFT($D$2:J$31,2)=A2)*COLUMN($D$1:$J$1)):將上式的陣列結果取其中的最大值,這個值即是第幾欄的意思,例如傳回 8,即代表是H欄。

最後將上式代入 OFFSET 函數,取得儲存格D1:J1中的其中一個內容,此為某個縣市的名稱,即為所求。

如果要避免輸入的地名找不到時會顯示錯誤訊息,則可以利用 IFERROR 函數稍加修改公式,讓錯誤訊息顯示為:查無此地。

儲存格B2:{=IFERROR(OFFSET($A$1,,MAX((LEFT($D$2:J$31,2)=A10)*COLUMN
($D$9:$J$9))-1),"查無此地")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,公式會自動產生「{}」。

沒有留言:

張貼留言

檢視其他文章

好康東東