vlookupErrorサムネ

Excel

【Excel関数】vlookupがエラーで使えない!原因と回避方法7パターン

vlookup関数が#N/A、#NAME?などよくエラーになってしまいますよね(サムネほど頭を抱えることはないと思いますがw)。vlookup関数は使いこなせれば作業効率は大幅に上がりますが、そうでないと時間を奪われる残念な関数になってしまいます。vlookup関数がエラーで使えないときの対処法をまとめています。理解がもう一歩かもという場合はvlookupの基本的な使い方参考にしてください。

1.関数の表記(スペル)が間違っている

このエラーは"#NAME?"と表示されます。vlookupと正しく入力してください。

馬鹿にしているわけではありません。データサイエンティストとして10年、BIツールや統計データとにらめっこしてきた私でもたまにありますw。vloolup、vkookup、vkoolup、vloopupなどパッと見ただけではどこが間違っているかわからないことも・・・。急いでいるときに限って、こんな基本的なミスをするんですよね。入力のコツは =vl と入力したところで候補リストが出てきたら[tab]キーで候補を選択することです。

作業効率を追求していくと、PC操作はできるだけキーボードで完結させ、マウスを使わないようになります。そうなると、Excel関数の入力もマウスを使わずキーボード操作で行うことが有効です。

2.引数が足りない(足りなくてズレている)

4つの引数(ひきすう)を正しく指定できていない時に表示されるエラーも#NAME?です。具体的には、
 (1)引数の1つ目もしくは2つ目に、セル指定以外を設定している場合(数字、アルファベット)
 (2)引数の3つ目に、数字以外を設定している場合
 (3)引数の4つ目に、TRUEもしくはFALSE以外を設定している場合
が考えられます。

 

vlookupの引数(ひきすう)は次の通り4つの引数で構成されております。なお、4つ目の引数[検索方法]は任意ですが、実務上はFALSE-完全一致を使うことが大半です。4つめの引数は省略も可能ですが、省略すると自動的にTRUE-近似一致の設定になってしまいます。TRUE-近似一致の場合には、想定していない結果になることがありますので、注意が必要です。(最後の項で説明していあります。)

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

 

引数が5つ以上と多い場合は、
vlookup引数不足
と"引数が多いですよエラー"が出ます。

逆に少ない場合はエラーが出ずに、最後の")"(カッコ)を閉じられます。見た目上は引数を3つしか設定していなくても、途中の","(カンマ)が多すぎて、3つ目の引数がブランクで、4つ目の引数ずれこんでしまっていることもあります。こんな感じで→ =vlookup(A2,E:I,,3)

#NAME?のエラーが出ているときは、vlookupの表記、","(カンマ)が余分に設定されていないかを確認しましょう。

3."検索値"の表示形式が異なっている

vlookupのエラーで一番多いのがこれ。私のところに"上手にできないんでけど~"と相談に来る人の8割程度はこれです。

表示形式とは、これのことです。
表示形式

具体的には、"検索値"と"範囲"の最左の表示形式が異なると、データが連動しません。見た目上同じに見えてわかりづらいのが、数字と文字列で異なっているケースが多くあります。

以下の例は、"検索値"が文字列、"範囲"の最左が数字となっているため、vlookupのそれ以外の引数は正しいのですが#N/Aのエラーになってしまっています。見た目上、同じ1でも、文字列の"1"と数字の1はExcel上での認識が異なります。
表示形式相違

見た目で分かる場合もあり、文字列の場合はセルの左寄せ数値の場合はセルの右寄せになっています。文字列でも、書式設定で右寄せにすれば、見た目上は数値と全く同じになってしまいまう場合があるので注意が必要です。この場合の解消方法としては、どちらかの表示形式を、もう一方の表示形式に合わせることです。

表示形式をあわせる方法は
(1)文字列→数字 … =文字列×1
(2)数字→文字列 … =Left(数字,必要な桁数)
と2通りあります。

変換したデータをデータシートに1列追加(または挿入)して、追加した列を"検索値"とする
表示形式修正1

処理でもよいですが、データ容量が増えてしまうのでvloolup関数の1つ目の引数内で
表示形式修正2

と処理することも可能です。

4."範囲"を超えた"列番号"になっている

このケースの場合、#REF!とエラー表示されます。ちなみにこのエラーのREFは、Referenceの先頭3文字で、参照、照合という意味を表しています。Excel関数で同様に何かを参照する関数では、このエラーが発生することもあるので、覚えておくとよいでしょう。

以下の例の場合、"範囲"で選択しているのがA~C列と全部で3列しかないにも関わらず、"列番号"として6を設定しています。
範囲超過

 

解消方法は、"範囲"内で"列番号"を指定する、もしくは"列番号"が含まれるように"範囲"を拡張することです。このエラーのポイントとしては、「"範囲"で設定している列数」<=「"列番号"」となります。

 

5."範囲"内に"検索値"がない

