【エクセル中級者向け】VLOOKUP関数の引数「範囲」を条件に応じて切り替えるテクニック【INDIRECT関数＋「名前の定義」機能】

AさんAさん

VLOOKUP関数で検索をかけたい表が複数ある場合、いちいち数式中の「範囲」を直すのが面倒ですが、何か良い方法はないでしょうか？？

森田森田

なかなか質問のレベルが上がってきましたね！
VLOOKUP関数で複数の表を自動的に切り替えるためには、INDIRECT関数と「名前の定義」機能を組み合わせればOKです。
それでは、詳細をわかりやすく解説していきますね。

Excelステップ講座

はじめに

本題に入る前に、この記事がおすすめな人を挙げてみます。

  • データの転記・集約作業が多い人
  • エクセルを使う頻度が高い人
  • 事務職の人

ちなみに、最低限「VLOOKUP関数の基礎」を把握している想定での記事となります。
※自信がない方はこちらの記事で学んでください。
→【初心者向け】データ転記の作業時間を9割カット！VLOOKUP関数の使い方

VLOOKUP関数で参照したい表が複数あるとは？

エクセルでデータ管理を行っていると、1つの表でなく、表を分けて管理した方がわかりやすい場合があります。

たとえば、以下のように、表の構成やデータの形式は共通しているものの、それぞれのデータを区別したいというケースです。

他にも、データを区別する基準として良くあるケースは以下のとおりですね。

  • 平日と休日
  • 会員と非会員、あるいはランク別
  • グループ別（クラス、部署、顧客属性など）
  • 時系列別（年度、月別、日別、時間別など）

他にも該当するケースがあるかもしれませんが、このような場合に役立つのが今回のテクニックです。

引数「範囲」にINDIRECT関数と「名前の定義」機能を組み合わせる！

VLOOKUP関数の引数「範囲」をエクセル側で条件に応じて自動的に参照する表を切り替えるようにするためにINDIRECT関数と「名前の定義」機能を活用します。

INDIRECT関数とは？

このINDIRECT関数はVLOOKUP関数と同じく検索/行列関数の一種で、任意のセルの文字列を数式で参照したいセル範囲の一部にすることができる関数です。

INDIRECT(参照文字列, [参照形式])

イメージが湧くように、INDIRECT関数を単独で使ってみましょう。

たとえば、B8セルに参照させたいセルを入力すると、その入力された内容のセルを実際に参照させたい場合にINDIRECT関数が役立ちます。

今回は以下のようにB8セルに”A1”という値を入力してみました。

C8セルのINDIRECT関数の引数「参照文字列」にB8セルを指定し、引数「参照形式」は省略したところ、”くだもの”という値が戻り値（返り値）になっていますね。

これは、INDIRECT関数の引数「参照文字列」に指定したB8セルに入っている”A1”という文字列をセルとみなした数式に変わることを意味します。

つまり、「=INDEIRECT(B8)」という数式が実質「=A1」となり、A1セルに入っている”くだもの”という文字列がINDIRECT関数の結果になっているということですね。

ちなみに、シート跨ぎのセル参照ももちろん対応しています。

たとえば、今度はB8セルの値を”台帳”にし、C8セルの数式を「=INDIRECT(B8&”!A2″)」と入力した結果も見ていきましょう。

INDIRECT関数の結果として、”2017/1/7”となっていますね。

これは、INDIRECT関数の引数「参照文字列」に指定したB8セルに入っている”台帳”という文字列もセル参照の一部として見られる点はいっしょです。

あとは、残りのセル参照したい部分の残りをダブルクォーテーション[“”]で囲い、アンパサンド[&]で繋げてあげれば、通常のシート跨ぎのセル参照と同じく「=台帳!A2」という数式とみなされます。

この結果、「台帳」シートのA2セルの値である”2017/1/7”という文字列がINDIRECT関数の結果になりますね。

同じようにブック跨ぎの参照の場合も同様ですので、いろいろお試しください。

INDIRECT関数の戻り値（返り値）となったセル範囲の確認方法

