エクセルのドロップダウンリスト(プルダウンリスト)を4段階(4階層)連動させる方法(365,2019,2016,2013,2010,2007対応)

スポンサーリンク

以前、エクセルのドロップダウンリスト(プルダウンリスト)で、2007以前のエクセルでは、別シートの参照元を名前で定義しないとリストを取得できないという記事を投稿しました。

Excelで入力規則のリストボックス(プルダウンリスト)が表示されない?バージョン違いによる問題はリスト範囲に名前をつけることで解決! Offset関数を利用してを可変リストも作成可能 プルダウン可変式リストは超便利!
久しぶりのOffice(オフィス)記事です。先日、Excel(エクセル)365では、入力規則に設定されたリストボックス(ドロップダウンリスト)が表示されるのに、Excel2007では入力規則がなくなっているとの指摘を受けました。...

今回は、結構需要が高いと思われる、ドロップダウンリストを選択することで4段階連動させる方法の紹介です。

ネットで検索しましたが、4連動を紹介している記事は見つかりませんでしたので、今回記事にしました。

スポンサーリンク

4段階ドロップダウンリストの作成

完成イメージはこちらです。「都道府県」から選択し、「支店」・「部署」・「氏名」を順にドロップダウンリスト(プルダウンリスト)で4連動で絞込選択する方法です。

4段階連動ドロップダウンリスト

4段階連動させるためにシートを複数作成

ここがドロップダウンリストを4連動させるために必要な所です。データを別シートに作成します。今回は、3都道府県、「東京」「大阪」「愛知」を作成しました。

「東京」シート
「大阪」シート
「愛知」シート

1段階目のドロップダウンリストの作成

まず、都道府県を選択する範囲、ここでは「A2:A7」セルを選択して、「データ」「データの入力規則」をクリックします。

データの入力規則

1段階目はリストを手入力で

今回のサンプルデータでは、都道府県名を3つにしましたので、手入力にしています。

「データの入力規則」で「入力値の種類」を「リスト」に、「元の値」に「東京,大阪,愛知」とコンマ(,)区切りで入力します。

コンマ区切りでリストを入力

リストがたくさんある場合は別シートを利用することも可能

リストがたくさんある場合は、コンマ区切りでは大変ですから、下図のように「都道府県」シートを作成してリストを作成することも可能です。その場合は、リストに名前を付ける必要があります。

名前を定義

セル範囲に名前を定義する

下の例では、「A1」セルから「A3」セルまでのを選択して、名前ボックスに名前を入力することで名前の定義をします。「名前ボックス」に「都道府県」と入力してEnterキーで名前を定義することができます。

「都道府県」名前の定義の完了

「名前ボックス」に「都道府県」と入力されました。これで名前の定義は完了です。名前の定義は結構簡単です。

セル範囲に名前を定義する方法は、この後も利用します。簡単なので覚えておいてください。

1段階目の都道府県を名前の定義にした場合は、「データの入力規則」の「元の値」は「=都道府県」と入力します。

元の値を定義した名前に
Excel(エクセル)プルダウンリスト(ドロップダウンリスト)を可変式リストにすればリストの項目が増えても大丈夫! リストを名前の定義とテーブルにする2つの方法
データの入力規則で、「元の値」をリストにして、プルダウンリスト(ドロップダウンリスト)を利用している方は多いと思います。「元の値」のリストは、Excel2007以前では、セル範囲での別シートを参照することができません。ですから参照...

第1段階目リストボックス完了

コンマ区切りのリストでも、名前の定義によるリストでも、第1段階のリストボックスが完了しました。

第1段階リストボックス完了

2段階は1段階目と連動させる「INDIRECT」関数を利用

2段階目は1段階目(都道府県)によって、リストの参照を自動で変更するように設定します。

具体的には、「INDIRECT」関数を利用します。INDIRECT関数とは、参照されたセルの値を返します。「B2」セルから「B7」セルを選択し、「データの入力規則」をクリックします。

A列で選択された都道府県に対応する支店を選択する

データの入力規則の「元の値」に、「=INDIRECT(A2)」と入力して「OK」ボタンをクリックします。これで、A列で選択された都道府県名(東京、大阪、愛知のいずれか)の名前をリストにすることができます。

