Microsoft Excel で VLOOKUP 関数を使用する方法 [+ ビデオチュートリアル]

Microsoft Excel で大量のデータを調整するのは、時間のかかる頭痛の種です。 ありがたいことに、その必要はありません。 VLOOKUP 関数を使用すると、このタスクを自動化し、時間を大幅に節約できます。

VLOOKUP は具体的に何をするのでしょうか? 簡単に説明すると、VLOOKUP 関数はデータ内の特定の値を検索し、その値を特定すると、その値に関連付けられた他の情報を検索して表示できます。

Microsoft Excel の VLOOKUP 関数は、思ったよりも簡単に使用できます。 さらに、それは信じられないほど強力であり、間違いなく分析武器の武器庫に入れておきたいものです。

スキップして:

VLOOKUPはどのように機能しますか?

VLOOKUPは「垂直検索」の略です。 Excel では、これは、スプレッドシートの列 (およびそれらの列内の一意の識別子) を検索の基礎として使用し、スプレッドシート全体でデータを垂直方向に検索する行為を意味します。 データを検索するときは、データが配置されている場所に縦方向にリストする必要があります。

VLOOKUP Excel 数式

Microsoft は、VLOOKUP の数式または関数を次のように説明しています。

=VLOOKUP(ルックアップ値、ルックアップ値を含む範囲、戻り値を含む範囲内の列番号、近似一致 (TRUE) または完全一致 (FALSE))。

検索したい値が検索したい戻り値の左側に来るようにデータを整理すると便利です。

数式は常に右方向に検索します。

Excel で VLOOKUP を実行するときは、基本的に、現在のスプレッドシートの古いデータに関連付けられている別のスプレッドシートで新しいデータを検索することになります。 VLOOKUP がこの検索を実行すると、常に現在のデータの右側にある新しいデータが検索されます。

たとえば、あるスプレッドシートに名前の縦方向のリストがあり、別のスプレッドシートにそれらの名前とその電子メール アドレスの整理されていないリストがある場合、VLOOKUP を使用して、最初のスプレッドシートにある順序でそれらの電子メール アドレスを取得できます。 これらの電子メール アドレスは、2 番目のスプレッドシートの名前の右側の列にリストされている必要があります。リストされていない場合、Excel はアドレスを見つけることができません。 (ゴーフィギュア…)

数式にはデータを取得するための一意の識別子が必要です。

VLOOKUP の仕組みの秘密? 一意の識別子。

一意の識別子は、両方のデータ ソースが共有する情報であり、その名前が示すとおり、一意です (つまり、識別子はデータベース内の 1 つのレコードにのみ関連付けられます)。 一意の識別子には、製品コード、在庫管理単位 (SKU)、および顧客の連絡先が含まれます。

さて、説明は十分です。VLOOKUP の動作の別の例を見てみましょう。

VLOOKUP Excel の例

以下のビデオでは、VLOOKUP 関数を使用して (2 番目のデータ ソースからの) 電子メール アドレスを別のシートの対応するデータと照合する実際の例を示します。

著者注: Excel にはさまざまなバージョンがあるため、上のビデオに表示されている内容が、実際のバージョンで表示される内容と必ずしも一致するとは限りません。 そのため、以下の書面による指示に従うことをお勧めします。

ExcelでVLOOKUPを使用する方法

1. 新しいデータを入力するセルの列を特定します。
2. [関数] (Fx) > VLOOKUP を選択し、この数式を強調表示されたセルに挿入します。
3. 新しいデータを取得するルックアップ値を入力します。
4. 目的のデータが配置されているスプレッドシートのテーブル配列を入力します。
5. Excel が返すデータの列番号を入力します。
6. 範囲ルックアップを入力して、ルックアップ値の正確またはおおよその一致を見つけます。
7. [完了] (または [入力]) をクリックして、新しい列を入力します。

参考までに、VLOOKUP 関数の構文は次のとおりです。

VLOOKUP(lookup_value , table_array ,col_index_num , range_lookup)

以下の手順では、各顧客の MRR を見つけるための一意の識別子として顧客名を使用して、これらの各コンポーネントに適切な値を割り当てます。

1. 新しいデータを入力するセルの列を特定します。

別のシートからデータを取得して、それをこのシートに配置しようとしていることに注意してください。 これを念頭に置いて、詳細情報が必要なセルの隣の列に、月ごとの経常収益に関する「MRR」などの適切なタイトルを上部のセルに付けます。 この新しい列には、フェッチしているデータが保存されます。

2. [関数] (Fx) > VLOOKUP を選択し、この数式を強調表示されたセルに挿入します。

スプレッドシートの上のテキスト バーの左側に、スクリプトのような小さな関数アイコンFxが表示されます。 列タイトルの下にある最初の空のセルをクリックし、この機能アイコンをクリックします。 [数式ビルダー]または[関数の挿入]というタイトルのボックスが画面の右側に表示されます (使用している Excel のバージョンに応じて)。

数式ビルダーに含まれるオプションのリストから「VLOOKUP」を検索して選択します。 次に、 [OK]または[関数の挿入]を選択して、VLOOKUP の構築を開始します。 スプレッドシートで現在強調表示されているセルは、「 =VLOOKUP() 」のようになっているはずです。

上の太字のテキストを目的のセルに正確に入力することで、この数式を手動で通話に入力することもできます。

最初のセルに =VLOOKUP テキストを入力したら、数式に 4 つの異なる基準を入力します。 これらの基準は、Excel が必要なデータの場所と検索対象を正確に絞り込むのに役立ちます。

3. 新しいデータを取得するルックアップ値を入力します。

