01/03
ExcelのVLOOKUPでデータに近似する一致を見つける
VLOOKUP関数のしくみ
垂直ルックアップを表すExcelのVLOOKUP 関数を使用して、データまたはデータベースのテーブルにある特定の情報をルックアップすることができます。
VLOOKUPは通常、その出力として単一のフィールドのデータを返します。 それはどうですか?
- VLOOKUPに、データテーブルのどの行またはレコードに目的のデータを探すかを指示する名前またはlookup_valueを指定します
- 探しているデータの列番号( col_index_num )を指定します
- この関数は、データテーブルの最初の列のlookup_valueを検索します
- VLOOKUPは、指定された列番号を使用して同じレコードの別のフィールドから検索した情報を検索して返します
最初にデータを並べ替える
必ずしも必要というわけではありませんが、通常、 ソートキーの範囲の最初の列を使用して、VLOOKUPが昇順で検索しているデータの範囲を最初にソートすることが最善です。
データがソートされていない場合、VLOOKUPは誤った結果を返す可能性があります。
VLOOKUP関数の構文と引数
関数の構文は、関数のレイアウトを参照し、関数の名前、角括弧、および引数を含みます 。
VLOOKUP関数の構文は次のとおりです。
= VLOOKUP(lookup_value、table_array、col_index_num、range_lookup)
参照_value - 検索する値 - (必要)上記のイメージで販売された数量
table_array - (必須)これは、VLOOKUPが後で情報を検索するために検索するデータのテーブルです。
- table_arrayには少なくとも2列のデータが含まれている必要があります
- 最初の列には通常、lookup_value
col_index_num - (必須)見つけたい値の列番号。
- ナンバリングは、 search_keyカラムからカラム1
- col_index_numが、 table_array引数で選択された列の数より大きい数に設定されている場合、#REF! 関数によってエラーが返される
range_lookup - (オプション) 範囲が昇順でソートされているかどうかを示します。
- 最初の列のデータがソートキーとして使用されます
- ブール値 - TRUEまたはFALSEは唯一の許容値です
- 省略された場合、この値はデフォルトでTRUEに設定されます
- TRUEまたは省略され、範囲の最初の列が昇順でソートされない場合、誤った結果が生じる可能性があります
- TRUEに設定するか省略し、 _valueルックアップの完全一致が見つからない場合、サイズまたは値が最も小さい最も近い一致がsearch_keyとして使用されます
- FALSEに設定すると、VLOOKUPは_valueのルックアップと完全に一致します 。 一致する値が複数ある場合、最初に一致する値が返されます
- FALSEに設定されていて、 search_keyに一致する値が見つからない場合、関数によって#N / Aエラーが返されます
例:購入数量の割引率を検索する
上記の画像の例では、VLOOKUP関数を使用して、購入したアイテムの数量に応じて異なる割引率を求めています。
この例では、19項目の購入に対する割引は2%です。 これは、「 数量」列に値の範囲が含まれているためです。 その結果、VLOOKUPは完全一致を見つけることができません。 正しい割引率を返すには、おおよその一致が見つかるはずです。
おおよその一致を見つけるには:
- table_arrayのデータを昇順でソートします。
- range_lookup引数をTRUEに設定します。
この例では、VLOOKUP関数を含む以下の式を使用して、購入された商品の数量に対する割引を検索します。
= VLOOKUP(C2、$ C $ 5:$ D $ 8,2、TRUE)
この式をワークシートのセルに入力するだけで、次の手順で使用する別のオプションは、関数のダイアログボックスを使用して引数を入力することです。
- ダイアログボックスを使用すると、関数の引数を正しく入力するのがより簡単になります。
VLOOKUPダイアログボックスを開く
上の図に示されているVLOOKUP関数をセルB2に入力するための手順は、次のとおりです。
- セルB2をクリックすると、VLOOKUP関数の結果が表示されているアクティブなセルになります
- [ 数式 ]タブをクリックします。
- リボンから参照と参照を選択して関数ドロップダウンリストを開きます
- リスト内のVLOOKUPをクリックすると、関数のダイアログボックスが表示されます
02/03
ExcelのVLOOKUP関数の引数の入力
セル参照を指す
VLOOKUP関数の引数は、上の図に示すように、ダイアログボックスの別々の行に入力されます。
引数として使用されるセル参照は、正しい行に入力することができます。また、以下の手順では、マウスポインタでセル範囲を強調表示するポインティングを使用してダイアログボックスに入力することができます。
ポインティングを使用する利点は次のとおりです。
- 入力よりも速いです。
- 正確なセル参照を入力する間違いが少なくなります。
引数による相対および絶対セル参照の使用
VLOOKUPの複数のコピーを使用して同じデータテーブルから異なる情報を返すことは珍しいことではありません。 これを簡単に行うために、VLOOKUPをあるセルから別のセルにコピーすることがよくあります。 関数が他のセルにコピーされるときは、関数の新しい場所を考慮して、結果のセル参照が正しいことを確認するように注意する必要があります。
上記の画像では、ドル記号( $ )がtable_array引数のセル参照を囲んでいるため、それらが絶対セル参照であることを示しています。つまり、関数が別のセルにコピーされても変更されません。 これは、VLOOKUPの複数のコピーがすべて情報源と同じデータテーブルを参照するため、望ましいことです。
一方、 lookup_valueに使用されるセル参照は、ドル記号で囲まれていないため、相対的なセル参照になります。 相対セル参照は、それらが参照するデータの位置に対して相対的な新しい位置を反映するようにコピーされると変更されます。
関数引数の入力
- VLOOKUPダイアログボックスのLookup _value行をクリックします。
- このセル参照をsearch_key引数として入力するには、ワークシートのセルC2をクリックします
- ダイアログボックスのTable_array行をクリックします。
- この範囲をTable_array引数として入力するには、ワークシートのセルC5〜D8をハイライト表示します。表の見出しは含まれません。
- 範囲を絶対セル参照に変更するには、キーボードのF4キーを押します
- ダイアログボックスのCol_index_num行をクリックします。
- 割引率はTable_array引数の列2にあるため、この行にCol_index_num引数として2を入力します
- ダイアログボックスのRange_lookup行をクリックします。
- Range_lookup引数としてTrueを入力します。
- キーボードのEnterキーを押してダイアログボックスを閉じ、ワークシートに戻ります。
- ワークシートのセルD2に回答2%(購入数量に対する割引率)が表示されます。
- セルD2をクリックすると、完全な関数= VLOOKUP(C2、$ C $ 5:$ D $ 8,2、TRUE)がワークシートの上の数式バーに表示されます
VLOOKUPが結果として2%を返した理由
- この例では、 Quantity列にはsearch_key値19の完全一致が含まれていません。
- is_sorted引数がTRUEに設定されているため、VLOOKUPはsearch_key値とほぼ一致します。
- 19のsearch_key値よりもまだ小さいサイズの最も近い値は11です。
- したがって、VLOOKUPは11を含む行の割引率を検索し、結果として2%の割引率を返します。
03/03
Excel VLOOKUPが機能しない:#N / Aおよび#REFエラー
VLOOKUPのエラーメッセージ
次のエラーメッセージはVLOOKUPに関連付けられています。
A#N / A(「値は使用できません」)エラーは次の場合に表示されます。
- ルックアップ_valueが範囲引数の最初の列に見つかりません
- Table_array引数が不正です。 たとえば、引数には範囲の左側に空の列が含まれます
- Range_lookup引数がFALSEに設定されており、 search_key引数の完全一致が範囲の最初の列に見つかりません
- Range_lookup引数がTRUEに設定され、 範囲の最初の列の値がすべてsearch_keyより大きい
#REF! (「範囲外の参照」)エラーが表示される場合:
- Col_index_num引数がTable_arrayの列数より大きい