【EXCEL：VBA】郵便番号から住所を知りたい

この記事では、EXCELで郵便番号を指定して、該当する住所を調べる方法について説明しています。

郵便局のホームページに載っている情報を使うので、間違いないです。

今回ご紹介するツールを一度作成しておけば、次から住所を間違わずに郵便物を遅れるようになります。

それでは、EXCELで郵便番号を指定して、住所を調べるツールを作成していきましょう。

困っている女性

顧客の書類を送るのに、郵便番号から住所を調べるのが地味に大変なの・・・。

困っている女性

一通、二通くらいならいいけど、何十通も送るとなると間違ってはいけないから神経を使うの。

それなら、VBAを使えばボタン1つ押すだけで、郵便番号から住所をサクッと調べることができるよ。

解決する男性

しかも、調べたい郵便番号をいくつでも入力しておけば、一気にすべての該当する住所を調べれるよ。

解決する男性

困っている女性

それそれ、早く教えてよ。

それでは、郵便番号から住所を検索するツールに必要なものを、1つ1つ順番に説明していきます。

１．郵便番号情報を準備する

まず、重要な郵便番号情報を郵便局のホームページからダウンロードしていきます。

郵便番号情報をダウンロードする

以下から、郵便番号をダウンロードします。

https://www.post.japanpost.jp/zipcode/dl/kogaki/zip/ken_all.zip

郵便番号情報をダウンロードする

郵便番号情報はZipファイルになっている

郵便番号情報はZipファイルになっていますので、解凍します。

解凍すると、CSVファイルになります。

Zipファイルを解凍する

ちなみに、CSVファイルの中身をみると、以下のようになっています。

郵便番号情報の中身

この郵便番号情報から実際に利用するのは、以下の4つだけです。

3列目：郵便番号
7列目：都道府県名
8列目：市区町村名
9列目：町域名

「データ」シートに郵便番号情報を貼り付ける

それでは、「データ」という名前のシートを用意し、ダウンロードしてきた郵便番号情報を貼り付けます。

注意点としては、3列目は文字列として貼り付けしてください。

例えば、「0640941」のような郵便番号の頭0が、貼り付け時に取れてしまいますので。

「データ」シートに郵便番号情報を貼り付ける

２．検索したい郵便番号を入力するシートを作成する

それでは、検索したい郵便番号を入力するシートを準備しましょう。

「住所検索」シートを作成する

「住所検索」という名前でシートを追加します。
1行目には、「郵便番号」と「住所」のタイトル行を作成します。
2行目以降は、検索したい郵便番号を好きなだけ入力します。

３．VBAで郵便番号から住所を検索する機能を記入する

「Microsoft Visual Basic for Applications」を起動します。

VBAプログラムを記入する

標準モジュールを追加して、以下のソースを書きます。

Public Sub MainProc()
    Dim shtMain As Worksheet
    Dim shtData As Worksheet
    Dim varZipCd As Variant
    Dim varAddress As Variant
    Dim lastRow As Long
    Dim nowRow As Long
    Dim i As Long
    
    '①「住所検索」シートを変数に格納する
    Set shtMain = ThisWorkbook.Sheets("住所検索")
    
    '②「データ」シートを変数に格納する
    Set shtData = ThisWorkbook.Sheets("データ")
        
    '③「住所検索」シートの初期化
    shtMain.Range("B2:B10000").ClearContents
                
    '④郵便番号データの最終行を取得する
    lastRow = shtData.Cells(shtData.Rows.Count, 3).End(xlUp).Row
                
    '⑤郵便番号データを配列に格納する
    varZipCd = shtData.Range("C1:C" & lastRow)
    
    '⑥住所データを配列に格納する
    varAddress = shtData.Range("G1:I" & lastRow)
                
    nowRow = 1
    
    Do While True
        
        '⑦「住所検索」シートの現在行を次の行に変更する
        nowRow = nowRow + 1
        
        '⑧郵便番号が空なら処理を抜ける
        If shtMain.Range("A" & nowRow) = "" Then
            Exit Do
        End If
        
        '⑨郵便番号で「データ」シートの郵便番号を探し、該当行の住所を取得する
        For i = 1 To UBound(varZipCd)
            If shtMain.Range("A" & nowRow) = varZipCd(i, 1) Then
                shtMain.Range("B" & nowRow) = varAddress(i, 1) & varAddress(i, 2) & varAddress(i, 3)
                Exit For
            End If
        Next
    Loop
    
    MsgBox "完了"
