月: 2020年6月

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