Excelで複数のシートやファイルを扱っていると、どのセルが変更されたのか、一覧の内容に不足や重複がないかを確認したい場面があります。
目視で行と列を追う方法は手軽ですが、データ量が増えるほど見落としや確認時間の増加につながります。
数式、条件付き書式、検索機能を使い分けると、シート間やブック間の違いを効率よく見つけられます。
データ比較で押さえたいポイントです。
・同じ位置にあるセルの値を比較する
・商品コードや社員番号などのキーを基準に照合する
・違いのあるセルを色で目立たせる
・比較前に並び順とデータ形式を整える
この記事では、同じExcelファイル内のシート比較から、別ファイルのデータ照合、差分の抽出までを順に解説します。
サンプルデータは、1行目に見出しが入っている一覧を前提にしています。
同じセル位置を比較する数式
それではまず、同じ位置に配置されたデータの違いを数式で確認する方法について解説していきます。
| 商品コード | 4月シート | 5月シート | 比較結果 |
|---|---|---|---|
| A001 | 120 | 120 | 一致 |
| A002 | 85 | 90 | 相違 |
比較用の列を作る手順
比較元のシートを開き、元データの右側に比較結果を表示するための列を追加します。
たとえば4月シートのA列に商品コード、B列に売上があり、5月シートの同じセル位置にある売上と比べる場合は、C列を比較結果用に使います。
1行目には「比較結果」など、用途が分かる見出しを入力しましょう。
見出しを用意しておくと、フィルターをかけたときにも差分だけを探しやすくなります。
セル位置で比較する方法は、行の順番と列の構成が両方のシートで一致している場合に向いています。
並び順が違う一覧では、後述するXLOOKUP関数やCOUNTIF関数を使う照合のほうが安全です。
【操作のポイント】比較結果の列は、元データを上書きしない位置に作成します。
イコール記号で一致を判定する数式
C2セルに、次の数式を入力します。
=IF(B2=5月!B2,”一致”,”相違”)
この数式では、4月シートのB2セルと、5月シートのB2セルを比較しています。
値が同じなら「一致」、異なるなら「相違」と表示される仕組みです。
シート名に空白が含まれる場合は、シート名をシングルクォーテーションで囲みます。
シート名が「5月 売上」の場合の例です。
=IF(B2=’5月 売上’!B2,”一致”,”相違”)
数式の比較では、数値の120と文字列の120は同じように見えても一致しない場合があります。
意図しない相違が多いときは、セルの表示形式や先頭のアポストロフィ、余分な空白を確認してください。
【操作のポイント】判定文字は「一致」「相違」のように短く統一すると、後の抽出が簡単です。
オートフィルによる比較範囲の展開
C2セルに数式を入力したら、セル右下に表示される小さな四角を下方向へドラッグします。
これがオートフィルで、行番号を自動的に変えながら同じ比較式をコピーできます。
データが連続している場合は、フィルハンドルをダブルクリックして最終行までコピーする方法も便利です。
比較結果に「相違」が表示された行を確認すれば、修正された売上や入力漏れ候補を絞り込めます。
数式をコピーした後は、先頭行と最終行の参照先を確認し、比較範囲が途中で切れていないか確認しましょう。
【操作のポイント】比較後は「相違」でフィルターをかけると、確認対象を最小限にできます。
条件付き書式による差分の色分け
続いては、異なるセルを色で表示して、シート同士の差分を見つけやすくする方法を確認していきます。
| 商品コード | 比較元 | 比較先 |
|---|---|---|
| A001 | 120 | 120 |
| A002 | 85 | 90 |
比較するセル範囲の選択
まず、色を付けたい比較元の範囲を選択します。
たとえばB2からB100までの売上を比較するなら、B2:B100をドラッグして指定します。
見出しがある1行目は選択に含めず、実際のデータが始まる2行目から選択するのが基本です。
複数列を同時に比較したい場合は、A2:D100のように表全体を選択できます。
条件付き書式は、最初に選択した範囲を基準に数式が適用されるため、開始セルを意識することが重要です。
【操作のポイント】比較対象の列だけを選ぶと、不要な色付けを避けられます。
数式ルールによる相違セルの強調
ホームタブの「条件付き書式」から「新しいルール」を選び、「数式を使用して、書式設定するセルを決定」を選択します。
B2セルを起点にして5月シートのB2と比較する場合は、数式欄に次の式を入力します。
=B2<>5月!B2
不等号の組み合わせである<>は、「等しくない」を意味します。
書式ボタンから塗りつぶしを淡い赤や黄色に設定すると、値が異なるセルだけが目立つようになります。
赤色だけに頼らず、太字や枠線も併用すると、印刷時や色覚の違いがある場合にも差分を確認しやすくなります。
【操作のポイント】数式内の最初のセル参照は、選択範囲の左上セルに合わせます。
空白とエラー値を含むデータの確認
空白セルも差分として確認したいときは、比較式だけでなく、片方が空白かどうかも意識します。
数値が未入力なのか、ゼロが入力されているのかは、集計結果に大きく影響するためです。
エラー値が含まれるデータでは、通常の比較式でエラーが表示されることがあります。
その場合はIFERROR関数を組み合わせ、エラーであることを示す文字を返す設定にすると確認しやすくなります。
=IFERROR(IF(B2=5月!B2,”一致”,”相違”),”要確認”)
空白、ゼロ、エラーを同じ扱いにすると、データの意味を取り違えるおそれがあります。
【操作のポイント】差分の色付け後は、条件付き書式のルール管理で適用範囲を確認します。
別ファイルを参照する比較式
続いては、別のExcelファイルに保存された一覧と比較する方法を確認していきます。
| 商品コード | 現在のファイル | 前月ファイル | 判定 |
|---|---|---|---|
| A001 | 120 | 115 | 相違 |
| A002 | 90 | 90 | 一致 |
比較対象ファイルを開く準備
別ファイルを参照する数式を作るときは、最初に比較元と比較先のブックを両方開きます。
開いた状態でセルをクリックすると、Excelがブック名、シート名、セル番地を含む参照式を自動で作成してくれます。
たとえば、現在のブックのB2セルと、前月売上.xlsxの売上シートにあるB2セルを比較する準備をします。
保存場所が異なるファイルでも参照できますが、ファイル名やフォルダー構成を後から変更するとリンクが切れる場合があります。
外部参照を含む比較では、両方のファイルの更新日と版数を先に確認しておくことが大切です。
【操作のポイント】比較専用のコピーを作成してから作業すると、元ファイルを安全に保管できます。
外部参照を含むIF関数
両方のブックを開いた状態で、比較結果を表示するセルにIF関数を入力します。
=IF(B2='[前月売上.xlsx]売上’!B2,”一致”,”相違”)
角かっこ内がブック名、その後ろがシート名、最後が参照するセル番地です。
ファイルを閉じると、Excelは必要に応じてフォルダーのパスも数式に追加します。
パスが長くなるのは正常ですが、共有フォルダーの場所が変わると参照できなくなる可能性があります。
外部参照式を手入力するより、比較先セルをクリックして参照を作成するほうが入力ミスを防げます。
【操作のポイント】外部ブックへのリンク更新が表示されたら、比較対象が信頼できるファイルか確認します。
外部リンクの更新と管理
ファイルを開いたときに外部リンクの更新を確認するメッセージが表示されることがあります。
比較対象が最新の状態であることを確認できるなら、更新を選択して最新値で判定します。
更新元が不明な場合や、意図しないリンクが含まれている場合は、更新せずにデータタブの「リンクの編集」で参照先を確認しましょう。
ファイル名を変更したときは、リンクの編集画面で参照先を変更できることがあります。
【操作のポイント】外部リンクを使う比較表には、比較日時と参照ファイル名を記録しておくと管理しやすくなります。
商品コードを基準にした照合
続いては、行の並び順が異なる表でも正確に比較できる、商品コードなどを基準にした照合を確認していきます。
| 商品コード | 現在売上 | 前月売上 | 比較 |
|---|---|---|---|
| A003 | 150 | 145 | 相違 |
XLOOKUP関数による対応データの取得
XLOOKUP関数は、商品コードを検索して、別シートにある対応する値を取得できる関数です。
現在の表のA2に商品コードがあり、比較先シートのA列に商品コード、B列に売上がある場合は、次の式を使えます。
=XLOOKUP(A2,前月!A:A,前月!B:B,”該当なし”)
この数式は、A2の商品コードを前月シートのA列から探し、見つかった行のB列にある売上を返します。
見つからない場合には「該当なし」と表示されるため、新規追加された商品も見分けられます。
XLOOKUP関数は検索列が並び替えられていても、コードが一致すれば対応する値を取得できます。
【操作のポイント】商品コードは数値と文字列が混在しないよう、入力形式を統一します。
VLOOKUP関数を使う場合の注意点
ExcelのバージョンによってXLOOKUP関数が使えない場合は、VLOOKUP関数でも照合できます。
=IFERROR(VLOOKUP(A2,前月!A:B,2,FALSE),”該当なし”)
FALSEを指定すると、商品コードが完全に一致するデータだけを取得します。
VLOOKUP関数では、検索する商品コードの列を範囲の一番左に置く必要があります。
また、左側の列を返す検索はできないため、列構成によってはXLOOKUP関数のほうが扱いやすいでしょう。
近似一致の指定を誤ると、似たコードの値を返すことがあるため、一覧照合ではFALSEを指定します。
【操作のポイント】検索範囲はコピー時にずれないよう、必要に応じて$記号で絶対参照にします。
取得値と元データの比較判定
取得した前月売上と現在売上を並べたら、IF関数で一致か相違かを判定します。
たとえばB列が現在売上、C列が検索で取得した前月売上なら、D2セルには次の数式を入力します。
=IF(C2=”該当なし”,”追加”,IF(B2=C2,”一致”,”相違”))
この式では、比較先に商品コードが存在しないときは「追加」、存在して値が同じなら「一致」、値が異なるなら「相違」を返します。
削除された商品も確認したい場合は、比較元と比較先を入れ替えた表も作成します。
【操作のポイント】コード照合では、追加、削除、相違を別の判定として残すと原因を追いやすくなります。
重複と不足を確認する関数
続いては、片方の一覧にしかないデータや、同じコードが複数回含まれるデータを確認する方法を解説していきます。
| 商品コード | 比較先にある件数 | 確認結果 |
|---|---|---|
| A001 | 1 | あり |
| A099 | 0 | 不足 |
COUNTIF関数による存在確認
COUNTIF関数は、指定した範囲に同じ商品コードが何件あるかを数える関数です。
現在シートのA2にあるコードが、比較先シートのA列にあるかを確認する式は次のとおりです。
=COUNTIF(比較先!A:A,A2)
結果が1なら通常は1件存在し、0なら比較先に存在しません。
2以上になった場合は、比較先に同じコードが重複している可能性があります。
COUNTIF関数は値そのものを取得せず、存在件数を確認したいときに適しています。
【操作のポイント】全列参照が重いと感じる大きな表では、実際のデータ行に合わせて範囲を絞ります。
不足データの表示
COUNTIF関数の結果をIF関数に渡すと、不足しているコードを分かりやすく表示できます。
=IF(COUNTIF(比較先!A:A,A2)=0,”比較先にない”,”確認済み”)
この結果列にフィルターを設定し、「比較先にない」だけを表示すれば不足分だけを一覧にできます。
取引先リスト、在庫一覧、名簿など、漏れが問題になる表で役立つ方法です。
比較対象に空白行が多い場合は、空白のコードまで不足として判定しないよう注意が必要です。
【操作のポイント】空白行を除外したい場合は、IF関数でA2が空白かどうかを先に判定します。
重複データの抽出
同じ一覧の中で重複を確認する場合もCOUNTIF関数を使えます。
A2セルのコードがA列に何回現れるかを確認する数式は、次のとおりです。
=IF(COUNTIF($A$2:$A$100,A2)>1,”重複”,””)
「重複」と表示された行をフィルターすれば、同じ商品コードが複数入力されている行を確認できます。
重複が正しい複数明細なのか、誤入力なのかは、日付、担当者、数量などの関連列と合わせて判断しましょう。
【操作のポイント】重複を削除する前に、フィルターで対象行を確認し、元データのバックアップを取ります。
フィルターと比較結果の確認
続いては、作成した判定列を使って相違、追加、不足の行を確認しやすくする方法を解説していきます。
| 商品コード | 現在売上 | 比較結果 |
|---|---|---|
| A002 | 90 | 相違 |
| A099 | 30 | 追加 |
オートフィルターによる差分表示
比較表の見出し行を含めて範囲を選び、データタブからフィルターを設定します。
比較結果列の矢印をクリックし、「相違」だけにチェックを残すと、値が違う行だけを表示できます。
「追加」「比較先にない」「重複」なども、必要な確認目的に応じて選択しましょう。
フィルターはデータを削除せず、表示する行だけを絞り込む機能です。
【操作のポイント】作業後はフィルターを解除し、全件表示に戻した状態も確認します。
並べ替えによる確認順序の整理
比較結果列で並べ替えると、「相違」や「追加」を表の上部に集められます。
商品コード順に並べた状態で比較したい場合は、商品コード列を昇順に整えてから数式を確認する方法もあります。
ただし、セル位置を直接比較する数式を使っている表では、片方だけを並べ替えると比較の前提が崩れます。
位置比較の表を並べ替えるときは、比較する両方のシートを同じキーで同じ順序にそろえます。
【操作のポイント】元の順番を残したい場合は、連番列を追加してから並べ替えます。
比較結果の保存と共有
比較結果を確認したら、判定列を含めたファイルを別名で保存しておくと、確認履歴を残せます。
共有する場合は、相違行にコメントを付ける、確認日を記入する、担当者列を追加するなどの方法が有効です。
数式の結果だけを渡したいときは、コピー後に値として貼り付けると、外部リンクや参照先の影響を受けない一覧になります。
ただし値貼り付けをすると数式は失われるため、計算式を残した元ファイルも保管しましょう。
【操作のポイント】比較済みファイルには、比較対象のファイル名と作業日を記載しておくと後から追跡できます。
まとめ エクセルでデータを比較する方法（ファイル・シートの違い）
エクセルでデータを比較する方法は、表の構成と確認したい違いに応じて選ぶことが重要です。
同じ行と列に同じ種類のデータが並ぶ表なら、IF関数でセル位置を比較する方法が分かりやすいでしょう。
違いを視覚的に探したい場合は、条件付き書式で相違セルを色付けすると確認効率が上がります。
別ファイルのデータは外部参照で比較できますが、リンク先やファイルの更新状態を確認する必要があります。
行順が異なる一覧では、商品コード、社員番号、注文番号など、重複しないキーを基準にXLOOKUP関数やVLOOKUP関数で照合する方法が有効です。
COUNTIF関数を使えば、比較先にないデータや重複データも確認できます。
最後にフィルターで相違行だけを抽出し、比較日時と対象ファイルを残して保存すると、正確で再利用しやすい比較作業になります。
