OFFSETは、基準セルから指定した行数・列数だけずらした場所を参照する関数です。
さらに「高さ」と「幅」を指定すると、1セルではなく範囲を返せます。この性質を使うと、行数が増減する表の集計範囲を自動で追従させられます。
書式
=OFFSET(基準, 行数, 列数, [高さ], [幅])| 引数 | 内容 |
|---|---|
| 基準 | 起点となるセル |
| 行数 | 下方向にずらす数。マイナスで上方向 |
| 列数 | 右方向にずらす数。マイナスで左方向 |
| 高さ | 返す範囲の行数。省略時は1 |
| 幅 | 返す範囲の列数。省略時は1 |
基本の動き
=OFFSET(A1, 2, 1)A1から下に2・右に1ずらした B3セル を参照します。
ずらす数が 0 なら、そのセル自身です。
=OFFSET(A1, 0, 0) → A1と同じ範囲として返す
高さと幅を指定すると、単一セルではなく範囲になります。
=SUM(OFFSET(A1, 1, 0, 5, 1))A1の1つ下（A2）から5行×1列 ＝ A2:A6 の合計です。
範囲を返す関数なので、OFFSET単体をセルに入れても意味のある表示にはなりません。SUM や AVERAGE など、範囲を受け取る関数の中で使うのが基本です。
実用例1：データが増えても自動で追従する集計
行数が変わる表の合計を、範囲を書き換えずに出したい場合。
=SUM(OFFSET(A2, 0, 0, COUNTA(A2:A1000), 1))COUNTA で入力済みの行数を数え、その行数ぶんの範囲を作っています。データを追記すれば範囲が自動で伸びます。
ただし、単に合計するだけなら =SUM(A2:A1000) や =SUM(A:A) で十分です。空白セルは無視されるためです。OFFSETが要るのは、グラフの参照範囲など「範囲そのものを正確に渡す必要がある」場面に限られます。
実用例2：最新N件を取り出す
追記型の表から、下から5件を取り出す例です。
=AVERAGE(OFFSET(A1, COUNTA(A:A)-5, 0, 5, 1))COUNTA(A:A) で最終行を求め、そこから5行戻った位置を起点にしています。直近の推移だけを見たいダッシュボードでよく使う形です。
見出し行の有無で1行ずれるので、実際に作るときは結果を目で確かめてください。
実用例3：横方向にずらしてコピーする
数式を下方向にコピーしながら、参照は右方向に進めたい、という場面。
=OFFSET($B$1, 0, ROW()-1)ROW() は自分の行番号を返すので、下にコピーするたびに列が1つずつ右に進みます。行と列を入れ替えて取り出す用途です。
なお、単純な行列入れ替えなら TRANSPOSE のほうが簡単です。
注意点：揮発性関数である
OFFSETは INDIRECT と同じく揮発性関数です。シートのどこかが変更されるたびに再計算が走ります。
- – 数十個なら問題になりません
- – 数百個を超えると、ファイル全体が目に見えて重くなります
重いと感じたら、まずOFFSETとINDIRECTの数を疑ってください。
多くの場合、INDEX で置き換えられます。INDEXは揮発性ではありません。
=SUM(INDEX(A:A, 2) : INDEX(A:A, 6))INDEXは範囲の一部として : で連結でき、この書き方なら可変範囲を揮発性なしで作れます。
エラーの対処
#REF! が出る
ずらした先がシートの外に出ています。A1から上にずらす、A列から左にずらす、といった指定です。COUNTA の結果が想定より小さく、マイナス方向に行き過ぎているケースが多いです。
#VALUE! が出る
高さや幅に 0 以下を指定しています。高さ・幅は1以上でなければなりません。
結果が1つずれる
基準セルを含むかどうかの数え間違いです。OFFSET(A1, 0, 0, 5, 1) は A1:A5（A1を含む）、OFFSET(A1, 1, 0, 5, 1) は A2:A6（A1を含まない）です。見出し行を含めるかで結果が変わります。
まとめ
- – 書式は
=OFFSET(基準, 行数, 列数, 高さ, 幅) - – 高さ・幅を指定すると範囲を返せる
- – COUNTA と組み合わせると可変範囲が作れる
- – 揮発性関数なので多用すると重い
- – 置き換えられるなら INDEX を使う
コメントを残す