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

シェアする

  • このエントリーをはてなブックマークに追加

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

スポンサーリンク
自動サイズレスボンシブ

VLOOKUP関数の基本的な動き

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


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

スクリーンショット 2015 06 10 20 55 26

VLOOKUP関数は、まず、


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

スクリーンショット 2015 06 10 21 02 06


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

スクリーンショット 2015 06 10 21 19 02


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


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


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


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

スクリーンショット 2015 06 10 21 43 28


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


VLOOKUP関数で入力するのは、


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


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


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


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


この4つです。


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

スクリーンショット 2015 06 10 21 59 19


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

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

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


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

スクリーンショット 2015 06 10 22 08 01


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


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


表を1つにすること


です。


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


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


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


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

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

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


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


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



①見つけたいデータ

②表の範囲

③列番号

④検索方法


②の「表の範囲」です。


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

スクリーンショット 2015 06 10 22 24 15


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


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


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


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

スクリーンショット 2015 06 11 1 14 19

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

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


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

スクリーンショット 2015 06 10 22 54 16


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

スクリーンショット 2015 06 11 1 02 54


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


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

スクリーンショット 2015 06 11 1 07 38

VLOOKUP関数を入力する

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


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


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

スクリーンショット 2015 06 11 0 08 35


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

スクリーンショット 2015 06 11 1 10 22


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


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

スクリーンショット 2015 06 11 0 14 58


VLOOKUP関数の入力内容は、


①見つけたいデータ

②表の範囲

③列番号

④検索方法


この4つでした。


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


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


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


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


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


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


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


④検索方法は、0

スクリーンショット 2015 06 11 0 29 04


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

スクリーンショット 2015 06 11 1 24 42
(「(マスコットの)名前」のセルの場合)


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


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

スクリーンショット 2015 06 11 0 49 42

スクリーンショット 2015 06 11 1 26 12


こうなります。


実際に入力した結果は、

スクリーンショット 2015 06 11 1 27 23


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

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

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

スクリーンショット 2015 06 11 1 26 12


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


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


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


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


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


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


INDIRECT関数は、

スクリーンショット 2015 06 11 1 50 37


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


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

スクリーンショット 2015 06 11 1 55 15

スクリーンショット 2015 06 11 1 55 24


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


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

スクリーンショット 2015 06 11 1 59 22

スクリーンショット 2015 06 11 1 59 42


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


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

スクリーンショット 2015 06 11 2 05 53
(大人気!)

スクリーンショット 2015 06 11 2 10 23
(J1クラブのマスコットとしては物足りないものの、まずまず)

スクリーンショット 2015 06 11 2 12 35
(若干地味だが、パフォーマンスは上々で、上位進出も夢じゃない。これからだ!)

スクリーンショット 2015 06 11 2 17 36
(見た目が怖すぎて、子供が必ず泣き出す始末。”なまはげ”と同類とあってはこの順位も致し方なし。現実を見よう!)


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

まとめ

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

おまけ

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

スポンサーリンク
自動サイズレスボンシブ
自動サイズレスボンシブ

シェアする

  • このエントリーをはてなブックマークに追加

フォローする