VLOOKUP関数範囲可変テクニック②

先日ご紹介したVLOOKUP関数の可変性を持たせる組み合わせの第五弾です。

VLOOKUP関数の可変性を持たせる組み合わせまとめ | Excelを制する者は人生を制す ~No Excel No Life~

今回は「範囲」を可変にするテクニックの中の一つ、「INDIRECT関数との組み合わせ」について解説していきたいと思います(・∀・)

INDIRECT関数と組み合わせる方法

VLOOKUP関数を使いこなしていくと、どんどんと応用範囲を広げたくなります。

今回のINDIRECT関数はVLOOKUP関数の可能性を広げてくれますよ(・∀・)

主な使いどころ・シチュエーション、メリットなど

今回のINDIRECT関数との組み合わせの使いどころやメリットの一例は以下の通りです。

  • VLOOKUP関数の参照範囲を条件によって変更することが可能
  • VLOOKUP関数の参照範囲を「名前の定義」別、シート別、ブック別に切り替えることが可能

サンプル前提条件

  • 2つの価格表(B4:C9とF4:F9)を参照範囲として、条件に応じて切り替えたい
  • B列・E列の席ランクがVLOOKUP関数の検索キー(C13セルで指定する)
  • VLOOKUP関数の参照範囲はINDIRECT関数で切り替える
  • B13セルで2つの価格表(B4:C9とF4:F9)の設定した「名前の定義」を選択する(INDIRECT関数の「参照文字列」として指定)
  • VLOOKUP関数の「列番号」はそれぞれ価格表の2列目のため「2」、「検索方法」は完全一致条件をしたいため「0」を指定
  • B13セルの「名前の定義」の表の中からC13セルの「検索値」に合致する金額をD13セルに表示したい

INDIRECT関数は、文字列で指定されるセル参照を返す関数です。
【関数の構成】INDIRECT(参照文字列,[参照形式])

VLOOKUP関数+INDIRECT関数

サンプル処理手順

  1. B4:C9に「平日」、E4:F9に「土日祝日」とそれぞれ「名前の定義」を設定(範囲選択後、マウス右クリック→「名前の定義」→「名前」に左記文言入力→「OK」)
  2. B13セルに1.で設定した「名前の定義」を入力(入力規則で設定しておくと良い)
  3. D13セルにVLOOKUP関数を組む(オートフィルなどしないためすべて絶対参照でOK)
  4. C13セルを検索値とする
  5. VLOOKUP関数の参照範囲の箇所でINDIRECT関数を組む(,[カンマ]の記述漏れに注意!)
  6. B13セルを参照文字列でOFFSET関数の記述をする(オートフィルなどしないためすべて絶対参照でOK)
  7. VLOOKUP関数の列番号は「2」、検索方法は「0」として「)[かっことじ]」で終了

応用

INDIRECT関数で別シートを参照範囲に切り替えることも可能です。

VLOOKUP関数+INDIRECT関数応用(別シート参照)

「=VLOOKUP($A3,INDIRECT(“‘”&C$2&”‘!$A$4:$B$8″),2,0)」

赤字のところはシート名になります。この部分をシート名を表すセルを指定して可変性を保ちましょう。

別ブックのデータもINDIRECT関数を通して参照することが可能です。それぞれ赤字のところに該当の文字列が入ったセル番地を指定してください(・∀・)

  • 【別ブック名をセル参照したい場合】
    「=VLOOKUP(参照値,INDIRECT(“[“&ブック名&”.xlsx]シート名!範囲”),列番号,検索方法)」
  • 【別ブックのシート名をセル参照したい場合】
    「=VLOOKUP(参照値,INDIRECT(“[ブック名.xlsx]”&シート名&”!範囲”),列番号,検索方法)」

上記拡張子(.xlsx)は参照したいファイルの拡張子に変更してくださいね。

まとめ

INDIRECT関数との組み合わせを覚えると、もともと他の人がつくった表からデータを引っ張りたい場合にも柔軟に対応することができます。

もちろん、別ブック・別シート問わず条件に応じて引用したいテーブルを変更することができるため、データベースをつくりこみたい人にも有用ですね(・∀・)

ひとまず、VLOOKUP関数との組み合わせテクニックは今のところ以上となります。(今後思いついたり、別の方法を知った場合、追加で記事にするかもですが)

他にも有効な活用テクニックをご存知の方がいれば、ご教示いただけると嬉しく思います(*゚▽゚*)

この記事が気に入ったら
いいね ! しよう