2 つの Excel シートの重複を比較する方法: 5 つの簡単���方法

Excel で大規模なデータセットを操作する場合、同様のデータを含む複数のワークシートが存在するのが一般的です。これらのシートを比較して重複レコードがないか確認することは、特にデータがさまざまな列や行に分散している場合には、非常に困難になることがあります。

2 つの Excel シートで重複を見つける最も一般的な方法は、次の方法です。

1. VLOOKUP、COUNTIF、または EXACT 関数

2. 条件付き書式

3. パワークエリ

4. 外部ツールとアドイン

5. 重複を視覚的にチェック

この記事では、複数のワークシートにわたる重複を特定して処理するための 5 つの方法を段階的に説明します。最後には、重複した値の管理がいかに簡単であるかがわかるでしょう。

始めましょう！

目次

• サンプルワークシートとデータ
• 1. VLOOKUP、COUNTIF、または EXACT 関数を使用して重複を検索する
  • A. VLOOKUP 関数を使用して 2 つのシートの重複を見つけるにはどうすればよいですか?
    • 異なるワークブックを処理している場合はどうなりますか?
  • B. COUNTIF 関数を使用してワークシート間で重複を見つけるにはどうすればよいですか?
  • C. EXACT 関数を使用してワークシート間で重複を見つけるにはどうすればよいですか?
• 2. 重複行に条件付き書式設定を使用する方法
  • 条件付き書式ルールマネージャーの使用方法
• 3. Power Query を使用してワークシート間で重複を検索する方法
• 4. ワークシート間の重複を特定するツールとアドイン
• 5. 2 つのシートの重複を視覚的に確認する方法
• Excel ワークシートを準備するための 3 つのヒント
• エラーと不一致に対処する方法
• 最終的な考え

サンプルワークシートとデータ

この記事の例に従うには、最初のシートの列 A に次のデータを含む新しい Excel ブックを作成します。

• りんご

• オレンジ

• 梨

• いちご

このデータを 2 番目のシートの列 A に入力します。

• 梨

• いちご

• りんご

• パイナップル

ワークシートは次のようになります。

ワークシートを設定したら、VLOOKUP、COUNTIF、EXACT 関数から始めて、2 つのシートで重複を検索するさまざまな方法を説明します。

さあ行こう！

1. VLOOKUP、COUNTIF、または EXACT 関数を使用して重複を検索する

Excel には、重複を簡単に検索できる 3 つの組み込み関数VLOOKUP、COUNTIF、およびEXACT があります。

これらの関数は、スプレッドシート内のデータの検索、カウント、比較を支援するように設計されており、重複エントリを見つけるための理想的なツールとなります。

各関数は独自の目的を果たし、その使用方法を理解することでデータ分析プロセスを大幅に効率化できます。

次のセクションでは、VLOOKUP、COUNTIF、EXACT 関数を使用して Excel ワークシート内の重複を効果的に特定する方法を説明します。

A. VLOOKUP 関数を使用して 2 つのシートの重複を見つけるにはどうすればよいですか?

VLOOKUP は垂直検索の略です。この関数を使用して、2 つの列の間で重複する値を検索します。構文は次のとおりです。

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

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

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

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

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

Excel ファイル内の 2 つのワークシートにまたがって関数を使用するには、数式で別のシートを参照する方法を知っておく必要があります。これを行うには、シート名に続いて感嘆符 (!) とセルまたはセル範囲を入力します。

たとえば、これは同じブックのシート 2 のセル A2 から A5 を参照します: Sheet 2!$A$2:$A$5。

サンプル スプレッドシートで VLOOKUP 関数を使用するには、次の手順に従います。

1. セル B2 を選択して、最初の比較結果を表示します。

2. 次の数式を入力します: =VLOOKUP(A2,Sheet2!$A$2:$A$5, 1, FALSE)。

3. Enter を押すと比較結果が表示されます。

4. 数式を入力して、最初のシートの残りの行の値を比較します。

結果は以下の図のようになります。

重複が見つからない場合にエラーの代わりにわかりやすいメッセージを表示することで、表示を整理できます。

たとえば、次の式では、見つかった値と見つからなかった値に対してそれぞれ「はい」または「いいえ」が表示されます。

=IF(ISNA(VLOOKUP(A2, シート 2!$A$2:$A$5, 1, FALSE)), “いいえ”, “はい”)

VLOOKUP を使用した 2 つの列の比較に関する記事には、この関数の例がさらに記載されています。

異なるワークブックを処理している場合はどうなりますか?

ワークシートが別々のブックにある場合、関数の使用方法は 2 つの Excel ファイルで同じです。ただし、2 番目のワークシートの参照は少し複雑です。

必要がある：

• Excel ワークブックの名前を括弧で囲みます。

• ワークシートの名前を続けます

• ワークブックとワークシートを引用符で囲みます。

たとえば、セルが「WB 2.xlsx」という名前のワークブックの Sheet2 という名前のシートにある場合、形式は次のようになります。

