【Excel】エクセルで2つのシートを比較して差分を抽出・色付けする方法（VLOOKUP・重複・条件付き書式・シート全体）

Excelで旧データと新データ、提出前の一覧と元データなど、2つのシートを比較したい場面は少なくありません。

目視でセルを追う方法は件数が増えるほど見落としやすく、修正箇所の確認にも時間がかかります。

そこで役立つのが、VLOOKUP関数やCOUNTIF関数、条件付き書式を組み合わせて差分を抽出し、変更されたデータを色付けする方法です。

この記事では、1行目がヘッダー行であるサンプルを使い、セル単位の比較からシート全体の比較までを順番に解説します。

数式を入力した後は、必要に応じてオートフィルで最終行までコピーしましょう。

 

エクセルで2つのシートを比較して差分を色付けする方法

それではまず、同じ配置の2つのシートで差分セルを色付けする基本操作について解説していきます。

商品コード	商品名	旧価格	新価格
A001	ノート	120	130
A002	ペン	80	80

比較するシートを、ここでは「旧データ」と「新データ」という名前で用意します。

両方のシートで列構成と行の並び順が同じであれば、条件付き書式だけで変更セルを見つけられます。

 

条件付き書式によるセル単位の照合

まず、色を付けたい「新データ」シートの比較範囲を選択します。

たとえばA2からD100までを比較対象にする場合は、新データシートでA2:D100を選択します。

続いてホームタブの条件付き書式から、新しいルールを選びます。

ルールの種類では「数式を使用して、書式設定するセルを決定」を選択してください。

数式欄には、先頭セルを基準にした次の式を入力します。

この式は、新データのA2と旧データのA2が一致しないときに真となる判定です。

比較演算子の<>は、左右の値が異なることを表します。

書式ボタンから塗りつぶし色を選び、赤や黄色など、変更箇所が分かりやすい色を指定します。

適用後は、異なるセルだけが自動で強調されます。

 

空白セルと数式結果の扱い

空白セルを含む一覧では、空白と空白を同じ値として扱うかどうかも重要です。

通常の比較式では、両方が空白なら差分として色付けされません。

一方だけが空白の場合は差分になるため、削除された値や入力漏れの発見に向いています。

数式が入力されているセルでは、数式そのものではなく計算結果を比較します。

数式の記述まで比較したい場合は、FORMULATEXT関数の利用や、数式表示モードでの確認を検討します。

 

比較範囲をずらさないための注意点

条件付き書式の数式では、選択範囲の左上セルを基準に参照先を指定します。

A2:D100を選択している場合、数式を=A2<>旧データ!A2とすると、各セルが対応する同じ位置のセルと比較されます。

列を固定したいときは$A2、行を固定したいときはA$2のようにドル記号を使用します。

参照の固定方法を間違えると、比較対象が横や縦にずれてしまいます。

最初は少ない行数で設定し、意図したセルだけが色付けされることを確認してから範囲を広げると安心です。

 

VLOOKUP関数による行順が異なるデータの比較

続いては、2つのシートで商品コードの並び順が異なる場合の比較方法を確認していきます。

商品コード	新データ価格	旧データ価格	判定
A002	80	80	一致
A001	130	120	差分

行番号で照合できない場合は、商品コード、顧客ID、社員番号などのキー列を用意します。

VLOOKUP関数は、そのキーをもとに別シートから対応する値を検索する関数です。

 

VLOOKUP関数で旧データを呼び出す数式

新データシートで、A列に商品コード、C列に新価格があり、D列に旧価格を表示する例を考えます。

旧データシートでは、A列に商品コード、C列に旧価格があるものとします。

D2セルには次の数式を入力してください。

VLOOKUPの1つ目の引数A2は、検索したい商品コードです。

2つ目の引数旧データ!$A:$Cは、旧データ側の検索範囲を示します。

3つ目の引数3は、範囲内の左から3列目であるC列の価格を返す指定です。

最後のFALSEは完全一致検索を意味し、コード比較では必ず指定したい設定です。

IFERRORを組み合わせると、見つからないデータをエラーではなく未登録と表示できます。

