スポンサーリンク

VLOOKUP関数は、「指定した範囲(表)の中から、検索値に対応する値を抽出する」関数。

ホントに便利な関数で、表が100行あろうが1000行あろうが一発で値を抽出できちゃいます。
使えるようになると「Excelやってるぜ!」感が出てきますよ。

「でも使い方がよくわからないし、ちょっと間違えたらエラーになっちゃいそうで怖い…。」
今回はそんなあなたも安心!
VLOOKUP関数の使い方をイチからみていきましょう。

VLOOKUP関数の構文とイメージ

ExcelのVLOOKUP関数の構文
構文はなんだか堅苦しくてわかりづらいですね。

でも大丈夫!
イメージと例をまじえて、ひとつずつ解説していきます。

今回使うイメージは、社員を管理する表。
そのなかから、ある社員番号の人のデータを抽出してみます。

ExcelのVLOOKUP関数の抽出イメージ
今回の目的は、「社員番号S000006」の人の「年齢」を抽出すること。

ではこれらの値はどのように設定されたものなのか。
このイメージを使って、ひとつずつ見ていきましょう。

VLOOKUP関数の入力方法

VLOOKUP関数は、関数のなかで4つの値を指定する必要があります。

構文は
=VLOOKUP(検索値, 範囲, 列番号, 検索の型)
でしたね。

ひとつめの「検索値」からみていきましょう。

検索値=検索したい値の指定

VLOOKUP関数の検索値
今回抽出するのは、「社員番号S000005」の人の「年齢」でしたね。
「S000005」というデータはB6セルにありますので、検索値はB6と指定します。

ここで検索値を直接"S000005"としてもかまいません。
その場合、「=VLOOKUP("S000005", B2:F12, 5, FALSE)」となります。
検索値が文字列(数値以外の値)の場合、「"」で囲むことを忘れずに。

次に「範囲」の指定方法を見ていきましょう。

検索する範囲の指定

次に、検索する表の範囲の指定です。
VLOOKUP関数の範囲
今回、B2~F35セルのため、B2:F35と指定しています。

「社員表はA1~F35セルじゃないの?」と思うかもしれません。
これには理由が2つあるんです。
特に1つめの理由は絶対に守らなければならないものです。

まず1つめの理由。
VLOOKUP関数で範囲を指定するときのお約束として、
「範囲の左端の列は検索値の列と同じにする」というものがあります。

どういうことかというと、検索値とする社員番号は、B列にありますよね。
この場合、B列を左端とする範囲を指定しなければならないんです。

つぎに2つめの理由。
1つめの理由から、「B1:F12」と範囲指定しても、実は大丈夫です。
検索値の列が左端、なおかつ抽出したい値が範囲に含まれていれば大丈夫。

ただ、1行目はタイトルの行になっていますので、ここに検索値=社員番号が含まれていることはありません。
そのような行はできれば指定範囲から除いたほうがスマートです。

まずは「範囲の左端の列は検索値の列と同じにする」ことを必ず意識して、慣れてきたら「余分な範囲は含まない」ことを意識してみましょう。

次に「列番号」の指定について見ていきましょう。

抽出したい列番号

「列番号」は、「検索値から見て、抽出したい値が何列目にあるか」を指定します。
検索値の列を1として、ひとつ右の列は2、そのまた右の列は3…といった具合です。

VLOOKUP関数の列番号
今回の例ですと、社員番号のB列からみて、抽出したい年齢のF列は5列目にあります。
よって、列番号は『5』と指定します。

ここで注意点がひとつ。
VLOOKUPは、検索値の右方向の値しか抽出できません。

逆に言えば、抽出したい値が右方向にあるとき、これ以上便利な関数はありません。
じゃあ左方向の値はどうやって抽出するかというと、「INDEX関数」「MATCH関数」を組み合わせて使うことで可能になります。
こちらの記事でまとめています。

[検索方法]は最初は「FALSE」で指定しよう

VLOOKUP関数の検索方法
さいごに「検索方法」ですが、最初は『FALSE』と指定するもの、と覚えておきましょう。
実際にはTRUEと指定する方法もあるのですが、FALSEにしておいてまず間違いはありません。

FALSEと指定すると「完全一致検索」、つまり検索値に一字一句、完全に一致する値を検索します。
検索方法をTRUEにすると、「近似値検索」(あいまい検索)という指定方法になり、結果も仕組みも一気にわかりづらくなります。

それでも知りたい!というアナタは【 ちょっと濃い話】第4引数をTRUEにしたほうが良い場合を読んでみてください。

VLOOKUP 演習問題

では1つ、演習問題を。
VLOOKUP関数の演習問題の表
こちらの社員表を使って、「No.が『3』の人の『年齢』を抽出」するVLOOKUP関数はどのようなものになるでしょうか。

上の例とは違って、検索値が「社員番号」ではなく「No.」です。
となると、範囲指定や列番号の指定もズレることになります。
落ち着いて考えてみてくださいね。

演習問題の解答

解答は、

=VLOOKUP(A4, A2:F12, 6, FALSE)

となります。

VLOOKUP関数の演習問題(解答)の表
=VLOOKUP(3, A2:F12, 6, FALSE)
としてもかまいません。

検索値がA列になっていること、それに合わせて範囲の左端もA列になっていること。
そして列番号も6となっていることがポイントですね。

【 豆知識 】VLOOKUP関数は超高速!100000行でも0.2秒

ここでちょっと豆知識。

「VLOOKUPって実際どれだけ速いの?」
ちょっと気になって実験してみました。

ExcelのVLOOKUPを100000行再計算させてみた
こんな表を用意。
E列にVLOOKUPが10万行入力されています。
これをいっぺんに再計算させてみました。
Excel2016を使用しています。

