VLOOKUP を使用して Excel の 2 つの列を比較する方法

Excel で大規模なデータセットを操作する場合、類似点または相違点を見つけるために 2 つの列を比較する必要がある場合があります。

VLOOKUP は、2 つの列間で一致するデータを検索できる強力な関数です。この関数を使用すると、ある列の値が別の列に表示されるものを検索できます。

この記事では、VLOOKUP を使用して 2 つの列を比較し、データを効率的に分析する方法を説明します。

目次

• Vlookup 関数の基礎
  • Vlookup関数
• VLOOKUP 用にデータを準備する
  • データのフォーマット
• VLOOKUP を使用して 2 つの列を比較する方法
  • その他のエラーの処理
• 列比較のための VLOOKUP の代替手段
  • 1. Index 関数と Match 関数の使用
  • 2. Excel での XLOOKUP の使用
• VLOOKUP のパフォーマンスを向上させる 5 つのヒント
  • 1.検索範囲を制限する
  • 2. 絶対参照を使用する
  • 3. データを並べ替える
  • 4. ダブル VLOOKUP を使用する
  • 5. 条件付き書式を使用する
• VLOOKUP の高度な使用法
• 私たちの最後の言葉

Vlookup 関数の基礎

列 A と列 B に項目の 2 つのリストが含まれるスプレッドシートがあるとします。リスト 2 にも表示されるリスト 1 の項目を検索したいとします。

何千もの項目のリストを手動で処理するには、非常に時間がかかることが想像できます。ありがたいことに、Excel ではVLOOKUP が役に立ちます。

VLOOKUP という用語は、Vertical Lookup の略です。この関数は 2 つの列を比較し、それらの間の一致を見つけて、関連付けられた値を返します。

Vlookup関数

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

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

要素の内訳は次のとおりです。

• lookup_value : table_array の最初の列で検索する値。

• table_array : 検索するデータを含むセルの範囲。

• Col_index_num : 値を返す table_array 内の列番号。

• range_lookup : オプション。TRUE (近似一致) または FALSE (完全一致) のいずれかです。デフォルトはTRUEです。

VLOOKUP 用にデータを準備する

VLOOKUP を使用してExcel の2 つの列を比較する前に、データを準備する必要があります。

ワークシート内に値を比較する 2 つの別々の列を作成します。この記事では、例として列 A と列 B を使用します。

この記事で使用されているサンプル データは次のとおりです。

これは、「VLOOKUP 用のデータの準備」というタイトルのセクションに追加されるヘッダー付きのサンプル テーブルです。

リスト 1 リスト 2
項目 1 項目 3
項目 2
項目 4 項目 3 項目 1
項目 4 項目 5

データのフォーマット

両方の列のデータが同じようにフォーマットされていることを確認することが重要です。VLOOKUP では大文字と小文字が区別されます。つまり、大文字と小文字が区別されます。

形式が一貫していない場合、最終結果で照合エラーが発生する可能性があります。

エラーのリスクを最小限に抑えるために、重複する値や空白のセルを削除することもお勧めします。

重複した値を削除するには:

1. 列を選択します。

2. 上部のリボンの「データ」タブに移動します。

3. 「データツール」セクションで「重複の削除」をクリックします。

ボタンが少し見づらいかもしれません。この図は次のことに役立ちます。

空白セルを削除するには:

1. 列を選択します。

2. 上部のリボンの「ホーム」タブに移動します。

3. 「検索と選択」メニューを展開します。

4. 「スペシャルへ移動」を選択します。

5. オプションから「ブランク」を選択します。

6. 「セル」セクションの「削除」をクリックします。

VLOOKUP を使用して 2 つの列を比較する方法

データが準備されたら、VLOOKUP 式を記述して Excel の 2 つの列を比較し、一致を特定できます。次の手順を実行します：

1. 比較結果を表示する新しい列のセルを選択します (セル C2 など)。

2. 次の数式を入力します: =VLOOKUP(A2, B:B, 1, FALSE))

3. Enter キーを押して数式を適用します。

4. 値が一致する場合、その値は結果列の同じ行 (列 C など) に表示されます。