最初の基準は検索値です。これは、Excel が検索して返すデータが関連付けられているスプレッドシートの値です。 入力するには、一致する値を見つけようとしている値が含まれるセルをクリックします。 上に示した例では、セル A2 にあります。 このセルは A2 にリストされている顧客名の MRR を表すため、新しいデータの D2 への移行を開始します。

ルックアップ値は、テキスト、数字、Web サイトのリンクなど、何でも構いません。 検索している値が参照元のスプレッドシートの値と一致する限り (これについては次のステップで説明します)、この関数は必要なデータを返します。

4. 目的のデータが配置されているスプレッドシートのテーブル配列を入力します。

「テーブル配列」フィールドの横に、上のスクリーンショットに示されている形式を使用して、検索するセルの範囲とこれらのセルが配置されているシートを入力します。 上記のエントリは、探しているデータが「ページ」というタイトルのスプレッドシート内にあり、列 B と列 K の間のどこにでも見つかることを意味します。

データが配置されているシートは、現在の Excel ファイル内に存在する必要があります。 つまり、以下に示すように、データは現在のスプレッドシートのどこかにある別のセルのテーブルに存在することも、ワークブックの下部にリンクされている別のスプレッドシートに存在することもできます。

たとえば、データが「Sheet2」のセル C7 と L18 の間にある場合、テーブル配列のエントリは「Sheet2!C7:L18」になります。

5. Excel が返すデータの列番号を入力します。

テーブル配列フィールドの下に、検索するテーブル配列の「列インデックス番号」を入力します。 たとえば、列 B ～ K (「テーブル配列」フィールドに入力すると「B:K」と表記されます) に注目しているが、必要な特定の値が列 K にある場合は、「10」と入力します。列 K は左から 10 番目の列であるため、「列インデックス番号」フィールド。

6. 範囲ルックアップを入力して、ルックアップ値の正確またはおおよその一致を見つけます。

私たちのような月次収益に関する状況では、検索しているテーブルから完全に一致するものを見つけたいと考えます。 これを行うには、「範囲検索」フィールドに「FALSE」と入力します。 これにより、各営業担当者に関連付けられた正確な収益のみを検索するよう Excel に指示されます。

あなたの質問に答えるには: はい、Excel が完全一致ではなく近似一致を検索できるようにすることができます。 これを行うには、上記の 4 番目のフィールドに FALSE ではなく TRUE を入力するだけです。

VLOOKUP が近似一致に設定されている場合、ルックアップ値と同一のデータではなく、ルックアップ値に最もよく似たデータが検索されます。 たとえば、Web サイトのリンクのリストに関連付けられたデータを検索していて、一部のリンクの先頭に「https://」がある場合、次のようなリンクがあった場合に備えて、おおよその一致を見つける必要があるかもしれません。この「https://」タグはありません。 こうすることで、この最初のテキスト タグがなくてもリンクの残りの部分が一致するため、Excel で見つからない場合は VLOOKUP 数式がエラーを返します。

7. [完了] (または [入力]) をクリックして、新しい列に入力します。

ステップ 1 で必要な値を新しい列に正式に取り込むには、「範囲検索」フィールドに入力した後、「完了」(Excel のバージョンによっては「Enter」) をクリックします。 これにより、最初のセルに値が入力されます。 この機会に、他のスプレッドシートを調べて、これが正しい値であることを確認してください。

その場合は、最初に塗りつぶされたセルをクリックし、このセルの右下隅に表示される小さな四角をクリックして、新しい列の残りの部分に後続の値を入力します。 終わり！ すべての値が表示されるはずです。

VLOOKUP チュートリアル

上記の手順で独自の VLOOKUP を実行しようとした後に行き詰まりましたか? Microsoft のこの便利なチュートリアルをチェックしてください。関数を適切に使用する手順を説明する便利なチュートリアルがあります。

画像ソース

上記の手順を実行しても VLOOKUP が機能しない場合は、次のいずれかに問題がある可能性があります。

• 構文(つまり、数式をどのように構成したか)
• 値(つまり、検索しているデータが良好で、正しくフォーマットされているかどうか)

VLOOKUP 構文のトラブルシューティング

まず、指定されたセルに記述した VLOOKUP 数式を確認します。

• キー識別子の正しい検索値を参照していますか?
• 取得する必要がある値の正しいテーブル配列範囲を指定していますか?
• 範囲に正しいシートを指定していますか?
• そのシートの綴りは正しいですか?
• シートを参照するために正しい構文が使用されていますか? (例: ページ!B:K または 'シート 1'!B:K)
• 正しい列番号が指定されていますか? (例: A は 1、B は 2 など)
• シートの設定方法の正しいルートは True または False ですか?

VLOOKUP 値のトラブルシューティング

構文が問題ではない場合、受信しようとしている値自体に問題がある可能性があります。 これは多くの場合、VLOOKUP で参照される値が見つからない #N/A エラーとして現れます。

• 値は垂直方向に右から左にフォーマットされていますか?
• 値は参照方法と一致していますか?

たとえば、URL データを検索する場合、各 URL は同じ行の左側に対応するデータを持つ行である必要があります。 データが垂直方向に移動する列ヘッダーとして URL がある場合、VLOOKUP は機能しません。

この例に従うと、URL は両方のシートの形式で一致する必要があります。 1 つのシートの値に「https://」が含まれ、もう 1 つのシートの「https://」が省略されている場合、VLOOKUP は値を照合できません。

強力なマーケティング ツールとしての VLOOKUP

マーケティング担当者は、リード生成 (およびその他) の全体像を把握するために、さまざまなソースからのデータを分析する必要があります。 Microsoft Excel は、特に VLOOKUP 関数を使用して、これを正確かつ大規模に実行するのに最適なツールです。

編集者注: この投稿はもともと 2019 年 3 月に公開され、包括性を高めるために更新されました。