延續前一篇文章:Excel-郵遞區號查詢(VLOOKUP,OFFSET,SUMPRODUCT)

如下圖,若在儲存格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

arrow
arrow
    全站熱搜

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