VLOOKUP関数で範囲検索を行う方法-DL有-
ExcelのVLOOKUP関数を使って、特定の範囲のデータを抽出する方法をご紹介します。
VLOOKUP関数の第4引数「検索方法」をTRUEまたは省略に設定することで、
特定の範囲内の値を検索します。
IF関数などの条件分岐を使うよりも、データ適応性1が高く、式が簡潔です。
VLOOKUP関数を使って範囲検索を行う
ExcelのVLOOKUP関数は、特定の値を検索し、その値が見つかった行から指定した列の値を返す関数です。
しかし、VLOOKUP関数の第4引数「検索方法」をTRUEまたは省略に設定すると・・・
検索値が完全に一致しない場合でも、指定した値以上で最も近い値
を検索します。
近似一致検索と言われます。
なお、VLOOKUP関数の基本は『【VLOOKUP関数】使い方と解説』を拝読ください。
例題
例えば、商品のブランドスコアに応じて、ブランドスコアの評価基準データを取得してみましょう。
解答
特定のブランドスコアに対応する評価を取得する場合、VLOOKUP関数を使って次のように検索できます。
VLOOKUP関数の第4引数「検索方法」をTRUEまたは省略に設定すると、
指定した検索値が完全に一致しない場合でも、指定した値以下で最も近い値を検索します。
まずは、商品Aの場合の[関数の引数]ダイアログボックスの設定は下図の通りです。
関数式は以下になります。
=VLOOKUP(C11, $B$3$C$7, 3, TRUE)
関数の結果(戻り値)は以下です。期待通りの結果を得られています。
A
関数式を行方向に、ブランドの数だけコピーすれば完成です
近似一致検索の動作とは??
VLOOKUP関数の第4引数をTRUEまたは省略にすると、以下のような動作をします。
- 検索範囲の最初の列(ここではA列)から検索値(ここでは83)の近い値の最大値を探します
- その値が含まれる行の情報を返します
A2:C5の範囲で83に近い最も大きいブランドスコアを検索し、
そのブランドスコアが含まれる行の評価を返します。
結果として、
第3引数「列番号」を3に設定しているため、「A」が返されます。
このように、VLOOKUP関数の第4引数をTRUEまたは省略に設定することで、
特定の範囲内の値を簡単に検索することができます。
ただし、参照範囲の1列目を昇順しておく必要がありますので注意してください
さらに、詳しい話は、以下でも解説しています。
MATCH関数の解説ですが、考え方は同じです。
VLOOKUP関数とIF関数の比較
IF関数を使用して同様のことを行うことも可能ですが、
VLOOKUP関数を使用する方が効率的である場合が多いです。
VLOOKUP関数の方が、
データ数が増えた場合や価格範囲が多い場合や価格範囲が頻繁に変更される場合
に効率的である場合が多いです。
加えて、VLOOKUP関数は一つの関数で結果を得られるため、計算式が簡潔です。
第2引数「検索方法」の使用上の注意点
VLOOKUP関数の近似一致検索は、強大な力を与えますが、
その力を制御するためには熟練した技術と知識が必要です。
下記が注意点です。
- 第2引数の[検索範囲]が昇順である必要
- 降順のデータセットでは、期待通りの結果を得られない可能性が高い
- 検索範囲の上限値が空セル
- 理由は、範囲以上の値でも強引に取得するため
- 文字列の検索は控える
- 文字列の大小比較はアルファベット順で行われるため、期待通りの結果を得られない可能性が高い
誤った使い方をすると、予期しない結果をもたらす可能性があります。
したがって、この強力なツールを使う際には、
その特性と使い方をしっかりと理解しておくことが重要です。
最後に
VLOOKUP関数の第4引数をTRUEに設定することで、
特定の範囲内の値を簡単に検索することができます。
近似一致と言われます。
本例では、範囲検索を行う方法を紹介しました。
IF関数などの条件分岐関数に比べ、VLOOKUP関数の方が、
データ数が増えた場合や価格範囲が多い場合や価格範囲が頻繁に変更される場合
に効率的である場合が多いです。
第4引数「検索方法」の使用上の注意点の理解して、使用しましょう。
解答付きサンプルデータが欲しい方は、下記からダウンロードしてください。
解答付きサンプルデータ
脚注
- 「データ適応性」とは、Excelの関数が様々なデータ形式やデータの変更に対応し、その影響を最小限に抑えつつ、正確な結果を出し続ける能力を指します ↩︎
参考記事:
Excel関数を操作するための基本概念です。基本概念を理解すると、各関数の理解がより深まります。
構文に関する記事
Excel関数の引数と戻り値は何だっけ?という時に、役立つ記事です。
ダイアログボックスの使い方に関する記事
Excel関数名は何だっけ?VLOOKUP関数の使い方は何だっけ?という時に、ダイアログボックスを使うと便利です。
下記はそのダイアログボックスに関する記事です。
参照形式に関する記事
関数式をコピーする時はどうしたらいい? $マークは、どう付けたらいいだろう?
といった悩みをお持ちの方は以下をご覧ください。