ちなみに、INDIRECT関数の戻り値（返り値）となったセル範囲の確認方法は、以下の手順で確認できます。

  1. INDIRECT関数の入ったセルを選択
  2. リボン「数式タブ」を表示
  3. 「数式の検証」をクリック
  4. 「数式の検証」ダイアログの「検証」ボタンをクリック（確認したい結果が出るまで複数回クリック）

「数式の検証」は地味に便利なので、この機会に覚えましょうね。

もっと詳細を知りたい方はこの記事もどうぞ→こちら

「名前の定義」機能とは？

「名前の定義」機能とは、セル範囲に任意の名前を付けることです。

この名前を付けることを「名前の定義」と呼びます。

定義した名前は数式に流用できるので、数式をシンプルにすることができますよ。

ただし、設定した人以外には逆にどこを指定しているのかわからなくなる可能性もあるため、乱用は避けましょう。

「名前の定義」の手順

「名前の定義」機能を活用するには、以下の操作手順で実現できます。

  1. VLOOKUPで検索したい表のセル範囲を選択し、マウス右クリック
  2. 「名前の定義」をクリック
  3. 「新しい名前」ダイアログの「名前」へ任意の名前を入力
  4. 「新しい名前」ダイアログの「OK」ボタンをクリック

上記の手順で、VLOOKUP関数で検索したい表すべてに名前を定義しちゃいましょう。

ちなみに、「名前の定義」の設定方法はいろいろパターンがありますが、もっと詳細を知りたい方はこの記事もどうぞ→こちら

組み合わせるとはどういうことか？

組み合わせるとは、INDIRECT関数をVLOOKUP関数の引数「範囲」へ入れるわけです。

もちろん、INDIRECT関数の引数「参照文字列」へ定義した名前が文字列として入っているセルを参照させます。

これで、INDIRECT関数が参照しているセルの値になっている名前を条件にして、VLOOKUP関数の引数「範囲」が自動的に切り替わるわけですね。

サンプルファイルで練習しよう！

では、実際に今回のVLOOKUP関数とINDIRECT関数・「名前の定義」機能の組み合わせテクニックを使ってみましょう！

サンプルの条件

今回の題材は次のとおりです。

「商品（会員）」と「商品（非会員）」の2つのシートそれぞれにくだものの名称・単価が一覧表になっています。（会員は非会員の80%の単価）

この2つのシートにそれぞれ記載のある「単価」を、別シート「台帳」のD列の2行目以降に転記したいとします。

この「台帳」シートのB列には”会員”か”非会員”のいずれかの文字列がすでに入っていて、この値を元に「商品（会員）」と「商品（非会員）」の2つのシートのどちらを参照するか切り替える条件にします。

また、C列には「くだもの」の名称がすでに入っているので、これをVLOOKUP関数のキーワードにしていきましょう。

実際に操作しよう！①

可能であれば、以下のサンプルファイルをダウンロードして実際に操作してみてください。

サンプルファイル_VLOOKUP関数_範囲③
※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
（上記リンクから登録フォームへ遷移します）

ファイルを開いたら次の手順を実施してください。

  1. 「台帳」シートを選択
  2. D2セルへ「=VLOOKUP($C2,INDIRECT($B2),2,0)」を入力
    ※赤字の部分をコピーして貼り付けてください。
  3. D2セルをコピー
  4. D3～D6へ貼り付け（ペースト）

上記手順を行った結果は「台帳 (関数あり)」シートにありますので、このシートの内容と実際に操作した結果が同じなっていればOKです。

「台帳」シートのB列の「会員/非会員」はドロップダウンリストになっていますので、今入っている値を変更してみて、きちんとVLOOKUP関数の戻り値（返り値）が切り替わるかも確認してみてくださいね。

実際に操作しよう！②

せっかくなので、「名前の定義」機能も実際に操作してみましょう！

サンプルファイルの「商品 （定義前)」シートの表を使って名前を定義してみてください。

手順は上記の「名前の定義の手順」の箇所か、こちらの記事を参考にしてくださいね。

ポイントの解説

最後に今回のテクニックの4つのポイントについて解説していきましょう。

【POINT1】主キーの設定

このテクニックに限らずVLOOKUP関数を使う際のマストなポイントです。

こちらは検索を行う対象の表に「主キー」が入ったものを準備しておきましょう。

