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

図1


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

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

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

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

  1. SUM関数を組み合わせた動的な範囲指定に向けた仕込み
  2. SUM関数を組み合わせた動的な範囲指定を実施
【OFFSET関数】SUM関数を組み合わせた動的な範囲指定 -1- より

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

GIFイメージ

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

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

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

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

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

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

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

例題

サンプルデータ

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

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

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

図2

結果

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

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

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

 

図3

解法

関数式

関数式は次の通りです。

=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つに分解できます。

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

  1. 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,
)

 

  1. 上記の結果を、OFFSET関数の引数に渡し、指定したデータを取得
    (裏では、各セルの値を配列として保持している)
OFFSET(1.MATCH関数で指定した「日付」と「集計項目」の行列番号)
  1. 上記の結果を、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)

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

図4
SUM関数のブロック

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

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

最後に

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

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

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

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

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

参考記事

上記記事の読みやすさをさらに向上させるために、Excel関数に関する原理原則についてまとめた記事を紹介します。

数式の原則|Excel関数を学ぶ前に

Excelほど、馴染みがあり、広く浸透しているツールもないでしょう。 さらに、Excel関数はExcel作業を便利するパッケージであり、習得することで、大きな進歩につながりま…

数式の原則|Excel関数の種類は大まかに知っておこう

冷静に、スムーズに仕事ができるため、Excel関数の種類を大まかに知っておくことが大切です。 Excel 2019では、Excel関数が全486関数もありますが、Excel機能を上手に活用…

数式の原則|Excel関数にはそれぞれ構文がある

Excel関数には構文があります。それが、引数と戻り値です。構文に沿えばいいだけです。言わば、数学の公式のようなものです。理論を知らなくても、公式に当てはめれば答え…

数式の原則|Excel関数の引数とは

Excelの引数は、料理を完成させるための材料のようなものです。しかも、材料に種類があります。その種類を理解すると、Excel関数を上手く使いこなせるようになります。

数式の原則|Excelの検索ダイアログから、関数を探す

Excelの検索ダイアログに使えば、目的の関数を発見しやすくなります。WEB検索する前に、Excel関数の検索ダイアログを使ってみましょう。