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

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

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

ですよね。

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

についてまとめます！

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

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

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

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

関数を設定すると、

関数を入力したセルに住所が表示されます！

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

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

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

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

EXTAN_YUJU関数について

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

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

構文と設定パラメータ

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

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

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

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

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

タカヒロ

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

CSVをExcelへ取り込む

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

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

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

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

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

まずはサンプルの郵便番号を入力していきます。

入力する値は半角の数字を入力します。

またハイフンや半角スペースが含まれても関数側で除外しますので、問題ありません。

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

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

Function EXTAN_YUJU(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, " ", "")
        strCellValue = Replace(strCellValue, "―", "")

        '郵便番号リストの最終行を取得します。
        lngEndrow = .Cells(Rows.Count, 1).End(xlUp).Row
        
        '郵便番号リストを配列に変換します。
        arrYuData = .Range(.Cells(1, 1), .Cells(lngEndrow, 9))
        
        '２行目から最終行まで処理を繰り返します。
        For i = 2 To lngEndrow
            '入力値の郵便番号とリストの郵便番号が合致していたら処理します。
            If strCellValue = arrYuData(i, 3) Then
                '郵便番号リストの住所を文字結合して変数に代入します。
                strJusho = arrYuData(i, 7) & arrYuData(i, 8) & arrYuData(i, 9)
                '出力値をクレンジングします。
                strJusho = Replace(strJusho, "以下に掲載がない場合", "")
                
                '郵便番号リストの住所を戻り値に設定します。
                EXTAN_YUJU = strJusho
                
                '繰り返し処理を中断します。
                Exit For
                
            End If

        Next i
    
    End With

End Function

VBAの実装

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

EXTAN_YUJU関数を入力する

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

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

はい、住所が出力されましたね！

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

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

VBAの説明

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

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

With ThisWorkbook.Worksheets(“KEN_ALL”)

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

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

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

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

入力値の郵便番号とリストの郵便番号が合致していたら処理します。

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

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

もしクレンジングしない場合は以下のようになる場合があります。

原因はデータに不要な文字列が含まれているためです。

タカヒロ

他にもメモ書きが入っていたりするので不要な文字があれば削除するようお願いします。

郵便番号リストの住所を戻り値に設定します。

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

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

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

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

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

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

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

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

タカヒロ

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

VBAの実装手順

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

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

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

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

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

こちらで完了です。

さいごに

いかがでしょうか。

今回は、

についてまとめました。

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