ExcelでVLOOKUPを使用する方法

VLOOKUP は Excel の最も便利な関数の 1 つですが、最も理解されていない関数の 1 つでもあります。この記事では、実際の例を使用して VLOOKUP をわかりやすく説明します。架空の会社用に使用可能な請求書テンプレートを作成します。

VLOOKUPはExcelの関数です。この記事では、読者が Excel 関数をある程度理解しており、SUM、AVERAGE、TODAY などの基本関数を使用できることを前提としています。最も一般的な使用法では、VLOOKUP はデータベース関数です。つまり、データベーステーブル、またはもっと簡単に言えば、Excel ワークシート内の項目のリストを操作します。どのようなものですか？まあ、どんなことでも構いません。従業員、製品、顧客、CD コレクションの CD、または夜空の星のリストを含むワークシートがあるとします。それはあまり関係ありません。

以下はリストまたはデータベースの例です。この場合、これは架空の会社が販売する製品のリストです。

通常、このようなリストには、リスト内の各項目に対して何らかの一意の識別子があります。この場合、一意の識別子は「商品コード」列にあります。注: VLOOKUP 関数がデータベース/リストを操作するには、そのリストに一意の識別子 (または「キー」、または「ID」) を含む列が必要であり、その列がテーブルの最初の列である必要があります。上記のサンプルデータベースはこの基準を満たしています。

VLOOKUP を使用する際に最も難しいのは、VLOOKUP の用途を正確に理解することです。それでは、まずそれを明確にできるかどうかを見てみましょう。

VLOOKUP は、指定された一意の識別子のインスタンスに基づいてデータベース/リストから情報を取得します。

上の例では、商品コードを含む別のスプレッドシートに VLOOKUP 関数を挿入すると、オリジナルで説明されている、対応する商品の説明、価格、または在庫状況 (「在庫」数量) が返されます。リスト。次の情報のうちどれがあなたに返されますか?まあ、これは式を作成するときに決めることになります。

必要な情報がデータベースから 1 つだけである場合、VLOOKUP 関数を使用して数式を構築するのは非常に手間がかかります。通常、この種の機能は、テンプレートなどの再利用可能なスプレッドシートで使用します。誰かが有効な商品コードを入力するたびに、システムは対応する商品に関する必要な情報をすべて取得します。

この例を作成してみましょう。架空の会社で何度も再利用できる請求書テンプレートです。

まず Excel を起動し、空白の請求書を作成します。

仕組みは次のとおりです。請求書テンプレートを使用する人が「A」列に一連の商品コードを入力すると、システムが製品データベースから各商品の説明と価格を取得します。その情報は、各アイテムの行合計を計算するために使用されます (有効な数量を入力したと仮定します)。

この例を単純にするために、製品データベースを同じワークブック内の別のシートに配置します。

実際には、製品データベースは別のワークブックに配置される可能性が高くなります。 VLOOKUP 関数とほとんど違いはありません。VLOOKUP 関数では、データベースが同じシート上にあるか、別のシート上にあるか、まったく異なるブック上にあるかはあまり関係ありません。

そこで、次のような製品データベースを作成しました。

これから作成する VLOOKUP 式をテストするために、まず、空白の請求書のセル A11 に有効な商品コードを入力します。

次に、アクティブセルを、VLOOKUP によってデータベースから取得した情報を格納するセルに移動します。興味深いことに、これはほとんどの人が間違えるステップです。さらに詳しく説明すると、セル A11 の品目コードに対応する説明を取得する VLOOKUP 式を作成しようとしています。この説明を入手したら、どこに記載すればよいでしょうか?もちろんセルB11です。そこで、セル B11 に VLOOKUP 式を書きます。ここでセル B11 を選択します。

VLOOKUP を選択して数式を完成させるための支援を得るには、Excel が提供する利用可能なすべての関数のリストを見つける必要があります。これを見つけるには、最初に [数式] タブをクリックし、次に [関数の挿入] を クリックします。

ボックスが表示され、Excel で使用できる関数のいずれかを選択できます。

探している関数を見つけるには、「lookup」などの検索語を入力します (対象の関数は検索関数であるため)。システムは Excel のすべての検索関連関数のリストを返します。 VLOOKUP は リストの 2 番目です。それを選択して 「OK」 をクリックします。

[関数の引数] ボックスが表示され、VLOOKUP 関数を完了するために必要なすべての引数 (またはパラメーター) の入力を求められます。このボックスは、次の質問をする関数と考えることができます。

データベース内でどのような一意の識別子を検索していますか?
データベースはどこにありますか?
一意の識別子に関連付けられたデータベースからどの情報を取得したいですか?

最初の 3 つの引数は 太字で 表示されており、それらが必須の引数であることを示しています (VLOOKUP 関数はこれらの引数なしでは不完全であり、有効な値を返しません)。 4 番目の引数は太字ではありません。これはオプションであることを意味します。

引数を上から下の順序で完成させます。

完了する必要がある最初の引数は Lookup_value 引数です。この関数では、説明を返す必要がある一意の識別子 (この場合は商品コード) の場所を伝える必要があります。先ほど（A11）に入力した商品コードを選択する必要があります。

