【Excel】意外と使う!VLOOKUP関数をマスター

キャリア

エクセルを使えるか使えないかの判断によく使われる[VLOOKUP]活用できてますか?
まだ自信をもって使う事ができない…と思っている方も大丈夫です
使い方自体はとても簡単なのでマスターしましょう!

※おさらいを兼ねて概要だけ再確認したい方は”まとめ”だけ見るのをおすすめします♪

VLOOKUPはどんな時に活用できる?

  • たくさんあるデータ(例えば商品データ)から特定のデータだけ引用して見積書を作りたい
  • 入力する内容が大体同じものの入力を簡素化したい(備品の発注履歴を簡単に残したい、など)
  • 特定の商品の情報を簡単に調べられるようにしたい

といったときに活用ができます。

”関数”というとどうしても計算や数字などを考えて作らないといけないイメージがありますが、VLOOKUP関数は簡単に言えば情報を検索するためのものです

なので、事務のお仕事をされている方は特にたくさんの商品データの中から見積書や発注書を作ったり、備品がどこに管理されているのか、または誰が持っているのかなどを管理することがあると思いますが

活用方法は様々ですがそういった管理を簡単にする事が出来るようになる関数、と思っておいてください!

慣れれば自分で応用もできるようになります♪

説明用の例を挙げると…

エクセルの説明なので何かしら例があると説明がしやすいので、
「備品一覧から、今月発注した備品だけを〇が都度発注備品という欄に記載していく」
なんてまぁまぁよくありそうな感じではありませんか?

この備品の数ならもちろん手入力で可能ですが、部品がものすごい量になった場合、
「えーっとどこだっけ、検索して…コピぺして…次これで…」

というのはやや効率が悪いのと何より入力しづらくてイライラしちゃいますね。
手入力も効率がよさそうですが、そのうち金額だけでもいいから自動入力して!ってなってきそうです

ならそれをしましょう!っていうのがVLOOKUP関数です。

まずはVLOOKUPを呼び出す

※エクセルの関数挿入を普段からやっている方はここを読み飛ばしても大丈夫です

まずは関数をどこに参照するか選択しておく

今回だと「金額だけでも自動入力して!」なので、VLOOKUPを利用するのは”金額部分”なので、表で言うならこの部分ですね。

“関数の挿入”を表示する

このfxと書いてあるマークをクリックすると
エクセル上で利用できるあらゆる関数を呼び出す事ができる「関数の挿入」というダイアログを呼び出すことができます。

VLOOKUP関数を検索する

  1. 何がしたいかを簡単に入力して、[検索開始]をクリックしてください。と書かれているところにVLOOKUPと記入して検索開始ボタンをクリック
  2. 下の関数名というところにVLOOKUPが表示されるので、それを選択して(青くなった状態)でOKボタンをクリック

※よくやりがちですが、VROOKUPではなく、VLOOKUPです。入力が不安な方は下記をコピペして利用してください♪

VLOOKUP

VLOOKUPを設定する

VLOOKUP関数を呼び出すと下記のようなダイアログが出てきます。

大体の人が、ここで「これはどういうこと?」と分からなくなってやめてしまう事が多いと思います。
とりあえずは理屈などは考えずに説明の通りに参照してみましょう!

検索値を設定する

まずは検索値を設定します。

  1. F5を選択
  2. 検索値の項目にF5と入力されているのを確認する

これで検索値の入力ができました!
うっかりOKを押すと「この関数に対して、少なすぎる引数が入力されています」とエラーが出ますのでまだ押さないでくださいね。
うっかり押してしまった場合は、そのままOKを押してfx(関数の挿入)を押すとまた入力画面が出てくるので慌てないでくださいね。

検索値って結局なに?

ざっくり簡単に説明すると、”検索フォームはどこですか?”とVLOOKUP側が聞いてきている感じです。
今回で言うと、検索フォームはF5ですね!じゃぁF5に入力されている”ボールペン”という文字を探すんですね!

というところまでが検索値で設定しているところです。

ボールペンという文字を探すのは分かったけど…どこから探すの…??
となるので、次に設定するのが”範囲”です。

範囲へは範囲の白い入力フォームをクリックすれば、次の範囲の設定に移りましょう!

範囲を設定する

まずは範囲を設定しましょう

  1. B5~C12までをドラッグしながら選択する
  2. F4キーを押す

これで範囲の設定も完了しました!

次は列番号の入力なので、列番号の部分をクリックして入力できるようにしておきましょう。

範囲って結局なに?

範囲は先ほど設定した検索値、今回で言うとボールペンという文字をここから探してきてね!という、探し場所の範囲を指定してあげています。

なのでこの場合だと、参照してほしい備品一覧はここだよーとVLOOKUPに教えてあげている感じですね。

F4キーって何で押すの?

F4キーを押すと「$」というマークが出てきてちょっとびっくりしますね。でもこれはエラーではなくて、「絶対参照」という設定をした事になります。

絶対参照は数式をコピーした時用の対策で、ブレずにここを参照するようにしますね!!とくぎを刺してる感じです。意外と設定してないと後から思わぬ落とし穴になることもあるので、範囲の設定の時にはF4で絶対参照しておくのも覚えておくと便利です◎

なので今回で言うと、絶対この備品一覧を参照している範囲は動かさないでね!とお願いしている感じです。参照元がずれでしまうと結果も変になってしまって困るので、それを防いでいます。

列番号を指定する

  1. 2と入力する(大文字でも自動で半角にしてくれるので大丈夫)

今回は数字の入力だけです!
なんのこっちゃ?という感じですのでちょっと解説します。

