今回の記事では、単体では使い道がなく目立たない関数ですが、範囲を指定するVLOOKUP関数やCOUNTA関数などと組み合わせることでマスタ側にデータを追加して行数が増えても、それに合わせて参照する範囲を自動で変えることが出来るとても便利なOFFSET関数について解説したいと思います。
OFFSET関数は=OFFSET(参照,行数,列数,高さ,幅)の5つの引数を指定することで使うことができます。
参照セルから行数と列数分移動した位置から高さと幅の分のセルの範囲のデータを参照します。1つのセルのデータではなく、範囲内のデータを全て取得（スピル）できるのが特徴です。
OFFSET関数はこのように使用しますが、この関数だけでは使いどころがほとんどありません。
しかし、COUNTA関数と組み合わせて使うことでVLOOKUP関数などで使う、範囲に指定するマスタの行数が変わったとき自動的に行数を変えることができるようになります。
それでは詳しく解説していきます。
↓記事のまとめページに戻るのはこちら↓
1．OFFSET関数とは？
OFFSET関数では＝OFFSET(参照,行数,列数,高さ,幅)と5つの引数(ひきすう)を指定して使用します。
参照の引数は、基準となるセル1つを指定する場合と、表の範囲を指定する場合があります。
指定する引数が5つと多く、文章では説明しづらいので表を使って解説します。
1-1．1つのセルを参照にする場合
上の表では、参照セルをB5セル1つに指定しています。そこから行数分2行下に列数分2列右に移動したセルを高さ1、幅1の1つのセルとして参照しているため、結果はD7セルに入力された11という数値が返されています。
1-2．表の範囲を参照にする場合
上の表では参照セルをB5からE8セルの範囲で指定しています。このような使い方をした場合、INDEX関数と同じような使い方ができますが、INDEX関数との相違点は基準となる行数と列数が0から数える点です。
行数と列数を0から数えるため、2行目、0列目と指定した上の表ではB7セルの9という数値が返されています。
2．VLOOKUP関数で参照範囲を可変させる方法とは？
ここまでの解説でOFFSET関数がどのような働きをするかは理解いただけたと思います。
ただ、このOFFSET関数の活用方法はイマイチどのように使っていいかわかりづらいと思います。
そこでOFFSET関数の使い方の一例として、VLOOKUP関数での参照範囲をOFFSET関数とCOUNTA関数を使って可変にする方法を解説したいと思います。
この使い方を覚えておくと、商品数が増えた時などに、参照するマスタ側を追加した場合にその都度VLOOKUP関数側の計算式を変更する必要がなくなるという、とても便利な使い方です。
それでは解説していきます。
VLOOKUP関数を使うときは、＝VLOOKUP(検索値,範囲,列番号,検索方法)の4つを指定します。
今回は、範囲を指定するところにOFFSET関数とCOUNTA関数を使うことで、自動で指定する範囲を常に表の一番下まで指定するようにしたいと思います。
↓それぞれの関数の解説はこちら↓
・VLOOKUP関数の使い方についてはこちらをお読みください。
・INDEX、MATCH関数の使い方についてはこちらをお読みください。
・COUNTA関数についてはこちらをお読みください。
上の表にある計算式が、今回の自動で範囲を指定する計算式です。
かなり長い計算式になるので、部分ごとに分解して解説していきたいと思います。
＝VLOOKUP(検索値,OFFSET(マスタの左上,0,0,COUNTA(マスタのある列)‐1,マスタ列数),列番号,FALSE)
この計算式はVLOOKUP関数の2つ目の引数の範囲の部分にOFFSET関数とCOUNTA関数を使っています。
赤部分がVLOOKUP関数、青部分がOFFSET関数、緑部分がCOUNTA関数です。
それ以外の部分は通常のVLOOKUP関数と変わらないのでその部分を詳しく解説します。
まず、OFFSET関数の1つ目の引数の参照(赤枠部分)にマスタの一番左上のセルを指定します。
今回の場合はマスタの左上はA2セルなので、A2セルを指定します。
2，3つ目の引数は、行と列ともにずらす必要はないのでどちらも”0”を指定します。
4つ目の引数に高さ(青枠部分)を指定することになるので、ここにCOUNTA関数を使用します。
今回の場合、A列にマスタがあるのでCOUNTA(A:A)とA列全体を指定します。
マスタ内のA列で文字の入力があるセルをCOUNTA関数で数えることで、マスタが増えたり、減ったりした場合に自動的に参照する高さの範囲を変えることができます。
また、1行目は題名が入っているので、そこを除くためにCOUNTA(A:A)の結果から-1をしています。
その結果、この表ではA列の行数が6、そこから-1することで”5”という数値が指定されます。
5つ目の引数はマスタの列数(緑枠部分)を指定します。
この表のマスタは3列なので”3”と指定します。
上の表の計算式をまとめると下のようになります。
=VLOOKUP(F4,OFFSET(A2,0,0,COUNTA(A:A)-1,3),2,FALSE)
このように、指定することでマスタの範囲がA2セルから下に5行、右に3列の範囲をOFFSET関数で指定することができます。
OFFSET関数で指定した範囲がそのままVLOOKUP関数の範囲に指定されるため、マスタの行数が変わってもCOUNTA関数の結果が変わり、自動的に範囲を修正してくれるVLOOKUP関数を作ることができます。
今回はOFFSET関数の使い方と、VLOOKUP関数での参照範囲を可変にする方法を解説しました。
応用としてOFFSET関数を使う場所を、行ではなく列に指定することで、マスタが横に増える場合に列数を可変させることも可能になります。
VLOOKUP関数を使う際に、自動的に範囲が変わるというのは思った以上に便利です。
ぜひ、マスターしていただければと思います。
最後までお読みいただきありがとうございました。
