残業を減らす！Officeテクニック
Excelでドロップダウンリストにマスターデータへの追加項目を自動反映させる方法
2024年10月21日 08:44
決まった値をくり返し入力する必要がある表では「ドロップダウンリスト」がよく使われます。その値を参照するVLOOKUP関数やXLOOKUP関数の数式を組み込むこともありますよね。
入力ミスや表記揺れも防ぐことができて重宝しますが、“マスターデータ”を更新した時にドロップダウンリストの設定も再設定するのが面倒ですよね。上記の例では、「製品マスタ」というテーブルを用意しており、ドロップダウンリストの設定は、テーブル内の特定のセル範囲を参照しています。
これはあまりよろしくない状態です。例えば、［製品マスタ］テーブルに新製品を登録しても、ドロップダウンリストには反映されません。
ドロップダウンリストの設定として、［製品マスタ］シートの“セルA2～A7だけ”を参照しているためです。テーブルに追加した情報は、ドロップダウンリストにも自動的に反映させたいですよね。今回は、自動的にドロップダウンリストも更新させる方法を紹介します。
INDIRECT関数でフィールドを参照する
このサンプルでは、ドロップダウンリストに表示させたい項目は［製品コード］です。「製品マスタ」テーブルを作成してあるので、［製品コード］列（フィールド）を参照するためにINDIRECT関数を利用します。
以下の手順で入力している「=INDIRECT("製品マスタ[製品コード]")」は、「製品マスタ」テーブルの［製品コード］列（フィールド）を参照する数式です。このような表記を構造化参照と呼びます。テーブル名と列（フィールド）名はご自分の環境に合わせて指定しましょう。「"」「[」「]」は半角で入力してください。
項目の順番はマスターデータと連動する
ドロップダウンリストの項目の順番は、マスターデータと連動します。ドロップダウンリストの順番を固定したい場合、マスターデータの順番を入れ替えることです。並べ替えても問題なければ、参照する列（フィールド）を昇順や降順としておきましょう。
SORT関数とスピルの結果を活用する
何らかの理由でマスターデータを並べ替えられないこともあります。例えば、製品コードとは別に管理番号のあるマスターデータを考えてみます。製品コードで並べ替えてしまうと、管理上都合が悪いですよね。
マスターデータは頻繁に操作しないと思いますが、管理者が［単価］列（フィールド）で並べ替えることもあるかもしれません。その都度、ドロップダウンリストの順番が入れ替わると、データを入力する人は戸惑ってしまいますよね。
SORT関数でテーブルを参照して、スピルで表示された結果をドロップダウンリストに表示すれば解決します。SORT関数は、指定したセル範囲を並べ替える関数です。セル範囲だけを指定した場合は、そのセル範囲を昇順で並べ替えた結果をスピルで表示します。
以下の例では、テーブル名「製品マスタ2」、フィールド名「製品コード」なので、SORT関数の数式は構造化参照で「=SORT(製品マスタ2[製品コード])」と入力します。ここでは［製品マスタ2］シートのセルF2に入力します。
この結果を参照するようにドロップダウンリストの設定を変更します。［データの入力規則］ダイアログボックスの［元の値］に「=製品マスタ2!$F$2#」と入力します。上記の手順で［製品マスタ2］シートのセルF2に入力したSORT関数の結果は、セルF2～F12に表示されました。スピルの結果は動的配列と呼ばれ、参照する場合は「$F$2#」のように「#」を付けることで表現できます。