最初の引数の右側にあるセレクターアイコンをクリックします。

次に、品目コード (A11) を含むセルを 1 回クリックし、 Enter キー を押します。

「A11」の値が第一引数に挿入されます。

次に、 Table_array 引数の値を入力する必要があります。言い換えれば、VLOOKUP にデータベース/リストの場所を指示する必要があります。 2 番目の引数の横にあるセレクターアイコンをクリックします。

次に、データベース/リストを見つけて、ヘッダー行を除くリスト全体を選択します。この例では、データベースは別のワークシートに配置されているため、まずそのワークシートのタブをクリックします。

次に、ヘッダー行を除くデータベース全体を選択します。

… Enter を 押します。データベースを表すセルの範囲 (この場合は「’製品データベース’!A2:D7」) が 2 番目の引数に自動的に入力されます。

次に、3 番目の引数 Col_index_num を入力する必要があります。この引数を使用して、A11 の品目コードに関連付けられたデータベースのどの情報を返したいかを VLOOKUP に指定します。この特定の例では、商品の説明を返してもらいたいと考えています。データベースワークシートを見ると、「説明」列がデータベースの 2 番目の列であることがわかります。これは、 Col_index_num ボックスに値「2」を入力する必要があることを意味します。

「説明」列はワークシートの B 列にあるため、ここでは「2」を入力しないことに注意することが重要です。データベースがワークシートの K 列から始まった場合でも、「説明」列は「Table_array」を指定するときに選択したセルのセットの 2 番目の列であるため、このフィールドには「2」を入力します。

最後に、最後の VLOOKUP 引数 Range_lookup に値を入力するかどうかを決定する必要があります。この引数には true または false の 値が必要です。そうでない場合は、空白のままにする必要があります。データベースで VLOOKUP を使用する場合 (90% の場合は当てはまります)、この引数に何を入れるかを決定する方法は次のように考えることができます。

データベースの最初の列 (一意の識別子を含む列) がアルファベット順または数値順に昇順に並べ替えられている場合、この引数に true の値を入力するか、空白のままにすることができます。

データベースの最初の列がソートされていない場合、または降順でソートされている場合は、この引数に値 false を入力する必要があります。

データベースの最初の列はソートされていないため、この引数に false を入力します。

それでおしまい！ VLOOKUP が必要な値を返すために必要な情報をすべて入力しました。 [OK] ボタンをクリックすると、品目コード「R99245」に対応する説明がセル B11 に正しく入力されていることを確認します。

作成された式は次のようになります。

セル A11 に別の商品コードを入力すると、VLOOKUP 関数の威力がわかります。新しい商品コードに一致するように説明セルが変更されます。

同様の一連の手順を実行して、セル E11 に返される品目の価格を取得できます。新しい数式はセル E11 に作成する必要があることに注意してください。結果は次のようになります。

…式は次のようになります。

2 つの式の唯一の違いは、3 番目の引数 ( Col_index_num ) が「2」から「3」に変更されていることです (データベースの 3 番目の列からデータを取得する必要があるため)。

これらの商品を 2 つ購入することに決めた場合、セル D11 に「2」を入力します。次に、セル F11 に簡単な数式を入力して、行の合計を取得します。

=D11*E11

…こんな感じです…

請求書のテンプレートを完成させる

これまで VLOOKUP について多くのことを学びました。実際、この記事でこれから学ぶことはすべて学びました。 VLOOKUP はデータベース以外の状況でも使用できることに注意することが重要です。これはあまり一般的ではないため、今後のハウツーオタクの記事で取り上げられる可能性があります。

請求書のテンプレートはまだ完成していません。それを完了するには、次のことを行います。

セル A11 からサンプル品目コードを削除し、セル D11 から「2」を削除します。これにより、新しく作成した VLOOKUP 数式にエラーメッセージが表示されます。これは、Excel の IF() 関数 と ISBLANK() 関数を適切に使用することで解決できます。式を次のように変更します… =VLOOKUP(A11,’製品データベース’!A2:D7,2,FALSE) …次のように… =IF(ISBLANK(A11),””,VLOOKUP(A11, 「製品データベース」!A2:D7,2,FALSE))
セル B11、E11、F11 の数式を請求書の品目行の残りの部分にコピーします。これを行うと、結果の数式がデータベーステーブルを正しく参照できなくなることに注意してください。この問題は、データベースのセル参照を絶対セル参照に変更することで修正できます。あるいは、さらに良い方法として、製品データベース全体の範囲名 (「Products」など) を作成し、セル参照の代わりにこの範囲名を使用することもできます。式は次のように変化します… =IF(ISBLANK(A11),””,VLOOKUP(A11,’製品データベース’!A2:D7,2,FALSE)) … =IF(ISBLANK) (A11),””,VLOOKUP(A11,Products,2,FALSE)) … そして、数式を残りの請求書項目行にコピーします。
おそらく、誰かが請求書を記入しに来たときに、慎重に作成した数式が誤って上書きされないように、数式を含むセルを「ロック」し (むしろ他のセルのロックを解除し)、ワークシートを保護するでしょう。
ファイルをテンプレートとして保存し、社内の全員が再利用できるようにします。