項目を追加した時に定義した名前の範囲を拡張させる - Excel 関数

Microsoft Excel では "名前" を定義することで、あらかじめ定めておいた範囲を名前で管理できるようになっています。

これを利用することで、関数や VBA から名前を使ってその範囲を指定したり、入力規則でその範囲の選択項目を表示させたりすることができます。

ただ、例えば "$A$2:$A$5" というように、A2 から A5 までの範囲を固定的に設定した場合、新しい項目を追加した時には、改めて名前の範囲を広げてあげる必要があります。

 

新しい項目を追加した場合に、自動的に範囲を拡張したい場合には、次のように OFFSET 関数を使用して "参照範囲" を設定しておく必要があります。

OFFSET 関数の引数は、次のようになっています。

第 n 引数	意味	上記の場合
1	基準となるセルまたはセルの範囲を指定します。	$A$2
2	第 1 引数で指定した基準から、下へいくつずらした範囲を対象とするかを指定します。	0 対象範囲を垂直方向にはずらしません。
3	第 1 引数で指定した基準から、右へいくつずらした範囲を対象とするかを指定します。	0 対象範囲を水平方向にはずらしません。
4	第 1 引数で指定した基準を、下方向へいくつ拡張させるかを指定します。	COUNTA(Sheet1!$A:$A) - 1) A 列内の値が設定されているセルの数から 1 引いた数（表題の 1 つを除く）だけ、A2 セルから下方向へ拡張します。
5	第 1 引数で指定した基準を、右方向へいくつ拡張させるかを指定します。	（省略） 右方向には拡張しません。

このように引数を指定することで、縦方向に項目が増えて行く度に、名前の範囲が自動的に拡張されるようになります。

もっとも、上記の例では縦方向の入力されているセルの数を数えて拡張しているので、空白行を挟んだり、下に別のリスト項目を書いたりすると、範囲が適切ではなくなってくるので注意が必要です。

 

なお、定義した名前が実際にどの範囲を指しているかについては、リボンメニューの【数式】から【名前の管理】を選択して、対象となる "名前" の "参照範囲" の定義の欄を選択することで確認できます。

"参照範囲" の入力欄を選択すると、実際のシートの範囲に点線が描かれるので、適切な範囲が選択されているかを目で見て確認することができます。