Aさん VLOOKUP関数の検索を行なう表にデータを追加すると、いちいち数式中の「範囲」を直すのが面倒ですが、何か良い方法はないでしょうか？
森田 VLOOKUP関数でよくある困りごとですね。
制約がなければ、引数「範囲」を列で指定してしまうか、検索したい表をテーブル化した上で「範囲」に指定することがおすすめです。
今回は、後者の検索したい表をテーブル化した上で「範囲」に指定するテクニックを教えます。
それでは、詳細をわかりやすく解説していきますね。
はじめに
本題に入る前に、この記事がおすすめな人を挙げてみます。
- データの転記・集約作業が多い人
- エクセルを使う頻度が高い人
- 事務職の人
ちなみに、最低限「VLOOKUP関数の基礎」を把握している想定での記事となります。
※自信がない方はこちらの記事で学んでください。
→【初心者向け】データ転記の作業時間を9割カット！VLOOKUP関数の使い方
VLOOKUP関数の参照先の表へデータ追加を行なうと参照範囲にできていない可能性が・・・
けっこうあるあるですが、VLOOKUP関数で参照している表にデータを追加すると、その追加したデータで検索しようとすると、範囲外で「エラー」なんてことがあります。
たとえば、以下のようなイメージですね。
VLOOKUP関数が入っているセルをダブルクリック（もしくはF2キー）すると、追加したデータのみが引数「範囲」の対象外になっていることがわかりやすいですね。
こうなると、VLOOKUP関数の引数「範囲」に指定しているセル範囲を再度手作業で設定し直さければならないので面倒です。
この面倒な事象を起こさないために、どう対応すべきかを順番に解説していきますね。
【原則】VLOOKUP関数の引数「範囲」は列で指定！
まず、特に制約がなければVLOOKUP関数の引数「範囲」は列で指定しましょう。
そうすれば、参照したい表に対してデータを追加・削除しても何ら影響ありません。
※もちろん、データ追加・削除を「列」で行わない想定です。
これで事足りれば、本記事のテクニックは不要です。
しかし、次の2つの条件のうち、1つ目は当てはまり、2つ目に当てはまらない場合に本記事のテクニックは非常におすすめです。
【条件その1】引数「範囲」で参照したい表の上に引数「検索値」と同じ値があって列指定できない
条件その1はイメージ湧きにくいと思いますが、つまりは以下のような状態の場合です。
たまたま、VLOOKUP関数の引数「範囲」の1番左の列に引数「検索値」と同じ値が、参照したい表の上にある場合（レアケースですが）、VLOOKUP関数の戻り値（返り値）がおかしくなる可能性があるわけです。
【条件その2】共有ブックが対象
条件その2は、ブック名の後ろに[共有]と記載されている場合です。
共有ブックではテーブル機能は使用できないため、対象のブックが共有されていないことが本記事のテクニックの条件になりますのでご注意ください。
逆に条件その2も当てはまる場合は、以下のテクニックの方がおすすめです。
引数「範囲」にテーブル機能を組み合わせる！
VLOOKUP関数の引数「範囲」をエクセル側で自動的にサイズ変更してくれるようにするために、テーブル機能を活用します。
テーブル機能とは？
テーブル機能とは、正式には「テーブルとして書式設定」という機能を用いて任意の表をテーブル化してしまうことです。
ちなみに、「テーブル」とは直訳すると“表”ですが、エクセルにおける「テーブル」は、行・列に新規データを追加すると、テーブル内の直近データの書式や数式、入力規則などの諸条件が引き継がれるという特徴を持った表を指します。
その他のテーブル機能のメリットはこちらの記事をご参照ください。→こちら
テーブル化の手順
テーブル機能を活用するのは、非常に簡単な操作手順で実現できます。
- テーブル化したい任意の表を選択
- リボン「ホーム」タブを表示
- 「テーブルとして書式設定」をクリック
- 任意のテーブルスタイルを選択
- 「テーブルとして書式設定」ダイアログの内容に問題ないことを確認
- 「テーブルとして書式設定」ダイアログの「OK」ボタンをクリック
簡単に設定できちゃいますね。
もっと詳細を知りたい方はこの記事もどうぞ→こちら
組み合わせるとはどういうことか？
組み合わせるにあたって特別な操作は不要です。
テーブル化を設定した上で、通常通りVLOOKUP関数を設定すればOKです。
すると、VLOOKUP関数の引数「範囲」が”テーブル1”のようにいつもと違う表記へ自動的に設定されます。
この表記は「構造化参照」といいます。簡単にいうと、テーブル化した表（セル範囲）は”テーブル1”という名称がついたのだと思ってください。
今後、数式でこの”テーブル1”を利用することで、VLOOKUP関数の引数「範囲」の参照先の表へデータ追加しても関数側で自動的に参照範囲を調整してくれます。
サンプルファイルで練習しよう！
では、実際に今回のVLOOKUP関数とテーブル機能の組み合わせテクニックを使ってみましょう！
サンプルの条件
今回の題材は次のとおりです。
「商品」というシートにくだものの名称・単価が一覧表になっています。
この「単価」を別シート「台帳」のC列の2行目以降に転記したいとします。
この「台帳」シートのB列には「くだもの」の名称がすでに入っているので、これをVLOOKUP関数のキーワードにしていきましょう。
実際に操作しよう！①
可能であれば、以下のサンプルファイルをダウンロードして実際に操作してみてください。
サンプルファイル_VLOOKUP関数_範囲①
※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
（上記リンクから登録フォームへ遷移します）
ファイルを開いたら次の手順を実施してください。
- 「台帳」シートを選択
- C2セルへ「=VLOOKUP($B2,テーブル1,2,0)」を入力
※赤字の部分をコピーして貼り付けてください。 - C2セルをコピー
- C3～C6へ貼り付け（ペースト）
上記手順を行った結果は「台帳 (関数あり)」シートにありますので、このシートの内容と実際に操作した結果が同じなっていればOKです。
「商品」シートの7行目に新規データを追加して、そのデータもVLOOKUP関数で取得できるかも確認してみましょう。（例は以下のとおり）
実際に操作しよう！②
せっかくなので、テーブル化も実際に操作してみましょう。
サンプルファイルの「商品 (テーブル化前)」シートの表を使ってテーブル化してみましょう。
手順は上記の「テーブル化の手順」の箇所か、こちらの記事を参考にしてみてください。
ポイントの解説
最後に今回のテクニックの3つのポイントについて解説していきましょう。
【POINT1】主キーの設定
このテクニックに限らずVLOOKUP関数を使う際のマストなポイントです。
こちらは検索を行う対象の表に「主キー」が入ったものを準備しておきましょう。
この「主キー」とは、データを固有のものと識別するための値であり、かつ一意（他データと重複していない）である必要があります。
今回の例でいえば、”出席番号”が該当しますね。
こういった「主キー」があると、VLOOKUP関数で意図しない戻り値（返り値）になることを防ぐことができます。
【POINT2】テーブル化してからVLOOKUP関数を挿入
今回のテクニックの最重要ポイントですね。
必ず順番としては、VLOOKUP関数の検索対象の表をテーブル化してからVLOOKUP関数を設定しましょう。
実際、順番を逆にしても、機能としては引数「範囲」は自動的に拡張されることは確認できますが、数式中の引数「範囲」の表記が構造化参照（テーブル1など）に変わりません。
つまり、通常通りの「A1:B6」のような表記のままとなり、データ追加時は自動的に「A1:B7」などと拡張されていきます。
しかし、「裏側でテーブル化されている」いうことになるので、設定した人以外に気づきにくくなるため、作業の順番には気をつけましょう。
【POINT3】絶対参照・相対参照の設定
これは、数式を扱う上で基礎中の基礎ですが、コピペを一括で行うために必要です。
今回セル参照しているのはVLOOKUP関数の引数「検索値」のみです。
今回は「$B2」と列のみ絶対参照にしていますが、行部分が相対参照であれば列は絶対参照でなくともOKです。
これは、C2セルのVLOOKUP関数を下方向にしかコピペしていかないため、行は固定しないようにするわけです。
さいごに
今回のテクニックでご紹介したテーブル機能は、設定が簡単ですが複数の機能がパッケージされているため、時短効果が期待できる便利機能です。
特段制限がない場合は、私は通常業務の中で触れる表は、なるべくテーブル化するようにしています。
また、今回のVLOOKUP関数以外の範囲を指定する関数とも相性が良いので、いろいろ試してみてください。
なお、その他のVLOOKUP関数関連の役立つテクニックについて、私の書籍で紹介しているので、こちらもご参考にしていただければと思います。
▼VLOOKUP関数を基本から応用まで体系的に学びたい方向け
▼VLOOKUP関数の応用テクニックに加えて、その他IF関数なども学びたい方向け
ご参考になれば幸いですm(_ _)m
森田 私はテーブル機能をExcel2010から頻繁に使うようになりました。設定するのは簡単にできますし、表の見た目もワンタッチで切り替えできるので罫線を引く手間もなくなって便利ですよ！本当におすすめな基本機能です。
