Excelで住所を郵便番号へ一括変換する方法!VLOOKUP不要!

Excelで住所を郵便番号へ一括変換したいときはないでしょうか。

けど、そんな中で悩むことは、

・Excelで住所を郵便番号へ一括変換したいが方法がよくわからない
・VLOOKUP関数を使わずに住所を郵便番号へ一括変換したいが方法がよくわからない

ですよね。

今回はそんなお悩みを解決する

・Excelで住所を郵便番号へ一括変換する方法
・VLOOKUP関数を使わずに住所を郵便番号へ一括変換する方法

についてまとめます!

Excelで住所を郵便番号へ一括変換するイメージ

Excelで住所を郵便番号へ一括変換するイメージについて説明をします。

まずは最新の郵便番号データを郵便局サイトからダウンロードします。

次に郵便番号データをExcelへインポートし、

住所を入力し、独自の関数を設定すると、

関数を入力したセルに郵便番号が表示されます!

なお、住所に半角/全角スペースが入っていても変換可能です!

大量の郵便番号データから住所を調べるときに便利ですね!

それでは早速試してみましょう。

Excelで住所を郵便番号へ一括変換する方法

Excelで住所を郵便番号へ一括変換するについて説明をします。

EXTAN_JUYU関数について

使う関数はEXTAN_JUYU関数となります。

EXTAN_JUYU関数はエク短オリジナル関数で、住所文字列を指定すると郵便番号を返す関数です。

構文と設定パラメータ

EXTAN_JUYU関数の構文と設定パラメータは以下の通りです。

関数名 EXTAN_JUYU
構文 EXTAN_JUYU (住所)
設定パラメータ 住所(String型):
必須。 住所を示す文字列を指定します。” ”や” “が入っていても問題ありません。

郵便番号データを郵便局サイトからダウンロードする

最新の郵便番号データを郵便局サイトからダウンロードします。

郵便番号データダウンロードページ
https://www.post.japanpost.jp/zipcode/dl/oogaki-zip.html

「全国一括」リンクをクリックします。

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

ダウンロードが完了したら、ZIPファイルを解凍し、CSVファイルを任意の場所に格納しましょう。

タカヒロ
タカヒロ
CSVファイルの格納先については今後のデータ更新の時に利用するフォルダとなりますので、フォルダの場所を変更しないようお願いします。

CSVをExcelへ取り込む

ダウンロードしたCSVファイルのデータをExcelへ取り込みましょう。

「データ」タブをクリックし、「テキストまたはCSVから」ボタンをクリックし、

ダウンロードしたCSVファイルを選択します。

「KEN_ALL」シート名で取り込められたら取り込み完成です。

 

サンプルの郵便番号を入力する

まずはサンプルの住所文字列をA列へ入力していきます。

住所文字列に半角/全角スペースが含まれても関数側で除外しますので、問題ありません。

オリジナル関数のVBAコードを実装する

オリジナル関数が含まれるVBAコードをExcelへ実装します。

Function EXTAN_JUYU(rngCell As Range) As String
    
    Dim arrYuData As Variant
    Dim strCellValue As String
    Dim strJusho As String
    Dim lngEndrow As Long
    Dim i As Long
    
    '郵便番号リストを対象にします。
    With ThisWorkbook.Worksheets("KEN_ALL")
        
        '入力値をクレンジングします。
        strCellValue = Replace(rngCell.Value, " ", "")
        strCellValue = Replace(strCellValue, " ", "")

        '郵便番号リストの最終行を取得します。
        lngEndrow = .Cells(Rows.Count, 1).End(xlUp).Row
        
        '郵便番号リストを配列に変換します。
        arrYuData = .Range(.Cells(1, 1), .Cells(lngEndrow, 9))
        
        '最終行から2行目まで逆順で処理を繰り返します。
        For i = lngEndrow To 2 Step -1

            '郵便番号リストの住所を文字結合して変数に代入します。
            strJusho = arrYuData(i, 7) & arrYuData(i, 8) & arrYuData(i, 9)
            '住所文字列をクレンジングします。
            strJusho = Replace(strJusho, "以下に掲載がない場合", "")

            '入力値の住所にリストの住所が部分一致したら処理します。
            If InStr(strCellValue, strJusho) Then
                
                '郵便番号を戻り値に設定します。
                EXTAN_JUYU = arrYuData(i, 3)
                
                '繰り返し処理を中断します。
                Exit For

            End If

        Next i
    
    End With

End Function

VBAの実装

VBAの実装方法については
VBAの実装手順
をご参照ください。

EXTAN_JUYU関数を入力する

EXTAN_JUYU関数を入力していきましょう。

B2セルに以下を入力します。

