2016年8月29日 星期一

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

網友問到如何在 Excel 中查詢郵遞區號的問題。首先下載郵局提供的郵遞區號對照表:
稍加整理後,即可用於查詢。(如下圖的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)
大功告成!

沒有留言:

張貼留言

好康東東