OFFSET関数とは
OFFSET関数とは
「基準にしたセルから、指定した行数と列数分だけ縦と横にずらした場所にあるセルを参照する」
実際の例で見てみましょう。
これはあるパソコン教室の、受講人数と受講講座数によって変わる、一人あたりの受講金額の一覧表です。
この表をもとに、
B10セルに受講人数、C11セルに講座数を入力すると、D10セルに一人当たりの金額が出るようにしたい
というような時、OFFSET関数を使います。
金額一覧表の例
例えば、「2名で、3つの講座を受講」という場合の一人当たりの受講料はこの表によるとD4セルの「43,000」になるわけですね。今回の場合、このD4セルの値をD10セルの「金額」欄に出したいわけです。
D10セルに次の式を入力します。
=OFFSET(A2,B10,C10)
D10セルにOFFSET関数を入力
次にB10セルに「2」、C10セルに「3」と入力すると、D10セルには「43,000」という答えがでます。ここで、D10セルの
=OFFSET(A2,B10,C10)
という関数式を日本語に訳してみましょう。
「A2セルから、B10セルの値の分だけ下、C10セルの値の分だけ右にずらした先のセル（D4セル）の値を出す」
という式になっているのです。
OFFSET関数は次のような書式になっています。
=OFFSET(基準セル,縦にずらす数,横にずらす数)
第一引数に基準となる任意のセルを指定して、そこから第二引数の分だけ下（負の数の場合は上に）、第三引数の分だけ右（負の数の場合は左に）ずらした先のセルを参照する、というのがOFFSET関数の基本になります。
基準セルにはもちろんOFFSET関数を入力するシートとは別のシートのセルも指定できます。
INDEX関数との違い
似たような処理ができる関数にINDEX関数があります。=INDEX(B3:D7,B10,C10）
という式でも同じ結果が得られます。
INDEX関数を使った例
この関数式を日本語に訳してみます。
第二引数＝B10セルには「2」、第三引数＝C10セルには「3」が入力されていますね。
範囲[B3:D7]において、上から2行目、左から3列目のセル（D4セル）の値を参照する
という式になっています。
これも使い分けは好みの問題というレベルですが、INDEX関数では最初に表の範囲を指定してしまうので、表の行数や列数が今後増える可能性がある場合はOFFSET関数のほうが柔軟に対応できるのでおすすめです。
OFFSET関数とINDEX関数との違い
OFFSET関数にはもう一つ、範囲を指定する使い方があります。このような日付と売上のデータにおいて、例えば「1日から5日までの売上合計を出したい」と言ったケースの場合。
日別の売上データ
合計はSUM関数で範囲を指定すれば簡単に出せますが、「じゃあ2日から6日までの合計は？」と言ったような、合計範囲を頻繁に変える必要がある時はどうすればよいでしょうか。このような時にOFFSET関数での範囲指定方法を知っていると便利です。
まず、OFFSET関数で範囲を指定する場合は引数が二つ増えます。例えば先ほどのシートで、B2セルからB6セルの5つのセル範囲を合計する式は次のようなものになります。
=SUM(OFFSET(B1,1,0,5,1))
E5セルに合計式を入力
SUM関数の括弧の中にあるOFFSET関数、これがB2:B6という範囲を指定しています。OFFSET関数の確固の中にある5つの引数それぞれの意味をひもときながら解読していきましょう。
範囲を指定する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))」と入力
B1セルから、D2セルの値の分だけ下、ゼロ個右にずらした先のセルを起点として、D3-D2+1……図の場合、5-1+1=5となり、5行分、そして1列分の範囲……つまり[B2:B6]をSUM関数で合計している式になります。
まとめると、OFFSET関数には二つの機能があるということです。
- 第一引数に指定した基準セルから、第二引数にした数だけ上下に、第三引数に指定した数だけ左右にずらした先のセルを参照する機能
- さらに第四引数で行数、第五引数で列数を指定すると、そのずらした先のセルを起点とした範囲を指定できる機能
特に2番の機能においては、何らかの対象範囲の行数や列数が増える、つまりデータの範囲が広がったりするケースにおいて可変対応できる仕組みを作るのに便利な関数になります。
例えば入力規則のリスト入力における「元の値」範囲の増減への自動対応や、グラフの参照範囲のデータ増減に対応できる仕組みが作れるようになります。
こちらは別途解説を加えて参ります。
OFFSET関数でVLOOKUP関数の弱点を補う
VLOOKUP関数では、検索範囲において検索値を探す列より左側のセルを参照することはできないという弱点があります。この弱点を補ってくれるのがOFFSET関数とMATCH関数の組合せです。言いかえればこの二つの関数の合わせ技で、VLOOKUP関数と同じ機能を果たせるということになります。
解説用に次のようなシンプルな例を用意しました。
OFFSET関数とMATCH関数の合わせ技
商品Noに応じた単価をE2セルに出したいのですが、ご覧の通りA列～B列のマスタ表は商品Noより左側に単価があるため、VLOOKUP関数では単価が出せません。
このような時、次の式で解決できます。
=OFFSET(B1,MATCH(D2,B2:B6,0),-1)
まず第二引数のMATCH関数ですが、これは第一引数で指定した値が、第二引数で指定した範囲の何個目のセルにあるかを出す関数です。
この例の場合、D2セルの値、つまり「1」が範囲[B2:B6]の中で何個目のセルに出てくるかを出します。この場合その答えは「1」ですね。範囲[B2:B6]においてD2セルの値（1)は一つ目に出てきます。
このMATCH関数をOFFSET関数の第二引数に指定し、第三引数でそこからいくつ右にずらすかを指定すればVLOOKUP関数と同じことができます。
さらに、OFFSET関数の第三引数をマイナスの数字にすれば、VLOOKUP関数では不可能な、検索値よりも左側のセルを参照することもできるようになる、ということなのです。
【関連記事】
