第三十四回 誰でもわかる&できる!VLOOKUP関数入門ガイド

Excelの入力作業で、時間を無駄にしていませんか?

例えば、毎月作成する請求書。
「商品名は…スティック…のり、単価が…えーっと200円」といちいち手入力しているあなたは、
残念ながらExcel半人前!

こんなとき、「Excelできる人」が必ずと言っていいほど使っているのが、VLOOKUP関数です。
この関数を使えば、データ入力が飛躍的にスピードアップするんですよ!

自動入力

このようにデータを自動入力してくれるので、VLOOKUP関数を使うか使わないかで作業時間が
何倍も変わってくるのです。

…と非常に便利なVLOOKUP関数ですが、実は「数式が長くてややこしい!」と苦手意識を持つ人が
多い関数でもあります。

そこで今回は、その最大のネックである「数式のややこしさ」を完全解消!
スッキリわかって使えるようになるまで、じっくり!丁寧に!解説します。



VLOOKUP関数を使う前の大切な準備

データ入力の強い味方であるVLOOKUP関数ですが、この関数を使う前に準備しておくことが
1つあります。

それは、 VLOOKUP関数を入力する表とは別に、データの一覧表を用意しておくということ。
以降は具体例をあげるとわかりやすいので、下記の例ですべて説明していきますね。

具体例

・データの一覧表:「商品一覧」シートに用意
・VLOOKUP関数を入力する表:「請求書」シートに用意
・VLOOKUP関数を使ってしたいこと:「請求書」シートの「商品番号」に番号を入力したら、「商品名」が自動入力されるようにしたい




商品番号

まずは、「商品番号」と「商品名」が入った
「データの一覧表」を準備しましょう。
その際、VLOOKUP関数のルールとして、
下記の点に注意してください。

・「商品番号」のデータは、表の左端に配置する

なお、データの一覧表と関数を入力する表は、
同じシートに作成することもできますが、
別シートに作成しておいたほうが、
後からデータや表を編集する際に便利です。



データの一覧表が準備できたら、「請求書」シートにVLOOKUP関数を入力します。
すると、下記のような仕組みでデータを自動入力してくれます。

データ一覧表 「請求書」シートの「商品番号」の欄に番号を入力すると、その番号を手がかりにして
「データの一覧表」から該当する「商品名」を検索し、自動入力しているんですね。

では、いよいよVLOOKUP関数の数式を見ていきましょう。



VLOOKUP関数のややこしすぎる数式を解読!

VLOOKUP関数をややこしくしている最大の要因…それは、数式の引数が多いこと!!

VLOOKUP関数 =VLOOKUP(検索値,範囲,列番号,[検索方法])

「検索値」や「範囲」など、「引数」と呼ばれる項目が4つもあるせいで、頭が混乱してしまうのです。
まずは、それぞれの引数が何を指しているのかを確認して、数式を解読しましょう。

数式



①検索値……データの一覧表から「商品名」を探す手がかりとなる値。
      ここでは、「請求書」シートの「商品番号」に入力される値(=「100」)のこと。

②範 囲……「商品一覧」シートにあるデータの一覧表
      ここから検索値(=「100」)を手がかりに、求める「商品名」を探します。

③列番号……自動入力したいデータが範囲の何列目にあるかを指定します。
      「商品名」がある「2列目」を指定すると、検索値「100」がある行の2列目=
      「ボールペン」が、「請求書」シートに自動入力されます。

④検索方法…範囲から検索値を探すときの検索方法を指定します。「完全一致」のデータのみ
      検索する方法と、「近似値」のデータを検索する方法の2種類があります。

「うーん、まだよくわからない…」という方も大丈夫!
次は数式を入力する手順を、引数ごとに1つ1つ確認していきます。




習うより慣れろ!数式を1つ1つ入力してみよう

数式の入力手順は、以下の流れで説明していきます。

 =VLOOKUP(検索値,範囲,列番号,検索方法)

 ①関数を入れる場所を確認    ④列番号を入力
 ②検索値を入力         ⑤検索方法を入力
 ③範囲を入力

①関数を入れる場所を確認

入力場所

左記のように「表がまっ白!」という状態では、
数式を入れる場所も戸惑ってしまいますよね。

VLOOKUP関数の数式は、自動入力したいセル
入力します。

自動入力したいのは「商品名」なので、数式は
「請求書」シートの「商品名」の列に入力します。


②検索値を入力

検索値

数式を入力する場所がわかれば、まずは
「検索値」を入力します。

「商品名」を探す手がかりとなるのは、
「商品番号」でしたね。

よって「商品番号」が入るセル番地
入力します。ここでは「請求書」シートの
セルB5となります。


【ポイント】先に「検索値」を1つ入れておく

慣れないうちは、数式を入力する前に検索値を1つ仮入力しておきましょう(ここでは「100」)。
そうすることで、「検索値の場所はここだ!」としっかり意識づけができます。

③範囲を入力

範囲入力

次に、「範囲」を入力します。
「商品一覧」シートを表示して、データの一覧表の範囲をドラッグで選択しましょう。

