Excelで2つの列を比較すると、同じ商品コードがそろっているか、名簿に漏れがないか、更新前後のデータに違いがあるかを短時間で確認できます。
ただし、目視で1行ずつ確認すると、件数が増えるほど見落としや入力位置のずれが起こりやすくなります。
そこで役立つのが、数式、条件付き書式、フィルター、関数を組み合わせた列比較です。
A列とB列を同じ行で比べる場合と、順番に関係なく一覧同士を照合する場合では、使う数式が異なります。
1行目が見出し、2行目からデータが始まる表として考えると、コピーやオートフィルも行いやすくなります。
この記事では、列比較で一致と不一致を判定し、不一致データを抽出したり、色付けしたりする方法を詳しく解説します。
同じ行の列比較で不一致を判定する数式
それではまず、同じ行に並ぶ2列を比較し、不一致をすぐに判定する方法について解説していきます。
| 行 | A列 旧コード | B列 新コード | C列 判定 |
|---|---|---|---|
| 2 | A-100 | A-100 | 一致 |
| 3 | B-205 | B-208 | 不一致 |
| 4 | C-310 | C-310 | 一致 |
IF関数による一致と不一致の表示
同じ行のA列とB列を比較するなら、C2セルにIF関数を入力する方法が基本です。
=IF(A2=B2,”一致”,”不一致”)
この式は、A2セルとB2セルの内容が完全に同じなら一致、異なるなら不一致と表示します。
判定結果を文字で表示しておくと、フィルターで不一致だけを抽出しやすくなります。
数式を入力したC2セル右下の小さな四角を下へドラッグすれば、3行目以降にも相対参照を保ったまま数式をコピーできます。
たとえばC3セルではA3とB3、C4セルではA4とB4が自動的に比較対象になります。
空白セルを除外する判定条件
比較表の末尾や途中に空白行がある場合、単純なIF関数だけでは空白同士を一致として扱います。
空白行を判定対象から外したいときは、先に両方のセルが空白かどうかを確認します。
=IF(AND(A2=””,B2=””),””,IF(A2=B2,”一致”,”不一致”))
AND関数は複数の条件がすべて満たされるかを調べる関数です。
この数式ではA2とB2が両方とも空白なら何も表示せず、それ以外では通常どおり一致か不一致かを返します。
未入力の行まで不一致として数えてしまう問題を防げるため、一覧の最終行が変動するデータで便利です。
片方だけ空白で、もう片方に値がある行は不一致と表示されます。
大文字小文字と余分な空白の確認
Excelのイコール比較では、通常は英字の大文字と小文字を区別しません。
たとえばABCとabcを別の文字列として確認したい場合は、EXACT関数を使います。
=IF(EXACT(A2,B2),”一致”,”不一致”)
また、コピーしたデータに余分な半角スペースが含まれると、見た目が似ていても比較結果は不一致になります。
前後の空白を除去してから比べるには、TRIM関数を組み合わせます。
=IF(TRIM(A2)=TRIM(B2),”一致”,”不一致”)
不一致が予想以上に多いときは、値そのものだけでなく、空白や全角半角の混在も疑いましょう。
【操作のポイント】同じ行を比較する表では、判定列を1列追加し、最初のデータ行で数式を完成させてからオートフィルを使うと作業が安定します。
COUNTIF関数による一覧同士の照合
続いては、並び順が異なる2つの一覧から、片方に存在しないデータを見つける方法を確認していきます。
| 行 | A列 発送予定一覧 | B列 出荷実績一覧 | C列 照合結果 |
|---|---|---|---|
| 2 | P-001 | P-003 | あり |
| 3 | P-002 | P-001 | なし |
| 4 | P-003 | P-004 | あり |
COUNTIF関数で存在の有無を調べる方法
A列の値がB列のどこかに存在するかを確認するには、C2セルにCOUNTIF関数を入力します。
=IF(COUNTIF($B$2:$B$100,A2)>0,”あり”,”なし”)
COUNTIF関数は、指定範囲内で条件に一致するセルの数を数えます。
ここではB2からB100までを検索範囲にし、A2の値が1件以上見つかればありと表示する仕組みです。
$記号でB列の検索範囲を固定することが重要です。
固定しないままオートフィルすると、下の行へコピーするたびに検索範囲までずれてしまいます。
片方にしかないデータの抽出条件
照合結果がなしになった行は、A列にはあるもののB列には存在しないデータです。
この状態で表の見出し行にフィルターを設定し、C列でなしだけを選択すると、不一致データだけを表示できます。
フィルターはデータタブから設定できますが、範囲内のセルを選択してCtrlキーとShiftキーとLキーを同時に押す方法もあります。
比較対象が注文番号、社員番号、商品コードなど一意の値なら、この方法で漏れを見つけやすくなります。
並び順の違いは不一致の原因ではないため、別シートから取得した一覧の照合にも向いています。
逆に、B列にあるがA列にない値を調べるときは、B列の隣に判定列を作り、検索範囲と検索値を入れ替えます。
重複データを含む一覧の扱い
COUNTIF関数は、同じ値が何件含まれているかも返せます。
存在の有無だけでなく、件数の違いまで確認したい場合は、IF関数を使わずCOUNTIF関数の結果をそのまま表示してもよいでしょう。
=COUNTIF($B$2:$B$100,A2)
結果が0ならB列に存在せず、1なら1件、2以上ならB列に重複があることを意味します。
ただしA列側にも同じコードが複数ある場合、単純な存在確認だけでは件数差を十分に判定できないことがあります。
件数まで正確に突き合わせるなら、コード別に集計表を作成するか、ピボットテーブルを活用する方法も検討しましょう。
【操作のポイント】一覧同士を比較するときは、比較したい値が重複していないかを先に確認し、存在確認と件数確認を使い分けます。
条件付き書式による不一致セルの色付け
続いては、数式の判定列を増やさずに、不一致のセルを色付けして目立たせる方法を確認していきます。
| 行 | A列 入力値 | B列 照合値 | 表示例 |
|---|---|---|---|
| 2 | 東京 | 東京 | 通常表示 |
| 3 | 大阪 | 大坂 | 赤系の塗りつぶし |
| 4 | 福岡 | 福岡 | 通常表示 |
同じ行で異なるセルを赤くするルール
A列とB列の同じ行を比較し、異なる場合だけ色を付けるには条件付き書式を使います。
まずA2からB100など、色を付けたい比較範囲を選択します。
ホームタブの条件付き書式から新しいルールを選び、数式を使用して書式設定するセルを決定を選択します。
=$A2<>$B2
この数式を入力し、書式ボタンで塗りつぶしの色を選択します。
<>は等しくないことを表す比較演算子です。
列Aと列Bを$で固定し、行番号だけを変化させると、各行を正しく比較できます。
一覧内に存在しない値を色付けする数式
A列の値がB列の一覧にない場合だけA列を色付けするなら、COUNTIF関数を条件付き書式の数式に利用します。
=COUNTIF($B$2:$B$100,A2)=0
このルールをA2からA100へ適用すると、B列に見つからないA列のデータだけが強調されます。
検索範囲のB2からB100は絶対参照にし、条件となるA2は行ごとに変化できる参照にする点がポイントです。
色付けは削除や修正を行う前の確認作業に向いており、元データを変更せず差異を見つけられます。
反対方向の漏れも確認したい場合は、B列側に対して同じ考え方のルールを設定します。
書式ルールの優先順位と適用先
条件付き書式の色が期待どおりに表示されない場合は、ルールの管理画面を確認します。
ホームタブの条件付き書式からルールの管理を開くと、適用先の範囲、数式、優先順位を見直せます。
複数のルールが同じセルに重なると、優先順位が上のルールの書式が表示されることがあります。
表を並べ替えた後や行を追加した後は、適用先が必要な範囲まで広がっているかを確認しましょう。
条件付き書式は視覚的な確認に強い一方、抽出用の判定列とは役割が異なります。
【操作のポイント】不一致の確認には赤系、確認済みの印には別の色というように、色の意味を表内で統一すると判断しやすくなります。
フィルターと詳細設定による不一致データの抽出
続いては、判定済みの表から不一致データだけを取り出し、確認用の一覧として扱う方法を確認していきます。
| 行 | A列 商品名 | B列 登録値 | C列 確認値 | D列 判定 |
|---|---|---|---|---|
| 2 | ノート | 120 | 120 | 一致 |
| 3 | ペン | 80 | 75 | 不一致 |
| 4 | 付箋 | 50 | 50 | 一致 |
オートフィルターによる不一致行の表示
判定列に一致と不一致を表示できたら、見出し行を含む表内のセルを1つ選択します。
データタブのフィルターをクリックすると、各見出しに絞り込み用の矢印が表示されます。
D列の矢印をクリックし、すべて選択のチェックを外して不一致だけにチェックを入れます。
すると、不一致と判定された行だけが表示され、修正対象や確認対象を集中的に確認できます。
非表示になった行は削除されたわけではないため、フィルターを解除すれば元の一覧に戻ります。
FILTER関数による抽出一覧の作成
Microsoft 365やExcel 2021以降では、FILTER関数を使って別の場所に不一致行だけを表示できます。
元表がA列からD列で、D列に判定結果がある場合は、空いているセルに次の数式を入力します。
=FILTER(A2:D100,D2:D100=”不一致”,”不一致のデータはありません”)
FILTER関数は、条件に合う行を自動的に展開して表示する関数です。
D列が不一致の行だけを抽出するため、修正リストや報告用の別表を手作業で作る必要がありません。
元データの内容が変わると抽出結果も連動して更新されるため、継続的な照合作業に適しています。
ただし、抽出結果が広がる範囲に値が入力されているとスピルエラーになるため、数式の下と右側は空けておきましょう。
抽出後に確認するデータの項目
不一致だけを抽出した後は、単に値が違うことを確認するだけで終わらせないことが大切です。
商品コードなら桁数、先頭のゼロ、ハイフンの有無を確認します。
金額なら数値と文字列の混在、端数処理、税込と税抜の条件を見直します。
氏名や住所なら漢字の異体字、旧字体、全角スペースなども差異の原因になるかもしれません。
不一致という結果は誤りの確定ではなく、確認が必要な行を示す目印です。
【操作のポイント】抽出一覧には元の行番号や管理番号も残し、修正後に元データのどの行へ戻るか分かる状態にしておきます。
XLOOKUP関数とVLOOKUP関数による照合
続いては、対応する値を別の表から取得し、照合結果を確認する関数の使い分けについて解説していきます。
| 行 | A列 商品コード | B列 入力単価 | C列 マスター単価 | D列 判定 |
|---|---|---|---|---|
| 2 | K001 | 150 | 150 | 一致 |
| 3 | K002 | 220 | 200 | 不一致 |
XLOOKUP関数でマスター値を取得する方法
商品コードをもとに別シートのマスターから正しい単価を取得するなら、XLOOKUP関数が便利です。
マスターシートのA列に商品コード、B列に単価があるとして、C2セルに次の数式を入力します。
=XLOOKUP(A2,マスター!$A$2:$A$100,マスター!$B$2:$B$100,”未登録”)
最初のA2は検索する商品コードです。
続く範囲はマスター内でコードを探す列、その次の範囲は見つかった場合に返す単価の列です。
見つからない場合に未登録と表示する指定を入れると、エラーではなく確認しやすい文字で状況を把握できます。
取得値と入力値を比較する判定式
C列へマスター単価を取得したら、D2セルでB列とC列を比較します。
=IF(B2=C2,”一致”,”不一致”)
未登録と表示された行は単価の比較対象が存在しないため、別のメッセージに分けることもできます。
=IF(C2=”未登録”,”マスター未登録”,IF(B2=C2,”一致”,”不一致”))
このように結果を分ければ、入力ミス、単価差異、マスター未登録を混同せずに処理できます。
照合の目的に応じて、結果の言葉を具体化すると後工程の担当者にも伝わりやすくなります。
旧バージョンでのVLOOKUP関数
XLOOKUP関数を利用できないExcelでは、VLOOKUP関数でも同様の照合が可能です。
マスターのA列にコード、B列に単価があり、コード列が表の左端にある場合は次のように入力します。
=IFERROR(VLOOKUP(A2,マスター!$A$2:$B$100,2,FALSE),”未登録”)
2は検索範囲の左から2列目を返す指定で、FALSEは完全一致で検索する指定です。
VLOOKUP関数は検索列より左側の値を取得できない制約があるため、表の配置によっては使いにくいことがあります。
新しいExcelを使える環境では、検索方向を選びやすいXLOOKUP関数を優先するとよいでしょう。
【操作のポイント】マスター照合では、検索するコード列の表示形式をそろえ、文字列と数値が混在しないように整えてから関数を入力します。
比較結果を正確にするデータ整形
続いては、数式が正しくても不一致になりやすいデータの特徴と、比較前に行う整形について解説していきます。
| 確認項目 | 比較前の例 | 整形後の例 |
|---|---|---|
| 前後の空白 | 「 A001 」 | 「A001」 |
| 全角半角 | 「ＡＢＣ」 | 「ABC」 |
| 数値と文字列 | 00123 | 文字列として00123 |
TRIM関数とCLEAN関数による余分な文字の除去
Webページや他システムから貼り付けたデータには、通常のスペース以外の不要な文字が混ざる場合があります。
TRIM関数は余分なスペースを整理し、CLEAN関数は印刷できない文字を除去するために使えます。
=TRIM(CLEAN(A2))
比較用の補助列にこの数式を入れて整形した値を作り、その列同士を比較すると原因を切り分けやすくなります。
元データを直接上書きする前に補助列で結果を確認すると、意図しない変更を避けられます。
表示形式と実際の値の違い
セルに00123と表示されていても、実際の値が123という数値で、表示形式によって先頭のゼロを付けていることがあります。
別の列では00123が文字列として保存されていると、用途によっては比較結果や検索結果に差が出ます。
コードや郵便番号のように先頭のゼロを保持すべき値は、文字列として統一する方法が安全です。
数値として計算する項目は、VALUE関数などで数値へ変換してから比較する選択肢があります。
データ型をそろえることは、列比較の精度を上げる基本です。
日付と小数点の比較時の注意
日付は見た目が同じでも、時刻の情報を含んでいると完全一致しない場合があります。
日付だけを比較したい場合は、INT関数で小数部分に含まれる時刻を切り捨てます。
=IF(INT(A2)=INT(B2),”一致”,”不一致”)
また、計算結果の小数には、ごく小さな誤差が含まれることがあります。
金額や測定値で小数第2位までを比較するなら、ROUND関数で桁数をそろえてから比較しましょう。
=IF(ROUND(A2,2)=ROUND(B2,2),”一致”,”不一致”)
【操作のポイント】不一致を修正する前に、空白、表示形式、データ型、日付時刻、小数誤差のどれが原因かを確かめます。
まとめ エクセルで不一致データを抽出する列比較方法
Excelで列を比較して不一致データを抽出するには、比較する目的に合う方法を選ぶことが大切です。
同じ行の値を比べるだけなら、IF関数で一致と不一致を表示する方法が分かりやすい選択です。
順番が異なる一覧同士を照合する場合は、COUNTIF関数を使うと、片方の列にないデータを確認できます。
不一致を目立たせたいときは条件付き書式、該当行だけを一覧化したいときはフィルターやFILTER関数が役立ちます。
照合式を作る前に、見出しは1行目、データは2行目からという形で表を整えると、数式のコピーや管理がしやすくなります。
また、余分な空白、全角半角、文字列と数値の違いは、比較結果がずれる代表的な原因です。
数式の結果だけで判断せず、不一致になった理由まで確認すれば、データの品質改善にもつながります。
日常の名簿確認、在庫照合、売上一覧の突合などに列比較を活用し、正確で効率的なExcel作業を進めましょう。
