Excel データは頻繁に変更されるため、データ範囲のサイズに合わせて自動的に拡大および縮小する、動的に定義された範囲を作成すると便利です。方法を見てみましょう。
動的に定義された範囲を使用すると、データが変更されたときに数式、グラフ、ピボットテーブルの範囲を手動で編集する必要がなくなります。これは自動的に行われます。
ダイナミック レンジの作成には、OFFSET と INDEX の 2 つの式が使用されます。この記事では、より効率的なアプローチである INDEX 関数の使用に焦点を当てます。 OFFSET は揮発性関数であるため、大規模なスプレッドシートの速度が低下する可能性があります。
Excel で動的に定義された範囲を作成する
最初の例では、以下に示す単一列のデータ リストがあります。
さらに多くの国が追加または削除された場合に範囲が自動的に更新されるように、これを動的にする必要があります。
この例では、ヘッダー セルを避けます。したがって、範囲 $A$2:$A$6 が必要ですが、動的になります。これを行うには、「数式」>「名前の定義」をクリックします。
[名前] ボックスに「countries」と入力し、[参照先] ボックスに以下の数式を入力します。
=$A$2:INDEX($A:$A,COUNTA($A:$A))
この方程式をスプレッドシートのセルに入力し、それを [新しい名前] ボックスにコピーする方が早くて簡単な場合があります。
これはどのように作動しますか？
数式の最初の部分では範囲の開始セル (この例では A2) を指定し、その後に範囲演算子 (:) が続きます。
=$A$2:
範囲演算子を使用すると、INDEX 関数はセルの値ではなく範囲を強制的に返します。 INDEX 関数は COUNTA 関数とともに使用されます。 COUNTA は、列 A の空白以外のセルの数をカウントします (この例では 6 つ)。
INDEX($A:$A,COUNTA($A:$A))
この数式は、INDEX 関数に列 A ($A$6) の空白でない最後のセルの範囲を返すように要求します。
最終結果は $A$2:$A$6 になります。COUNTA 関数により最後の行が検索されるため、動的になります。データ検証ルール、式、グラフなど、すべての国名を参照する必要がある場所で、この「国」定義名を使用できるようになりました。
双方向の動的定義範囲の作成
最初の例は高さのみが動的でした。ただし、少し変更して別の COUNTA 関数を使用すると、高さと幅の両方で動的な範囲を作成できます。
この例では、以下に示すデータを使用します。
今回は、ヘッダーを含む動的定義範囲を作成します。 [数式] > [名前の定義] をクリックします。
[名前] ボックスに「sales」と入力し、[参照先] ボックスに以下の数式を入力します。
=$A$1:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
この数式では、$A$1 を開始セルとして使用します。 INDEX 関数は、ワークシート全体の範囲 ($1:$1048576) を使用して検索し、そこから戻ります。
COUNTA 関数の 1 つは空白以外の行をカウントするために使用され、もう 1 つは空白以外の列に使用され、両方向に動的になります。この数式は A1 から始まりますが、任意の開始セルを指定できます。
この定義された名前 (sales) を数式で使用したり、グラフ データ シリーズとして使用して動的にすることができるようになりました。
