欲しい情報を狙い撃ち！Excel「VLOOKUP関数」の使い方講座！＋オマケ
大量のデータから、特定のデータを探したい…
そんなとき、ありますよね。
「Ctrl」+「F」で検索ウィンドウを出して調べてコピーしてきてもいいけど、それだと値が変わったときにまた直さなきゃいけない…
なんて思っている方、必見です！
これ、「VLOOKUP関数」というものを使えば解決できますよ！！
というわけで、今回はExcelにおける「VLOOKUP関数」の使い方をご紹介します！！！
目次です。
0.とりあえず使い方だけ見たい方向け
まとめの内容をそのまま持ってきます。
- VLOOKUP(検索値, 範囲, 列番号[, 検索方法])
- 検索値：どんな値を検索したいか
- 範囲：どの列を検索して、どの列を持ってきたいか両方を含めた範囲
- 列番号：見つかったら、範囲の左から何番目を持ってきたいか
- 検索方法：
- TRUE：近いデータ、昇順にする必要あり、見つからなかったら近いデータ
- FALSE：完全一致、見つからなかったら「#N/A」というエラー
- COUNTIF(範囲, 検索条件)
- 範囲：検索する対象の範囲
- 検索条件：どんな値を検索したいか
ここからは、この内容をとことん解説していきます！
1.サンプルデータ
今回、「各都道府県の人口データ」を例に使用します。
データの出典は、統計局ホームページです。
このページの、「2-2.都道府県別人口と人口増減率」のExcelをDLしました。
その中から、都道府県の列と、平成27年の人数の列を別シートにコピーして使用していきます。
「B3」セルから「B49」セルまでに都道府県名を入れ、それに対応する人数を隣の「C3」セルから「C49」セルに入れておきました。
また、検索したい都道府県名をあらかじめ「F3」セルに入力しておきました。
そして、人数を表示させたいセルを「F4」セルとします。
画像にすると、以下のような形ですね。
2.人数を表示してみよう
さて、早速探していきましょう！
人数を表示したいセル(今回の例の場合は、「F4」セル)に、以下の内容をそのまま入力してみてください。
=VLOOKUP(F3,B3:C49,2,FALSE)
入力すると、「F3」セルに入れた都道府県の人数が表示されます！
表示できたら、都道府県の名前を変えて、数字も変わるか試してみてください。
逆に、検索している都道府県の数字を変えても、検索結果の部分にしっかり反映されるはずです。
あ、数字を変えたら元に戻しておきましょう（笑）
3.細かく見てみよう
次に、どんなことをしているか詳しく見ていきます。
基本的な構文は、以下の通りです。
VLOOKUP(検索値, 範囲, 列番号[, 検索方法])
4つの値を入力する必要があるんですね。
一つずつ見ていきましょう。
3-1.検索値
これは、どんなデータを探したいかを入力します。
今回であれば、「F3」セルに人口を持ってきたい都道府県を入力しているので、「F3」と入力しました。
もちろん、ここに直接「"東京"」と入力すれば、東京を探すこともできます！
3-2.範囲
これは、どこからその値を探すかを入力します。
このとき、2つほど注意があります。
一つ目は、『検索したい列を一番左に指定する』ことです。
今回であれば、都道府県名で検索をしたいので、そうなるように左端をB列にしています。
二つ目は、『検索した結果を表示する値を、検索したい列より右に含める』ことです。
今回は人数を表示したかったので、人数の列、つまりC列も範囲に入れています。
ここに限らず、範囲をセル指定する場合は、「左上:右下」で入力します。
なので、今回は「B3:C49」と入力した、というわけですね。
3-3.列番号
ここには、検索して見つかった場合、その行の左から数えて何番目のセルを結果として表示するかを入力します。
今回は、範囲の中の2列目、人口を表示したかったので、「2」を入力しました。
3-4.検索方法
ここが厄介です。慎重にいきましょう。
3-4-1.入力する値
まず、ここは「TRUE」、もしくは「FALSE」のどちらかを入力します。
どちらを入力しても、「検索する値と全く同じ値が見つかった場合」は、その場所のデータを返します。
問題は、見つからなかった場合ですね。
それぞれ、見つからなかった場合の動作は以下の通りです。
- TRUE：一番近いデータ、正確には入力した値未満の最大値を返す。
- FALSE：エラー「#N/A」を返す。
「FALSE」の場合は非常にわかりやすいと思います。見つからなかったらエラーだよって教えてくれるんですね。
3-4-2.「TRUE」の動作…え、何これ？
問題は「TRUE」の場合。
これ、ぱっと見だと非常に変な動きをします。例えば、上の例でここを「TRUE」にすると…
あれ、数字が変わった！？
となり、実際のデータを見てみると…
…なんで大阪？というふうに、不可解なことになっていますね。
検索したい「東京」がデータの中にあるはずなのに、なぜかそれではなく「大阪」のデータを持ってきてしまっています。
3-4-3.「TRUE」を指定する場合の前提条件
実は、ここを「TRUE」にする場合、検索する範囲(指定した範囲の一番左の列)が昇順で並んでいる必要があるんです！
数字なら小さい順、文字なら辞書順ですね。
というわけで、並び替えてみましょう。
データの上、見出しの部分を選択して、「Ctrl」＋「Shift」＋「L」を押してみてください。
なんか、小さいボタンが出てきましたね。これ、フィルターといいます。
この、都道府県の方のボタンを押すと、メニューが出てきます。
その中から「昇順」を押すと…
はい、一瞬でデータが並び変わりました！
右の検索結果も、しっかり東京の数字になってますね。
3-4-4.なんでデータが昇順じゃないといけないの？
これは、値の検索方法のせいです。
感覚的に、一番上から順番に見ているように思えますが、実は違うんです。
このサイトを参考にさせてもらいましたが、どうやら「二分探索」という方法を使っているらしいです。
これは、真ん中のデータを見て、それが探したいデータより小さければその後ろを、大きければその前を見る、ということを繰り返して目的のデータを探す方法になります。
えー、これを本格的に解説しだすと別の話になってしまうのでこのくらいで。細かく見たい方は、Wikipedia先生をご覧ください。
で、この「二分探索」というやつを使うには、データが昇順に並んでいる必要があります。
だから、「TRUE」を指定する場合はデータが昇順である必要があるんですね。
3-4-5.なんか構文に大括弧入ってるんだけど…
これは、そこが省略可能であることを意味しています。
なので、以下のように書くことも可能です。
=VLOOKUP(F3,B3:C49,2)
このときですが、4つ目は「TRUE」が入ったときと同じ動作になります。
そして、ここでも1つ注意。
以下のようにも書くことができるんです。
=VLOOKUP(F3,B3:C49,2,)
上との違いがわかるでしょうか…？
並べてみるとわかりやすいかもしれませんね。
- =VLOOKUP(F3,B3:C49,2)
- =VLOOKUP(F3,B3:C49,2,)
はい、括弧の最後にコンマが入っているかどうかです。
実は、コンマが入っていると、今度は「FALSE」と同じ動きになります！
だいぶ厄介ですね…なので、基本的には省略しないようにしましょう。
さて、これでどんな入力を行っているかわかりましたね。
では、オマケでもう一個解説していきたいと思います。
4.データがあるかどうかだけ見たいとき
はい、見出しの通り、検索したい値が対象の範囲にあるかどうかだけを見たい場合もありますよね。
今回の都道府県の例だと、「こんな名前の都道府県あったっけ？」でしょうか。
4-1.「VLOOKUP関数」を使って探してみよう
さあ、問題です。
「VLOOKUP関数」を使って、指定した名前の都道府県があるかどうか調べてみてください。もちろん、ここまで解説した内容で探せますよ。
あ、結果がこうなればその都道府県がある、こうなればないというふうに決めてもらって構いません。
ちょっと改行を挟みます…
改行このくらいでいいですかね。
そろそろ正解例を出しましょう。
実は、最初に出した例そのままで大丈夫です！
上に戻るのも面倒だと思うので、もう一度書いておきます。
=VLOOKUP(F3,B3:C49,2,FALSE)
で、どうやって判定するか…ですが。
四つ目の値、「FALSE」を指定すると、見つからなかったら「#N/A」っていうエラーが返ってくるんでしたね？
というわけで、「数字が出てくればその都道府県はある」、「エラーが出てくればその都道府県はない」となります！
皆さん、正解できましたか？
4-2.え、探すだけで毎回「VLOOKUP関数」を使わなきゃいけないの？
と思う方、いらっしゃると思います。
実はこの「VLOOKUP関数」、結構処理が重くて、大量のデータを探したいときなんかはExcelの動作自体まで遅くなる、といったことが結構あります。
なので、別の方法を使ってみましょう。
今回は、そんな中の一つ、「COUNTIF関数」を使う方法をご紹介します！
4-3.「COUNTIF関数」って何？
これは、元々「指定した範囲の中で、ある条件に一致したものの個数を返す」関数になります。
早速、使ってみましょう。
また別のセルでいいです、以下の内容を書いてみてください。
=COUNTIF(B3:B49,F3)
すると、「1」となるはずです。
4-4.細かく見てみよう
さあ、こっちはどんなことをしているのでしょうか。
まず、構文を見てみましょう。
COUNTIF(範囲, 検索条件)
4-4-1.範囲
「VLOOKUP関数」と同じく、どの範囲から探すかを入力します。
範囲の指定方法も同じですね。「左上:右下」です。
4-4-2.検索条件
個数をカウントする条件を書きます。
今回は、あるデータを探したいので、そのデータ、つまり「F3」に入力した内容を探したいので、「F3」を入力しています。
今回は、都道府県が1つずつ並んでいるデータなので、見つかっても多くて1個しかないはずですよね。
なので、見つかったら「1」が、見つからなかったら「0」が返ってくるようになります！
「VLOOKUP関数」よりも簡単に使えるので、目的によって使い分けてみましょう。
まとめ
いかがだったでしょうか。
ちょっと蛇足が入って長くなってしまいましたが、改めて今回紹介した2つの関数の使用方法をまとめます。
- VLOOKUP(検索値, 範囲, 列番号[, 検索方法])
- 検索値：どんな値を検索したいか
- 範囲：どの列を検索して、どの列を持ってきたいか両方を含めた範囲
- 列番号：見つかったら、範囲の左から何番目を持ってきたいか
- 検索方法：
- TRUE：近いデータ、昇順にする必要あり、見つからなかったら近いデータ
- FALSE：完全一致、見つからなかったら「#N/A」というエラー
- COUNTIF(範囲, 検索条件)
- 範囲：検索する対象の範囲
- 検索条件：どんな値を検索したいか
これらを使えば、狙った情報をピンポイントで持ってこれるようになります！
非常に使える関数たちなので、この機会に是非使ってみてください！！
Twitterもやっています。他のExcel講座を公開したときもこちらで呟くので、よかったら覗いてみてください。
それでは。
あとがき
これ書いた方がいいかなを繰り返した結果この長さだよ、シノです。
関数2個でこの長さって…厳密に説明しようとするとかなり長くなるんですね。
あ、でも今回紹介した「VLOOKUP関数」はほんとに使えるので、よかったら使ってみてくださいね。
さて、今回いきなりExcel解説をしてみたわけですが、思ったより色々機能あるんですよね。
さらっと本文に入れたフィルター機能(「Ctrl」＋「Shift」＋「L」で出てくるやつ)なんかもめっちゃ使えるので。
こういったショートカット集も今度書こうかななんて思っていたり。
というわけで、最後までご覧いただき、ありがとうございました。