数式を入力したら、セル右下のフィルハンドルを最終行までドラッグしてオートフィルします。

 

比較結果を表示するIF関数

旧価格をD列へ表示できたら、E列で新価格と旧価格を比較します。

E2セルには次の式を入力します。

この数式では、旧データに存在しない商品を新規、一致する価格を一致、それ以外を差分と判定します。

判定列を作ると、フィルター機能で「差分」だけを絞り込めるようになります。

値の修正だけでなく、新しく追加された商品や削除候補の確認にも便利です。

数式の結果を色で補助するだけでなく、文字でも残すと印刷やCSV出力後の確認がしやすくなります。

 

VLOOKUPで比較する際のエラー対策

VLOOKUPで検索できない主な原因は、コードの形式が揃っていないことです。

たとえば片方が数値、もう片方が文字列になっていると、見た目が同じでも一致しません。

前後に空白が含まれる場合も検索に失敗します。

TRIM関数で余分な空白を除去し、VALUE関数やTEXT関数でデータ型を統一すると改善することがあります。

また、VLOOKUPは検索範囲の最も左の列でしか検索できません。

キー列が左端にないデータでは、XLOOKUP関数やINDEX関数とMATCH関数の組み合わせも候補になります。

 

重複データと片方にしかないデータの抽出

続いては、2つのシートに共通しているデータや、片方にしかないデータを抽出する方法を確認していきます。

商品コード	新データ内の件数	旧データ内の件数	状態
A001	1	1	共通
A003	1	0	新規

重複の有無だけなら、COUNTIF関数を使うとシンプルに確認できます。

比較対象がコード列だけの場合にも扱いやすい方法です。

 

COUNTIF関数による存在確認

新データシートのA列に商品コードがあり、旧データシートのA列にも商品コードがある場合を考えます。

新データ側のB2セルに、次の数式を入力します。

結果が1以上なら、A2の商品コードは旧データシートにも存在します。

結果が0なら、旧データにはない新規データです。

COUNTIFは一致する件数を返すため、同じコードが複数回含まれているかどうかも確認できます。

重複を許可しない管理表では、結果が2以上の行も見逃せません。

 

新規と削除候補を判定する数式

表示を分かりやすくするには、COUNTIFの結果をIF関数で文字へ変換します。

新データシートのB2セルには、次のように入力できます。

逆に、旧データ側で新データを検索すれば、削除候補となるコードを抽出できます。

ただし、削除候補は必ずしも削除済みとは限りません。

集計対象の期間、抽出条件、非表示行などの違いで一時的に表示されないこともあります。

判定結果は削除を実行する根拠ではなく、確認が必要な候補として扱うことが大切です。

 

フィルターを使った差分一覧の作成

判定列を作成した後は、データタブのフィルターを設定します。

見出し行を含む表内のセルを選び、フィルターをクリックすると各列に絞り込みボタンが表示されます。

判定列で新規だけを選択すれば、新たに追加された行だけを一覧にできます。

差分や未登録だけを抽出して別シートへコピーすれば、確認用リストの作成も簡単です。

元の表を並べ替える前に、必要に応じてバックアップ用のシートを複製しておくと安全でしょう。

 

条件付き書式による複数列とシート全体の比較

続いては、行単位で複数列を比較し、差分があるレコード全体を強調する方法を確認していきます。

商品コード	商品名	価格	判定
A001	ノート	130	差分あり
A002	ペン	80	一致

シート全体を比較するときは、キー列で対象行を特定し、比較したい複数の値をまとめて判定します。

差分がある行だけを色付けすると、確認の優先順位が明確になります。

 

複数列の差分を判定する数式

新データのA列を商品コード、B列を商品名、C列を価格とし、旧データにも同じ構成がある例です。

D2セルに判定結果を表示するなら、次の数式を使用できます。

OR関数は、いずれか1つでも条件を満たす場合に真を返します。

このため、商品名または価格のどちらかが異なれば、差分ありと表示されます。

比較対象の列が増えるほど、どの列を比較しているかを数式内で明確にすることが重要です。

