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

2023-02-20 2024-01-25

えくせるサイトウ

先日の記事『【OFFSET関数】SUM関数を組み合わせた動的な範囲指定 -1-』では、
「1.SUM関数を組み合わせた動的な範囲指定に向けた仕込み」を紹介しました。

今回は、「3.【計算】セル範囲を可変しながら、合計値や平均値を計算できる」の中で、
「2. SUM関数を組み合わせた動的な範囲指定を実施」に焦点を当てます。

主に、次のようなシーンで役立ちます。

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

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

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

“【OFFSET関数】本当の理解を目指して”より

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

SUM関数を組み合わせた動的な範囲指定を実施

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

動的な範囲指定とは、次のようなこを指します。

なお、OFFSET関数に関連する記事は次の通りです。

SUM関数を組み合わせた動的な範囲指定を実施
- 例題
最後に
- 参考記事

SUM関数を組み合わせた動的な範囲指定を実施

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

SUM関数を組み合わせた動的な範囲指定を実施しましょう。

MATCH関数と組み合わせます。

関数式はかなり複雑になります。

そのため、前回記事「【OFFSET関数】SUM関数を組み合わせた動的な範囲指定 -1-」を理解している前提となってますので、
不明な方は復習をお願いします。

例題

サンプルデータ

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

A列には日付、B列には商品名、C列には数量、D列には単価、E列には売上が入っています。

赤枠のセル「I2：日付」「I3：集計項目」をどれを選択しても、「I7」のような結果を得たいと思います。
まさに、可変であり、動的です。

結果

OFFSET関数とSUM関数に、さらにMATCH関数を組み合わせれば、
動的（日付範囲と集計項目を変更しても）に、3520 という結果が得られます。

セル日付を変更すると、自動的に合計値（≒ 動的）に変わります。

関数式入りのExcelファイル、アウトプットイメージは次の通りです。

関数式入りのExcelファイル.xlsx ダウンロード

解法

関数式

関数式は次の通りです。

=SUM(
OFFSET(
$A$1,
MATCH($I2,$A$1:$A$11,0)-1,
MATCH($I4,$A$1:$E$1,0)-1,
(
MATCH($I3,$A$1:$A$11,0)-1)
-(MATCH($I2,$A$1:$A$11,0)-1)
+1,
)
)

複雑な関数式の解読方法は、改めて別記事で取り上げたいと思いますが、

関数式は複雑な場合、「どんな関数式も型通りであること」を忘れずにいる姿勢が大切です。

各関数の引数を分解する

3つ関数があるため、大きく、3つに分解できます。

関数式の分解して理解することが大切ですが、詳しい分解方法は別記事で説明できればと思います。

MATCH関数で指定した「日付」と「集計項目」の行列番号を取得

MATCH($I2,$A$1:$A$11,0)-1,
MATCH($I4,$A$1:$E$1,0)-1,
(
MATCH($I3,$A$1:$A$11,0)-1)
-(MATCH($I2,$A$1:$A$11,0)-1)
+1,
)

上記の結果を、OFFSET関数の引数に渡し、指定したデータを取得
（裏では、各セルの値を配列として保持している）

OFFSET(1.MATCH関数で指定した「日付」と「集計項目」の行列番号)

上記の結果を、SUM関数の引数に渡し、合計値を算出

=SUM(2.上記の結果を、OFFSET関数の引数に渡し、指定したデータ)

次に、各関数の引数を分解していきましょう

大きく、3つの関数の関係性を理解しました。

あと、今までと同じように、各関数の引数ごとに、読みほどけばいいだけです。

各関数は次の記事で説明しています。

どんな複数の関数でも、関数が積み重なり、最終的な関数式が出来上がっているに過ぎません。

本例に限らず、「どんな関数式も型通りであること」を忘れずにいる姿勢でいてください。

MATCH関数のブロック

MATCH関数は4つ使用されています。同じ結果を得られる式があるため、実質は3つです。
数式のセル番地が何を参照しているかは、図2を確認してください。
「新しいタブで画像を開く」を実施すれば、拡大できます。

各MATCH関数で、「-1」にしている理由は、

OFFSET関数が0スタートであるのに対して、
MATCH関数は1スタートであるため、-1 しています。

OFFSET関数は基準となるセルを0として考えているため、MATCH関数と違いが生まれていると考えています。
詳しくは下記記事をご確認ください。

上記のMATCH関数式で、「終了日付 – 開始日付 + 1 = 集計」の対象期間を取得できます。
「+1」にしているのは、
一般的に、最初の数を「1」として数を数えるため、数の番号を数える際に「+1」する必要があるのです。
「新しいタブで画像を開く」を実施すれば、拡大できます。

OFFSET関数のブロック

A1セル（行3）から、

開始日付の行数分だけ下へ移動（行4）、

売上の列数分だけ右へ（行6）移動し、

その移動後の対象期間分（行6～行10）

のデータを取得しています。

SUM関数のブロック

OFFSET関数で取得したデータを、SUM関数で合計しています。

以上が、MATCH関数→OFFSET関数→SUM関数のロジックでした。
これにより、合計する対象期間が動的に変化しても、合計値を算出できる関数式を完成することができました。

最後に

以上で、「2. SUM関数を組み合わせた動的な範囲指定を実施」について解説してきました。

SUM関数とMATCH関数、OFFSET関数をうまく組み合わせることで、
範囲指定が可変であり動的な集計処理を実現することができます。

このような関数の組み合わせは、Excelの応用的な活用法の一例です。

SUM関数以外にも、AVERAGE関数やMAX関数、MIN関数などの計算系関数でも使用可能です。

Excelの使い方に慣れていくことで、さまざまな処理を効率的に行うことができます。