網友問到如何在 Excel 中查詢郵遞區號的問題。首先下載郵局提供的郵遞區號對照表:

http://www.post.gov.tw/post/internet/Download/default.jsp?ID=22

稍加整理後,即可用於查詢。(如下圖的A,B,C欄)

Excel-郵遞區號查詢(VLOOKUP,OFFSET,SUMPRODUCT)

 

【公式設計與解析】

1. 由郵遞區號查詢:縣市和區

儲存格F3:=VLOOKUP(E3,A3:C370,2,FALSE)

儲存格G3:=VLOOKUP(E3,A3:C370,3,FALSE)

當在儲存格E3輸入一個郵遞區號時,即可對照顯示縣市和區。

 

2. 由縣市和區查詢:郵遞區號

這個範例,要使用下拉式清單來選取縣市:

Excel-郵遞區號查詢(VLOOKUP,OFFSET,SUMPRODUCT)

再根據縣市,在另一個下拉式清單中選取區:

Excel-郵遞區號查詢(VLOOKUP,OFFSET,SUMPRODUCT)

參考以下步驟:

1. 選取儲存格A2:C63,按 Ctrl+Shfit+F3 鍵,勾選「頂端列」,定義名稱:

郵遞區號、縣市、區。

2. 在儲存格I1:I24中置入縣市的清單。

3. 選取儲存格I1:I24,定義名稱:縣市名。

4. 設定儲存格E7的資料驗證。儲存格內允許:清單;來源:=縣市名。

image

5. 輸入公式,儲存格K2:

{=OFFSET($C$3,SMALL(IF(縣市=$E$7,ROW(區),999)-3,ROW(1:1)),0)}

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

複製儲存格K2,貼至儲存格K2:K34。

6. 將儲存格K2:K34定義名稱:區名。

7. 設定儲存格F7的資料驗證。

定義名稱:完整區名。

其參照公式:=OFFSET($K$2,0,0,COUNTA(區名)-COUNT(區名),1)

image

8. 設定儲存格E7的資料驗證:

儲存格內允許:清單;來源:=完整區名。

image

9. 輸入公式,儲存格G7:

=OFFSET(A3,SUMPRODUCT((縣市=E7)*(區=F7)*ROW(郵遞區號))-3,0)

大功告成!

創作者介紹

學不完.教不停.用不盡

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