Aさん VLOOKUP関数の検索を行なう表にデータを追加すると、いちいち数式中の「範囲」を直すのが面倒ですが、何か良い方法はないでしょうか？
森田 VLOOKUP関数でよくある困りごとですね。
制約がなければ、引数「範囲」を列で指定してしまうのが良いですが、制約がある場合は2つのテクニックがあります。
今回は、2つのテクニックのそれぞれの使いどころについて解説していきますね。
はじめに
本題に入る前に、この記事がおすすめな人を挙げてみます。
- データの転記・集約作業が多い人
- エクセルを使う頻度が高い人
- 事務職の人
ちなみに、最低限「VLOOKUP関数の基礎」を把握している想定での記事となります。
※自信がない方はこちらの記事で学んでください。
→【初心者向け】データ転記の作業時間を9割カット！VLOOKUP関数の使い方
VLOOKUP関数で参照している表へデータ追加を行なうと参照範囲にできていない可能性が・・・
けっこうあるあるですが、VLOOKUP関数で参照している表にデータを追加すると、その追加したデータで検索しようとすると、範囲外で「エラー」なんてことがあります。
たとえば、以下のようなイメージですね。
VLOOKUP関数が入っているセルをダブルクリック（もしくはF2キー）すると、追加したデータのみが引数「範囲」の対象外になっていることがわかりやすいですね。
こうなると、VLOOKUP関数の引数「範囲」に指定しているセル範囲を再度手作業で設定し直さければならないので面倒です。
この面倒な事象を起こさないために、どう対応すべきかを順番に解説していきますね。
【原則】VLOOKUP関数の引数「範囲」は列で指定！
まず、特に制約がなければVLOOKUP関数の引数「範囲」は列で指定しましょう。
そうすれば、参照したい表に対してデータを追加・削除しても何ら影響ありません。
※もちろん、データ追加・削除を「列」で行わない想定です。
これで事足りれば、まったくもって問題なしです。
しかし、次の2つの条件に当てはまるかどうかによって、次の2つのテクニックも追加で使えるようになっておくと、なお良いですよ。
【条件その1】引数「範囲」で参照したい表の上に引数「検索値」と同じ値があって列指定できない
条件その1はイメージ湧きにくいと思いますが、つまりは以下のような状態の場合です。
たまたま、VLOOKUP関数の引数「範囲」の1番左の列に引数「検索値」と同じ値が、参照したい表の上にある場合（レアケースですが）、VLOOKUP関数の戻り値（返り値）がおかしくなる可能性があるわけです。
【条件その2】共有ブックが対象
条件その2は、ブック名の後ろに[共有]と記載されている場合です。
共有ブックではテーブル機能は使用できないなど別の制約が生じます。
では、本題の2つのテクニックを解説していきましょう。
引数「範囲」を自動拡張する2つのテクニック
いずれもVLOOKUP関数の引数「範囲」の部分に組み合わせて使いますよ。
- テーブル機能（テーブルとして書式設定）
- OFFSET関数＋COUNTA関数
先ほどの2つの条件を踏まえて、どのテクニックを使えばよいか整理したのが次の表です。
ちなみに、各条件の”TRUE”は条件を満たしているということ、”FALSE”は条件を満たしていない場合という意味になります。
| 条件その2 | |||
|---|---|---|---|
| TRUE | FALSE | ||
| 条件その1 | TRUE | テクニック②【OFFSET関数＋COUNTA関数】 【中級者向け】データ追加してもVLOOKUP関数の引数「範囲」を自動で拡張させるテクニック②【OFFSET関数＋COUNTA関数】 | Excelを制する者は人生を制す ～No Excel No Life～ |
テクニック①【テーブル機能】【初心者向け】データ追加してもVLOOKUP関数の引数「範囲」を自動で拡張させるテクニック①【テーブル機能】 | Excelを制する者は人生を制す ～No Excel No Life～ |
| FALSE | 列指定 | 列指定 ※テクニック①【テーブル機能】でも可 |
|
状況に合わせて使い分けできるようになりましょう！
2つのテクニックの詳細は上記表のリンクから個別記事をご参照くださいね。
さいごに
VLOOKUP関数の「範囲」を列指定できないというケースはあまり頻度が高いものではないものの、実際に遭遇すると面倒です。
もし、実務で上記の状況に遭遇した際は、解説したテクニックを参考に取り入れてみてくださいね。
今回はVLOOKUP関数の「範囲」に特化した記事でしたが、それ意外のVLOOKUP関数の各引数に対する応用テクニックを私の書籍で紹介しているので、こちらもご参考にしていただければと思います。
▼VLOOKUP関数を基本から応用まで体系的に学びたい方向け
▼VLOOKUP関数の応用テクニックに加えて、その他IF関数なども学びたい方向け
ご参考になれば幸いですm(_ _)m
森田 今回の「範囲」の話は、実はVLOOKUP関数だけでなく、セル範囲を指定する引数で構成されている関数で応用可能なテクニックです。
このように、VLOOKUP関数を中心に関連する他の機能や関数を学習していくことで、エクセル全般のスキル向上に実はつながっているんです。