End Sub

Public Sub MainProc()

Dim shtMain As Worksheet

Dim shtData As Worksheet

Dim varZipCd As Variant

Dim varAddress As Variant

Dim lastRow As Long

Dim nowRow As Long

Dim i As Long

'①「住所検索」シートを変数に格納する

Set shtMain = ThisWorkbook.Sheets("住所検索")

'②「データ」シートを変数に格納する

Set shtData = ThisWorkbook.Sheets("データ")

'③「住所検索」シートの初期化

shtMain.Range("B2:B10000").ClearContents

'④郵便番号データの最終行を取得する

lastRow = shtData.Cells(shtData.Rows.Count, 3).End(xlUp).Row

'⑤郵便番号データを配列に格納する

varZipCd = shtData.Range("C1:C" & lastRow)

'⑥住所データを配列に格納する

varAddress = shtData.Range("G1:I" & lastRow)

nowRow = 1

Do While True

'⑦「住所検索」シートの現在行を次の行に変更する

nowRow = nowRow + 1

'⑧郵便番号が空なら処理を抜ける

If shtMain.Range("A" & nowRow) = "" Then

Exit Do

End If

'⑨郵便番号で「データ」シートの郵便番号を探し、該当行の住所を取得する

For i = 1 To UBound(varZipCd)

If shtMain.Range("A" & nowRow) = varZipCd(i, 1) Then

shtMain.Range("B" & nowRow) = varAddress(i, 1) & varAddress(i, 2) & varAddress(i, 3)

Exit For

End If

Loop

MsgBox "完了"

End Sub

４．郵便番号を指定して住所を検索する機能の使い方

それでは、郵便番号から住所を検索する方法を説明します。

A列の2行目以降に、住所検索したい郵便番号を好きなだけ入力します。

郵便番号から住所を検索する機能を動かす

次に、「開発」タブの「マクロ」をクリックします。

実行ボタンをクリックする

先程作成した自作関数「MainProc」が一覧に表示されていますので、選択します。

次に、「実行」ボタンをクリックして集計を開始します。

少し待つと、入力されている郵便番号に該当する住所がB列の2行目以降に表示されます。

郵便番号から住所を検索した結果

どうですか、簡単でしょ。

５．郵便番号を指定して住所を検索するVBAプログラムの説明

それでは、VBAプログラムを説明します。

①「住所検索」シートを変数に格納する

'①「住所検索」シートを変数に格納する
Set shtMain = ThisWorkbook.Sheets("住所検索")

1 2	'①「住所検索」シートを変数に格納する Set shtMain = ThisWorkbook.Sheets("住所検索")

ここでは、このあとの処理で何度も使う「住所検索」シートを変数に格納しています。

そうすることにより、入力するプログラムを短くすることができるからです。

何度も「ThisWorkbook.Sheets(“住所検索”)」と入力するより、「shtMain」と入力するほうが少なくてすむからです。

②「データ」シートを変数に格納する

'②「データ」シートを変数に格納する
Set shtData = ThisWorkbook.Sheets("データ")

1 2	'②「データ」シートを変数に格納する Set shtData = ThisWorkbook.Sheets("データ")

ここでは、このあとの処理で何度も使う「データ」シートを変数に格納しています。

この「データ」シートには、郵便番号情報が取り込みされています。

この「データ」シートの郵便番号情報から住所を検索します。

