エクセルで2つのリストを比較する方法

このWikiHowでは、条件付き書式を使用してExcelスプレッドシートの2つのリスト上のすべてのセルを比較し、両方のリストに表示されるセルをマークする方法を説明します。この機能はExcelのデスクトップ版でのみ使用でき、モバイルアプリではサポートされていません。

方法1

CountIf式を使用する

1. 編集したいエクセルのスプレッドシートを開きます。比較したいリストがあるスプレッドシート・ファイルを見つけて、そのファイルをダブルクリックしてMicrosoft Excelで開きます。
2. 最初のリストを選択します。最初のリストの最初のセルをクリックし、リストの最後のセルまでマウスをドラッグして、データ範囲全体を選択します。
3. ツールバーリボンの「数式」タブをクリックします。このタブは、スプレッドシートの上部にあるツールバーの上にあります。ツールバーリボンの数式ツールが開きます。
4. ツールバーの [名前の定義] をクリックします。このオプションは、「数式」リボンの中央にあります。新しいポップアップ・ウィンドウが開き、リストに名前を付けることができます。
5. 名前」フィールドに「List1」と入力します。ポップアップ・ウィンドウの上部にあるテキスト・フィールドをクリックし、ここにリスト名を入力します。

   • この名前を使用して、後でリストを比較式に挿入することができます。
   • あるいは、ここでリストに別の名前を付けることもできます。例えば、これが場所のリストであれば、"location1 "または "locationList "と名付けることができます。
6. ポップアップ・ウィンドウでOKをクリックします。これで操作が確認され、リストに名前が付けられます。
7. 二つ目のリストをList2と名付けます。最初のリストと同じ手順で、2番目のリストに名前をつけます。これにより、後でこの2番目のリストを比較式ですぐに使用できるようになります。

   • リストには好きな名前を付けることができます。ここで各リストに付けた名前を覚えておくか、メモしておくようにしてください。
8. 最初のリストを選択します。最初のリストの最初のセルをクリックし、下にドラッグしてデータ範囲全体を選択します。

   • 条件付き書式の設定を始める前に、最初のリストが選択されていることを確認してください。
9. ツールバーの「ホーム」タブをクリックします。これは、ツールバーリボンの左上隅にある最初のタブです。ツールバーの基本的なスプレッドシートツールが開きます。
10. ツールバーの「条件付き書式」をクリックします。このオプションは、いくつかのセルが赤と青で強調表示された小さなスプレッドシートのアイコンのように見えます。すべての書式設定オプションのドロップダウンメニューが表示されます。
11. ドロップダウンメニューの［新規ルール］をクリックします。新しいポップアップ・ウィンドウが開き、選択した範囲に新しい書式ルールを手動で設定できます。
12. 数式を使用して書式設定するセルを決定する」オプションを選択します。このオプションでは、2つのリストを比較するための書式設定式を手動で入力することができます。

    • Windowsの場合は、ルールリストの一番下の「ルールの種類を選択」ボックスにあります。
    • Macの場合は、ポップアップの一番上にある「スタイル」ドロップダウンで「クラシック」を選択します。次に、「スタイル」メニューの下にある2番目のドロップダウンでこのオプションを見つけます。
13. ポップアップ・ウィンドウの数式フィールドをクリックします。ここに有効なExcelの数式を入力して、条件付き書式ルールを設定することができます。
14. 数式バーに「=countif(List2,A1)=1」と入力します。この数式は2つのリストをスキャンし、2つ目のリストにも表示される最初のリストのセルをすべてマークします。

    • 数式のA1は、最初のリストの最初のセルの番号に置き換えてください。
    • 例えば、最初のリストの最初のセルがセルD5の場合、数式は=countif(List2,D5)=1のようになります。
    • 2番目のリストに別の名前を付けた場合は、数式中のList2を実際のリスト名に置き換えてください。
    • あるいは、2番目のリストに表示されないセルをマークしたい場合は、数式を=countif(List2,A1)=0に変更する。
15. 数式バーに=countif(List1,B1)=1と入力する（オプション）。2つ目のリストで、1つ目のリストにも現れるセルを見つけてマークしたい場合は、最初の数式の代わりにこの数式を使う。

    • List1 を最初のリストの名前に置き換え、B1 を 2 番目のリストの最初のセルに置き換えます。
16. セルをマークするカスタム書式を選択します（オプション）。カスタム背景塗りつぶし色と異なるフォント・スタイルを選択して、数式が検出するセルをマークできます。

    • Windows の場合は、ポップアップ・ウィンドウの右下にある「書式」ボタンをクリックします。塗りつぶし」タブで背景色を、「フォント」タブでフォント・スタイルを選択できます。
    • Macの場合は、下部にある「Format with」のドロップダウンで書式プリセットを選択します。ここでカスタムフォーマットを選択して、背景の塗りつぶしやフォントスタイルを手動で選択することもできます。
