【必見!】Excel上で VLOOKUP関数の使い方を知りたい方
読者登録お願いいたします!
今回はVLOOKUP関数の使い方についてお話します。VLOOKUP関数は関数の中でもよく使われる関数です。
ただ、使い方が難しくて私も以前、理解するのに時間がかかりました。
こんな方が対象
・VLOOKUP関数の使い方がよくわからない方
・VLOOKUP関数を知らない方
・VLOOKUP関数の使用事例を知りたい方
上記当てはまる方は是非お読みください。
VLOOKUP関数の特徴
VLOOKUP関数とは？
・表を縦方向に検索し、特定のデータに対応する値を取り出すExcel関数
・参照元データを参照して、検索条件に一致するデータを抽出する関数
・面倒なデータ入力を一瞬で終わらせることを可能にする関数
上記の特徴があります。
VLOOKUP関数の用途
VlOOKUP関数は一般的に下記のケースで使われます。
・部署一覧表(参照元データ)から従業員一覧表へ部署コードを抽出する場合
→記事内の事例1参照
・商品一覧表(参照元データ)から注文書へ商品金額、商品名を抽出する場合
→記事内の事例2参照
大量のデータから、複数の関連した項目をもったデータを簡単に抽出するときに役立つのがVLOOKUP関数。
VlOOKUP関数書式
下記補足説明
①検索値:どのデータで
②範囲:どこを検索して
③列番号どの列にある値を取り出すか
④検索方法:FALSE ＴＲＵＥを設定
①-④の構成でできているのがVLOOKUP関数。
といってもわかりずらいので、事例1,事例2で説明します。
VLOOKUP関数の事例集
では下記事例1,事例2でVLOOKUP関数の使い方をご紹介します。
事例1:従業員一覧表へ部署コードを抽出
まずは「従業員一覧表へ部署コードを抽出してみたいと思います。
VLOOKUP関数を使う場合、前提として下記2つのデータが必要になります。
①□赤色で囲った転記先のデータ
②□オレンジ色で囲った参照元データ
①②を使います。
PS:「社長」は部署ではありませんが、あまり気にしないでください。
手順1
①「D4」セルを選択後、「fx」を押下
②「Vlookup」を入力
③「検索開始」を押下
④「VLOOKUP」を選択
⑤「ＯＫ」を押下
手順2
ここがちょっと難しいです。
①検索値:
□オレンジ色で囲まれた参照元データから手がかりとなる値を探します。今回は従業員一覧表内C4の値「社長」を入力。
②範囲
□オレンジ色で囲まれた参照元データの範囲(今回はA19:B23)です。ここから①の検索値のC4「社長」を手掛かりに参照元データの部署コードを探します。
③列番号
□オレンジ色で囲まれた参照元データ②の列番号を参照します。
選択した範囲は2列あります。1列目:部署名、2列目:部署コードです。今回は部署コードを抽出したいので、「2」と入力
④検索方法
TRUE:近似値、FALSE:完全一致のデータのみを抽出すること
基本FALSEをよく使うので、FALSEって入力しておけば大丈夫です。
ちなみに、下記のように「0」と入力してもFALSEと同じ意味です。
⑤「OK」を押下。
手順3
①D4の値に「1001」と入力されました。参照元データの2列目の部署コード「1001」を抽出できました。
②D2の黄色〇で囲む箇所を↓にD16まで引っ張る(書式を全部コピーできる。)
手順4
「よしできた！・・・おや？」予想を裏切るエラーが発生しました。
「#N/A」の一部を確認すると、「=VLOOKUP(C4,A19:B23,2,0)」から「=VLOOKUP(C4,A19:B27,2,0)」と範囲がずれています。
実はEXCELの仕様上選択範囲を固定しないと、セルがずれる現象が発生します。
では、どうやって修正するのか。次の画像をご覧ください。
赤□「=VLOOKUP(C4,$A$19:$B$23,2,0)」と修正しています。
「$A$19:$B$23」の「$」を2つけています。
実はこれ「絶対参照」というもので、選択した範囲がずれないように固定します。
再度D4の黄色〇で囲む箇所を↓にD16まで引っ張る(書式を全部コピーできる。)。
下記のように、参照元データより、部署に関連する部署コードを抽出完了。
事例2:注文書へ商品金額、商品名を抽出
次に、注文書へ商品金額、商品名を抽出してみたいと思います。
事例1と同様、VLOOKUP関数を使う場合、前提として下記2つのデータが必要です。
①□赤色で囲った転記先のデータ
②□オレンジ色で囲った参照元データ
①②を使います。
手順1
先に「コード」項目の列に、参照元データの任意のコードを入力。
今回は「FI-001」「FI-004」「FI-003」「FI-006」と入力。
手順2
①「B13」セルを選択後、「fx」を押下
②「Vlookup」を入力
③「検索開始」を押下
④「VLOOKUP」を選択
⑤「ＯＫ」を押下
手順3
①検索値:
□オレンジ色で囲まれた参照元データから手がかりとなる値を探します。今回は従業員一覧表内A13の値「FI-001」を入力。
②範囲
□オレンジ色で囲まれた参照元データの範囲(今回は$H$13:$J$19)です。ここから①の検索値のA13「FI-001」を手掛かりに参照元データの部署コードを探します。
注意:事例1でも説明しましたが、「$」をつけて固定することをお忘れなく
③列番号
□オレンジ色で囲まれた参照元データ②の列番号を参照します。
選択した範囲は3列あります。1列目:コード、2列目:商品名、2列目:単価です。
今回は部署コードを抽出したいので、「2」と入力。
④検索方法
TRUE:近似値、FALSE:完全一致のデータのみを抽出すること
基本FALSEをよく使うので、FALSEって入力しておけば大丈夫です。
ちなみに、下記のように「0」と入力してもFALSEと同じ意味です。
⑤「OK」を押下。
手順3
①B13の値に「ズワイガニ」と入力されました。参照元データの2列目:商品名のズワイガニを抽出できました。
②B13の黄色〇で囲む箇所を↓にB16まで引っ張る(書式を全部コピーできる。)
手順4
①A13~A16までのコードが参照元データ1列目の「コード」を基準に2列目の商品名が返されていることを確認。
手順5
①次に、下記画像の□赤色枠のVLOOKUP関数をC列のC13のように入力
②C13に「5000円」と記載表示。
補足:
□オレンジ色の参照元データ($H$13:$J$19)を確認後、3列目の項目に「単価」があります。「=VLOOKUP(A13,$H$13:$J$19,3,0)」の「3]と入力します。
結果、□オレンジ色参照元データの3列目の値「5,000」を取得します。
③C13の黄色〇で囲む箇所を↓にC16まで引っ張る(書式を全部コピーできる。)
手順6
①A13~A16までのコードが参照元データ1列目の「コード」を基準に3列目の単価が返されていることを確認。
まとめ
VLOOKUP関数の使い方について説明しました。
まとめるとVLOOKUP関数は下記4つの特徴があります。
・大量のデータから、複数の関連した項目をもったデータ抽出
・参照データ、転記先のデータと2つ必要
・「$A$19:$B$23」のように参照元データ範囲は固定しておくこと
・VLOOKUP関数の書式「検索方法」は基本FALSEにしておくこと
以上です。是非日常業務でご活用ください。
