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

2023-02-17 2024-06-21

えくせるサイトウ

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

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

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

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

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

今回の記事では、3の利用シーンを例に取り上げます。
以下の2つステップを実施します。

SUM関数を組み合わせた動的な範囲指定に向けた仕込み　⇒ 今回：まずは静的範囲の関数を作成
SUM関数を組み合わせた動的な範囲指定を実施　⇒ 次回：静的範囲から動的範囲へ

OFFSET関数の基本
ステップ1：仕込み　⇒　静的範囲
- 例題
  - - サンプルデータ
    - 解法
      - 関数式
      - 引数「OFFSET($A$1,4,4,3,)」の詳細解説
次回に向けて（静的範囲から動的範囲へ）

OFFSET関数の基本

OFFSET関数は、指定した基準セルから特定の行と列の位置にある範囲を返すExcel関数です。この関数の構文は次のようになります：

OFFSET(参照,行,列,[高さ],[幅])

例えば、A1セルから2行下、3列右の範囲を取得したい場合、OFFSET(A1, 2, 3)という形で使用します。

こちらから、より丁寧な解説をご覧いただけます。

ステップ1：仕込み　⇒　静的範囲

例題を元に、ステップ1の操作です。

例題

サンプルデータ

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

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のセルは配列として保持しています）