Introduction
Ver. all 2013 2016 2019 365
Excel（エクセル）のドロップダウン（プルダウン）入力で、リストを連動して絞り込む方法を説明します。
※複数のリストの連動が必要無い場合は、Excel（エクセル）でリストを作成して一覧からデータ入力する方法｜プルダウン、ドロップダウンリストをご覧ください。
例えば、下の表のように部署ごとに名前を入力したい場合の設定です。
「部署」をクリックすると、
用意されたリストから一覧が表示され、
例えば「企画部」を選択すると、
その選択から絞り込んで、さらなるリストを表示してくれるようにします。
手順は、
- リストとなる表を用意する
- 最初の場所のリスト入力を設定する
- 2か所目以降のリストに使用したい場所に名前を定義付ける
- 定義付けた名前を使用し、新たなリストをINDIRECT関数を使用して設定する
になります。
入力がさらにスムーズになるにゃー
設定方法
リスト用の表を用意する
まず、入力で使用するリスト用の表を作成する必要があります。
形は問いません。
今回は下の右側のような表を用意しますが、
結局、後でリストにする場所を選択して決めるので、行列が逆になっている、
このような形でも良いですし、1つ目のリストと2つ目のリストが離れていても、
問題ありません。
さすがに、それぞれのリスト部分はまとまっていないと困ります。
最初のリストを設定する
では、最初のリストを作成しましょう。
これからリストを設定したいセルを選択して、
［データ］ → ［データの入力規則］の上部分をクリック。
表示された［データの入力規則］ダイアログボックスで、［入力値の種類］の「▼」をクリックして、一覧から［リスト］を選択します。
［元の値］に、リストにしたいセル範囲を選択して［OK］。
最初の設定終了です。
2か所目以降のリストにする場所に名前を付ける
ここからが、今回のポイントになります。
少し面倒なのですが、絞り込むデータ範囲の場所に、それぞれ名前を定義する必要があります。
※名前の定義に関して詳しく知りたい方は、セル範囲に名前を定義する方法｜変更、削除方法や、重複した名前を登録する方法をご覧ください。
まず、製造部の場所を選択します。
選択ができたら、左上の［名前ボックス］に「製造部」と入力。
ここで注意事項です。
- リストに表示するデータ部分のみ選択する
- 定義する名前は、前のリスト一覧にある名前と同じにする
この2点を守らないといけません。
1番上の項目名まで入れて選択すると、その項目名までリストに表示されてしまいます。また、名前を最初に設定したリスト一覧にあるものと違う名称にしてしまうと、連動できなくなります。
この名前の定義付け作業を全てにする必要があります。次の「営業部」も、リストにしたい場所を選択して、［名前ボックス］に「営業部」と入力・・・。
全て名前を付けたら、確認します。［名前ボックス］の右側にある「▼」をクリックすると登録した名前の一覧が表示されます。
2か所目以降のリストを作成する
では、2か所目のリストを設定します。
「名前」の場所を選択して、
［データの入力規則］ダイアログボックスを表示して、［リスト］を選択。
ここからが大きなポイント。
［元の値］の場所は、INDIRECT関数を使用します。
INDIRECT関数・・・指定された文字列の参照を返す
=INDIRECT（参照文字列,参照形式［省略可］）
- 参照文字列・・・セル参照や、文字列、定義された名前を指定する
- 参照形式・・・［参照文字列］で指定したセル参照を「TRUE」か「FALSE」で指定する。省略するかTRUEの場合はA1形式、FALSEの場合はR1C1形式になる。
INDIRECT関数は、指定した文字列の参照を返します。この場合では、「B2」を参照に指定することで、「B2」で選ばれた文字列と一致した文字列（定義された名前）の参照（範囲）を返す（表示する）ということ。
※［参照形式］は特殊な場合を除き、R1C1形式を使用していることは無いと思いますので省略します。
まず、誤入力を防ぐために「ひらがな入力」は避けて、「半角英数入力」に切り替えましょう。
「＝indirect(b2）」と直接、手入力します。
アルファベットは小文字でかまいませんが全角にならないように。
※B2は後で下にコピーをすることを考えて絶対参照にはせず、相対参照のままにします。（＄記号は付けない）
[OK]をクリックすると、[元の値はエラーと判断されます。続けますか？］と表示されますが、そのまま［はい］をクリックしましょう。
参照する「B2」がまだ空欄なので、「大丈夫ですか？」と注意してくれているだけです。
※適当に「B2」に「部署」を選んで入れておけば、この画面は表示されません。
では、結果を確認です。「部署」に「総務部」を選んでみます。
隣の「名前」のセルの「▼」をクリックすると、
ちゃんと絞り込まれたデータが表示されました。
データが追加された場合に対応するやり方
先ほどの場合だと、選択リストのデータが追加された場合、毎回リストの範囲を登録し直す必要があります。
途中でリストが増加する場合に備える方法を2つ紹介します。
広めの範囲を選択しておく方法
1つ目は単純に、多めにリスト登録しておく方法です。
例えば、「製造部」を登録する際に、追加するかもしれない予定の範囲まで広めの選択範囲を定義します。
すると、広めにした分だけデータが追加できます。しかし、空欄の場所も常に一覧に表示されるのが少し気になります。
リストの最初に選択されている部分は中央付近になるので、あまり広い空欄場所を選択してしまうと上の選択肢が最初から表示されないのも困ります。
2・3件のデータしか増える予定が無い場合には有効でしょう。
テーブル機能を使用する方法
テーブル機能を使用すると、追加データへの対応は楽になります。
では、やってみましょう。
例えば製造部のリストを作成する場合は、見出しを含めてその場所を選択し、
［ホーム］ → ［テーブルとして書式設定］をクリック。
好きなデザインを選びます。
［先頭行をテーブルの見出しとして使用する］にチェックが入っているのを確認して［OK］。
すると、テーブルとしての登録が完了します。デザインが変更され、項目に「▼」が付きます。
次に、このテーブルに名前を定義します。
テーブルの中のセルをクリックしておくと［テーブルツール］の［デザイン］タブが表示されるので、［テーブル名］にリストとして定義したい「製造部」と入力します。
同じように全てのリストのテーブル名を定義すれば完了です。
このテーブル機能の良いところ。新しいデータを追加すると、
自動でテーブルとして登録した範囲が広がってくれます。もちろんリストにも反映されます。
登録に少し時間がかかりますが、1度設定しておけば、範囲の変更や定義の変更が必要無いのでおすすめです。
まとめ
今回は、一段階リストを絞ってみましたが、同じ作業を繰り返すことで、さらに候補を絞ることも可能です。
誤入力を防ぎ、スピードアップを図るために利用したい機能ですね。
少し複雑だけどなかなか便利な機能だにゃー