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関数でリストを作れます”)。 
少し面倒ですが汎用性は高いので、今回よりも条件が複雑な場合は、そちらも試してみて下さい。

タイトルとURLをコピーしました