OFFSET関数は値や範囲を参照する関数であり、指定した位置にあるセルや範囲を抽出するのに便利です。都度セルの位置を目視する必要がなくなるため業務効率化と相性が良く、他の関数と併用すれば範囲内の合計や数値の自動抽出も可能です。
本記事では、OFFSET関数の使い方を解説します。基本的な使い方から応用まで幅広く扱っているので日常業務に役立てましょう。
OFFSET関数の基本
まずはOFFSET関数の基本的な概要を解説します。使い方や事例を見ながら活用法を理解していきましょう。
OFFSET関数とは
OFFSET関数は、値や範囲を参照する関数です。基準となるセルから、指定した行数と列数分だけ縦と横にずらした場所にあるセルを参照することができます。
同じように値や範囲を参照する関数としてINDIRECT関数が挙げられますが、INDIRECT関数は「引数に指定した文字列のセル参照を返す関数」なので注意しましょう。OFFSET関数ではセルの範囲や参照したいセルを列番号・行番号で指定し、INDIRECT関数では「A1」や「B2:C3」のような文字でセルの範囲や参照したいセルを指定するという点がで異なります。
なお、OFFSET関数は「＝OFFSET(基準, 行数, 列数, [高さ], [幅])」で表記します。
| 参照 | 基準となるセル範囲の参照を指定する部分であり、必ず指定する必要があります。 |
| 行数 | 基準のセルを縦にずらす数を指定する部分であり、必ず指定する必要があります。基準の左上隅のセルを上方向または下方向へシフトする距離を行数単位で指定します。 |
| 列数 | 基準のセルを横にずらす数を指定する部分であり、必ず指定する必要があります。結果の左上隅のセルを左方向または右方向へシフトする距離を列数単位で指定します。 |
| 高さ | 省略可能です。 オフセット参照の行数を指定します。 |
| 幅 | 省略可能です。 オフセット参照の列数を指定します。 |
基本構文を理解しておくと応用もしやすくなるので覚えておきましょう。
簡単な例で理解するOFFSET関数
【1】縦横にバラバラの数値が入力されている表に対し、「A2セルから数えて下方向に3つ、右方向に4つズレたセルの数値」を自動抽出したい場合、「=OFFSET(A2,3,4)」と入力します。
【2】EnterキーでOFFSET関数を反映させると、E5セルの数値である「9」が表示されます。
マイナス方向にOFFSET関数を動かしたい場合
【1】マイナス方向にOFFSET関数を動かしたい場合、「行数」や「列数」に「マイナス」をつけて表記します。例えば「D3セルから数えて上方向に1つ、左方向に2つズレたセルの数値」を自動抽出したい場合、「=OFFSET(D3,-1,-2)と入力します。
【2】EnterキーでOFFSET関数を反映させると、B2セルの数値である「3」が表示されます。
OFFSET関数の応用例
ここからは、OFFSET関数の応用例を紹介します。他の関数との組み合わせ方なども習得し、用途に合わせて使いこなしましょう。
MATCH関数との組み合わせ
【1】MATCH関数とは、指定した値と一致する最初のセルの位置を返す関数です。基本構文は「MATCH(検査値, 検査範囲, [照合の型])」で、検査範囲の中で照合する値を指定しながら使用します。
MATCH関数とOFFSET関数を併用することで、指定した値と一致するセルを基準に、指定した数分ずらした場所にある数値を自動で抽出します。ここでは出発地から到着地までの運賃を自動計算する関数として使用します。
【2】C10セルに記載のある「渋谷」から、C11セルに記載のある「武蔵小杉」までの運賃を自動計算する場合、「運賃」欄に「=OFFSET(B2,MATCH(C10,B3:B8,0),MATCH(C11,C2:H2,0))」と入力します。「B3からB8の範囲でC10セル（渋谷）に合致する部分と、C2からH2の範囲でC11（武蔵小杉）に合致する部分とが重なる数値を抽出する」という意味になります。
【3】Enterキーで関数を反映させると、渋谷から武蔵小杉までの運賃「277（円）」が表示されます。
【4】OFFSET関数だけで都度基準となるセルを指定しながら抽出する方法もありますが、MATCH関数を使うと今回のC10（渋谷）やC11（武蔵小杉）の内容を変更するだけで出力結果も自動で変わってくれます。
C10を「中目黒」に、C11を「自由が丘」に変えると、運賃も180円に変わります。
SUM関数との組み合わせ
【1】SUM関数とは、引数で指定したセルもしくはセルの範囲内にある数値の合計を計算する関数です。売上額の合計値、参加者の合計人数、倉庫内にある在庫商品の数など、数値を合算したいときに便利です。
MATCH関数とOFFSET関数を併用することで、指定したセルから範囲指定した分の数値を合計できます。ここでは契約件数の合計を求める関数として使用します。
【2】上位3位までの合計契約件数を求める場合、「=SUM(OFFSET(D3,0,0,F3,1))」と入力します。「D3を基準に、F3で指定している数分だけ下の範囲にある数値全てを合計する」という意味になります。行数・列数は省略できないため「0」で指定し、省略可能な高さ・幅の部分を指定しています。
【3】Enterキーで関数を反映させると、上位3位以内にある東京都・千葉県・神奈川県の契約件数が合計され、「49」と表示されます。
【4】SUM関数だけで都度合計したい範囲数を変えながら計算していく方法もありますが、OFFSET関数を使うと今回のF3に入力する数値（何位までの合計値を出したいか）を変更するだけで出力結果も自動で変わってくれます。
F3を「8」にすると、8位までの契約件数を合計した「95」が表示されるので便利です。
よくある質問
Excelで使うOFFSET関数に関して「よくある質問」を紹介します。
INDIRECT関数とOFFSET関数の違いは何ですか？
OFFSET関数は、値や範囲を参照する関数です。基準となるセルから、指定した行数と列数分だけ縦と横にずらした場所にあるセルを参照することができます。指定部分の参照ができる関数という意味ではどちらも同一ですが、OFFSET関数ではセルの範囲や参照したいセルを列番号・行番号で指定します。一方、INDIRECT関数では「A1」や「B2:C3」のような文字でセルの範囲や参照したいセルを指定します。
INDIRECT関数の場合、指定範囲の外にあるセルを参照しようとするとエラーになるため注意しましょう。
OFFSET関数の範囲をずらすには？
OFFSET関数を範囲をずらすときは、行数・列数・高さ・幅の引数を変更します。行数と列数は必須項目ですが、高さと幅だけを指定したいときはどちらも「0」で入力しましょう。高さと幅は省略できるため、範囲ではなく特定のセルだけをピンポイントで抽出したいときは行数・列数だけの記載で問題ありません。
行数と高さは下に、列数と幅は右に進んでいくのも特徴です。上や左に進みたいときは数値に「マイナス(-)」をつけ、逆方向に進むよう指示しましょう。
まとめ
OFFSET関数は値や範囲を参照する関数であり、抽出箇所のスタート位置を指定できるのが特徴です。行数・列数だけでなく高さや幅も指定できるので、用途に応じて柔軟な活用ができます。
OFFSET関数はMATCH関数やSUM関数と併用することも多いです。併用の方法を習得すれば都度関数の内容を書き換えることなく抽出範囲を変動させられるので、便利に使っていきましょう。
Excel OFFSET関数に関する重要用語
| 用語 | 説明 |
| OFFSET関数 | 値や範囲を参照する関数。基準となるセルから、指定した行数と列数分だけ縦と横にずらした場所にあるセルを参照できる。 |
| MATCH関数 | 指定した値と一致する最初のセルの位置を返す関数。検査範囲の中で照合する値を指定しながら使用できる。 |
| SUM関数 | 引数で指定したセルもしくはセルの範囲内にある数値の合計を計算する関数。 |
| INDEX関数 | 値や範囲を参照する関数で、OFFSET関数と違って「A1」や「B2:C3」のような文字でセルの範囲や参照したいセルを指定できる。 |
