Excel上級者が教えるOFFSET関数の基本と他の関数と組み合わせた便利な使い方

スポンサードリンク
excel offset エクセル
「offset関数ってどんな関数?」
「sumやvlookupは行追加に対応できないのがネックなんだよね…」

エクセルでミスを起こす原因の一つに、「データが追加されたのに数式の範囲を変更していない」ことが挙げられます。
でも、数式の範囲をその都度確認するってとても面倒ですよね。

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

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

1.offset関数の使い方

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

1-1.offset関数とは?

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

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

excel offset

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

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

excel offset

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

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

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

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

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

OFFSET(参照,行数,列数,高さ,幅)
参照 基準となる参照を指定
列数 基準から上または下へシフトする距離を指定
行数 基準から左または右へシフトする距離を指定
髙さ 設定したい高さを行数で指定
設定したい幅を列数で指定

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

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

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

excel offset

(1)数式バーに「=OFFSET(」と入力しfxを押す
excel offset

(2)引数「参照」の所でSheet1をクリックした後、任意の基準セルを指定する
excel offset

(3)引数「行数」、「列数」を指定する
excel offset

(4)完了
excel offset

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

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

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

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

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

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

excel offset

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

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

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

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

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

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

excel offset

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

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

excel offset

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

excel offset

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

  • OFFSET関数の行数、列数にそれぞれMATCH関数を使う
  • 行数に指定したMATCH関数はA列の番号を検索させる
  • 列数に指定したMATCH関数はタイトル行(1行目)の値を検索させる

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

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

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

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

excel offset

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

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

excel offset

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

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

3.まとめ

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

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

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

コメント