Excelのデータは頻繁に変更されるので、データ範囲のサイズに合わせて自動的に拡大・縮小する動的に定義された範囲を作成すると便利です。見てみましょう。

動的に定義された範囲を使用することで、データが変更されたときに、数式、チャート、ピボットテーブルの範囲を手動で編集する必要がありません。

ダイナミックレンジの作成には、オフセットとインデックスの2つの計算式が使用されます。この記事では、より効率的な方法であるため、インデックス関数を使用することを中心に説明します。オフセットは不安定な機能で、大規模なスプレッドシートの速度を低下させる可能性があります。

エクセルで動的な定義範囲を作成する

最初の例では、次のような1列のデータリストがあります。

国が追加されたり削除されたりすると、範囲が自動的に更新されるよう、動的であることが必要です。

この例では、ヘッダーセルを使用しないようにしたい。したがって、$A$2:$A$6の範囲が欲しいが、動的である。これを行うには、「数式」→「名前の定義」をクリックします。

名前」ボックスに「国」と入力し、「参照」ボックスに次の数式を入力します。

=$A$2:INDEX($A:$A,COUNTA($A:$A))

この数式をスプレッドシートのセルに入力し、新しい名前ボックスにコピーする方が早くて簡単な場合があります。

どうしたんだ？

数式の最初の部分では、範囲の開始セルを指定し（ここではA2）、その後に範囲演算子（:）が続きます。

=$A$2:

RANGE 演算子を使用すると、INDEX 関数がセルの値ではなく、範囲を返すように強制されます。COUNTAはA列の空白でないセルの数（この場合は6）をカウントします。

INDEX($A:$A,COUNTA($A:$A))

この数式では、A列の最後の空白でないセル（A$6）の範囲を返すために、インデックス関数が必要です。

最終結果は$A$2:$A$6で、COUNTA関数のおかげで最後の行を見つけるので動的です。これで、データ検証ルール、数式、チャートなど、すべての国名を参照する必要がある場所で、この「国」で定義された名前を使用できます。

双方向の動的な定義域を作成

最初の例は、高さをダイナミックに変化させただけです。しかし、ほんの少しの修正と別のCOUNTA関数で、高さと幅の両方が動的な範囲を作成することが可能です。

この例では、以下のようなデータを使用します。

今回は、タイトルを含む動的に定義された範囲を作成します。「数式」→「名前の定義」をクリックします。

名前」ボックスに「売上高」と入力し、「参照」ボックスに以下の数式を入力します。

=$A$1:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))

この式では、$A$1を開始セルとして使用します。そして、INDEX関数は、ワークシート全体の範囲（$1:$1048576）を使って検索し、返します。

COUNTA関数の一方は空でない行の計算に使われ、もう一方は空でない列の計算に使われ、双方向に動的な計算ができます。この数式はA1から始まりますが、任意の開始セルを指定することができます。

この定義された名前（売上高）を数式やチャートデータ系列で使用することで、ダイナミックな動きをすることができるようになりました。