エクセルで郵便番号から自動的に住所を入力! 7桁の郵便番号を入力するだけで住所を一発表示 365・2019・2016・2013・2010・2007対応

エクセルで郵便番号から住所を入力する方法として、「IMEの変換」機能を利用して簡単に入力するというサイトをよく拝見します。

具体的には、郵便番号をハイフンを含めて日本語入力モードで入力し、変換するという作業になります。

1つ2つなら便利な機能なのですが、数百もある住所録では大変です。

「IMEの変換」を利用した郵便番号から住所の入力

今回はデータベースを用意し、エクセルの「VLOOKUP関数」を利用して郵便番号7ケタを入力すると、自動的に住所を表示させる方法の紹介です。アドインは利用しません。

超簡単!エクセルのVLOOKUP関数で成績一覧から個人の成績票を作成 VLOOKUP関数の裏技も紹介! 365・2019・2016・2013・2010・2007対応
今回は、成績一覧データから、個人の成績票を「VLOOKUP関数」で作成する方法です。下記の成績一覧データから、個人の成績票を作成します(ちなみに名前は...

こちらの記事の完成版をnote上に公開中です。

エクセルで7桁の郵便番号を入力して住所を一発表示! 全国の住所に対応! マクロやアドイン不要!|リーダーの独り言|note
リーダーです。 今回は、エクセルのセルに7桁の郵便番号を入力すると、自動で住所が入力されるプログラムです。 マクロやアドインは利用していません。 サイトの記事はこちらです。 エクセルで郵便番号から自動的に住所を入力! 7桁の郵便番号を入力するだけで住所を一発表示 365・2019・2016・2013・2010...
スポンサーリンク

まずは日本郵便のサイトから郵便番号データを入手

まずは郵便番号と住所のデータベースとして、日本郵便のサイトから郵便番号データを入手します。

読み仮名データの促音・拗音を小書きで表記しないもの - zip形式 日本郵便
郵便番号データのうち、「ホツカイドウ」のように促音・拗音を小書きで表記しないデータをご提供しています。

全国一括のデータは12万件もあるそうです。今回は例として「神奈川県」のデータを利用します。

上記サイトのデータはCSV形式ですので、一旦エクセル形式で保存してから住所録を作成していきます。

CSVファイルを「Excel ブック (*.xlsx)」で保存

下記が、CSVファイルからエクセル形式に保存したブック(シート)です。こちらをデータベースとして利用して、郵便番号から住所を一発で入力できるようにします。

神奈川県の郵便番号データ

まずは、データベースとして範囲に名前をつけて「VLOOKUP関数」で利用しやすいように準備をします。具体的には、下記の場合はC列が「郵便番号」、G列が「都道府県」、H列が「区」、I列が「町名」となっていますので、C1セルからI2295セルまで選択して「住所」という名前を定義しました。

住所録の名前を定義

定義した名前の「参照範囲」はこちらになります。

神奈川県の住所録データベースに名前を定義
Excel(エクセル)プルダウンリスト(ドロップダウンリスト)のリストを可変式リストにすればリストの項目が増えても再設定不要! リストを名前の定義とテーブルにする2つの方法 365・2019・2016・2013・2010・2007対応
データの入力規則で、「元の値」をリストにして、プルダウンリスト(ドロップダウンリスト)を利用している方は多いと思います。「元の値」のリストは、Exce...
スポンサーリンク

「郵便番号」セルの表示形式を変更しておく

続いて下のような表を作成します。順に「郵便番号」「都道府県」「住所1」「住所2」としています。

郵便番号は7桁になりますが、ハイフンは自動で表示されるように「セルの書式設定」で表示形式を変更しておきます。

セルA2からA9までを選択し、キーボードの「Ctl」+「1」を押します(「セルの書式設定」ショートカットキー)。

「Ctl」+「1」 (セルの書式設定ショートカットキー)を押す

「セルの書式設定」ダイアログボックスで、「その他」「郵便番号」を選択し、「OK」をクリックします。

セルの書式設定「その他」「郵便番号」
エクセルで日付をコピーしたらシリアル値になる。。。日付に戻す方法!TEXT関数とセルの書式設定でも使える日付表示形式の種類の一覧 365・2019・2016・2013・2010・2007対応
先日エクセルで、謎の意味不明の日付データが届きました。なぜ日付と曜日を分けて表示する?イベント開催の日程表(イベント名は消しています)なのです...

これで準備完了です。7桁の数字を入力すると、自動的にハイフンが表示され、郵便番号の形式になります。

7桁の郵便番号に自動的にハイフンが表示される

これですべて準備は完了です。それでは、郵便番号から自動で住所を入力するために、「VLOOKUP関数」を使って数式を入力していきましょう。

スポンサーリンク

「VLOOKUP関数」を利用して郵便番号から住所を取り出す

「VLOOKUP関数」を利用して、郵便番号から住所を取り出していきます。

まずはB2セルに

=IFERROR(VLOOKUP($A2,住所,5,FALSE),"")

と入力します。

まずは都道府県を取り出す

「VLOOKUP関数」の引数は下記の通りです。

「検索値」に「$A2(列のみ絶対参照にしておく必要があります)」、「範囲」は先ほど名前を定義したデータベース「住所」、列番号は「都道府県」が格納されている「住所」中の5列目、「検索方法」は「完全一致」を表す「FALSE」としています。

「VLOOKUP関数」の引数

そして「IFERROR関数」で、「VLOOKUP関数」がエラーの場合に空白(””)が表示されるように設定しています。

「VLOOKUP関数」がエラーのときは非表示(空白)に
エクセルで住所から都道府県と市や区名を関数で抽出・取り出す方法 市や区がない場合も取り出し可能! 365・2019・2016・2013・2010・2007対応
以前、文字列関数の使用方法の解説で都道府県、市、区や町名を取り出す方法について記事にしました。今回はその記事の上級者向けの、「住所一覧」から長さの違う...

B1に数式を入力後、B10セルまで数式をコピーします。当然ですが、今回はすべて「神奈川県」と入力されます。

「郵便番号」から「都道府県」を取り出すことができた
エクセルでスペースのない氏名(姓名)を姓(名字)と名に分割する(分ける)方法 意外と簡単 「VLOOKUP関数」で名字を確定して名前も取り出す! 365・2019・2016・2013・2010・2007対応
エクセルで、姓と名の間にスペースがある氏名から「姓」と「名」に分割する方法は簡単です。ところが、姓と名の間にスペースや区切り文字がない場合、「...
スポンサーリンク

「VLOOKUP関数」の数式をコピーして「区」と「町名」を取り出す

次は、B2セルに入力した数式をコピーして、「住所1」と「住所2」を取り出します。

「VLOOKUP関数」の検索値はA2セルのままですので、先ほど「$A2」と列のみ絶対参照にしています。

数式をコピー

そして、住所1の列は、「VLOOKUP関数」の引数「列番号」を6に変更します。

「VLOOKUP関数」の「列番号を「6」に

さらにD2セルの「列番号」も「7」に変更します。

D2セルの列番号も変更する

名前で定義した範囲「住所」では、列番号が下記のようになっています。

名前で定義した範囲内での「列番号」

それぞれの列番号を変更後、C2とD2セルを選択後、D9セルまでコピーした状態が下図になります。

郵便番号から住所を取り出すことができた

ちなみに、下図のように7桁の郵便番号を入力して、確定させると自動的に住所が入力されます。

7桁の郵便番号を入力

下図のように、7桁の郵便番号を入力するだけで一発で住所を表示させることができました。

郵便番号の入力だけで住所が表示された

ちなみに、住所を結合させる方法は、下記のように「VLOOKUP関数」を「&」でつなげることで可能になります。

数式は下記の通りです。

=IFERROR(VLOOKUP(A2,住所,5,FALSE) & VLOOKUP(A2,住所,6,FALSE) & VLOOKUP(A2,住所,7,FALSE),"")
スポンサーリンク

データベースを全国版にすると12万件以上のデータの利用が可能に

元のデータベースを全国版にすると、12万件以上ありますが、全国各地の郵便番号に対応可能です。

最初に全国版のCSV形式ファイルを開く際には時間がかかりますが、一旦エクセルファイルとして保存すると、12万件以上あるデータベースですが、開く際にそれほどの時間はかかりません。

全国の住所録データベースを名前の範囲にすると、下図のように全国の郵便番号から住所を自動で取得可能です。

全国の郵便番号に対応
全国の住所録をデータベースを範囲として名前を定義

非常に便利なエクセルの活用方法です。皆さんもぜひ、当記事を参考に住所録の編集に活用してみてください。

12万件以上の郵便番号データベースを可変式名前リスト(メンテナンスフリー)にした、今回の記事の完成版はこちらをご覧ください。

エクセルで7桁の郵便番号を入力して住所を一発表示! 全国の住所に対応! マクロやアドイン不要!|リーダーの独り言|note
リーダーです。 今回は、エクセルのセルに7桁の郵便番号を入力すると、自動で住所が入力されるプログラムです。 マクロやアドインは利用していません。 サイトの記事はこちらです。 エクセルで郵便番号から自動的に住所を入力! 7桁の郵便番号を入力するだけで住所を一発表示 365・2019・2016・2013・2010...

ご不明な点があれば、コメントいただけたらご回答致します。

スポンサーリンク
Office・Windows
スポンサーリンク
リーダーの独り言

コメント

  1. ワコドット より:

    ありがとうございました!!助かりました!!

    • リーダーの独り言 より:

      ワコドット様
      コメントありがとうございます。

      コメントいただくとこちらもうれしくなります。
      また困ったことがございましたらお気軽にコメントください。

タイトルとURLをコピーしました