VLOOKUP関数の使い方と具体例

ExcelのVLOOKUP関数はデータを調べるのにとても便利です。Excel で VLOOKUP 関数を使用する場合に知っておくべきことは次のとおりです。

Excel スプレッドシート内の特定の値を確認し、それに関する情報を調べたいとします。この場合、VLOOKUP を実行すると、労力を大幅に節約できます。これは、Microsoft Excel で垂直クエリを実行する最良の方法の 1 つです。この記事では、Microsoft ExcelのVLOOKUP関数の意味、使い方、具体的な例について説明します。

• これらは、理解する必要がある Excel の最も基本的な関数です

VLOOKUP関数の使い方

• VLOOKUP関数とは何ですか？
• Excel 2016、2013、2010、2007、2003のVLOOKUP関数の計算式
• VLOOKUP関数の使い方に関するビデオチュートリアル
• 例 1: VLOOKUP 関数を使用して学生とスタッフの評価を評価します。
• 例 2: VLOOKUP を使用して絶対検出を行い、データを取得します
• 例 3: VLOOKUP を使用してデータを抽出する
• 例 4: 2 つの異なる Excel シートで VLOOKUP 関数を使用する
• VLOOKUP関数は何に使うのでしょうか？
• VLOOKUP関数の注意点

VLOOKUP関数とは何ですか？

VLOOKUP関数はExcelのデータ検索関数です。Excel の知識があり、SUM、AVERAGE、TODAY などの基本関数を使用できることを前提としています。VLOOKUP の最も一般的な関数の 1 つはデータ関数です。これは、データベース テーブル、またはより単純に項目のリストを操作することを意味します。リストにはさまざまなものがあります。会社の従業員、製品カテゴリ、顧客リストなどに関するテーブルを作成できます。以下は A 社が市場で販売している製品のリストです。

多くの場合、データベース テーブルには各製品の識別子が付いています。上記のデータテーブルでは、特別な識別記号は「商品コード」列です。注: VLOOKUP 関数を使用できるようにするには、データ テーブルにコードや ID などの識別記号を含む列が必要で、上記のテーブルのように最初の列である必要があります。

VLOOKUP はおそらく Excel で最も有名な関数ですが、良い理由と悪い理由の両方があります。明るい面としては、VLOOKUP 関数は使いやすく、非常に便利な機能を備えています。特に新規ユーザーにとって、VLOOKUP 関数がテーブルをスキャンし、一致するものを見つけて正確な結果を返すのを見るのは、非常にエキサイティングです。VLOOKUP 関数を使いこなすことは、初心者から熟練した Excel ユーザーまで必須のスキルです。

マイナス面としては、VLOOKUP 関数には制限があり、デフォルト値が危険であることが挙げられます。INDEX や MATCH (または XLOOKUP) とは異なり、VLOOKUP 関数には最初の列にルックアップ値が含まれる完全なテーブルが必要です。このため、複数の条件を指定して VLOOKUP 関数を使用することが困難になります。さらに、VLOOKUP のデフォルトの一致動作により、誤った結果が得られやすくなります。でも心配しないでください。VLOOKUP 関数を使いこなすための鍵は、基本をマスターすることです。

VLOOKUP には、lookup_value、table_array、column_index_num、range_lookup の 4 つの引数があります。Lookup_value は検索する値、table_array は検索する垂直方向のデータ範囲です。table_array の最初の列には、検索するルックアップ値が含まれている必要があります。引数 column_index_num は取得する値の列番号で、table_array の最初の列は列 1 です。

最後に、range_lookup は、一致を見つける動作を制御します。range_lookup が TRUE の場合、VLOOKUP 関数は近似一致を実行します。range_lookup が FALSE の場合、VLOOKUP 関数は完全一致を実行します。重要なのは、range_lookup はオプションであり、デフォルトは TRUE であるため、VLOOKUP はデフォルトで近似一致を実行します。

ExcelのVLOOKUP関数の計算式

Excel の VLOOKUP 関数には次の式があります。

=VLOOKUP(ルックアップ値,テーブル配列,列インデックス番号,[ルックアップ範囲] )

そこで：

• VLOOKUP:関数名です
• 太字のパラメータは必須です。
• lookup_value:ルックアップに使用される値
• table_array:検索する値を含むテーブル。先頭に $ 記号を付けて絶対値で指定します (例: $A$3:$E$40)。
• Col_index_num: table_array のルックアップ値を含む列の順序。たとえば、テーブル $A$3:$E$40 では、列 B に検索対象の値が含まれている場合、ここのcol_index_num は 2 になります。テーブル $C$3:$F$40 の列 E にはルックアップ値が含まれており、ここでのcol_index_num は 3 です。
• range_lookup: 検索範囲です。TRUE は 1 (相対検索)、FALSE は 0 (絶対検索) に相当します。このパラメータは式に必ずしも必要というわけではありません。

