command lab
  • TOP
  • BLOG
  • Excel

  • 【エクセル】OFFSET関数の使い方について分かりやすく解説

【エクセル】OFFSET関数の使い方について分かりやすく解説

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

OFFSET関数とは

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

OFFSET関数の構文

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

使用上の注意

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

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関数は、高さまたは幅の引数に負の値を許可しません。

OFFSET関数の使用例

さまざまな種類の範囲を返すOFFSET関数の例をまとめてみました。下記はExcel365で取得されているため、結果が複数のセルの場合、OFFSET関数は動的配列を返します。

使用例1

以下の画面では、OFFSET関数を使用して、2列目 (関西) の3行目の値 (3月) を返します。式は次のとおりです。

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

使用例2

下の画面では、OFFSET関数を使用して、3行目の列 (東北) の最後の値 (6月) を返します。式は次のとおりです。

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

使用例3

以下では、OFFSET関数を使用して、3行目の列 (東北) のすべての値を返します。式は次のとおりです。

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

使用例4

以下では、OFFSET関数を使用して 5月のすべての値を返します (5行目)。式は次のとおりです。

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

使用例5

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

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

使用例6

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

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

おすすめの解説動画

OFFSET関数の解説動画として一番くわしい動画をご紹介します。非常に丁寧に解説しているのでおすすめです。

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

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

下のスタートボタンを押して練習モードに入ります。
STARTSTOP
※入力モード切り替え ctrl + shift + alt + space
PRE
NEXT
ガイドモード
リピートモード

※推奨ブラウザ Google Chrome

PUSH ENTER

関連記事