=EXTAN_JUYU(A2)

はい、郵便番号が出力されましたね!

最後のセルまでドラッグしましょう。

はい、他の住所も郵便番号が出力されましたね!!

VBAの説明

VBAについて説明をします。

Withで郵便番号リストシートを対象にします。シート名は”KEN_ALL”に固定するようお願いします。

With ThisWorkbook.Worksheets(“KEN_ALL”)

入力値をクレンジングします。

strCellValue = Replace(rngCell.Value, " ", "")
strCellValue = Replace(strCellValue, " ", "")

郵便番号リストの最終行を取得します。

lngEndrow = .Cells(Rows.Count, 1).End(xlUp).Row

郵便番号リストを配列に変換します。

arrYuData = .Range(.Cells(1, 1), .Cells(lngEndrow, 9))

最終行から2行目まで逆順で処理を繰り返します。

For i = lngEndrow To 2 Step -1

タカヒロ
タカヒロ
逆順で繰り返し処理をする理由は、以下のような3つ目の住所がない場合が優先されないようにしているためです。

逆順にすることにより、3つ目の住所が優先され、該当する郵便番号が返る形になります。

郵便番号リストの住所を文字結合して変数に代入します。

strJusho = arrYuData(i, 7) & arrYuData(i, 8) & arrYuData(i, 9)

住所文字列をクレンジングします。

strJusho = Replace(strJusho, "以下に掲載がない場合", "")

入力値の住所にリストの住所が部分一致したら処理します。

If InStr(strCellValue, strJusho) Then

タカヒロ
タカヒロ
InStr関数を使い部分一致した場合の結果から処理継続の判定をしています。
なお、以下のように仕様上リストデータ内にメモ書きがあったり、(**丁目)と記載があるものは検索対象外となりますので、予めご了承ください。。

郵便番号リストの住所を文字結合して変数に代入します。

strJusho = arrYuData(i, 7) & arrYuData(i, 8) & arrYuData(i, 9)

出力値をクレンジングします。

strJusho = Replace(strJusho, "以下に掲載がない場合", "")

郵便番号を戻り値に設定します。

EXTAN_JUYU = arrYuData(i, 3)

繰り返し処理を中断します。

Exit For

郵便番号データを更新する

郵便番号と住所は日々更新されています。

最新の状態にするためには同じく郵便局サイトから郵便番号データをダウンロードしてExcel側へ取り込んでいきます。

「全国一括」リンクをクリックします。

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

ダウンロードが完了したら、ZIPファイルを解凍し、CSVファイルを前回と同じ場所へ格納します。

次にExcel側のデータタブをクリックし、すべて更新ボタンをクリックします。

下段のステータスバーが消えれば顔料となります。

タカヒロ
タカヒロ
データを更新するボタンがあることで更新作業がぐっと楽になりましたね。

VBAの実装手順

実装手順は以下の通りです。

Excel側にVBAを実装していきます。

①Excelを新規に開き、「開発」タブをクリックし、「VisualBasic」をクリックします。
もしくはショートカットキー「Alt」+「F11」でもOKです。

②標準モジュールを追加します。
左ペインのVBAProjectを右クリックし、「挿入」、「標準モジュール」を選択します。

③右ペインのウインドウに上記のVBAを入力します。

こちらで完了です。

 

さいごに

いかがでしょうか。

今回は、

・Excelで住所を郵便番号へ一括変換する方法
・VLOOKUP関数を使わずに住所を郵便番号へ一括変換する方法

についてまとめました。

また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。



この記事の関連キーワード

こちらの記事の関連キーワード一覧です。クリックするとキーワードに関連する記事一覧が閲覧できます。









2 件のコメント

  • EXTAN_JUYU関数に関しては、住所が都道府県から入っている前提ですか?
    〇〇市からだと表記されなかったため。
    その場合、VBAの編集等で対応できますでしょうか。
    当方、VBAはこちらの記事を参考に初めて使いました。
    勉強になります、ありがとうございます。

    • いつもご利用ありがとうございます。

      郵便番号を判断する基準は都道府県を含む住所となります。

      市から始まる住所で郵便番号を判断する方法につきましては、以下のように都道府県の判定を外していただければと存じます。
      ■変更前
      ‘郵便番号リストの住所を文字結合して変数に代入します。
      strJusho = arrYuData(i, 7) & arrYuData(i, 8) & arrYuData(i, 9)

      ■変更後
      ‘郵便番号リストの住所を文字結合して変数に代入します。
      strJusho = arrYuData(i, 8) & arrYuData(i, 9)

  • コメントを残す

    メールアドレスが公開されることはありません。 * が付いている欄は必須項目です