プルダウンリストの項目追加

今回はプルダウンリストの元データを変更した時に、データ範囲の指定変更をしないですむように設定します。

元データの範囲を自在にする関数 Excel2016
 プルダウンリストの項目追加

プルダウンリストの項目追加

プルダウンリストに自動的に項目を追加

入力規則を使ったプルダウンリスト(ドロップダウンリスト)は便利なので、いろいろな表で使われていると思います。 ただ、入力項目が増える度に、通常は、リストの元データを「データの入力規則」ダイアログから設定変更しなければなりません。 Excel操作になれていれば、難しくはないのですが、それでも面倒です。
今回は、手間いらずの元となるデータを範囲指定の変更なしにリストに追加できるようにしてみましょう。 そのためには、関数を使います。範囲を返すOFFSET関数と、空白以外のデータを数えるCOUNTA関数の2つです。

Excelバージョン:201920162013 201020072003

リストに自動的に項目を追加で同じシートの場合

前回と同様のプルダウンリストを設定したシートです。これは、リストが同じシートにある場合の例。
プルダウンリストの項目追加自動1
データの入力規則ダイアログから、参照する元の値に入力された式を自動的に項目を追加できるように変更します。
プルダウンリストの項目追加自動2
式は次のように変更します。
=OFFSET($E$3,0,0,COUNTA(E:E),1)
OFFSET書式 OFFSET(参照,行数,列数,高さ,幅)
OFFSET関数は、基準となるセルを決め、そこから縦と横にずらしたセルを参照する関数です。
プルダウンリストの項目追加自動3
元の値に
=OFFSET($E$3,0,0,COUNTA(E:E),1)の数式を入力して、データの入力規則ダイアログボックスを閉じたら、 データの部分に項目を追加してみます。
プルダウンリストの項目追加自動4
プルダウンリストを参照すると、追加した項目が反映されています。
プルダウンリストの項目追加自動5

リストに自動的に項目を追加で別シートの場合

別シートにあるデータをOFFSET関数で参照する場合は、参照するシートを指定する必要があります。
下記サンプルの場合は、元表というシートですから、範囲の前に”元表!”と追加して指定します。

プルダウンリストの項目追加自動6
別シートにあるデータをOFFSET関数で参照する場合は、数式を以下のように入力します。
=OFFSET(元表!$B$2,0,0,COUNTA(元表!B:B)-1,1)
プルダウンリストの項目追加自動6
もっと知りたいプルダウンリストの使い方