エクセルで住所から自動的に郵便番号を導き出す一番簡単な方法をご紹介
エクセルで、顧客一覧とか社員一覧を作成する際に、住所から郵便番号を取り出せないか悩んだことはないでしょうか。
実際、ネットを検索してみると、かなりの数の情報がヒットします。皆さん、この問題には頭を悩ませているんだとわかります。
住所から郵便番号を導き出す方法は複数の方法があるようです。
住所を変換して郵便番号を取得する方法はありますが、一覧になっている場合、一つ一つ住所を変換するわけにはいかないですよね。
そこで、住所から自動的に郵便番号を導き出す方法をいくつか試したので、その中から一番簡単なやり方をご紹介します。
もうひとつ方法があったので、合わせて見ていただきます。使いやすい方法をチョイスください。
住所を変換して郵便番号を取得
最初に、住所を変換することで候補に郵便番号が出ることを知っていますか？まずは、その手順を見ていただきましょう。
始めに、テキストエディタで住所を入力します。60爺は「秀丸」でやってみました。
次に、この住所全体を選択しましょう。
ここで、「変換」を押下します。すると、候補が表示されます。
郵便番号が表示されているので、半角の郵便番号を選択します。これで、住所から郵便番号を取得できました。
でも、この方法では、エクセルで一覧の住所から自動的に郵便番号を導き出すことはできません！
住所を入れても、変換内容に郵便番号が表示されない場合は、以下の設定を行いましょう。
設定＞時刻と言語＞言語と地域＞オプション＞Microsoft IME ･･･キーボードオプション
⇒学習と辞書の中にある「郵便番号辞書」を「オン」にする。
エクセルで住所から自動的に郵便番号を導き出す方法
自動的に郵便番号を導き出す方法ですが、2つの方法があります！
それぞれ、住所から郵便番号を関数で自動入力する方法なんですが、使用する関数が異なります。
- WebAPI関数
- VLOOKUP関数
これらの方法には、当然、メリット、デメリットがあります。これらを一覧にしてみました。
| № | 使用関数 | メリット | デメリット |
|---|---|---|---|
| 1 | WebAPI関数 | ・準備が不要 ・最新の郵便番号データを使える | ・特定の住所の場合、その郵便番号は取得できない。 ・1日の利用件数の限度がある（1日1万件まで無料） ・Excel2013以降しか使えない |
| 2 | VLOOKUP関数 | 最新の郵便番号データを使える | ・郵便番号データの作成が必須で作業が大変 ・住所の番地は別のセルに作成しなければならない |
この中で、60爺が推薦するのは、断然№1の方法ですね。何の準備をすることなく、関数を投入するだけで郵便番号を持ってこれます。
しかも、住所に番地が入ったまま検索できるのも、おすすめする理由です！
各々の関数の使い方を解説します。
WebAPI関数
この方法は、何の準備も不要で、関数をセットするだけで郵便番号を取得できる超簡単なモノです。その手順を見てください。
初めに、excelに住所の一覧を作成します。
次に、セルB2へ次の関数を入力します。
=WEBSERVICE("http://api.excelapi.org/post/zipcode?address="&ENCODEURL(A2))入力後、Enterキーを押すと郵便番号がセットされます。
セルB2をコピーして、セルB3からセルB21へペーストします。
すると、セルB2～セルB21の郵便番号が設定されます。
関数を入れるだけで、住所から郵便番号を取得できました。簡単で手軽です。
但し、デメリットがいくつかあります。下記に詳細を示します。
- 一部の住所(北海道が多い)では郵便番号を返せない場合があり
上記のセルB9のように、郵便番号が空白で帰ってくる場合があるんです。提供元に確認したところ、「円田は、郵便番号が大字以降によって変わるため判定が難しく、このような住所は誤った郵便番号を返さないために空白を返す仕様」となっているそうです - 特定のビル固有、事業所固有の郵便番号については、汎用的な郵便番号を返します。
東京都千代田区霞が関2-1-2 総務省は本来1008926なんですが、霞が関2-1-2に当たる1000013が返ってきます。これでも郵便は届くのでご安心を - 負荷対策のため、1日1万件まで
- （WebAPI関数がある）Excel2013以降しか使用できない
個人で住所録を作成する場合は恐らく問題は無いと思います。特定のビル固有、事業所固有の郵便番号を持つ住所が多い場合は、多少考える必要があるかもしれません。
大人数を管理する場合、1日1万件という制限があるので、そこに抵触する場合はちょっと問題があるかもです。以下のサイトをご確認ください。
VLOOKUP関数
こちらの関数を使う方法は、日本郵便のWEBサイトから郵便番号データをダウンロードして変換表を作成する必要があります。
作成した変換表から郵便番号をゲットする方法ですね。ですから、事前の準備が必須です。
それでは、まず、VLOOKUP関数による郵便番号設定の方法を見た後、変換表の作成手順を解説します。
VLOOKUP関数による郵便番号設定
この方法は、変換表が出来上がっていることを前提としています。
■変換表のブック名：Vlookup_henkan.xlsx
■変換表のシート名：Sheet2
初めに、excelに住所の一覧を作成します。この時、番地以降は別のセルに作らねばなりません。
郵便番号を入れるセルC3に、次のVLOOKUP関数をセットします。
=VLOOKUP(B2,[Vlookup_henkan.xlsx]Sheet2!$A$2:$B$124575,2,FALSE)ここで、上述したように、「Vlookup_henkan.xlsx」は変換表のブック名、「Sheet2」は変換表のシート名です。
このVLOOKUP関数は、別ブックの別ファイルから郵便番号を取得しています。
入力後、Enterキーを押すと郵便番号がセットされます。
セルC2をコピーして、セルC3からセルC21へコピーします。
すると、セルC３～セルC21の郵便番号が設定されます。
こちらも、関数を入れるだけで、住所から郵便番号を取得できました。簡単で手軽です。
デメリットを述べておきます。
- 郵便番号変換表の作成が必須で作業が面倒くさい
以下に、変換表の作成方法を示してありますが、ちょっと面倒くさいですな！ - 住所の番地を同じセルに入れておくと、郵便番号を抽出できない。
WebAPI関数関数の場合は、番地を同じセルに入れておいても良かったのですが、こちらの場合はそうはいきません。このため、番地は別のセルに作成しなければならないのです。
vlookupは複数条件でも使用できますが、調べてみると正規の使い方ではないことが分かりました。詳細については、次の記事をご覧ください。
今回も採用していますが、vlookup関数は別シートにある変換表からも、必要な値を抽出できます。解説記事をご覧ください。
郵便番号変換表の作成
郵便番号変換表は、次の手順で作成します。
- 郵便番号データをダウンロード
- 郵便番号データの解凍
- 郵便番号データを開く
- 変換表の作成
郵便番号データをダウンロード
日本郵便の郵便番号データダウンロードページに行きます。
「住所の郵便番号(CSV形式)」の「読み仮名データの促音・拗音を小書きで表記するもの」をクリックします。
「都道府県一覧」の一番下にある「全国一括」をクリックします。もし、住所録が都道府県に限定できるなら、対象の都道府県をクリックしてください。
クリックすることで、ダウンロードが行われます。
郵便番号データの解凍
ダウンロードした「Ken_all.zip」は、パソコンの「ダウンロード」フォルダに保存されています。
「Ken_all.zip」をダブルクリックします。すると、csvファイルが現れます。
このファイルをコピーして、別フォルダに格納してください。
郵便番号データを開く
さて、csvファイルを開くんですが、単純に開くと、郵便番号の先頭に０がついたものが消えてしまうので、事前の準備が必要です。
まず、excelを立ち上げます。「ファイル」タブをクリックします。
メニューの一番下にある「オプション」をクリックします。
Excelのオプション画面から「データ」をクリックします。
表示されたメニューのうち、「レガシ データ インポート ウィザードの表示」にある「テキストからレガシ」にチェックを入れた後、右下にある「ＯＫ」をクリックします。
これで、excelから、csvを開く準備が出来ました。メニューの「データ」をクリック後、左端の「データの取得」をクリックします。
続いて、「従来のウィザード」をクリックし、表示される「テキストからレガシ」をクリックします。
「テキストファイルのインポート」画面が出るので、先程コピーしたcsvファイルを選択して「インポート」をクリックします。
テキストウィザード画面が表示されます。1/3では、「コンマやタブ･･･データ」をチェックして「次へ」をクリックし、2/3では「コンマ」をチェックして「次へ」をクリックします。
テキストウィザード 3/3画面が表示されます。左から3つの列を選択します（シフトキーとマウスで）。次に、「文字列」を選択すると、先程選んだ3列の見出しが「文字列」に変わります。「完了」をクリックします。
データの取り込み画面が出ますので「OK」をクリックします。
郵便番号のCSVファイルが開きます。
変換表の作成
変換表は、次の手順で作成します。
- 住所の文字列を結合
- 不要な列を削除
住所の文字列を結合
G列に県、H列とI列に市区村町名が記載されているので、これらの列を「”＆”演算子」を用いて一つの住所にします。
=G4&H4&I4
あとは、このセルを最後までコピペして出来上がりです。
不要な列を削除
今作成した住所の列は、別の列を作成して「値貼り付け」します。その後、郵便番号（C列）と住所以外の列を削除します。
今回は、住所から郵便番号を取得するので、列の順序は、住所、郵便番号にしなければなりません。
出来上がった変換表を保存します。変換表のブック名は、「Vlookup_henkan.xlsx」、変換表のシート名は、「Sheet2」にしてください。
郵便番号7桁に対して、自動的にハイフンを入れるやり方を確認しました。
最後に
エクセルで住所から自動的に郵便番号を取り出す方法はないか、そのやり方をご紹介しました。
基本的に関数を使うしかないのですが、2つの方法を示しました。
WebAPI関数を用いれば、何の準備もなく、一発で郵便番号を取得できるのでお勧めです。しかも、1日1万件以内なら無料ですし、なかなか良いと思います。
別に、変換表を自分で作る必要はあるものの、WebAPI関数を使えない方向けに、VLOOKUP館数を使用したやり方も示しています。
住所を打ち込めば、基本的に変換することで郵便番号を取得できますが、数が増えるとちょっとやっていられないんで、自動的な取得方法を示してみました。
皆様のご参考になれば。
■追記：エクセルをテーマに記事をいくつか書いています
60爺
60路を越え、RaspberryPi と出会い、その関係でブログ開設（2017/2～）となりました。始めてみると、コツコツやるのが性に合ってしまい、漢字の記事から家の補修・将棋・windows10関係・別名・言い方などジャンルを拡大して今に至ってます。まだまだ、元気なので新たな話題を見つけて皆様に提供できればと思っています。「プロフィールはこちら」