Excel使いこなしの基礎と応用


郵便番号表とVLOOKUPで郵便番号から住所1を自動表示する

かなモードで郵便番号を打ち込み、変換すると住所の前半部が表示されるのは広く認知されています。ですが、ここでは郵便番号を半角で打ち込み、日本郵便で配布している郵便番号データからVLOOKUP関数を使って住所を呼び出す方法を採ります。

このページの目次


住所録の作成プロセス


日本郵便で配布の郵便番号データをダウンロードしてカスタマイズ

郵便局のサイトで郵便番号のデータベースがダウンロードできます。こちらの全国版ローマ字のフリガナ版をダウンロードして適宜カスタマイズして使います。「使用・再配布・移植・改良について:郵便番号データに限っては日本郵便株式会社は著作権を主張しません。自由に配布していただいて結構です」とありますので安心して使って大丈夫。(当サイトの住所録にはカスタマイズした郵便番号データを添付してありますので、ご自由にお使い下さい

スポンサーリンク

ダウンロードした郵便番号データを解凍し、Excelで開き、不要な列を削除するか非表示に、不要な文字列やスペースをアスタリスク(*)を使った置換などで削除しておきます。この作業はやり過ぎないことが肝要です。特に行の削除はやらないこと。郵便番号と住所の整合が崩れる危険性があります。
郵便番号データのカスタマイズ
カスタマイズが済んだら、全体を選択してコピー。「住所録」のブックに新しいシートを追加してそこにペーストしておきます。
住所録と同じブックの別シートにコピペ
シート名を「郵便番号表」とし、A列~E列までの範囲にも名前を定義しておきました。
郵便番号データA列~E列に名前を定義

※注意:この郵便番号データは公共施設など事業所の住所には対応していません。事業所のデータは別に配布されていますので、公共・事業所用の住所録をプライベート用とは分けて作成されることをお勧めします。

VLOOKUP関数で住所1を自動表示

住所1」の最初のセルを選択して下さい。
住所1の最初のセルを選択
VLOOKUPの数式は未入力のセルにエラー値が表示されないようIFERROR関数にネストして使いますので、最初に「関数ライブラリ」→「論理」→「IFERROR」のダイアログを呼び出し、名前ボックス▼から「VLOOKUP」をクリック。(名前ボックスに見当たらなかったら「その他の関数」を選択、ダイアログで分類を「検索/行列」にしてVLOOKUPを探して下さい)
IFERRORのダイアログが開いたら、名前ボックスでVLOOKUPをクリック
VLOOKUPのダイアログボックスが開きます。▶VLOOKUP関数についてはこちらも参照
「検索値」:郵便番号の最初のセルをクリックして、[郵便番号]と入力
「範囲」:郵便番号データのA~E列に前章で定義した名前、〒住所と入力
「列番号」:住所1に表示するE列は左から何番目の列? で、5と入力
「検索方法」:FALSE(完全一致)=0、TRUE(近似値含む)=1 選択 0と入力
VLOOKUPのダイアログで全ての引数を入力
引数の入力が終わったら、「OK」は押さず、数式バーの「IFERROR」をクリックしてIFERRORのダイアログに戻り、「エラーの場合の値」にダブルクォーテーションを続けて2つ「""」(空の文字列という意味)入れます。これで「OK」。
エラーの場合の値にダブルクォーテーションを続けて2つ入力
数式は「=IFERROR(VLOOKUP([郵便番号],〒住所,5,0),"")」。「もし、郵便番号のセルに「〒住所」という名前の範囲の左端の列から検索した完全一致の値があれば左端から5番目にある列の値を表示する。もし、検索値がなければ空欄のままにする

スポンサーリンク

郵便番号は半角でハイフン無しで打ち込みます。表示形式はあらかじめ郵便番号仕様になっていますのでハイフンは自動で付きます。適正に「住所1」が表示されるか確認しましょう。
郵便番号を打ち込んで住所の表示を確認

住所2を入力と同時に住所1+住所2で印刷用住所1を表示

「住所1」に表示されるのは住所の前半部。残りの後半部は「住所2」に手入力します。○丁目○番~に当たる「1-2-3」のようにハイフンで繋いだ箇所ですね。ここは建物名などを入れなければ半角英数字で入力します。そして、Wordの差し込み印刷と連携させるため、「住所1」「住所2」CONCAT関数で連結させて「印刷用住所1」に表示させます。

前章のVLOOKUPと同じく、エラー値が入るのを避けるためIFERROR関数に入れ子します。「印刷用住所1」の最初のセルを選択し、「関数ライブラリ」→「論理」→「IFERROR」のダイアログを呼び出し、名前ボックス▼から「CONCAT」をクリック。(名前ボックスに見当たらなかったら「その他の関数」を選択、ダイアログで分類を「文字列操作」にしてCONCATを探して下さい)文字列の結合について

CONCAT関数のダイアログボックスが開いたら、「テキスト1」「テキスト2」にそれぞれ「住所1」「住所2」を指定し、
CONCAT関数のテキストに住所1と住所2を指定
数式バーのIFERRORをクリックして戻ります。「」の数式を確認し、「エラーの場合の値」にダブルクォーテーションを続けて2つ「""」(空の文字列という意味)入れて「OK」。
IFERRORのダイアログに戻り、数式を確認、エラーの場合の値に""を入力してOK
数式は「=IFERROR(CONCAT([@住所1],[@住所2]),"")」「住所1と住所2を結合して表示する。もし不備があれば空欄のままにする

これで「住所2」を入力すると同時に「住所1」と結合されて「印刷用住所1」に自動表示されるようになります。
住所2の入力と同時に印刷用住所1が表示
※ 「住所2」の入力後、セルの左上角に緑色の三角が出現するかもしれません。これはエラー警告のマークで無視しても構いませんが、気になるようなら▶左上角の緑色▼エラーを今消したい時の解決法を参照して下さい。

最後に「印刷用住所2」の役割が解りにくいかもしれないので補足説明しておきます。

スポンサーリンク

下図は年賀はがきの宛名面の印刷プレビューです。
Wordの差し込み印刷の宛名面プレビュー
住所1」と「住所2」を連結させたデータが「印刷用住所1」に入り、実際に宛名印刷で呼び出すのは「印刷用住所1」になります。通常はこれだけで完結しますが、建物名などが長く、1行に収まりきらない場合は2行に分ける必要が出てきますね。この2行目に表示される部分を「印刷用住所2」に入力するわけです。

ですから、「住所2」と「印刷用住所2」を入力する時は、宛名印刷の住所を1行に収めるか2行に分けるかを意識しながら作業することになります。
印刷で宛名の住所を1行にするか2行に分けるかで入力が違う

住所録はこれでほぼ完成です。▶次は、完成した住所録のダウンロードと使い方

Officeで作れるもの