HLOOKUP関数 / 指定した値を検索し、別の行から一致する値を返す
Excel2
Excelの使い方- How To Use Excel
VLOOKUP関数 / リストから値を検索し、データを取り出す
Excel2
Contents
VLOOKUP関数の使い方（完全一致のデータを検索する）
Excelで指定した値に一致するデータを見つけたい場合は、VLOOKUP関数を使用します。
VLOOKUPの 「V」はverticalの略で垂直を意味します。
テーブル内のデータを縦に並べ、データを横に並べる必要があります。
VLOOKUP関数の基本的な使い方から、エラーが出さない方法、○以上□未満、のように条件での分類などの応用方法までを説明します。
水平構造のデータの場合は「HLOOKUP関数」を使用してください。
VLOOKUP関数の基本的な使用方法について簡単に説明します。
Sample
品番を入れたら、品名、単価を自動的に表示したい
上記ような明細表を作成する時に、商品一覧のリストにある商品の情報を何度も入力する時があると思います。
商品が売れるたびに同じデータを入力するのは無駄な作業であり、ミスをする可能性も高くなります。
このような場合、商品を番号を入れるだけで、リストからその一致する番号の商品や金額などのデータを自動で表示させることができる関数がVLOOKUP関数です。
上記の例では、B列の品番は手入力し、その番号に対応する商品名(C列)を自動で表示させるやり方を紹介します。
VLOOKUP関数は自動表示させたいセルに入力するため、今回は「セルC16」に以下のようなVLOOKUP関数の数式を入力します。
=VLOOKUP(B16,商品一覧!A4:C8,2,FALSE)
[解説]
B16に入力された品番を商品一覧シートの表から探し、その表の左から2列目のデータをC16に表示する。
検索値 B16
範囲 商品一覧!A4:C8
列番号 2
検索方法 FALSE（代わりに数字の0でも可）
上記の表では、品番は一番左側の列にあり、品名は2列目にあります。
VLOOKUP関数を使用するには、4つの情報、「引数」を指定します。
- 手入力するデータ（表に何のデータを探すのかの基準になる検索する値）
- テーブルを構成するセルの範囲
- 結果として表示したい列の番号
- 検索方法（TRUE =近似一致、FALSE =完全一致）
検索方法にfalseとtrueを入力するのは面倒なので、FALSEにしたい時は数字の「0」、TRUEの場合は「1」を代わりに入力することもできます。
VLOOKUPでは、検索値が範囲の一番左の列に表示されるように指定します。
次の例では、単価を検索したいので、列番号に3を使用しています。
=VLOOKUP(B16,商品一覧!A4:C8,3,FALSE)
[解説]
B16に入力された品番を商品一覧シートの表から探し、その表の左から3列目のデータをD16に表示する。
検索値が範囲の一番左の列に表示されるように指定します。
ダイアログボックスからの指定方法
関数が入力されているセルを選択し、Shift+F3キーでダイアログボックスを表示できます。
関数を入力時の場合は、セルに「=VLOOKUP(」まで入力し、Shift+F3キーを押すことでも表示ができます。
- 検索値 B16
- 範囲 商品一覧!A4:C8
- 列番号 2
- 検索方法 FALSE
関数は数式を直接入力でもできますが、入力項目が多い関数はわかりにくいため、関数のダイアログボックスを使用するとわかりやすいです。
- VLOOKUP関数は、範囲で設定したデータの左側を取り出すことができません。範囲の一番左の列から検索値を検索します。
- 数式を他のセルにコピーするときに範囲が変わってしまわないように範囲を選択する時は必ず絶対参照（$付き）で指定します。
- 検索値に一致するデータが範囲に2つ以上ある場合は、一番最初に見つかった値を返します。
- 検索方法は必ず「FALSE（もしくは0）」を指定します。
省略（もしくはTRUEを設定）すると、近似一致となり、完全一致するデータがない場合でも一番近い値を表示します。
詳しい使い方は以下の説明を確認してください。
VLOOKUP関数の近似値の使い方（IFS関数のような使い方）
完全一致ではなく、○以上△以下のような数字の範囲で該当する結果を検索したい場合は、検索方法を「TRUE」にする近似値での使い方でデータを検索することができます。
Sample
購入台数による割引率を出したい（○以上△以下での検索）
近似値でのVLOOKUP関数の使い方（検索方法をTRUEにした場合）
TRUEもしくは省略した場合、完全一致のデータが見つからない場合は、最も近いデータを検索します。
この時、範囲で指定したデータは昇順に並べ替えておく必要があります。そうしないと正しい値を見つけられない可能性があります。
例えば以下のような条件で結果を分けたい時、通常はIF関数を複数入れ子にするかIFS関数を使用して条件に合わせて結果を複数に分けるのが一般的です。
- 購入台数が0〜4台までは「0％」
- 購入台数が5〜9台までは「5％」
- 購入台数が10〜49までは「10％」
- 購入台数が50〜99までは「20％」
- 購入台数が100台以上は「30％」
上記のように条件で結果をわけたい場合、IF関数やIFS関数で結果を分けることができますが、条件が多いと以下のように入力内容が多く、数式が長くなってしまいます。
■IFS関数の場合（IFS関数の詳しい説明はこちら）
=IFS(C3<5,"0%",C3<10,"5%",C3<50,"10%",C3<100,"20%",C3>=100,"30%")
IFS関数で入れると、条件が多いほど入力内容が増え、入力も大変なうえに、内容を把握するのも困難になってしまいます。
■IF関数の場合（IF関数の詳しい説明はこちら）
=IF(C3<5,"0%",IF(C3<10,"5%",IF(C3<50,"10%",IF(C3<100,"20%","30%"))))
IF関数の場合、条件が増えるごとにIF関数内でさらにIF関数をいれて、入れ子でたくさん入力しないといけなくなるため、入力が大変だったりミスが増えたりする可能性もあります。
IF関数やIFS関数での入力では大変なものも、VLOOKUP関数の検索方法を「TRUE」にする近似値での使い方で上記と同様のことがわかりやすく簡単にできます。
ワイルドカードで部分一致の検索（○○を含む）
VLOOKUP関数で検索値を部分一致（含む、前方一致、後方一致）で検索して一致するデータを探す場合にはワイルドカードを使用します。
ワイルドカードで指定する場合は、以下のように「*（アスタリスク）」をダブルクォーテーションで囲み「&」でくっつけます。
検索値の前後に『*』をつけます。
=VLOOKUP("*"&C3&"*",取引先一覧!$A$4:$D$13,1,FALSE)
検索値の前後に『*』をつけると「◯◯を含む」の意味になります。
上記の場合「製薬」を含むデータの検索ができます。
ワイルドカードで前方一致の検索（○○で始まる）
=VLOOKUP(C3&"*",取引先一覧!$A$4:$D$13,1,FALSE)
検索値の後ろに『*』をつけると「◯◯から始まる」の意味になります。
上記の場合「ABC」から始まるデータの検索ができます。
ワイルドカードで後方一致の検索（○○で終わる）
=VLOOKUP("*"&C3,取引先一覧!$A$4:$D$13,1,FALSE)
検索値の前に『*』をつけると「◯◯で終わる」の意味になります。
上記の場合「銀行」で終わるデータの検索ができます。
ExcelのVLOOKUP - 概要
VLOOKUP（ブイルックアップ）は、テーブル内のデータを参照して検索するための関数です。
関数の構文
ExcelのVLOOKUP関数の構文は次のとおりです。
=VLOOKUP(検索値, 範囲,列番号,検索方法)
| 引数 | 説明 |
|---|---|
| 検索値 | 必須。検索する値。次に指定する［範囲］の一番左の列に入っている値（セル）を指定。 |
| 範囲 | 必須。前に指定した［検索値］を探しに行く表の範囲を指定します。 指定したセル範囲の一番左の列に前で指定した［検索値］があるように範囲を指定します。 |
| 列番号 | 必須。前に指定した［範囲］のうち、答えとして使用したいデータが左から何列目かを数字で指定します。 |
| 検索方法 | 完全一致か近似一致かを指定します。
TRUE：省略するかTRUEまたは1として指定することで近似一致となります。 |
使用するときの注意
- 表の中に同じ検索値が複数ある場合、最初の値のみ表示します。
- VLOOKUPはアルファベットの大文字と小文字のテキストを別々に処理しません。
VLOOKUPでは、"ABC"のような文字は "abc"と同じ文字として扱います。 - ［検索方法］のデフォルトはTRUEです。指定しないと完全一致ではなく、近似一致となります。
検索値と同じ値が表に見つからなかった場合、異なる行の値が返されます。 - ワイルドカード(？、*)を使用することで部分一致などの条件指定もできます。
？(疑問符)は任意の1文字に一致し、*(アスタリスク)は任意の文字列に一致します。
関数の読み方とバージョン
読み方：ブイルックアップ
バージョン：Excel2002以降すべてのバージョン