このケースの場合、#N/Aとエラー表示されます。N/Aは、Not Available valueで、入手できる値が無いということを意味しています。

参照先のシート、この場合シート[人種]には、人物No.20、44、0のデータがありません。式が正しく設定されていた場合でも、参照先にデータがない場合は#N/Aと表示されます。
データ無

 

関数自体は正しく、参照するデータがないこの場合には、エラー処理を施す必要があります。この場合のエラー処理としては、参照先の値がない場合○○○とする。という処理です。エラー処理には、IFERROR関数を使用します。

=IFERROR(式,エラーの場合の値)

 

IFERRORの引数1つ目の式には正常に機能していた式を、エラーの場合の値には"不明"という文字列を指定します。すると、
データ無修正

と、参照先データがない場合はこのように不明と表示することができます。

 

6.[検索方法]がTRUE-近似一致になっている

vlookupの4つ目の引数[検索方法]は任意で省略可能ですが、省略した場合はTRUE-近似一致が設定されているものとして扱われます。

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

 

4つめの引数をTRUE-近似一致にすると、"検索値"と異なる値の参照先でもデータを参照してしまいます。前項まででエラー処理まで組み込んでFALSE-完全一致で作成したデータと並べてみましょう。C列がFALSE-完全一致、D列がTRUE-近似一致のデータです。
類似一致

D列のTRUE-近似一致のデータでは、人物No.20や44に一致する名前がないのですが、人造人間と表示されています。完全一致では、エラー処理のエラーケースに該当するために"不明"と表示されていましたが、異なる結果となっています。

類似一致のルールは奥深いため理解は不要ですが、人物No.20や44は、シート「人種」の人物No.の中で最初の2桁のデータ(20号)の人造人間を表示しています。類似一致のルールを理解して、意図的に使うケースもありますが、よっぽどのプログラムではない限りFALSE-完全一致を設定すればよいです。

 

7.検索値もしくは範囲のどちらかにブランクがある

画面上では違いがわからないこともしばしば。例えば、以下の2つはどこが違うでしょうか。

A:孫悟空
B:孫悟空

違いは、Bは孫悟空の後ろに1桁分のブランクがあることです。わからないですよね。excel上では、上記のAとBは別ものとして認識されるのでvlookup関数が連動してくれないことがあります。

この解決方法は一斉置換でブランクを消すです。ショートカットキーを使って[Ctrl]+[H]で以下のウィンドウがでます。1行目にだけブランクを有力して、左下のすべて置換をぽちり。すると見えないブランクがすべて消し飛びます。
vlookup-error7

 


 

vlookupって使いこなせないと、時間の浪費関数になってしまいます。私がデータ屋としての経験をモリ盛り込んだvlookupの基本的。使い方が難しい関数こそ基本が大切!参考にしてください。

 

スポンサーリンク
スポンサーリンク

関連記事

  1. カレンダー

    Excel

    マクロを使わない自動罫線のExcelカレンダー(2018年度/2019年度)

    個人での予定管理から、プロジェクトのタスク管理まで、カレンダーはあらゆ…

  2. Excel

    【Excel関数】住所情報から都道府県を抽出する方法

    "東京都千代田区1-1-1"のように連結した住所情報から都道府県だけを…

  3. vlookupサムネ

    Excel

    【Excel関数】応用の前にまずは基本!vlookupの使い方

    vlookup関数は、別シートや別表のデータを参照できる関数です。でも…

  4. Excel

    【Excel】見やすいきれいな折れ線グラフの作り方

    今回のビズ技は、基本中の基本。グラフを見やすくするコツを紹介します。基…

  5. Excel

    【Excel関数】年齢を年代に3秒で IF文を使わず簡単変換

    年齢を年代に変換するとき、IF文を使っていたりしませんか?INT関数を…

  6. Excel

    【Excel】重複を抽出して削除する方法(Countif 他全4種)

    "重複チェック"や"重複削除"の方法は、ひとつではありません。その方法…

スポンサーリンク




特集

  1. 折れ線グラフ特集1
  2. 特集働き方改革サムネ
  3. SC特集サムネ

スポンサーリンク




仕事効率アップ系記事

  1. Altキーサムネ
  2. 働き方改革-職場
  3. ショートカットサムネ
  4. カレンダー

スキルアップ系記事

  1. not_dateadd
  2. vlookupサムネ
  3. 新社会人-エクセル
  4. 体重計

スポンサーリンク




ピックアップ記事

  1. 管理職マネジャー
  2. プレミアムフライデー
  3. サムネイル
  4. 愚痴-サムネ
  5. regret
  1. SC特集サムネ

    PC

    【特集】ショートカットキーで仕事効率化!働き方改革の小さな一歩👣…
  2. 折れ線グラフ特集1

    PC

    【特集】"使い方"では足りない!「折れ線グラフ」の"使いこなし方"
  3. 特集働き方改革サムネ

    Column

    【特集】「働き方改革」を独り歩きさせない!「働き方改革」の実現に向けて
PAGE TOP