Excelで作成された表の中から、条件に合うデータを抽出するのに便利なのが、VLOOKUP関数。複数の表からデータを抽出する方法を考えます。
VLOOKUP関数の基本的な動き
まずは、VLOOKUP関数で何ができるかをおさらいしておきましょう。
次のような表を考えます。
VLOOKUP関数は、まず、
「見つけたいデータ」を指定すると、表の1番左の列を縦に検索して、該当するデータがないかを探します。
「見つけたいデータ」が見つかったら、1番左の列から数えて、何列目にあたるかを指定すると、指定した列のセルのデータを表示します。
覚えていただきたいのは、
① 表の1番左の列を検索して、「見つけたいデータ」を探す
②「見つけたいデータ」が見つかったら、指定した数だけ列を右に移動して、データを表示
という関数の大きな動きです。
この動きをつかんでおくと、VLOOKUP関数で入力が必要な項目も理解しやすくなります。
VLOOKUP関数で入力するのは、
①見つけたいデータ：（例）B3 （「川崎フロンターレ」と入力したセル）
②表の範囲：（例）$B$6:$F$24 （表をセル範囲で指定）
③列番号：（例）３ （1番左の列から数えて、何列目を表示させるか）
④検索方法：（例）０（＝完全に一致する場合以外はエラーにする）
この４つです。
上の（例）で示した内容を入力すると、
このように、正しい内容を表示させることができました。
「VLOOKUP関数を使って、複数の表の中から必要なデータを表示させる」ことを考える前に
VLOOKUP関数の基本的な中身を確認してきましたが、それは、1つの表の中から、必要なデータを表示する方法でした。
では、次のように表が複数に分かれている場合、どうすれば良いか考えてみましょう。
急に難しく感じるかもしれません。
ですが、最優先すべきことは決まっています。
それは、
「表を１つにすること」
です。
Excelを使っていて、難しい問題が出てきたとき、関数やマクロを使って何とかしようと考えてしまいますが、それは順番が逆です。
データの方を使いやすい形に整えて、シンプルな関数やマクロを使うことを考える方が、簡単に問題を解決できるケースが多いのです。
従って、「VLOOKUP関数で複数の表からデータを抽出する」ことを考えるよりも、まずは、データを整理して１つの表の中からデータを抽出する形にできないかを考えましょう。
それができない場合に、次の手段を考えます。
VLOOKUP関数を使って、複数の表の中から必要なデータを表示させる
それでも、VLOOKUP関数で、複数の表から必要なデータを表示させなければいけないこともあります。
その方法を考えてみましょう。
VLOOKUP関数の４つの入力項目の中で、ポイントになるのは、
①見つけたいデータ
②表の範囲
③列番号
④検索方法
②の「表の範囲」です。
先ほどの例を思い出していただきたいのですが、表の範囲を指定する際には、
「$B$6：$F$24」とセル番地で指定しました。
この指定の方法を変えます。
それぞれの表に「名前をつける」ことで、「表の範囲」を「名前」で指定します。
そうすることで、VLOOKUP関数の検索対象とする表を、切り替えられるようにするのです。
「名前の定義」で表に名前をつける
表に名前をつけましょう。
「名前の定義」を使います。
まず、名前をつけたいセルの範囲をドラッグして反転させます。
リボンの下にある、「名前ボックス」にカーソルを合わせて、名付けたい名前（ここでは「J1リーグ」）を入力して「Enterキー」をタッチ。
表に名前をつけることができました。
これで、VLOOKUP関数でも、セル番地でなく名前（「J1リーグ」）で表の範囲を指定することができます。
同じ方法で、右の表にも名前をつければ、完了です。
VLOOKUP関数を入力する
2つの表に名前をつけることができたので、あとは、VLOOKUP関数を入力する際に、「②表の範囲」を名前で指定すればOKです。
具体的に入力内容を考えましょう。
次のような表を完成させることを考えます。
最初に、クラブ名をリストから選ぶと、
それに連動してカテゴリーが表示されます。
※この２つは、ドロップダウンリストとVLOOKUP関数で処理していますが、本題から外れてしまうので、ここでは割愛します。
クラブ名とカテゴリーの情報を元にして、VLOOKUP関数を使って、「マスコットの名前」「順位」「ポジション」を表示させる表です。
VLOOKUP関数の入力内容は、
①見つけたいデータ
②表の範囲
③列番号
④検索方法
この４つでした。
さらに、VLOOKUP関数の動きは、
（１） 表の1番左の列を検索して、「見つけたいデータ」を探す
（２）「見つけたいデータ」が見つかったら、指定した数だけ列を右に移動して、データを表示
だったので、対応するものを見ていくと、
①見つけたいデータは、表の1番左の列にある「クラブ」
②表の範囲は、表に名付けた「名前」（＝J1リーグかJ2リーグ）
③列番号は、マスコットの名前が、表の左端の列から3列目、順位が4列目、ポジションが5列目なので、
名前のセル：３
順位のセル：４
ポジションのセル：５
④検索方法は、０
このようになります。
ですので、VLOOKUP関数は、
（「（マスコットの）名前」のセルの場合）
こうなりますが、それぞれの要素を直接入力すると、条件が変わる度に、いちいち書き換えなければいけないので、
各要素が入力されているセルに置き換えて、関数を作り直します。
こうなります。
実際に入力した結果は、
「値が数式または関数に対して無効です。」とのエラーメッセージが出てしまいました。
INDIRECT関数で、名前を「文字列」から「セル範囲」のデータに変換する
入力したVLOOKUP関数をもう一度確認しておくと、
こうなっているのですが、この中には、関数で使えないデータが含まれています。
それは、「②表の範囲」を示す、「C47」＝「J1リーグ」のところ。
「J1リーグ」をExcel上のデータの種類で分類すると、数値でも、セル範囲でもなく、文字列にあたります。
文字列は、そのまま、関数の中で使うことはできません。
関数の中で、文字列を「名前の定義によって、名付けられたセル範囲」として認識させるためには、文字列をセル範囲のデータに変換しておかなければいけません。
その役割を果たしてくれるのが、INDIRECT関数です。
INDIRECT関数は、
このように、INDIRECTに続けて、セル範囲を表す「名前」を入力することで、文字列からセル範囲のデータへと変換してくれます。
先ほどの、VLOOKUP関数の式を、INDIRECT関数を使った式に修正します。
すると、
エラー表示は消え、正しい内容が表示されました。
さらに、このVLOOKUP関数を、「順位」「ポジション」のセルにも入力すると、
同様に正しく表示されることがわかりました。
念のため、他のクラブについても見ておくと、
（大人気！）
（J1クラブのマスコットとしては物足りないものの、まずまず）
（若干地味だが、パフォーマンスは上々で、上位進出も夢じゃない。これからだ！）
（見た目が怖すぎて、子供が必ず泣き出す始末。”なまはげ”と同類とあってはこの順位も致し方なし。現実を見よう！）
「J1リーグ」「J2リーグ」の２つに表が分かれていますが、VLOOKUP関数によって、正しくデータが表示されていることがわかりました。
まとめ
VLOOKUP関数で、２つの表からデータを抽出する場合は、「名前の定義」で名前をつけ、INDIRECT関数を組み込むことで、名前によって、表を切り替えられるようにしましょう。
ただし、優先すべきは、２つの表を１つにまとめることができないかを考えること。
Excelでは、関数やマクロでの工夫よりも、データの整理を優先させる方が、効率よく課題を解決できる可能性が高いです。
おまけ
今日の午後は、池袋のジュンク堂書店までラン。
気温は高いですが、湿度が低いので気持ち良く走れました。