こんにちは、HARUです！

Excelで関数を用いて演算や集計をするときは、計算対象となる特定の範囲を指示します。

対して、こちらが指定した条件に応じて集計対象のデータを切り替えたり検索範囲をスライドしたりしたいケースがよくあります。

たとえば10月の出荷量合計を求めた表において、10月4日～10月10日期間の出荷量を取得したいとなったとします。

関数を編集モードにし、カラーリファレンスを手動で伸縮させることもできますが、値を入力したりリストから選択したりした日付にしたがって計算対象範囲が自動で限定されるしくみが理想です。

そこでこの記事では、集計対象のデータ範囲を柔軟に変更するテクニックをご紹介します。

特殊な挙動をする関数を取り上げますが、マスターするととてつもなく便利にお使いいただけますので、ぜひ最後までご一読ください！

OFFSET関数の基本動作

今回はOFFSET関数を使います。
offsetにはもともと「埋め合わせる」「相殺する」「補正する」といった意味がありますが、ITの分野では基準となる位置からの差（距離、ズレ）を表す値を示します。

まずはOFFSET関数の挙動をおさえましょう。

45個の値が入力された表の欄外にOFFSET関数を挿入します。

基準となる地点から移動する

第１引数「参照」には、基準となるセルを参照します。
今回はあらかじめ色がけしている真ん中のD6セルを参照します。

第２引数「行数」と第３引数「列数」に、それぞれ"3"と"2"をひとまず入力します。
これがどのような役割をするのか、結果を見ながら解説します。

OFFSET関数で必ず指示する引数は第３引数までなので、結果を見てみます。
すると、「40」が返されます。

第１引数「参照」で参照したD6セルから、第２引数「行数」に入力した"3"行分、さらに第３引数「列数」に入力した"2"列分進んだF9セルの値が返されたのです。

行数や列数にはマイナス値を入力することもできます。
行数を"-4"、列数を"1"とした場合、基準となるセルから4行戻り、1列進んだE2セルの値を返します。

OFFSET関数はこのように、第１引数で参照した基準セルから、第２引数・第３引数で指示した行数・列数分だけ移動した地点のセルのデータを返すのです。

参照範囲を変える

第２引数「行数」に"2"、第３引数「列数」に"1"と入力し、続けて第４引数「高さ」に"3"、第５引数「幅」に"2"と入力します。

すると、D6セルから2行・1列進んだE8セルを基点に、高さ3行・幅1列分の範囲がスピル機能で取得されました。

行数・列数と同じく、高さ・幅についてもマイナス値を指示できます。

また、行数や列数は必須入力の引数ではありますが、0と入力することで基準セルから移動することなく、参照範囲の可変のみ適用することもできます。
（下図はD6セル～D10セル（D6セルから5行・幅1列分）が選択された状態）

0値を入力せず、カンマ区切りのみで済ませても結果は同じです。
OFFSET関数の使い方として、行数・列数設定による移動はせずに基準セルから参照範囲だけを伸縮させるユースケースもあることをおさえておきましょう。

参照範囲を演算・集計する

前述までの数式を他の関数にネストすることで、OFFSET関数で取得したセル範囲の演算・集計ができます。

▶SUM関数で合計値を求めた場合
（D6セル～D10セルのデータが合算された状態）

▶AVERAGE関数で平均値を求めた場合
（D6セル～D10セルのデータの平均が取得された状態）

▶COUNTA関数で平均値を求めた場合
（D6セル～D10セルのデータの個数が取得された状態）

OFFSET関数の構成や基本動作をおさえたところで、実際のユースケースをみていきましょう。

指定期間の出荷量合計を求める

サンプルはとある商品の10月の出荷履歴です。
指定した開始日から終了日までの出荷量をF4セルに抜き出していきます。

開始日と終了日はあらかじめ設定したドロップダウンリストから選択します。

①OFFSET関数を挿入し、第１引数「参照」に出荷量の見出しを参照する。

②第２引数「行数」にMATCH関数を挿入し、日付の範囲において開始日が上から何行目に位置するかを取得する。

これにより、第２引数「行数」には開始日に応じて参照範囲のスタート位置を自動変更する役割をもたせている。

③第３引数「列数」には何も入力しない。
今回の合計対象範囲はC列のみなので、列数を指示する必要がないためだ。
※同じ考え方で、第５引数「幅」にも何も入力しない。

④第４引数「高さ」には、開始日から何日分を取り出すかを指示する。

まずは開始日と終了日の差分を求める。
ただし単に終了日から開始日を差し引くだけでは取得すべき日数が１日不足するため、"+1"の処理を追加する。

これにより、まずはリストから選択した開始日「10月4日」から終了日「10月10日」までのデータが取得できる。

⑤OFFSET関数をSUM関数でネストする。
結果、指定期間の出荷量の合計が求められる。

開始日と終了日を変更すると、計算対象範囲も自動更新される。

グラフの掲載対象期間を自動更新する

OFFSET関数による特定範囲のデータ取得をマスターすると、グラフ用のデータ範囲も自在に変えられます。

下図は、元データに入力した週別の販売実績から、当週を含む直近5週分のデータをグラフ用に取得したものです。

現時点は、最新の当年販売実績が入力されている11月第2週を基準セルとして、OFFSET関数でそこから4週遡った計5週分の販売状況を取り出しています。

ここで11月第3週の実績が確定したので、グラフの掲載期間を更新します。
OFFSET関数の第１引数「参照」を11/3週のI2セルに変更します。

これにより対象のデータ範囲がスライドし、グラフも自動更新されます。

いかがでしたか？

OFFSET関数で参照範囲を柔軟に可変させ、他の関数と組み合わせることで、データ集計や演算の幅が一気に広がります。

計算対象の期間やカテゴリをシンプルな操作で切り替えたいシーンで、ぜひ活用してみてくださいね！

↓↓Excel操作をとにかく高速化したい方へ↓↓