Excel 郵便番号から住所を検索する方法

この記事ではセルに郵便番号を入力したら

自動的に住所を検索して

表示する方法を紹介します。

手順

郵便番号と住所の一覧表の入手、取り込み、加工

郵便局のHPからダウンロード

まずは郵便番号と住所の対応表を

手に入れる必要があります。

それには郵便局のHPが最適です。

郵便局(郵便番号データダウンロード)

読み仮名データを選んでリンクを進みます。

郵便番号データダウンロードのキャプチャ(2019/09/17時点)
キャプチャ(2019/09/17時点)

次に地域が表示されるため選択します。

一番、右下には全国がありますが

かなり重いため使用には

動作が重くなる覚悟が必要です。

郵便番号データダウンロード(地域別)のキャプチャ(2019/09/17時点)
キャプチャ(2019/09/17時点)

ZIP形式でダウンロードされるため

ダブルクリックして解凍します。

解凍されたCSVのキャプチャ

郵便番号と住所の一覧表をExcelにインポート

テキストエディタでCSVを開くと下のような状態であり

下記、赤枠部分のように0から始まるものは

普通にExcelで開くと上手くいきません。

CSVをテキストエディタで開いたキャプチャ。0から始まる項目を赤枠で強調

このように先頭のゼロが消えてしまいます。

CSVをExcelで開いた状態のキャプチャ。赤枠部分は先頭の0が消えている状態の強調

それに対応するには、まず新規でExcelを開き

「データ」より「ファイルから」、

「テキストまたはCSVから」を選択します。

「データ」より「ファイルから」、「テキストまたはCSVから」を選択するキャプチャ

取り込み形式を選択するウインドウが表示されるため

「データが検出しない」を選択し、

「データの変換」を押下します。

取り込み形式を選択するウインドウで、「データが検出しない」を選択し、「データの変換」を押下するキャプチャ

先頭0を残したまま取り込まれました。

このままだと操作の拍子に

消えてしまう可能性があるため

書式を文字列に変更しておきましょう。

(0消えに注意すれば後述の数式を

 作った後でもよいです)

先頭3列を文字列に変換するキャプチャ

なおカラム名はCSV内にないため

そのままにしています。

このままでも支障がないですが、

必要な方は下記ページを参考に付けておきましょう。

郵便局(郵便番号データの説明)

検索結果として表示する列を作成

今回は住所と読み仮名を表示するので

VLOOKUPされるための列を作ります。

CONCAT関数で同じ行のI~K列を

範囲指定するだけです。

もし関数が実行されない場合、

列の表示形式が文字列になっているため

標準に戻しましょう。

この方法により住所と住所(カナ)の列を作成します。

なおシート名はそのままでもいいですが、

判り易くするために「対応表」としています。

CONCAT関数で都道府県名、市区町村名、町域名を結合した状態の数式解説(キャプチャ)

なお先頭行にはカラム名を都道府県名、

市区町村名、町域名と付けています。

データインポートの際、

自動的にテーブルに変換され

構造化参照という状態になります。

これにより、いつもの列表記ではなく

@[都道府県名]のような列名表記になります。

更に2行目に数式を入れると

自動的に一番下まで数式がコピーされます。

(手でコピーする必要がありません)

_01HOKKAIはインポート時に

CSVファイル名から自動的に付与されたセル範囲名です。

テーブル化と構造化参照は

非常に高機能なため興味のある方は

こちらの記事で詳しく書いているので

参照頂けると幸いです。

検索するシートを作成

まず郵便番号を入力するシートを作ります。

ここは先頭0を許容するため

先ほどと同様、書式設定で文字列にします。

そしてVLOOKUP関数で検索の数式を作ります。

VLOOKUP関数の基本的な形式は下記のとおりです。

VLOOKUP(検索値,検索範囲,表示項目の列番号,FALSE)
VLOOKUP関数の指定例のキャプチャ

まず引数1の検索値は郵便番号セル(C2)を指定し、

検索範囲は検索値から表示項目を含んだ

対応表の列C:Eを指定します。

次に引数の列番号は、

住所を表示するセルは2、

カナを表示するセルには3を指定します。

これは引数2の左端を1とした連番です。

最後のFALSEは固定です。

このように指定すると検索するシートが完成します。

VLOOKUP関数の引数について図解

関連記事

VLOOKUP関数の使い方・活用方法の記事一覧

文字列を連結する(CONCAT関数)

テキストデータの取り込み方法(区切り文字、固定長)

フォローする