エクセル|ドロップダウンリスト|自動追加・削除編

エクセル|ドロップダウンリスト|自動追加・削除編

最初からドロップダウンリストに設定する項目が変動すると分かっていたとしても、毎回リストの項目を追加設定し直すのは非常に面倒ですよね
今回は自動でドロップダウンリストに追加又は削除してくれる方法を説明します。

 

最初の設定がポイント

最初のドロップダウンリストの設定時の参照方法がポイントとなります。
参照には2つの関数を使用します。
・OFFSET関数
 ドロップダウンリストで使用するリストのセル範囲を求めます。
・COUNTA関数
 リストのセル範囲で空白でないセルの個数を求めます。

手動で追加設定する方法はこちら

 

OFFSET関数とは

OFFSET関数でドロップダウンリストのセル範囲を求めます。
参照したいセルの範囲が基準からどの位離れているか、またセルの範囲の領域はどのくらいか。

OFFSET(参照,行数,列数,高さ,幅)
OFFSET(E3,0,0,5,1)
※基準のE3からE7までがドロップダウンリストのセル範囲となる

COUNTA関数とは

COUNTA関数でドロップダウンリストに含まれる空白ではないセルの個数を求めます。

COUNTA(値1, 値2, …, 値255)
値:個数を求めたいセルやセル範囲を指定する
  ① E列を指定する場合:COUNTA(E:E)
  ② E列の範囲を指定する場合:COUNT(E3:E10)

データの入力規制に設定する値

ドロップダウンリストのデータの入力規制で設定する値はOFFSET関数とCOUNTA関数を組み合わせます。

=OFFSET(E3,0,0,COUNTA(E:E),1)
※ E列の全ての範囲を自動追加させる場合

次の手順でドロップダウンリストに追加します。
1) 追加したいドロップダウンリストのセルを選択する
2) メニューの「データタブ」ー「データの入力規制」を選択する
3) 「設定タブ」の「リスト」を選択する

4) OFFSET関数を設定する
5) セルのドロップダウンリストに追加された事を確認する

① 追加したいドロップダウンリストのセルを選択する

② メニューの「データタブ」ー「データの入力規制」を選択する

③ 「設定タブ」の「リスト」を選択する

④ OFFSET関数を設定する

⑤ セルのドロップダウンリストに項目が追加された事を確認する

自動追加されたか確認する

「項目6」をロップダウンリストのセルに追加し、リストに自動追加されたかを確認する。

 

自動削除されたか確認する

ドロップダウンリストのセルから「項目6」を削除し、リストから自動削除されるかを確認する

削除は行削除がポイント

ドロップダウンリストのセルから項目を削除する場合は行削除してください。
行削除しないと空白セルが残ります。