データのダイナミックレンジの合計を見つけるにはSUMとOFFSETを使用します
Excel ワークシートにセル範囲の変化に基づく計算が含まれている場合、SUM OFFSET式でSUM関数とOFFSET関数を併用すると、計算を最新の状態に保つ作業が簡単になります。
SUM関数とOFFSET関数を使用してダイナミックレンジを作成する
毎月の売上合計など、継続的に変化する期間の計算を使用する場合、OFFSET機能を使用すると、毎日の売上高が追加されるたびに変化するダイナミックレンジを設定できます。
それ自体では、 SUM関数は、通常、加算される範囲に挿入されるデータの新しいセルを受け入れることができます。
関数が現在位置しているセルにデータが挿入されると、1つの例外が発生します。
この記事に添付されているサンプル画像では、毎日の新しい売上高がリストの最後に追加され、新しいデータが追加されるたびに合計が1つのセルを連続的に下に移動します。
SUM関数を単独で使用してデータを集計した場合、新しいデータが追加されるたびに関数の引数として使用されるセルの範囲を変更する必要があります。
ただし、SUM関数とOFFSET関数を併用すると、合計される範囲は動的になります。 言い換えると、新しいデータセルに対応するように変更されます。 データの新しいセルを追加しても、新しいセルが追加されるたびに範囲が調整され続けるため、問題は発生しません。
構文と引数
このチュートリアルでは、この記事に付随する画像を参照してください。
この式では、SUM関数を使用して、引数として指定されたデータの範囲を合計します。 この範囲の開始点は静的であり、数式によって合計される最初の数値へのセル参照として識別されます。
OFFSET関数はSUM関数の内部にネストされ、数式で合計されたデータの範囲に動的エンドポイントを作成するために使用されます。 これは、範囲の端点を数式の位置の上の1つのセルに設定することによって実現されます。
= SUM（範囲の開始：OFFSET（参照、行、列））Range Start - （必須）SUM関数によって合計されるセル範囲の開始点。 サンプル画像では、これはセルB2です。
参照 - 多くの行と列の離れた範囲の端点を計算するために使用されるセル参照。 この例の画像では、 Reference引数は式のセル参照を表しています。これは、常に範囲の上に1つのセルを式の上に置きたいからです。
行 - （必須）オフセットの計算に使用されるReference引数の上または下の行の数。 この値は、正、負、またはゼロに設定できます。
オフセットの位置がReference引数よりも大きい場合、この値は負の値になります。 下にある場合、 Rows引数は正の数です。 オフセットが同じ行にある場合、この引数はゼロです。 この例では、オフセットはReference引数より1行上で始まるため、この引数の値は負の1（-1）です。
Cols - （必須）オフセットの計算に使用されるReference引数の左または右の列の数。 この値は、正、負、またはゼロに設定できます
オフセットの位置がReference引数の左にある場合、この値は負の値になります。 右側の場合、 Cols引数は正の値です。 この例では、合計されるデータは数式と同じ列にあるため、この引数の値はゼロです。
SUM OFFSET数式を使用した売上データの合計
この例では、SUM OFFSET式を使用して、ワークシートの列Bにリストされている1日の売上高の合計を返します。
最初に、数式はセルB6に入力され、4日間の売上データが合計されました。
次のステップでは、SUM OFFSET数式を行の下に移動して、5日目の売上合計のためのスペースを確保します。
これは、新しい行 6を挿入することによって実行され、式7を行7に移動します。
移動の結果、Excelは参照引数をセルB7に自動的に更新し、セルB6を式で合計された範囲に追加します。
SUM OFFSET数式の入力
- 数式の結果が最初に表示される場所であるセルB6をクリックします。
- リボンメニューの式タブをクリックします。
- リボンからMath＆Trigを選択して、関数のドロップダウンリストを開きます。
- リスト内のSUMをクリックして、関数のダイアログボックスを表示します 。
- ダイアログボックスで、 Number1行をクリックします。
- このセル参照をダイアログボックスに入力するには、セルB2をクリックします。 この場所は、数式の静的エンドポイントです。
- ダイアログボックスで、 Number2行をクリックします。
- 次のOFFSET関数を入力します。OFFSET（B6、-1,0） ：式の動的終点を形成します。
- OKをクリックして機能を完了し、ダイアログボックスを閉じます。
合計$ 5679.15 セルB7に表示されます。
セルB3をクリックすると、完全な関数= SUM（B2：OFFSET（B6、-1,0））がワークシートの上の数式バーに表示されます 。
翌日の売上データの追加
翌日の売上データを追加するには：
- 行6の行ヘッダーを右クリックしてコンテキストメニューを開きます。
- メニューの[ 挿入 ]をクリックして、ワークシートに新しい行を挿入します。
- その結果、SUM OFFSET式はセルB7に移動し、行6は空になります。
- セルA6をクリックします。
- 5日目の売上合計が入力されていることを示す番号5を入力します。
- セルB6をクリックします。
- 数字$ 1458.25を入力し、キーボードのEnterキーを押します。
セルB7は$ 7137.40の新しい合計に更新されます。
セルB7をクリックすると、更新された数式= SUM（B2：OFFSET（B7、-1,0））が数式バーに表示されます。
注 ：OFFSET関数には、2つのオプションの引数HeightとWidthがありますが、この例では省略されています。
これらの引数を使用すると、高さが非常に多く、幅が非常に多いため、出力の形状をOFFSET関数に伝えることができます。
これらの引数を省略すると、関数はデフォルトで参照引数の高さと幅を代わりに使用します。この例では、高さが1行、幅が1列です。