「#N/A」「#REF!」 VLOOKUP関数でエラー値が返ってきた時の対処法

エラー値が返ってきたときにどう対応すればよいのか

おさ氏：はい、みなさんこんにちは。今日はVLOOKUP関数の第2段をやっていきましょう。前回の動画で、VLOOKUP関数を実際に指定することができるようになりました。今回の動画では、その指定したVLOOKUP関数の返ってきた値が仮にエラー値だったときに、どういう風に対応したらいいのかといことを学んでいきます。

さっそくワークシートに移っていきましょう。

では、こちらのワークシートをご覧ください。

今日は皆さんがExcel 2007以降のバージョンを使っている前提でお話しを進めて行きます。仮に2007以前のバージョンを使っている方がいらっしゃいましたら、ちょっと使う関数が異なってくるので、そのときはニーズがあればコメント欄に「こんなとき、どうすればいいのですか？」と教えてください。よろしくお願いします。

では見ていきましょう。こちらには前回用いたのと、まったく同じリストをご用意しています。商品名にひも付いた商品番号、旬月、仕入れ値が書かれていますね。四列分ございます。

このリストから、右側の表に引っ張ってきています。商品名に、仕入れ値。VLOOKUP関数でここに引っ張ってきています。式はこんな感じです。

そしてこれらの費用合計がいくらなのかを掛け算して算出しています。こんな感じです。「I3*J3」と。

仕入れのところに実際に組み込まれているVLOOKUP関数は、1が、正しい値、これですね。数式、見やすい値に書かれています。正しい商品名、範囲指定、列指定が行われています。

「#N/A」「#REF!」さまざまなエラー

今日はエラー値が返ってくる時をみたいので、どのような時にエラー値が返ってくるのかを2、3、4に指名しています。

間違えた商品名を入力した場合（2）、間違えた範囲指定をした場合（3）、間違えた列指定をした場合（3）、それぞれこういう（#N/A、#REF!）ようなマークのエラー値が返ってきます。

「#N/A」とは、Not Available value。「値がないですよ」ということを教えてくれます。

「#REF!」は、a REFerence to a cell that does not exist。要するに「参照したいセルがエクセル上に存在しないよ」ということを教えてくれます。

エラーが返ってくる場合を見ていきましょう

では、このエラー値が返ってくるところを、実際に見ていきましょう。

上に、正しい値を置いておきたいので今ちょっとコピーして下の方に入力しておきたいと思います。ではこちらにVLOOKUP関数を入力していきたいと思います。

前回とまったく一緒ですが、検索値としては商品名（えび）のところです。H11を指定して、範囲はこの4列分をしっかり指定してあげます。列番号としては、4列目が仕入値ですので4。検索方法は完全値の0ですね。

これでエンターを押すと、上とまったく同じ数式ですので同じ値にすべて返ってきています。

ですが（ここですね。エラー値）、例えば2のパターンをやってみましょう。間違えた商品名を入力してしまった場合。つまり間違えた検索値ですね。H11の値をちょっと変えてみましょう。えび、とひらがなで書いて入力されていますが、仮にですね、エビをカタカナで入力します。

これでエンターを押すと、（仕入値に）エラー値が返ってきましたね。#N/A。これなんでかというと、商品名がカタカナでエビというのはこのリスト上、一番左の列に存在しないからです。ひらがなは存在するけれど、カタカナは存在しません。

エラー値を見せない2つの方法

こういう時にエラー値が返ってくるんですけれど、よくあるパターンとして、エラー値が出てきたときに、「エラー値を見せたくない」というのがあるんです。

代わりに、「空白にしておきたい」というパターンと、あるいは"商品名が違うよ！"みたいな「文字列を変えさせる」パターン、２つのパターンがあります。

その時は、Excel2007以降のバージョンを使っている方は、IFERROR関数というものをVLOOKUP関数の前に入力してあげるといいんです。

IFERROR関数、まだ具体的に勉強していないんですが、2つ引数を指定します。値と、エラーの場合の値です。

どういうことかというと、一つ目の引数に指定した値、これが仮にエラー値だった場合、エラー値を返してきた場合に、二つ目の引数で、例えば空白の場合「空白を返してね」、というように指定することができます。

今、エンターを押すと、空白が返ってきました。先ほどエラー値だったものが、空白に変わりました。

文字列を返すことも可能

空白だとわかりにくいので、ここに"商品名が違います"と返させるようにしましょう。

そうすると、"商品名が違います"が返ってきましたね。

元の「えび」というひらがなに変えてみると、先ほどと同様に数値が「1,542」と返ってきます。

間違えたものを入力すると、エラー値が返ってきます。

「#VALUE!」は不適切なデータが原因

右側の「#VALUE!」というものがあります。これもエラー値の一種で、これは「不適切なデータが組み込まれています」というものです。

数式「I11*J11」としているけれど、「どっちかが不適切ですよ」もしくは「両方不適切です」ということです。

例えば、今回の場合は、テキスト×数値、文字列×数値になっているので不適切といわれています。

このような場合にでもまったく同じパターンで、IFERRORと打って一つ目の数式を一つ目の引数に置いて、エラーの場合の値を空白にして、カッコで閉じてエンターを押してあげると……

こういう感じで空白になり返ってきてくれます。

ここにもう一回「えび」とひらがなで打ってあげると、「1,542」「4,626」とまったく上と同じ値が返ってくるわけです。

こういった感じで、「エラー値があるときには二つの対応方法があるんだな」ということを覚えておきましょう。

2、3、4とありますが、今日は2だけを見てきましたが、3、4もすべて同じ対応になります。忘れずに覚えて置きましょう。どうもありがとうございました。

エラー値への対応は、ほかの関数にも応用可能

はい、皆さまお疲れさまでした。今日は「VLOOKUP関数でエラー値が返された時に、どういう風に対応すればいいのか？　」というのを勉強してきました。

具体的には「VLOOKUP関数の前に、IFERROR関数というものを置いてあげる」ことで、エラー値の時に例えば空白を返させたり、あるいは自分が指定したい文字列を返させることが可能になります。

VLOOKUP関数の場合をみてきたのですが、ほかの関数でもエラー値がはじき出されることが存在します。こういう時にもまったく同じ方法で対応できるので、この方法をしっかり覚えておくようにしましょう。