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．入力用のシートを用意し（「入力」シート）、入力規制の設定をする。

都道府県の入力列に、「マスター」シートの「都道府県」を参照してデータを選択できるようにする。