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関数で該当行・該当列の値、すなわち、郵便番号を取得して表示する。

特集記事
最新記事
アーカイブ
タグから検索
ソーシャルメディア
  • Facebook Basic Square
  • Twitter Basic Square
  • Google+ Basic Square