Excel。VLOOKUP関数。その9
範囲の表が複数ある場合ってどうするの？
VLOOKUP関数＋名前の定義＋INDIRECT関数
VLOOKUP関数のテクニックもアレコレご紹介してきましたが、
企業研修や、お仕事をしている方から、よくご質問があるものがありまして、
それが、VLOOKUP関数の範囲。
その範囲の表が2つないし、複数あるのですが、VLOOKUP関数は使えますか？出来ますか？というもの。
出来ないことも、無いわけではないのですが、VLOOKUP関数だけでは、ちょっと出来ないので、
今回は、VLOOKUP関数にINDIRECT関数の力も借りて、紹介してみたいと思います。
現場レベル・実践では、ホント色んなケースがありますから、
こういうことも出来るという事だけでも覚えておくと、手駒が増えますので、
使わないなぁ～と思う方も多いとは思いますが、知っておいて損はない、テクニックです。
なお、これからご紹介するものは、以前紹介したものの、【ほぼ再録】です。
まず、下記の表があります。
VLOOKUP関数の範囲は、ジャンルが異なっても一つにまとまっていて、
なおかつ、管理番号が重複しないような表・リストならば、
ノーマルなVLOOKUP関数で検索することが出来るのですが、
今回のように、野菜という表と果物という表がそれぞれあって、
管理番号がそれぞれ同じものが使われていて、重複している状態なのです。
そして、どう処理したいのかというと、A2からの表のC列の商品名とD列の単価を、
それぞれ、野菜と果物の表の範囲から、B列の管理番号を元に、検索したいという動作です。
つまり、ジャンルの表の管理番号の商品名を検索する。という動作になります。
そして、このような場合、A列のジャンルというのがポイントになってきます。
さて、説明はこのぐらいにして、作業に入りましょう。
まず、この野菜と果物の表に、名前の定義をつかって、範囲に名前を設定しましょう。
設定の方法は、範囲選択をして、名前ボックスに範囲の名前を入力すればOKですね。
ちなみに、今回は、G3:I5に野菜、G10:I12に果物という名前を設定してあります。
これで準備完了。VLOOKUP関数を作っていきます。
まず、
ジャンルのA3に野菜と入力しておきます。
管理番号のB3に100と入力しておきます。
C3にVLOOKUP関数の数式を作成していきますので、
VLOOKUP関数のダイアログボックスを表示しましょう。
検索値には、B3を入力します。これは、通常のVLOOKUP関数と変わりません。
そして、変わるのが範囲です。
範囲が２つある。
そこで、ジャンルを使って見分けるようにしていくわけです。
では、どうのようにするのかというと、INDIRECT関数を使うことによって可能になります。
先ほど、野菜と果物という名前を範囲に設定しました。
以前にも紹介しましたが、VLOOKUP関数の範囲に名前を使えるので、
名前の定義で設定した範囲の名前を参照できれば、その範囲を参照することができます。
野菜と果物をイチイチ、関数の修正で直すわけにはいきません。
そこで、INDIRECT関数を使うと、その修正を行わなくても、切り替えることが出来るのです。
INDIRECT関数は、その情報（文字）をそのまま使うことが出来る関数なので、ジャンル。
すなわち、A3に入力された文字を、名前の定義をした範囲として、
VLOOKUP関数の範囲として使えるようになります。
それでは、VLOOKUP関数の範囲のボックスをクリックして、
INDIRECT関数を挿入してINDIRECT関数のダイアログボックスを表示します。
参照文字列は、A3をクリックして、OKボタンは押さずに、
数式バーのVLOOKUPという文字をクリックしましょう。
なぜOKボタンをクリックしてはいけないのか？
というと、まだVLOOKUP関数の数式自体が完成しておりませんので、エラーになってしまいます。
数式バーのVLOOKUPという文字をクリックすると、
VLOOKUP関数のダイアログボックスに戻れます。
列番号は2。
検索方法は、false。
これで、あとはOKボタンをクリックしましょう。
じゃがいも
と表示されましたね。数式は、
=VLOOKUP(B3,INDIRECT(A3),2,FALSE)
この数式を下方向にオートフィルハンドルを使って、コピーしてみましょう。
りんごと表示されましたね。ジャンルを果物から、野菜に変えてみましょう。
りんごが、じゃがいもに変わるのが確認できます。
このように、VLOOKUP関数+名前の定義+INDIRECT関数というテクニックを使うと、
複数の範囲からでも検索することが可能になります。
まぁ、余談ですが、A列のジャンルはイチイチ、入力するのが面倒ですので、
入力規則のリストを設定しておくと便利ですね。
当然、今回作成した数式。管理番号が空白だとエラーになってしまうので、
IF+VLOOKUP関数にするのが、本来の形です。
VLOOKUP関数は、とっても奥が深い関数ですので、色々と試してみましょう。
