はじめに

ExcelのOFFSET関数とは？

ExcelのOFFSET関数とは、起点となるセルから行数と列数を指定して特定の値や範囲を参照する数式（関数）のことです。また、離れた場所にあるセルの値や範囲も抜き出せます。

セルの位置によっては進む方向が逆になる（マイナスの値を入力する）こともありますので、最初は混乱するかもしれませんが、慣れると大変便利です。OFFSET関数は、単独で使うよりも他の関数と組み合わせて使うケースが多いため、この記事でも他の関数との組み合わせ方をご紹介しています。

OFFSET関数の数式

OFFSET関数の構文は以下の通りです。また、それぞれの単語の意味も解説します。

• OFFSET関数の構文

  ＝OFFSET(基準, 行数, 列数, [高さ], [幅])

単語の意味

• OFFSET：指定した位置から次の範囲を表してください
• 基準：起点となるセルの範囲
• 行数：起点となるセルから何行（縦に）進むか（戻るか）
• 列数：起点となるセルから何列（横に）進むか（戻るか）
• 高さ：最終的に移動したセルから範囲としたい行（縦）方向のセル数を指定（省略可）
• 幅：最終的に移動したセルから範囲としたい列（横）方向のセル数を指定（省略可）

上記の単語は「引数（ひきすう）」といいます。引数とは、プログラムや関数を組む際にコンピュータプログラム側へ渡す値のことです。

OFFSET関数の使い方

以下から基本的なOFFSET関数の使い方をご紹介します。

OFFSET関数の挿入方法

OFFSET関数は数式バーへ直接入力することもできますが、初心者の方には「関数の挿入ボタン（fxボタン）」をクリックする方がわかりやすいかもしれません。

