Excelで2つのリストを比較する方法｜シート間の違い・一致するデータを簡単に抽出する手順

Excelで2つのシートに入力された大量のデータを比較したい場合、1件ずつ目視で確認する方法では時間がかかり、入力ミスも発生しやすくなります。特に数百件以上の用語や商品名、顧客リストなどを比較する場合は、Excelの関数を利用することで効率的に違いと共通点を確認できます。

この記事では、2つのシートにあるデータを比較して「片方にしか存在しないデータ」「両方に存在する共通データ」を調べる方法を、初心者でも実践できるように解説します。

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

Excelで2つのリストを比較する場合、まず確認したい内容を3種類に分けます。

これらはExcelの関数を使えば自動判定できます。数百件や数千件のデータでも短時間で処理できるため、目視で探す必要はありません。

最も簡単な方法はCOUNTIF関数を使って、別のシートに同じデータが存在するか調べる方法です。

例えば、シート1のA列にサッカー用語が入力されていて、シート2のA列と比較する場合、シート1のB1セルに以下の数式を入力します。

=COUNTIF(シート2!A:A,A1)

この結果が「0」の場合、シート1の用語はシート2には存在しません。1以上の場合は、シート2にも同じ用語があります。

例えば、シート1のA1に「オフサイド」と入力されていて、シート2にも「オフサイド」があれば結果は「1」になります。一方、シート2に存在しない用語なら「0」になります。

シート1だけに存在するデータを一覧化したい場合は、COUNTIF関数とIF関数を組み合わせます。

シート1のB1セルに以下を入力します。

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

下までコピーすると、シート2に存在しない用語だけを確認できます。

さらにフィルター機能で「シート1のみ」と表示された行だけを抽出すれば、シート1固有の用語リストを作成できます。

逆にシート2にあってシート1にないデータを調べる場合も同じ考え方です。

シート2側で以下の数式を使用します。

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

これにより、シート2独自の用語だけを抽出できます。

例えばサッカー用語リストの場合、シート1にはない新しく追加された用語や、片方の資料だけに掲載されている用語を簡単に確認できます。

2つのシートに共通して存在するデータを調べたい場合は、COUNTIF関数の結果が1以上になるものを抽出します。

例えば以下の数式を使います。

=IF(COUNTIF(シート2!A:A,A1)>0,"共通データ","")

「共通データ」と表示されたものだけをフィルターすれば、両方のシートに登録されている用語一覧を作成できます。

600件と900件程度のリストであれば、この方法でも十分高速に処理できます。

Microsoft 365やExcel 2021以降を利用している場合は、FILTER関数やXLOOKUP関数を利用すると、より効率的に比較できます。

例えばFILTER関数を使うと、条件に一致したデータだけを別の場所へ自動抽出できます。

またXLOOKUP関数を利用すると、別シートに同じデータがあるかを検索しながら結果を表示できます。

大量のデータを定期的に比較する場合は、これらの新しい関数を利用すると作業時間を大幅に短縮できます。

Excelで比較するときは、見た目が同じでも実際には別データとして扱われるケースがあります。

例えば「オフサイド」と「オフサイド」では、後者に余分な空白があるため別データとして判定される場合があります。

比較前にTRIM関数などで余分な空白を削除したり、表記を統一しておくことで正確な結果を得られます。

Excelで2つのシートにある大量のデータを比較する場合、1件ずつ確認する必要はありません。

COUNTIF関数を利用すれば、シート1だけにあるデータ、シート2だけにあるデータ、両方に存在する共通データを簡単に調べることができます。

数百件以上のリスト比較でも関数を活用すれば短時間で正確に確認できるため、用語集や商品一覧、名簿など幅広い場面で活用できます。