OFFSET関数とは

OFFSET関数とは ※画像:PIXTA
「基準にしたセルから、指定した行数と列数分だけ縦と横にずらした場所にあるセルを参照する」
実際の例で見てみましょう。
これはあるパソコン教室の、受講人数と受講講座数によって変わる、一人あたりの受講金額の一覧表です。この表をもとに、B10セルに受講人数、C11セルに講座数を入力すると、D10セルに一人当たりの金額が出るようにしたいというような時、OFFSET関数を使います。

金額一覧表の例 ※画像:筆者撮影
D10セルに次の式を入力します。
=OFFSET(A2,B10,C10)

D10セルにOFFSET関数を入力 ※画像:筆者撮影
「A2セルから、B10セルの値の分だけ下、C10セルの値の分だけ右にずらした先のセル(D4セル)の値を出す」という式になっているのです。
OFFSET関数は次のような書式になっています。
=OFFSET(基準セル,縦にずらす数,横にずらす数)
第一引数に基準となる任意のセルを指定して、そこから第二引数の分だけ下(負の数の場合は上に)、第三引数の分だけ右(負の数の場合は左に)ずらした先のセルを参照する、というのがOFFSET関数の基本になります。基準セルにはもちろんOFFSET関数を入力するシートとは別のシートのセルも指定できます。
INDEX関数との違い
似たような処理ができる関数にINDEX関数があり、=INDEX(B3:D7,B10,C10)という式でも同じ結果が得られます。
INDEX関数を使った例 ※画像:筆者撮影
これも使い分けは好みの問題というレベルですが、INDEX関数では最初に表の範囲を指定してしまうので、表の行数や列数が今後増える可能性がある場合はOFFSET関数のほうが柔軟に対応できるのでおすすめです。
OFFSET関数とINDEX関数との違い
OFFSET関数にはもう一つ、範囲を指定する使い方があります。このような日付と売上のデータにおいて、例えば「1日から5日までの売上合計を出したい」と言ったケースの場合。
日別の売上データ ※画像:筆者撮影
まず、OFFSET関数で範囲を指定する場合は引数が二つ増えます。例えば先ほどのシートで、B2セルからB6セルの5つのセル範囲を合計する式は次のようなものになります。 =SUM(OFFSET(B1,1,0,5,1))

E5セルに合計式を入力 ※画像:筆者撮影
範囲を指定するOFFSET関数部分の引数は次のようになります。
OFFSET(基準セル,縦にずらす数、横にずらす数,範囲の行数,範囲の列数)
OFFSET(B1,1,0,5,1)の場合、次のような範囲を意味しています。B1セルから、下に1、右に0ずらした先のセル……つまりB2セルを起点として、そこから5行分、1列分の範囲。つまり、セル範囲[B2:B6]を意味しています。
さらに、「じゃあ2日から9日までは?」といったケースに備え範囲の始点と終点を柔軟に変えるために、D2セルに合計の開始日、D3セルに合計の終了日を入力することにします。そしてこのD2セルとD3セルを使って次のような式を作ることで、合計対象範囲の柔軟な変更が可能なシートができます。
=SUM(OFFSET(B1,D2,0,D3-D2+1,1))

E5セルに「=SUM(OFFSET(B1,D2,0,D3-D2+1,1))」と入力 ※画像:筆者撮影
- 第一引数に指定した基準セルから、第二引数にした数だけ上下に、第三引数に指定した数だけ左右にずらした先のセルを参照する機能
- さらに第四引数で行数、第五引数で列数を指定すると、そのずらした先のセルを起点とした範囲を指定できる機能
特に2番の機能においては、何らかの対象範囲の行数や列数が増える、つまりデータの範囲が広がったりするケースにおいて可変対応できる仕組みを作るのに便利な関数になります。
例えば入力規則のリスト入力における「元の値」範囲の増減への自動対応や、グラフの参照範囲のデータ増減に対応できる仕組みが作れるようになります。こちらは別途解説を加えて参ります。
OFFSET関数でVLOOKUP関数の弱点を補う
VLOOKUP関数では、検索範囲において検索値を探す列より左側のセルを参照することはできないという弱点があります。この弱点を補ってくれるのがOFFSET関数とMATCH関数の組合せです。言いかえればこの二つの関数の合わせ技で、VLOOKUP関数と同じ機能を果たせるということになります。解説用に次のようなシンプルな例を用意しました。
OFFSET関数とMATCH関数の合わせ技 ※画像:筆者撮影
このような時、次の式で解決できます。
=OFFSET(B1,MATCH(D2,B2:B6,0),-1)
まず第二引数のMATCH関数ですが、これは第一引数で指定した値が、第二引数で指定した範囲の何個目のセルにあるかを出す関数です。この例の場合、D2セルの値、つまり「1」が範囲[B2:B6]の中で何個目のセルに出てくるかを出します。この場合その答えは「1」ですね。範囲[B2:B6]においてD2セルの値(1)は一つ目に出てきます。
このMATCH関数をOFFSET関数の第二引数に指定し、第三引数でそこからいくつ右にずらすかを指定すればVLOOKUP関数と同じことができます。さらに、OFFSET関数の第三引数をマイナスの数字にすれば、VLOOKUP関数では不可能な、検索値よりも左側のセルを参照することもできるようになる、ということなのです。