2 つのワークブック間の VLOOKUP: 簡単なステップバイステップ ガイド

Excel で複数のブックを操作している場合は、VLOOKUP 関数を使用して、あるブックから別のブックにデータを取得することが必要になる場合があります。

VLOOKUP 関数は、別のブックのテーブルで特定の値を検索し、同じ行から対応する値を返します。構文には、2 番目のワークブックとワークシート名への完全修飾参照が含まれている必要があります。

関数を初めて使用する場合、参照を正しく取得するのは難しい場合があります。この記事では、サンプル スプレッドシートを使用して、数式を入力する方法を正確に説明します。

また、VLOOKUP のいくつかの機能拡張と、この関数のいくつかの代替手段についても学習します。

始めましょう！

目次

• VLOOKUPとは何ですか?
• 2 つのワークブック間で VLOOKUP を使用する方法
  • サンプルデータ
  • ステップ 1: 両方のワークブックを開いたままにする
  • ステップ 2: VLOOKUP 数式の入力を開始する
  • ステップ 3: 2 番目のワークブックを参照する方法
• 3 つの一般的な VLOOKUP エラーとその修正方法
  • 1.#N/Aエラー
  • 2. #参照! 列番号エラー
  • 3. 引用符と角括弧のエラー
• さまざまなワークブックを管理するための 4 つのヒント
• データを自動的に更新する方法
• VLOOKUP関数の高度な使い方
• VLOOKUP の 2 つの主な代替手段
  • 1. インデックスマッチ
  • 2.XLOOKUP
• 複数のシートで VLOOKUP を使用する方法
  • ステップ 1: コンポーネントを特定する
  • ステップ 2: データを設定する
  • ステップ 3: VLOOKUP 関数を使用する
  • (オプション) ステップ 4: #N/A エラーのトラブルシューティング
• 最終的な考え

VLOOKUPとは何ですか?

製品の売上に関するワークブックと、各製品の価格を記載した別のワークブックがあるとします。2 番目のワークブックの価格にアクセスし、それを最初のワークブックの計算に使用したいと考えています。

VLOOKUP は、このタスクに対する 1 つのソリューションです。この用語は「Vertical Lookup」の略です。「

これは、列内の特定の値を検索し、その位置に基づいて別の列の対応する値を返すことができる組み込み関数です。

以下と併用できます。

• 単一のスプレッドシート

• 同じワークブック内の複数のシート

• 2 つの Excel ワークブック

複数のワークブックによる複雑さが問題でない場合は、VLOOKUP を使用して Excel の 2 つの列を比較するこのチュートリアルを確認してください。

2 つのワークブック間で VLOOKUP を使用する方法

VLOOKUP 関数は、複数のワークシートまたはブックにまたがってデータを検索する必要がある場合に特に便利です。

そのための主な手順は次の 3 つです。

1. 両方のワークブックを開きます。

2. 最初の Excel ブックに VLOOKUP 数式を入力します。

3. 2 番目のワークブックへの完全修飾参照を提供します。

サンプルデータ

この簡単なチュートリアルでは、サンプル データに基づいた例を使用して説明します。手順に従って、「Sales」という名前の新しいワークブックを作成し、列 A と列 B に次のデータを入力します。

• リンゴ、40個

• オレンジ、30個

• 梨、70

• バナナ、20本

価格情報を保持する 3 番目の列を追加します。

「Product」という 2 番目の Excel ワークブックを作成し、Sheet1 の名前を「Prices」に変更します。次のデータを列 A と列 B に入力します。

• リンゴ、1.50

• オレンジ、2.30

• 梨、3.60

• バナナ、2.10

データは次のようになります。

最初のスプレッドシートで売上の収益を計算するには、2 番目のスプレッドシートで価格を調べる必要があります。VLOOKUP が役に立ちます!

ステップ 1: 両方のワークブックを開いたままにする

2 つのブック間で VLOOKUP 式を使用する場合は、両方のブックが開いている必要があります。2 番目のブックが閉じられている場合、関数はエラーを返します。

これらを個別に開くことも、Excel の「開く」コマンドを使用して両方を一度に開くこともできます。いずれかのワークブックを開くには 2 つの方法があります。

