OFFSET関数の使い方｜動的範囲の作成と集計への活用

OFFSET関数は、基準セルから指定した行数・列数だけ移動した位置のセルや範囲を返す関数です。データが追加されても自動的に集計範囲を拡張したり、特定の条件に応じて参照先を変更したりと、動的な範囲指定に欠かせない関数です。この記事では、基本構文から実務での活用パターンまでを丁寧に解説します。

OFFSET関数の基本構文

=OFFSET(基準, 行数, 列数, [高さ], [幅])

引数の説明

引数	説明	必須/任意
基準	基準となるセル参照	必須
行数	基準から下方向に移動する行数（負の値で上方向）	必須
列数	基準から右方向に移動する列数（負の値で左方向）	必須
高さ	返す範囲の行数（省略時は基準と同じ）	任意
幅	返す範囲の列数（省略時は基準と同じ）	任意

基本的な動作

セルA1を基準に、2行下・1列右のセルを参照する場合は以下のように記述します。

=OFFSET(A1, 2, 1)

この数式はセルB3の値を返します。A1から下に2行（1行目→2行目→3行目）、右に1列（A列→B列）移動した位置です。

範囲を返す場合

高さと幅を指定すると、複数セルの範囲を返します。SUM関数などと組み合わせて使用します。

=SUM(OFFSET(A1, 1, 0, 5, 1))

A1の1行下（A2）を起点とする5行1列の範囲、つまりA2:A6の合計を返します。

データ追加に自動対応する動的範囲

OFFSET関数の最も実用的な使い方は、データの増減に応じて自動的に範囲を拡張する動的範囲の作成です。

COUNTA関数と組み合わせた動的範囲

売上データが以下のようにA列に随時追加されていく場合を考えます。

A列	B列
日付	売上金額
2026/1/1	150000
2026/1/2	230000
2026/1/3	180000
（追加予定）

データ件数に応じて自動的に合計範囲を調整する数式です。

=SUM(OFFSET(B1, 1, 0, COUNTA(B:B)-1, 1))

COUNTA(B:B)でB列のデータ件数（見出し含む）を数え、-1で見出し行を除外しています。データが追加されるたびにCOUNTAの結果が増えるため、合計範囲も自動的に拡張されます。

名前の定義に動的範囲を設定する

数式バーに毎回OFFSET関数を書くのは手間がかかります。名前の定義に動的範囲を設定すると、使い回しが簡単になります。

「数式」タブの「名前の管理」を開きます
「新規作成」で以下のように設定します

項目	設定値
名前	売上範囲
参照範囲	=OFFSET(Sheet1!$B$1,1,0,COUNTA(Sheet1!$B:$B)-1,1)

これで「売上範囲」という名前で動的な範囲を参照できます。

=SUM(売上範囲)
=AVERAGE(売上範囲)

グラフの参照範囲を自動拡張する

OFFSET関数で定義した動的範囲は、グラフのデータ範囲としても使用できます。データを追加するだけでグラフが自動更新される仕組みを構築できます。

設定手順

まず、グラフに使用するデータ範囲を名前の定義で動的に設定します。

名前	参照範囲
グラフ日付	=OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1,1)
グラフ売上	=OFFSET(Sheet1!$B$1,1,0,COUNTA(Sheet1!$B:$B)-1,1)

グラフへの適用

グラフのデータソースを編集し、系列の値に名前付き範囲を指定します。

=Sheet1!グラフ売上

項目軸のラベルにも同様に名前付き範囲を指定します。

=Sheet1!グラフ日付

これにより、シートにデータを追加するだけでグラフが自動的に更新されます。月次報告書など、定期的にデータが追加される資料で特に有効です。

MATCH関数との組み合わせ

OFFSET関数とMATCH関数を組み合わせると、検索結果に基づいて参照先を動的に決定できます。

特定の商品の売上を取得する

以下のような商品別売上表があるとします。

A列	B列	C列	D列
商品名	1月	2月	3月
ノートPC	500000	620000	480000
マウス	80000	95000	72000
キーボード	120000	110000	135000

セルF1に商品名、セルG1に月名が入力されているとき、対応する売上を取得する数式です。

=OFFSET(A1, MATCH(F1, A2:A4, 0), MATCH(G1, B1:D1, 0))

MATCH(F1, A2:A4, 0)で商品名の行位置を取得し、MATCH(G1, B1:D1, 0)で月の列位置を取得しています。2つのMATCH関数の結果をOFFSETの行数・列数に渡すことで、交差するセルの値を返します。

直近N件のデータを集計する

データの末尾からN件分だけを集計する場合にも、OFFSETとCOUNTAの組み合わせが有効です。

=AVERAGE(OFFSET(B1, COUNTA(B:B)-5, 0, 5, 1))

この数式は、B列のデータの末尾5件の平均を返します。COUNTA(B:B)-5で末尾5件の開始位置を算出し、高さ5の範囲を指定しています。直近5日間の平均売上を求めたい場合などに使えます。

実務での活用例

月次データの自動集計シート

各月のデータが行方向に追加されていく集計シートで、当月分だけを抽出する例です。

=SUM(OFFSET(C1, (MONTH(TODAY())-1)*30+1, 0, 30, 1))

月ごとに30行ずつデータが入っている前提で、現在の月に該当する30行分を合計しています。実際のデータ構造に合わせて行数の計算は調整してください。

在庫管理での直近入出庫の取得

在庫管理台帳から最新の入出庫データを参照する例です。

=OFFSET(A1, COUNTA(A:A)-1, 0)

A列の最後のデータ（最新の入出庫記録）を返します。COUNTA(A:A)-1でデータの最終行の位置を求めています。

OFFSET関数の注意点

揮発性関数である

OFFSET関数はINDIRECT関数と同様に揮発性関数です。セルの再計算のたびにすべてのOFFSET数式が再計算されるため、大量に使用するとブックのパフォーマンスが低下します。

代替手段の検討

Excel 2021やMicrosoft 365では、テーブル機能やスピル対応の関数（FILTER関数など）で動的範囲を実現できる場合があります。新しいバージョンを使用している場合は、これらの代替手段も検討してみてください。

参照先の確認が難しい

OFFSET関数は数式を見ただけでは参照先がわかりにくいという欠点があります。数式を検証する際は、F9キーで数式の一部を評価すると参照先を確認しやすくなります。

まとめ

OFFSET関数は、動的な範囲指定を実現する強力な関数です。以下のポイントを押さえておきましょう。

基準セルから指定した行数・列数だけ移動した位置を参照できる
COUNTA関数と組み合わせてデータ追加に自動対応する動的範囲を作成できる
名前の定義に設定することで、グラフの自動更新にも活用できる
MATCH関数との組み合わせで、検索結果に基づく動的な参照が可能
揮発性関数のため、大量使用時はパフォーマンスに配慮する

動的な範囲指定はExcelの自動化において非常に重要なテクニックです。OFFSET関数を使いこなすことで、データの追加や変更に強いシートを作成できます。