Excelで2つのリストを比較する方法：実践ガイド

比較のためにリストを準備する

比較数式を適用する前に、両方のリストがクリーンで一貫した書式であることを確認してください。余分なスペース、先頭または末尾のアポストロフィ、印刷されない文字は、誤った不一致を引き起こす可能性があります。ExcelのTRIM関数とCLEAN関数は、これらの問題のほとんどを取り除きます。

リストに同じ列内で重複が含まれている場合は、すべてのインスタンスを比較するか、一意の値のみを比較するかを決定してください。最初に内部の重複を削除すると、特に一致をカウントする際に誤解を招く結果を避けることができます。リスト間の比較の前に、[重複の削除]機能またはCOUNTIFを使用したヘルパー列を使用して重複にフラグを付けます。

1. 各リストをそれぞれの列（例：List1を列A、List2を列B）に、行1からコピーします。
2. 各列を選択し、一意のエントリのみを比較する場合は、[データ]タブの[重複の削除]コマンドを実行します。
3. 新しい列に =TRIM(A1) を適用し、値を貼り付けて不要なスペースを取り除き、元のデータをクリーニングされたバージョンに置き換えます。

• 変換を適用する前に、必ず生データのバックアップコピーを保管してください。
• 他のシステムからインポートされることが多い印刷不可能な文字を削除するには、=CLEAN(A1) を使用します。

=TRIM(A1)

=CLEAN(A1)

COUNTIFを使用して欠落または余分なアイテムを特定する

COUNTIFは、あるリストの各アイテムが他のリストに存在するかどうかを確認する最も簡単な方法の1つです。この数式は、指定された範囲内での値の出現回数をカウントします。結果が0の場合はアイテムが存在しないことを意味し、正の数は少なくとも1回は存在することを意味します。

すべての行に対して「一致」または「List1のみ」を返すヘルパー列を作成すると、重複と不一致を明確に把握できます。

1. 両方のリストが列Aと列Bにあることを確認します。List1の隣に新しい列Cを挿入します。
2. セルC1に「=IF(COUNTIF(B:B,A1)=0, "Only in List1", "Match")」と入力します。数式をドラッグしてList1全体に適用します。
3. 列DのList2についても同じプロセスを繰り返して、List2にのみ存在するアイテムを確認します。

• COUNTIFは大文字と小文字を区別しません。大文字と小文字が重要な場合は、SUMPRODUCTとEXACTを使用する方法を参照してください（セクション5参照）。
• 大きな範囲の場合、COUNTIFはワークブックの速度を低下させる可能性があるため、使用する実際のデータに範囲を制限することを検討してください。

=IF(COUNTIF(B:B, A1)>0, "Match", "Only in List1")

XLOOKUPを使用して関連データを接続する

はい/いいえの結果だけでなく、関連するフィールドを返す必要がある場合、XLOOKUPは1つの範囲を検索し、別の範囲から対応する値を返すことができます。これはMicrosoft 365およびExcel 2021以降などの現在の永続的なExcelリリースで利用可能です。古いインストールでは、INDEX-MATCHまたはVLOOKUPが必要になる場合があります。

XLOOKUPは各ルックアップ数式に対して1つの結果を返します。ルックアップが失敗した場合、オプションの見つからない引数を使用して「見つかりません」などの明確なラベルを表示できます。1つのキーが複数のレコードを返す必要がある場合は、FILTER、Power Query、または構造化結合を使用してください。

1. ルックアップ値が列A（List1）にあり、取得したいデータが列BとC（ルックアップ列B、戻り列C）にあるとします。
2. 列Dに「=XLOOKUP(A2, $B$2:$B$100, $C$2:$C$100, "Not found")」と入力します。範囲をデータに合わせて調整します。
3. 数式を下にコピーします。「Not found」と表示されるエントリは、List2に存在しないか、存在しても一致するレコードがないことを示します。

• XLOOKUPはデフォルトで完全一致であり、リスト比較に最適です。
• 古いExcelバージョンでは、代わりにINDEX‑MATCHまたはVLOOKUP(FALSE)を使用してください。

=XLOOKUP(A2, $B$2:$B$100, $C$2:$C$100, "Not found")

=INDEX($C$2:$C$100, MATCH(A2, $B$2:$B$100, 0))

条件付き書式を使用して差異を強調表示する

条件付き書式を使用すると、ルールを満たすセルに色を適用でき、余分な列を追加しなくても不一致を即座に視覚化できます。List2にないList1のセルを強調表示したり、その逆も可能です。