17. ポップアップウィンドウで「OK」をクリックします。これで比較式が確定し、適用されます。最初のリストのセルで、2 番目のリストにも表示されるすべてのセルに、選択した色とフォントが表示されます。

    • たとえば、薄い赤の塗りつぶしで濃い赤のテキストを選択した場合、最初のリストで繰り返し表示されるセルはすべてこの色になります。
    • 上記の2番目の数式を使用すると、条件付き書式設定によって、2番目のリスト上の繰り返しセルにマークが付けられます。

方法2

VLookup数式を使う

1. エクセルのスプレッドシートを開きます。比較したいリストがあるExcelファイルを探し、ファイル名またはアイコンをダブルクリックしてMicrosoft Excelでスプレッドシートを開きます。
2. 2番目のリストの最初の項目の隣にある空のセルをクリックします。スプレッドシートで2番目のリストを見つけ、一番上の最初のリスト項目の隣にある空のセルをクリックします。

   • ここにVLookup式を挿入できます。
   • あるいは、スプレッドシート上の任意の空のセルを選択することもできます。このセルは、2番目のリストの横にある比較を見やすくするだけです。
3. 空のセルに「=vlookup()」と入力します。VLookup式は、2つの別々のリストのすべての項目を比較し、値が繰り返し値か新しい値かを確認することができます。

   • 数式が完成するまで、数式の括弧を閉じないでください。
4. 2番目のリストの最初の項目を選択します。数式の括弧を閉じずに、2番目のリストの最初の項目をクリックします。これで、2 番目のリストの最初のセルが数式に挿入されます。
5. コンマを数式に入力します。2番目のリストの最初のセルを選択した後、数式にカンマを入力します。次に比較範囲を選択します。
6. 最初のリスト全体を押しながら選択します。これで、最初のリストのセル範囲がVLookup数式の2番目の部分に挿入されます。

   • これにより、最初のリストから2番目のリストの選択された項目（2番目のリストの先頭の項目）を検索し、それが繰り返し値か新しい値かを返すことができます。
7. コンマを数式に入力します。これにより、数式内の比較範囲が固定されます。
8. カンマの後の数式に1を入力します。この数値は列インデックス番号を表します。これは、VLookup 数式に、その隣にある別の列ではなく、実際のリスト列を検索するように促します。

   • 最初のリストの右隣の列の値を数式で返したい場合は、ここに 2 を入力します。
9. コンマを数式に入力します。これにより、VLookup 数式の列インデックス番号 (1) が固定されます。
10. 数式に「FALSE」と入力します。これは、近似一致ではなく、選択した検索項目（2 番目のリストの先頭の項目）と完全に一致するリストを検索します。

    • FALSEの代わりに0を使ってもまったく同じです。
    • 近似一致を検索したい場合は、TRUEまたは1を入力することもできます。
11. 数式を閉じるには、最後に ) を入力します。これで式を実行し、2番目のリストで選択された検索項目が繰り返し値か新しい値かを確認できます。

    • たとえば、2 番目のリストが B1 から始まり、1 番目のリストがセル A1 から A5 までの場合、数式は =vlookup(B1,$A$1:$A$5,1,false) のようになります。
12. キーボードの ↵ Enter または ⏎ Return を押します。これで数式が実行され、最初のリストから2番目のリストの最初の項目が検索されます。

    • これが繰り返し値の場合、数式セルに同じ値が再度表示されます。
    • これが新しい値の場合は、ここに「#N/A」と表示されます。
    • たとえば、最初のリストで "John "を検索しているときに、数式セルに "John "と表示された場合、これは両方のリストに表示される繰り返し値です。もし「#N/A」と表示されたら、それは2つ目のリストの新しい値です。
13. 数式セルを選択します。数式を実行し、最初のリスト項目の結果を確認したら、数式セルをクリックして選択します。
14. セルの右下にある緑色の点をクリックして下にドラッグします。これにより、数式セルがリストに沿って拡大され、2番目のリストのすべてのリスト項目に数式が適用されます。

    • こうして、2番目のリストのすべての項目を、最初のリスト全体と比較することができます。
    • これは、最初のリストと2番目のリストの各項目を個別に検索し、結果を各セルの横に個別に表示します。
    • 新しい値に対して、"#N/A" ではなく別のマーカーを表示したい場合は、次の数式を使用します。これは、新しい値に対して "#N/A "の代わりに "New Value "と表示します。

この記事は、CC BY-NC-SAの下で公開されている " How to Compare Two Lists in Excel " を修正して作成されました。特に断りのない限り、CC BY-NC-SAの下で利用可能です。