エクセルのプルダウンリストの範囲を自動的に増減させる方法とは？

今回の記事ではプルダウンリスト（ドロップダウンリスト）の応用編として、プルダウンに指定するリストの数を増減させたときに、OFFSET関数とCOUNTA関数を使い自動的にリストの範囲を可変する方法を解説したいと思います。

通常はプルダウンのリストのデータ数を修正するたびに範囲の修正が必要ですが、この方法を覚えておくことでデータ数が変わった場合でも範囲を修正する必要がなくなりとても便利です。

シーンに合わせてプルダウンを使いこなして、さらなる時短を目指していただければと思います。

プルダウンのリストを可変にするには、データの入力規制の「元の値」のウインドウ部分に、
=OFFSET(リストの一番上のセル,0,0,COUNTA(リストがある列全体),1)
と指定することで設定することができます。

この記事を読むと分かること

好きな項目から読めます

1．プルダウンのリストの範囲を可変にする方法とは？

通常はプルダウンのリストを設定した後に追加や削除をした場合は、リストの範囲をその都度修正する必要がありますが、OFFSET関数とCOUNTA関数を使用することで、範囲に指定したデータの増減に合わせて自動的に範囲を可変させることができます。

OFFSET関数の指定方法
=OFFSET(参照,行数,列数,[高さ],[幅])
参照から行数と列数移動した先から、高さと幅の範囲を取得します。
COUNTA関数の指定方法
=COUNTA(範囲)
計算式での答えが空白や文字列も含めた、空白以外のセルの数を数えます。

↓OFFSET関数について詳しくはこちら↓
OFFSET関数とは
↓COUNTA関数についてはこちら↓
COUNTA関数とは

それでは実際に設定方法を解説していきます。

①プルダウンを作成したいセルを選択した状態でデータタブ⇒データツールのデータの入力規制を押し、データの入力規制のウインドウを開きます。

②設定タブの「入力値の種類」を「リスト」にし、「元の値」の部分に、

=OFFSET(リストの一番上のデータ,0,0,COUNTA(リストがある列全体),1)
今回の場合はF列にデータがあるので、
=OFFSET($F$2,0,0,COUNTA($F:$F),1)

と入力します。
※「ドロップダウンリストから選択する」にチェックが入っていることを確認してください。

「元の値」に計算式を入力する際、入力した計算式が見えなくなるなどの癖があります。
計算式を入力しづらい場合は、一旦他のセルに計算式を入力してコピペすることで簡単に入力をすることができます。
その際にセルの指定には絶対参照を使用するよう注意してください。

③「OK」を押すことでリストの範囲を自動的に可変することができるようになります。

なぜ、範囲が自動で取得されるのか？
OFFSET関数の4つ目の引数が範囲の縦方向のセル数になっているため４つ目の引数にCOUNTA関数を指定し、リストのデータ数を取得することで縦方向のセル数が自動的に修正されるという仕組みになっています。

今回の記事では、プルダウンの範囲を可変にする方法を解説しました。

慣れるまでは設定に苦戦するかもしれませんが、最初に設定をしてしまえばリストのデータを更新するたびに範囲を再設定する手間が省けるため、かなりの時短に繋げることができます。

ただ、2021年8月現在スプレッドシートに変換して使用する場合にこの機能はうまく引き継がれずプルダウンのリスト自体を表示することもできなくなってしまうようです。
スプレッドシートで使用する予定がある場合はご注意ください。

最後までお読みいただきありがとうございました。

大量のデータを扱うのに便利な記事一覧