このアプローチは、永続的なマーカーを保持する必要がない場合や、余分な数式列を見たくない同僚とワークブックを共有する場合に、視覚的な検査に最適です。

1. List1の範囲（例：A2:A100）を選択します。[ホーム]タブで、[条件付き書式] > [新しいルール]をクリックします。
2. [数式を使用して、書式設定するセルを決定]を選択します。「=COUNTIF($B$2:$B$100, $A2)=0」と入力します。
3. [書式]をクリックし、List1に固有のセルの塗りつぶし色（例：赤）を選択して、[OK]をクリックします。List2についても、範囲としてList1を参照して繰り返します。

• 複合参照（$A2）を使用して、数式が各行に対して正しく調整されるようにします。
• 同じ範囲に対してルールを「=COUNTIF($B$2:$B$100, $A2)>0」に変更すると、2つのリスト間の重複も強調表示できます。

=COUNTIF($B$2:$B$100, $A2)=0

大文字と小文字の区別と余分なスペースに対処する

Excelの標準的な比較数式（COUNTIF、XLOOKUP、VLOOKUP）は大文字と小文字を区別しません。リストに「Apple」と「apple」が含まれており、それらを異なるものとして扱う必要がある場合は、EXACT関数をSUMPRODUCTまたは条件付き書式と一緒に使用する必要があります。

大文字と小文字を区別しない比較でも、末尾のスペースが誤った不一致を引き起こす可能性があります。比較する前に必ず両方のリストにTRIMを適用するか、数式内にTRIMをネストしてください。

1. List1とList2の大文字と小文字を区別した一致を実行するには、C1に「=IF(SUMPRODUCT((EXACT(A2, $B$2:$B$100))*1)>0, "Match", "Different")」と入力し、下にドラッグします。
2. または、EXACTに基づくルールを使用した条件付き書式を使用します：「=SUMPRODUCT((EXACT($A2, $B$2:$B$100))*1)=0」
3. スペースを処理するには、各範囲参照をTRIMでラップします：「=IF(COUNTIF($B$2:$B$100, TRIM(A2))=0, ...)」

• EXACTは大文字と小文字を区別し、スペースも考慮するため、事前にデータをクリーニングしてください。
• 大きなリストの場合、EXACTを使用したSUMPRODUCTは遅くなる可能性があるため、完全一致フラグを持つヘルパー列の使用を検討してください。

=IF(SUMPRODUCT((EXACT(A2, $B$2:$B$100))*1)>0, "Match", "No match")

大規模または複雑な比較におけるExcelの制限を理解する

Excelワークシートには固定の行制限がありますが、比較の実用的な制限はそれよりも低いことがよくあります。ワークブックのサイズ、数式の範囲、再計算、条件付き書式、利用可能なメモリ、デバイスの速度はすべて応答性に影響します。

正確なメンバーシップチェックは簡単です。ファジーマッチング、複数列結合、反復可能なデータパイプラインには、Power Query、専門的なアドイン、スクリプト、またはデータベースが必要です。ブラウザリストツールは、焦点を絞った正確な比較に役立ちますが、ファジーマッチングエンジンではなく、代表的なサンプルでテストする必要があります。

• 境界のある範囲やテーブルで十分な場合は、列全体の参照を避けてください。
• 一般的な行しきい値に頼るのではなく、実際のワークブックでベンチマークを取ってください。
• タスクに構造化結合や反復可能な自動化が必要な場合は、Power Query、SQL、またはスクリプトを使用してください。

ブラウザベースのリスト比較ツールに切り替えるタイミング

1回限りの正確な比較の場合、2つの値列を専用のブラウザツールにコピーする方が、ワークブックで数式を維持するよりも迅速です。CompareTwoListsは、共有値、最初のリストのみの値、2番目のリストのみの値、または結合セットを表示できます。

処理はページ内で行われるため、貼り付けられたリスト値はサイトサーバーにアップロードされません。比較は値ベースです。名前をファジーマッチングしたり、完全なスプレッドシートレコードを結合したり、どのヘッダーが対応するかを推測したりしません。それらの機能が必要な場合は、Excel、Power Query、SQL、またはデータフレームに戻ってください。

1. 比較する2つの値列のみをコピーします。
2. それらをCompare Two ListsまたはCompare Columnsに貼り付けます。
3. 共有、最初のみ、2番目のみ、または結合結果を選択します。
4. 出力を使用する前に、行数を検証し、いくつかの値をスポットチェックします。

• 貼り付けられた値のローカルなページ内処理
• 正確なリストメンバーシップとセット操作
• ファジーマッチングや完全レコード結合なし
• パフォーマンスはブラウザ、デバイス、入力に依存します