「OFFSET関数はなんとなく難しそう」と感じている方が多い理由は、引数が5つあって直感的にイメージしにくいからです。しかし構造を一度理解すると、他の関数では実現できない「動的な範囲」を自在に構築できる強力な関数であることがわかります。
たとえば「データを追加するたびにグラフの範囲も自動で広がる」「スクロールバーで参照行を移動する」「COUNTA関数と組み合わせて入力済み行だけを集計する」といった操作は、OFFSET関数なしには実現が難しい機能です。
本記事では、OFFSETの基本構文・5つの引数の意味・COUNTAとの組み合わせによる自動拡張範囲・可変グラフへの応用・よくあるエラーと対処法・INDIRECTとの違い・MOS Excel試験での位置づけを体系的に解説します。
OFFSET関数の基本構文
OFFSETの構文は次の通りです。
=OFFSET(基準, 行数, 列数, [高さ], [幅])| 引数 | 説明 | 省略 |
|---|---|---|
| 基準 | 移動の起点となるセルまたは範囲 | 必須 |
| 行数 | 基準から何行移動するか（正=下方向・負=上方向） | 必須 |
| 列数 | 基準から何列移動するか（正=右方向・負=左方向） | 必須 |
| 高さ | 返す範囲の行数。省略すると基準と同じ高さ | 省略可 |
| 幅 | 返す範囲の列数。省略すると基準と同じ幅 | 省略可 |
最もシンプルな例：A1セルを基準に2行下・1列右に移動したセルの値を取得する場合は次のように書きます。
=OFFSET(A1, 2, 1)この数式はB3セルの値を返します。A1から行方向に2つ（A3）、列方向に1つ（B3）移動した先がB3です。「行数」と「列数」をそれぞれゼロにするとA1自身を参照します。
「高さ」「幅」引数で範囲を返す仕組み
第4引数（高さ）と第5引数（幅）を指定すると、OFFSETは単一セルではなく範囲（レンジ）を返します。これがOFFSETの最大の特徴です。
たとえば次の数式は、B2セルを起点に「5行×1列」の範囲を返します。
=SUM(OFFSET(B2, 0, 0, 5, 1))B2:B6の5行分の合計と同じ結果になります。SUM・AVERAGE・COUNTなどの集計関数と組み合わせることで、「起点セルから指定行数分だけ集計」という動的な計算が実現できます。
| 数式の例 | 意味 |
|---|---|
| =SUM(OFFSET(B2, 0, 0, 5, 1)) | B2から5行・1列の範囲（B2:B6）を合計 |
| =AVERAGE(OFFSET(B2, 0, 0, 10, 1)) | B2から10行分（B2:B11）の平均 |
| =MAX(OFFSET(A1, 1, 1, 3, 3)) | B2:D4の3行×3列範囲の最大値 |
COUNTAと組み合わせた自動拡張範囲
OFFSETの実務で最も使われる組み合わせがCOUNTAとのセットです。データの行数をCOUNTAで動的に数え、それをOFFSETの「高さ」引数に渡すことで「入力済みの行数だけを範囲とする動的参照」が完成します。
=SUM(OFFSET(B1, 1, 0, COUNTA(B:B)-1, 1))この数式の意味は次の通りです。
- COUNTA(B:B)-1：B列全体の入力済みセル数から見出し行の1を引いた値（=データ行数）
- OFFSET(B1, 1, 0, データ行数, 1)：B1の1行下（B2）を起点に、データ行数分・1列幅の範囲
- SUM(…)：その動的範囲を合計
B列に新しいデータを追加するたびに、COUNTAの結果が増え、OFFSETの参照範囲が自動で広がります。従来の固定範囲=SUM(B2:B1000)と違い、「書いた分だけ集計する」を自動で実現できます。
名前付き範囲と組み合わせた可変グラフの作り方
OFFSETの最も実用度が高い応用がグラフの動的範囲設定です。データを追加するたびグラフが自動で更新される仕組みを作れます。
手順①：名前付き範囲にOFFSETを定義する
「数式」タブ→「名前の定義」→「新しい名前」ダイアログを開きます。
- 名前：売上データ（任意の名前）
- 参照範囲：=OFFSET(Sheet1!$B$2, 0, 0, COUNTA(Sheet1!$B:$B)-1, 1)
これで「Sheet1のB2を起点に入力済みデータ行数分」を指す動的な名前付き範囲が完成します。
手順②：グラフのデータ系列に名前付き範囲を指定する
グラフを右クリック→「データの選択」→系列を選択して「編集」→「系列値」欄に次のように入力します。
=Sheet1!売上データこれで、B列にデータを追加するたびにグラフの範囲も自動で広がります。毎月手動でグラフ範囲を修正する手間が不要になり、誤操作によるグラフ崩れも防げます。
スクロールバーと組み合わせた参照行の移動
Excelの「開発」タブのスクロールバーコントロールとOFFSETを組み合わせると、スクロールバーを動かすだけで参照行が変わるインタラクティブなダッシュボードを作れます。
- スクロールバーのリンクするセルをE1に設定（E1に1～100の整数が入る）
- 次の数式でE1の値に応じた行のデータを参照する
=OFFSET($A$1, $E$1, 0)E1が3のときはA4（A1から3行下）の値を表示します。スクロールバーを動かすとE1が変わり、表示されるデータ行が追随します。大量データの中から任意の行をピックアップして集計・表示する「移動式ビュー」に応用できます。
実務シナリオ別の活用パターン
シナリオ1：直近N件の平均を求める（移動平均）
B列に日別売上が入っており、G1セルに件数（例：7）を入力して直近7日分の平均を出すケースです。
=AVERAGE(OFFSET(B2, COUNTA(B:B)-1-G1, 0, G1, 1))入力済みデータの末尾からG1件分さかのぼった範囲を動的に参照します。G1を7に変えれば7日移動平均、30に変えれば30日移動平均になります。
シナリオ2：左に値を返す（VLOOKUPの代替）
VLOOKUPは検索列より左のデータを返せませんが、OFFSETとMATCHの組み合わせで左方向の検索も実現できます。
=OFFSET(C1, MATCH(G2, C:C, 0)-1, -2)C列で検索値を探し（MATCH）、見つかった行のC列から2列左（A列）の値を返します。検索列の左側にあるIDや管理番号を取得したい場面で役立ちます。ただしMicrosoft 365・Excel 2021以降では同じ操作をXLOOKUPで行う方がシンプルです。
シナリオ3：MATCH+OFFSETで行と列の交差点を取得する
クロス集計表（行ヘッダーが商品名・列ヘッダーが月名）から、指定した商品・月の交差点の値を取得します。
=OFFSET($A$1, MATCH(G2, $A:$A, 0)-1, MATCH(H2, $1:$1, 0)-1)G2に商品名、H2に月名を入力するだけで交差点の値が返ります。INDEX+MATCHでも同じ結果を得られますが、OFFSETで書く場合は「A1からの相対移動距離」という直感的なイメージで組みやすい点が特徴です。
よくあるエラーと原因・対処法
| エラー | 主な原因 | 対処法 |
|---|---|---|
| #REF! | 移動後の参照先がシートの範囲外（行0以下・列0以下・最大行列超過） | 行数・列数・高さ・幅の計算結果がシート範囲内に収まるかを確認する |
| #VALUE! | 行数・列数・高さ・幅に文字列・空白セルを渡している | 引数に数値が確実に入るようIFERRORやISNUMBER関数でガードする |
| 0や空白が返る | 移動先のセルが本当に空欄になっている・参照ずれ | OFFSET(基準,行,列)の移動量を一つずつ確認する。名前付き範囲の定義式を「数式」→「名前の管理」で再確認する |
| 高さ・幅が0または負 | COUNTAがヘッダー1行しかなくCOUNTA-1=0になっている | =MAX(1, COUNTA(B:B)-1)とすることで最低1行を保証する |
| グラフが更新されない | 名前付き範囲の定義先がブック名付きで正しく参照されていない | 系列値に「=ブック名.xlsx!名前」形式で正確に入力されているか確認する |
#REF!デバッグの基本手順：① 行数・列数の計算結果を別セルに出して数値を確認→ ② 高さ・幅が0以下や負になっていないかチェック→ ③ OFFSETの引数を一つずつ固定値に変えて絞り込む。この順番で進めると原因を素早く特定できます。
OFFSETとINDIRECTの使い分け
「動的に参照先を変える」という機能ではOFFSETとINDIRECTは似ていますが、仕組みと用途が異なります。
| 項目 | OFFSET | INDIRECT |
|---|---|---|
| 参照の方法 | 基準セルからの「移動量」で参照先を決める | 文字列を「アドレス」として解釈して参照先を決める |
| 可変範囲（高さ・幅） | 引数で直接指定できる | 単一セルが基本（範囲にするにはADDRESS関数との組み合わせが必要） |
| 揮発性関数 | 揮発性（シート操作のたびに再計算） | 揮発性（同様に再計算コスト大） |
| 典型的な用途 | 動的範囲の集計・可変グラフ・スクロール参照 | シート名や列名をセルから動的に指定して参照（「シート選択式集計」等） |
| 名前付き範囲との相性 | 定義式に直接使える（推奨） | テキストで名前を作るため間接的・複雑になりやすい |
原則：「可変サイズの範囲を動的に作りたい」場合はOFFSET、「セルに入力したシート名・列名を使って参照先を切り替えたい」場合はINDIRECTを選びます。どちらも揮発性関数（Excelが変更されるたびに全部再計算する）なので、大量データのシートでは計算が遅くなることがあります。パフォーマンスが気になる場合はINDEX関数（非揮発性）での代替を検討します。
OFFSETのパフォーマンス注意点
OFFSETは揮発性関数です。揮発性関数を多数含むシートでは、セル1つを編集するたびにシート全体が再計算されるため、データ量が多いと応答が遅くなります。
- 数十行の小～中規模データ：OFFSETをそのまま使って問題なし
- 数千～数万行の大規模データ：名前付き範囲のOFFSETを最小限に抑え、必要なセルのみに限定する
- 代替の検討：Excel 2021以降はFILTER・SORT・UNIQUEなどの動的配列関数が非揮発性で動的範囲を扱えるため、用途によってはOFFSETより適切な場合がある
- Excelテーブル（ListObject）の活用：データをテーブルとして定義すると、構造化参照で自動拡張する参照が書けるため、COUNTAとOFFSETの組み合わせを使わなくて済むケースも多い
MOS Excel試験でのOFFSETの位置づけ
OFFSETはMOS Excel 365＆2019（アソシエイト）の中核スキル項目ではなく、MOS Excel Expert（上級）レベルの応用スキルに相当します。アソシエイト試験ではSUM・AVERAGE・SUMIF・COUNTIFなどの標準関数が主な出題範囲ですが、Expertレベルでは名前付き範囲・関数のネスト・動的参照の理解が問われます。
試験の合否を左右する頻出スキルではないものの、「名前付き範囲の定義と管理」「関数を組み合わせた複合数式の作成」という文脈でOFFSETの概念理解が問われることがあります。また実務ではOFFSETを含むファイルを引き継ぐ場面が多く、意味を正確に読み解く力が必要です。
| 確認ポイント | 操作内容 | 対象レベル |
|---|---|---|
| OFFSET単体の基本構文理解 | =OFFSET(基準, 行数, 列数)で任意セルを参照できる | アソシエイト基礎 |
| 高さ・幅引数で範囲を返す | SUM(OFFSET(…, 高さ, 幅))で動的な集計範囲を作れる | アソシエイト応用 |
| 名前の定義でOFFSETを使う | 「名前の定義」ダイアログの参照範囲欄にOFFSET式を入力できる | Expert基礎 |
| COUNTAとの組み合わせ | COUNTA-1を高さに渡す自動拡張範囲式を作れる | Expert基礎 |
| 可変グラフへの適用 | 名前付き範囲をグラフ系列に設定してデータ追加で自動更新させる | Expert応用 |
| MATCHとの組み合わせ | MATCH結果を行数・列数に渡してクロス集計表の交差値を取得する | Expert応用 |
まとめ：OFFSETは「動く範囲」を作る関数
本記事のポイントをまとめます。
- OFFSETの本質：基準セルから「行・列の移動量」と「高さ・幅」を指定して、動的なセル・範囲を返す関数
- COUNTAとの組み合わせ：=OFFSET(B1, 1, 0, COUNTA(B:B)-1, 1) でデータ行数に連動した自動拡張範囲が完成する
- 可変グラフ：名前付き範囲にOFFSET式を定義し、グラフ系列に紐づけるとデータ追加で自動更新するグラフになる
- #REF!の主な原因：移動後の参照先がシート範囲外、または高さ・幅が0以下になっている。MAX(1, COUNTA(…)-1)で最低値を保証する
- INDIRECTとの使い分け：可変サイズの範囲はOFFSET、セルに入力したシート名・列名で参照を切り替えるにはINDIRECT
- 揮発性に注意：大規模データではシートの再計算が遅くなるため、OFFSETの使用箇所を最小限にする。Excelテーブルや動的配列関数（FILTER・SORT等）も選択肢になる
- 試験での位置づけ：MOS Excel Expertの「関数の組み合わせ・名前付き範囲管理」領域で問われる応用スキル
OFFSETを習得すると、「データを追加するたびに手動でグラフ範囲を直す」「集計式の参照行を毎月書き直す」といった繰り返し作業から解放されます。COUNTAと組み合わせた自動拡張範囲を一度実装してしまえば、以降はデータを入力するだけで集計・グラフが自動追従します。実務で「動く範囲」が必要になったとき、真っ先に思い出してほしい関数です。