• 「ファイル」タブに移動し、「開く」を選択します。

• 「Ctrl + O」キーボードショートカットを使用します。

両方のワークブックを開いたら、Excel ウィンドウの下部にある適切なタブをクリックしてワークブックを切り替えることができます。

ステップ 2: VLOOKUP 数式の入力を開始する

VLOOKUP 関数の基本的な構文は次のとおりです。

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

• lookup_value : 他のワークブックで検索する値。

• table_array : 取得するデータを含む他のブック内のセル範囲。

• Col_index_num : 取得するデータを含む範囲内の列番号。

• range_lookup : この最後のパラメータはオプションです。TRUE に設定するか省略すると、VLOOKUP は近似一致を検索します。FALSE に設定すると、VLOOKUP は完全一致のみを検索します。

サンプル データの 2 番目のワークブックでリンゴの価格を調べたいとします。この式は次のように分解されます。

• lookup_value は、最初のブックのセル A2 にあります。

• テーブル配列は、2 番目のブックのセル範囲 A1:B5 にあります。

• Col_index_num は、価格を保持する 2 番目の列です。

VLOOKUP 数式の最も複雑な部分は、2 番目のブックを正しく参照することです。

ステップ 3: 2 番目のワークブックを参照する方法

2 つのブックの VLOOKUP 式を完了するには、2 番目のブックの次の詳細を含む完全修飾参照を使用する必要があります。

• ワークブック名

• シート名

• セル範囲

これにより、探しているデータがどこにあるかを Excel が正確に認識できるようになります。完全修飾参照は、VLOOKUP 関数の table_array 引数に使用されます。

これらはサンプル データの値です。

• ワークブック名​​: Product.xlsx

• シート名: 価格

• セル範囲: $A1:$B5

最初のアイテムの価格を調べるには、最初のブックのセル C2 に次の数式を入力します。

=VLOOKUP(A2, [Product.xlsx]価格!$A$1:$B$5, 2, FALSE)

この図は、最初のセルの数式バーで動作している関数を示しています。

ワークブック名​​が角括弧で囲まれ、シート名の後に感嘆符が続くことに注意してください。

Excel ファイル名にスペースまたは特殊文字が含まれている場合は、ワークブックとシート全体を一重引用符で囲む必要があります。

数式では、セル範囲の絶対値 ($ プレフィックス) が使用されます。これにより、塗りつぶしハンドルをクリックして式を残りの行にコピーできます。

VLOOKUPの結果は次のとおりです。

以上です！VLOOKUP を効果的に使用する方法がわかりました。エラーが発生した場合に役立つように、次のセクションで最も一般的な 3 つの VLOOKUP エラーをまとめました。

3 つの一般的な VLOOKUP エラーとその修正方法

別のブックから VLOOKUP を使用する方法を学習したので、いくつかの一般的なエラーが発生する可能性があります。

Here are the three most frequent errors and how to fix them.

1. #N/A Error

The #N/A error occurs when the lookup value is not found in the table array. To fix this error, you can try the following:

• Check the spelling of the lookup value.

• Make sure that the lookup value is in the first column of the table array.

• Check that the lookup value is not formatted as text in the two sheets.

• Ensure that the range_lookup argument is set to FALSE to find an exact match.

The above example has failed to find a matching value for Cherries in the second workbook.

2. #Ref! Column Number Error

The #Ref! Error happens when the col_index_num argument is greater than the number of columns in the table array.

To fix this error, check that the col_index_num argument is less than or equal to the number of columns in the source table.

In the picture below, the col_index_num has been set to 3 but there are only two columns in the reference range.

3. Quotes and Square Brackets Error

The Quotes and Square Brackets Error happens when you perform VLOOKUP with a value containing quotes or square brackets.

To fix this error, you can try the following:

• Use double quotes around the lookup value.

• Use the CHAR(34) function to insert double quotes.

• Use the IFERROR function to return a blank cell if the lookup value contains quotes or square brackets.

Once you’re familiar with these errors, fixing them is straightforward! To further help you become a VLOOKUP pro, we’ve provided four tips for managing different workbooks in the next section.

