【関数の組み合わせ】VLOOKUP関数とINDIRECT関数で参照先を変える技!

5 min 5,149 views
エクセル初心者

エクセル初心者

VLOOKUP関数とINDIRECT関数を同時に使っている数式を見たことあるけど、どういう時に使うのかな?

参照先を変えてデータを検索したいときにVLOOKUP関数とINDIRECT関数を使用するよ。とても便利な組み合わせだから、これから詳しく解説するね。

Dr.オフィス

Dr.オフィス

今回は、VLOOKUP関数とINDIRECT関数を使って参照先を変える方法を解説します。

参照先を変える方法といっても、ピンときませんよね。

では、VLOOKUP関数を使って商品を検索をする場合、[範囲]を指定しますが、この[範囲]に指定する参照先が、食品一覧、飲料一覧、日用品一覧、というようにカテゴリー別に作成された複数の一覧だったら、どうしますか。

おそらく今までは、食品一覧、飲料一覧、日用品一覧が1つにまとまった一覧をVLOOKUP関数の[範囲]に指定していたと思います。

しかし今回は、カテゴリー別に作成された複数の一覧を同時に参照先に指定しなければ商品が検索できません。

このようなときに、VLOOKUP関数とINDIRECT関数を使うと、参照先を変えてデータを検索することができます。

VLOOKUP関数とINDIRECT関数で参照先を変える簡単ステップ
  1. VLOOKUP関数の書式 =VLOOKUP(検索値,範囲,列番号,[検索の型])を応用する
  2. 参照先になるセル範囲に名前を付ける
  3. VLOOKUP関数の[範囲]にINDIRECT関数を使って、上記の名前を指定する

以上の3ステップで参照先を変えることができます。

他にも関数を組み合わせてデータを抽出するクロス抽出があります。

クロス抽出には、INDEX関数とMATCH関数を使って解説しています。
詳しくは、【エクセルのクロス抽出】INDEX関数とMATCH関数の組み合わせを参考にしてみてください。

次項より、VLOOKUP関数について詳しく解説するので、ぜひ読み進めてください。

※本記事は『OS:Windows10』画像は『Excelのバージョン:2016』を使用しています。

VLOOKUP関数とは

エクセルでデータの検索に欠かせない関数が、VLOOKUP関数ですよね。
ここで、少しVLOOKUP関数を思い出してみましょう。

VLOOKUP関数
VLOOKUP関数