このとき、表の見出しは選択しなくてもかまいません。

【ポイント】範囲は絶対参照に

範囲を選択するときについ忘れがちなのが、絶対参照にするということ。

VLOOKUP関数は数式をコピーすることが多いので、必ず絶対参照にする癖をつけておきましょう。

④列番号を入力

列番号入力

続いて、「列番号」を入力します。

自動入力したいデータ=「商品名」は「範囲」の左から2列目にあるので、「2」と入力します。

ちなみに「単価」を自動入力したい場合だと列番号は「3」になりますね。

【ポイント】「範囲」の列番号を数える!

列番号を数えるのは、「範囲」の表です。

関数を入れる表と、データの一覧表の見た目が似ている場合は、どちらの表の列番号を見ればよいか混同しやすいので、注意しましょう。

⑤検索方法を入力

列番号入力

最後に、範囲から検索値を探すときの「検索方法」を入力します。

「0」と入力すると、「検索値」と完全一致するデータのみ探します。

ここでは、検索値「100」と完全一致する行を探して、2列目の「商品名」を自動入力してくれます。

ちなみに、「1」または未入力の場合は、完全一致のデータが範囲内にないとき、一番近いデータを探します。


あれほどややこしく感じたVLOOKUP関数の数式も、1つ1つ引数を確認しながら入力すると、スッキリ頭に入ってきますね。あとは、実際にどんどん使って数式に慣れていきましょう!

マスターするまではちょっと大変なVLOOKUP関数ですが、一度覚えてしまうと本当に便利!
というのも「自動入力」って、ただ入力スピードが速くなるだけではないんですよ。




「自動入力」がもたらすありがたすぎる効果

VLOOKUP関数の「自動入力」という仕組みは、データ入力の「スピードアップ」以外にも、
こんな素晴らしい効果を発揮してくれます。

《効果①データの更新》
検索元となる一覧表のデータを変更すると、VLOOKUP関数で自動入力したデータも
すべて一度に更新されます。
1つ1つ修正する手間が省ける上、修正漏れもないのでとても便利!

データの更新


《効果②入力ミスの防止》
自動入力だと、手入力時によくやりがちな「漢字の変換ミス」などの入力ミスがない点も大きな魅力!

ミス防止


こんな便利な関数を「難しそう…」といつまでも敬遠しているのはもったいない!
ぜひこの機会にVLOOKUP関数と向きあって、「Excelができる人」の仲間入りを果たしてくださいね!




おまけ:こんなエラーが出てしまったら・・・

おかしいな。ちゃんと数式を入れたのに、エラーが出る…」
VLOOKUPの正しい数式を入れたのにエラーが出る場合は、たいてい下記の2つが原因です。

《エラー① 数式をコピーしたらエラーが!!》
数式エラー

数式をコピーすると、エラーが出てしまいました。これは、「検索値」(ここでは「商品番号」)の欄が空白になっているからです。

こんな場合は、IF関数を使って「空白処理」をしましょう。

操作の詳細は、下記の過去記事をご参照ください。
第三回 「IF関数」の空白処理を知らないとExcelの便利さ半減!?



《エラー② 「検索値」を入力したらエラーが!!》
検索値エラー

検索方法を「完全一致」にしている場合、入力した「検索値」がデータの一覧表と少しでも違うとエラーが出ます。こんな場合は入力規則のリストを使うと便利です。


入力規則

左記のように、リストから検索値を選ぶだけにしておけば、入力ミスの心配がなくて安心!

操作の詳細は、前回の記事をご参照ください。
第三十三回 気遣い抜群のExcel注文書でお客様も大満足!





今回のExcelスキルの習熟度をチェックするためのシートをダウンロードしていただけます! (※感想など頂けると嬉しいです!)

今回のExcelスキル:VLOOKUP関数



“第三十四回 誰でもわかる&できる!VLOOKUP関数入門ガイド” への4件のコメント

  1. [...] VLOOKUP関数の使い方は、下記の過去記事で丁寧に解説しています。 第三十四回 誰でもわかる&できる!VLOOKUP関数入門ガイド Excelの入力作業で、時間を無駄にしていませんか? [...]

  2. 手塚 圭 より:

    このようなサイトがあると助かります。

  3. 手塚 圭 より:

    どうなればダウンロードできるのでしょうか。

    • マス夫 より:

      コメントをいただきありがとうございます!
      これからもExcelの便利な機能をわかりやすくお伝えしていきますので、ぜひお役立てください。

      なお、チェック用素材のダウンロードにつきましては、
      「ダウンロードページはこちら」をクリック→ポップアップページ下部の「利用規約に同意の上ダウンロードする」をクリック
      で、ダウンロードいただけます。
      ポップアップが表示されない場合は、インターネットの設定から、ポップアップを有効にして再度お試しください。
      ※設定方法はブラウザーによって異なります。

コメントをどうぞ

このページの先頭へ

Get Adobe Flash player Plugin by wpburn.com wordpress themes