4 Tips for Managing Different Workbooks

When working with multiple workbooks, it is important to keep track of which workbook contains which data.

Here are our four best tips to keep your sanity:

1. Use naming conventions to help you remember which Excel spreadsheets contain which data.

2. Use absolute references in your formulas to ensure that the lookup range references the correct cells even if you move or copy the formula to another location or Excel sheet.

3. Use the IFERROR function to handle any #N/A errors that may occur if the lookup values are not found in the source data.

4. Consider locking the cells in the reference workbook so that they can’t be overwritten.

How to Update Data Automatically

When working with multiple Excel files, it can be time-consuming to update the data manually.

他のブックにデータを追加または削除したときに VLOOKUP 数式が自動的に更新されるようにするには、INDIRECT 関数を使用します。

その方法は次のとおりです。

• VLOOKUP 数式で、table 配列引数を INDIRECT 関数に置き換えます。

• INDIRECT 関数では、他のブックのテーブル配列を含むセルへの参照を使用します。

サンプル ワークブックでこれを実現するには、次の手順に従います。

1. D 列の 1 つのセルを使用して、「Product」ワークブックの「Prices」シートへの参照とセル範囲 A1:B5 を次のように入力します。 [Product.xlsx]Prices!A: B

2. 「Sales」ワークブックのセル C2 で、次の数式を使用します: =VLOOKUP(A2, INDIRECT($D$1), 2, FALSE)

3. 数式を下にコピーします (絶対参照により、スプレッドシートのセルが保持されます)。

これらのアクションにより、元のシートの変更が接続されたセルで自動的に更新されます。

この図は、INDIRECT 関数と VLOOKUP を組み合わせたものを示しています。

この例では、範囲「A:B」を使用して 2 つの列を参照していることに注意してください。これは、さらに製品と価格を追加しても、数式は現在のワークブックで引き続き機能することを意味します。

VLOOKUP の機能を理解したところで、このセクションで関数の高度な使用法をいくつか見てみましょう。

VLOOKUP関数の高度な使い方

この記事の例では、検索値ごとに 1 つの一致を返します。別のブックからの複数の一致を処理する場合は、代わりに配列数式を使用できます。

VLOOKUP を他の関数と組み合わせることもできます。たとえば、これを IF 関数内に埋め込んで、さらに複雑な分析を行うことができます。

複雑な分析には、カイ二乗検定や対応のあるサンプルの T 検定などの手法が含まれます。Excel でカイ二乗検定を使用する例を次に示します。

上級ユーザーは VLOOKUP を利用してデータ セットを比較し、不一致を特定します。この記事では、 Excel での不一致を見つける他の方法を説明します。

しかし、何らかの理由で VLOOKUP を使用したくない場合はどうすればよいでしょうか? あなたは幸運です！いくつかの代替手段があり、それについては次のセクションで説明します。

VLOOKUP の 2 つの主な代替手段

さまざまなシートの値を検索する場合、多くの場合、VLOOKUP が頼りになります。ただし、特定の状況で役立つ代替手段がいくつかあります。

最も一般的なのは次の 2 つです。

• インデックスマッチ

• XLOKUP

1. インデックスマッチ

INDEX と MATCH は、テーブル内の値を検索するために使用できる 2 つの関数の強力な組み合わせです。ルックアップ値がテーブルの左端の列にある必要がある VLOOKUP とは異なり、INDEX MATCH は任意の列の値をルックアップできます。

仕組みは次のとおりです。

1. INDEX 関数は、行番号と列番号に基づいて、指定された範囲から値を返します。

2. MATCH 関数は、指定された範囲内のルックアップ値の位置を返します。

INDEX MATCH を使用するには、検索する範囲、検索値を検索する行、および結果を返す列を指定する必要があります。

式の例を次に示します。

=INDEX(テーブル範囲,MATCH(ルックアップ値,ルックアップ範囲,0),列インデックス)

これをサンプル スプレッドシートで使用するには、次の式になります。