③「住所検索」シートの初期化を行う

'③「住所検索」シートの初期化
shtMain.Range("B2:B10000").ClearContents

1 2	'③「住所検索」シートの初期化 shtMain.Range("B2:B10000").ClearContents

ここでは、「住所検索」シートの検索された住所をクリアしています。

そうすることにより、前回実行した検索結果をキレイに消し去ることができます。

毎回新たな気分で検索ができます。

④郵便番号データの最終行を取得する

'④郵便番号データの最終行を取得する
lastRow = shtData.Cells(shtData.Rows.Count, 3).End(xlUp).Row

1 2	'④郵便番号データの最終行を取得する lastRow = shtData.Cells(shtData.Rows.Count, 3).End(xlUp).Row

ここでは、「データ」シートの3列目に入力されている「郵便番号」の最終行を取得しています。

取得した最終行番号を変数「lastRow」に格納しています。

郵便番号を検索するときに、どの行まで検索すればいいのかを指定するために、最終行番号は利用するからです。

⑤郵便番号データを配列に格納する

'⑤郵便番号データを配列に格納する
varZipCd = shtData.Range("C1:C" & lastRow)

1 2	'⑤郵便番号データを配列に格納する varZipCd = shtData.Range("C1:C" & lastRow)

ここでは、先程調べておいた郵便番号の最終行番号を利用して、配列「varZipCd」に全ての郵便番号データを格納しています。

このあとの処理で、利用するからです。

EXCEL上で郵便番号を検索するより、配列にいれたデータから検索するほうが高速に検索できるからです。

⑥住所データを配列に格納する

'⑥住所データを配列に格納する
varAddress = shtData.Range("G1:I" & lastRow)

1 2	'⑥住所データを配列に格納する varAddress = shtData.Range("G1:I" & lastRow)

ここでは、「データ」シートのG列の「都道府県名」、H列の「市区町村名」、I列の「町域名」を配列「varAddress」に格納しています。

先程調べておいた郵便番号の最終行番号を利用しているのは、「郵便番号」の最終行と「都道府県名」、「市区町村名」、「町域名」の最終行は同じだからです。

⑦「住所検索」シートの現在行を次の行に変更する

'⑦「住所検索」シートの現在行を次の行に変更する
nowRow = nowRow + 1

1 2	'⑦「住所検索」シートの現在行を次の行に変更する nowRow = nowRow + 1

ここでは、変数「nowRow」に格納されている値+1を、変数「nowRow」に格納しています。

つまり、nowRowの値を+1しています。

nowRowには最初、1がセットされていますので、+1されて2が格納されます。

「住所検索」シートの2行目から入力されている郵便番号を取得するために、最初に1をセットしています。

⑧郵便番号が空なら処理を抜ける

'⑧郵便番号が空なら処理を抜ける
If shtMain.Range("A" & nowRow) = "" Then
    Exit Do
End If

'⑧郵便番号が空なら処理を抜ける

If shtMain.Range("A" & nowRow) = "" Then

Exit Do

End If

ここでは、郵便番号が入力されているかチェックしています。

２行目から１行ずつ値が入力されていない行まで繰り返しチェックし、空の行がみつかると住所検索処理を終わらせます。

この処理があるおかげで、入力されている郵便番号全てに対して、住所を検索することを実現しています。

⑨郵便番号で「データ」シートの郵便番号を検索し、該当行の住所を取得する

'⑨郵便番号で「データ」シートの郵便番号を探し、該当行の住所を取得する
For i = 1 To UBound(varZipCd)
    If shtMain.Range("A" & nowRow) = varZipCd(i, 1) Then
        shtMain.Range("B" & nowRow) = varAddress(i, 1) & varAddress(i, 2) & varAddress(i, 3)
        Exit For
    End If
Next

'⑨郵便番号で「データ」シートの郵便番号を探し、該当行の住所を取得する

For i = 1 To UBound(varZipCd)