Excel の VLOOKUP 関数 – 初心者向けガイド: 構文と例

今日、Excel の最も便利な機能の XNUMX つを説明する一連の記事を開始します- VPR (VLOOKUP)。 同時に、この関数は最も複雑で、最も理解されていない関数の XNUMX つです。

このチュートリアルでは、 VPR 経験の浅いユーザーのために学習プロセスをできるだけ明確にするために、基本をできるだけ単純にレイアウトしようとします. さらに、関数の最も一般的な使用例を示す Excel 数式を使用したいくつかの例を学習します。 VPR.

  • 一般的な説明と構文
  • VLOOKUP の例
    • VLOOKUP を使用して別の Excel シートで検索する方法
    • VLOOKUP を使用して別のブックを検索する
    • VLOOKUP を使用して数式で名前付き範囲またはテーブルを使用する方法
    • VLOOKUP 数式でのワイルドカードの使用
  • VLOOKUP 関数での完全一致または近似一致
  • Excel での VLOOKUP – これを覚えておく必要があります。

Excel の VLOOKUP 関数 – 一般的な説明と構文

それで、それは何ですか VPR? さて、まずはエクセルの関数です。 彼女がする事？ 指定した値を検索し、他の列から対応する値を返します。 技術的に言えば、 VPR 指定された範囲の最初の列の値を検索し、同じ行の別の列から結果を返します。

最も一般的なアプリケーションでは、関数 VPR データベースで特定の一意の識別子を検索し、それに関連する情報をデータベースから抽出します。

関数名の頭文字 VPR (VLOOKUP) の意味 В垂直 （V垂直）。 それによって区別できます VPR から GPR （HLOOKUP）、範囲の一番上の行の値を検索します- Г水平（H水平に）。

演算 VPR Excel 2013、Excel 2010、Excel 2007、Excel 2003、Excel XP、および Excel 2000 で使用できます。

VLOOKUP 関数の構文

演算 VPR (VLOOKUP) の構文は次のとおりです。

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])

ご覧のとおり、関数 VPR Microsoft Excel の には、4 つのオプション (または引数) があります。 最初の XNUMX つは必須で、最後の XNUMX つはオプションです。

  • 参照値 (lookup_value) – 検索する値。 これは、値 (数値、日付、テキスト) またはセル参照 (ルックアップ値を含む)、または他の Excel 関数によって返される値のいずれかです。 たとえば、この式は値を探します 40:

    =VLOOKUP(40,A2:B15,2)

    =ВПР(40;A2:B15;2)

ルックアップ値が、ルックアップされる範囲の最初の列の最小値より小さい場合、関数は VPR エラーを報告します ＃で (#N/A)。

  • テーブル・アレイ (テーブル) – XNUMX つ以上の列のデータ。 関数を覚えておいてください VPR 引数で指定された範囲の最初の列の値を常に探します テーブル・アレイ （テーブル）。 表示可能な範囲には、テキスト、日付、数値、ブール値など、さまざまなデータを含めることができます。 関数は大文字と小文字を区別しません。つまり、大文字と小文字は同じと見なされます。 したがって、式は値を探します 40 からの細胞で A2 〜へ A15、A は引数で指定された範囲 A2:B15 の最初の列であるため テーブル・アレイ （テーブル）：

    =VLOOKUP(40,A2:B15,2)

    =ВПР(40;A2:B15;2)

  • col_index_num (column_number) は、見つかった行の値が返される、指定された範囲内の列の番号です。 指定された範囲の左端の列は 1、XNUMX列目は 2、XNUMX列目は 3 等々。 これで、式全体を読み取ることができます。

    =VLOOKUP(40,A2:B15,2)

    =ВПР(40;A2:B15;2)

    値を求める数式 40 範囲内 A2：A15 列 B から対応する値を返します (B は範囲 A2:B15 の XNUMX 番目の列であるため)。

引数の値が col_index_num (列番号) 未満 1その後 VPR エラーを報告します ＃値！ （＃価値！）。 範囲内の列数を超える場合 テーブル・アレイ (テーブル)、関数はエラーを返します #REF！ （＃リンク！）。

  • 範囲ルックアップ (range_lookup) – 検索対象を決定します。
    • 完全一致、引数は等しい必要があります 間違った情報 （間違い）;
    • 近似一致、引数が等しい 真のコード (TRUE) またはまったく指定されていません。

    このパラメーターはオプションですが、非常に重要です。 このチュートリアルの後半で VPR 完全一致と近似一致を見つけるための式の書き方を説明する例をいくつか示します。

VLOOKUP の例

機能を願っています VPR あなたに少し明確になります。 それでは、いくつかのユースケースを見てみましょう VPR 実際のデータを含む式で。

VLOOKUP を使用して別の Excel シートで検索する方法

実際には、関数を含む数式 VPR 同じワークシートのデータを検索するために使用されることはめったにありません。 多くの場合、別のシートから対応する値を検索して取得します。

使用するために VPR, 別の Microsoft Excel シートで検索する, 引数に指定する必要があります テーブル・アレイ (表) 感嘆符とそれに続くセル範囲でシート名を指定します。 たとえば、次の式は、範囲が A2：B15 という名前のシートにあります Sheet2.

=VLOOKUP(40,Sheet2!A2:B15,2)

=ВПР(40;Sheet2!A2:B15;2)

もちろん、シート名を手動で入力する必要はありません。 式の入力を開始するだけで、引数になると テーブル・アレイ (表)、目的のシートに切り替えて、マウスで目的のセル範囲を選択します。

以下のスクリーンショットに示されている数式は、ワークシートの列 A (範囲 A1:B1 の最初の列) のテキスト「製品 2」を検索します。 価格、またオプションについて.

=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)

