キーから値を抽出 VLOOKUP 2

VLOOKUP関数は便利で使用頻度も高いと思います。完全一致で値を抽出する、キーから値を抽出 VLOOKUPを参照してください。

今回は、近似値を返す場合をご説明します。

vlookup1

お買い上げ金額により値引き率を変更する場合を考えます。上の図は、1000円買っていただいたら、2%値引き、5000円買っていただいたら、5%の値引きするという意味だと思ってください。A列にある金額と、ちょうど同じ金額をお買い上げなら隣のB列の値を参照すればよいですね。では、例えば3000円買っていただいたら何パーセントの割引率を適用すればよいでしょうか。1000円から5000円未満までは、2%の割引率にします。同様に5000円以上10000円未満は5%が割引率になります。

この時、VLOOKUP関数を使って、うまくB列の割引率が求められるでしょうか。キーから値を抽出 VLOOKUPでは完全一致でしたので、3000円の場合、3000という数値がA列にないのでエラーになってしまいます。1円刻みで表を作っておけば完全一致も可能でしょうが、とっても大変なことになります。

このような場合もVLOOKUPが使えます。A列に金額を入れるとB列に値引き率が返ってくるような計算式をセルB2に入力します。

vlookup1_1

セルB2に =VLOOKUP(A2,$E$1:$F$8,2,TRUE) と入力します。ポイントは第4引数がTRUEとなっているところです。第4引数がFALSEの場合は完全一致になりますが、TRUEの場合は、完全一致で検索し、一致するデータがない場合は、検索値未満の最大値が使用されます。

vlookup1_2

まず、セルA4に3000という値があったら、E1からE8で3000という値を探します。ありませんので近似値を求めます。検索値未満の最大値ですから、3000未満で最大値は1000です。次にVLOOKUP関数の第3引数の2を参照し、第2引数の範囲の2列目の値を返しますので2%という数値が返ってきます。(表に1行目の見出しを含む必要はありません。)

vlookup1_3

第4引数のTRUEを使うときは、参照する表(今回は、$E$1:$F$8)の左端列が昇順に並んでいないと正確な結果が返ってこないことがあります。($E$1:$F$8の絶対参照はコピーするときのためで必須ではありません。)

TRUEの場合は、第4引数を1としても同じです。また、TRUEの場合は第4引数を省略できます。

コメントを残す

メールアドレスが公開されることはありません。