【Excel】VLOOKUP関数を使って「~以上」の区切りでランク付けする

VLOOKUP関数では、特定の値を検索するだけでなく、「～以上」という区切りで検索を行い、該当する区分を特定することができます。

VLOOKUP関数とは

VLOOKUP関数では、特定の値に紐づくデータを自動で入力することができます。

たとえば、商品の注文内容に対して自動で価格を表示させる際に用います。以下のような注文内容の価格を商品リストから引用して表示させてみます。商品リストは別のシートなどにすることが多いですが、今回はわかりやすさのため同じシートにしています。

VLOOKUP関数では、検索したい値（検索値）を指定し、検索に用いるデータの範囲、列から紐づく値を指定することができます。

関数	役割
VLOOKUP(検索値,範囲,列番号,[検索方法])	検索値で指定した値を、範囲の左端列から検索。 検索値と一致するデータの左から列番号目の値を抽出し表示する。

商品Dの価格を表示させるため、以下のように数式を入力します。注意が必要なのは、指定する範囲は商品名と価格の範囲です。VLOOKUPでは、左端の列を検索するためです。

=VLOOKUP(C4,$H$4:$I$13,2,FALSE)

それぞれの数式と役割については以下の通りです。

数式	役割
C4	「商品D」という名前で検索する
$H$4:$I$13	検索に用いたい商品名と価格のデータの範囲を指定する。今回は商品名で検索したいので、商品名の列が範囲の左端に来るようにする。 他の商品も同じ範囲で検索したいので、絶対参照にする。
2	検索した結果、表示させたいデータの列を指定する。今回は価格を表示させたいので範囲の「2」列目を指定する。
FALSE	検索値と完全に一致するデータを検索する

そうすると、以下のように商品Dに該当する金額が表示されます。

セルD4の数式を商品C、商品Fの価格のセルにもコピー&ペーストすることで、それぞれの価格が表示されるようになります。

VLOOKUP関数を使って「~以上」の区切りでランク付けする

VLOOKUPでは、特定の値を指定するだけでなく、「～以上」という範囲で検索して、該当する値を表示させることも可能です。その際、VLOOKUPの検索方法は「TRUE」とします。

たとえば、以下のように注文金額によって変動する送料を自動で入力できるようにします。

まず、注文金額と送料の対応表を準備します。この際、「注文金額（~円以上）」の列は昇順、つまり小さい順に並べておく必要があります。

「田中 紘一」の注文金額「¥2,800」を算出するために以下のように数式を入力します。

=VLOOKUP(C4,$G$4:$H$8,2,TRUE)

それぞれの数式と役割については以下の通りです。

数式	役割
C4	注文金額「¥2,800」という値で検索する。
$H$4:$I$13	検索に用いたい注文金額と送料のデータの範囲を指定する。今回は注文金額で検索したいので、注文金額の列が範囲の左端に来るようにする。 他の商品も同じ範囲で検索したいので、絶対参照にする。
2	検索した結果、表示させたいデータの列を指定する。今回は送料を表示させたいので範囲の「2」列目を指定する。
TRUE	「～以上」と数値を区切って該当する区分を特定する。

そうすると、注文金額「¥2,800」に該当する送料が表示されます。この場合、\0以上\3,000未満なので、送料は\1,000です。

セルD4の数式を別の人の送料のセルにもコピー&ペーストすることで、注文金額に対応する送料が表示されます。

このようにVLOOKUPの検索方法を「TRUE」とすることで、「～以上」という数値で区切って該当する値を表示させることができます。テストの点数でランクをA~Dに分けたい場合などにも活用することができます。