'[WB 2.xlsx]Sheet2'!$A$2:$A$5

数式を入力する前に、2 番目のブックを閉じてください。そうしないと、エラーが発生します。

B. COUNTIF 関数を使用してワークシート間で重複を見つけるにはどうすればよいですか?

Excel の COUNTIF 関数は、指定された条件を満たす、指定された範囲内のセルの数を数えるために使用されます。

複数のシートを比較するには、最初のワークシートのセルと一致する 2 番目のワークシートのセルの数を数えます。

関数の構文は次のとおりです。

=COUNTIF(範囲、条件)

• Range : 指定した基準に基づいてカウントするセルの範囲。

• 基準: セルがカウントされるために満たさなければならない条件。

サンプル データで関数を使用するには、次の手順に従います。

1. セル B2 を選択して、最初の比較結果を表示します。

2. 次の数式を入力します: =COUNTIF(Sheet2!$A$2:$A$5, A2)

3. Enter を押すと比較結果が表示されます。

4. 数式を入力して、最初のシートの残りの行の値を比較します。

この関数は、一部のセルでは一致するものを 1 つ見つけますが、他のセルでは一致しません。比較セルにはカウントが表示されます。サンプルデータの結果は次のとおりです。

COUNTIF 関数は、Excel で空白以外のセルを数えるなど、他の便利なタスクにも使用できます。

C. EXACT 関数を使用してワークシート間で重複を見つけるにはどうすればよいですか?

Excel の EXACT 関数を使用すると、2 つの異なる Excel ワークシートの同じセル内の重複を検索することもできます。構文は次のとおりです。

=EXACT(テキスト1, テキスト2)

• text1は、比較する最初のテキスト文字列です。

• text2は、比較する 2 番目のテキスト文字列です。

次の手順を実行します：

1. セルB2を選択します。

2. 数式=EXACT(A2, Sheet2!A2)を入力します。

3. Enter を押すと比較結果が表示されます。両方の値が同一の場合、数式は TRUE を返し、それ以外の場合は FALSE を返します。

4. 数式を入力して、最初のシートの残りの行の値を比較します。

Note that this method doesn’t search for duplicates across a cell range. Instead, you are only looking for matches based on the same cell in a different sheet.

This can be useful with ordered data where you only expect a few exceptions.

The VLOOKUP, COUNTIF, and EXACT functions are useful for finding duplicates, but Excel is a versatile program, and there are other options. In the next section, we look at how you can use conditional formatting to identify duplicates in two sheets.

2. How to Use Conditional Formatting for Duplicate Rows

In this section, you’ll learn how to use conditional formatting to find and highlight duplicate rows in two Excel worksheets.

To create a conditional formatting rule, follow these steps:

1. Select the range of cells containing the data (A2:A5 in this case).

2. Click on the “Home” tab in the Excel ribbon.

3. Click on “Conditional Formatting” in the “Styles” group.

4. Choose “New Rule” from the drop-down menu.

The next task is to provide a formula for your rule to use. Follow these steps:

1. Choose “Use a formula to determine which cells to format” in the dialog box

2. Enter the following formula: =COUNTIF(Sheet2!$A$2:$A$5, A2) > 0

Finally, you apply the formatting you prefer for duplicate cells.

1. Click on the Format button to open the Format Cells dialog box.

2. Choose a format e.g. fill duplicates with a yellow background color.

3. Click OK.

Your duplicate data is now highlighted in yellow.

How to Use the Conditional Formatting Rules Manager

Once you’ve created the conditional formatting rule, you can manage it using the Conditional Formatting Rules Manager.

To access the manager:

1. Go to the Home tab.

2. Click on Conditional Formatting.

3. Choose “Manage Rules”.

You will see a list of all conditional formatting rules applied to the selected sheet. You can edit, delete, or change the order of rules by selecting the rule and clicking the appropriate buttons.

To apply the same rule to the other sheet, follow these steps:

1. Select the range you want to compare in the second sheet.

2. Go to the Conditional Formatting Rules Manager.

3. Select the rule, click on Duplicate Rule and then hit Edit Rule.

4. Replace “Sheet2” with the name of the first sheet to compare.

Now that you’ve applied the conditional formatting rule to both sheets, duplicates will be highlighted according to the formatting you’ve chosen.

Make sure to adjust the range and cell references in the formulas as needed to cover all the data you want to compare.

Conditional formatting might seem a little primitive. If you want finer control, then Power Query may be the answer! In the next section, we cover how you can use Power Query to find duplicates.

3. How to Use Power Query to Find Duplicates Across Worksheets

Power Query is a data transformation and data preparation tool in Microsoft Excel. Identifying the same values is just one of the many analysis tasks you can perform with the tool.

To do so, you should first import the data in the two worksheets into separate tables. Follow these steps within each sheet:

1. Right-click the cell range.

2. Choose “Get Data from Table/Range”.

3. Amend the table name to something appropriate.

