【エクセル】OFFSET関数の使い方（基準点から指定された分離れた範囲の参照を返す）

2022 10/22

2022-10-22

基準点を指定し、そこから指定した分だけ離れた範囲のセル参照を返す関数です。使い所が特殊ですが、構文説明から使用例まで解説していきます。

OFFSET関数とは

ExcelのOFFSET関数は、(1) 基準点、(2) 行数、(3) 列数、(4) 高さ、(5) 幅　の5つから構成される範囲への参照を返します。

OFFSET関数の構文

＝OFFSET（基準点,行数,列数,[高さ],[幅]）

基準点：基準となるセル参照を指定します。セルもしくはセル範囲を参照します。
行数：基準点からいくつ行がずれるかを指定します。負の数を指定すると基準点から上にずれます。
列数：基準点からいくつ列がずれるかを指定します。負の数を指定すると基準点から左にずれます。
高さ：範囲の高さを指定します。この値は省略可能で省略すると基準の参照と同じ高さになります。
幅：範囲の幅を指定します。この値は省略可能で省略すると基準の参照と同じ幅になります。

OFFSET関数の使用例

さまざまな種類の範囲を返すOFFSET関数の例をまとめてみました。下記はExcel365で取得されているため、結果が複数のセルの場合、OFFSET関数は複数セルに渡って結果を返します。（スピル）

使用例１

OFFSET関数を使用して、3行目 (３月)の2列目 (関西)の値を返します。

= OFFSET ( B3 , 3 , 2 )  // D6 を返す

使用例２

OFFSET関数を使用して、6行目 (6月)の3列目 (東北) の値を返します。

= OFFSET ( B3 , 6 , 3 )  // E9 を返す

使用例３

OFFSET関数を使用して、3行目の列 (東北) のすべての値を返します。

= OFFSET ( B3 , 1 , 3 , 6 )  // E4:E9 を返します

使用例４

OFFSET関数を使用して 5月のすべての値を返します (5行目)。

= OFFSET ( B3 , 5 , 1 , 1 , 4 )  // C8:F8 を返します

使用例５

OFFSET関数を使用して関西地方の 4月、5月、6月の値を返します。

= OFFSET ( B3 , 4 , 2 , 3 , 1 )  // D7:D9 を返します

使用例６

OFFSET関数を使用して、関西と東北の 4月、5月、6月の値を返します。式は次のとおりです。

= OFFSET ( B3 , 4 , 2 , 3 , 2 )  // D7:E9 を返します

使用上の注意

基準点は、１つのセルまたはセルの範囲にすることができます。行数と列数の引数は、出発点からのどれだけ離れたところかを指定します。高さと幅の引数は任意であり、作成された範囲の大きさを指定できます。高さと幅が省略されている場合は、基準点の高さと幅が適応されます。基準点が範囲を参照していなくて、セル単体を参照していれば、高さは１セル分、幅も１セル分となります。

A1から始まるC5を参照するためには、基準点はA1であり、行数は4で列数は2

= OFFSET ( A1 , 4 , 2 )  // C5 への参照を返す

A1からC1:C5を参照するには、基準点は A1、行数は 0、列数は 2、高さは 5、幅は 1

= OFFSET ( A1 , 0 , 2 , 5 , 1 )  // C1:C5 への参照を返します
注: 幅はデフォルトで 1 になるため、省略できます。

範囲を指定する関数にOFFSET関数が入れ子にされているのはよくあることです。例えば、SUM関数と組みあわせてA1を基準としてC1:C5を返すには下記を指定します。

= SUM (OFFSET( A1 , 0 , 2 , 5 , 1 ))  // SUM C1:C5

OFFSET関数の主な目的は、数式が利用可能なデータまたはユーザー入力に動的に調整できるようにすることです。OFFSET関数を使用して、チャートまたはピボットテーブルの動的な名前付き範囲を作成し、参照データを動的にすることが可能です。

注: Excel のドキュメントには、高さと幅に負の値を指定することはできないと記載されていますが、1990 年代初頭から負の値でも問題なく機能しているようです。ちなみにGoogleスプレッドシートの OFFSET関数は、高さまたは幅の引数に負の値を許可しません。

ショートカットキーの練習できます

当サイトはショートカットキーの練習を実際の動きを確認しながら練習できる機能がついています。繰り返し指を動かして練習ができるので、ゲーム感覚で遊んでいくうちに自然とショートカットキーが使えるようになれます。ショートカットキーは暗記するよりも実際に手を動かして練習していったほうが習得が早くなるので、是非当サイトをブックマークに登録し定期的に練習してみてください。下記のSTARTキーを押下すると練習を始められます。

下のスタートボタンを押して練習モードに入ります。

STARTSTOP

※入力モード切り替え ctrl + shift + alt + space

PRE

現在の問題をリストから除外

除外したリストをリセット

現在の除外状況のURL取得

コピー

ガイドモード

リピートモード

※推奨ブラウザ Google Chrome

もっとショートカットキーの練習をする