カテゴリー: VLOOKUP

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をマスターして、仕事の業務効率化をしましょう

VLOOKUP（ブイルックアップ）は指定した値を参照し、すぐ隣にあるデータを引っ張ってくる関数です。この関数を使いこなせば１つの値を入れればデータを複数個飛ばすことができるのでデーター入力の効率化ができます。

用途
・情報整理
・売上票の作成
・データーベースからの参照

関数

=VLOOKUP（①検索値, ②範囲, ③列番号, ④検索方法）

関数の説明
①検索値　入力したデーターの参照元になる値を入れます。
②範囲　　データーベースの表の範囲を指定します。
③列番号　データーベースの左から数えた番号を入れます。
④検索方法　FALSEまたはTRUEのどちらかを入れます。

サンプルとしまして、商品情報を入れれば自動で売上金額を自動で計算される表を用意しました。

こちらをクリックしてダウンロード


サンプルでの表の見方
データーベース部分

左側にある部分がデーターベースの部分になります。

VLOOKUP関数が入っている箇所は右側のデーター入力箇所の黄色に塗ったところになります。

茶色の情報入力欄の部分に各項目を入れると自動で黄色の値（商品名と単価）が表示され、緑色の金額が自動で計算されるという仕組みになっています。

黄色の表のL5にはこのようにVLOOKUP関数を入れています。

=VLOOKUP(K5,$D$4:$F$9,2,FALSE)
これを分解すると・・・
①検索値　→K5
セル番号K5に入っている商品コード１と、範囲で合致した値を検索する。

②範囲　　→$D$4:$F$9
データーベースの表D4からF9までの範囲までを参照する。

③列番号　→2
データーベースの２列目の値（商品名）を出すこと。

④検索方法→FALSE
一致しないものはエラーを表示する。

としています。

隣のM5には・・・
=VLOOKUP(K5,$D$4:$F$9,3,FALSE)
が入っています。

①検索値　→K5
セル番号K5に入っている商品コード１と、範囲で合致した値を検索する。

②範囲　　→$D$4:$F$9
データーベースの表D4からF9までの範囲までを参照する。

③列番号　→３
データーベースの３列目の値（単価）を出すこと。

④検索方法→FALSE
一致しないものはエラーを表示する。

関数の引用の画面だとこのような形になります。

L5とM5の関数が出来上がれば、オートフィルで下に式をコピーすれば検索値が自然と移動するので簡単にできあがります。

・データーベース作成の注意点
①検索値の値を左側のセルに持ってくること
データーベースを作成するには次の注意点が必要です。VLOOKUP関数では①検索値から右側にある値しかもっていきません。そのため、①検索値になるようなコード類は必ず左側に持っていき、必ず②範囲指定で最左にするようにしてください。検索値はデーターベースと入力項目で一致していれば記号でも文字列でも結果が返ってきます。結果が返ってくるといっても検索値にするのはできるだけシンプルな数字や英語を使ったコードにすると良いです。

②データーベースの検索値部分は同一の値を存在させないこと
データーベース内に同一のコードが存在すると、VLOOKUP関数の性質から上位にあるコードを優先して値を返すようになっています。重複させないようにしてください。

このようにデーターベース部分にコード２と書いたバナナとレモンがあったときに、データーべースから返ってくる値はデーターベース上部にあるコードのバナナを優先して値が返ってきます。レモンには他のコードとは重複しないコードを与えれば正しい結果が返ってくるようになります。

・VLOOKUP関数でよくあるエラー
TRUEとFALSEの使い分け
FALSEを使う場合
完全にデーターベースと一致するコードを参照したい場合はFALSEを使ってください。

TRUEを使う場合
検索値に近いコードで結果を求めている場合はTRUEを選択してください。（TRUEを使う用途があまり思いつかないです。）
試しにサンプルのL5の関数にて④検索方法部分をFALSEからTRUEに修正し、K5を2.4にすると2に近いバナナが表示されます。FALSEだとエラー表示になります。この幅は2.0～2.9の値であればすべてバナナが表示されます。こういった不確定な幅で検索結果を求める場合はTRUEを使うと良いです。