列番号に2と入力した理由

今回は表全体ではなく、先ほど”範囲”で選択した部分だけでの話になります
選択した部分の左から何番目の列を結果として表示する?というのを聞かれているので、今回の場合は備品一覧からボールペンを探して、ボールペンの価格を結果として出してほしいので、ボールペンの商品名列が1列目、価格が2列目となるので2を入力します。

この部分が一番ややこしく感じるかもしれませんが、

・範囲で選択した部分だけに対してだけ
・左から1列…2列…とVLOOKUPは認識していて、結果を表示してほしい列番号を入力してあげる

というのを覚えれば大丈夫です。ここさえ超えればあとは仕上げです!

検索方法の入力をクリックして入力に移りましょう!

検索方法を設定する

  1. FALSEと入力する(大文字でも小文字でも全角でもスペルがあっていれば大丈夫です)
  2. OKをクリックする

FALSEって何?

FALSEというのは検索方法の指定になります。よーくみると、ここに説明が書いてあります。

検索方法には検索値と完全に一致する値だけを検索するか、その近似性を含めて検索するかを、理論値(近似値を含めて検索=TRUEまたは省略、完全一致の値を検索=FALSE)で指定します。

と記載されているのです。

例として挙げると、ペンと記入した場合、ボールペンだろうがシャープペンだろうがペンでしょ!と検索結果に出しちゃうのがTRUE

ペンっていう文字に全部合致するやつ探すね!がFALSEです
基本的にVLOOKUPではFALSEを使う場面の方が多いのでこの違いなんだなーくらいで大丈夫です。

ちなみにうっかり空欄にするとTRUE(近似値を含めた検索)になるので注意です!

 

これで完了です!

OKを押すとボールペンの金額「100」が表示されました!
これでVLOOKUPの設定ができました!おめでとうございます☆ 

あとはこの数式をほかの価格のセルにも適用すれば
商品名を入れれば自動的に金額を入れてくれる!という機能が出来上がりました!

なんか数字が出ない場合

#REF!とか#N/Aとか#だけとか出る!!!
って困っている方はこんなのが原因かもしれません

#REF!

指定してる場所なんて無いですけど!って言われている状態です。

今回の表で分かりやすく説明すると、項目の範囲で選択しているのは商品名と価格の2つの列だけなのに列番号に3を入れてしまったりすると、そもそも2列しかないのに3列目ってなに!エラーです!
と言って

無効なセルを情報として表示してくださいって命令になっちゃってるよというエラー表示になります。この#REF!が出る場合は数式が間違っている状態なので改めて設定を見直す必要があります。

#N/A

一致する結果がありません!!って言われている状態です。

今回の表で分かりやすく説明すると、ボールペンって入れたら100円って入力してくれるようにしましたが、うっかりタイプミスにしていてボーーーールペンなんかにしちゃうとこの#N/Aが表示されています。

備品一覧にボーーーールペンなんてものはないので確かに結果はありません。
なのでこの場合は数式が間違っているというよりも、入力されている内容、(難しく言うと検索値に指定したセルの文字列)が間違っている可能性の方が高いので、いったん入力内容のチェックをして、

合ってるのに出る!という場合は範囲の指定が間違っているかも?検索の型はfalseにちゃんとしたかな?と順番に原因を見ていってください。

ちなみに検索値にしていされている場所が空欄でもこのエラーを出してしまうのでこの数式をコピーしておいて、まだ何も検索値に入力がされていない場合も#N/Aと出てしまいます。

#だけ表示される

セルが小さすぎて表示できないよって言われている状態です。

この状態にまでなることはあまりありませんが、#だけがでてしまっているセルの幅を広くしてあげると直ります。

#VALUE!

そもそもVLOOKUPとして成り立ってないよ!と言われている状態です。

検索の型であるTRUEやFALSEのスペル間違いでFULSEとかFARUSEとかTURUやTLUEなんて入れちゃうと
VLOOKUPはそういう入力を想定してないのでエラーを返してしまいます。

あとは列番号は必ず1列目…2列目…と1列目から始まるのに、0や-1などの数字を入れると、同じエラーが出てしまいます。

スペル間違いがないかなどを確認して、関数の因数ダイアログ(検索値や範囲などをしていたやつ)をもう一度見直す必要があります。

 

 

まとめ

ざっくり今回のVLOOKUPを振り返りましょう。おさらいとしてみたい方はここだけでも大丈夫です。

まずVLOOKUP関数は、特定の検索して探してきてくれる関数です。
そのために設定する必要があるのが

  • 検索値
  • 範囲
  • 列番号
  • 検索の型

です。書く項目は下記の図のような説明がされます。

躓きやすいところとしては検索値と範囲はどっちがどっちなの?となりやすいのですが、検索値の情報をもとに範囲で指定したところから探してくる!という動作の流れを考えれば覚えやすいですよ!

せっかく設定したのになんだかエラーのような表示がされる場合は

  • #REF!…指定してる場所なんて無いですけど!って言われている状態
  • #N/A…一致する結果がありません!!って言われている状態
  • #だけ表示される… セルが小さすぎて表示できないよって言われている状態
  • #VALUE!…そもそもVLOOKUPとして成り立ってないよ!と言われている状態

エラーのどれにも言える事ですが、指定している場所や入力のスペルミスが無いかの再チェックをしましょう

VLOOKUPは設定項目の多さからとっつきにくさがあるように思えますが、使えるようになるといろいろと応用が利く関数なのでどんどん使ってみてください!

 

 

ピックアップ記事

関連記事一覧