「データが増えるたびに集計範囲を手で直している」。スプレッドシートでそんな手間、ありませんか？
毎回セル範囲を修正するのは面倒ですし、直し忘れると集計がズレてしまいます。
スプレッドシートのOFFSET関数を使えば解決できます。基準セルからの移動量を指定して、動的に範囲を作れる関数です。基本の書き方から実務で使える動的集計の作り方まで紹介します。
スプレッドシートのOFFSET関数とは？
OFFSET関数は、基準セルから指定した行数・列数だけ移動した位置のセルやセル範囲を返す関数です。
読み方・語源
OFFSET関数は「オフセット関数」と読みます。英語の「offset」は「ずらす」という意味です。基準セルからの「ずれ」を指定して参照先を決めます。
OFFSET関数でできること
最大の特徴は、第4・第5引数で「高さ」と「幅」を指定できる点です。任意のサイズの範囲を動的に生成できます。SUM関数の引数に入れれば、データの増減に自動で対応する集計式が作れますよ。
NOTE
OFFSET関数は「揮発性関数」です。シート内のどこかのセルが変更されるたびに再計算されます。大量に使うとシートの動作が重くなることがあるので注意してください。
OFFSET関数の書き方（構文と引数）
基本構文
=OFFSET(セル参照, 行数, 列数, [高さ], [幅])引数の説明
| 引数 | 必須/省略可 | 説明 |
|---|---|---|
| セル参照 | 必須 | 移動の起点となるセルまたはセル範囲 |
| 行数 | 必須 | 基準から下方向に移動する行数。負の数で上方向 |
| 列数 | 必須 | 基準から右方向に移動する列数。負の数で左方向 |
| 高さ | 省略可 | 返す範囲の行数。省略時は基準と同じ行数 |
| 幅 | 省略可 | 返す範囲の列数。省略時は基準と同じ列数 |
TIP
行数・列数に
0を指定すると移動しません。「行だけずらしたい」ときは列数を0にします。
OFFSET関数の基本的な使い方
単一セルを参照する
セルA1を基準に、2行下・1列右のセルを参照する例です。
=OFFSET(A1, 2, 1)A1から2行下・1列右なので、B3の値が返ります。
上方向・左方向に移動する
行数や列数に負の数を指定すると、反対方向に移動できます。
=OFFSET(C5, -2, -1)C5から2行上・1列左に移動するので、B3の値が返ります。
範囲を返す（高さ・幅の指定）
第4・第5引数を指定すると、複数セルの範囲を返します。
=SUM(OFFSET(A1, 0, 0, 5, 1))A1を起点に、高さ5行・幅1列の範囲（A1:A5）の合計です。この「範囲を返す」使い方が実務では最も重要です。
OFFSET関数の実務活用パターン
SUM + OFFSET + COUNTAで動的合計
データが随時追加される表で、常に最新の合計を出したい場面はよくありますよね。OFFSET関数とCOUNTA関数（空白以外のセルを数える関数）を組み合わせましょう。
B列に売上金額が入っていて、B1が見出し行だとします。
=SUM(OFFSET(B1, 1, 0, COUNTA(B:B)-1, 1))この数式の動きを分解するとこうなります。
COUNTA(B:B)-1でB列のデータ件数を取得します（見出し行を除く）OFFSET(B1, 1, 0, ...)でB2を起点にした範囲を生成します- SUM関数でその範囲を合計します
データを1行追加すると、COUNTAの結果が1つ増えます。OFFSETの高さも自動的に1行広がるのがポイントです。
AVERAGE + OFFSETで直近N件の平均
直近のデータだけを対象に平均を出したい場合にも使えます。
B列に売上金額、E1に「直近何件分か」の件数を入力するとします。
=AVERAGE(OFFSET(B1, COUNTA(B:B)-E1, 0, E1, 1))E1に 5 と入力すれば、最新5件の平均が計算されます。値を変えるだけで件数を切り替えられるのが便利ですよ。
MATCH + OFFSETで検索結果のずれた列を取得
MATCH関数（検索値の位置を返す関数）で見つけた行位置を使い、隣の列の値を取得するパターンです。
A列に商品名、B列に価格が入っている表で、商品名から価格を取得します。
=OFFSET(A1, MATCH("りんご", A:A, 0)-1, 1)MATCH関数が「りんご」の行番号を返します。OFFSET関数がA1からその行数分だけ下に移動し、1列右（B列）を参照します。
TIP
この用途ではINDEX関数とMATCH関数の組み合わせが一般的です。OFFSETが活きるのは、行と列の両方を動的に動かしたい場合です。
よくあるエラーと対処法
| エラー | 主な原因 | 対処法 |
|---|---|---|
| #REF! | 移動先がシートの範囲外 | 行数・列数の値を確認する |
| #REF! | 高さまたは幅に0を指定した | 高さ・幅は1以上の正の数にする |
| #VALUE! | 引数に数値以外が入っている | 行数・列数・高さ・幅にはすべて数値を指定する |
| 意図しない結果 | 基準セルの位置がずれている | 基準セルに $A$1 のような絶対参照を使う |
TIP
OFFSET関数の参照先を確認したいときは、数式が入ったセルを選択してみてください。参照範囲が色付きの枠で表示されるので、意図した位置を指しているか目視で確認できます。
似た関数との違い・使い分け
OFFSET関数 と INDEX関数
| 比較項目 | OFFSET関数 | INDEX関数 |
|---|---|---|
| 参照方法 | 基準セルからの相対移動 | 範囲内の行番号・列番号で指定 |
| 範囲の返却 | 高さ・幅を指定して範囲を返せる | 単一セルを返す |
| 揮発性 | 揮発性関数（再計算が多い） | 非揮発性（動作が軽い） |
| 使い分け | 動的範囲の生成（SUM+OFFSET等） | 固定範囲からの値取得（INDEX+MATCH等） |
単一セルの値を取得するだけならINDEX関数が有利です。高さ・幅を動的に変えて範囲を生成する場面がOFFSET関数の出番です。
OFFSET関数 と INDIRECT関数
| 比較項目 | OFFSET関数 | INDIRECT関数 |
|---|---|---|
| 参照の指定方法 | 基準セル＋行数・列数の数値 | 文字列で参照先を指定 |
| 範囲の返却 | 高さ・幅で範囲サイズを指定 | 文字列で”A1:A10″のように指定 |
| 揮発性 | 揮発性 | 揮発性 |
| 使い分け | 相対位置で動的範囲を作る | シート名やセルアドレスを文字列で組み立てる |
OFFSETは数値で位置を指定します。INDIRECTは文字列で参照先を組み立てます。別シートの参照ならINDIRECT関数、データ件数に応じた範囲変更ならOFFSET関数が向いています。
まとめ
OFFSET関数は、基準セルからの相対位置で参照先を動的に変えられる関数です。
- 基本は
=OFFSET(基準, 行数, 列数)の3引数 - 第4・第5引数で高さ・幅を指定すると範囲を返せる
- SUM関数+COUNTA関数と組み合わせると、データ追加に自動対応する集計式が作れる
- 揮発性関数のため、大量使用は避けてINDEX関数を検討する
「数式を直す手間」をなくせるのがOFFSET関数の強みです。まずはSUM+OFFSET+COUNTAの動的合計から試してみてください。