5. 数式を C2 から下にドラッグして、必要な数のセルにコピーします。

サンプル データを使用している場合は、比較結果を 3 番目の列に入力します。この数式は、リスト 1 の 4 つの項目の中から一致する 3 つを見つけます。

項目 2 が #N/A と表示されていることに注意してください。Excel は、一致が適用できない、つまり一致が見つからなかったことを示しています。

それは正しく、欠損値に関する有益な情報です。ただし、Excel ユーザーの中には、データの問題または関数エラーがあると考える人もいるかもしれません。

一致するものが見つからなかったことを別の方法で示すことをお勧めします。それは単に空白である可能性があります。

これを行うには、次のように VLOOKUP 関数と IFNA 関数を組み合わせます。

=IFNA(VLOOKUP(A1, B:B, 1, FALSE), “”)

IFNA 関数は #N/A エラーを検出し、出力を空のスペース ("") に置き換えます。ISNA 関数を使用したり、IF 関数を使用して追加のロジックを使用したりすることもできます。

その他のエラーの処理

VLOOKUP では、比較結果に他のエラーが発生する場合があります。#REF ! Excel のエラーは、データに関する真の問題です。

通常、指定した範囲が正しくない場合に発生します。この例では列全体を参照していますが、垂直方向のセル範囲を使用することもできます。

参照している検索範囲が、比較するすべての値をカバーしていることを確認してください。

列比較のための VLOOKUP の代替手段

Excel で 2 つの列を比較して一致を見つける場合、主に 2 つの代替検索関数があります。

1. Index 関数と Match 関数の使用

VLOOKUP を使用する代わりに、INDEX 関数と MATCH 関数を組み合わせることにより、Excel で 2 つの列を比較できます。

この方法は、データを検索するためのより柔軟な方法を提供し、隣接しない列を操作する場合や列インデックスが変更される可能性がある場合に特に便利です。

これは結果列に入力する構文です。

=INDEX(戻り値の範囲, MATCH(検索値, 検索範囲, 0))

• Return_range : 返したいデータを含むセルの範囲。

• Lookup_value : ルックアップ範囲内で検索する値。

• Lookup_range : ルックアップ値を検索するセルの範囲。

前の例と同じデータを使用して、VLOOKUP 式を次のように置き換えます。

=(INDEX(B:B, MATCH(A2, B:B, 0)))

MATCH 関数は検索範囲内の検索値の相対位置を返し、INDEX 関数は戻り範囲から対応する値を返します。

同じデータに対する結果は、前に使用した VLOOKUP と同じになります。この図は、使用中の関数を示しています。

#N/A エラーをカスタム メッセージに置き換えることもできます。IFERROR 関数の使用例を次に示します。

=IFERROR(INDEX(B:B, MATCH(A2, B:B, 0)), “見つからない”)

2. Excel での XLOOKUP の使用

Excel 365 または Excel 2019 を使用しているユーザーにとって、XLOOKUP は、2 つの列に共通する値を見つけるための VLOOKUP の代替論理テストです。

XLOOKUP にはいくつかの利点があります。これを使用すると、データを水平方向と垂直方向の両方で検索したり、隣接しない列を操作したり、エラーのカスタム値を指定したりできます。

XLOOKUP の構文は次のとおりです。

=XLOOKUP(検索値、検索範囲、戻り値範囲、[見つからない場合]、[一致モード]、[検索モード])

• lookup_value : ルックアップ範囲内で検索する値。

• lookup_range : ルックアップ値を検索するセルの範囲。

• return_range : 返したいデータを含むセルの範囲。

• 必要に応じて、カスタム エラー値、一致モード、および検索モードのパラメーターを追加します。

XLOOKUP は、ルックアップ範囲内でルックアップ値を検索し、戻り範囲から対応する値を返します。

この数式は、複数の列のデータ セットに 2 つのリストが埋め込まれている場合に特に便利です。前の例では、リストは最初の列と 2 列目にありましたが、シートにはそれより多くのデータが含まれる可能性があります。

以下は、スプレッドシートの 1 列目と 3 列目に 2 つのリストがある例です。

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

