2つの列を行ごとに比べるには、最初の行の隣に=B2=C2と入力して下にコピーします。TRUEは2つのセルが一致している、FALSEは違うという意味です。一方の列の値が、順番にかかわらずもう一方の列のどこかにあるかを探すには、代わりに=COUNTIF($B$2:$B$8,A2)>0を使います。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Old | New | Same? | Status |
| 2 | Apple | $1.20 | $1.20 | TRUE | Same |
| 3 | Pear | $1.50 | $1.60 | FALSE | Changed |
| 4 | Carrot | $0.80 | $0.80 | TRUE | Same |
| 5 | Bread | $2.40 | $2.20 | FALSE | Changed |
| 6 | Milk | $1.10 | $1.10 | TRUE | Same |
| 7 | Cheese | $4.50 | $4.90 | FALSE | Changed |
D3、D5、D7はFALSEで、条件付き書式のルール=$B2<>$C2がその3行に色を付けます。E列はTRUEとFALSEの代わりに言葉で同じ判定を表示します。C3を1.5に変えると、3行目はSameになります。
IFで2つの列を比較する
=B2=C2はTRUEかFALSEを返します。IFで囲むと言葉を選べます。上のE列のように=IF(B2=C2,"Same","Changed")とします。一致する行を空白にして違いだけに印を付けるなら=IF(B2<>C2,"Changed","")を使います。数値がどれだけ変わったかを示すなら、比べる代わりに引き算します：=C2-B2。
作業列なしで違いを数えるには、SUMPRODUCTの中で2つの範囲を比べます。上のシートでは=SUMPRODUCT(--(B2:B7<>C2:C7))が3を返します。
数式を使わない方法もあります。B2がアクティブセルになるようにB2:C7を選択し、ホーム > 検索と選択 > 条件を選択してジャンプでアクティブ行との相違を選んでOKを押します(WindowsではCtrl+\でも同じです)。Excelは、その行でB列と違うセル、C3、C5、C7を選択するので、塗りつぶしの色を付けて印にします。
EXACTで大文字と小文字を区別して比べる
=による比較は大文字と小文字を区別しないので、ab12はAB12と等しくなります。大文字と小文字に意味があるとき(商品コード、パスワード、ID)は、2つの文字列が1文字ずつまったく同じときだけTRUEになるEXACT(A2,B2)を使います。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Code | Entered | Equal? | EXACT |
| 2 | AB12 | AB12 | TRUE | TRUE |
| 3 | CD34 | cd34 | TRUE | FALSE |
| 4 | EF56 | EF56 | TRUE | TRUE |
| 5 | GH78 | Gh78 | TRUE | FALSE |
=で比べるC列は、4つとも一致すると判断します。EXACTは、cd34とGh78に小文字が使われているので、3行目と5行目が違うと判断します。
一方の列にあってもう一方にない値を探す
2つのリストの順番が同じでないときは、各値をもう一方の列全体と比べます。COUNTIF($B$2:$B$8,A2)はA2がB2:B8に何回出てくるかを数えるので、>0は「見つかった」、=0は「ない」という意味になります。$記号によって、数式を下にコピーしても探す範囲が固定されます。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | January | February | In February? | With MATCH |
| 2 | Ana | Dan | TRUE | TRUE |
| 3 | Ben | Fay | TRUE | TRUE |
| 4 | Cara | Ana | FALSE | FALSE |
| 5 | Dan | Gus | TRUE | TRUE |
| 6 | Eve | Hal | FALSE | FALSE |
| 7 | Fay | Ivy | TRUE | TRUE |
| 8 | Gus | Ben | TRUE | TRUE |
CaraとEveはFALSEです。1月に買い、2月には買っていません。MATCHは別の道筋で同じ答えを出します。MATCH(A2,$B$2:$B$8,0)はB列でのA2の位置を返し、なければ#N/Aを返すので、ISNUMBERがそれをTRUEかFALSEに変えます。逆方向(2月の新しい顧客)を調べるには、B列の隣に範囲を入れ替えた同じ数式を置きます：=COUNTIF($A$2:$A$8,B2)>0。
2つのリストを比較して一致する値を返す
問いが「あるか」だけでなく「その隣の値が一致するか」であることもよくあります。ここでは、請求書を順番の違う入金のリストと照合しています。XLOOKUPが入金の中から各請求書を見つけて入金額を返し、D列がそれを請求額と比べます。
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Invoice | Amount | Paid | Match? | Payment for | Paid | |
| 2 | INV-101 | 120 | 120 | TRUE | INV-103 | 240 | |
| 3 | INV-102 | 85 | Not paid | FALSE | INV-101 | 120 | |
| 4 | INV-103 | 240 | 240 | TRUE | INV-105 | 140 | |
| 5 | INV-104 | 60 | 60 | TRUE | INV-106 | 95 | |
| 6 | INV-105 | 150 | 140 | FALSE | INV-104 | 60 | |
| 7 | INV-106 | 95 | 95 | TRUE |
INV-102には入金がないので、C3はNot paidと表示します。INV-105は150ではなく140が入金されたので、D6もFALSEです。XLOOKUPの最後の引数"Not paid"は、値がないときに出る#N/Aの代わりになります。XLOOKUPにはExcel 2021かMicrosoft 365が必要で、Excel 2019では=IFERROR(VLOOKUP(A2,$F$2:$G$6,2,FALSE),"Not paid")を使います。ほかの引数はXLOOKUPのページにあります。
もう一方の列にない値を一覧にする
TRUE/FALSEの列の代わりに、FILTERでない値をリストとして返せます。2つ目の引数に範囲を渡したCOUNTIF(B2:B8,A2:A8)はAのすべての値を一度に数え、FILTERは数が0のものを残します。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | January | February | Not in February | |
| 2 | Ana | Dan | ||
| 3 | Ben | Fay | ||
| 4 | Cara | Ana | ||
| 5 | Dan | Gus | ||
| 6 | Eve | Hal | ||
| 7 | Fay | Ivy | ||
| 8 | Gus | Ben |
やってみよう: D2で、2月のリストにいない1月の顧客を並べてください。
答えはCaraとEveをスピルします。=FILTER(A2:A8,ISNA(MATCH(A2:A8,B2:B8,0)))も使えます。全員が戻ってきたらFILTERは#CALC!を返すので、その場合に備えて3つ目の引数を足します：=FILTER(A2:A8,COUNTIF(B2:B8,A2:A8)=0,"None")。FILTERにはExcel 2021かMicrosoft 365が必要です。ほかの条件はFILTERを参照してください。
2つの列の違いに色を付ける
上の数式は条件付き書式のルールとしても使えます。最初のリストを選択してホーム > 条件付き書式 > 新しいルール > 数式を使用して、書式設定するセルを決定を開き、その最初のセルについての数式を入力します。ここではA2:A8に=COUNTIF($B$2:$B$8,A2)=0を、B2:B8に=COUNTIF($A$2:$A$8,B2)=0を設定していて、片方のリストにしかない名前すべてに色が付きます。
| A | B | |
|---|---|---|
| 1 | January | February |
| 2 | Ana | Dan |
| 3 | Ben | Fay |
| 4 | Cara | Ana |
| 5 | Dan | Gus |
| 6 | Eve | Hal |
| 7 | Fay | Ivy |
| 8 | Gus | Ben |
1月ではCaraとEveに、2月ではHalとIvyに色が付きます。行ごとに一致するはずの2つの列なら、このページの最初のシートのように、両方の列に=$A2<>$B2のルールを設定します。代わりに両方のリストにある名前に色を付けるなら、重複に色を付けるのページのように>0を使います。
同じ値が違うと表示される理由
最もよくある理由は、目に見えないスペースです。末尾にスペースのあるAna はAnaと等しくありません。ほかのシステムやWebページから貼り付けたデータには、よくスペースが含まれています。代わりにTRIMをかけた値を比べます。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Other list | Equal? | Trimmed |
| 2 | Ana | Ana | FALSE | TRUE |
| 3 | Ben | Ben | TRUE | TRUE |
| 4 | Cara | Cara | FALSE | TRUE |
| 5 | Dan | Dan | TRUE | TRUE |
C列は2行目と4行目が違うと判断します。TRIMで両端のスペースを取り除いた後のD列は、4つとも一致すると判断します。もう1つのよくある理由は、片方の列では文字列として保存された数値で、もう片方では本物の数値になっていることです。101と'101は同じに見えますが、Excelの=による比較はFALSEを返し、MATCH、VLOOKUP、XLOOKUPも一方の中からもう一方を見つけません。COUNTIFは例外で、数値に見える文字列をその数値として読むので、等しいものとして数えます。セルの隅の緑色の三角形が文字列のほうの印です。=VALUE(A2)か=A2*1で変換するか、セルを選択して警告アイコンから数値に変換するを選びます。
よくある質問
Excelで2つの列を比較して一致を調べるには？
行ごとなら、C2に=A2=B2と入力して下にコピーします。TRUEは2つのセルが一致しているという意味です。Aの各値がBのどこかにあるかを調べるには=COUNTIF($B$2:$B$8,A2)>0を使います。
2つの列を比較して、2つ目のリストから値を返すには？
値を検索します。=XLOOKUP(A2,$F$2:$F$7,$G$2:$G$7,"Not found")はG列から一致する値を返し、なければNot foundを返します。Excel 2019以前では=IFERROR(VLOOKUP(A2,$F$2:$G$7,2,FALSE),"Not found")を使います。
Excelで2つのセルを比べるとき、大文字と小文字は区別されますか？
されません。=A2=B2はabcとABCを等しいとみなします。大文字と小文字を区別して比べるには、大文字・小文字を含めてすべての文字が一致するときだけTRUEになる=EXACT(A2,B2)を使います。
一方の列にあってもう一方の列にない値を一覧にするには？
Excel 365と2021では、=FILTER(A2:A8,COUNTIF(B2:B8,A2:A8)=0)が、A2:A8の値のうちB2:B8にないものをすべてスピルします。
同じ値なのにExcelが違うと判断するのはなぜですか？
たいていは、片方に余分なスペースがあるか、文字列として保存された数値です。スペースの可能性を除くには=TRIM(A2)=TRIM(B2)で比べ、文字列の数値は=VALUE(A2)か=A2*1で変換します。
