【Excel】リストに追加した項目を自動的にドロップダウンリストに反映する方法

2025年2月19日2025年3月30日

2021年以降を中心にExcelに追加された機能・関数の中で特に実務に役立つものについて解説しています。基本的な使い方や他の機能・関数と組み合わせた応用テクニックも紹介しています。

コンサルタントとして様々なプロジェクトに従事してきた著者が、実務において実際に活用したExcelのテクニックを紹介します。著者自身が「あれ？どうすればいいんだろう」と悩んだ部分を中心にピックアップしています。

リストの項目を追加すれば、自動的にドロップダウンリストにも反映されるような仕組みの作り方を紹介します。頻繁に更新するようなリストはこの方法を使うことで、都度参照範囲を変更する手間を省くことができます。

基本的なドロップダウンリストの設定方法

たとえば、このような営業記録表で会社名をドロップダウンリストから選択できるようにします。

あらかじめ顧客のリストを別のシートに作成しておきます。

次に顧客の列を選択し、データタブのデータの入力規則を開きます。

入力値の種類にリストを選択し、次の値で先ほどの顧客リストの範囲を選択します。

これでOKを押せば、ドロップダウンリストから顧客名を選択できるようになります。

しかし、この方法では顧客リストの顧客を追加しても、ドロップダウンリストには反映されません。たとえば「スイカ商事」という会社名を顧客リストの下に追加します。

しかし、ドロップダウンリストにはスイカ商事という名前は追加されていません。

入力規則のドロップダウンリストの範囲として「スイカ商事」は選択されていないからです。

リストの追加を反映するには、テーブル機能を活用します。どこでも良いので顧客リストにカーソルを合わせ、Ctlr+Tを押します。そうすると、顧客リストの範囲を自動的に選択し、テーブルを設定してくれるので、OKを押します。

そうすると、顧客リストがテーブルになりました。

テーブルデザインのタブからテーブル名を分かりやすいものにします。ここでは、顧客リストという名称にします。

営業記録表に戻り、顧客の列を選択し、データの入力規則を開きます。入力値の種類には「リスト」を設定し、値にはINDIRECT関数を使用します。

INDIRECT関数
指定した文字列をセル参照として解釈し、そのセル参照の値を返す。

以下のように入力します。

=INDIRECT(“リスト!顧客リスト”)

「リスト」とはシート名を指し、「顧客リスト」は先ほど設定したテーブルの範囲を表しています。これで、先ほど設定した顧客リストを参照することを意味します。

この方法でリストを設定すると、今の顧客リストから顧客名を選択することができます。さらに、顧客リストの下にスイカ商事という会社を追加すると、ドロップダウンリストにも反映されます。

テーブル機能を使用しない方法もあります。テーブル機能ではデザインなどの柔軟性が欠けるため、使いたくないという方はこちらの方法がおすすめです。顧客の列を選択し、データの入力規則を開きます。値にはOFFSET関数を使用します。

OFFSET(基準, 行数, 列数, [高さ], [幅])
指定したセルを基準に別のセル範囲を指定できる。基準：基準となるセルまたはセル範囲を指定。行数：基準セルから何行下に移動するかを指定。列数：基準セルから何列右に移動するかを指定。高さ：参照する範囲の高さを指定。幅：参照する範囲の幅を指定。

ここでは、このように数式を入力します。

=OFFSET(リスト!$A$2,0,0,COUNTA(リスト!$A:$A)-1,1)

数式	役割
基準	顧客リストの一番上のセルA2を指定。
行数	参照する範囲はセルA2を含むその下の範囲で、移動する必要はないため「0」を指定。
列数	参照する範囲はセルA2を含むその下の範囲で、移動する必要はないため「0」を指定。
高さ	A列にデータが入力されている範囲を参照するため、COUNTA関数を使ってデータがあるセルの個数を数える。
幅	A列の1列だけを参照するため、「1」と入力。

この方法でドロップダウンリストを設定すれば、今の顧客リストからも選択でき、顧客リストの新しい顧客名を追加してもドロップダウンリストに反映されます。