VLOOKUP関数やINDEX関数のようなExcelの行列関数の仲間に、OFFSET関数という関数があるのをご存知でしょうか？
OFFSET関数は表の中で指定した位置のセルを参照する関数のこと。
非常に特殊な関数で、普通の関数は、ある一つの値を答えとしますが、OFFSET関数は、範囲を答えとします。範囲を答えとしますので、OFFSET関数の答えをセルに求めても正しく何かを求める形にはなりません。ほとんどのケースではOFFSET関数は他の関数と組み合わせて使用します。
他の関数とは、「範囲」をもとに値を求める関数です。
範囲を指定する関数はとても多く、単純なものであればSUM関数も範囲を指定します。
Excelをある程度使いこなしている方であれば、SUM関数などと組み合わせて利用することもあるでしょう。
ほかにもMATCH関数との組み合わせでVLOOKUP関数ではできない「検索値よりも左側のセルを参照する」こともできる、名バイプレーヤー的存在なのです。
そこで今回の記事では、そんなOFFSET関数の使い方を詳しく解説していきます。
目次
OFFSET関数とは？
まずはOFFSET関数の構文や使い方についてみていきましょう。
OFFSET関数は、指定したセルまたはセル範囲から指定された行数と列数だけ移動した位置にあるセル範囲を返します。
もともとOFFSETの意味は、「相殺する」「埋め合わせするもの」と定義されており、関数の意味とは異なります。
OFFSET関数は、セルまたはセル範囲から指定された行数と列数だけシフトした位置にあるセル範囲の参照を返します。 返されるセル参照は、単一のセル、セル範囲のいずれかの参照です。 また、返されるセル参照の行数と列数を指定することもできます。
構文は次の通りです。
| OFFSET(基準, 行数, 列数, [高さ], [幅]) |
OFFSET関数の構文には、次の引数があります。
参照
1番目の引数は「参照」です。この引数は必須入力項目で、基準となるセルまたはセル範囲を指定します。参照はセルまたは隣接するセル範囲を参照する必要があります。
それ以外の場合は、エラー値 #VALUE! が返されます。
行数
2番目の引数は「行数」です。この引数は必須入力項目です。
第1引数「参照」で指定したセルまたはセル範囲の左上（以下基準とします）から上方向、または下方向へ移動する距離を数値で指定します。
「0」を指定すると基準と同じ行、「1」だと基準の1つ下の行、「2」だと基準の2つ下の行・・・を指定していくのです。
「-1」のように負の値を指定すると基準より上の行を指定できます。
行数に正の数を指定すると開始位置の下方向へシフトし、負の数を指定すると開始位置の上方向へシフトする、というわけです。
列数
必ず指定します。 結果の左上隅のセルを左方向または右方向へシフトする距離を列数単位で指定します。 列数に 5 を指定すると、オフセット参照の左上隅のセルは、基準の左上隅のセルから 5 列右方向へシフトします。 列数に正の数を指定すると開始位置から右方向へシフトし、負の数を指定すると開始位置から左方向へシフトします。
高さ
この引数は必須入力項目です。結果として返したいセル範囲の行数を数値で指定し、高さは正の値である必要があります。省略した場合、第1引数「参照」で指定したセルまたはセル範囲と同じ行数とみなされるので注意しましょう。
幅
この引数は任意で、必要に応じて返したいセル範囲の列数を数値で指定します。
幅は正の値である必要があります。省略した場合、第1引数「参照」で指定したセルまたはセル範囲と同じ列数とみなされます。
参照する行または列がワークシートの端よりも外側になる場合は、エラー値 #REF! が返されます。
高さまたは幅を省略すると、基準のセル範囲と同じ行数または列数であるとみなされます。
このように、OFFSET関数は実際にセルを移動させたり、選択対象を変更したりしないで、ただセル参照を返すだけです。
OFFSET関数は、セル参照を引数として使う関数と共に使います。 たとえば、数式 SUM(OFFSET(C2,1,2,3,1)) では、セルC2を基準とし、セルC2から下方向に1行、右方向に2列シフトし、高さが3行で幅が1列のセル範囲の値を集計します。
他の関数と組み合わせてOFFSET関数を使う方法
関数を作成するには2通りの作成方法があります。
1つ目は手入力で、2つ目は関数の挿入というツールを使って作成する方法です。
手入力で関数を作成する場合、関数で使用する引数の意味や個数をしっかりと把握しておかなければなりません。
一方、関数の挿入ツールを使えば、引数部分にそれぞれ必要項目を入力するだけで関数を作成することが可能になります。
また、関数の挿入を使うことで、引数ごとに使用する演算子と呼ばれる「,（カンマ）」や「/（スラッシュ）」などの記号、文字列を入力する際、必ず入力しなければならない「””(ダブルクォーテーション)」も自動で入力してくれるため、ぬけもれによるエラーを防ぎながら関数を作成できるのもポイントです。
また手入力だと、「OFFSET」といちいち英単語を入力しなければなりません。
しかしながら、関数の挿入では頭文字さえ覚えていれば入力した頭文字を含む関数を検索して自動表示してくれます。そのため、Excel初心者だけでなくある程度使いこなしている方でも、スペルに自信がない場合には重宝します。
ですから、関数を作成する場合は手入力だけでなく、関数の挿入と呼ばれるツールでも作成できることを覚えておきましょう。
入力ミスによるエラーを防ぎながら上手に関数を活用できるようになりますよ。
参考：キャリチェン｜【Excel】OFFSET関数とは？指定した数だけシフトした位置にあるセル範囲を参照する関数
OFFSET関数とINDEX関数の違い
OFFSET関数と似ている関数にINDEX関数があります。
ここではINDEX関数の定義と、OFFSET関数と何が違うのかを紹介していきます。
INDEX関数とは
INDEX関数は、範囲内の「縦にいくつ」「横にいくつ」の位置にあるセルの値を調べる関数です。INDEX 関数には、次の2つの使い方があります。
- 指定したセルまたはセルの配列の値を求める場合は、「配列形式」を参照してください。
- 指定したセルの参照を求める場合は、「セル範囲形式」を参照してください。
配列形式
行番号と列番号で指定されるテーブルまたは配列の要素の値を返します。INDEX 関数の1番目の引数が配列定数のときは、配列形式を使います。
構文は次の通りで
| INDEX(配列, 行番号, 列番号) |
INDEX関数の配列形式には次の引数があります。
配列
配列は必須入力項目で、セル範囲または配列定数を指定します。
配列が1行または1列のみの場合、それぞれ行番号または列番号を省略することができます。配列が複数行および複数列で構成され、行番号または列番号のどちらか一方しか指定されていない場合、配列の中にある行または列全体の配列が返されます。
行番号
列番号が存在する場合を除いて必須の項目です。
配列の中にあり、値を返す行を数値で指定します。 行番号を省略する場合は、列番号が必要になります。
列番号
列番号は任意なので、必要に応じて配列の中にあり、値を返す列を数値で指定してください。 列番号を省略する場合は行番号が必要になるので注意が必要です。
また、行番号と列番号の両方の引数を使用している場合は、行番号と列番号の共通部分にあるセルの値が INDEX関数によって返されます。
行番号と列番号には、配列内のセルを指定する必要があるため、配列の範囲外のセルを指定すると、INDEX関数は #REF!エラーを返します。
さらに、行番号または列番号を 0 （ゼロ)）と設定すると、列全体または行全体の値の配列がそれぞれ INDEX関数によって返されます。
そのため、配列として返される値を使用するには、INDEX関数を配列数式として入力しましょう。
INDEX関数との違いについて
OFFSET関数と使い方が似ているINDEX関数との違いについてみていきましょう。
両者とも行と列を指定した位置にあるセル範囲を返しますが、OFFSET関数はさらに結果として返したいセル範囲の大きさを指定することができます。
引数の数はOFFSET関数が最大5つ、INDEX関数が最大で4つとなっています。
また、基準からの行と列の数え方も違います。
OFFSET関数とINDEX関数では基準からの行と列の数え方に違いがあります。OFFSET関数は0から数えますが、INDEX関数は1から数えます。
上記内容をまとめると、次の2つがOFFSET関数とINDEX関数の違いといえます。
- 関数の意味と引数が違う
- 基準からの行と列の数え方が違う
応用編｜OFFSET関数を他の関数と組み合わせて使う
最後にOFFSET他の関数との組み合わせを3つご紹介します。
SUM関数との組み合わせ
1つ目は、SUM関数との組み合わせです。
基準から移動した先で範囲を指定し合計を計算するという動きをします。
この例は基準セルA1から下に「5」、右に「4」移動した先をさらに基準にして、高さを「3」、幅「1」の範囲をSUM関数で合計しています。
数式の形は「=SUM(OFFSET(参照,行数,列数,高さ,幅))」です。
注意点としては、「行数、列数として0（移動しない）は指定できるが、高さと幅で「0」は指定できない」ことです。
「1行だけ」、「1列だけ」と指定したい場合には「1」と入力しましょう。
MATCH関数との組み合わせ
2つ目は、MATCH関数との組み合わせ方です。
OFFSET関数とMATCH関数を組み合わせると、「値を抜き出したい行と列の条件を可変的にすること」ができます。
上の例はセルG3でA列のNo.を指定し、セルH2で表示させたい内容を指定しています。
ここでは「No.1の名前」を指定していますので、結果「小山」がセルH3に表示されました。
そして、これを「No5の契約料」に変えると値は「1,500」に変わります。
セルH3に入力している数式は「=OFFSET(A1,MATCH(G3,A2:A11,0),MATCH(H2,A1:E1,0)-1)」です。
行数のMATCH関数の検査値をセルG3に入力した値とし、列数のMATCH関数の検査値をセルH2とすれば、セルG3とH2の値を変えることで、抽出する値を変えられます。
VLOOKUP関数との組み合わせ
3つ目は、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関数で指定できるのです。
参考：ExcelのOFFSET関数は何ができる？使い方の基本と関数を組み合わせる応用編
まとめ
いかがでしたでしょうか。
今回の記事ではOFFSET関数について、構文の作り方や使い方、他の関数との組み合わせを紹介してきました。OFFSET関数はExcel初心者の方にはあまり馴染みがなく、どちらかといえば応用した使い方をされる関数です。
そのため、慣れるまでは分かりにくい点はありますが、活用できると業務の幅がぐっと広がります。本記事を参考に、OFFSET関数の使い方をしっかりとマスターしましょう！