データが見つからない可能性がある場合は、IFERROR関数を加えてエラー表示を抑えましょう。

 

行全体を色付けする条件付き書式

判定列Dに差分ありと表示される場合、A2:D100を選択して条件付き書式を設定します。

数式を使用するルールで、次の式を入力してください。

列Dだけをドル記号で固定し、各行の判定結果に応じて行全体へ書式を適用します。

塗りつぶしは薄い赤、文字色は濃い赤などにすると、表の可読性を保ちながら変更行を示せます。

判定列を非表示にしても条件付き書式は動作するため、完成した報告用シートにも活用できます。

 

大きな表を比較するときの実務上の工夫

数千行以上のデータを比較する場合、列全体参照は計算負荷を大きくすることがあります。

必要な行数に範囲を限定するか、データをテーブル形式に変換して構造化参照を使うと管理しやすくなります。

比較用の列は元データを直接書き換えず、別の確認用シートで作成する方法も有効です。

担当者が複数いる場合は、判定列、確認者、確認日、対応状況の列を追加すると作業履歴を残せます。

 

比較結果を見やすくする関数と確認手順

続いては、差分抽出の精度を高め、確認作業を進めやすくする関数と手順を確認していきます。

確認項目	判定例	対応
コードの存在	未登録	新規・削除候補を確認
値の変更	差分あり	変更理由を確認

比較結果は、数式が正しくても元データの前提が違えば誤解につながります。

照合前の整形と、照合後の確認を分けて進めることが実務では重要です。

 

文字列と数値の統一

コード列に先頭ゼロがある場合、数値へ変換すると00123が123に変わることがあります。

コードを識別子として扱うなら、セルの表示形式を文字列にするか、TEXT関数で桁数を揃えます。

日付も表示形式の違いだけでなく、実際のシリアル値や文字列になっているかを確認しましょう。

見た目が同じ値でも、データ型が異なればVLOOKUPやCOUNTIFの結果が変わる場合があります。

比較前に不要な空白、全角と半角、改行記号を確認する習慣が役立ちます。

 

XLOOKUP関数を使える場合の選択肢

Microsoft 365などでXLOOKUP関数を利用できる環境では、VLOOKUPより柔軟な検索が可能です。

旧データシートのA列からコードを検索し、C列の価格を返す式は次のようになります。

XLOOKUPは検索列と戻り列を別々に指定できるため、キー列が表の左端でなくても使えます。

新しいExcelを使っている場合は、列の追加や並び替えに強いXLOOKUPも有力な選択肢です。

ただし、古いExcelで開く共有ファイルでは互換性に注意しましょう。

 

差分確認後の保存と共有

色付けした結果を共有する場合は、比較元のファイル名、比較日、比較条件をシート上に記録します。

条件付き書式の色だけに頼らず、差分、一致、新規などの判定列を残すと、他の人も理由を追いやすくなります。

確認完了後に数式を値へ変換する場合は、元の数式入りシートを複製して保管しておくと再確認に役立ちます。

フィルターで表示されている行だけをコピーする際は、非表示行まで含まれていないかを確認してください。

 

まとめ エクセルで2つのシートを比較して差分を抽出・色付けする方法

Excelで2つのシートを比較する際は、まずデータの並び順と、照合の基準になるキー列を確認します。

同じ位置に並ぶ表なら、条件付き書式で=A2<>旧データ!A2のような式を使うことで、異なるセルをすぐに色付けできます。

行順が違う表では、商品コードなどをキーにしてVLOOKUP関数やXLOOKUP関数で対応する値を取り出す方法が有効です。

片方にしかないデータはCOUNTIF関数で抽出でき、重複したコードの発見にもつながります。

複数列をまとめて確認する場合は、IF関数とOR関数で判定列を作り、その結果を条件付き書式で行全体へ反映しましょう。

差分の色付け、文字による判定、フィルターによる抽出を組み合わせると、大きな一覧でも確認漏れを抑えられます。

最後に、数値と文字列の違い、先頭や末尾の空白、コードの桁数などを確認すれば、比較精度をさらに高められます。