=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)

テキスト値を検索するときは、Excel の数式で通常行われるように、引用符 ("") で囲む必要があることに注意してください。

引数の場合 テーブル・アレイ (テーブル) 常に絶対参照 ($ 記号付き) を使用することが望ましいです。 この場合、数式を他のセルにコピーしても、検索範囲は変更されません。

VLOOKUP を使用して別のブックを検索する

機能する VPR XNUMX つの Excel ワークブック間で作業する場合、シート名の前にワークブック名​​を角かっこで指定する必要があります。

たとえば、以下は値を検索する数式です。 40 シートに Sheet2 本の中で Numbers.xlsx:

=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)

=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)

Excelで数式を作成する最も簡単な方法は次のとおりです VPRこれは別のワークブックにリンクしています:

  1. 両方の本を開きます。 これは必須ではありませんが、この方法で数式を作成する方が簡単です。 ワークブック名​​を手動で入力したくありませんか? さらに、偶発的なタイプミスからあなたを守ります。
  2. 関数の入力を開始する VPRそして議論になると テーブル・アレイ (テーブル)、別のワークブックに切り替えて、必要な検索範囲を選択します。

以下のスクリーンショットは、ワークブック内の範囲に設定された検索を含む数式を示しています 価格表.xlsx シートに 価格、またオプションについて.

演算 VPR 以下に示すように、検索されたワークブックを閉じて、ワークブック ファイルへのフル パスが数式バーに表示されても機能します。

ワークブックまたはシートの名前にスペースが含まれている場合は、アポストロフィで囲む必要があります。

=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)

=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)

VLOOKUP を使用して数式で名前付き範囲またはテーブルを使用する方法

複数の機能で同じ検索範囲を利用する場合 VPR、名前付き範囲を作成し、その名前を引数として式に入力できます テーブル・アレイ （テーブル）。

名前付き範囲を作成するには、セルを選択し、フィールドに適切な名前を入力するだけです First Name、数式バーの左側にあります。

これで、製品の価格を見つけるための次の式を書き留めることができます 製品1:

=VLOOKUP("Product 1",Products,2)

=ВПР("Product 1";Products;2)

ほとんどの範囲名は Excel ワークブック全体で機能するため、引数にシート名を指定する必要はありません テーブル・アレイ (テーブル)、数式と検索範囲が異なるワークシートにある場合でも。 それらが異なるワークブックにある場合は、範囲の名前の前に、次のようにワークブックの名前を指定する必要があります。

=VLOOKUP("Product 1",PriceList.xlsx!Products,2)

=ВПР("Product 1";PriceList.xlsx!Products;2)

式はより明確に見えますよね？ また、数式を他のセルにコピーしても名前付き範囲は変更されないため、名前付き範囲を使用することは絶対参照に代わる優れた方法です。 これは、式の検索範囲が常に正しいことを確認できることを意味します。

コマンドを使用してセルの範囲を本格的な Excel スプレッドシートに変換する場合 表 （表）タブ 挿入 (挿入)、マウスで範囲を選択すると、Microsoft Excel は自動的に列名 (テーブル全体を選択した場合はテーブル名) を数式に追加します。

完成した式は次のようになります。

=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)

=ВПР("Product 1";Table46[[Product]:[Price]];2)

