OFFSET関数について①

ここからは LEVEL 8 の最終盤、上級者の領域 に踏み込んでいきます。

第1弾は OFFSET関数（オフセット関数） です。「オフセット」は英語で「ずらす、相殺する」という意味があります。

文字通り、基準のセルから「○行○列ずらした位置」を指し示す関数 です。

これだけ聞くと「何の役に立つのか」と思われるかもしれません。実際、初級・中級ではほぼ登場しない関数です。

けれども、OFFSET を理解すると、Excel の表現力が一気に跳ね上がります。

表のサイズが変わっても 自動で追随する集計範囲
データを入力するたびに 勝手に伸びるドロップダウンリスト
月を切り替えると 参照範囲が動くグラフ

こうした「動的な仕組み」の根っこにあるのが OFFSET です。最初は構文に戸惑いやすい関数ですが、丁寧に分解していきましょう。

OFFSET関数の構文

=OFFSET(参照, 行数, 列数, 高さ, 幅)

引数は5つあります。

参照：スタート地点になるセル（または範囲）
行数：基準から 下方向 へ何行ずらすか（マイナスなら上）
列数：基準から 右方向 へ何列ずらすか（マイナスなら左）
高さ：返す範囲の行数（省略すると基準と同じ高さ）
幅：返す範囲の列数（省略すると基準と同じ幅）

「参照セルから、行と列をずらして、その先の範囲を取ってくる」── これがOFFSETの仕事です。

まずは一番シンプルな使い方

引数を全部使うと混乱するので、前半3つだけ で動かしてみます。

:::excel
A1=商品コード
B1=商品名
C1=単価
A2=P001
B2=コーヒー
C2=400
A3=P002
B3=紅茶
C3=350
A4=P003
B4=ケーキ
C4=500
A5=P004
B5=プリン
C5=300
:::

E1セルに、

=OFFSET(A1, 2, 1)

結果：紅茶

「A1 から 下に2行、右に1列 ずれたセル」── つまり B3 のセル を指しています。

OFFSET の動きは、Excel の中で 「指差し」をしている ような感覚で捉えると分かりやすいです。「A1 を基準に、ここから下に2、右に1。そこを見てね」── そう Excel に伝えているわけです。

マイナス指定で「上方向・左方向」へ

行数・列数には マイナスの値 も入れられます。

=OFFSET(C5, -1, -2)

C5 から 上に1行、左に2列 ずれた位置 ── A4 を指します。結果：P003

「今いる場所からの相対位置」で考えられるのが、OFFSET の柔軟さです。

「範囲」を返すこともできる

ここから OFFSET の真価です。引数の 4番目（高さ）と5番目（幅） を指定すると、「点（セル1つ）」ではなく「範囲（セルの塊）」を返す ようになります。

=OFFSET(A1, 1, 2, 4, 1)

A1 を基準に、
下に1行、右に2列ずれた位置（= C2 セル）から、
高さ4行、幅1列 の範囲

つまり C2:C5 という範囲を返します。

これを SUM 関数で囲うと、

=SUM(OFFSET(A1, 1, 2, 4, 1))

→ C2:C5 の合計 = 1550（=400+350+500+300）

「OFFSET で動く範囲を作って、別の関数で集計する」── これが OFFSET の使い道の中心です。

単独では結果が見えにくい

OFFSET をセルに単独で入れて「範囲」を返すと、スピル対応の Excel では範囲が広がって表示 されますが、それ以前のバージョンでは #VALUE! などのエラーになることがあります。

OFFSET は「範囲を作る関数」と理解して、SUM や COUNTA、AVERAGE など、範囲を引数に取る関数と組み合わせて使う のが基本スタイルです。

高さと幅は「動的に決められる」

ここがいちばん大事なポイント。

OFFSET の 「高さ」「幅」も、固定の数字ではなく、関数で動的に計算できる のです。

たとえば、COUNTA関数（レッスン034）で「A列に入力されているセルの数」を数えて、それを OFFSET の高さに渡せば、

=SUM(OFFSET(C2, 0, 0, COUNTA(A2:A1000), 1))

C2 を基準に
ずれゼロ（C2のまま）
高さは「A2:A1000 の入力数」← データが増えれば自動で増える
幅は1列

データの追加に合わせて、合計範囲も自動で伸びる仕組み が作れます。

これがテーブル（レッスン070）の 自動範囲拡張 に近い動きを、テーブル化せずに実現する方法です。

詳しい応用例は次回のレッスン073でじっくり扱いますが、「範囲を動的にできる」というイメージだけ、いま持っておいてください。

OFFSET vs INDEX

ここで、似た役割の関数 INDEX関数（レッスン054） との違いをひと言。

INDEX ：あらかじめ範囲を渡しておいて、その 何行目・何列目 を取り出す
OFFSET ：基準セルから 何行・何列ずらして、そこから 指定サイズの範囲 を作る

INDEX は「既にある範囲の中の1点を指す」のに対し、OFFSET は「範囲そのものを作る」関数です。役割が違うのですね。

「集計関数の 範囲指定 を動的にしたい」ときは OFFSET、「ルックアップ的に1つの値を取り出したい」ときは INDEX、という使い分けが基本です。

OFFSET は「揮発性関数」

OFFSET を使うにあたって、必ず知っておいてほしい注意点 が1つあります。

OFFSET は 揮発性関数（volatile function） と呼ばれる、特殊なタイプの関数です。

「揮発性」というのは、ファイル内のどこかのセルが少しでも変更されると、OFFSET を含むすべての数式が再計算される という性質。

通常の関数（SUMやVLOOKUP）は、参照しているセルが変わったときだけ 再計算されます。一方、OFFSET（TODAY、NOW、RAND、および後に学ぶ INDIRECT など）は、自分が直接関係していないセルの編集でも、毎回計算し直されます。

少数なら問題ありませんが、

大量のセルで OFFSET を使う
巨大な表で何度も OFFSET を呼び出す

このようなケースでは、Excel が重くなる原因 になります。

一番わかりやすい例でいうと、NOW関数です。入力をするごとに、関数が再読み込みされて動的に動きます。

ですから、OFFSET は 「動的な仕組みが必要な場所だけ」に絞って使う、というのが上級者の流儀です。「テーブル化で済むなら、まずはテーブルで」── これが第一原則です。

とはいえ、どんなシーンで使えるのか、なかなかイメージができない人のほうが多いでしょう。

事例を次のレッスンで取り上げますので、ひとまずこちらでは OFFSET関数なんて不思議な関数があるんだー とだけ理解していただければ十分です。

まとめ

OFFSETは 基準セルから○行○列ずらした位置・範囲を返す 関数
構文：=OFFSET(基準, 行数, 列数, 高さ, 幅)
行数・列数は マイナス も可（上方向・左方向）
高さ・幅を指定すれば範囲を返せる
SUM・COUNTA など 範囲を取る関数と組み合わせる のが基本
INDEX が「点を指す」、OFFSET が「範囲を作る」
OFFSET は 揮発性関数。使いすぎるとファイルが重くなる

次回は、OFFSET の本領発揮編。動的な集計範囲・連動ドロップダウン・伸びるグラフ など、「これは凄い」と思わず唸る実例を紹介します。

ショートカット

※このレッスンに対応するショートカットはありません。