・②範囲指定がずれる
同じデーターベースを参照して関数を作る場合は、②範囲の指定は他のセルにコピーしたときでも、範囲がズレないように②範囲に入れたセルの部分を＆で囲うと便利です。

・③列番号の指定
列番号を指定を誤るとVLOOKUPの値の結果が誤った値が出てきてしまいます。サンプルのようにデーターベースの上に列番号を表記しておくと便利です。データーベースが２０列になるとゴチャゴチャになってしまいますからね。

VLOOKUP関数と他の関数を組み合わせて使う
＃N/Aの表示を無くす
①検索値にある値の箇所に数値が入ってないとこのようにエラー表記になってしまいます。表の作りとしては問題ありませんが、見栄えが悪いです。

これを回避するために、VLOOKUP関数と組わせでIFERROR関数を組み合わせると①検索値が空白であっても＃N/Aが表示されなくなります。
サンプルデータのL5を
=IFERROR(VLOOKUP(K5,$D$4:$F$9,2,FALSE),” “)
にすると＃N/Aの表示が消えます。
これはエラー表示だった場合にどのような表記をするかという関数を組み合わせた結果でエラー表示を消しています。
エラー部分を空白としていますが、上記の組み合わせ関数で赤く塗った箇所を
=IFERROR(VLOOKUP(K5,$D$4:$F$9,2,FALSE),”記入なし”)
とすると、無記入の①検索値に対しては記入なしと自動で表示されます。

VLOOKUP関数を作成したときに、基本では１つの検索値でないと検索できないですが、関数にちょっとした工夫を加えれば複数条件による検索が可能になります。

この表で実現したいこと
黄色の情報入力欄にて商品名（H列）と銘柄（I列）を入力したらVLOOKUP関数にてコード（J列）・単価（K列））・生産地（L列）を自動で表示させたい。

実現させる方法
通常のVLOOKUP関数では１つの条件でしか検索をかけることができません。VLOOKUP関数に検索値の欄にHとIを合体して参照する式を作成し、データーベース上に商品名を合体した式を作成すれば実現することができます。

データーベースの準備
VLOOKUO関数の性質上、検索値にあたる部分には他のコードと重複しない項目が必要になります。データーベースを一通り作成したら、固有コードを作る為にA列にはB列とC列を合体させた列を作ります。
A4にはセル同士を結合する　=B4&C4　を入れています。このセルをA10までコピペしたら準備完了です。


複数検索を実現させた式
セル番号：J4にはこのような式が入っています。
=VLOOKUP(H4&I4,$A$4:$F$10,4,FALSE)

検索値：H4&I4
&を使ってセルのH4とI4を合体させています。これをすることで商品名と銘柄をセットにして検索値として認識してくれます。

範囲：$A$4:$F$10
A4からF10を絶対参照で指定

列番号：4
データーベース4列目のコードを飛ばす

検索方法：FALSE
完全一致した値を出す


複数検索かつ未入力と情報入力誤りを表示させた式

過去の記事でVLOOKUP関数にて検索値に未入力を表示させ、情報入力誤りを自動で表示させる式を組み合わせた式を紹介しました。
これに対応させた式はこのようになります。
=IFERROR(IF(H10=””,”未入力”,VLOOKUP(H10&I10,$A$4:$F$10,4,FALSE)),”情報入力誤り”)

H10が無記入だった場合は、未入力と表示され誤った情報が入ってたら情報入力誤りと表示されます。サンプルデータの10行目J・K・Lにこの式を組み込んでますので参考にしてみてください。

未入力の場合

情報入力誤りの場合

複数検索をかけるときのコツ
複数検索する場合は情報入力欄が２箇所になってしまいます。２箇所作りたくなければ、データベース上に簡単なコードを１列作ってしまうのも一つの方法になります。単純に上から順番に１２３４５と番号を振っていくだけでも十分OKです。VLOOKUP関数の性質上、他のコードと重複してなければ結果として返ってきますので状況に応じて楽な手段を選んでみてください。

