実務で使えるExcel入門セミナー VLOOKUP関数で複数の表からデータを抽出する方法です

Excelで作成された表の中から、条件に合うデータを抽出するのに便利なのが、VLOOKUP関数。複数の表からデータを抽出する方法を考えます。

スポンサーリンク

VLOOKUP関数の基本的な動き

まずは、VLOOKUP関数で何ができるかをおさらいしておきましょう。

 
次のような表を考えます。

 

 

VLOOKUP関数は、まず、

 
見つけたいデータ」を指定すると、表の1番左の列に検索して、該当するデータがないかを探します。

 

 
「見つけたいデータ」が見つかったら、1番左の列から数えて、何列目にあたるかを指定すると、指定した列のセルのデータを表示します。

 

 
覚えていただきたいのは、

 
① 表の1番左の列を検索して、「見つけたいデータ」を探す

 
②「見つけたいデータ」が見つかったら、指定した数だけ列を右に移動して、データを表示

 
という関数の大きな動きです。

 

 
この動きをつかんでおくと、VLOOKUP関数で入力が必要な項目も理解しやすくなります。

 
VLOOKUP関数で入力するのは、

 
見つけたいデータ:(例)B3 (「川崎フロンターレ」と入力したセル)

 
表の範囲:(例)$B$6:$F$24 (表をセル範囲で指定)

 
列番号:(例)3 (1番左の列から数えて、何列目を表示させるか)

 
検索方法:(例)0(=完全に一致する場合以外はエラーにする)

 
この4つです。

 
上の(例)で示した内容を入力すると、

 

 
このように、正しい内容を表示させることができました。

 

 

 

「VLOOKUP関数を使って、複数の表の中から必要なデータを表示させる」ことを考える前に

VLOOKUP関数の基本的な中身を確認してきましたが、それは、1つの表の中から、必要なデータを表示する方法でした。

 
では、次のように表が複数に分かれている場合、どうすれば良いか考えてみましょう。

 

 
急に難しく感じるかもしれません。

 
ですが、最優先すべきことは決まっています。 
それは、

 
表を1つにすること

 
です。

 
Excelを使っていて、難しい問題が出てきたとき、関数やマクロを使って何とかしようと考えてしまいますが、それは順番が逆です。

 
データの方を使いやすい形に整えてシンプルな関数やマクロを使うことを考える方が、簡単に問題を解決できるケースが多いのです。

 
従って、「VLOOKUP関数で複数の表からデータを抽出する」ことを考えるよりも、まずは、データを整理して1つの表の中からデータを抽出する形にできないかを考えましょう。

 
それができない場合に、次の手段を考えます。

 

 

 

VLOOKUP関数を使って、複数の表の中から必要なデータを表示させる

それでも、VLOOKUP関数で、複数の表から必要なデータを表示させなければいけないこともあります。

 
その方法を考えてみましょう。

 
VLOOKUP関数の4つの入力項目の中で、ポイントになるのは、

 
 
①見つけたいデータ
 
②表の範囲
 
③列番号
 
④検索方法

 
②の「表の範囲」です。

 
先ほどの例を思い出していただきたいのですが、表の範囲を指定する際には、

 

 
「$B$6:$F$24」とセル番地で指定しました。

 
この指定の方法を変えます。

 
それぞれの表に「名前をつける」ことで、「表の範囲」を「名前で指定します。

 
そうすることで、VLOOKUP関数の検索対象とする表を、切り替えられるようにするのです。

 

 

 

「名前の定義」で表に名前をつける

表に名前をつけましょう。 
「名前の定義」を使います。

 
まず、名前をつけたいセルの範囲をドラッグして反転させます。

 

 
リボンの下にある、「名前ボックス」にカーソルを合わせて、名付けたい名前(ここでは「J1リーグ」)を入力して「Enterキー」をタッチ。

 

 
表に名前をつけることができました。 
これで、VLOOKUP関数でも、セル番地でなく名前(「J1リーグ」)で表の範囲を指定することができます。

 
同じ方法で、右の表にも名前をつければ、完了です。

 

 

 

VLOOKUP関数を入力する

