2016年8月30日 星期二

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

如下圖,若在儲存格E3中選取某一個縣市(例如:新北市),如何能自動列出該縣市的各區名稱?本篇要改良前一篇的公式。
Excel-郵遞區號查詢(VLOOKUP,OFFSET,SUMPRODUCT)2
選取儲存格A2:C63,按 Ctrl+Shfit+F3 鍵,勾選「頂端列」,定義名稱:
郵遞區號、縣市、區。
再來,要設定一個名稱:完整區名。並設定其參照到:
=OFFSET($C$2,MATCH($E$3,縣市,0),0,SUMPRODUCT(1*(縣市=$E$3)),1)
Excel-郵遞區號查詢(VLOOKUP,OFFSET,SUMPRODUCT)2
(1) SUMPRODUCT(1*(縣市=$E$3))
計算在縣市資料中和儲存格E3相同者的數量。
(2) MATCH($E$3,縣市,0)
找出儲存格E3在縣市資料中位於第幾個。
(3) OFFSET($C$2,第(2)式,0,第(1)式,1)
利用第(1)式和第(2)式找出,某一個縣市的各區儲存格範圍。
最後,在儲存格F3中利用資料驗證設定成下拉式清單:
儲存格內允許:清單
來源:=完整區名
Excel-郵遞區號查詢(VLOOKUP,OFFSET,SUMPRODUCT)2

沒有留言:

張貼留言

好康東東