【OFFSET関数】本当の理解を目指して
えくせるサイトウ
本記事は、OFFSET関数について解説します。
本記事を読むことで、OFFSET関数の使い方を理解し、実務的に役立てていただければ幸いです。
関数の紹介
機能
OFFSET関数は、指定したセルから特定の行数・列数分、移動した先のセルまたは範囲を返す関数です。
ザックリ言うと、可変できる関数といことです。
名前の由来
SET（固定）をOFFにする ⇒ 可変
と推察しています。
主に、役立つシーン
主に、次のようなシーンで役立ちます。
- 【条件付き書式】セル範囲を可変しながら、条件付き書式を適用できる
- 【参照範囲】セル範囲を可変しながら、セル値の取得やグラフを作成することができる
- 【計算】セル範囲を可変しながら、合計値や平均値を計算できる
型
OFFSET(基準, 行数, 列数, [高さ], [幅])
OFFSET 関数 – Microsoft サポート
基準セルに対して、行数と列数を指定して、セルを可変に動かします。
基準となるセルを分かりやすく説明すると、スタート地点となるセルです。
[]は省略可能です。
Microsoft社の公式リファレンスは[高さ][幅]のみ省略可能としていますが、行数と列数も省略できます。こちらは後述します。
- 参照：セル範囲の起点となるセルとなる参照セル。本記事では、スタート地点と呼びます
- どの地点から動かせばいいかをExcel関数に教えます
- 行数：スタート地点からの行方向への移動数。正の値は下に移動し、負の値は上に移動します
- スタートセルから、どのぐらい動かすかをExcel関数に教えます
- 列数：スタート地点からの列方向への移動数。正の値は右に移動し、負の値は左に移動します
- スタートセルから、どのぐらい動かすかをExcel関数に教えます
- 高さ：返されるセル範囲の行数。省略時は1
- 複数行を取得できます。1であれば、1行分取得するという意味です。
- 幅：返されるセル範囲の列数。省略時は1
- 複数列を取得できます。1であれば、1列分取得するという意味です。
例題
以下は、OFFSET関数を使って、「➋ 【参照範囲】セル範囲を可変しながら、セル値の取得やグラフを作成することができる」の例です。
まずは基本です。
なお、➋のグラフにOFFSET関数をグラフに活用した例は、本記事では取り上げません。
今後改めて、紹介します。
引数「行数」「列数」の指定パターン一覧
下図の見方
「番号付き矢印」と「矢印番号」が対応し、「番号付き矢印」の方向へ移動させるための数式が「矢印番号」の行に記述されています。
B3セルをスタート地点として、「行数」「列数」に動かしています。
「行数」「列数」をどのように設定すると、どうセルが動いているかを確認してください。
- 「行数」であれば、正の数を指定すると下方向へ、負の数を指定すると上方向へシフト
- 「列数」であれば、正の数を指定すると右方向へ、負の数を指定すると左方向へシフト
では、それぞれのOFFSET関数に関して、解説します。
正であれば「進む」、負であれば「戻る」と覚えましょう。
| 関数式 | 説明 | 結果 |
|---|---|---|
| =OFFSET($B$3,,,,) | B3のセルをスタートとした位置から移動しません | B3 |
| =OFFSET($B$3,1,,,) | B3のセルをスタートとして1行下に移動します | C3 |
| =OFFSET($B$3,-1,,,) | B3のセルをスタートとして1行上に移動します | A3 |
| =OFFSET($B$3,,1,,) | B3のセルをスタ―トとして1列右に移動します | C3 |
| =OFFSET($B$3,,-1,,) | B3のセルをスタートとして1列左に移動します | A3 |
| =OFFSET($B$3,1,1,,) | B3のセルをスタートとして1行下、1列右に移動します | C4 |
| =OFFSET($B$3,-1,-1,,) | B3のセルをスタートとして1行上、1列左に移動します | A2 |
上記関数式から分かるのは、公式リファレンスと異なり、「行数」「列数」ともに省略可能ということです。
つまり、第1引数「基準」以外は省略可能となっているわけです、
引数「高さ」「幅」
引数「高さ」
次の例では、「$B$3」から始まる4行分の値を返しています。
この範囲は、スタートとなるセル（ここでは「$B$3」）を含めた4つのセルから値を取得します。
引数「幅」
次の例では、「$B$3」から始まる2列分の値を返しています。
この範囲は、スタートとなるセル（ここでは「$B$3」）を含めた2つのセルから値を取得します。
引数「高さ」「幅」の覚え方
=OFFSET(基準, 行数, 列数, [高さ], [幅]) 「高さ」が行数、「幅」が列数であることが分かります。
[高さ], [幅])の引数も、「行数」「列数」の引数と同様に、行, 列の順になっていることが分かります。
ゆえに、[高さ], [幅])の引数の覚え方は、第２引数と第3引数の「行数」「列数」と同様の順で、
「行」「列」の順と覚えてください。
最後に
OFFSET関数は、シートのセルや範囲を移動して参照することができる便利な関数です。
スタート地点のセルから、どれだけ(行数/列数)を動かし、どの程度(高さ/幅)の値を取得するか
「高さ」が行数、「幅」が列数を、表現したものでした。
次の３点に関しては、「主に、役立つシーン」は、今後改めて紹介できればと思います。
- 【条件付き書式】セル範囲を可変しながら、条件付き書式を適用できる
- 【参照範囲】セル範囲を可変しながら、セル値の取得やグラフを作成することができる
- 【計算】セル範囲を可変しながら、合計値や平均値を計算できる
