Excel SUMとOFFSET式

Excelワークシートにセル範囲の変化に基づく計算が含まれている場合、SUM OFFSET式でSUM関数とOFFSET関数を併用すると、計算を最新の状態に保つ作業が簡単になります。

SUM関数とOFFSET関数を使用してダイナミックレンジを作成する

毎月の売上合計など、継続的に変化する期間の計算を使用する場合、ExcelのOFFSET関数では、毎日の売上高が追加されるたびに変化するダイナミックレンジを設定できます。

それ自体では、SUM関数は、通常、新しいセルのセルを合計される範囲に挿入することに対応できます。

関数が現在位置しているセルにデータが挿入されると、1つの例外が発生します。

上記の例の画像では、新しいデータが追加されるたびに1つのセルが連続して1つずつシフトするように、各日の新しい売上数値がリストの最後に追加されます。

SUM関数を単独で使用してデータを集計した場合、新しいデータが追加されるたびに関数の引数として使用されるセルの範囲を変更する必要があります。

ただし、SUM関数とOFFSET関数を併用すると、合計される範囲は動的になります。言い換えると、新しいデータセルに対応するように変更されます。データの新しいセルを追加しても、新しいセルが追加されるたびに範囲が調整され続けるため、問題は発生しません。

構文と引数

このチュートリアルでは、上記の画像を参照してください。

この式では、SUM関数を使用して、引数として指定されたデータの範囲を合計します。この範囲の開始点は静的であり、数式によって合計される最初の数値へのセル参照として識別されます。

OFFSET関数はSUM関数の内部にネストされ、数式で合計されたデータの範囲に動的エンドポイントを作成するために使用されます。これは、範囲の端点を数式の位置の上の1つのセルに設定することによって実現されます。

数式の構文は次のとおりです。

= SUM（範囲の開始：OFFSET（参照、行、列））

• 範囲の開始：SUM関数によって合計されるセル範囲の開始点が必要です。サンプル画像では、これはセルB2です。
• 参照：範囲の終点を計算するために使用される必要なセル参照は、多くの行と列に配置されています。この例の画像では、参照引数は数式の上に1つのセルの範囲を終了させるため、式自体のセル参照です。
• 行：オフセットの計算に使用されるReference引数の上または下の行の数が必要です。この値は、正、負、またはゼロに設定できます。オフセットの位置が 参照 引数では、値は負です。下にある場合、Rows引数は正の数です。オフセットが同じ行にある場合、引数はゼロです。この例では、オフセットはReference引数より1行上で始まるため、この引数の値は負の1（-1）です。
• コルズ：オフセットの計算に使用されるReference引数の左または右の列の数が必要です。この値は、正、負、またはゼロに設定できます。オフセットの位置がReference引数の左にある場合、この値は負の値になります。右側の場合、Cols引数は正の値です。この例では、合計されるデータは数式と同じ列にあるため、この引数の値はゼロです。

SUM OFFSET数式を使用した売上データの合計

この例では、SUM OFFSET式を使用して、ワークシートの列Bにリストされている1日の売上高の合計を返します。

最初に、数式はセルB6に入力され、4日間の売上データが合計されました。

次のステップでは、SUM OFFSET数式を行の下に移動して、5日目の売上合計のためのスペースを確保します。これは、新しい行6を挿入することによって実行され、式7を行7に移動します。

移動の結果、ExcelはReference引数をセルに自動的に更新しますB7 セルを追加するB6 数式で合計された範囲に設定します。

SUM OFFSET数式の入力

1. 数式の結果が最初に表示される場所であるセルB6をクリックします。
2. クリックしてください数式 タブをクリックします。
3. 選択する数学と三角リボンから関数ドロップダウンリストを開きます。
4. クリック和 リスト内で関数のダイアログボックスを表示します。
5. ダイアログボックスで、Number1ライン。
6. セルをクリック B2 このセル参照をダイアログボックスに入力します。この場所は、数式の静的エンドポイントです。
7. ダイアログボックスで、2番ライン。
8. 次のOFFSET関数を入力します。 OFFSET（B6、-1,0） フォーミュラの動的エンドポイントを作成します。
9. クリック OK 機能を完了し、ダイアログボックスを閉じます。

合計がセルB7に表示されます。

翌日の売上データの追加

翌日の売上データを追加するには：

1. 行6の行ヘッダーを右クリックしてコンテキストメニューを開きます。
2. メニューで、をクリックします。インサート 新しい行をワークシートに挿入します。
3. その結果、SUM OFFSET式はセルB7に移動し、行6は空になります。
4. セルをクリック A6.
5. 番号を入力してください5 5日目の売上合計が入力されていることを示します。
6. セルB6をクリックします。
7. 番号を入力してください$1458.25 を押して入る キーを押します。

セルB7が新しい合計に更新されます。$7137.40.

セルB7をクリックすると、更新された式= SUM（B2：OFFSET（B7、-1,0）） 数式バーに表示されます。

OFFSET関数には2つのオプション引数があります：高さと幅 , この例では使用されていませんでした。

これらの引数は、非常に多くの行が高く、多くの列幅の観点から出力の形状をOFFSET関数に伝えるために使用できます。

これらの引数を省略すると、関数はデフォルトで参照引数の高さと幅を代わりに使用します。この例では、高さが1行、幅が1列です。