この「主キー」とは、データを固有のものと識別するための値であり、かつ一意（他データと重複していない）である必要があります。

今回の例でいえば、”出席番号”が該当しますね。

こういった「主キー」があると、VLOOKUP関数で意図しない戻り値（返り値）になることを防ぐことができます。

【POINT2】VLOOKUP関数で検索したい表へ「名前を定義」を設定

あらかじめVLOOKUP関数で検索したい表に対して名前を定義しておきましょう。

今回でいえば、「商品（会員）」シートのA1:B6のセル範囲には「会員」という名前を、「商品（非会員）」シートのA1:B6のセル範囲には「非会員」という名前をそれぞれ定義しています。

定義の仕方については、本記事の「名前の定義」の手順をご参照ください。

なお、ここで指定した名前が、INDIRECT関数の引数「参照文字列」で指定することになります。

【POINT3】VLOOKUP関数側の表へ定義した名前のドロップダウンリストの設定＆追加

続いてのポイントですが、そもそもPOINT2で定義した名前をINDIRECT関数で参照しなければ、今回のテクニックは成立しません。

よって、INDIRECT関数で参照できるように、VLOOKUP関数をセットする表へ定義した名前を入力するセルを用意しておくと良いです。

今回でいえば、「台帳」シートのB列へ”会員”か”非会員”のどちらなのかを入力する欄を設けています。

ちなみに、定義した名前は誤入力していると、当然INDIRECT関数もVLOOKUP関数もエラーになりますので、入力規則のリスト機能でドロップダウンリストにしておくと良いですよ。

入力規則について詳細を知りたい方はこの記事もご参照ください。→こちら

【POINT4】絶対参照・相対参照の設定

これは、数式を扱う上で基礎中の基礎ですが、コピペを一括で行うために必要です。

今回セル参照しているのはVLOOKUP関数とINDIRECT関数を合わせて2つの引数ですが、それぞれ以下の意図があって絶対参照・相対参照を設定しています。

すべて「台帳」シートのD2セルをもとに説明していきますね。

VLOOKUP関数－引数「検索値」

今回は「$C2」と列のみ絶対参照にしていますが、行部分が相対参照であれば列は絶対参照でなくともOKです。

これは、D2セルのVLOOKUP関数を下方向にしかコピペしていかないため、行は固定しないようにするわけです。

INDIRECT関数－引数「参照文字列」

上記のVLOOKUP関数の引数「検索値」と理屈はいっしょです。

今回は「$B2」と列のみ絶対参照にしていますが、行部分が相対参照であれば列は絶対参照でなくともOKです。

これは、D2セルのVLOOKUP関数を下方向にしかコピペしていかないため、行は固定しないようにするわけです。

絶対参照・相対参照のまとめ

上記の説明は実際にワークシート上にさらに複数行のVLOOKUP関数が増えるとわかりやすいです。

さいごに

今回のテクニックはVLOOKUP関数で検索したい表が分かれていればいるほどに楽になるものです。

INDIRECT関数自体はなかなかマニアックですが、ワークシート上にある文字列を生かしてセル範囲を切り替えることができるので、なるべく自動化したい方は必修項目ですね。

なお、その他のVLOOKUP関数関連の役立つテクニックについて、私の書籍で紹介しているので、こちらもご参考にしていただければと思います。

▼VLOOKUP関数を基本から応用まで体系的に学びたい方向け

エクセル関数をゼロから極める本【VLOOKUP関数編】[Kindle版]

posted with ヨメレバ
森田 貢士 2017-03-11
Kindle
Amazon[書籍版]

▼VLOOKUP関数の応用テクニックに加えて、その他IF関数なども学びたい方向け

すごい! 関数 ~作業効率を劇的に変えるExcelの使い方~

posted with ヨメレバ
森田貢士 秀和システム 2016-09-27
Amazon
Kindle
楽天ブックス

ご参考になれば幸いですm(_ _)m

森田森田

INDIRECT関数は地味に重宝している関数です。今回のようなVLOOKUP関数の応用にも使えますし、入力規則を階層化するなんて難易度が高いことにも応用が利きます。
今回のテクニックをきっかけに日常使いができるようになることをおすすめします。