After importing both sheets, the first task is to merge the data:

1. Go to the Data tab.

2. Click “Get Data”.

3. Select “Combine Queries”.

4. Choose “Merge” and select the two tables.

5. Click on the two key columns.

6. Choose “Inner” as the “Join Kind” and click OK.

The Power Query Editor will open with the combined data from both tables in your Excel sheet. You will see two columns, one from each table. Since you are only interested in the duplicate values, you can remove the second column.

You can click “Close & Load” in the Power Query Editor to load the duplicates to a new worksheet.

To explore more aspects of this powerful feature, follow the examples in our article on how to use Power Query in Excel.

Excel also has third-party tools and add-ins that add the ability to seamlessly find duplicates, so let’s take a look at some of those tools in the next section.

4. Tools and Add-Ins to Identify Duplicates Across Worksheets

外部ツールとアドインは、Excel のネイティブ機能では利用できない高度な機能を提供できます。これらのツールを使用すると、シートの重複を比較するプロセスをさらに効率化できます。

Spreadsheet Compare は、2 つのワークブックを並べて比較し、相違点を強調表示し、重複を簡単に識別できる Microsoft ツールです。Microsoft Web サイトからダウンロードできます。

重複を見つけるプロセスを自動化するためにインストールできるアドインがいくつかあります。その一例が「Duplicate Remover」です。アドインをインストールするには:

1. 「挿入」タブに移動します。

2. 「アドインを入手」をクリックします。

3. 「重複」を検索します。

4. 選択したツールの「追加」をクリックします。

5. 2 つのシートの重複を視覚的に確認する方法

どうやってもダメなら目を使え！Excel の [ウィンドウの配置] ダイアログ ボックスを使用すると、複数のワークシートまたはブックを並べて表示できます。

重複を直接検出するわけではありませんが、ワークシートまたはワークブック間でデータを視覚的に比較して重複を特定するのに役立ちます。次の手順を実行します：

1. Excelのリボンの「表示」タブをクリックします。

2. 「ウィンドウ」グループの「すべて配置」をクリックします。

3. 「垂直」または「水平」などの配置オプションを選択します。

これにより、両方のシートが並べて表示されるか、上下に表示されます。各シートのデータを手動で比較して重複を特定できるようになりました。

データをスクロールして各値を視覚的に検査し、一致する値を見つける必要があります。

この方法は手動での比較が必要なため、大規模なデータセットに対しては効率的ではないことに注意してください。この記事の他の方法を使用すると、大規模なデータセットで重複を見つけるのがより効果的になります。

これが、Excel シート内の重複値を見つけるための一般的な方法の最後の方法です。次のセクションでは、ワークシートを準備するためのヒントをいくつか紹介します。

Excel ワークシートを準備するための 3 つのヒント

複数のシートの比較を開始する前に、データセットの列と行が適切に並んでいることを確認してください。

両方のExcel シートの構造とヘッダー名が同じであることを確認してください。必要に応じて、両方のシートの列を相互に一致するように再配置できます。

正確な比較を行うための 3 つの提案を次に示します。

1. 両方のシートでデータを同じ順序で配置します。これにより、Excel 関数が効果的に動作しやすくなります。

2. 一貫した書式設定、大文字の使用、およびデータ型を使用してデータを正規化します。これにより、些細な違いによるエントリの不一致を防ぐことができます。

3. 比較プロセスを妨げる可能性があるため、不要な空白の行または列を削除します。

一意の値をカウントすることで、データ セット内にどれだけの重複があるかを評価できます。このビデオでは、いくつかの方法を説明します。

エラーと不一致に対処する方法

データの不一致は、比較プロセスに影響を与える可能性があります。不一致を解決するための 4 つのヒントを次に示します。

1. 同じ列内にテキストと数値が混在しているなど、データ型の不一致をチェックします。

2. 日付、数値、その他のデータ型に一貫した書式設定が使用されていることを確認します。

3. Examine your data for missing or incorrect entries, and update if necessary.

4. Standardize abbreviations or inconsistent naming conventions within your data sets.

Final Thoughts

Finding duplicates across two Excel worksheets is an essential task for data management and analysis, ensuring data integrity and accuracy. Excel offers multiple techniques to identify duplicates, each with its own advantages and limitations.

The choice of method depends on the user’s needs, the size and complexity of the dataset, and the desired outcome. For smaller datasets and straightforward comparisons, using VLOOKUP, COUNTIF, or conditional formatting may be sufficient.

大規模なデータセットやより複雑なデータ変換の場合、Power Query は、重複の検索などの幅広いデータ準備タスクを処理できる強力で柔軟なツールです。

最後に、Excel シートの重複を比較することは、ツールボックスに入れておくと非常に便利なスキルです。この記事のテクニックを使えば、厄介な重複を見つけてデータをきれいに保つことができます。

信頼してください。これが上達すれば、データ タスクをスムーズに完了し、周囲の人に好印象を与えることができるようになります。