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

2021年11月6日

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

自動的に住所を検索して表示する方法を紹介します。

手順

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

郵便局の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関数XLOOKUP関数で検索されるための列を作ります。

CONCAT関数で同じ行のI~K列を範囲指定するだけです。

もし関数が実行されない場合、列の表示形式が文字列になっているため標準に戻しましょう。

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

なおシート名はそのままでもいいですが、判り易くするために「対応表」としています。

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

なお先頭行にはカラム名を都道府県名、市区町村名、町域名と付けています。

データインポートの際、自動的にテーブルに変換され構造化参照という状態になります。

これにより、いつもの列表記ではなく@[都道府県名]のような列名表記になります。

更に2行目に数式を入れると自動的に一番下まで数式がコピーされます。

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

_01HOKKAIはインポート時にCSVファイル名から自動的に付与されたセル範囲名です。

検索するシートを作成

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

ここは先頭0を許容するため先ほどと同様、書式設定で文字列にします。

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

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

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

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

検索範囲は検索値から表示項目を含んだ対応表の列C:Eを指定します。

次に引数の列番号は、住所を表示するセルは2、カナを表示するセルには3を指定します。

これは引数2の左端を1とした連番です。最後のFALSEは固定です。

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

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

Excel完全マスター: 関数・テクニック・新機能を網羅した決定版ガイド

本書は、Excelの基礎から応用まで、包括的に学べるガイドブックです。当サイトの豊富な情報を、電子書籍向けに最適化し、読みやすく再構成しました。

【本書の特徴】

  • 情報量:Word-A4サイズ換算で1,400ページ相当
  • 基本操作から高度なテクニックまで、段階的に学習可能
  • 140種類の関数を詳細に解説
  • 109種類の実用的なテクニックと具体例を紹介
  • 最新機能(XLOOKUP関数、LET関数、シートビュー、LAMBDA関数、スピル等)を徹底解説

【対象読者】

  • Excel初心者からプロフェッショナルまで
  • 体系的にExcelスキルを向上させたい方
  • 業務効率化を目指すビジネスパーソン
  • データ分析や可視化のスキルを磨きたい方

【本書の強み】

  • 実務に即した例題と解説
  • 視覚的な図表やスクリーンショットで理解を促進
  • 最新のExcelバージョンに対応した内容
  • 著者の長年の経験に基づく、実践的なTipsを多数収録

【更新とサポート】

  • 常に最新の情報を反映するため、定期的に内容を更新
  • 最新版の入手方法:Amazonカスタマーサポートへお問い合わせください

【入手方法】

  • 買い切り:950円
  • Kindle Unlimited:読み放題プランで利用可能

Excelマスターを目指す方、業務効率を劇的に向上させたい方に、自信を持っておすすめできる一冊です。この1冊で、Excelの可能性を最大限に引き出す力が身につきます。

関連記事

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