「VLOOKUP関数」と言えば、いかにもマニア向けで複雑な機能のように感じられるのではないかと思います。しかし、この記事を読み終えるころには、どうして今までVLOOKUPなしでExcelを使ってこられたのかと不思議に感じられるはずです。

Microsoft ExcelのVLOOKUP関数は、ピボットテーブルと同じように、皆さんが思っているよりも簡単に使うことができます。しかも、驚くほど便利な機能なので、データ分析の際には絶対に揃えておきたい道具の1つと言えます。

VLOOKUPがどのような関数なのかを一言で説明すると、「データを検索して特定の値を見つけ出し、見つけた値に関連する他の情報を探して表示する関数」となります。

具体的な手順としては、1枚目のシートの顧客データに、2枚目のシートから取得した売上データを統合します。こうすることで、自社の業績の全体像を把握できるようになります。

ここからは、このVLOOKUPの活用例を7つの手順に分けて説明していきます。

VLOOKUPの仕組み

VLOOKUPとは、「vertical lookup(垂直方向の検索)」の略です。Excelの場合、スプレッドシート内のデータを縦方向に検索する処理を意味します。データの検索はスプレッドシートの列に沿って行われ、列に含まれる一意の識別子が検索の基準になります。VLOOKUPを使うには、表示場所を問わず、データが縦方向にリストアップされていることが必要です。

VLOOKUPでは左から右に検索するのがルール

原則として、ExcelでVLOOKUP関数を使用する場合は、今表示されているスプレッドシート内の既存のデータと対応する新しいデータを、別のスプレッドシート内で検索することになり、新しいデータの検索は、既存のデータから右側に向かって行われるようになっています。

たとえば、シート1には氏名が縦方向に並んでおり、シート2にはその氏名と対応するEメールアドレスがランダムに入力されている場合、VLOOKUPを使用すれば、シート2のEメールアドレスをシート1の氏名と同じ順番で取得することができます。このEメールアドレスは、シート2で氏名の右側の列に入力されている必要があり、それ以外の場合は、アドレスの取得ができなくなります。

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

VLOOKUP関数が機能するための鍵となるのは、一意の識別子です。

一意の識別子とは、2つのデータソースに共通している情報で、その名のとおり一意、つまりデータベース内で関連付けられているレコードが1つしかないものを指します。一意の識別子の例としては、製品コードや最小在庫管理単位(SKU)、顧客のコンタクトなどが挙げられます。

HubSpotを始めとするCRMでは、データベース内でコンタクトを一意に識別するためにEメールアドレスを使用しています。そのため、HubSpotのユーザーは、Eメールアドレスを一意の識別子としてVLOOKUP関数を使うことができます。

では、先ほど紹介したVLOOKUPの活用例を基に、手順を見ていくことにしましょう。たとえば、いつコンタクトの取引が成約したのかに加え、コンタクト別のMRR(月間経常収益)がいくらなのかをチェックしたいと仮定します。

しかし、ここで1つの問題が発生します。それは、「成約日」と「MRR」のデータが別々のシートに記載されているということです。「MRR」が記載されているシートでコンタクトを1人ずつ検索してMRRをチェックし、「成約日」が記載されているシート内の対応するコンタクトにマッチさせることを手動で行うことは可能でしょう。しかし、この作業には気が遠くなるほどの時間がかかり、現実的ではありません。

そこでVLOOKUP関数の登場です。参考までに、VLOOKUP関数の構造は次のようになります。

VLOOKUP(検索値, 範囲, 列番号, [検索方法])

この関数の各引数に適切な値を指定し、顧客名を一意の識別子として使用して、顧客別のMRRを特定する手順について、ここから見ていきましょう。

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

Entering a new column title in Excel in order to use the VLOOKUP function

取得するデータがExcel内で作成したピボットテーブルに含まれている場合は、そのデータを新しいスプレッドシートにコピーして、VLOOKUP関数が自由にデータを読み取れるようにしておきます。

次に、データが入力されているセルの隣の列で、一番上のセルに適切なラベルを入力します。ここでは、月間経常収益を表す「MRR」と入力します。この新しい列に、関数で取得した値が表示されることになります。

2. [関数の挿入](Fxのアイコン)をクリックし、選択中のセルにVLOOKUPの数式を挿入する

Excel formula builder with VLOOKUP function highlighted

スプレッドシートの上部に表示されている数式バーの左側に、小さく「Fx」と書かれた手書き風の関数アイコンがあります。先ほど入力した列のラベルのすぐ下にある空のセルを選択した状態で、この関数アイコンをクリックすると、関数を選択する画面が表示されます。

表示されたリストの中からVLOOKUP関数を選び、リストの下部にある挿入ボタンをクリックします。すると、選択中のセルに「=VLOOKUP()」と表示されます。

