【OFFSET関数】SUM関数を組み合わせた動的な範囲指定 -2-
えくせるサイトウ
先日の記事では、まずは静的範囲の関数を作成に関してです。つまり、動的にするための仕込みでした。
- SUM関数を組み合わせた動的な範囲指定に向けた仕込み ⇒ 前回：まずは静的範囲の関数を作成
- SUM関数を組み合わせた動的な範囲指定を実施 ⇒ 今回：静的範囲から動的範囲へ
ステップ2：静的範囲から動的範囲へ
先日の記事で、まずは静的な範囲指定を作成しました。
ステップ2では、SUM関数を組み合わせた動的な範囲指定を実施しましょう。
例題
サンプルデータ
以下のようなサンプルデータ(先日の記事と同じ)を考えます。
A列には日付、B列には商品名、C列には数量、D列には単価、E列には売上が入っています。
赤枠のセル「I2：日付」「I3：集計項目」をどれを選択しても、「I7」のような結果を得たいと思います。
まさに、可変であり、動的です。
結果
OFFSET関数とSUM関数に、さらにMATCH関数を組み合わせれば、
動的（日付範囲と集計項目を変更しても）に、3520 という結果が得られます。
セル日付を変更すると、自動的に合計値（≒ 動的）に変わります。
関数式入りのExcelファイル、アウトプットイメージは次の通りです。
解法
関数式
関数式は次の通りです。
=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関数と違いが生まれていると考えています。
詳しくは下記記事をご確認ください。
【OFFSET関数】本当の理解を目指して
本記事は、OFFSET関数について解説します。 名前の由来は、SET(固定)をOFFにする ⇒ 可変と推察しています。 スタート地点のセルから、どれだけ(行数/列数)を動かし、どの…
データ分析ドットコム 
上記のMATCH関数式で、「終了日付 – 開始日付 + 1 = 集計」の対象期間を取得できます。
「+1」にしているのは、
一般的に、最初の数を「1」として数を数えるため、数の番号を数える際に「+1」する必要があるのです。
「新しいタブで画像を開く」を実施すれば、拡大できます。
OFFSET関数のブロック
A1セル（行3）から、
開始日付の行数分だけ下へ移動（行4）、
売上の列数分だけ右へ（行6）移動し、
その移動後の対象期間分（行6～行10）
のデータを取得しています。
SUM関数のブロック
OFFSET関数で取得したデータを、SUM関数で合計しています。
以上が、MATCH関数→OFFSET関数→SUM関数のロジックでした。
これにより、合計する対象期間が動的に変化しても、合計値を算出できる関数式を完成することができました。
最後に
SUM関数とMATCH関数、OFFSET関数をうまく組み合わせることで、
範囲指定が可変であり動的な集計処理を実現することができます。
このような関数の組み合わせは、Excelの応用的な活用法の一例です。
SUM関数以外にも、AVERAGE関数やMAX関数、MIN関数などの計算系関数でも使用可能です。
Excelの使い方に慣れていくことで、さまざまな処理を効率的に行うことができます。
