Excelで2つのシートを比較して一致・片方のみのデータを抽出する方法｜重複チェック手順を解説

Excelでは、2つのシートにあるデータを比較して「両方に存在するデータ」「片方のシートにしか存在しないデータ」を分類することができます。例えば、1回目のワールドカップ出場国と2回目の出場国を比較する場合も、関数を使えば簡単に3パターンへ分類できます。

この記事では、Excel初心者でもできるように、2つのリストを比較して一致・不一致データを抽出する具体的な手順を紹介します。

Excelで2つのシートのデータを比較する基本的な考え方

2つのシートを比較する場合は、片方のデータがもう片方に存在するかを確認する方法が基本になります。

今回の例では、シート1に1回目のワールドカップ出場国、シート2に2回目の出場国が入力されているとします。

確認したい結果は以下の3種類です。

この分類はExcelのCOUNTIF関数やXLOOKUP関数を利用すると簡単に作成できます。

まず、最も分かりやすい方法としてCOUNTIF関数を使います。

例として、シート1のA列に国名が入力されている場合、シート1のB2セルに以下の数式を入力します。

=IF(COUNTIF(シート2!A:A,A2)>0,"両方に出場","シート1のみ")

この数式では、シート2のA列の中にシート1の国名が存在するかを確認しています。

存在する場合は「両方に出場」、存在しない場合は「シート1のみ」と表示されます。

両方の大会に出場した国だけを一覧化したい場合は、フィルター機能を利用すると便利です。

手順は以下の通りです。

例えば、ブラジルやドイツなど、2回とも出場している国だけを抜き出したい場合、この方法で簡単に確認できます。

シート1にはあるが、シート2には存在しないデータを調べる場合もCOUNTIF関数を利用できます。

数式は以下になります。

=IF(COUNTIF(シート2!A:A,A2)=0,"シート1のみ","")

この結果、「シート1のみ」と表示された国が、1回目だけ出場した国になります。

同じようにシート2側からシート1を検索すれば、2回目だけ出場した国も抽出できます。

Excel 365やExcel 2021以降を使用している場合は、XLOOKUP関数を使う方法もあります。

例えば、シート1の国名がシート2に存在するか確認する場合は以下のような式を使えます。

=IF(ISERROR(XLOOKUP(A2,シート2!A:A,シート2!A:A)),"シート1のみ","両方に出場")

XLOOKUPは検索機能が強力で、大量のデータを扱う場合でも比較的管理しやすい方法です。

数式だけでなく、条件付き書式を使うと一致しているデータを視覚的に確認できます。

手順は以下の通りです。

例えば両方に存在する国を青色、一方だけ存在する国を赤色にするなど、データ量が多い場合でも確認しやすくなります。

国名などの文字データを比較する場合は、入力ミスや表記違いに注意が必要です。

例えば「アメリカ」と「アメリカ合衆国」、「ドイツ」と「ドイツ連邦共和国」のように表記が違う場合、Excelでは別データとして扱われます。

比較前にデータの表記を統一したり、不要な空白を削除したりすると正確な結果を得られます。

Excelで2つのシートを比較して、両方に存在するデータや片方だけに存在するデータを抽出するには、COUNTIF関数やXLOOKUP関数を使う方法が便利です。

基本的には「片方のデータがもう片方に存在するか」を確認することで、3種類の分類ができます。

少ないデータならCOUNTIF関数、大量データならXLOOKUPや条件付き書式を利用すると、効率的に比較作業を行うことができます。