今回は、
・vlookup関数を大量に使ってデータが重くなってきた…
・vlookup関数の範囲が増減するので、自動で可変するようにしたい…
このような悩みを解決していきます。
通常、vlookup関数で範囲を指定した後、行が増えると書き直さないといけないですよね。
今回使うOFFSET関数とCOUNTA関数で、
VLOOKUP関数の範囲を自動的に変更して指定する関数を解説していきます。
OFFSET関数とCOUNTA関数で範囲を可変として指定する
結論として、最終的な関数はこちらになります。
=VLOOKUP( F4 , OFFSET( A2 , 0 , 0 , COUNTA(A:A) , 4 ) , 4 , false )
実際に使用しているシートはこちらのスプレッドシートになります。
関数のみ知りたい、という方はサンプルのスプレッドシートの関数をコピペして使ってみて下さい！
ここからは具体的な中身を解説していきます。
範囲が可変するVLOOKUP関数を作る
今回使っているのはOFFSET関数とCOUNTA関数です。
それぞれの関数を解説します。
概要：指定の範囲から指定の範囲だけ値を指定して取り出すことができる関数
関数の書き方：
=OFFSET( 基準のセル , 基準からいくつ縦にずれるか , 基準からいくつ横にずれるか , 範囲行数 , 範囲列数)
概要：指定の範囲で空ではないセルの個数をカウントしてくれる関数
関数の書き方：
=COUNTA（範囲1 , 範囲2 , …）
この２つの関数を使って、
COUNTAでセルに値が入っている大きさを特定する（＝可変すべき範囲）
↓
OFFSETでその範囲を抽出する
↓
VLOOKUP関数の第２引数の範囲に入れ込む
この3ステップで関数を組んでいきます。
STEP1：COUNTAでセルに値が入っている大きさを特定する
まずセルに入力がある範囲＝vlookupの範囲となる行になりますので、
=counta( A : A )
と関数を作ります。
STEP2：OFFSETでその範囲を抽出する
次に、範囲を作っていきます。
今回は、vlookupの範囲となる基準点はA2セルになります。
= offset（A1 , ）
今回はシリアル値が入力されている範囲、を出力していきたいので、
縦、横にずれる必要はありません。そのため
= offset（A1 , 0 , 0 , ）
取り出したい高さ（＝行）はcountaで抽出したものになりますので、
= offset（A1 , 0 , 0 , counta( A : A )）
今回の範囲は4列ありますので、範囲列数は4となり、
= offset（A1 , 0 , 0 , counta( A : A ) , 4）
これで今回vlookupで指定する可変する範囲を作れました。
offsetのみで出力してみると、しっかり範囲が出力されていることがわかります。
STEP3：VLOOKUP関数の第２引数の範囲に入れ込む
あとは通常のvlookup関数の範囲部分に入れ込めば完成です。
=VLOOKUP（ F4 , OFFSET(A1,0,0,COUNTA(A:A) , 4 ) , 4 , false ）
まとめ
今回はvlookup関数の範囲を可変にする方法を解説していきました。
offset関数は使用する頻度はそこまで高くないですが、応用がしやすい関数になります。
しっかり理解して使えるようになりましょう！
