検索・参照
OFFSET関数の使い方・読み方
読み方：OFFSET は「オフセット」と読みます。（一般的な読み方です。公式に読み仮名の定めはありません）
OFFSETは、基準セルから指定した行数・列数だけずらした場所を参照する関数です。
このページの中身（22項目）
この記事の書式と対応環境の出どころ（2026-08-26 確認）
- Google スプレッドシート ヘルプ：公式の書式は
OFFSET(セル参照, オフセット行, オフセット列, 高さ, 幅)
https://support.google.com/docs/answer/3093379 - Microsoft サポート：OFFSET 関数
https://support.microsoft.com/ja-jp/excel/functions/offset-function
関数の仕様は追加・変更されることがあります。書式が合わないときは、上の公式ページで最新をご確認ください。
さらに「高さ」と「幅」を指定すると、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は便利な関数ですが、揮発性関数と呼ばれる性質を持っています。これは、シート内のどこかが変更されるたびに、その変更と関係があるかどうかにかかわらず再計算されるという意味です。この性質を知らずに多用すると、ファイルが目に見えて重くなります。
通常の関数は、参照しているセルが変わったときだけ再計算されます。ある列の合計を出す数式は、その列に変更があったときだけ計算し直されます。関係のないシートのセルを書き換えても、何も起こりません。
揮発性関数はそうではありません。どこか一つのセルに文字を入力しただけで、ファイル内のすべてのOFFSETが再計算されます。さらに、OFFSETを参照している数式も連鎖して再計算されます。数個であれば体感できませんが、数百個になると入力のたびに待たされるようになります。
影響が出やすいのは、行数の多い表で作業列としてOFFSETを使っている場合です。一行ずつコピーして千行になっていれば、千個の揮発性関数が入力のたびに走ります。加えて、それを参照する集計が連鎖します。
したがって、OFFSETを使うかどうかの判断基準は「他の方法で書けないか」です。書けるなら、そちらを選んでください。INDEXは揮発性ではないため、同じことができる場面では常にINDEXが有利です。
INDEXで書き換えられる場面
OFFSETの用途の多くは、INDEXで置き換えられます。考え方を整理しておくと、書き換えの判断が早くなります。
基準のセルからずらして一つの値を取り出す場合は、INDEXで位置を直接指定すれば済みます。基準からの相対位置ではなく、範囲の中での絶対位置で考えるだけの違いです。
データが増えても自動で追従する範囲を作りたい場合も、INDEXで書けます。範囲の開始位置と終了位置をそれぞれ指定し、コロンでつなぐ形です。終了位置を、データの件数を数える関数で求めれば、行が増えたときに自動で広がります。この書き方は揮発性ではないので、行数が多くても軽いままです。
一方、INDEXでは書きにくい場面もあります。範囲の高さや幅そのものを可変にしたい場合、OFFSETのほうが素直に書けます。移動平均のように、常に直近N件を対象にする計算では、OFFSETの引数の意味がそのまま処理内容に対応します。
つまり、単純なずらしや範囲の伸縮はINDEXへ、高さと幅の両方を動的に決める必要がある場合はOFFSET、という使い分けになります。後者は数が限られるはずなので、揮発性の影響も小さく収まります。
動的な範囲を作るときの注意
データが増えても自動で追従する範囲は便利ですが、いくつか落とし穴があります。
まず、範囲の高さを件数から求める場合、数え方を間違えると範囲がずれます。数値だけを数える関数と、空白以外を数える関数では結果が違います。見出し行を含めるかどうかでも一行ずれます。範囲を作ったら、実際に何行が対象になっているかを確認してください。名前を定義して数式で範囲を作った場合、参照先を確認する画面で実際の範囲を見られます。
次に、データの途中に空白行があると、そこで数え方が変わります。空白以外を数える方法では空白行が含まれず、範囲が短くなります。データの持ち方として、途中に空白行を作らないことが前提になります。
三つ目に、動的な範囲をグラフの元データにする場合、そのままでは指定できないことがあります。名前として定義してから、その名前を指定する手順が必要です。
四つ目に、他の人がファイルを開いたときに、なぜその範囲になっているのかが分かりません。数式の中に隠れているためです。名前を定義するか、補助的な説明を残しておくと親切です。
直近N件を対象にする計算
OFFSETがもっとも自然に書ける用途が、直近の一定件数を対象にした計算です。移動平均や、最新数件の合計がこれにあたります。
考え方は単純で、データの最終行を求め、そこから遡ってN行分の範囲を作ります。最終行はデータの件数から求められるので、行が増えれば自動的に対象がずれていきます。
この計算を他の関数で書こうとすると、条件が複雑になります。INDEXで開始位置と終了位置を計算する方法もありますが、引数の意味が処理内容と対応しないため、後から読み解きにくくなります。OFFSETなら、基準からいくつずらして、高さいくつの範囲、という形がそのまま数式になります。
ただし、この用途でも件数が多い場合は注意が必要です。移動平均を千行分並べれば、千個の揮発性関数になります。表示用に上位数行だけ計算する、集計シートを分けるといった工夫で、数を抑えられます。
揮発性関数を数える
ファイルが重いと感じたとき、揮発性の関数がいくつあるかを把握するのが第一歩です。
揮発性の関数には、位置をずらして参照するもの、文字列から参照を作るもの、今日の日付を返すもの、乱数を返すものなどがあります。これらは、シート内のどこかが変更されるたびに再計算されます。
検索の機能で関数名を数えれば、おおよその数が分かります。数十個であれば影響は限定的ですが、数百個になると入力のたびに待たされるようになります。
数えたら、置き換えられるものから対処します。位置をずらす処理の多くは、位置を直接指定する関数で書けます。文字列から参照を作る処理も、条件分岐や検索で代替できることが多いものです。今日の日付は、一箇所に置いて各行から参照する形にします。
すべてを置き換える必要はありません。本当に必要な数個だけ残せば、体感は大きく改善します。
置き換えの作業は、一度に全部やらないでください。一箇所を書き換えて動作を確認し、それから広げます。控えを取ってから作業するのは当然です。
動的な範囲の代替手段
データが増えても自動で追従する範囲を作る方法は、複数あります。揮発性でない方法を優先してください。
もっとも基本的なのは、位置を指定する関数で範囲の開始と終了を指定し、コロンでつなぐ方法です。終了位置を件数から求めれば、行が増えたときに自動で広がります。揮発性ではないため、行数が多くても軽いままです。
もうひとつは、表の形式に変換する方法です。範囲を表として登録すると、行を追加したときに自動で範囲が広がります。数式側では表の名前を参照するだけで済みます。この方法がもっとも単純ですが、表の形式には制約があるため、既存のシートに適用できないこともあります。
三つ目は、範囲を広めに固定しておく方法です。想定される最大行数まで指定しておけば、追加のたびに広げる必要がありません。空白行が含まれることになりますが、集計の関数は空白を無視するため、多くの場合は問題ありません。ただし、範囲が大きすぎると動作が重くなります。
用途に応じて選んでください。集計の範囲であれば三つ目で十分なことが多く、グラフの元データや入力規則の参照先であれば一つ目か二つ目が必要になります。
「検索・参照」の関数を全体から見る
検索・参照に含まれる関数を、全体から見ておきます。同じカテゴリの関数は、つまずく場所も似ています。この記事のほかに16本あります。
| 記事 |
|---|
| ADDRESS |
| CHOOSE |
| COLUMN |
| COLUMNS |
| HLOOKUP |
| IMPORTDATA |
| IMPORTHTML |
| IMPORTRANGE |
| INDEX |
| INDIRECT |
| MATCH |
| ROW |
| ROWS |
| VLOOKUP |
| XLOOKUP |
| XMATCH |
このカテゴリで共通するつまずき
検索値の型と前後の空白で結果が変わります。掃除を先に済ませてください。1つで理解した内容は、同じカテゴリの他の関数にもそのまま使えます。
どれを使うかの決め方
検索列が左端にあるか、返したい列が複数あるかで使う関数が決まります。関数の一覧を眺めて選ぼうとすると、どれも当てはまるように見えて決まりません。
迷ったときは
→ 全関数の一覧 に目的別の索引があります。
数式を書く前の手順
手順を分けると、どこで詰まっているかが分かります。
1. 出したい結果を一文で書く
「何を、どの条件で、どんな形で出したいか」。この一文が書ければ、関数はほぼ決まります。書けないなら、まだ要件が固まっていません。
2. 元データの型を確認する
検索値の型と前後の空白で結果が変わります。掃除を先に済ませてください。=ISNUMBER(A2) で数値かどうかを確認できます。見た目が同じでも、数値と文字列は別物です。
3. 空欄と0の扱いを決める
未入力なのか、実績が0なのか。表の意味が変わります。どちらとして扱うかを決めてから書いてください。
4. 条件の数を数える
検索列が左端にあるか、返したい列が複数あるかで使う関数が決まります。将来条件が増える可能性があるなら、最初から複数条件に対応する形にしてください。
5. 共有先の環境を確認する
新しい関数は、古い環境で開くとエラーになります。社外に渡すファイルでは、この一点で選択肢が変わります。
6. 1行だけ書いて確認する
全行にコピーする前に、1行分の結果が正しいかを見てください。間違ったまま広げると、直す手間が増えます。
どの関数でも共通する確認
以下は、どの関数を使っていても同じです。
参照を固定したか
下方向にコピーする数式では、動いてはいけない参照を固定します。上の数行だけ見て判断しないでください。ずれは下のほうで表面化します。
範囲の行数が揃っているか
複数の範囲を渡す関数では、行数がずれると結果が狂います。エラーにならず値が返るため、気づきにくい種類の間違いです。
型が揃っているか
=ISNUMBER(A2)数値として入っているかを判定します。システムから書き出したデータでは、これが原因のことが多くあります。
前後に空白が入っていないか
=LEN(A2)&" / "&LEN(TRIM(A2))数が違えば、余分な空白が入っています。見た目では判別できません。
別の方法で検算したか
同じ数字を別の関数でも出して、突き合わせてください。1つの結果だけを見ても、正しいかどうかは判断できません。
最終行まで確認したか
一番下までスクロールして、結果を見てください。途中から値が変わっていないかを確認します。
引き継ぐときに残すこと
数式は動いていても、意図は残りません。渡す前に、次の点をシート内にメモしてください。3分で終わります。
何を出している数式か
一行で構いません。読めば分かると思っても、数か月後の自分には分かりません。
どの表を参照しているか
参照先のシート名と、更新の担当。別ファイルなら、その場所も書いてください。
該当しないときの扱い
空欄にしているのか、文言を出しているのか、エラーのまま残しているのか。書いていないと、受け取った側は空欄をデータなしと読みます。
触ってはいけない場所
列を挿入すると壊れる数式がある場合、必ず書き残してください。この一行があるだけで、引き継ぎ後の事故が大きく減ります。
使った関数の対応環境
新しい関数を使っている場合、古い環境では開けません。共有の範囲が広がる可能性があるなら、明記してください。
確定した期間は値にする
締めた月の集計は、値貼り付けにしてください。参照元を消しても壊れなくなり、ファイルも軽くなります。
このサイトの収録状況
全201本を、次のカテゴリに分けて収録しています。
| カテゴリ | 記事数 |
|---|---|
| 目的別 | 51本 |
| 集計 | 47本 |
| 文字列 | 31本 |
| 日付 | 19本 |
| 検索・参照 | 15本 ←この記事 |
| 条件 | 15本 |
| 機能 | 12本 |
| 配列・抽出 | 10本 |
| 関数一覧 | 1本 |
探し方の順番
関数名が分かっているなら検索窓から。やりたいことだけ決まっているなら「目的別」から。近い関数を比べたいなら、同じカテゴリの一覧から入ってください。
記事の構成
どの記事も、書式・実例・つまずきやすいところ・似た関数との使い分けを同じ順番で並べています。1本読めば、他の記事も同じ場所を探せます。
よくある質問
Q. 参照が無効というエラーが出ます
ずらした先がシートの範囲外です。負の値で上や左に行き過ぎているか、高さや幅にゼロを指定している可能性があります。
Q. 高さと幅を省略するとどうなりますか
基準と同じ大きさになります。一つのセルを基準にすれば、一つのセルが返ります。
Q. 結果が範囲になるのはどういうときですか
高さや幅に二以上を指定した場合です。範囲として返るので、そのまま集計関数に渡せます。単独のセルに入れると、環境によってはエラーになります。
Q. ファイルが重いのですが原因ですか
数が多ければ原因になります。まずOFFSETの数を数えて、置き換えられるものからINDEXに変えてください。
Q. 名前の定義に使えますか
使えます。動的な範囲を名前として定義する定番の方法です。ただし揮発性の性質は変わりません。
Q. 削除した行を参照するとどうなりますか
OFFSETは位置で参照するため、行を削除しても参照が壊れません。これは利点でもあり、意図せず別の行を指してしまう危険でもあります。
Q. 条件付き書式で使えますか
使えますが、揮発性の影響が大きくなります。条件付き書式は表示のたびに評価されるため、他の方法を優先してください。
Q. スプレッドシートとExcelで違いはありますか
基本的な動作は同じです。ただし揮発性の影響の出方は環境によって差があります。
まとめ
- 書式は
=OFFSET(基準, 行数, 列数, 高さ, 幅) - 高さ・幅を指定すると範囲を返せる
- COUNTA と組み合わせると可変範囲が作れる
- 揮発性関数なので多用すると重い
- 置き換えられるなら INDEX を使う