Excel 郵便番号データベース&入力規制リストでプルダウンメニューから住所入力
日本郵便の郵便番号データと入力規制リストを組み合わせて、プルダウンメニューから住所入力ができるようにします。(サンプルはこちらからダウンロード)

作り方
1. 日本郵便のサイトから郵便番号データを取得、任意のシートに全データを貼り付ける(「郵便番号」シート)
2. 「郵便番号」シートに都道府県の列の前に一列増やして、都道府県、市区町村、それ以下の住所の三つを連結したデータを入れる。(下図参照)

3. シートを追加(「マスター」シート)し、そこに都道府県のリストを入れ、「都道府県」と名前を定義しておく。
4. 「郵便番号」シートから「都道府県」の列と「市区町村」の列をコピーし、「マスター」シートに貼り付ける。
5. 「市区町村」の列にオートフィルターをかけて、重複データを削除し、別の場所へ貼り付ける。
6. 「市区町村」の列に名前を定義する、名前はその市区町村が所属する都道府県名。つまり48都道府県の数だけ行う。
上記3~6の手順で下図の状態にする。

手順6(都道府県名を所定の範囲に定義する)は手動でやるのは手間なので、下記のようなマクロを使います。
Sub 名前を定義() Dim i As Long Dim lastRow As Long Dim currentCity As String Dim prevCity As String: prevCity = "北海道" Dim firstRow As Long: firstRow = 2 With ActiveSheet For i = 2 To 1897
'現在の行の都道府県 currentCity = .Range("C" & i).value
'現在の行の都道府県が前の行の都道府県と異なる場合 If currentCity <> prevCity Then lastRow = i - 1' 前の行を最終行として設定
'市区町村の該当範囲に都道府県名を定義する ActiveWorkbook.Names.Add _ Name:=prevCity, RefersTo:=.Range("D" & firstRow & ":" & "D" & lastRow) firstRow = i End If prevCity = currentCity Next End With End Sub
7. 「郵便番号」シートに戻り、地名の列に名前を定義します。名前は都道府県と市区町村を連結したものです。(例)北海道札幌市中央区
これも手間な作業なので、上のマクロに少し手を加えて実行します。
Sub 名前を定義() Dim i As Long Dim lastRow As Long Dim currentCity As String Dim prevCity As String: prevCity = "北海道札幌市中央区" Dim firstRow As Long: firstRow = 2 With ActiveSheet For i = 2 To 124179
'現在の行の都道府県&市区町村 currentCity = .Range("C" & i).value & .Range("D" & i).value
'現在の行の都道府県&市区町村が前の行の都道府県&市区町村と異なる場合 If currentCity <> prevCity Then lastRow = i - 1' 前の行を最終行として設定
'地名の該当範囲に都道府県名&市区町村を定義する ActiveWorkbook.Names.Add _ Name:=prevCity, RefersTo:=.Range("E" & firstRow & ":" & "E" & lastRow) firstRow = i End If prevCity = currentCity Next End With End Sub
8.入力用のシートを用意し(「入力」シート)、入力規制の設定をする。
都道府県の入力列に、「マスター」シートの「都道府県」を参照してデータを選択できるようにする。

市区町村の入力列に、「マスター」シートで定義した範囲を参照してデータを選択できるようにする。下図では、INDIRECT関数でB列の値「北海道」を取得し、「マスター」シートの市区町村の列のうち「北海道」と名前を定義された範囲からデータを参照する。

地名の入力列には、下図のように、INDIRECT(B2&C2)によって「北海道札幌市中央区」を取得し、「マスター」シートの地名の列のうち「北海道札幌市中央区」と定義された範囲を参照できるように設定する。

9. 郵便番号の列に下記の式を入れて、郵便番号を参照する。
その前に、「郵便番号」シートの郵便番号の列と都道府県市区町村地名の連結列にそれぞれ、「郵便番号」「都道府県市区町村」などと名前を定義しておく。
INDEX(郵便番号, MATCH(B2&C2&D2, 都道府県市区町村, 0), 1)
都道府県&市区町村&地名を結合した文字列を、「郵便番号」シートの連結列「都道府県市区町村」から、探し、取得した行番号を基に、INDEX関数で該当行・該当列の値、すなわち、郵便番号を取得して表示する。