使い方は、数式バーに「＝OFFSET(」と入力して、左横の「fxボタン」を押したあと、各引数を入力→「OK」を押すと反映されます。

OFFSET関数の例題

（1）～（9）までの番号が振られている図を用いて例題を2つご紹介します。
1.赤のセル（1）（B3）を起点として白のセルの値を求める場合

赤のセル（1）から見て白のセルは「1行進んだのち2列進む」ことになるので「=OFFSET(B3,1,2)」となり、白のセルの番号（6）が表示されます。

2.茶のセル（9）（D5）を起点として黒のセルの値を求める場合

茶のセル（9）から見て黒のセルは「2行戻って1列戻る」ことになりますので、「=OFFSET(D5,-2,-1)」となり、黒のセル番号の（2）が表示されます。

上記、OFFSET関数の例題からわかるように、下（行）と右（列）方向に進むときは入力する値が「正の数」になり、上（行）と左（列）方向に進むときは「負の数」となります。

OFFSET関数と似ている関数

OFFSET関数と似ている関数に「INDEX関数」や「INDIRECT関数」があります。
以下から違いを解説します。

INDEX関数との違い

OFFSET関数と似ている関数の1つに「INDEX関数」があります。INDEX関数とは、「指定した行と列が交差する位置にあるセルの値を反映する関数」のことで、構文は「＝INDEX（配列or参照,行番号,[列番号],[領域番号]）」です。
INDEX関数とOFFSET関数では、起点となるセルからの行と列の数え方が異なります。

また、どちらも行数と列数を指定して特定のセルの値を反映する数式ですが、OFFSET関数とINDEX関数では引数の数が違います。INDEX関数の引数が最大4つなのに対して、OFFSET関数の引数は最大5つです。

さらに、INDEX関数単体では絶対参照の値がズレるケースもあるため、OFFSET関数と組み合わせて使うことを推奨します。
※絶対参照とは、参照元のセルを固定しておくとコピー先のセルの値も変動しない手法のこと

INDIRECT関数との違い

INDIRECT関数もOFFSET関数と同じくセルの範囲を指定する関数です。OFFSET関数が数値で指定するのに対して、INDIRECT関数は「A1」「B2：F5」などセル範囲で指定できます。
INDIRECT関数の構文は「=INDIRECT（参照文字列, 参照形式）」です。引数は参照文字列と参照形式の2つですので、OFFSET関数よりも複雑ではありません。

INDIRECT関数は以下のような使い方ができます。
下記画像ではE4列に参照する色のセル番地（ここでは「A8」）を入力しました。E4列への入力を変えると色の名前も変更されます。

OFFSET関数の活用方法

OFFSET関数は、単独で使うよりも他の関数と組み合わせる方が活用の幅も広がります。以下から、具体例をご紹介します。

SUM関数と組み合わせる

SUM関数とは「選択範囲の合計（足し算）を行う関数」です。以下の図のように、表内の緑のセル範囲を起点として青のセル範囲の和を求める場合は、OFFSET関数とSUM関数を併用するといいでしょう。

上記画像のOFFSET以降の数式を解説します。
まず、緑のセル「B3:D3」から2行1列進みます。青のセル部分は2行3列ですので式にその旨を指定すると、青のセル内の合計が「9900」と算出されました。

INDEX関数と組み合わせる

先述した通り、INDEX関数単体では絶対参照で指定した値がズレることもあります。
以下から具体例を解説します。

1.D店の売上をINDEX関数で絶対参照している以下の表があったとする

2. 誤ってB店の列を削除してしまった場合、先ほど絶対参照で指定した値がズレてしまった

上記のような事態は、INDEX関数とOFFSET関数を組み合わせることで対処できます。
以下の画像のように、INDEX関数とOFFSET関数で指定したあとB店の列を削除すると、値の変動はありません。

INDEX関数・COUNTA関数と組み合わせる

COUNTA関数とは、「選択範囲の空白ではないセルの個数を求める関数」のことです。数字に限らず文字や記号などが入力されていてもカウントされます。
たとえば、以下の図のように表から果物名だけ抜き出したいときは、INDEX関数・COUNTA関数を組み合わせると便利です。

上記画像のOFFSET以降の数式を解説します。
まず、「名前」と書かれている「A3」のセルを選択して、1行0列移動（列は移動しない）します。次に、COUNTA関数を挿入してA列を指定（A:A）したあと、「-1」とします（果物名には「名前」のセルを含めないため）。最後のA列の幅は1列分のため「1」です。最後に任意のセルを選択します。

先ほど、A列全体を参照するように指定したため、A列の値や数値が増えると自動で反映されます。

MATCH関数と組み合わせる

OFFSET関数とMATCH関数を組み合わせて、VLOOKUP関数の弱点を補えます。VLOOKUP関数とは「表を縦方向に検索して指定したセルから値を取り出す関数」のことです。
以下の図のように「区間」から「運賃」を求めるときはVLOOKUP関数が有効です。

ただし、VLOOKUP関数には弱点があります。
VLOOKUP関数では、「選択範囲（図の紫枠）の左端」より左側を検索しようとすると、以下のようにエラーが出てしまいます（「区間」から「距離」を求める場合）。

OFFSET関数とMATCH関数を組み合わせることで、「選択範囲（図の紫枠）の左端」より左側も検索できるようになります。MATCH関数とは「選択範囲のなかで指定したセルが何番目かを求める関数」のことです。
以下画像内のMATCH関数以降の数式をご説明します（※下段の表参照）。

まず、指定する区間（ここではD22）を選択します。表の区間は「B15:B19」です。次に照合の種類の番号（ここでは「FALES：完全一致」の「0」）を選択します。最後の「-1」は参照する列（「区間」から1列戻った「距離」の列）のことです。

入力規則のリスト設定でINDEX関数・COUNTA関数と組み合わせる

入力規則のリスト設定からOFFSET関数とINDEX関数・COUNTA関数を組み合わせて、プルダウンメニューの作成ができます。「データの入力規則」は「データ」タブから開くことが可能です。
以下図のように、「E1」セルにプルダウンメニューを作成する場合、開いた「データの入力規則」タブの値に直接数式を入力します。

今回は、果物列のプルダウンメニューを作成するため、COUNTA以降の数式はA列を参照（A:A）します。次の「-1」は「名前」のセル（A1）を含めないようにするための値です。次に幅が1列分なので「1」と入力します。最後に、反映するセル（ここでは「E1」）を指定して終了です。

OFFSET関数を用いてプルダウンメニューを作成するメリットは、以下図のようにあとから項目が追加されても自動で反映されることです。

OFFSET関数の起点を変動させる

OFFSET関数では参照範囲だけでなく、起点を変動させることも可能です。以下から具体例を用いてご説明します。

起点を変動させる方法

OFFSET関数は使い方次第で、起点を可変にする（変動させる）こともできます。たとえば、以下画像のような一か月のスケジュールがあったときに、OFFSET関数とMATCH関数・TODAY関数を組み合わせることで、本日から一週間の予定だけを抜き出すことも可能です。

MATCH関数以降の数式はまず、TODAY関数を入力して、その後MATCH関数の引数を入力します。ここでの、「1:1」は日付の行列を指します（1行1列のため）。次の照合の種類は「FALES」を表す「0」です。その次に、「日付」のセルを含めないよう「-1」します。最後の「3,7」は、日付・曜日・予定の「3列」と指定日数である「7日」です。

まとめ

OFFSET関数は引数が多いため、慣れるまでは難しく感じるかもしれませんが、一度覚えてしまうと大変便利です。また、他の関数と組み合わせてデータを自動追加・削除できたり、起点を変動できたりもするので、単独で活用するよりも汎用性が高くなります。

業務でも幅広く活用できますので、ぜひこの機会にOFFSET関数を学んでみてはいかがでしょうか。