VVLOOKUP関数を使ったエクセルのシートを作るときに覚えておくと便利なテクニックを紹介します。

テクニック１
データベースシートを専用に作る
大きなデータベースを使って作業をする場合はデーターベース専用のシートを作ると良いです。
データーベースの隣に作業スペースを作っても良いですが、データーベースエリアは一度データをおいたらほとんど手を触れない場所になります。作業エリアで作業している間にうっかりとデーターベース部分のところに触れてしまったら時間の無駄になってしまいます。

テクニック２
列の絶対参照・行の絶対参照を使いこなす

列の絶対参照
下記の画像のようにC列の下に関数をコピペするときに使うときに便利です。
列の絶対参照が入っている部分はこの$の部分です。

式の中身
=$B2*$D$1
$B2→本体価格の18900円
$D$1→10%

これがあることで下にコピペしてもB列の下にコピペしてもB3やB4にも正しい結果が返ってくるようになります。

行の絶対参照
横に対して絶対参照をかける方法です。下記の画像のように右にある単価と本数をかけた場合に表を作成したものです。

E5の式の中身
=$C$5*E$2
$C$5→本数を固定
E$2→単価の１００円を指している

E$2の部分を行参照にしています。この状態で右のセルにコピーしていくと自動的にそれぞれの単価で本数をかけた結果が表示されます。

VLOOKUP関数の場合だと検索値の部分で列と行の絶対参照が有効です。

テクニック３
関数の一括置換して効率的に式を作る
下記の例で関数に対して一括置換をかけることで簡単に黄色のエリアに正しいVLOOKUP関数を作成することができます。

①検索値のセルを固定する
=VLOOKUP($I$5,$D$4:$G9,2,FALSE)
上記の赤丸の部分にこのような式を作成します。

②J5を右にセルコピーする
コピーすると自動でK列・L列に式が挿入されます。

③式を修正する
列番号が正しくなるように修正します。
K列（修正前）
=VLOOKUP($I$5,$D$4:$G9,2,FALSE)
K列（修正後）
=VLOOKUP($I$5,$D$4:$G9,3,FALSE)

L列（修正前）
=VLOOKUP($I$5,$D$4:$G9,2,FALSE)
L列（修正後）
=VLOOKUP($I$5,$D$4:$G9,4,FALSE)

修正したあとはこのようになります。

④式に対して置換する
J・K・Lに入っている関数に対して一括で置換します。
キーボードのコントロールボタン、シフトボタン、アットマークボタンを同時に押します。押すと、式の中身が表示されます。

⑤一括置換する
黄色エリアにある式に対して一括置換します。
今、これらの式が入っています。
J5
=VLOOKUP($I$5,$D$4:$G9,2,FALSE)
K5
=VLOOKUP($I$5,$D$4:$G9,3,FALSE)
L5
=VLOOKUP($I$5,$D$4:$G9,3,FALSE)

検索値の$I$5が入っている為、下にドラッグしたら全てI5を参照してしまい５行目に入ってる式と全く同じ結果になってしまいます。ドラッグしたあとに１個ずつ直すことで正しい結果を出すこともできますが、これが１００個や１０００個になれば手で直す事は困難になります。ここで一括置換をすれば簡単にドラッグ可能な式を作成することができます。J５・K５・L５をドラッグで選択し、コントロールボタンとシフトボタンとアットマークボタンを同時に押します。このような感じでセルが式の中身を表示するようになります。

次に、コントロールボタンとHボタンを押して置換メニューを表示させます。

メニューを表示したら
検索する文字列→　$I$5
置換後の文字列→　I5
を指定しすべて置換をクリックします。

置換後は閉じるを押して下さい。
J５・K５・L５に入っていた式の一部が$I$5からI5になっているはずです。これを確認したら再度コントロールボタンとシフトボタンとアットマークボタンを同時に押します。これで元に戻ります。

⑥セルのコピー
J５・K５・L５を下のセルにコピーして下さい。
これで一括で式の作成が完了となります。

応用すれば他の関数でも活用できます。複数の式の置換があればこのテクニックを使ってみて下さい。

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が表示される