Excelで住所から郵便番号を自動入力する方法｜VLOOKUP関数・PHONETIC関数

住所データをExcelで管理しているとき、「この住所に対応する郵便番号を自動で入力できたら」と思ったことはありませんか？

PHONETIC関数を使えば、簡単・スピーディーに住所から郵便番号を返すことができます。また、VLOOKUP関数を活用することで、あらかじめ作成した対応表をもとに、入力した住所に対応する郵便番号を自動で取得したり、他の情報も検索したりすることができます。

本記事では、PHONETIC関数の使い方と、VLOOKUP関数の基本、住所と郵便番号の対応表の作成、実際の使い方、注意点を解説していきます。

PHONETIC関数（オススメ！）

PHONETIC関数は、入力時の読み情報を表示させる機能です。名前のフリガナなどを入力するときなどによく使われます。

この関数の便利なところは「打ち込んだ時の情報を表示している」点です。例えば「羽生」という苗字は「ハニュウ」や「ハブ」のように複数の読み方がありますが、この関数を使用すると、打ち込んだ時の読み方が表示されるため、誤った読み方をすることがありません。

そして実は、この機能を利用することで、簡単に住所から郵便番号を表示することができます。

使い方

まず住所を入力します。このとき日本語入力でセルに郵便番号を打ち込み、スペースキーで変換します。すると郵便番号が住所になります。ここで郵便番号から住所を打ち込んでおくことがこの関数を使う上で一番重要なポイントです。

次に郵便番号を表示したいセルに=PHONETICと入力し、参照に住所のセルを選択します。

これを実行すると、住所の読みとして郵便番号が簡単に出力されます。

なお、最初の住所入力の際に普通に住所を打ち込んでしまうとその読み方が出力されてしまうので、そこにだけは注意しましょう。

VLOOKUP関数とは

VLOOKUP関数は、Excelで特定の値に対応する情報を検索・取得できる便利な関数です。
「顧客名に対応する電話番号」や「商品コードから商品名を取得」といった場面で使われることが多いですが、これを応用すれば「住所から郵便番号」を返すこともできます。

検索値

リストから検索したい数値や文字列を入力します。今回の例では住所にあたります。なお、検索値は範囲の一番左に指定されている必要があるので、返す値を住所にしたいときや、他の用途で使いたいときは注意しましょう。

範囲

検索する範囲を指定します。このとき、先述したように、検索値の列は範囲の左端になるように指定してください。また、必ず返す値の列も範囲に含めましょう。

列番号

返したい値の列が、左端から数えて何列目にあるかを入力します。

検索方法

検索値を完全一致で取得するか、近似値で取得するかを指定します。完全一致なら「FALSE」、近似値なら「TRUE」を入力しましょう。住所検索の場合は完全一致で検索することが多いので、「FALSE」と入力して下さい。

VLOOKUP関数の使い方

対応表の作成

VLOOKUP関数を使うには、まず住所と郵便番号がペアになった「対応表」を用意する必要があります。以下の手順で日本郵便の公式データを取得し、Excelで使えるように整えましょう。

日本郵便からCSVデータをダウンロード

まず、こちらのページにアクセスして、住所の郵便番号（CSV形式）から読み仮名データの促音・拗音を小書きで表記するものを選択します。すると各都道府県分のデータと全国一括にまとめられたデータが用意されているので、希望のデータをダウンロードします。検索したい都道府県が決まっているならその都道府県のものを、そうでなければ全国版をダウンロードしましょう。

ここでは全国一括版を例にとります。全国一括をダウンロードすると、ken_allというZIPファイルがダウンロードされます。展開して、中にKEN_ALLというCSVファイルが入っていることを確認し、デスクトップなど適当なファイルに移動させてください。ZIPファイルの中に入ったままだとこの後の操作が上手くいきません。

データのインポート

Excelを開き、オプションの設定を変更します。オプションのデータの設定画面を開くと、レガシデータインポートウィザードの表示というメニューがあります。まず、その中のテキストから（レガシ）にチェックを入れましょう。また、その下のメニューに自動データ変換というのがあるので、その中の先頭のゼロを削除して数値に変換するからチェックを外しましょう。

新しいブックにデータをインポートします。データタブのデータの取得をクリックし、従来のウィザードを選択するとテキストから（レガシ）という選択肢が表示されます。それをクリックし、KEN_ALLを選択して開きます。

すると下図のようなウィンドウが表示されます。データのファイル形式はデフォルトのコンマやタブなどの区切り文字によってフィールドごとに区切られたデータでいいので、そちらが選択されていることを確認したら次へ進みましょう。

次に区切り文字を選択します。ここでは必ずコンマにチェックを入れましょう。コンマにチェックを入れたら次へ進みます。

次に列のデータ形式を確認します。データプレビューで先頭の0が消えずに表示されていれば、ここまでの作業に問題はありません。そのまま完了を押してください。