INDIRECT関数でリストを可変する

次に各都道府県の支店名のリストに「東京」「大阪」「愛知」の名前の定義します。

リストがとびとびにならないように工夫が必要

「A1」~「I1」セルを選択して「東京」という名前を定義すると、リストがとびとびになってしまいます。

セルを結合しているので空白の欄がある

こんな感じになります。

リストに空白が入る

第2段階目もリストは手入力に

そこで、各都道府県のシートのK列(どこでも可)に、各支店名を入力し、下図では「東京」と名前を定義しています。下図では関数が入っていますが、複雑になるのでここでは割愛します。また機会があればご紹介するかもですが。

支店名を手入力で

同様に大阪・愛知もK列から横に支店名を入力し、それぞれ「大阪」「愛知」という名前で定義をします。

支店名を「大阪」という名前を定義

以上で第2段階目まで完了です。

ドロップダウンリストが連動された
VBAを利用せずリストを予測候補表示(サジェスト機能)で絞り込む方法!エクセルのドロップダウンリスト(プルダウンメニュー)は便利なようで結構めんどい。。。
エクセルのドロップダウンリスト(プルダウンリスト)って、便利なようですがリストが増えると選択するのが大変なんですよね。入力候補が多数あると、入力したい値を探すのに一苦労です。入力規則があるとデータが多いときに選択が大変...

3段階目からはデータがあれば簡単に連動可能

3段階目、部署です。こちらも名前を定義するのは大変ですが、方法は簡単です。下図のように「A2」~「C2」を選択して、都道府県名+支店名を名前として定義していきます。例えば、「東京丸の内」「東京赤羽駅前」「東京新宿通」と都道府県名+支店名にするのがポイントです。それぞれ、東京、大阪(大阪+支店名)、愛知(愛知+支店名)で名前を定義していきます。

都道府県名+支店名で名前を定義

名前を定義後、「データ」シートで入力規則を設定します。3段階目では「C2」~「C7」セルを選択し、「データの入力規則」をクリックします。

3段階目 部署の入力規則の設定

3段階目での「元の値」は「=INDIRECT(A2&B2)です。先ほど都道府県名+支店名の名前を定義した理由が分かりましたでしょうか。A2セルには都道府県名、B2セルには支店名が入力されていますので、3段階目はINDIRECT関数で都道府県名+支店名の名前をリストにしています。

「元の値 はエラーと判断されます。続けますか?」と出ますが「はい(Y)」をクリックします。

3段階目、連動完了しました。「C2」のセルのリストは「東京新丸の内」と定義された範囲をリストボックスに表示させています。

いよいよ4段階目 名前の定義が大変

最後に4段階目の設定です。名前の定義が大変ですが、それぞれの都道府県の支店の部署の下にある人物名を選択し、「都道府県名+支店名+部署名」を名前に定義します。下の例では、「A3」~「A5」までを選択し、名前を「東京丸の内営業」とします。大変ですが、それぞれ「東京新丸の内総務」から、「愛知豊田経理」まで名前の定義をします。

すべての名前の定義を完了後、「データ」シートの「D2」~「D7」を選択し、「データの入力規則」をクリックします。

4段階目の「データの入力規則」の「元の値」は「=INDIRECT(A2&B2&C2)」とし、INDIRECT関数によって「元の値」はそれぞれ選択した、都道府県名+支店名+部署名となります。

以上で完成です。下図をご覧ください。

ドロップダウンリスト(プルダウンリスト)は5段階以上も可能だが

INDIRECT関数を利用して、名前の定義を工夫すればドロップダウンリストは5段階以上も可能です。かなり複雑になりますが、基本は名前の定義とINDIRECT関数で文字列をつなげていくことにより可能となります。

リストはこんな表になると思いますが(笑)。

INDIRECT関数で何段階でも連動可能!

今回の記事でご不明な点があればコメントをいただけたらありがたいです。

スポンサーリンク
Office・Windows
スポンサーリンク
リーダーの独り言

コメント

タイトルとURLをコピーしました