Excel で 2 つの列を比較する方法
Excel で 2 つの列を行ごとに比べるには、3 つ目の列に =A2=B2 と入力し、下にコピーします。TRUE は 2 つのセルが一致していることを意味します。リストの順番が違う場合は、COUNTIF または XLOOKUP を使って、各値がもう一方のリストに含まれるかを確認します。このガイドでは、2 つの方法と、違いを強調表示する方法を説明します。
1. 行ごとに比較する
各行で 2 つの列に同じ値が入っているべき場合は、比較の数式で十分です。結果は TRUE または FALSE になります。この比較では大文字と小文字を区別しないため、「Ana」と「ANA」は等しいと判断される点に注意してください。
例:2 つのセルは一致しますか?
| A | B | C | |
|---|---|---|---|
| 1 | 入力値 | 期待値 | 一致? |
| 2 | Ana | ANA | TRUE |
この比較では大文字と小文字を区別しないため、Ana と ANA は等しくなります。
2. 大文字と小文字を正確に比較する
製品コードやパスワードのように、大文字と小文字の違いが重要な場合は EXACT を使います。EXACT は、文字が 1 文字ずつ完全に一致する場合にだけ TRUE を返します。
例:大文字と小文字を区別する正確な比較
| A | B | C | |
|---|---|---|---|
| 1 | 入力値 | 期待値 | 完全一致? |
| 2 | Ana | ANA | FALSE |
Ana と ANA は大文字と小文字が異なるため、EXACT は FALSE を返します。
3. 両方のリストにある値を見つける
リストの順番が異なる場合は、行ごとの比較では正しく判定できません。代わりに、COUNTIF で、1 つ目のリストの各値が 2 つ目のリストに何回含まれるかを数えます。件数が 0 より大きければ、その値は両方のリストに含まれています。数式を下にコピーしても範囲がずれないように、2 つ目の範囲には $ を付けて固定します。
例:両方のリストにあるか、1 つ目だけにあるか?
| A | B | C | D | |
|---|---|---|---|---|
| 1 | リストA | リストB | 確認 | |
| 2 | Ben | Dee | 両方にある | |
| 3 | Ana | Ben | ||
| 4 | Cam | Eve | ||
| 5 | Dee |
Ben はリストB に 1 回出現するので件数は 1 になり、この行には「両方にある」と表示されます。
4. もう一方のリストから値を検索する
2 つ目のリストから価格やステータスなどを取り出すには、XLOOKUP(古いバージョンでは VLOOKUP)を使います。値が見つからない場合は #N/A が返るため、どの値が不足しているかもわかります。IFERROR で数式を囲むと、代わりに「不足」などの文字を表示できます。
5. 違いを強調表示する
2 つの列を選択し、ホーム > 条件付き書式 > セルの強調表示ルール > 重複する値 を選びます。そして「重複」を「一意」に変更します。どちらか一方にしか現れない値に色が付きます。行ごとの違いを見つけるには、2 つの列を選択して Ctrl + G を押し、「セル選択」をクリックして、「行の相違」を選びます。Excel は、各行で最初の列と異なるセルを選択します。
よくあるミス
目に見えない空白。 末尾に空白が付いた「Ana」と「Ana 」は、見た目は同じでも一致しません。
修正: 比較する前に、両方の列を TRIM で整えます。
数値が文字列として保存されている。 数値の 100 と、文字列の「100」は等しくありません。
修正: 文字列を数値に変換するか、ISNUMBER で文字列のセルを確認します。
範囲を固定していない。 =COUNTIF(B2:B4,A2) を下にコピーすると範囲がずれて、行が抜けてしまいます。
修正: 下にコピーする前に、$B$2:$B$4 のように範囲を固定します。
よくある質問
2 つの列を比較して、差分を返すにはどうすればよいですか?
Microsoft 365 では、FILTER を使うと、一方のリストにあって他方にはない値を返せます。古いバージョンでは、補助列に COUNTIF を入力してから、その列でフィルターします。
2 つのシートをこの方法で比較できますか?
はい。数式の中で他のシートを参照します(例: Sheet2!B2:B100)。同じ方法が使えます。
同じに見えるのに、比較結果が FALSE になるのはなぜですか?
多くの場合、目に見えない空白か、文字列として保存された数値が原因です。TRIM を使い、セルの表示形式を確認してください。
関連する関数
- COUNTIFCOUNTIF は、範囲内で 1 つの条件を満たすセルを数えます。条件は、文字列、数値、">100" のような比較、ワイルドカードのいずれかです。構文、例、よくある間違いを説明します。.
- XLOOKUPXLOOKUP は、1 つの列または行を検索し、どの方向でも、別の列または行から対応する値を返します。見つからなかったときのメッセージも組み込まれています。構文、例、練習問題を説明します。.
- VLOOKUPVLOOKUP は、表の最初の列から値を探し、同じ行の別の列の値を返します。構文、例、よくある間違い、練習問題を説明します。.
- IFIF は条件を確かめ、真なら一方の値、偽なら別の値を返します。文字列と数値の例、ネストした IF、よくある間違いを説明します。.
- IFERRORIFERROR は、数式が #N/A や #DIV/0! などのエラーを返すときに、指定した値を返します。それ以外のときは通常の結果を返します。構文、例、使うべきでない場合を説明します。.
- TRIMTRIM は、テキストの余分なスペースを取り除きます。先頭と末尾のスペース、単語の間の連続したスペースが対象です。構文、例、#N/A になる検索を直す理由を紹介します。.
列の比較 について詳しく
このガイドでは、行ごとの比較、COUNTIF による確認、強調表示を説明しました。ここでは、2 つのシートにあるリストを比べる方法、片方にしかない値を取り出す方法、雑なデータに強い比較にする方法を説明します。
2 つの別々のシートにあるリスト
リストが別々のシートにあっても、同じ方法が使えます。範囲の前にシート名を付け、その後に感嘆符を付けます。シート名に空白が含まれる場合は、一重引用符で囲みましょう。
=IF(COUNTIF(Sheet2!$A$2:$A$100,A2)>0,"In both","Only here")片方のリストにしかない値を取り出す
ヘルパー列で各行に印を付け、「Only in A」でフィルターすると不足している値を一覧できます。Microsoft 365 では、1 つの数式で直接結果を返すことができ、データが変わると結果も更新されます。
=FILTER(A2:A5,COUNTIF(B2:B4,A2:A5)=0)2 つの範囲を入れ替えると、逆のリストが得られます。結果は下のセルにスピルするため、そこは空けておきましょう。
雑なデータにも強い比較にする
実際のリストには、余分な空白、大文字と小文字の不統一、文字列として保存された数値などが含まれています。まずヘルパー列で各列をきれいにし、きれいにした列同士を比べましょう。余分な空白は TRIM で削除できます。ブック内の他のリストも、同じ方法で比べられます。クリーニングは一度ヘルパー列で行い、すべての比較をそのきれいな列に向けましょう。そうすれば、1 か所の修正がすべてに反映されます。リストが長い場合は、先に並べ替えておくと違いを目で確認しやすくなります。
一方のリストから値を取り出す検索については、[XLOOKUP のレッスン](/learn/xlookup) と [VLOOKUP と XLOOKUP の比較](/guides/vlookup-vs-xlookup) を参照してください。