結論から言うと、かかった時間は0.2秒
速いなんてもんじゃないです。爆速です。

以前のバージョンではふつうに数分かかっていたのですが、Excel2016から超爆速になりました。
ノートPCでこれなら速度としては満足だと思います。

VLOOKUPは検索方法にFALSEを指定した場合、検索値に完全一致する値を範囲から探します。
方法としては、検索値を範囲の上から下まで、一行ずつ検索します。

「どこかな~どこかな~」ってVLOOKUPがひとつずつ見ていくんですね。
超地道な作業です。
なので検索値が範囲の下にあればあるほど、処理に時間がかかります。

さらにそれが10万個。
10万行の検索を10万回です。もう嫌がらせですね。

しかしそれをVLOOKUPさんが0.2秒でやってくれました。
へへぇ、おみそれしました!

今までは「VLOOKUPなんて遅いよ!」とか言われてきたのですが、さすがにこうなると遅いとは言えませんね。
なんというか、マイクロソフトさんの「どや!Excelは遅くなんかないんじゃ!」という意気込みを感じます。

ここまで爆速になった背景としては、たぶんなんですが。
大量のデータを扱う現代に合わせたというのと、Excelブランドもうかうかしていられなくなってきた、というのがあると思います。

今や10万行や100万行といった大量のデータを扱うのは当たり前になってきましたから、そのニーズにあわせられるように。
そして最近ではgoogleさん他、優秀な企業やソフトはごまんとありますから、呑み込まれないように…ということなんでしょう。

IE(インターネットエクスプローラ)がブラウザのシェアをほぼ独占していたのが、今ではそうでもないように。
うかうかしているとExcelのブランドも地に落ちてしまうかも!そんな気概を感じます。

とにもかくにも、VLOOKUP、超速いです。
一昔前は検索方法をどうにかTRUEにして…なんてことをやっていたんですが、FALSEでも超速いです。
VLOOKUPが使える場面では、どんどん使っていきましょう。

【 ちょっと濃い話】第4引数をTRUEにしたほうが良い場合

今回、VLOOKUPの使い方として「とりあえず検索方法はFALSEにしましょうね!」と伝えてきました。
実はTRUEにしたほうが、処理速度自体は速いです。

でもこのお話、ちょっとムズカシイことに首を突っ込みます。
「なんで検索方法はFALSEなの?なんで?ねえなんで!?」
と気になって夜も眠れない、というアナタは読んでみてください。

そうでもない…と言う場合は、VLOOKUPを使うことに慣れてきたとき、
読んでみると「へぇ~なるほど」となるかもしれません。( ̄ー ̄)ニヤリ

どんなときにTRUEにするべきなの?というと。

検索値の候補が数値のみで、かつ昇順に並び替えられている。
さらに検索値が必ず存在するとわかっているとき。

この場合、検索方法は「TRUE」を使ったほうが良いでしょう。

どういうことなのか?
まず検索方法のFALSEとTRUEの違いから。

検索方法をFALSEにすると、VLOOKUPは範囲のなかから検索値を1行ずつしらみつぶしに探します。
「この行と検索値は違う。次の行も…ちがう。まだかなまだかな~」
という感じに。

対して検索方法をTRUEにすると、VLOOKUPは「二分検索」という方法を使って検索値をさがします。
これは範囲が昇順である前提で、「だいたいこのへんかな」とアタリをつけて検索する方法です。
そして「検索値に一致する値」または「検索値を越えないもので、もっとも近い値」を抽出します。

具体的には、まず範囲の半分の地点に目をつけ、それより大きな値か、小さな値かを判断します。
大きな値なら半分より上の値はすべて対象外、半分より下の値だけ見れば良いわけです。
さらに残った範囲の半分の地点に目を付け…とういことを繰り返します。

範囲を半分、またその半分…とアタリをつけて探していくので、とても高速です。
位置行一行しらみつぶしに探す「検索方法:FALSE」より、圧倒的に高速です。

ただ、前述したように「範囲内の検索値の候補が昇順で並び替えられていること」が大前提です。
たとえば検索値の範囲に

1 7 4 5 3 9 15

と縦に並んでいたとして、「3」を検索値としましょう。
まずVLOOKUP(検索方法:TRUE)は真ん中の「5」にアタリをつけます。
目的の「3」は5より小さい値ですので、それより前の値を検索しにいきます。

ところが前方向の値は「1」「7」「4」しかありません。
目的の「3」は見つからずじまい…ということになってしまいます。
値が昇順に並び替えられていないと、このようなことがおこります。

さらに検索値の候補が数値のみと書きましたが、これは文字列が含まれると「超わかりづらい」からです。
ダメというわけではありません。

たとえば以下のような文字列の並びがあります。
Excelでの文字列の並び替え
数値に見えますが、すべて文字列です。
これを昇順に並びかえるとどうなるでしょうか。
ちょっと考えてみてください。

 

Excelの文字列の並び替え_結果
こうなります。
なんでやねん!って感じですよね。

これが数値なら、00001→999→33333→1000000 となるところです。
これが「文字列の並び替え」の難しいところです。
この動きをわかったうえで使うなら良いのですが、そうでない場合は検索値を「FALSE」にしたほうが何かと良いです。

最近のExcel、特にExcel2016以降では、検索方法をFALSEにしてもVLOOKUPは超速いです。
上の【 豆知識 】VLOOKUP関数は超高速!100000行でも0.2秒で書いたように、10万行でもわずか0.2秒。
過去のバージョンでVLOOKUPが遅くて困っている場合や、どうしても速さにこだわる場面では、検索方法をTRUEにしてみても良いでしょう。

スポンサーリンク
おすすめの記事