【OFFSET関数】SUM関数を組み合わせた動的な範囲指定 -1-

2023-02-17 2024-03-13

えくせるサイトウ

先日の基礎理論で『【OFFSET関数】本当の理解を目指して』を紹介しましたが、応用編を紹介します。
OFFSET関数の活用シーンは、主に次の3つでした。

【条件付き書式】セル範囲を可変しながら、条件付き書式を適用できる

【参照範囲】セル範囲を可変しながら、セル値の取得やグラフを作成することができる

【計算】セル範囲を可変しながら、合計値や平均値を計算できる

【OFFSET関数】本当の理解を目指して

今回の記事では、次の➌に関して、SUM関数を例に取り上げます。

今回の記事では、OFFSET関数とSUM関数を組み合わせますが、静的な指定にとどまっています。

OFFSET関数とSUM関数を組み合わせて、動的な範囲指定を実施するための仕込みが必要だからです。

SUM関数を組み合わせた動的な範囲指定に向けた仕込み
SUM関数を組み合わせた動的な範囲指定を実施

なお、本記事で画像が小さい場合は「新しいタブで画像を開く」を実施すれば、拡大できます。

SUM関数を組み合わせた動的な範囲指定に向けた仕込み

SUM関数を組み合わせた動的な範囲指定に向けた仕込みとして、まずは静的な範囲指定を理解しましょう。

例題

サンプルデータ

以下のようなサンプルデータを考えます。

A列には日付、B列には商品名、C列には数量、D列には単価、E列には売上が入っています。
2022年3月から2022年6月までの範囲を合計したいとします。

解法

関数式

次のように、OFFSET関数とSUM関数を組み合わせれば、3520 という結果が得られます。

OFFSET関数で指定範囲のデータを取得（裏では、セルを配列として保持）
SUM関数の引数に渡し、合計値を算出

引数「OFFSET($A$1,4,4,3,)」の詳細解説

OFFSET関数の引数「行数」「列数」の数え方は、OFFSET関数が0スタートであるため、-1 して数えます。

ややこしいですが、OFFSET関数の第3引数「高さ」と第4引数「幅」は1スタートです。

基準となるスタートセルは 0 スタートとして考えるためと思います。

ここでは、基準となるスタートセル「$A$1」が 0スタート になっているわけです。

次のようなイメージで、データを取得しています(裏ロジックの順番は正確には違います)。

OFFSET関数を用いることで、赤枠セル範囲のデータを取得できています。

ただ、少なくとも、SUM関数の場合は、OFFSET関数を使用した方が手間で、間違いやすいです。

では、なぜ、このようなことを実施したのか？

それは、SUM関数で指定するセル範囲を動的にするためです。

A1をスタート地点として、

4列右へ、

4行下へ、

4行目から3行分を取得（裏では、E5,E6,E7のセルは配列として保持しています）