Excelで州・都市・郵便番号の階層ドロップダウンリストを作成する方法

住所を表現するには、通常「州」「都市名」「郵便番号」の3つの要素が必要です。1つの州には複数の都市が存在し、さらにその都市の下には複数の郵便番号エリアが紐づいています。本記事では、Excelで州 → 都市 → 郵便番号という3段階の階層構造を作成する方法を詳しく解説します。まず州を選択し、次にその州に属する都市を選び、最後に各都市の下にある郵便番号エリアを取得できるようにします。

階層テーブルとは？

階層テーブルとは、異なる要素同士の関係性を表現するための表です。どの要素が親（上位）で、どの要素が子（下位）にあたるのか、要素間がどのように関連しているのかをひと目で把握できます。

以下は、州・都市・郵便番号の階層構造を示した例です。

Excelで州・都市・郵便番号の階層を作成する手順

ここからは、Excelで州・都市・郵便番号の階層を作成する具体的な手順を、ステップごとに詳しく見ていきましょう。まず、5つの州からなるデータセットを作成し、このデータをもとに必要な情報を整理していきます。

⦿ ステップ1：データを準備する

このステップでは、州・都市・郵便番号の階層に必要なデータを準備します。

• まず、各州について5つの都市名を収集します。

• 続いて、ジョージア州の各都市に対応する郵便番号をいくつか収集します。

同じ要領で、他の州についてもデータを揃えていきます。

• フロリダ州のデータ。

• アラバマ州のデータ。

• カリフォルニア州のデータ。

• ハワイ州のデータ。

これでデータセットの準備が完了しました。

関連記事：Excelピボットテーブルで階層を作成する方法（簡単な手順）

⦿ ステップ2：データの入力規則で州のリストを作成する

階層を作成するために、Excelの「データの入力規則」機能を活用します。

• まず、階層リストを作成するための表を用意します。

• 次に、セルB5を選択します。
• [データ]タブから[データツール]グループを開きます。
• [データの入力規則]を選択します。

• [データの入力規則]ダイアログボックスが表示されます。

• [入力値の種類]のドロップダウンリストから[リスト]を選択します。

• [元の値]ボックスに以下の数式を入力します。

=Hierarchy!$B$5:$B$9

• 最後に[OK]ボタンをクリックします。

選択したセルの横にドロップダウンの矢印が表示されます。

• 続いて、Ctrl + CキーでセルB5をコピーします。
• その後、範囲B6:B9を選択します。

• [貼り付け]のドロップダウンを開きます。
• [形式を選択して貼り付け]オプションを選択します。

• [形式を選択して貼り付け]ウィンドウが表示されます。
• [貼り付け]セクションの[入力規則]にチェックを入れます。

• 最後に[OK]ボタンをクリックします。

他のセルにもドロップダウンの矢印が表示されていることが確認できます。

• 各セルのドロップダウンリストから選択肢を挿入しましょう。

関連記事：Excelで行の階層を追加する方法（2つの簡単な方法）

⦿ ステップ3：データの入力規則で都市リストを作成する

このステップでは、都市（City）列にデータの入力規則を適用します。

• セルC5を選択して、データの入力規則を適用します。

• 先ほどと同じ手順で[データの入力規則]ウィンドウを開きます。
• [元の値]ボックスに以下の数式を入力します。

=OFFSET(Hierarchy!B$11,1,MATCH($B5,Hierarchy!$B$11:$F$11,0)-1,5,1)

• [OK]ボタンをクリックします。

• データセットを確認してみましょう。

ドロップダウンリストとその選択肢が表示されています。この設定を範囲C6:C9にも展開しましょう。

• セルC5を選択します。
• Ctrl + Cキーでセルをコピーします。
• 次に範囲C6:C9を選択し、Ctrl + Alt + Vキーで形式を選択して貼り付けを実行します。
• [形式を選択して貼り付け]ウィンドウで[入力規則]にチェックを入れます。

• 最後に[OK]ボタンを選択します。

データの入力規則によるドロップダウンリストの選択肢が表示されるようになりました。

関連記事：Excelで多段階層を作成する方法（2つの簡単な方法）

⦿ ステップ4：郵便番号のドロップダウンリストを作成する

次に、郵便番号（Zip）列にデータの入力規則を適用します。この入力規則は、範囲C5:C9の値（選択された都市）に基づいて動作します。

• まずセルD5を選択します。

• 先ほどと同じ手順で[データの入力規則]ウィンドウを開きます。
• [元の値]ボックスに数式を入力します。

=OFFSET(Hierarchy!B$19,1,MATCH($C$5,Hierarchy!$B$19:$F$19,0)-1,5,1)

• [OK]ボタンをクリックします。

• データセットを確認し、ドロップダウンリストの下矢印をクリックしてみましょう。

リストに郵便番号が表示されます。同じ操作を範囲C5:C9内の残りのセルにも適用していきます。

• セルD6用のデータの入力規則の数式：

=OFFSET(Hierarchy!B$27,1,MATCH($C$6,Hierarchy!$B$27:$F$27,0)-1,5,1)

• セルD7用のデータの入力規則の数式：

=OFFSET(Hierarchy!B$35,1,MATCH($C$7,Hierarchy!$B$35:$F$35,0)-1,5,1)

• セルD8用のデータの入力規則の数式：

=OFFSET(Hierarchy!B$43,1,MATCH($C$8,Hierarchy!$B$43:$F$43,0)-1,5,1)

• セルD9用のデータの入力規則の数式：

=OFFSET(Hierarchy!B$51,1,MATCH($C$9,Hierarchy!$B$51:$F$51,0)-1,5,1)

• 改めてデータセットを確認してみましょう。

これで、ドロップダウンリストから郵便番号を選択できるようになりました。

まとめ

本記事では、Excelで州・都市・郵便番号の階層構造を作成する方法を解説しました。「データの入力規則」とOFFSET関数・MATCH関数を組み合わせることで、上位の選択内容に応じて下位の選択肢が自動的に切り替わる、使いやすい連動型ドロップダウンリストを実現できます。住所入力フォームや顧客管理表など、さまざまな場面で応用できるテクニックなので、ぜひ実際の業務でも活用してみてください。ご意見・ご感想があれば、ぜひコメント欄でお聞かせください。

関連記事

• Excelで階層チャートを作成する方法（3つの簡単な方法）
• Excelピボットテーブルで日付階層を作成する方法（簡単な手順）
• ExcelでSmartArtの階層図を使う方法（簡単な手順）