VLOOKUP 関数ではキーを検索して、欲しい情報を抜き出します。でも思った行を抜き出してくれない、検索対象は一致するはずなのに、なんで違う値を抜き出してくるのだろう？ってことありませんか？ VLOOKUP 関数は、先頭から検索して初めに一致した行を抜き出します。ですから、検索の対象範囲に同じ値が複数存在すると、下にある行は抜き出してくれないのです。
そこで、検索対象の検索キーが重複していないかをチェックして、重複がある場合は『重複あり！』ない場合は『重複なし』と表示、そして、データの中の重複部分のセルの背景を赤表示して知らせる仕組みを作ります。
このシステムでは、データベースを「DB」シートに作成し、検索は「検索」シートに作成することとします。
また、検索シートでは、 VLOOKUP 関数を使う前にデータベースの重複ありを判定して、重複ありの時は、『データベースエラー』を表示するようにすることで、不用意なエラーを表示させないように処理を施します。
DBシートの作成
まず、DBシートから作成します。
DBシートは、商品コード、商品名、単価、重複チェックのカラムを作成しました。
商品コードをキーとして検索しますから、重複があってはいけません。この商品コードの重複がチェックできるように仕組みを組み込みます。
商品名およびその単価を商品コードに紐付けてレコードを構成します。
重複チェックの列は、その行の商品コードがデータベースの中に何個存在するかを表す数値です。正常であれば1となり、重複すると2、3と重複している数が表示されます。
この重複チェックのセルを元に、条件付き書式を使って商品コードの書式を変更します。
また、重複チェックの列に1以上のものが1つでもあれば、A1のセルに重複チェックを表示することとします。これも、条件付き書式を使って表示を変更します。
COUNTIF 重複チェックの条件式
重複チェックの式は、COUNTIF 関数を使います。
$A$4:$A$15の範囲にその行の商品コードが何個有るかをカウントして表示しています。
D4の数式をD15までコピーすることで、この列が完成します。
例では、A003の商品コードがA6とA14に2つ存在していますから、共に重複チェックの値が2となっています。そして、重複が3つ、4つとあるとその数値が表示されることになります。
商品コードの条件付き書式
A4:A15を選択して Alt H L2 N と順にキー入力して、新しい書式ルールを表示させ、「書式を使用して、書式設定するセルを決定」を選択し、=D4>1を入力して、書式を設定します。
例では、背景を赤で文字を白の太字としています。
条件の入力時に、D4としているのがポイントです。
A4～A15を範囲選択していますが、その中のカーソルはA4にありますね。
このときは、A4の行に関する条件として入力することになります。
そうすることで、次の行には行数をプラスしたセル番号で条件が作成されると考えてください。
また、今回は一列ですからこれで良いですが、複数の列（行全体に適用する時など）は条件式の列を固定して=$D4＞1と書くことに注意してください。
重複チェックの列の値が2の行の商品コードが条件に一致し、赤色で表示されてました。
MAX 関数 重複チェックあり表示
重複チェックの列に1つでも重複がある、すなわち1より大きい値がある場合に”重複あり”を表示させ、それ以外であれば”重複なし”と表示させます。
その判定には、MAX関数を使います。
MAX関数は、指定範囲の中で最も大きい値を返すので、全て重複なしであればMAX関数の値は1となります。それを超える場合は全て重複ありと判断できるのです。
また、セルに表示された文字が”重複あり”ならば、条件付き書式によって赤表示するようにします。
これで、DBシートの完成です。
VLOOKUP 検索シートの作成
次に、検索シートの作成です。
検索シートでは、B3に検索コードを入力すると該当する商品コードをDBシートから検索して結果をD3に商品名およびD4に単価を表示させます。
検索の結果、データベースに該当が無い場合は、空白表示とします。
そして、データベースの検索キーすなわち商品コードに重複がある場合は、検索エラーとしてエラー表示させます。
数式は、以下の様になっています。
=IF(DB!$A$1="重複あり","DB-ERR",IFERROR(VLOOKUP($B3,DB!$A$4:$C$15,2,FALSE),""))
詳しく説明します。
赤く表示しましたが、VLOOKUP関数から見ていきます。
VLOOKUP関数では、B3に入力された商品コードを、DBシートのA4:C15の範囲の一番左の列（ここではA列）から検索し、範囲の2番目（ここではB列）の商品名を取得しています。
該当する商品コードがあれば、その外のIFERROR関数は働かず、DBシートの重複が無ければIF関数も働きませんから、VLOOKUP関数の値が表示されるようになります。
もしも、VLOOKUP関数で該当なしの場合、エラーを返しますので、IFERROR関数が働き、空白が表示されます。
また、データベースが重複ありであれば、そもそも、VLOOKUP関数もIFERROR関数も働かず、”DB-ERR”とエラー表示されます。
例では、先ほどのDBシートで重複ありとなっていますから、DB-ERRと表示されています。
これで完成です。
VLOOKUP まとめ
全てエラーの場合を例にとって説明しましたが、DBシートの重複を訂正して、検索すれば普通に検索が出来ますので、やってみてください。
最初は難しそうと思うかもしれませんが、組み合わせの一つ一つの仕組みは簡単なものですから、順に作り込んでいけば、以外と簡単にできると思います。
VLOOKUP の記事については、他にもおすすめの記事がありますので、良ければ参照してみてくださいね。
それでは以上です。
コメント