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

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

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

ですよね。

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

についてまとめます！

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

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

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

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

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

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

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

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

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

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

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

EXTAN_JUYU関数について

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

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

構文と設定パラメータ

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

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

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

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

ダウンロードが完了したら、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))
        
        '最終行から２行目まで逆順で処理を繰り返します。
        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セルに以下を入力します。

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

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

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

VBAの説明

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

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

With ThisWorkbook.Worksheets(“KEN_ALL”)

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

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

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

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

タカヒロ

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

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

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

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

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

タカヒロ

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

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

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

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

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

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

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

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

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

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

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

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

タカヒロ

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

VBAの実装手順

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

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

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

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

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

こちらで完了です。

さいごに

いかがでしょうか。

今回は、

についてまとめました。

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