=INDEX([Product.xlsx]価格!$B$1:$B$5, MATCH(A2, [Product.xlsx]価格!$A$1:$A$5, 0))

これは次のように分類されます。

• MATCH 関数は、「Product」ワークブックの「Prices」シートの範囲 A1:A5 内のセル A2 (製品名) の値を検索します。

• INDEX 関数は、MATCH 関数によって提供される行番号に基づいて、「Product」ワークブックの「Prices」シートの範囲 B1:B5 から値を返します。

この図は、実際の数式を示しています。

2.XLOOKUP

XLOOKUP は、 Excel 365で導入された新しい関数です。VLOOKUP に似ていますが、いくつかの追加機能があります。

XLOOKUP の最大の利点の 1 つは、ルックアップ列の左側の値を検索できることです。

仕組みは次のとおりです。

1. XLOOKUP 関数は、ルックアップ値に基づいて、指定された範囲から値を返します。

2. 完全一致するか、近似一致するかを指定できます。

3. 一致するものが見つからなかった場合に何を返すかを指定することもできます。

XLOOKUP を使用するには、ルックアップ値、検索する範囲、結果を返す列を指定する必要があります。

式の例を次に示します。

=XLOOKUP(LookupValue,LookupRange,ReturnRange,”見つかりません”,0)

これをサンプル スプレッドシートで使用するには、次の式になります。

=XLOOKUP(A2, [Product.xlsx]価格!$A$1:$A$5, [Product.xlsx]価格!$B$1:$B$5)

これは次のように分類されます。

• A2: 調べたい値（製品名）です。

• [Product.xlsx]Prices!$A$1:$A$5: ルックアップ値を検索する範囲 (「Product」ワークブックの「Prices」シート内の製品名)。

• [Product.xlsx]Prices!$B$1:$B$5: 戻り値 (「Product」ワークブックの「Prices」シート内の価格) を含む範囲。

実際の式は次のとおりです。

複数のシートで VLOOKUP を使用する方法

2 つのブック間で VLOOKUP を実行する必要がある場合は、複数のシート間で VLOOKUP を使用する必要がある場合もあります。

幸いなことに、そのプロセスは 2 つのブック間で VLOOKUP を実行する場合と非常に似ています。

ステップは 4 つあります。

ステップ 1: コンポーネントを特定する

1 つのシートの場合のようにテーブル配列を含めるのではなく、シート名も含める必要があります。

たとえば、Sales_reports.xlsx ワークブックの 1 月シートで範囲 A2:B6 の値を検索する場合、数式は次のようになります。

=VLOOKUP(A2,[Sales_reports.xlsx]1月!$A$2:$B$6,2,FALSE)

ステップ 2: データを設定する

複数のシートにわたって簡単に参照できる方法でデータが編成されていることを確認してください。

特定の参照データに対して別のタブを作成することを検討することもできます。

ステップ 3: VLOOKUP 関数を使用する

データを設定したら、VLOOKUP 関数を使用して他のシートからデータを検索して取得できます。

上に示したように、数式には必ずシート名を含めてください。

(オプション) ステップ 4: #N/A エラーのトラブルシューティング

複数のシートで VLOOKUP を使用するときに #N/A エラーが発生した場合は、数式を再確認して、必要なシートと範囲をすべて正しく参照していることを確認してください。

範囲検索ではなく完全一致を実行していることを確認することもできます。

最終的な考え

VLOOKUP を使用して 2 つのスプレッドシートにまたがるデータを検索および取得する方法を学習しました。この多用途関数により、あるワークブックの値を、別のワークブックの対応する値に基づいて簡単に検索できるようになります。

VLOOKUP 関数の機能は単一のワークシートをはるかに超えて拡張され、さまざまなワークブックにまたがってデータをリンクして分析します。

この関数をマスターすることは、データの管理と解釈の新たな可能性を開くため、Excel に習熟するための重要なステップとなります。

INDIRECT、INDEX-MATCH、XLOOKUP などの関数を組み込むことにより、データ検索の柔軟性と有効性をさらに高めることができます。

Excel の膨大な機能を練習して探索し続けてください。これは氷山の一角にすぎません。楽しいデータハンティングを！