相対検索は、table_array 内で検索する値が順序 (昇順、降順、またはアルファベット順) に配置されている場合にのみ適用できます。このようなテーブルでは、無限IF 関数を使用するのと同様の相対検索を使用できます。検索できない値や並べ替えられていない値については、絶対検索を使用して正確な値を見つけます。

VLOOKUP関数の使い方に関するビデオチュートリアル

例 1: 学生と職員の評価を評価する VLOOKUP 関数

次のような生徒の成績証明書があるとします。

そしてランキング規定表は以下の通りです。

次に、VLOOKUP 関数を使用して生徒の評価を入力します。ランキング表は低いものから高いもの（弱いものから良いもの）の順に配置されていることがわかります。この場合、相対検索を使用できます。

Excel では、これら 2 つのテーブルは次のように表示されます。

ここでは、検出対象の値は 6 行目から始まる C 列にあります。検索範囲は $A$18:$B$21 で、検出された値が含まれる列の順序は 2 です。

セル D6 に、数式 =VLOOKUP($C6,$A$18:$B$21,2,1) を入力します。これは相対検索式です。必要に応じて (またはランキングがソートされていないため)、次のように式に 0 を追加することで絶対検索を実行できます: =VLOOKUP($C6 ,$A$18:$B$21,2, 0)。Enterを押します。

セル D6 をクリックすると、右下隅に小さな四角形が表示されます。それをクリックして表の下にドラッグし、残りの学生の採点式をコピーします。

その際、VLOOKUP関数を使って生徒の学力を分類した結果は以下の通りです。

なぜ C6 の前に $ を使用しなければならないのか疑問に思いませんか? $ は列 C を固定したままにし、数式をテーブルの下にドラッグしたときにのみ行を変更します。また、ランキング ルール テーブルを修正して、数式をドラッグしても変更されないようにする $A$18:$B$21 もあります。

例 2: VLOOKUP 関数は絶対検索してデータを取得します

従業員コード、氏名、役職を保存する従業員データ テーブルがあるとします。別のテーブルには、従業員コード、出身地、教育レベルが保存されます。各従業員の出身地情報と教育レベルを入力したい場合、どうすればよいでしょうか?

次のような従業員と従業員の出身地を示すテーブルがあるとします。

次に、従業員の出身地情報を入力したいとします。セル D4 に、次のように絶対検索式を入力します。 =Vlookup($A4,$A$16:$C$25,2,0)

次に Enter を押します。セル D4 の隅に表示される小さな四角形をクリックし、表全体に下にドラッグして、他の従業員に数式をコピーします。

従業員の資格情報を入力するには、セル E4 に絶対検索式=VLOOKUP($A4,$A$16:$C$25,3,0)を入力します。

Enter キーを押し続け、下にスクロールして残りの従業員に数式をコピーすると、次の結果が得られます。

例 3: VLOOKUP を使用してデータを抽出する

例 2 のデータ セットを続けて、3 人の従業員、Nguyen Hoang Anh、Tran Van Anh、Nguyen Quang Vinh の出身地を見つけます。それを新しいテーブル F15:G18 に抽出しました。

出身地と資格を入力した後、表 A3:E13 に対してこの検索を実行します。ここで、次の絶対検索式をセル G16 に入力します: =VLOOKUP($F16,$B$3:$E$13,3,0) > Enter キーを押します。

残りの 2 人の従業員の数式をコピーすると、次の結果が得られます。

この例では、検出値は列 B にあるため、検出領域は列 A ではなく列 B から計算されることに注意してください。

例 4: 2 つの異なる Excel シートで VLOOKUP 関数を使用する

例 2 のデータ セットに戻り、従業員の資格と出身地を入力した後、シートに QTM という名前を付けます。

QTM1 という名前のスプレッドシートの別のシートでは、従業員の配置順序が変更された従業員の資格と職位に関する情報を取得する必要があります。ここで VLOOKUP 関数の真価がわかります。

従業員の「資格」に関するデータを検索するには、シート QTM1 のセル C4 に数式 =VLOOKUP($B4,QTM!$B$3:$E$13,4,0) を入力します。

そこで：

• B4 は検出に使用される値を含む列です。
• QTM！は、検出する値を含むテーブルを含むシートの名前です。シート名の後に ! を追加します。
• $B$3:$E$13 は、ルックアップ値を含むテーブルとテーブル (QTM) を含むシートです。
• 4はQTMシートの「氏名」欄から算出した「資格」欄の通し番号です。
• 0は絶対検出です。

