Aさん 大項目/小項目のような関連性のある複数列でドロップダウンリストを使う場合、大項目に応じて小項目の選択肢を変えたいです。。
現状、小項目は全大項目の選択肢が出てしまって選ぶのが大変ですし、誤選択してしまうこともあるので・・・。
何か対策となるテクニックはありますかね？
森田 ドロップダウンリストを階層構造にしたいということですね。
そんな場合、ドロップダウンリストの「元の値」を複数テーブルで整理した上で、「データの入力規則」と関数の「INDIRECT」を組み合わせれば実現可能ですよ！
では、詳細を解説していきますね。
はじめに
この記事はドロップダウンリストとテーブルを連動させるテクニック（「データの入力規則」+INDIRECT+テーブル）を把握していることが前提です。
ドロップダウンリストとテーブルを連動させるテクニックの詳細は、以下の記事をご参照ください。
はじめに この記事はドロップダウンリストの設定方法とテーブルを把握していることが前提です。 参考記事 「データの入力規則」でドロップダウンリストを設定する方法とテーブルの詳細は、以下の記事をご参照ください。 「元の値」が …
ドロップダウンリストを「階層化」することで誤選択の可能性を下げる
実務では、本部/部/課といった部署情報や、大項目/小項目のような階層的なデータを扱うことは多いもの。
加えて、上位階層によって下位の階層のデータが異なることが一般的です。
こうしたデータに対し、通常のドロップダウンリストでは、下位の階層の選択肢が上位階層すべての一覧となり、選択が煩雑、かつ実際に存在しない組み合わせパターン（「A本部」なのに「B1部」を選択等）を誤選択するリスクもあります。
こうした場合、ドロップダウンリストを階層化し、下位の階層の選択肢が上位階層によって可変するように設定すると良いです。
この設定は、ドロップダウンリストの「元の値」を複数テーブルで整理の上、「データの入力規則」の「元の値」に関数の「INDIRECT」を活用すれば実現可能です。
上位階層の選択肢ごとにテーブルを用意し、INDIRECTを活用すれば、ドロップダウンリストの階層化が可能！
今回は、社員マスタの「本部」と「部」の2階層を例にした設定内容です。
まず、ポイントとなるのはドロップダウンリストの「元の値」となるテーブル群の事前準備です。
次のように、1階層目（本部）の選択肢（部）ごとに1列のテーブルを作成し、横に並べます。
テーブルは同一シートに複数作成可能。
そして、各テーブルはフィールド名と一致させましょう。
不一致の場合、2階層目のドロップダウンリストへ連動しない。
後は、各階層のドロップダウンリストを設定していきましょう。
1階層目の「元の値」は、先ほど準備した2階層目の各テーブルのフィールド名部分を参照します。
2階層目の「元の値」は、INDIRECTの数式を入力し、隣のセル番地を参照します。
複数セルにまとめてドロップダウンリストを設定する際は、一番上のセルを基準に相対参照でセットしましょう。
今回であればD2~D11セルの範囲にまとめて入力規則を設定するため、D2セルを基準に参照セル（左隣のC2セル）を指定するイメージです。
これにより、D3セル以降の入力規則（INDIRECT）の参照セルはレコードごとにスライドし、同じ行のC列のセルを参照します。
これで、1階層目の値に応じて2階層目のドロップダウンリストの選択肢が連動するようにできました。
なお、注意点としては、一度2階層目の選択入力を終えた後に、1階層目の選択肢を変更すると、1階層目と2階層目の組み合わせパターンに矛盾がある場合があります。
この方法も完璧に制御できるわけではないことに留意の上、運用に役立ててください。
相対参照の詳細は、以下の記事をご参照ください。
関数の数式を直接入力できるようになってきました！ もっと関数を実務で役立てていくために、他に覚えておいた方が良いことはありますか？？ …
【参考】3階層の場合のテーブル準備例
前述は2階層の場合の例でしたが、3階層以上にする場合、上位階層の選択肢ごとにテーブルを準備しましょう。
本部/部/課という3階層であれば、次のテーブルを用意するイメージです。
- 1階層目（本部）の選択肢（部）ごとに1列のテーブルを作成
- 2階層目（部）の選択肢（課）ごとに1列のテーブルを作成
なお、上図では、1階層目のドロップダウンリストの参照をしやすくするため、本部のテーブルも追加で用意しています。
2階層の場合の事前準備でも1階層目のテーブルを準備してもOK。
後は、2階層の時と同じようにドロップダウンリストの「元の値」を設定しましょう。
なお、3階層目のドロップダウンリストの「元の値」もINDIRECTの数式を使いますが、参照セルを左隣のセル（2階層目の値）にすればOKです。
ちなみに、理論上は4階層以上も可能ですが、階層が増えると事前準備や入力、メンテナンス等のそれぞれが大変になるため、基本は2階層までを推奨します。
3階層以上になる場合、VLOOKUP等で特定の値を基準にマスタから複数列をまとめて転記する方が楽ちんです（例：部署コードを入力し、本部~課を転記させる等）。
関数のVLOOKUPの詳細は、以下の記事をご参照ください。
はじめに この記事は関数の概要を把握していることが前提です。 参考記事 関数の概要の詳細は以下の記事をご参照ください。 別表からのデータ転記を自動化したい場合は「VLOOKUP」が有効 実務でデータ集計/分析を行う際、1 …
【参考】階層構造のドロップダウンリストをメンテナンスするには
階層構造のドロップダウンリスト設定後のメンテナンスは、ケース別に以下の通りとなります。
2階層のドロップダウンリストが例。
| # | ケース | 「元の値」の参照範囲（入力規則側） | 「元の値」で参照しているテーブル側 |
|---|---|---|---|
| 1 | 1階層目の選択肢の名称変更 | - | 該当の2階層目のテーブル名・フィールド名を変更 ※1階層目のテーブルがある場合は、その該当レコードの値の変更も必要 |
| 2 | 1階層目の選択肢追加 | 参照範囲を修正（追加したテーブルのフィールド名のセルを含む） ※1階層目のテーブルがあり、INDIRECTで連動させている場合は、対応不要 |
追加する選択肢（2階層目）のテーブルを追加（新規作成）+テーブル名設定 ※1階層目のテーブルがある場合は、そのテーブルのレコード追加も必要 |
| 3 | 1階層目の選択肢削除 | 参照範囲を修正（削除したテーブルのフィールド名のセルを外す） ※1階層目のテーブルがあり、INDIRECTで連動させている場合は、対応不要 |
削除する選択肢（2階層目）のテーブルを削除 ※1階層目のテーブルがある場合は、そのテーブルの該当レコードの削除も必要 |
| 4 | 2階層目の選択肢の名称変更 | - | 該当テーブル（2階層目）の該当レコードの値を変更 |
| 5 | 2階層目の選択肢追加 | - | 該当テーブル（2階層目）へレコード追加 |
| 6 | 2階層目の選択肢削除 | - | 該当テーブル（2階層目）の該当レコードを削除 |
ケース1~3は1階層目のテーブルを用意しているかどうかでメンテナンス内容が若干変わります。
なお、ケース2・3が少々複雑なため、ケース2を例にイメージでも補足します（ケース3はこの逆）。
いずれかの作業が漏れると、ドロップダウンリストがテーブル側でいずれかの部分が連動しなくなるため、ご留意ください。
サンプルファイルで練習しよう！
可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。
サンプルファイル_データの入力規則_ドロップダウンリスト階層化（テーブル+INDIRECT）.xlsx
サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります（上記リンクから登録フォームへ遷移します）。
ブックを開いたら、次の手順を実施してください（今までの解説のまとめです）。
「社員マスタ_2階層」シートへ2階層のドロップダウンリストを設定（参照先は「元の値_2階層」シート）。
- 1階層目の入力規則を設定したいセル範囲を選択
※今回はC2~C11セル - リボン「データ」タブをクリック
- 「データの入力規則」をクリック
- 「リスト」を選択
- 「元の値」へ2階層目の各テーブルのフィールド名部分を選択
※今回は「元の値_2階層」シートのA1~B1セル - 「OK」をクリック
- 2階層目の入力規則を設定したいセル範囲を選択
※今回はD2~D11セル - リボン「データ」タブをクリック
- 「データの入力規則」をクリック
- 「リスト」を選択
- 「元の値」へINDIRECTの数式を入力
※今回は「=INDIRECT(C2)」 - 「OK」をクリック
本記事の解説と同じ結果になればOKです！
さいごに
いかがでしたでしょうか？
選択肢の複数テーブルの準備を工夫し、「データの入力規則」+INDIRECTを組み合わせることで、ドロップダウンリストの階層化が可能です。
階層関係の複数列をドロップダウンリストで選択入力する際の誤選択を防止したい場合に活用しましょう。
なお、本テクニック以外にもExcelでのデータ収集の各種テクニックを拙著で解説していますので、こちらも参考にしてみてください。
ご参考になれば幸いですm(_ _)m
森田 本記事のテクニックは、テーブルの代わりに「名前」で代用することも可能です。
ただし、テーブルはレコード追加/削除があった場合に、テーブル範囲とドロップダウンリスト側が連動（INDIRECT使用必須）しますが、「名前」の場合は参照範囲が自動で拡大/縮小しないため、メンテンナンス工数が増えます（都度「名前」の参照範囲も修正が必要）。
よって、階層構造のドロップダウンリストが必要な場合は本記事のテクニックを基本的に活用すると良いですね。
