トップ > 関数 > 検索・照合 > OFFSET

直近3か月の売上合計を、自動でずらして出す

OFFSET(オフセット)関数

月別売上の表で「直近3か月の合計」をいつも最新に保ちたい。月が進むたびに合計の範囲を手で選び直すのは、忘れやすく間違いやすい作業です。OFFSET は「基準のセルから、指定した行数・列数だけずれた場所の範囲」を返す関数です。データの件数を数える COUNT と組み合わせると、「最後の行から3か月分」という範囲が自動で決まります。

✅ これができます:新しい月の売上を足すだけで、「直近3か月の合計・平均」が自動で最新の範囲に切り替わるようになります

完成形(実行前 → 実行後)

実行前
実行前
実行後
実行後

数式を入れる前(左)は集計欄が空欄。入れた後(右)は、データの最後の3行(10月・11月・12月)の合計 487 が自動で出ます。新しい月を足せば、範囲は自動で後ろにずれます

これをコピペすれば動きます

直近nか月の合計(集計セルの F2 に入れる)

=SUM(OFFSET($B$2,COUNT($B$2:$B$13)-$E$2,0,$E$2,1))

直近nか月の平均(合計の代わりに平均が欲しいとき)

=AVERAGE(OFFSET($B$2,COUNT($B$2:$B$13)-$E$2,0,$E$2,1))

変えるのはここだけ

変える場所を示した図
数式の「変える場所」を番号で示した図。番号は下の表と対応しています
番号変える場所意味書き方・例
1起点のセル
$B$2
ずらす計算の出発点になるセル。ここでは売上データの先頭自分の表では「データの先頭セル」に読み替える
2数える範囲
$B$2:$B$13
データが何件あるかを COUNT で数える範囲。データを入れる場所として、予定される最大の行まで含めておく2年分なら $B$2:$B$25 のように、将来の追加分まで含めた範囲にする
3対象月数のセル
$E$2
「直近何か月分か」を決める数字が入ったセル。ここを変えるだけで合計の範囲が変わる3 を書けば直近3か月、6 を書けば直近6か月。数式を直さずにセルで調整できるのが、この書き方の利点

組み込み手順

  1. サンプル表を用意します。A列に月、B列に売上(12か月分)を並べます
  2. E2 に「直近何か月分か」の数字(この例では 3)を入れます
  3. F2 に1つ目の数式を貼り付けると、直近3か月の合計が出ます
  4. 平均も欲しい場合は、その下のセルに2つ目の数式を貼ります
  5. 翌月の売上を B14 に足すと、範囲が自動でずれて「直近3か月」が新しい3か月になります(②の範囲が B14 を含んでいる必要があります)

このページのサンプル表は、A2:B13 に12か月分の売上(万円単位:120/135/128/142/150/138/155/160/148/152/165/170)が入っています。直近3か月は 152+165+170=487 です。

Excelとスプレッドシートの違い

すべてのバージョンの Excel で使えます。INDIRECT と同じく「開くたびに再計算される」性質があり、大量に使うと重くなります。集計セル数個の規模なら問題ありません。Microsoft 365 では INDEX で範囲を作る代替も書けますが、このページの書き方がそのまま動きます。

Googleスプレッドシートでも OFFSET がそのまま使えます。数式・引数の順番は Excel と同じです。

カスタマイズ例

直近n週間の集計にしたい

考え方は同じです。週次データの列に対して、③の月数を「週数」に読み替えるだけです。

ずらした先の値を1つだけ取りたい

SUM で囲まずに OFFSET だけを使うと、ずれた先の1セルの値が返ります。例:=OFFSET($B$2,COUNT($B$2:$B$13)-1,0) で「最新の月の売上」が取れます。

範囲の高さを行数でも指定したい

OFFSET の4番目の引数が「高さ(行数)」、5番目が「幅(列数)」です。横方向の月次表なら、幅を使って横にずらす集計も作れます。

つまずきどころ

関連する関数・レシピ

次はこれ:SUMIF/COUNTIF(条件に合うものだけ合計・件数を数える)
検索・照合の次は条件集計です。まずは単一条件の SUMIF/COUNTIF から

よくある質問

Q. COUNT ではなく COUNTA ではダメ?

A. 数値データなら COUNT で十分です。途中に文字列や空白が混ざる表では、数え方が変わるので注意してください(空白は COUNT では数えません)。

Q. INDEX で書き直したほうがいい?

A. 再計算の重さを避けたい場合は、INDEX で範囲を作る書き方のほうが軽くなります。ただし数式は長くなります。件数が少ない集計なら OFFSET のほうが読みやすいです。

Q. Mac でも使える?

A. 使えます。Mac版 Excel と、ブラウザ版の Googleスプレッドシートで動作します。

PR(広告)

もっと体系的に学びたい人へ
独学で体系化するなら、講座で一気に学ぶのが近道です。

Udemy:Excelショートカット講座を見る Udemy:Googleスプレッドシート入門講座を見る