もし、0が消えているようなら、その列を選択して列のデータ形式を文字列に変更すると良いです。

最後にデータを返す先を選択します。これは既存でも新規でもどちらでも構いませんが、新しいブックにインポートしているのであれば既存のワークシートでいいでしょう。選択してOKをクリックしてください。これでインポートが完了します。

データの整形

今回は住所から郵便番号を返すためのリストを作るので、左に住所、右に郵便番号の2列の表を作っていきます。

＜住所＞

そのままのデータだと住所が、県、市、町村と列を分けて記入されています。それを結合した列を作成しましょう。
新しい列をJ列に挿入し、＝G1＆H1＆I1の式を立て実行すると県市町村が全て結合されます。この式をJ列の最後までコピーすれば簡単に住所の列が完成します。コピーするときは、セル右下の四角にカーソルを合わせてダブルクリックすると一気に下までコピーされます。

＜郵便番号＞

郵便番号にハイフンを入れます。新しい列を作成し、＝VALUEで文字列データを数式データに変更し、表示設定から郵便番号を選択し反映させましょう。
もしかしたら＃が並ぶような表示になるかもしれませんが、そうなっても次の作業で問題なくハイフン入りの郵便番号になるので、そうなっても気にしなくて大丈夫です。

＜必要な列だけ取り出す＞

新しいシートを開き、一番左の列に、先ほど作った住所をコピペします。住所はそのまま貼り付けで大丈夫です。
その隣の列に郵便番号をコピペします。ここで貼り付けるときに値と数式の書式を選んで貼り付けると、もし前のシートで#######となっていても、こちらのシートでは普通の数字とハイフンの表示になります。

後は必要に応じて、一番上の行に見出しを入れたり、住所で調整が必要なところを修正したりしてください。

これで対応表は完成です。

検索方法

VLOOKUP関数を利用して検索します。大抵の場合、別のExcelファイルで作った名簿などから検索したいと思われるので、そのように違うファイルの範囲から検索する方法を紹介します。

まずあらかじめ、郵便番号を入力したいセルの書式設定を郵便番号に設定しておきましょう。

そうしたら、郵便番号を入力したいセルに

=VLOOLUP（検索値,範囲,列番号,検索方法）

と入力します。（）の中は以下のように指定してください。

まず検索値は住所を入れる、入れた枠を選びます。

次に範囲を指定します。VLOOKUP関数を使っているExcelファイルを開いたまま、作ったリストのファイルに移動し、リストの列を範囲として選択します。すると自動的にリストのファイル名、シート、列の範囲が入力されます。

次は列番号を選択します。住所と郵便番号だけのリストであれば、郵便番号は左から2番目の列なので、2と入力しましょう。もし他にも列がある場合は、左から何番目に返したい値があるかを数えて、その番号を入力しましょう。

最後に完全一致か近似値のどちらかを選択します。文字列の検索のせいか、近似値で設定すると全然違う郵便番号を返してくることが多いので、ここは完全一致「FALSE」を選びましょう。

これで住所に対応した郵便番号を返す関数の設定が完了します。実行すると郵便番号が返されます。

注意点

VLOOKUP関数は便利ですが、いくつか注意が必要です。というのも、この方法はリストと検索値の文字列が完全一致していないと郵便番号が返ってこないため、リストの住所表記によっては値が返ってきません。

具体的には、番地の前半と後半で郵便番号が違う地域、ビルの階数で郵便番号が変わる地域、その他でくくられる地域など、元の日本郵便のリストで特殊な書き方をされている住所は、その部分のリストを丁寧に修正する、番地それぞれのリストを追加するなどしないと、エラーが返ってきます。

これを避けるには、検索するのに必要なリストの文字列を暗記しておく必要があり、現実的ではありません。また、リストの方を修正するにしてもこれらの修正は、その地域にかなり精通していないと難しいですし、手間もかかります。

そのため、エラーが返ってくる住所については、できる地域のみリストを修正し、残りは手打ちで対応するのが一番簡単かもしれません。

VLOOKUP関数活用例

VLOOKUP関数は住所と郵便番号だけでなく、以下のような場面でも活躍します。

• 顧客名から電話番号やメールアドレスを取得
• 商品コードから価格や在庫数を取得
• 従業員名から部署や入社日を表示

ポイントは、「検索値を左端に置く」「返したい列番号を正しく指定する」こと。これを守れば、様々なデータ管理に応用できます。

まとめ

この記事の要点は以下の通りです。

• 住所から郵便番号を返すだけならPHONETIC関数が便利
• VLOOKUP関数は大量のデータから検索した情報を返す関数
• 検索する言葉は対応表一番左側の列に
• 対応表は表記ゆれや特殊な地域分類などに注意し、必要に応じて手修正を行う
• VLOOKUP関数は住所と郵便番号だけでなく他の情報管理にも非常に便利

PHONETIC関数やVLOOKUP関数を使って、住所と郵便番号の整理を簡単にしましょう。