または多分このように：

=VLOOKUP("Product 1",Table46,2)

=ВПР("Product 1";Table46;2)

名前付き範囲を使用する場合、関数をどこにコピーしても、リンクは同じセルを指します。 VPR ワークブック内。

VLOOKUP 数式でのワイルドカードの使用

他の多くの機能と同様に、 VPR 次のワイルドカード文字を使用できます。

  • 疑問符 (?) – 任意の XNUMX 文字を置き換えます。
  • アスタリスク (*) – 任意の文字列を置き換えます。

関数でのワイルドカードの使用 VPR たとえば、次のような場合に役立ちます。

  • 検索する必要があるテキストを正確に覚えていない場合。
  • セルの内容の一部である単語を検索したい場合。 ことを知っている VPR オプションが有効になっているかのように、セルの内容全体で検索します セルの内容全体を一致させる (セル全体) 標準の Excel 検索で。
  • セルのコンテンツの先頭または末尾に余分なスペースが含まれている場合。 このような状況では、式が機能しない理由を理解しようとして、長い間頭を悩ませることができます。

例 1: 特定の文字で開始または終了するテキストを探す

以下に示すデータベースで特定の顧客を検索するとします。 彼の姓は覚えていませんが、「ack」で始まることは知っています。 これがうまくいく式です：

=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)

=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)

正しい名前がわかったので、同じ数式を使用して、この顧客が支払った金額を見つけることができます。 これを行うには、関数の XNUMX 番目の引数を変更するだけです。 VPR 目的の列番号に。 この場合、これは列 C (範囲の 3 番目) です。

=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)

=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)

ワイルドカードを使用したその他の例を次に示します。

~ 「男」で終わる名前を見つける:

=VLOOKUP("*man",$A$2:$C$11,1,FALSE)

=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)

~ 「ad」で始まり「son」で終わる名前を見つけます。

=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)

=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)

~ 5 文字で構成されるリストの最初の名前を見つけます。

=VLOOKUP("?????",$A$2:$C$11,1,FALSE)

=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)

機能する VPR 常に使用する必要があるXNUMX番目の引数として、ワイルドカードを使用すると正しく機能しました 間違った情報 （間違い）。 検索範囲にワイルドカードを含む検索語に一致する複数の値が含まれている場合、最初に見つかった値が返されます。

例 2: VLOOKUP 式でワイルドカードとセル参照を組み合わせる

次に、関数を使用して検索する方法のもう少し複雑な例を見てみましょう VPR セル内の値によって。 列 A がライセンス キーのリストで、列 B がライセンスを所有する名前のリストであるとします。 さらに、セル C1 にある種のライセンス キーの一部 (数文字) があり、所有者の名前を見つけたいとします。

これは、次の式を使用して行うことができます。

=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)

=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)

この数式は、指定された範囲のセル C1 から値を検索し、列 B から対応する値を返します。最初の引数では、セル参照の前後にアンパサンド (&) 文字を使用してテキスト文字列をリンクしていることに注意してください。

下の図でわかるように、関数は VPR 彼のライセンス キーにはセル C1 の一連の文字が含まれているため、"Jeremy Hill" が返されます。

引数に注意してください テーブル・アレイ 上のスクリーンショットの (table) には、セルの範囲を指定する代わりに、テーブルの名前 (Table7) が含まれています。 これは、前の例で行ったことです。

VLOOKUP 関数での完全一致または近似一致

最後に、関数に指定された最後の引数を詳しく見てみましょう。 VPR – 範囲ルックアップ (interval_view)。 レッスンの冒頭で述べたように、この議論は非常に重要です。 同じ数式でその値を使用して、まったく異なる結果を得ることができます 真のコード (TRUE) または 間違った情報 （間違い）。

