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


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

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

本記事は、OFFSET関数について解説します。 名前の由来は、SET(固定)をOFFにする ⇒ 可変と推察しています。 スタート地点のセルから、どれだけ(行数/列数)を動かし、どの…

  1. 【条件付き書式】セル範囲を可変しながら、条件付き書式を適用できる
  2. 【参照範囲】セル範囲を可変しながら、セル値の取得やグラフを作成することができる
  3. 【計算】セル範囲を可変しながら、合計値や平均値を計算できる
【OFFSET関数】本当の理解を目指して

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

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

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

  1. SUM関数を組み合わせた動的な範囲指定に向けた仕込み
  2. 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のセルは配列として保持しています)

次回に向けて(静的範囲から動的範囲へ)

引数「OFFSET($A$1,4,4,3,)」では、4,4,5 のように、OFFSET関数の引数を静的指定しています。

データの範囲が変化する場合、毎回範囲を手動で変更する必要があります。

そのため、動的な範囲指定が必要になります。

SUM関数を組み合わせた動的な範囲指定(2/2)では、動的な範囲指定について解説します。

参考記事