2つの表に名前をつけることができたので、あとは、VLOOKUP関数を入力する際に、「②表の範囲」を名前で指定すればOKです。

 
具体的に入力内容を考えましょう。 
次のような表を完成させることを考えます。

 

 
最初に、クラブ名をリストから選ぶと、

 

 
それに連動してカテゴリーが表示されます。

 

 
※この2つは、ドロップダウンリストとVLOOKUP関数で処理していますが、本題から外れてしまうので、ここでは割愛します。

 
クラブ名とカテゴリーの情報を元にして、VLOOKUP関数を使って、「マスコットの名前」「順位」「ポジション」を表示させる表です。

 

 
VLOOKUP関数の入力内容は、

 
①見つけたいデータ
 
②表の範囲
 
③列番号
 
④検索方法

 
この4つでした。

 
さらに、VLOOKUP関数の動きは、

 
(1) 表の1番左の列を検索して、「見つけたいデータ」を探す

 
(2)「見つけたいデータ」が見つかったら、指定した数だけ列を右に移動して、データを表示

 
だったので、対応するものを見ていくと、

 
①見つけたいデータは、表の1番左の列にある「クラブ」

 
②表の範囲は、表に名付けた「名前」(=J1リーグかJ2リーグ)

 
③列番号は、マスコットの名前が、表の左端の列から3列目、順位が4列目、ポジションが5列目なので、 
  名前のセル:3 
  順位のセル:4 
  ポジションのセル:5

 
④検索方法は、0

 

 
このようになります。 
ですので、VLOOKUP関数は、

 

 
(「(マスコットの)名前」のセルの場合)

 
こうなりますが、それぞれの要素を直接入力すると、条件が変わる度に、いちいち書き換えなければいけないので、

 
各要素が入力されているセルに置き換えて、関数を作り直します。

 

 

 
こうなります。

 
実際に入力した結果は、

 

 
「値が数式または関数に対して無効です。」とのエラーメッセージが出てしまいました。

 

 

INDIRECT関数で、名前を「文字列」から「セル範囲」のデータに変換する

入力したVLOOKUP関数をもう一度確認しておくと、

 

 
こうなっているのですが、この中には、関数で使えないデータが含まれています。

 
それは、「②表の範囲」を示す、「C47」=「J1リーグ」のところ。

 
「J1リーグ」をExcel上のデータの種類で分類すると、数値でも、セル範囲でもなく、文字列にあたります。

 
文字列は、そのまま、関数の中で使うことはできません。

 
関数の中で、文字列を「名前の定義によって、名付けられたセル範囲」として認識させるためには、文字列をセル範囲のデータに変換しておかなければいけません

 
その役割を果たしてくれるのが、INDIRECT関数です。

 
INDIRECT関数は、

 

 
このように、INDIRECTに続けて、セル範囲を表す「名前」を入力することで、文字列からセル範囲のデータへと変換してくれます。

 
先ほどの、VLOOKUP関数の式を、INDIRECT関数を使った式に修正します。 
すると、

 

 

 
エラー表示は消え、正しい内容が表示されました。

 
さらに、このVLOOKUP関数を、「順位」「ポジション」のセルにも入力すると、

 

 

 
同様に正しく表示されることがわかりました。

 

 
念のため、他のクラブについても見ておくと、

 

 
(大人気!)

 

 
(J1クラブのマスコットとしては物足りないものの、まずまず)

 

 
(若干地味だが、パフォーマンスは上々で、上位進出も夢じゃない。これからだ!)

 

 
(見た目が怖すぎて、子供が必ず泣き出す始末。”なまはげ”と同類とあってはこの順位も致し方なし。現実を見よう!)

 
「J1リーグ」「J2リーグ」の2つに表が分かれていますが、VLOOKUP関数によって、正しくデータが表示されていることがわかりました。

 

 

 

まとめ

VLOOKUP関数で、2つの表からデータを抽出する場合は、「名前の定義」で名前をつけ、INDIRECT関数を組み込むことで、名前によって、表を切り替えられるようにしましょう。 
ただし、優先すべきは、2つの表を1つにまとめることができないかを考えること。 
Excelでは、関数やマクロでの工夫よりも、データの整理を優先させる方が、効率よく課題を解決できる可能性が高いです。
 

おまけ

今日の午後は、池袋のジュンク堂書店までラン。 
気温は高いですが、湿度が低いので気持ち良く走れました。