Aさん ドロップダウンリストの「元の値」で参照する表をテーブルにしていますが、そのテーブルにレコードを追加してもドロップダウンリストに連動しないです。。
ドロップダウンリストとテーブルを連動することはできないんですかね？
森田 「データの入力規則」でテーブルを参照する場合のみ、固定のセル範囲扱いになってしまうんですよね。。
そんな場合、「データの入力規則」と関数の「INDIRECT」を組み合わせれば、ドロップダウンリストとテーブルを連動させることが可能ですよ！
では、詳細を解説していきますね。
はじめに
この記事はドロップダウンリストの設定方法とテーブルを把握していることが前提です。
「データの入力規則」でドロップダウンリストを設定する方法とテーブルの詳細は、以下の記事をご参照ください。
セルを選択入力させたい場合は「ドロップダウンリスト」が有効 後からデータ集計/分析をするにあたり、表のデータは完全一致したものである必要があります。 なぜなら、「りんご」と「林檎」等、人間目線では同じ意味だと読み取れても …
はじめに 本題に入る前に、この記事がおすすめな方を挙げてみます。 Excelでデータの集計/分析作業を行うこと …
「元の値」がテーブルでもドロップダウンリストに連動しない
「データの入力規則」の「元の値」で参照するマスタをテーブルにしたとしても、固定のセル範囲扱いとなってしまい、後からマスタへレコード追加してもドロップダウンリスト側へ連動しません。
通常、テーブルはレコード追加等で範囲が変わった際、そのテーブルを参照している関数やピボットテーブル等の他機能の参照範囲も自動的に連動しますが、「データの入力規則」のみ例外的に注意が必要になるというわけです。
これでは、マスタへレコード追加する度に「元の値」の参照範囲を修正する必要があり、メンテナンス工数がかかってしまいます。
こうした場合、「データの入力規則」の「元の値」に関数の「INDIRECT」を活用し、構造化参照の数式にすることでドロップダウンリストとテーブルを連動させることが可能です。
関数のINDIRECTと構造化参照の詳細は、以下の記事をご参照ください。
はじめに この記事は関数の概要を把握していることが前提です。 参考記事 関数の概要の詳細は以下の記事をご参照ください。 ワークシート上のセルの値を数式の一部として利用したい場合は「INDIRECT」が有効 実務では、ワー …
はじめに この記事は関数に指定するデータ型「参照」や参照演算子を把握していることが前提です。 参考記事 関数に指定するデータ型「参照」や参照演算子の詳細は以下の記事をご参照ください。 数式の参照セルにデータ名があると分か …
「データの入力規則」とINDIRECTを組み合わせれば、ドロップダウンリストとテーブルの連動が可能！
「データの入力規則」の「元の値」へINDIRECTを用いて、構造化参照の数式にしたものが以下です。
今回は商品マスタの「商品コード」フィールドが対象。
これで、後から該当のマスタへレコードを追加しても、自動的にドロップダウンリストへ反映されるようになります。
なお、INDIRECTの数式は原則「=INDIRECT("テーブル名[フィールド名]")」ですが、参照するマスタが1列のみであれば、「=INDIRECT("テーブル名")」と省略してもOKです。
テーブル名とフィールド名の部分はそれぞれ実際の名称に書き換え。
【参考】INDIRECTの数式のフィールド名はセル参照がおすすめ
前述のINDIRECTの数式は分かりやすいように定数にしましたが、実務ではフィールド名の部分をセル参照で指定することがおすすめです。
前提条件は、ドロップダウンリスト側の表とマスタでフィールド名が共通であること。
このように、フィールド名の部分は、ドロップダウンリスト側の表の見出しをセル参照し、それ以外の文字列とアンパサンド（&）で連結しましょう。
こうすることで、ドロップダウンリストの設定列が複数ある場合、このドロップダウンリストを設定済みのセル範囲をコピーし、未設定のセル範囲へ形式を選択して貼り付け（入力規則）することで入力規則の設定を使い回しできて便利です。
なお、セル参照の部分は複合参照（例：C$1）にしないと、セルによって参照セルが該当の見出しからずれてしまうため、ご注意ください。
複合参照の詳細は、以下の記事をご参照ください。
関数の数式を直接入力できるようになってきました！ もっと関数を実務で役立てていくために、他に覚えておいた方が良いことはありますか？？ …
サンプルファイルで練習しよう！
可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。
サンプルファイル_データの入力規則_ドロップダウンリスト（テーブル+INDIRECT）.xlsx
サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります（上記リンクから登録フォームへ遷移します）。
ブックを開いたら、次の手順を実施してください（今までの解説のまとめです）。
- 入力規則を設定したいセル範囲を選択
※今回はC2~C11セル - リボン「データ」タブをクリック
- 「データの入力規則」をクリック
- 「リスト」を選択
- 「元の値」へINDIRECTの数式を入力
※今回は「=INDIRECT("商品マスタ[商品コード]")」 - 「OK」をクリック
本記事の解説と同じ結果になればOKです！
さいごに
いかがでしたでしょうか？
「データの入力規則」+INDIRECT+テーブルの組み合わせテクニックは、ドロップダウンリストとマスタの連動に役立ちます。
ドロップダウンリストで参照するマスタ側にレコードが追加されることが多い場合に、本テクニックを活用しましょう。
なお、本テクニック以外にもExcelでのデータ収集の各種テクニックを拙著で解説していますので、こちらも参考にしてみてください。
ご参考になれば幸いですm(_ _)m
森田 INDIRECTは使いどころがマニアックになりがちな関数ですが、本テクニックは実務での活用頻度はかなり高いので、このテクニックを使うためだけでも覚えると良いですね。
なお、スピル環境（Excel2021以降またはMicrosoft365）なら、他にもドロップダウンリストと「元の値」の表を連動させる方法はありますが、本テクニックはExcel2007以降のバージョンから利用できるため、このテクニックを覚えておけば汎用性が高いのでおすすめです。
