残業を減らす!Officeテクニック

Excelでドロップダウンリストにマスターデータへの追加項目を自動反映させる方法

マスターデータの更新内容をドロップダウンリストにも反映させる

 決まった値をくり返し入力する必要がある表では「ドロップダウンリスト」がよく使われます。その値を参照するVLOOKUP関数やXLOOKUP関数の数式を組み込むこともありますよね。

ドロップダウンリストから値を選択できる
その値を参照するVLOOKUP関数やXLOOKUP関数の数式を組み込むことがある

 入力ミスや表記揺れも防ぐことができて重宝しますが、“マスターデータ”を更新した時にドロップダウンリストの設定も再設定するのが面倒ですよね。上記の例では、「製品マスタ」というテーブルを用意しており、ドロップダウンリストの設定は、テーブル内の特定のセル範囲を参照しています。

「製品マスタ」というテーブルで、製品コードを管理している
ドロップダウンリストの設定は「=製品マスタ!$A$2:$A$7」となっている。[製品マスタ]シートのセルA2~A7を参照している状態

 これはあまりよろしくない状態です。例えば、[製品マスタ]テーブルに新製品を登録しても、ドロップダウンリストには反映されません。

「製品マスタ」テーブルに「A003」「B003」「C003」を追加した
ドロップダウンリストには反映されない。ドロップダウンリストでは[製品マスタ]シートのセルA2~A7を参照しているためだ

 ドロップダウンリストの設定として、[製品マスタ]シートの“セルA2~A7だけ”を参照しているためです。テーブルに追加した情報は、ドロップダウンリストにも自動的に反映させたいですよね。今回は、自動的にドロップダウンリストも更新させる方法を紹介します。

INDIRECT関数でフィールドを参照する

 このサンプルでは、ドロップダウンリストに表示させたい項目は[製品コード]です。「製品マスタ」テーブルを作成してあるので、[製品コード]列(フィールド)を参照するためにINDIRECT関数を利用します。

 以下の手順で入力している「=INDIRECT("製品マスタ[製品コード]")」は、「製品マスタ」テーブルの[製品コード]列(フィールド)を参照する数式です。このような表記を構造化参照と呼びます。テーブル名と列(フィールド)名はご自分の環境に合わせて指定しましょう。「"」「[」「]」は半角で入力してください。

ドロップダウンリストを設定するセル範囲を選択して、[データ]タブにある[データの入力規則]をクリックする
[元の値]に「=INDIRECT("製品マスタ[製品コード]")」と入力して、[OK]をクリックする
「製品マスタ」テーブルに新しいデータを追加してみる
追加した項目を含んでドロップダウンリストに反映された

項目の順番はマスターデータと連動する

 ドロップダウンリストの項目の順番は、マスターデータと連動します。ドロップダウンリストの順番を固定したい場合、マスターデータの順番を入れ替えることです。並べ替えても問題なければ、参照する列(フィールド)を昇順や降順としておきましょう。

列(フィールド)のボタンをクリックして[昇順]を選択する
並べ替えられた順番でドロップダウンリストに表示される
ドロップダウンリストの項目の順番が並べ替えられた

SORT関数とスピルの結果を活用する

 何らかの理由でマスターデータを並べ替えられないこともあります。例えば、製品コードとは別に管理番号のあるマスターデータを考えてみます。製品コードで並べ替えてしまうと、管理上都合が悪いですよね。

管理番号の昇順で管理しているので、製品コードを昇順に並べ替えると都合が悪い

 マスターデータは頻繁に操作しないと思いますが、管理者が[単価]列(フィールド)で並べ替えることもあるかもしれません。その都度、ドロップダウンリストの順番が入れ替わると、データを入力する人は戸惑ってしまいますよね。

 SORT関数でテーブルを参照して、スピルで表示された結果をドロップダウンリストに表示すれば解決します。SORT関数は、指定したセル範囲を並べ替える関数です。セル範囲だけを指定した場合は、そのセル範囲を昇順で並べ替えた結果をスピルで表示します。

 以下の例では、テーブル名「製品マスタ2」、フィールド名「製品コード」なので、SORT関数の数式は構造化参照で「=SORT(製品マスタ2[製品コード])」と入力します。ここでは[製品マスタ2]シートのセルF2に入力します。

セルF2に「=SORT(製品マスタ2[製品コード])」と入力する
製品コードが昇順で並べ替えられた結果がスピルで表示された

 この結果を参照するようにドロップダウンリストの設定を変更します。[データの入力規則]ダイアログボックスの[元の値]に「=製品マスタ2!$F$2#」と入力します。上記の手順で[製品マスタ2]シートのセルF2に入力したSORT関数の結果は、セルF2~F12に表示されました。スピルの結果は動的配列と呼ばれ、参照する場合は「$F$2#」のように「#」を付けることで表現できます。

ドロップダウンリストを設定するセル範囲を選択して、[データ]タブにある[データの入力規則]をクリックする
[元の値]に「=製品マスタ2!$F$2#」と入力して、[OK]をクリックする
[製品マスタ2]シートのセルF2のSORT関数のスピルの結果が参照され、ドロップダウンリストに表示された