関数好き（？）な人にはおなじみのOFFSET関数ですが、Vlook等と違い余り活用されているのを実務の場でも見かけない気がします。
なので「こんな感じで活用できるよ」をご紹介。
OFFSET関数を使用してどんなことができる？
まずは具体的に、OFFSET関数を使ったらどんなことが便利になるのかです。
下記のようなことでよく利用されていると思います。
- 合計する対象が頻繁に変わる計算で便利（SUM関数（SUBTOTAL関数）と組み合わせて使う）
- 入力規則に表示する値も動的に取得できる（入力規則と組み合わせて使う）
SUM関数は業務でも良く使うと思いますが、例えばでサンプル表を作成しました。
サンプル表で説明
こちらのサンプル表の概要
- 名前（行）は営業担当者
- 月（列）は実績対象の月
- 入力「担当者」は入力規則から担当者を選択
- 入力「月」は入力規則から対象月を選択
- 成約数の集計にはそれぞれ自動で数値が表示される
- 入力の「担当者」の入力規則のリスト指定
- 入力の「月」の入力規則のリスト指定
- 成約数の集計の「〇月分成約数」の数式
- 成約数の集計の「担当者合計」の数式
それぞれ下記で説明します。
入力規則もOFFSET関数が便利
入力規則は選択範囲（対象の値の個数）に変更があった場合に手動で再設定が必要になります。
OFFSET関数でリストの範囲を指定しておくと、担当者や月の数が変動しても自動で入力規則に反映してくれるようにできるので便利です。
入力規則のデータ変動が多いものに設定すると恩恵が大きい
武田さんを最後尾に追加すると、入力規則にも自動で追加されます。
最後尾じゃなくて行の途中でも追加されます。
行を消すと入力規則でも削除されます。
（織田さんの前にいた鈴木さんを削除してます）
担当者が増えたり減ったりしても、入力規則をいちいち修正する手間がなくなります
リストの範囲をOFFSET関数で設定する
こんな感じでリストを選択して元の値にOFFSET関数を入力しています。
=OFFSET($A$1,1,0,COUNTA($A:$A)-1,1)
関数の詳細は後述しますが、一応ざっくりと説明。
「$A$1」は基準とするセルです。
「1」は移動する行の数、「0」は移動する列の数です。
「COUNTA関数」は返される範囲の高さを指定しています。タイトル行の分が余計なので「-1」しています。
最後の「1」は返される範囲の幅です。こちらは1しか入力できません。
ちなみに幅を「2」以上で入れようとするとエラーになります
「月は入力でもいいじゃん！」と思われる方も多いと思います。
実際その方が楽だと思います。
ですが、入力規則を設定することで想定外の操作（ありえない数字、文字の入力）によるトラブル防止になる場合があります。
今回の月に関しては余り他に影響しないものですが、場合によっては誤った入力によって、トラブルになるものもあるからです。
自分しか使わない場合は不要ですが、いろんな人が使用するファイルの場合は有効な対策の一つかなと思います。
「SUM関数」など指定範囲を計算する数式で使う
SUM関数などの指定した範囲内を対象にしているものはOFFSET関数で指定が可能です。
入力の値を変えることで、見たい成約数が簡単に切り替わって表示されるので便利です。
それぞれ少し内容が違うのでバラして説明します。
〇月分成約数の数式
=OFFSET($A$1,MATCH($I$3,$A:$A,0)-1,MATCH($I$4,$1:$1,0)-1,1,1)
ざっくりと説明。
「$A$1」は基準とするセルです。
「MATCH($I$3,$A:$A,0)-1」は移動する行の数を取得しています。今回は「I3」に入力している担当者と一致する名前がある行番号を取得しています（MATCHの説明は別記事参照）
（移動する数なので、取得した行番号-1してます）
「MATCH($I$4,$1:$1,0)-1」は移動する列の数です。「I4」を基準として、タイトル行から一致する月の列番号を取得しています。
範囲については今回は固定値で「1,1」となります（仮に範囲指定しても表示されるのは1つだけ）
書いておいてなんですが、これはINDEX関数でもできます
正直これに関しては、INDEX関数の方がすっきり書けます。
一応OFFSET関数でもできること、として書いてみました。
=INDEX($A$1:$F$8,MATCH($I$3,$A:$A,0),MATCH($I$4,$1:$1,0))
担当者合計の数式
=SUM(OFFSET($A$1,MATCH($I$3,$A:$A,0)-1,1,1,5))
ざっくりと説明。
SUM関数の範囲指定のところにOFFSET関数を入れています。
基準の値が「$A$1」です。
移動する行の数は、MATCHで「I3」の担当者名と一致する行番号から-1で取得。
移動する列の数は、右隣が開始位置なので1列移動。
返される範囲の高さは、担当者の行だけで良いので「1」としています。
返される範囲の幅は、担当者行の最終列6から-1して取得しています。
担当者に連動して、対象になる行だけ取得してあげる感じです。
他の値は固定なのでわかりやすいと思います。
注意点としては月数（幅）を固定にしているので、月が増えた際は手動で変更が必要になります。
OFFSET関数ってどんな関数？
OFFSET関数について公式の説明です。
セルまたはセル範囲から指定された行数と列数だけシフトした位置にあるセル範囲の参照を返します。 返されるセル参照は、単一のセル、セル範囲のいずれかの参照です。 また、返されるセル参照の行数と列数を指定することもできます
Microsoft公式 OFFSET 関数
- 指定したセルから、指定した行列を移動したセルを基準としてセル範囲を返す
- 返される範囲はセル単位、範囲のどちらもあり
- 範囲は指定できる
細かいところはちょっと分かり難そうなので図解します。
文字での説明が下手なんです、すみません・・・
指定したセルから、指定した行列を移動したセルを基準としてセル範囲を返す
- こちらの例の「OFFSET($A$1,1,4,1,1)」のA2が「指定したセル」にあたります
-
基準となるセルで、ここから行列に「何セル移動するか」を指定します
- 「OFFSET(A1,1,4,1,1)」の1、4が移動するセルの値になります
-
1=行
4=列図解するとこんな感じです
まず行は1なので1こ下に動きます。
次に列は4なので4つ分セルを移動します。
返される範囲を指定
今回は「1,1」なのでセル1コが返される対象範囲という意味です。
結果「90」という値が「J2（関数が入力されているセル）」に表示されています。
「返される範囲の指定」は指定した内容通りにならないこともある
-
「返される範囲」となっているので、範囲で返せるのか思いきや、今回のように「オフセットした位置の値を取得して、セルに表示する」場合は1つのセル（範囲）しか表示されません。
仮に範囲で指定（「2,1」とか）すると一番最初の値が表示されました。
範囲では取得できないっぽい
SUM関数や入力規則と組み合わせる時は範囲指定が使える
別の関数や機能と組み合わせて使う場合（「SUM」や「入力規則」など）は、指定した範囲がちゃんと対象になってる。
なので、範囲指定に関しては下記の認識です。
- 指定範囲を貼り付ける（関数のあるセルを起点として）ことはできない（※方法はある）
- 他の関数や機能と組み合わせた場合は範囲で取得できる
私の調べ不足と認識誤りの可能性もあるので、気になる人はご自分で検証してみてください。
なんかセルで範囲指定したらペロッと、そのセルを起点にデータ持ってこれるのかなとか思ってた・・・
「配列数式」と組み合わせると範囲まとめて取得（表示）できるよ
「配列数式」というものを使うと、指定した範囲をペロッと貼り付けて表示することができるようになります。結構便利そうです。
これについては別記事で書きますが、これを使うとこんな感じで便利に範囲を取得できます。
下記は例えですが、学年を「3」とすると、学年項目が3の全員を取得してきます。学年を変えることでデータの内容が変わります。
こちらではマクロ（VBA）は使っていません。
VLOOKやINDEX等で取得する場合は1セルのみしか持ってこれないので、こういう風にまるっと対象が取得できるのは便利かなと
あわせて読みたい【Excel/OFFSET関数＆配列数式】条件に合う範囲(リスト)を取得して表示できた！ 「OFFSET関数」は知ってるけど「配列数式」って何ぞや？ていう方は多いと思います、あんまり聞かないですよね。私もつい最近知りました。 この二つを合わせて使うと便利…
OFFSET関数のちょいメモ
OFFSET関数を使用する際に注意しておきたいポイントや、ちょっとメモです。
OFFSET関数の注意点
- 参照する表は適宜ソートしておく必要がある
- セルに表示する時は範囲指定しても最初の1つしか表示されない
適宜ソートしておく
VLOOKなどと違い値を比較してひとつづつを取得しているわけでは無く、単純に基準のセルを起点として、移動した位置を起点とした範囲を取得するだけなので、利用する場合は対象の値がまとまっている必要がある。
セルに表示する場合は通常は1つだけ取得される
数式で返される範囲を複数行（列）で指定した場合でも、取得した範囲の最初の1つだけが表示される。
表のように範囲を取得したい場合は「配列数式」を組み合わせるとたぶんできる（別記事参照）
まとめ
普段の業務でOFFSET関数が活躍しそうなのは入力規則が多いかなーと思います。
SUM関数なども相性は良いので、良い感じに取り入れてると作業効率アップできそうです。
最初とっつき辛い感じですが、指定する数がちょっと多いだけなので、慣れてくると結構使えると思います。
