OFFSET関数のExcel【解説用の図あり】－使える関数と組み合わせて構文指定方法をわかりやすく！

「OFFSET関数ってどんな関数？何ができるの？」
「SUMやVLOOKUPは行追加に対応できないのがネックなんだよね…」エクセルでミスを起こす原因の一つに、「データが追加されたのに数式の範囲を変更していない」ことが挙げられます。
しかし、数式の範囲をその都度確認するってとても面倒ですよね。

そんな時に便利なのがOFFSET関数です。
組み合わせて使えば数式の範囲を自動で調整してくれます。

ここではOFFSET関数の基本的な使い方、他の関数との便利な組み合わせについて詳しく解説します。
これできっとミスを減らすことができますよ！

1.OFFSET関数の使い方

まずはoffset関数の概要と基本的な使い方を見てみましょう。

1-1.OFFSET関数とは？

offset関数とは、「指定した位置から指定した行数・列数移動した先のセルの値、または範囲を返す」関数です。

では、例を見てみましょう。

上の例は基準となる位置を「セルA1」とし、そこから移動する値を「５行目かつ２列目」と指定しています。
その結果、セルA1から５つ下で２つ右の「富山」が返ってきました。

また、別の使い方もあります。

上の例は「OFFSET関数で移動した位置を基準に、指定した範囲の値をSUM関数で合計した」ケースです。

こちらの例も基準となるセルはA1で、移動する行数は「５」ですが、移動する列数を「４」とし、高さという引数を「３」、幅という引数を「１」として、その範囲をSUM関数で合計してみました。

その結果、赤い四角の枠で囲まれた範囲の合計が計算され、「5,450」が返ってきています。

このように、offset関数は「移動先での範囲」も指定できるのです。

なお、offset関数の構文は次の通りです。

1-2.別シートからデータを抜き出したい時は？

次に、OFFSET関数を使って別シートからデータを抜き出す方法についてです。

例えば、Sheet1に下のような表があった場合、セルC3の「滋賀」の値をSheet2に抜き出してみましょう。

（１）数式バーに「=OFFSET(」と入力しfxを押す

（２）引数「参照」の所でSheet1をクリックした後、任意の基準セルを指定する

（３）引数「行数」、「列数」を指定する

（４）完了

このようにすれば、別シートから値を取ってくることができます。

2.offset関数応用編！他の関数と組み合わせて便利に使う！

次にoffset関数の応用編として、他の関数との組み合わせを３つご紹介します。

2-1.sum関数との組み合わせ

まずはSUM関数との組み合わせです。

こちらは先ほどもご紹介しましたが、基準から移動した先で範囲を指定し合計を計算するという動きをします。

繰り返しになりますが、この例は基準セルA1から下に「５」、右に「４」移動した先をさらに基準にして、高さを「３」、幅「１」の範囲をSUM関数で合計しています。

数式の形は「=SUM(OFFSET(参照,行数,列数,高さ,幅))」です。
注意点としては、「行数、列数として０（移動しない）は指定できるが、高さと幅で「０」は指定できない」ことです。

「１行だけ」、「１列だけ」と指定したい場合には「１」と入力しましょう。

2-2.match関数との組み合わせ

次にMATCH関数との組み合わせ方です。

OFFSET関数とMATCH関数を組み合わせると、「値を抜き出したい行と列の条件を可変的にすること」ができます。

上の例はセルG3でA列の№を指定し、セルH2で表示させたい内容を指定しています。
ここでは「№1の氏名」を指定していますので、結果「小田　勝」がセルH3に表示されました。

そして、これを「№5の受講料」に変えると値は「1,500」に変わります。

セルH3に入力している数式は「=OFFSET(A1,MATCH(G3,A2:A11,0),MATCH(H2,A1:E1,0)-1)」です。

数式のポイントは次の３つです。

そして、行数のMATCH関数の検査値をセルG3に入力した値とし、列数のMATCH関数の検査値をセルH2とすれば、セルG3とH2の値を変えることで、抽出する値を変えられます。

2-3.vlookup関数との組み合わせ

続いては、OFFSET関数とVLOOKUP関数の組み合わせについてです。

OFFSET関数とVLOOKUP関数を組み合わせて使うと、「行数の追加に対応できる形での条件に合うデータの抽出」ができます。

VLOOKUP関数は「条件に合うデータを抜き出すことができる」関数なので、VLOOKUP関数単体でも、引数「範囲」で表がある列全て（例でいうと『A:E』）という指定をすれば、データの追加には対応が可能です。

しかし、エクセルデータが重くなるという問題があります。
これを解消するのがOFFSET関数とVLOOKUP関数の組み合わせで、例えば下の例だと数式は「=VLOOKUP(G2,OFFSET(A2,0,0,COUNTA(A:A)-1,5),2,FALSE)」と書きます。

数式のポイントはOFFSET関数内の引数「高さ」で、文字列が入ったセルをカウントする関数「COUNTA」の結果からタイトル行分１行を引くことで、表のデータの数に対応した範囲がOFFSET関数で指定できるのです。

とても便利なので使ってみてください。

3.まとめ

OFFSET関数について、基本的な使い方と応用編として他の関数との組み合わせ方を解説しました。

OFFSET関数は応用テクニックで使われる関数です。
そのため、分かりづらい点はありますが、活用できるとやれることの幅がぐっと広がります。

これを機会に使い方をしっかりとマスターしましょう！