Enter キーを押して、テーブル内の残りのすべての従業員の数式をコピーすると、次の結果が得られます。

従業員の「役職」データを検索するには、シート QTM1 のセル D4 に数式 =VLOOKUP($B4,QTM!$B$3:$E$13,2,0) を入力し、Enter キーを押します。

残りの従業員の数式をコピーすると、次のようになります。

VLOOKUP関数は何に使うのでしょうか？

まず、「VLOOKUP 関数は何に使用するのですか?」という質問に対する正確な答えを見つける必要があります。

VLOOKUP 関数は、利用可能な識別子に基づいたデータ フィールドまたはリスト内の情報の検索をサポートするために使用されます。

たとえば、商品コードを含む VLOOKUP 関数を別のスプレッドシートに挿入すると、そのコードに対応する商品情報が表示されます。その情報は、作成したレシピの内容に応じて、説明、価格、在庫数量になります。

検索する必要がある情報の量が少ないほど、VLOOKUP 関数の作成は難しくなります。通常、この関数は再利用可能なワークシートでテンプレートとして使用します。適切な製品コードが入力されるたびに、システムは対応する製品に関する必要な情報をすべて取得します。

VLOOKUP関数の注意点

Excel の VLOOKUP 関数について覚えておくべき重要事項のリストを次に示します。

• range_lookup が省略された場合、VLOOKUP では非完全一致が許可されますが、可能であれば完全一致も使用されます。
• この関数の最大の制限は、常に右側の値を取得することです。この関数は、テーブルの最初の列の右側の列からデータを取得します。
• ルックアップ列に重複した値が含まれている場合、VLOOKUP 関数は最初の値のみと一致します。
• この関数では大文字と小文字が区別されません。
• ワークシートに既存の VLOOKUP 数式があるとします。その場合、テーブルに列を挿入すると数式が壊れる可能性があります。これは、列のインデックス値がハードコーディングされており、列が挿入または削除されても自動的に変更されないためです。
• VLOOKUP では、アスタリスク (*) や疑問符 (?) などのワイルドカード文字を使用できます。
• テーブルで、テキストとして入力された数値を含む関数を操作しているとします。テーブル内の列から数値をテキストとして取得するだけであれば、それは問題ではありません。ただし、表の最初の列にテキストとして入力された数値が含まれている場合、#N/A! ルックアップ値もテキストでない場合に表示されます。
• エラー#N/A! VLOOKUP 関数が、指定された lookup_value に一致するものが見つからなかった場合に発生します。
• エラー#REF! 次の場合に発生します:
  • Col_index_num 引数が、指定された table_array の列数を超えています。
  • 数式は存在しないセルを参照しようとしました。
• エラー#VALUE! 次の場合に発生します。
  • Col_index_num 引数が 1 未満であるか、数値として認識されません
  • range_lookup 引数は、論理値 TRUE または FALSE のいずれかとして認識されません。

Excel の VLOOKUP 関数と XLOOKUP 関数の主な違い

Y - はい

N - いいえ

続きを見る：

• ExcelでIF関数を使う方法
• ExcelでAVERAGEIF関数を使用する方法

VLookup の使用方法を理解した後は、一般的なエラーと問題の解決方法についても知る必要があります。

Excel で VLOOKUP 関数を使用するときによくあるエラー

ルックアップ値が間違った列にあります

VLOOKUP 数式を使用するときに発生する最も一般的なエラーの 1 つは、関数が値 #N/A のみを返すことです。このエラーは、VLOOKUP に検索を依頼したルックアップ値が見つからない場合に発生します。

場合によっては、VLOOKUP の代わりに XLOOKUP 関数を使用できますが、このエラーは VLOOKUP 関数でのみ簡単に修正できます。

問題を解決する方法:

VLOOKUP で目的の値が見つからない理由は、値がテーブルにないことが考えられます。ただし、すべての #N/A 値が返され、ルックアップ値がテキストである場合は、左から右へしか読み取れないことが原因である可能性が高くなります。

Excel で VLOOKUP 用のテーブルを作成する場合は、返したい値の左側にルックアップ値を配置してください。これを行う最も簡単な方法は、テーブルの最初の列にそれを配置することです。

これで、数式は値を返すようになります。

全体として、VLOOKUP は Microsoft Excel でデータをより高速にクエリするための優れた方法です。VLOOKUP クエリは列に対して垂直に実行され、他にもいくつかの制限がありますが、Microsoft は Excel のルックアップ機能を継続的に更新して、適用範囲を拡大しています。たとえば、HLOOKUP、XLOOKUP... は、さまざまな方向でデータを検索するのに役立ちます。LuckyTemplates.com では、今後もこの機能の使用方法に関する情報を次の記事で提供していきます。