まず、Microsoft Excel の完全一致と近似一致の意味を確認しましょう。

  • 引数の場合 範囲ルックアップ (range_lookup) は等しい 間違った情報 (FALSE)、式は完全一致、つまり引数で指定された値とまったく同じ値を探します。 参照値 （参照値）。 範囲 t の最初の列にある場合可能な配列 (テーブル) 引数に一致する XNUMX つ以上の値に遭遇する 参照値 (search_value) の場合、最初のものが選択されます。 一致するものが見つからない場合、関数はエラーを報告します ＃で (#N/A)。 たとえば、次の数式はエラーを報告します ＃で (#N/A) A2:A15 の範囲に値がない場合 4:

    =VLOOKUP(4,A2:B15,2,FALSE)

    =ВПР(4;A2:B15;2;ЛОЖЬ)

  • 引数の場合 範囲ルックアップ (range_lookup) は等しい 真のコード (TRUE) の場合、式は近似一致を探します。 より正確には、最初に関数 VPR 完全に一致するものを探し、見つからない場合は近似のものを選択します。 近似一致は、引数で指定された値を超えない最大の値です。 参照値 （参照値）。

引数の場合 範囲ルックアップ (range_lookup) は等しい 真のコード (TRUE) または指定されていない場合、範囲の最初の列の値は昇順、つまり最小から最大の順に並べ替える必要があります。 それ以外の場合、関数 VPR 誤った結果を返す可能性があります。

選択の重要性をよりよく理解するために 真のコード (真実) または 間違った情報 (FALSE)、関数を使用してさらにいくつかの数式を見てみましょう VPR 結果を見てください。

例 1: VLOOKUP による完全一致の検索

覚えているように、完全一致を検索するには、関数の XNUMX 番目の引数 VPR 重要であるべき 間違った情報 （間違い）。

最初の例の表に戻って、どの動物が一定の速度で移動できるかを調べてみましょう 50 時速マイル。 この式で問題が発生することはないと思います。

=VLOOKUP(50,$A$2:$B$15,2,FALSE)

=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)

検索範囲 (列 A) には XNUMX つの値が含まれていることに注意してください。 50 – 細胞内 A5 и A6. 数式はセルから値を返します B5. なんで？ 完全一致を探すとき、関数は VPR 検索対象の値に一致する最初に見つかった値を使用します。

例 2: VLOOKUP を使用して近似一致を検索する

機能を使用する場合 VPR おおよその一致を検索します。つまり、引数が 範囲ルックアップ (range_lookup) は等しい 真のコード (TRUE) または省略された場合、最初に行う必要があるのは、範囲を最初の列で昇順に並べ替えることです。

これは非常に重要です。 VPR 指定された値の次に大きい値を返し、検索を停止します。 正しい並べ替えを怠ると、非常に奇妙な結果やエラー メッセージが表示されます。 ＃で (#N/A)。

これで、次の式のいずれかを使用できます。

=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)

=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)

ご覧のとおり、どの動物の速度が最も近いかを調べたいと考えています。 69 時速マイル。 そして、これが関数が私に返した結果です VPR:

ご覧のとおり、式は結果を返しました アンテロープ (カモシカ)、その速度 61 時速マイル、リストには以下も含まれます Cheetah 速く走る（チーター） 70 時速マイル、70 は 69 よりも 61 に近いですね。 なぜこうなった？ 機能だから VPR 近似一致を検索する場合、検索対象の値より大きくない最大値を返します。

これらの例が関数の操作に光を当てることを願っています VPR Excel で、あなたは彼女を部外者として見なくなりました。 今、私たちが研究した資料の重要なポイントを簡単に繰り返して、それを記憶に定着させることは問題ありません。

Excel での VLOOKUP – これを覚えておく必要があります。

  1. 演算 VPR Excelは左を見ることができません。 引数で指定された範囲の左端の列の値を常に探します テーブル・アレイ （テーブル）。
  2. 機能中 VPR すべての値は大文字と小文字を区別しません。つまり、大文字と小文字は同等です。
  3. 探している値が検索範囲の最初の列の最小値より小さい場合、関数は VPR エラーを報告します ＃で (#N/A)。
  4. 第3引数の場合 col_index_num (列番号) 未満 1function VPR エラーを報告します ＃値！ （＃価値！）。 範囲内の列数より大きい場合 テーブル・アレイ (テーブル)、関数はエラーを報告します #REF！ （＃リンク！）。
  5. 引数で絶対セル参照を使用する テーブル・アレイ (テーブル) 式をコピーするときに正しい検索範囲が保持されるようにします。 代わりに、名前付き範囲または Excel のテーブルを使用してみてください。
  6. 近似一致検索を行うときは、探している範囲の最初の列を昇順で並べ替える必要があることに注意してください。
  7. 最後に、XNUMX 番目の引数の重要性を思い出してください。 使用値 真のコード (真実) または 間違った情報 (FALSE) 意図的にそうすれば、多くの頭痛が解消されます。

関数チュートリアルの次の記事では VPR Excel では、さまざまな計算を実行するなど、より高度な例を学習します。 VPR、複数の列から値を抽出するなど。 このチュートリアルをお読みいただきありがとうございます。また来週お会いできることを楽しみにしています!

コメントを残す