=XLOOKUP(A2,C:C,C:C,””)

この図は、列 D の比較結果を示しています。最初の値は存在しますが、2 番目の値がありません。

追加のエラー式はありませんが、欠損値が空白として表示されることに注意してください。これは、カスタム エラー値である 4 番目のパラメーターを使用しているためです。この場合、それは空の文字列です。

おまけとして、XLOOKUP を使用して行間を比較する方法を紹介します。行 1 と行 2 にある 2 つのリストがある場合、数式の最も単純な使用法は次のようになります。

=XLOOKUP(B1, 2:2, 2:2, “”)

この図は 2 つの行の結果を示しています。

機能と柔軟性が向上した XLOOKUP は、Excel でリストを比較して一致するものが存在するかどうかを判断するための VLOOKUP の優れた代替手段です。

VLOOKUP のパフォーマンスを向上させる 5 つのヒント

VLOOKUP 関数を最適化すると、長い待ち時間を回避し、Excel ワークシートの全体的な応答性を向上させることができます。

ここでは、Excel での VLOOKUP のパフォーマンスを向上させるために適用できる 6 つのヒントとコツを紹介します。

1.検索範囲を制限する

簡単にするために、例では列全体を使用しました。大量のデータを扱う場合は、そうしないようにしてください。列全体を使用すると、Excel の計算プロセスが遅くなる可能性があります。

代わりに、データに必要な正確な範囲 (A1:A100 など) を使用するようにしてください。これにより、VLOOKUP 関数で評価する必要があるセルの数が減ります。

2. 絶対参照を使用する

データ範囲 (例: B2 から B5 のセル) を指定する場合は、絶対参照を使用します。これにより、数式を複数のセルにコピーした場合でも、数式に一貫性があり、データが存在することが保証されます。

以下に例を示します。

=VLOOKUP(A2, $B$2:$B$5, 1, FALSE)

3. データを並べ替える

ルックアップ列のデータが昇順で並べ替えられていることがわかっている場合は、'range_lookup' 引数に TRUE または 1 を指定して VLOOKUP を使用できます。

これにより、Excel は近似一致を実行します。これは、すべてのセルの完全一致よりも高速です。ただし、並べ替えが正しくないと誤った結果が生じる可能性があるため、このオプションを使用する場合は注意してください。

4. ダブル VLOOKUP を使用する

2 つの VLOOKUP 関数を使用すると、Excel での検索プロセスを高速化できます。

最初の VLOOKUP は、「col_index_num」を 1 に設定し、「range_lookup」を TRUE に設定することによって、ルックアップ値が存在するかどうかを判断します。

TRUE が返された場合、2 番目の VLOOKUP は、「range_lookup」を TRUE に設定して目的の値を取得します。

5. 条件付き書式を使用する

Excel ワークシートで条件付き書式を使用すると、指定した列の一致する値または欠落している値を強調表示できます。固有の値に色を適用することもできます。これにより、データが読みやすくなります。

条件付き書式メニューは、[ホーム] タブのスタイル グループにあります。

VLOOKUP の高度な使用法

対応のあるサンプルの T 検定は、2 つの関連するサンプルの平均を比較するために使用されます。このビデオでは、Excel での使用法を示します。

分析中に参照する必要がある追加のデータまたは変数がある場合は、VLOOKUP を使用して、別のテーブルまたはワークシートから必要な値を取得できます。

たとえば、VLOOKUP を使用して、データセット内のペアになった観測ごとに人口統計情報や治療条件を取得できます。

私たちの最後の言葉

ここまでで、VLOOKUP の使用方法をしっかりと理解できました。この強力な機能を使用すると、2 つのリスト間の相違点と一致する値を迅速に識別できるため、データ分析がより効率的かつ正確になります。

XLOOKUP、INDEX、MATCH など、他の検索関数と参照関数の例も見ました。これらの要素をスキルに追加すると、データ分析能力がさらに強化されます。

VLOOKUP スキルを練習して磨き続ければ、すぐに Excel で列を比較するエキスパートになり、時間を節約し、生産性を向上させることができます。