Excel情報511　セル--ドロップダウンリスト(入力規則)の参照を自動的に変更する方法

【テーマ】
今回は、入力規則を使ったドロップダウンリストのデータが増えた時、参照範囲の変更を自動的に行う方法をご紹介します。

【方法】
OFFSET関数、COUNTA関数を使用します。

【参考】
OFFSET関数の詳細は、「バックナンバー487」、COUNTA関数の詳細は「バックナンバー176」をご参照ください。

データを入力する際、ドロップダウンリストから選択することで入力間違いが減り、スピードも速いですが、

リストにデータを追加した場合、設定を変更しなければドロップダウンリストに反映されません。

そこで今回は、ドロップダウンリスト(入力規則)の参照を自動的に変更する方法をご紹介します。

■設定方法

例としてリストには、下図の商品リストを使用します。

ドロップダウンリストを設定したいセルまたはセル範囲を指定し、

「データの入力規則」の「元の値」を、リストがある列全体で指定します。

＝商品リスト!A:A

ドロップダウンリストが設定されました。

商品リストに「プリンター」を追加すると、ドロップダウンリストに自動で「プリンター」が追加されました。

ただしこの設定方法では、下図のように1行目から始まっていないリストや見出しがある場合、

不要なデータも含まれてしまいます。

1行目から始まっていないリスト

見出しがあるリスト

その場合、OFFSET関数とCOUNTA関数で下記のように指定すると、必要なデータのみ設定できます。

=OFFSET(商品リスト!A2,0,0,COUNTA(商品リスト!A:A)-1,1)

■解説

=OFFSET(基点のセル,○行,△列,高さ,幅)

基点のセルから○行△列移動し、そこから高さと幅の分だけ範囲選択します。

=OFFSE

①：

商品リストシートのA2セルを基点に、

②③：

0行、0列移動し(つまり移動せず)、

④：

商品リストシートA列の入力セルの個数の行数(見出し行を含めないように-1しています)、

⑤：

1列(つまりA列のみ)

の範囲を選択しています。

　　　※OFFSET関数の詳細については、以下のバックナンバーをご参照ください。

　　　※COUNTA関数の詳細については、以下のバックナンバーをご参照ください。