エクセル(Excel)で2つのプルダウンリストを連動しよう♩+INDIRECT関数

エクセル(Excel)で2つのプルダウンリストを連動しよう♩+INDIRECT関数

Yurie Sasaki

Yurie Sasaki

こんにちは、管理部のゆりえです(´-`*)
だんだんと涼しくなってきましたね、いかがお過ごしでしょうか。季節の変わり目は体調を崩しがちなので休養をしっかり取りましょう。

さて、インターネットで何かの申し込みフォームを入力するとき、住所の欄で「都道府県」を1つ選択すると、となりにその県の市区町村の選択肢が自動で出てくることってありますよね。
これ、すごいなー。便利だなー。エクセルのプルダウンでも同じことがやりたいなーと思い、見つけたやり方です。

上の記事では、「プルダウンリスト」の設定方法を説明いたしましたが、今回はそれを少し応用してみたいと思います。

▼エクセルはもっと便利にできる



2つのプルダウンリストを連動させる

動的に選択肢を出すなんて、難しい数式が必要なんじゃない?と思うかもしれませんが、そんなことないんです!
以前紹介した「名前」という機能を使って、簡単に2つのプルダウンリストを連動させてみましょうー。

1. 表を作成する

たとえば、こういう感じの表を作成するとします。

エクセルの表の画像

この表のなかで、「ユニット(職種)」と「担当者」の2か所をプルダウンで選びたい! 選んだユニットと連動して、担当者の選択肢が変わるようにしたい!とします。

2. リストの元データに「名前」を付ける

まず、選択肢の元となるリストを作ります。

名前を記載したエクセルの表の画像

例の場合ですと別シートで作成していますが、同じシート内でも大丈夫です^^
タイトルを付けて表形式にすることで、編集をする場合や他の人が見る場合にわかりやすくなります。

1つ目のユニットを枠で囲んだエクセルの表の画像

ここで活躍するのが前回説明した「名前」の機能です。
プルダウンリストは、「名前」を使って選択肢を設定すると便利なので、必要なリスト毎に名前をつけます。
まずは1つ目の「ユニット(職種)」リスト。A1:D1のユニット名の部分を選択し、エクセルエリアの左上にある「名前ボックス」に「ユニット」と入力し、範囲に名前をつけます。

1つ目のユニットを枠で囲み「選択範囲から作成」が表示されたエクセルの表の画像

次に、2つ目の「担当者」リストは、「ユニット」のリストの数だけ「名前」を設定します。今回は4つです。
まずは「ディレクター」です。A1:A10を選択し、「数式」タブ⇒「定義された名前」の「選択範囲から作成」⇒上端行にチェックが付いた状態で「OK」で完成です。

A2:A10に「ディレクター」という名前が付いたエクセルの表の画像

A2:A10に「ディレクター」という名前が付きました。

「名前の管理」画面が表示されたエクセルの画像

同様に、B1:B10、C1:C10、D1:D10をそれぞれ選択し、1行目のユニット名をつけていきます。前回も紹介しましたが、「名前」機能の便利なところは、タイトルの入力をすることなく勝手に名前をつけてくれるところです。便利ですねー。
付けた「名前」たちは、「数式」タブ⇒「名前の管理」で出てくるダイアログで確認・編集することができます。

3. プルダウンリストを設定する

さて、いよいよメインの表作成にうつります。

「データの入力規則」画面が表示されたエクセルの画像

B列の「ユニット」欄には、メンバーの所属ユニットをプルダウンリストから選択できるよう設定します。
B2を選択し、リボンの「データ」タブ⇒「データの入力規則」⇒表示されたダイアログにて、「入力値の種類」で「リスト」を選択し、「元の値」の部分には「=ユニット」と入力します。

B列にプルダウンリストが設定されたエクセルの表の画像

これで、B列のプルダウンリストには、「ユニット」という名前の範囲にある4種類のユニット名が出てくるようになりました。

「データの入力規則」画面のエクセルの画像

次に、「担当者」欄では、選んだユニットによって、そのユニットに所属する個人名がプルダウンリストに出てくるようにします。
今回の目的である、担当者を動的にするために、プルダウンの設定も動的にしましょう。

まず、C2のセルにプルダウンリストを設定します。
このとき、「データの入力規則」ダイアログのなかで、「入力値の種類」は同様に「リスト」を選択し、「元の値」には何も考えずに「=INDIRECT(B2)」と入力します。それだけ。ただそれだけです。

B2でディレクターを選んだ場合

B2で選んだユニットに連動して、C2ではそれぞれ所属している担当者だけがプルダウンリストで選べるようになりました。簡単ですねー。

C2で所属している担当者の名前をプルダウンリストから選択しているエクセルの表の画像

B2でエディターを選んだ場合

B2で所属している担当者の名前をプルダウンリストから選択しているエクセルの表の画像

4. プルダウンリストの設定をコピー

これでプルダウンリストの連動が完成しました。あとはB2のセルをB3~B11へ、C2のセルをC3~C11へコピーすれば完成です!

C5で所属している担当者の名前をプルダウンリストから選択しているエクセルの表の画像

これで完成です! もう、ものすごく簡単ですね!
※実際に使うときは、「B2」は今回の場合ですので、「=INDIRECT(xx)」のxx部分に、連動させたい1つ目のプルダウンの住所を記入してください。

おまけ:INDIRECT関数を使う理由

さて、これでプルダウンリストの連動はマスターしました。ここまでの設定を覚えれば問題はないのですが、なぜこんな関数を使うの?と思った方もいると思います。

C2のプルダウン設定のお話に戻りますが、プルダウンリストを連動させるには、「元の値」のところに表示させたいリストの「名前」を入力すればいいわけです。
ユニット欄のB2で選択したユニットが、そのユニットメンバーリストの「名前」とイコールなので、「元の値」欄に動的になるような記入をすれば、選択肢が動的になるはずですね。
では、なぜ、単純に「=B2」と入力しないのでしょうか?

「データの入力規則」画面が表示されたエクセルの画像

「元の値」欄に「=B2」と設定してみましょう。

C2で所属している担当者の名前をプルダウンリストから選択しているエクセルの表の画像

B2で「デザイナー」を選択したら、C2の選択肢はデザイナー7人の名前……ではなく、「デザイナー」という1択になってしまいました。

なぜだーーー。
私はこの壁に激突したとき、困り果ててしまいました。
「元の値」に「=デザイナー」と入力すると、ちゃんと7人の名前が出てきます。つまり、プルダウンの設定において、「名前」機能を使うことに問題はないのです。
しかし、「=B2」(B2=”デザイナー”)とすると、きちんと「名前」の範囲を読み込んでくれません。エクセルは、直打ちの「デザイナー」は「名前」として認識するけれど、読み込みの「デザイナー(=B2)」は「B2の値である”デザイナー”」として認識してしまうようで、「名前」として認識してくれないのです。困ったなあ。

そこで、いろいろ調べてたどり着いたのが、今回の「INDIRECT関数」です。INDIRECT関数は、指定される文字列への参照を返す関数です。この関数を使うことにより、「=INDIRECT(B2)」がきちんと「デザイナー」として認識され、その「名前」がついたリストを表示することができるようになりました。

参考:INDIRECT 関数
https://support.office.com/ja-jp/article/INDIRECT-%E9%96%A2%E6%95%B0-474b3a3a-8a26-4f44-b491-92b6306fa261




さいごに

2つのプルダウンの連動方法についてまとめましたがいかがでしたでしょうか。思ったより簡単な設定でできるので、ぜひ参考にしていただければ幸いです。

エクセルたのしい(´-`*)

ありがとうございました。

エクセルに関する記事はこちら

この記事のシェア数

千葉大学を卒業後、広告のコンサルティング企業にて広告システムの開発、新規事業の立ち上げ、メディアのディレクションなどを経て、2015年にLIGに入社。人事部の発足や、経理・総務・秘書などバックオフィス全般を担いながら、数々の新規事業の立ち上げに助力。現在は経営企画として、スムーズな経営活動がおこなえる環境づくりを目指している。

このメンバーの記事をもっと読む