Excel関数は組み合わせて使う VLOOKUP関数を「〜以上」「〜以下」の条件に対応させます

シェアする

  • このエントリーをはてなブックマークに追加

VLOOKUP関数の弱点に一つに、「〜以上」「〜以下」のような幅のある条件指定ができないことがあります。ですが、この弱点は、他の関数を組み合わせることで克服することができます。

スポンサーリンク
自動サイズレスボンシブ

VLOOKUP関数の弱点

VLOOKUP関数は、


①見つけたいデータ


②調査するセルの範囲


③表示するデータの場所 
=①と一致するデータがあるセルから、いくつ分右に進んだセルか


④検索の型


この4つの要素でできた関数です。


たとえば、次のような表があるとします。

スクリーンショット 2015 02 27 21 33 43


「①見つけたいデータ」を「木曜」 
「②調査するセルの範囲」を、上の表の範囲($B$4:$C$8)に指定すると、


「①見つけたいデータ(木曜)」に一致するデータがあるかどうか、表の一番左の列に調べてくれます。

スクリーンショット 2015 02 27 21 45 30


「①見つけたいデータ(木曜)」に一致するデータが見つかったら、

スクリーンショット 2015 02 27 22 01 41


「③表示するデータの場所」で指定した、セルの個数分、に進んだセルのデータを表示してくれます。

スクリーンショット 2015 02 27 22 01 03


この場合、関数は次のようになり、 
(「④検索の型」は、一致するデータが無ければエラーになる”0”にします)


=VLOOKUP( “木曜”, $B$4 : $C$8 , 2 ,0)

スクリーンショット 2015 02 27 22 09 12


このように正しく表示されます。


以上がVLOOKUP関数の仕組みですが、この関数にはいくつか弱点があります。


そのひとつが、「①見つけたいデータ」を探すのに「20以上」「30以下」のような”幅”を持たせることができないこと。


数字であることは構わないのですが、「20」や「30」のように一つに決められたデータしか、探すことができないのです。


ただし、この弱点は、他の関数と組み合わせることで克服することができます。


ROUNDUP関数です。

ROUNDUP関数で、VLOOKUP関数の弱点を克服

次の表を見て下さい。

スクリーンショット 2015 02 27 22 43 27


マラソン大会で、整理番号に応じて、スタートするエリアを決めている表です。


表の下に、整理番号367番とありますので、VLOOKUP関数で367番に該当する範囲を見つけて、スタートリアを表示させることを考えます。


VLOOKUP関数の要素をもう一度確認すると、


①見つけたいデータ


②調査するセルの範囲


③表示するデータの場所 
=①と一致するデータがあるセルから、いくつ分右に進んだセルか


④検索の型


でしたが、VLOOKUP関数で探せるのは、「①見つけたいデータ」とぴったり一致したデータだけでした。


「①見つけたいデータ」を「367番」として(実際には「367番」が入力されている「C9セル」)、VLOOKUP関数を完成させても、


表に「367」はないので、

スクリーンショット 2015 02 27 22 56 40


「N/A」と表示されます。 
(「N/A」は「『①見つけたいデータ』は見つかりませんでした」というエラーメッセージです)


そこで、「①見つけたいデータ」を「367番」から、 
表にある「400番以下」の形に変換することを考えます。


ROUNDUP関数を使います。

ROUNDUP関数 数値を切り上げて表示してくれます

ROUNDUP関数は、数値を切り上げて表示してくれる関数で、


①数値


②小数第何位を切り上げるか


の2つを入力すればOKです。


たとえば、「42.195」をを小数第2位で切り上げて表示する場合、 

=ROUNDUP(42.195 , 2)


と入力すると、

スクリーンショット 2015 02 27 23 16 29


小数第2位の9が切り上げられて、「42.2」と表示されます。


「①数値」については、直接入力するだけでなく、数値が入力されているセルを指定することもできて、

スクリーンショット 2015 02 27 23 20 11


同じように表示させることができます。

「367番」「400番以下」を数値で表示する

ROUNDUP関数で「367番」を「400番以下」と表示させましょう…、と言いたいところですが、「」「番以下」はどうするのか、と言う問題があります。


別の言い方をすると、ROUNDUP関数で「367」という数値を400にすることはできますが、「367番」は文字列(文字データ)なので、


「400番以下」はもちろん「400」にさえできないということです。


この点については、セルに入力する時に、「番」「番以下」を入力しないで、「367」「400」と数値を入力することで対応します。


では、「番」「番以下」は諦めるのかと言うとそうではなく、セルの書式設定の「ユーザー定義」を使って、


「367」を「367番」、「400」を「400番以下」と表示させます。


具体的には、「セルの書式設定」を開いて(「ctr+1」のショートカットで!)、「表示形式」タブにある「分類」から、「ユーザー定義」を選択。


「種類」の欄で、「0”番以下”」のように、 
「0」のあと「” ”」で囲んで、表示させたい内容を入力すれば、数値データのまま、表示させたい形式で表示させることができます。

スクリーンショット 2015 02 27 23 41 46


これで、「367」を「367番」、「400」を「400番以下」と表示させることができます。

ROUNDUP関数をVLOOKUP関数に組み込む

表と整理番号についてもう一度整理しておくと、


表の「100番以下」から「500番以下」は、ユーザー定義で「番以下」を表示させているだけで 
セルには「100」「500」と言った数値が入力されていて、


整理番号の「367番」も同様に、「番」はユーザー定義で表示させているだけで、セルには「367」と数値が入力されています。


これを前提にすると、「①見つけたいデータ」をROUNDUP関数で「367」から「400」に変換すれば、 
表にある「400番以下」のセルと一致することになります。 
(「400番以下」の「番以下」はセルの形式で表示させているだけで、セルには「400」と数値が入力されているため)


スクリーンショット 2015-02-28 0.03.53


VLOOKUP関数の式をまとめると、


①見つけたいデータ:ROUNDUP(C9,-2) 
 ※10の位を切り上げる場合は「-2」


②調査するセルの範囲:$B$2 : $C$7 
 ※表の範囲


③表示するデータの場所 
=①と一致するデータがあるセルから、いくつ分右に進んだセルか : 2 
 ※「スタートエリア」は表の一番左の列の右隣。なので”2”


④検索の型:0 
 ※一致するデータが無ければエラーになる”0”


こうなります。 
実際に関数を入力してみると、


スクリーンショット 2015-02-28 0.37.54


正しく、「AREA D」と表示されました。

まとめ

VLOOKUP関数は、ROUNDUP関数と組み合わせることで「〜以上」「〜以下」のデータを探すことができます。 
その際、セルの書式設定の「ユーザー定義」で表示方法を変更できることを知っておくと、さらに使いやすくなります。


<おまけ>
VLOOKUP関数で「〜以上」「〜以下」のデータを探す方法としては、IF関数を使って独自コードを割り当てる方法を過去にご紹介しました(参考;”重複データ&複数条件」でもVLOOKUP関数でリストを作れます”)。 
少し面倒ですが汎用性は高いので、今回よりも条件が複雑な場合は、そちらも試してみて下さい。

スポンサーリンク
自動サイズレスボンシブ
自動サイズレスボンシブ

シェアする

  • このエントリーをはてなブックマークに追加

フォローする