【エクセルの使い方】VLOOKUP関数がうまくいかない時のチェックポイント

    エクセルを習熟している方が必ず使っていると言っても過言ではないのが、VLOOKUP関数です。この関数は、繰り返し同じ商品の価格を入力する、同じ会社の住所を間違えずに何度も入力したい、といった請求書や見積書などで重宝する関数です。今回は簡単なようで間違えやすいVLOOKUP関数の使い方と、うまく動作しない時のチェックポイント、間違いを起こさない予防法を解説します。



    VLOOKUP関数とは

    指定された範囲内の一番左の列の値を検索して、その行にある指定した列の値を返します。文字で説明すると何のことか分かりにくいと思いますが、下の図のように、果物の価格表という範囲内でメロンを検索して、メロンに対応する単価1200円を抽出するという事です。

    VLOOKUP関数の書き方

    VLOOKUP関数は「=VLOOKUP(検索する値,検索範囲,返す値の列,検索の型)」と書きます。 先ほどの例ですと、「=VLOOKUP(“メロン”,A2:B8,2,FALSE)」となります。メロンをセルA2からB8の範囲の一番左の列から探し出して、メロンの行の2列目にある値を返しなさい、メロンに限りますよ(完全一致)、という意味です。

    最後の検索の型はFALSEとTRUEがあり、FALSEは完全一致、TRUEは近似値を検索してきます。上記の果物の価格表の中には「イチジク」はありませんが、先ほどの式の検索する値を”イチジク”として「=VLOOKUP(“イチジク”,A2:B8,2,TRUE)」とすると、価格表の中でイチジクに一番近いイチゴの価格380円を返します。

    注意 VLOOKUP関数でありがちな単純ミス

    VLOOKUP関数に限らず、セルに関数を入力して、「やった、できた」とホッとして、その関数をコピペで出来上がりと思ったら、「あれ、値がおかしい」というのは、うっかりやりがちなミスです。

    VLOOKUP関数は範囲を指定するので、そのままオートフィルでコピーすると範囲もペースト先のセルに応じて変わってしまいます。つまり、ペースト先の関数に指定された範囲の参照する表がずれてしまったためにこうしたことが起こります。コピペする場合は、範囲指定は$A$2:$B$8のように絶対値で指定しておきましょう。

    空白のセル、参照範囲にない値を参照した際の#N/Aエラーの回避法

    簡単な例で説明します。下図は、請求書を作成するために左側の果物価格表から商品番号を検索値として、商品名、単価をVLOOKUP関数で入力するようにしたものです。

    黄色背景の列には「=VLOOKUP($F3,$A$2:$C$8,2,FALSE)」というようにセルF3に入力された値を$A$2:$C$8のA列から検索して、対象の行の2つ目の値(商品名)を返すためのVLOOKUP関数式、緑背景の列には「=VLOOKUP($F3,$A$2:$C$8,3,FALSE)」、3つ目の値(単価)を返す関数式となっています。

    例のように、F列にA列の値があれば問題ありませんが、F列に空白やA列にない値が入力されると下図のように#N/Aエラーが表示されます。 ※J列はVLOOKUP関数は使っていませんが、単純に単価×数量の式なので、当然エラーとなります。

    エラー回避はIFERROR関数(EXCEL2007以降)を使います。セルG5を例にとると、「=IFERROR(VLOOKUP(F5,$A$2:$C$8,2,FALSE),””)」と入力することで、エラーがあった場合、空白にするという関数式になります。G列、H列ともにIFERROR関数を使って置き返したのが下図です。

    価格の部分#VALUEは空白に乗算しているので発生するエラーです。こちらもIFERROR関数を使って「=IFERROR(H7*I7,””)」というようにし、「エラーが発生すれば空白を表示する。」としておけば、エラーは表示されません。

    入力規制で存在しない値を入力出来ないようにする

    今回の例でいえば、F列に品番を入力する際、A列にある品番を入力するのですが、全角で入力してしまったり、スペースを入れてしまったりと人間がやることにはミスがありがちです。これを回避するためにA列無いものは入力出来ないように規制しておけば、エラーの回避になります。

    入力規制の方法は、A列の値の範囲A3:A8を選択しておいて、左上の名前BOXに品番と入力し範囲の名前を付けておきます。

    続いて、F列の品番入力欄F3:F8を選択し、リボンのデータタブから、入力規制を選択します。

    入力値の種類の「リスト」を選択し、元の値の欄に「=品番」と入力し、OKをクリックします。

    これで、間違った値を入力しようとすると、下図のようなエラーメッセージが表示され入力ミスを回避することが出来ます。

    その他、エラーを未然に防ぐテクニック

    VLOOKUP関数を使って大量のデータを入力していく際に、例えば住所録など、同じ名前が2回入力されないようにする必要があります。こういった場合はCOUNTIF関数を使い重複したデータを入力することを防ぎます。

    COUNTIF関数の式は「=COUNTIF(範囲,検索条件)」と書きます。

    エラーが出てしまった場合のチェックポイント

      • 参照するセルの範囲がオートフィルやコピペでずれていないか
      • 入力する値が参照する値にあるか
      • 入力した値が半角や全角、スペースが入っている

     

      (※コピペで入力すると文字の前後にスペースが入ってしまっているという事がよくあります)など参照する値と一致しているか

    VLOOKUP関数の式が間違っていないとすれば、ほとんどが上記にある単純ミスのはずです。

    まとめ

    VLOOKUP関数を使いこなせば、作業効率がアップすること間違いなしです。さらにエクセルを熟知した「出来る人」という尊敬も集められるに違いありません。是非、習熟しておきましょう。

    この記事が気に入ったら
    いいね ! しよう



     トイロハのおすすめ記事