↑VLOOKUP関数の書式は、[=VLOOKUP(検索値,範囲,列番号,[検索の型]]になります。

引数[検索値]をもとに指定されたセル範囲の1列目で特定の値を検索し、その行の引数に[列番号]指定した列にあるセルの値を返します。

これがVLOOKUP関数でしたね。

ちょっと自信が無いという場合は、エクセル【VLOOKUP関数】で別シートから簡単に条件にあう値を参照しよう!で詳しく解説しているので、参考にしてみてください。

次項より、INDIRECT関数について詳しく解説するので、ぜひ読み進めてください。

INDIRECT関数とは

INDIRECT関数とは、セルに入力されている文字列を介して、間接的にセルを指定する関数です。

  • INDIRECT関数の書式
    =INDIRECT(参照文字列,[参照形式])
  • 参照文字列は必須
    文字列は『”』(ダブルクォーテーショ)で囲んで指定
  • 参照形式は省略できる
    TRUE または省略:A1 形式のセル参照
    FALSE:R1C1 形式のセル参照

それでは、実際にINDIRECT関数を使って名前検索をしてみましょう。

セルC3に[=INDIRECT(B3)]を入力
セルC3に[=INDIRECT(B3)]を入力

↑下記の順に設定します。

  1. セルB3に[E7]を入力
  2. セルC3に[=INDIRECT(B3)]を入力
セルC3に[=INDIRECT(B3)]を入力した結果
セルC3に[=INDIRECT(B3)]を入力した結果

↑セルC3に『小野 弥生』が表示されました。

どのような動きをしているかというと、INDIRECT関数によってセルB3に入力された値をセル番号として認識し、そのセル番号に入力されている内容をセルC3に返したことになります。

では、ここでINDIRECT関数に指定している『セルB3の値』を削除するとどうなるか、確認してみましょう。

セルB3の値を削除
セルB3の値を削除

↑セルC3の数式は、[=INDIRECT(B3)]と同じですが、セルB3にセル番号と認識できる値が入力されていないためエラーとなりました。

次に、セルC3の数式を、[=INDIRECT(“E7”)]に変更してみましょう。E7をダブルクォーテーションで囲んでいることに注目してくださいね。

セルC3に[=INDIRECT("E7")]と入力した結果
セルC3に[=INDIRECT(“E7”)]と入力した結果

↑[=INDIRECT(“E7”)]と入力したため、セルC3に『小野 弥生』が返されました。

このように、[参照文字列]にセル参照を表す文字列を指定する(セル番号やセルの名前を直接入力する)場合は、『”』(ダブルクォーテーショ)で囲んで指定します。

他にも文字列を結合し、シートを指定することもできます。
INDIRECT関数を使って以下の『会議室A』『会議室B』『会議室C』という3種類のシートを指定し、それぞれ集計されている利用人数を『集計』シートに集約してみましょう。

3種類のシート
3種類のシート

↑それぞれの会議室の日付毎の利用人数の合計が、セルB8に集計されています。

『集計』シートのセルC3に[=INDIRECT(B3&"!B8")]を入力
『集計』シートのセルC3に[=INDIRECT(B3&”!B8″)]を入力

↑下記の順に設定します。

  1. 『集計』シートのセルC3を選択
  2. セルC3に[=INDIRECT(B3&”!B8″)]を入力
セルC3に[=INDIRECT(B3&"!B8")]を入力した結果
セルC3に[=INDIRECT(B3&”!B8″)]を入力した結果

↑シート『会議室A』のセルB8の値が表示されました。

セルB3の値とシート名が同一のため、セルを参照し、文字列を結合することで、シート『会議室A』のセルB8を指定することができました。オートフィルでセルC5までコピーしましょう。

『集計』シートに集約した結果
『集計』シートに集約した結果

↑『会議室A』『会議室B』『会議室C』のセルB8の値を参照し、『集計』シートに集約することができましたね。

このようにINDIRECT関数は、[参照文字列]に文字列を指定でき、その文字列を『文字』ではなく、シート名やセル番号として認識します。

INDIRECT関数は、直接セルを指定せずに間接的にセルの参照をするので、使い方を理解するのには、少し時間がかかるかもしれませんが、使い方次第で作業範囲が広がるので、マスターしておきたい関数です。

[=A1]とセル参照する場合と、数式[=INDIRECT(“A1”)]や[=INDIRECT(A1)]とする場合の違いをしっかり理解しておこう!

Dr.オフィス

Dr.オフィス

次項では、VLOOKUP関数とINDIRECT関数で参照先を変更する方法を解説します。

VLOOKUP関数とINDIRECT関数で参照先を変更しよう

それでは、VLOOKUP関数とINDIRECT関数を使って参照先を変更できるように設定してみましょう。

参照先に名前を定義
参照先に名前を定義

INDIRECT関数は、参照先の名前を(文字列)範囲として指定できるため、使用する3つの参照先に下記の順で名前を付けます。

  1. セルA7からB9を選択
  2. [数式]を選択
  3. [名前の定義]を選択
[新しい名前]ダイアログボックスより設定
[新しい名前]ダイアログボックスより設定

↑[新しい名前]ダイアログボックスが表示されたら下記の順に設定しましょう。

  1. [名前]-[食料品]を入力
  2. [OK]を選択

同様に残り2つの参照先にも下記の内容で名前をつけます。

[新しい名前]ダイアログボックスより設定
[新しい名前]ダイアログボックスより設定

↑セルD7からE9には、『飲料品』と名前を付けます。

[新しい名前]ダイアログボックスより設定
[新しい名前]ダイアログボックスより設定

↑セルG7からH9には、『日用品』と名前を付けます。

名前ボックスを確認
名前ボックスを確認

↑セルやセル範囲に名前をつけると、画面左上にある名前ボックスに一覧が表示されます。それぞれ名前を選択し、正しく参照範囲が反映されるか確認しておきましょう。

また、名前ボックスに直接名前を入力して、セル範囲に名前を付けることもできます。

VLOOKUP関数とINDIRECT関数を設定
VLOOKUP関数とINDIRECT関数を設定

↑参照先に名前を付け終わったら下記の順に設定します。

  1. セルA2に[食料品]を入力
  2. セルB2に[パン]を入力
  3. セルC2を選択
  4. セルC2に[=VLOOKUP(B2,INDIRECT(A2),2,FALSE)]を入力
名前の定義で指定した[食料品]を参照している
名前の定義で指定した[食料品]を参照している

↑セルC2に食料品のパンの価格が表示されましたね。

他の飲料品と日用品の品名も正しく検索されるか確認してみましょう。
今度は、セルA2に飲料品、セルB2にコーラを入力します。

名前の定義で指定した[飲料品]を参照している
名前の定義で指定した[飲料品]を参照している

↑セルC2に飲料品のコーラの価格が表示されましたね。

最後に、セルA2に日用品、セルB2にとシャンプーを入力します。

名前の定義で指定した[日用品]を参照している
名前の定義で指定した[日用品]を参照している

↑セルC2に日用品のシャンプーの価格が表示されましたね。

このように、[検索値]と[範囲]に応じて動的に参照先を変えて、データを検索することができます。

また、エクセルのIFERROR関数を使うと、値が見つからないときやその他エラーが起こる場合は、空白を返してくれます。
セルC2には、[=IFERROR(VLOOKUP(B2,INDIRECT(A2),2,FALSE),””) ]と入力しておくとスマートになりますよ。

VLOOKUP関数とINDIRECT関数を使って、ワンランク上のデータ検索をしよう!

VLOOKUP関数とINDIRECT関数を使うことによって、複数の参照先からデータを検索できるようになりましたね。

[検索値]と[範囲]に応じて参照先を切り替えることができるため、IF関数を使った複雑な条件式を考える必要もありません。また、数式もスマートになり見栄えも良くなります。

参照先が1つでもVLOOKUP関数とINDIRECT関数を使って設定しておくといいですよ。
後日発生するかもしれない、参照先が増えた時などのメンテナンスがしやすくなります。

VLOOKUP関数とINDIRECT関数を組み合せる方法のまとめ
  • 参照先になるセル範囲に名前を付ける
  • VLOOKUP関数とINDIRECT関数を使うと、簡単に参照先を切り替えることができる
  • VLOOKUP関数とINDIRECT関数を使うと、データ検索の数式が簡素化できる

これからは、VLOOKUP関数とINDIRECT関数を使って、ワンランク上のデータ検索を心がけましょう。

今回は、VLOOKUP関数とINDIRECT関数を使って、参照先を切り替える方法を解説しましたが、他にもINDEX関数とMATCH関数を使ってクロス抽出する方法があります。

クロス抽出については、【エクセルのクロス抽出】INDEX関数とMATCH関数の組み合わせで詳しく解説しているので、参考にしてみてください。

ExcelドクターがおすすめするExcel本はこちら

カテゴリー:

20万人以上が参考にしたエクセルドクターで最も人気の記事

エクセルで家計簿を作る方法

ワード初心者

ワード初心者

エクセルで本格的な家計簿を作ってみたいんだけど詳しく解説してる本とかがないんだよね・・・
徹底的に解説してほしいな!

エクセルで本格家計簿の作り方なら私に任せて!過去に20万人以上のユーザーが参考にした解説記事を紹介するね♪

Dr.オフィス

Dr.オフィス

エクセルを少し使えるようになってきたら、本格的な家計簿を作ってみるのがとてもおすすめです。

収支シートの出来上がりイメージ
収支シートの出来上がりイメージ

基本機能をしっかりと使って作るから、エクセルをマスターするのにも家計簿づくりはぴったり!

20万人以上が参考にしたエクセルでの本格的な家計簿の作り方はこちら↓↓

【エクセル家計簿の作り方】月ごとに入力して合計を反映させれば見やすくなる!

関連記事