タグ: エラー

VLOOKUP関数が完成したものの人為的なミスで、情報入力する箇所（検索値）に、未入力及び検索値に誤った情報を入れるとミスであることを気づきやすくする方法を紹介します。

下記の赤丸のJ5に入れる関数を例として紹介します。

通常の方法
J5には通常のVLOOKUP関数であればこれだけ入れれば作動します。

=VLOOKUP(I5,$D$4:$F$9,2,FALSE)

未入力だけを表示させる方法
もっと便利にする為に、検索値のI5が未記入の場合は未記入であることを表示させたい場合は次の関数を追加します。

＝IF(I5=””,”未記入”,VLOOKUP(I5,$D$4:$F$9,2,FALSE))

赤字の部分が追加した関数です。
IF関数を追加しています。
もし、I5に値が入っていなければ未記入（真の場合）と表示させ、値が入ってた場合（偽の場合）はVLOOKUP関数を処理するという内容になっています。赤字の未記入と記載されている部分の文字を任意の表記にしても問題ありません。値に絶対入力が必要な場合に役に立ちます。入力漏れがなくても支障がない場合は未記入となっている部分は、””にすると検索値欄（I5）が空白だったときに♯M/Aではなく空白の値を返してくれ見栄えが良くなります。

データベースに載ってない値を入れた場合は”情報入力誤り”と表示させる方法

=IFERROR(VLOOKUP(I5,$D$4:$F$9,2,FALSE),”情報入力誤り”)

赤字の部分が追加した関数です。IFERROR関数を追加しています。もし、I5に検索値のエラーがあった場合は”情報入力誤り”と表示させるという内容になっています。赤字の”情報入力誤り”と記載されている部分を任意の表記にしても問題ありません。また”情報入力誤り”という部分を無表記　””　だけにすると、エラーがあった場合は空白として表示するようになります。

検索値が未入力の表示かつデータベースに載ってない値を入れた場合に”情報入力誤り”と表示させる方法

上記2つの関数を組み合わせたものです。
=IFERROR(IF(I5=””,”未入力”,VLOOKUP(I5,$D$4:$F$9,2,FALSE)),”情報入力誤り”)

黒文字→本来のVLOOKUP関数
赤文字→空白だった場合に未入力と表示させる部分の関数
青文字→データベース上に存在しない値があった場合に情報入力誤りと表示させる

入力を絶対に間違えたくない場合はこの関数を使えば入力漏れミスを減らせると思います。

VLOOKUPでエラー表示が出たとき下記のチェックをしてみてください。

＃N/Aが表示されてしまう。

原因①　データーベース上に値がない。
完全一致もしくは近似値の値が見当たらない可能性があります。

→対応
範囲指定した中に検索値が含まれているか、検索値が左側のセルに存在するか確認してください。下記の例だと検索値にデータベース上に存在しない商品コード9・8・7を入力していることが原因です。データーベース上に9・8・7のコードをもたせたデーターを追加するか、商品コードをデーターベースの中にある数値を入れることで解消されます。

原因②　検索値に値がない
データ入力箇所に検索値に値が入ってない場合は＃N/Aが表示されます。

→対応
回避する手段としてIFERROR関数と組み合わせると良いです。検索値にコード入力がされてないとVLOOKUPでは値がないことでエラー表示となってしまいます。

J列に入っている式にはこのような関数が入っています。
=VLOOKUP(I5,$D$4:$F$9,2,FALSE)

ここにIFERROR関数を入れると
=IFERROR(VLOOKUP(I5,$D$4:$F$9,2,FALSE),””)
検索値に値が空白であってもエラー表示されなくなります。


原因③　検索値とデーターベースの値が完全一致していない
データーベースにある値と入力欄の値が一致していとVLOOKUPでは違うデーターとして認識し正しい結果が返ってきません。

→対応
必ずデーターベースと一致するものを入力するようにしてください。
下記の画像のようにりんごは一致しますが、リンゴやﾘﾝｺﾞはエラーとなります。データーベース上に存在する”りんご”を入れるようにしてください。

原因④　値が近似値になっている。（FALSEを指定している）

検索値の値がデーターベース上にある値と異なっているか、近似値になっていることが原因です。

→対応
検索値を数字として、データーベース上の数字を参照して呼び出しているときに起きる現象です。VLOOKUP関数の検索方法をTRUEにするか、データーベース上にある数値と一致させてください。

検索方法をFALSEを使うならば商品コードをデーターベース上にある数値と同じにしてください。

検索方法をTRUEを使うことで、商品コードが近似値となっていて合致しているものを検索結果として結果が返ってきます。下記の画像のようにデーターベース上に存在しない1.5を使っても近似値となる”りんご”が結果として出てきています。

原因⑤　データーベース上の並び替えをしていない（TRUEを使っている場合）

VLOOKUPの性質上、データーベースに存在するデータの上にある数値から値を返してきます。

↓の画像だと、商品コード１を選択した場合、データーベースの上から順番に見ていった場合１に該当するコードは存在しないことになっています。検索値に２を入れた場合、２から３．９までが”りんご”という値を返してきてます。

→対応
データーベース上の値を並び替えする。データーベース上のコードを昇順で並び替えすることで正しい結果が返ってきます。

並び替えの仕方
①データーベース上の並び替えしたい範囲をドラックで選択する

②上のタブからデータ→並び替えをクリックします

③並び替えのメニューが出てきたら最優先されたキーの箇所に検索値となる列を指定します。順序は昇順を指定し、OKを押します

④正しく入れ替わったか確認する
並び替えが正しくできているか、検索値の値が正しいものが出ているか確認してください。

→対応②
検索方法をTRUEではなくFALSEに修正してください。

原因⑤各種項目の指定が誤っている
検索値→誤った箇所を指定している。
対応：正しい箇所を指定してください。

範囲→範囲指定が変な所を設定している。
対応：範囲を見直してください。コピペして範囲がズレた場合は範囲をセルを$で囲み絶対参照にしてください。

列番号→範囲からはみ出た番号を指定している。
対応：範囲で指定した列番号にしてください。

＃REF!が表示される
原因：参照している先の値（セルや行・列・シート）を削除した。

対応：正しい式を組み直すか、削除する前の状態に戻して下さい。VLOOKUP特有の問題ではないですが、うっかり１列削除してREFが大量に表示されたときはびっくりしてしまいますね。

例：検索値にあるJ列を削除すると・・・

こんな感じに検索値がなくなった為、REFが大量に出現してしまいます。

VALUEが表示される