検索・参照中級
OFFSET関数を見て理解する
基準となるセル・範囲を選び、行と列の移動量、必要な高さと幅を指定して参照先を動かす関数です。
3つのポイント
- 基準のセル・範囲を選ぶ
- 行・列の移動量を指定する
- 高さ・幅で参照先の範囲を決める
表の参照範囲を行・列方向へ移動する
サンプル数式
- 基準となる範囲
- 行の移動量
- 列の移動量
- 高さ（行数）
- 幅（列数）
いまの結果：
先頭行：50 ｜ 60
入力・条件：5項目
- 基準となる範囲B2:C3（2行 × 2列）先頭行：10 ｜ 20
- 行の移動量F2：1
- 列の移動量F3：1
- 高さ（行数）F4：2
- 幅（列数）F5：2
B2:C3から1行・1列移動し、C3:D4（2行×2列）を参照します。 結果はH2からスピル表示します。
サンプルデータ
※ Excel と Google スプレッドシートで結果や挙動が異なる場合、Excel の仕様を基準にしています。
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 商品 | 1月 | 2月 | 移動量・寸法 | 結果 | ||||
| 2 | 10 | 20 | 30 | 50 | 60 | ||||
| 3 | 40 | 50 | 60 | 80 | 90 | ||||
| 4 | 70 | 80 | 90 | ||||||
| 5 | 100 | 110 | 120 |
F2、F3、F4、F5に値を入力すると、結果と処理順が変わります。
5つの引数
色付きの丸数字は、サンプル数式と引数ガイドで同じ引数を示しています。表にも同じ番号があれば、その引数で指定しているセルを示します。
B2:C3表のセル: B2:C3B2:D5から範囲を選び、その左上を行・列移動の起点にします。
F2表のセル: F2正なら下、負なら上へ参照位置を移します。
F3表のセル: F3正なら右、負なら左へ参照位置を移します。
F4表のセル: F4返す範囲の高さを指定します。負の値はExcel 2021実測に従って扱います。
F5表のセル: F5返す範囲の幅を指定します。負の値はExcel 2021実測に従って扱います。
この数式を日本語にすると
B2:C3を基準に下へ1行・右へ1列ずらし、高さ2行・幅2列のC3:D4を参照します。H2から2行×2列に結果が広がります。
この説明は、最初に表示されているサンプルデータをもとにしています。入力値を変更しても説明文は変わりません。
処理の流れ
手順1〜6を順番に確認しましょう。表内の小さな角丸数字は、対応する手順を示します。
基準範囲を確認する
B2:C3B2:C3を基準にします。この範囲の左上が移動の起点です。
行方向の移動量を確認する
F2行の移動量：1を指定します。
列方向の移動量を確認する
F3列の移動量：1を指定します。
高さを確認する
F4高さ（行数）：2を指定します。
幅を確認する
F5幅（列数）：2を指定します。
移動先と結果を確認する
C3:D4H2B2:C3から1行・1列移動し、C3:D4（2行×2列）を参照します。 結果はH2からスピル表示します。
対応アプリ
- Excel
- Google スプレッドシート
関数自体が対応するアプリです。教材例の数式や再計算が、両アプリで同じように動くことを保証するものではありません。
この例の注意点
- OFFSETはセル番地の文字列を返すADDRESSとは異なり、移動先の参照そのものを返します。動的配列では複数セルがスピル表示されます。
- rowsは下が正・上が負、colsは右が正・左が負です。高さ・幅を省略すると基準範囲と同じ寸法を引き継ぎます。
- Excel 2021実測では負の高さ・幅が逆方向の範囲を表し、小数の寸法にも特有の挙動があります。Google スプレッドシートとは異なることがあります。
- 参照先がExcelワークシート範囲外の場合は#REF!、スピル先が塞がれている場合は#スピル!です。教材表示範囲を超えることとは異なります。
- この教材はExcel 2021の実測187件を根拠とし、入れ子の数式、外部ブック、非表示シート、全関数による参照消費は対象外です。
一般構文
対応：Excel / Google スプレッドシート ・ 構文：構文は同じ
- Excel 2021では負の高さ・幅が逆方向へ範囲を広げる実測例があり、Sheetsでは#VALUE!になるケースがあります。1未満の小数の範囲寸法にも差があります。
- 高さ・幅が0のときExcel 2021は#REF!、Sheetsは#VALUE!となる実測例があります。全角数値文字列や空文字列も異なる場合があります。
- 空白参照を直接表示・スピルすると、Excelは0、Sheetsは空欄として見える実測例があります。
- スピル先に既存値があるとExcelでは#スピル!、Sheetsでは#REF!を観測しました。妨害を除去すると両方で復旧しました。
Excel
=OFFSET(reference, rows, cols, [height], [width])[] は省略できる引数です。
省略時
height- 省略すると基準範囲の高さを引き継ぎます。
width- 省略すると基準範囲の幅を引き継ぎます。
Google スプレッドシート
=OFFSET(reference, rows, cols, [height], [width])[] は省略できる引数です。
省略時
height- 省略すると基準範囲の高さを引き継ぎます。
width- 省略すると基準範囲の幅を引き継ぎます。
公式資料・確認範囲
公式資料
- Microsoft Excel 公式構文・省略時の扱い
- Google スプレッドシート 公式対応状況・構文・省略時の扱い
実機確認
- Excel 2021 / Google スプレッドシート省略時の扱い・計算結果・入力の扱い・エラー・配列展開を確認済み
この教材で扱わない範囲
- OFFSETは参照を返す関数です。この教材ではA1形式の単一矩形参照と直接値表示を体験します。SUMやROWSなど他関数とのネスト式は表中の例示にとどめ、数式パーサーで評価しません。
- Excelワークシートの有効範囲と教材で描画できる小さな表は別です。境界外の参照エラーと表示上限を区別します。
次に学ぶ関数
INDIRECT
OFFSETで基準から参照範囲を移動したら、INDIRECTで文字列から参照先を切り替える仕組みを学びます。
次に学ぶ