「基準のセルから何行・何列ずれた場所を参照したい」。そんな場面で活躍するのがOFFSET関数です。
単体でも便利ですが、SUM関数やCOUNTA関数と組み合わせるのが本領です。データが増えても自動で集計範囲が広がる仕組みを作れます。基本の書き方から実務で使える動的範囲の作り方まで解説します。
この記事は次のような人におすすめ
- OFFSET関数の基本的な使い方を知りたい
- データが増減しても自動で対応する集計式を作りたい
- MATCH関数やSUM関数との組み合わせ方を知りたい
OFFSET関数とは？
OFFSET関数は、基準のセルから指定した行数・列数だけ移動した位置にあるセルやセル範囲を返す関数です。
読み方は「オフセット関数」です。英語の offset は「ずらす」という意味で、基準点からの「ずれ」を表します。
最大の特徴は、第4・第5引数で「高さ」と「幅」を指定できる点です。任意のサイズの範囲を動的に生成できます。SUM関数やAVERAGE関数の引数に入れれば、データの増減に自動対応します。
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の値が返ります。
セル参照で移動量を指定する
行数・列数を直接入力せず、セルの値で指定することもできます。
=OFFSET(A1, B1, B2)B1に 3、B2に 2 が入っていれば、C4を参照します。値を変えるだけで参照先を切り替えられるのが便利です。
範囲を返す（高さ・幅の指定）
第4・第5引数を指定すると、OFFSET関数は複数セルの範囲を返します。
=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件の平均
直近の売上データだけを対象に平均を取りたい場合も、OFFSET関数が便利です。
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からその行数分だけ下にずれた隣の列（B列）を参照します。
TIP
この用途ではINDEX関数とMATCH関数の組み合わせが一般的です。OFFSETを使うケースは、行と列の両方を動的に動かしたい場合に向いています。
名前定義と組み合わせた動的な入力規則
OFFSET関数を名前定義に使うと、ドロップダウンリストの選択肢を自動で増減できます。
- A列にリストの選択肢を入力（A1が見出し）
- 「数式」タブ →「名前の管理」→「新規作成」
- 名前を「商品リスト」、参照範囲に次の数式を入力
=OFFSET(Sheet1!$A$2, 0, 0, COUNTA(Sheet1!$A:$A)-1, 1)- データの入力規則で「リスト」を選び、元の値に
=商品リストと入力
A列にデータを追加するだけで、ドロップダウンの選択肢が自動的に増えます。
よくあるエラーと対処法
| エラー | 主な原因 | 対処法 |
|---|---|---|
| #REF! | 移動先がシートの範囲外になっている | 行数・列数の値を確認する。基準セルから上方向にシートの外まで移動していないかチェック |
| #VALUE! | 引数に数値以外（文字列など）が入っている | 行数・列数・高さ・幅にはすべて数値を指定する |
| #REF! | 高さまたは幅に0を指定した | 高さ・幅は1以上の正の数を指定する。省略するか基準と同じサイズにする |
| 結果が0 | OFFSET単体の結果を確認しようとしている | OFFSET関数は範囲を返すだけなので、SUM等と組み合わせるか、Ctrl+Shift+Enterで配列数式にする |
| ファイルが重い | OFFSET関数を大量に使っている | OFFSET関数は揮発性関数のため、不要なセルでは通常の参照やINDEX関数に置き換える |
NOTE
OFFSET関数の結果を確認したいときは、数式バーで数式を選択して
F9キーを押すと、計算結果をプレビューできます。確認後はEscキーでキャンセルしてください。
似た関数との違い・使い分け
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の動的合計から試してみてください。
