「○以上△未満」の条件で表引きする(近似検索)…VLOOKUP関数

投稿日:2018年6月23日 更新日:

[Excel 2016/2013/2010/2007]

VLOOKUP関数では、「○以上△未満」の条件で表から検索を行えます。ここでは、「○点以上△点未満の評価は◇」というような得点による評価付けを例に、VLOOKUP関数の使い方を紹介します。

スポンサーリンク

作例…得点に応じて「A、B、C、D」と評価を付ける

作例

図1の得点評価対応表から「70」点に対応する評価を調べます。

VLOOKUP関数の引数[検索値]に検索条件となる「70」を入力したセル「B2」、[範囲]に得点評価対応表のデータが入力されているセル「D3:F6」(「D3:E6」と指定してもOK)、[列番号]に「評価」の列「2」を指定すると、70点に対応する評価「B」が求められます。[範囲]の1列目のデータを小さい順に入力しておき、[検索の型]に「TRUE」を指定すると、[検索値]が見つからない場合に[検索値]未満の最大値が検索されます。

[範囲]から[検索値]を探し、見つかったセルから数えて[列番号]目のデータを求める
=VLOOKUP(検索値, 範囲, 列番号[, 検索の型])

セルB3 | =VLOOKUP(B2,D3:F6,2,TRUE)

図1 VLOOKUP関数を使用して、「70」点に対応する評価を求める。

作例詳解

図1の作例では、次の流れで得点から評価が求められます。

図2 
 (1)
範囲の1列目から検索値を探す。
 (2) 検索値「70」が1列目に存在しないので、「70」未満の最大値である「50」が検索される。

図3
 (3) 検索結果のセル(セルD5)から数えて2列目のデータ「B」を探す。
 (4) VLOOKUP関数を入れたセルに「B」と表示される。

メモ

IF関数とVLOOKUP関数の使い分け
図1と同様の処理をIF関数で行うと、以下の数式になります(セルB2の値が負数や文字の場合、VLOOKUP関数の式と次式とでは異なる結果になります)。
=IF(B2>=80,"A",IF(B2>=50,"B",IF(B2>=20,"C","D")))
IF関数の場合、得点と評価の対応表をシートに作成することなく、数式1つで結果が出るので便利です。ただし、条件分岐が多い場合、IF関数が何重もの入れ子になるため、数式が長く複雑になります。条件分岐が多い場合は、簡潔な数式で済むVLOOKUP関数を使うとよいでしょう。

関数解説…VLOOKUP関数

書式

=VLOOKUP(検索値, 範囲, 列番号[, 検索の型])

範囲]の1列目から[検索値]を探し、見つかったセルから数えて[列番号]目のデータを求めます。[検索の型]では、完全一致検索(FALSE)を行うのか、近似検索(TRUE)を行うのかを指定します。近似検索を行う場合は、[範囲]の先頭列を基準に昇順に並べ替えておく必要があります。

引数

引数 指定 説明
検索値 必須 検索する値を指定する
範囲 必須 検索する表のセル範囲を指定する
列番号 必須 求める値が入力されている列が何列目にあるかを指定する
検索の型 省略可

「TRUE」を指定するか指定を省略すると、近似検索となる。
検索値が見つからない場合、検索値の代わりに検索値未満の最大値が検索される。

「FALSE」を指定すると、完全一致検索となる。
検索値が見つからない場合、エラー値「#N/A」が返される

引数[検索の型]、「FALSE」と「TRUE」の違い

範囲]の1列目に[検索値]が存在する場合、引数[検索の型]に「FALSE」と「TRUE」のどちらを指定しても、[検索値]に対応するデータが返されます。存在しない場合、以下のような違いが出ます。

■「FALSE」の場合
・[範囲]の左端列は、昇順でなくてもOK。
・[検索値]が見つからない場合、戻り値は「#N/A」(値が未定であることを示すエラー値)になる。
・[検索値]と全く同じデータを探す場合は必ず「FALSE」を指定する。具体例は、「コード番号を検索して表引きする(完全一致検索)…VLOOKUP関数」を参照。

■「TRUE」の場合
・[範囲]の左端列は、昇順で並べ替える必要がある。
・[検索値]が見つからない場合、[検索値]未満の最大のデータが検索される。
・「○以上△未満」の条件で検索する場合に「TRUE」を指定する。

STEPUP…複数の受験者の評価一覧表を作る

図4の成績表のように、VLOOKUP関数を入力したセルをコピーして使う場合は、引数[検索値]を相対参照(「A1」形式)、[範囲]を絶対参照(「$A$1」形式)で指定します。相対参照で指定したセルB3は、コピー先でB4、B5…とずれていきます。一方、絶対参照で指定したセルは、常に「$E$3:$G$6」に固定されます。

先頭のセルに入力する数式
セルC3
| =VLOOKUP(B3,$E$3:$G$6,2,TRUE)

コピー先の数式
セルC4
| =VLOOKUP(B4,$E$3:$G$6,2,TRUE)
セルC5 | =VLOOKUP(B5,$E$3:$G$6,2,TRUE)

図4 VLOOKUP関数の数式をコピーして使い回す場合は、[範囲]を絶対参照で指定して固定する。

スポンサーリンク

関連記事

Copyright© Officeのチカラ , 2018 All Rights Reserved.