なお、対象のセルに「=VLOOKUP()」と手動で入力しても差し支えありません。

3. 新しいデータを取得するための検索値を指定する

Lookup value in VLOOKUP formula builder in Excel

最初のセルに「=VLOOKUP」と入力したら、次は数式に含まれる4つの引数を指定していきます。これらの引数は、Excelが検索するデータの範囲と値を絞り込むためのものです。

最初の引数は検索値です。スプレッドシートには、この値に対応するデータが含まれており、Excelはこの値を検索して結果を返します。検索値を指定するには、検索する値が入力されているセルをクリックします。今回の例では、上の画像のように、A2のセルが該当します。A2のセルの顧客の氏名に対応するMRRが、D2のセルに新しいデータとして読み込まれることになります。

検索値に特に制限はなく、テキスト、数値、ウェブサイトのアドレスなど、何でも指定できます。次の手順で説明する参照先のスプレッドシートに、検索値と一致する値が含まれていれば、VLOOKUP関数で必要なデータが返されます。

4. 検索対象のデータが含まれるスプレッドシート内の範囲を指定するTable array field in VLOOKUP formula builder

[範囲]フィールドに、検索対象のシートと検索するセルの範囲を入力します。書式は上のスクリーンショットのとおりです。今回の例では、検索対象のデータが含まれているシートの名前が「Pages」で、検索する範囲はB列からK列までとしています。

なお、検索対象のデータが含まれているシートは、今表示されているExcelファイル内にあることが必要です。つまり、検索対象のデータが、今表示されているシート内の別の表か、同じファイル内の別のシートに含まれている必要があります。別のシートには、下のスクリーンショットのとおり、画面下のタブからアクセスできます。

spreadsheet-tabs-excel

たとえば、検索対象のデータが「Sheet2」のC7~L18の範囲に入力されている場合、範囲には「Sheet2!C7:L18」と指定します。

5. 取得するデータの列番号を指定する

[範囲]フィールドの下には、検索対象の範囲に含まれる[列番号]を入力します。たとえば、検索範囲がB列からK列で([範囲]フィールドの指定は「B:K」)、実際に取得する値がK列に入力されている場合、K列は左端から10番目の列なので、[列番号]フィールドには「10」と入力します。

Column index number field in VLOOKUP formula builder

6. 検索値と完全に一致する値と近似値のどちらを検索するかを指定する

Range lookup field in VLOOKUP formula builder

今回の例では、毎月の収益を知りたいので、検索対象の表から「完全一致」の値を取得する必要があります。そこで、[検索方法]フィールドに「FALSE」と入力します。これにより、検索値とコンタクトの氏名が完全に一致する場合にのみ、各コンタクトに関連付けられている売上高が取得されるようになります。

なお、お気付きのとおり、完全一致の代わりに近似値を検索することもできます。その場合は、先ほどの[検索方法]のフィールドで、「FALSE」の代わりに「TRUE」と入力します。

VLOOKUPの検索方法に近似値を設定すると、検索値と完全に一致する値が見つからない場合、検索値に最も近い値(検索値が数値の場合、検索値以下で最も大きい値)が表示されます。たとえば、ウェブサイトのリンクの一覧に基づいてデータを検索しており、リスト内のリンクには、最初に「https://」が付いているものと付いていないものがあると仮定します。この場合、「https://」という文字列がないことを前提に、検索方法を近似値に設定しておくのも1つの方法です。この方法では、「https://」がなくてもリンクの照合を行うことができ、近似値が見つからない場合はエラーが返されます。

7. 入力を完了する

[検索方法]フィールドを入力し、すべての引数の指定が完了すると、手順1で作成した新しい列の1つ目のセルに値が入力されます。ここで、参照先のスプレッドシートを確認し、入力された値が正しいかどうか確認しておくことをお勧めします。

Entering the first new value from Excel's VLOOKUP formula builder

値が正しければ、残りのセルにも値を入力していきましょう。1つ目のセルをクリックして、セルの右下にある小さな正方形(フィルハンドル)をダブルクリックすると、隣接するセルにデータが入力されているところまで、自動的に入力できます。入力が完了したら、値がすべて正しく表示されていることを確認してください。

Filling a new column with data from Excel's VLOOKUP formula builder

いかがでしたでしょうか?高価な分析ツールをお持ちでなくても、まずはVLOOKUPを活用して、手軽にデータ分析を行ってみてください。

Excelのさまざまな機能について、さらに知識を深めたい方は、HubSpotのExcel活用完全ガイドをご覧ください。マーケティング担当者必見エクセルの使い方

マーケティング担当者必見エクセルの使い方

元記事発行日: 2019年7月09日、最終更新日: 2019年7月10日