セルにデータを入力するとき入力規則で予め設定されたリストから選択できると便利ですね。でもリストに表示する内容（件数）を追加した場合リスト範囲を再設定するのは面倒です。
そこで、
◆こんな時
リスト表示される範囲を自動化したい。
◆解決ポイント
次の２つの方法のどちらかで解決できます。
１）名前の定義で変動範囲を設定し入力規則で参照する方法
２）INDIRECT関数で入力規則に直接変動範囲を記述する方法
◆解決策
例）Sheet1のセルA1からA列に入力されたデータの件数でリスト範囲を
自動で求めます。
１）名前の定義で変動範囲を設定し入力規則で参照する方法
リボン-「数式」-「名前の定義」でOFFSET関数を使い以下のように
名前を定義します。
上図の（参照範囲）には次の式を入力しています。
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
※OFFSET関数の引数はこの式を参考に自分で調べてくださいね。
下図の入力規則の（元の値）に上で定義した名前「リスト範囲」を
入力します。 イコール（=）を忘れずに。
２）INDIRECT関数で入力規則に直接変動範囲を記述する方法
下図の入力規則の（元の値）に INDIRECT関数で直接指定します。
上図の（元の値）には次の式を入力しています。
=INDIRECT("Sheet1!A1:A"&COUNTA(Sheet1!A:A))
※こちらの方法では名前の定義は不要です。
なお、どちらも COUNTA関数を使って件数（行数）を取得しています。
上記、１）または２）の方法でリストが増えても（減っても）正しくドロップダウンリストに表示されるようになります。
以前にも入力規則を取り上げていますがその機能の使い道は多岐に亘りますので覚えてくと便利です。
※Excel2007以上の環境で説明しています。
◆「StiLL」のコマンドボタンについて
「StiLL」には（BtOffsetName）というデータ件数で範囲を名前定義できるボタンがあります。
これを使うとやさしくまた動的にデータ範囲を設定することができるのでドロップダウンリストはもちろん「StiLL」のソート（BtSort）ボタンを使って名前定義された範囲のデータをソートするなど応用も広がり、わかりやすくシステムに摘要することができます。
ではまた来週。
PR
