修正前と修正後の表を目で見比べても、1文字の違いや数字1桁の違いは見落としがちです。2つのシートのレイアウトが同じなら、同じ位置のセルどうしを数式や条件付き書式で比べれば、変わったセルだけを機械的に拾えます。
この記事では、3つの比べ方を紹介します。差分用のシートにIF式を並べる方法、条件付き書式で色を付ける方法、Spreadsheet Compareを使う方法です。最後に、変わったセルを「セル番地・修正前・修正後」の一覧にするところまで扱います。
比べる前にそろえておくこと
セル単位の比較は、「A5は修正前も修正後もA5のデータを指している」ことが前提です。最初に次の点を確認してください。
- 行や列が挿入・削除されていないか：修正後のシートで途中に1行挿入していると、それより下のセルがすべて「変わった」と判定されます。行の追加や削除がある表は、セル単位ではなく、顧客IDなどのキー列を使ってレコード単位で比べる方法が向いています。
- 2つのシートを同じブックに入れる：「修正前」「修正後」のように名前を付けて、1つのブックにまとめると数式が書きやすくなります。シートの「移動またはコピー」で「コピーを作成する」にチェックを入れて移せば、元のファイルは変わりません。
- 比べる範囲を決める：たとえば
A1:H200のように、修正前と修正後で同じ範囲を指定します。 - 何を比べるか決める：この記事の方法1・2・4が比べるのは、セルの「値」です。数式の中身や書式の違いまで確認したい場合は、方法3のSpreadsheet Compareを使ってください。
方法1：差分用のシートにIF式を並べる
新しいシート（たとえば「差分」）を作り、A1に次の式を入れます。比べる範囲（例：A1:H200）と同じ大きさになるようにコピーしてください。
=IF(EXACT(修正前!A1,修正後!A1),"",修正前!A1&" → "&修正後!A1)
値が同じセルは空欄になり、違うセルには「修正前の値 → 修正後の値」が表示されます。空欄でないセルが、変わったセルです。
比べ方は EXACT と = の2通りあり、判定が少し違います。
| 比べ方 | 大文字と小文字 | 数値の1と文字列の"1" |
|---|---|---|
EXACT(修正前!A1,修正後!A1) |
区別する | 同じとみなす |
修正前!A1=修正後!A1 |
区別しない | 違うとみなす |
英字の大文字・小文字の違いも拾いたいなら EXACT、数値と文字列の違いを拾いたいなら = を選びます。
変わったセルの数は、次の式で数えられます。
=SUMPRODUCT(--NOT(EXACT(修正前!A1:H200,修正後!A1:H200)))
どちらかのシートにエラー値（#N/Aなど）があると、そのセルではIF式もエラーになり、件数の式もエラーになります。差分シートにエラーが出たら、そのセルは目で確認してください。
シート名に空白や記号が含まれる場合は、'修正 前'!A1 のように、シート名をシングルクォーテーションで囲みます。
方法2：条件付き書式で修正後のシートに色を付ける
修正後のシートで、変わったセルを直接目立たせる方法です。
- 「修正後」シートで、比べる範囲（A1:H200）を選びます。このときA1がアクティブセル（白く残っているセル）になるように、A1から選び始めてください。
- 「ホーム」→「条件付き書式」→「新しいルール」→「数式を使用して、書式設定するセルを決定」を選びます。
- 数式に
=NOT(EXACT(A1,修正前!A1))と入力し、塗りつぶしの色を設定します。
セルをクリックして参照を入れると、$A$1 のように $ 付きの絶対参照になります。絶対参照のままでは、すべてのセルがA1とだけ比べられてしまうので、$ を消して A1 にしてください。
条件付き書式で別のシートを参照できるかどうかは、Excelのバージョンによって異なります。エラーが出て設定できない場合は、方法1の差分シートを使ってください。
この方法では、修正後の画面のまま変わった場所が分かります。一方で、修正前の値は表示されず、一覧にもできません。件数や変更前の値も記録しておきたい場合は、方法1か方法4と組み合わせます。
方法3：Spreadsheet Compareでブックどうしを比べる
Officeのエディションによっては、「Spreadsheet Compare」というツールが入っています。Windowsのスタートメニューで「Spreadsheet Compare」を検索し、見つかれば使えます。見つからない場合は、ほかの方法を使ってください。
- このツールが比べるのは、ブック（ファイル）どうしです。修正前と修正後を別々のファイルとして保存しておきます。
- 比べる2つのファイルを指定すると、違いのあるセルが一覧で表示されます。
- 入力値だけでなく、数式や書式の違いも比べられます。どの項目を比べるかは、画面のオプションで選びます。
- 結果をExcelファイルに書き出す機能があるかどうかは、お使いのバージョンの画面メニューで確認してください。
数式が書き換わった、書式だけが変わった、といった値以外の違いを確認したいときに向いています。
方法4：変わったセルを一覧にする（VBA）
変わったセルを報告したり、1件ずつ確認したりするには、「セル番地・修正前・修正後」の一覧があると便利です。次のマクロは、新しいシートにその一覧を作ります。
Sub ListChangedCells()
Const TARGET As String = "A1:H200" ' 比べる範囲
Dim wsOld As Worksheet, wsNew As Worksheet, wsOut As Worksheet
Dim cOld As Range, cNew As Range
Dim outRow As Long
Set wsOld = ThisWorkbook.Worksheets("修正前")
Set wsNew = ThisWorkbook.Worksheets("修正後")
Set wsOut = ThisWorkbook.Worksheets.Add(After:=wsNew)
wsOut.Range("A1:C1").Value = Array("セル", "修正前", "修正後")
outRow = 2
Application.ScreenUpdating = False
For Each cOld In wsOld.Range(TARGET).Cells
Set cNew = wsNew.Range(cOld.Address)
If TypeName(cOld.Value) <> TypeName(cNew.Value) _
Or CellText(cOld) <> CellText(cNew) Then
wsOut.Cells(outRow, 1).Value = cOld.Address(False, False)
wsOut.Cells(outRow, 2).Value = "'" & CellText(cOld)
wsOut.Cells(outRow, 3).Value = "'" & CellText(cNew)
outRow = outRow + 1
End If
Next cOld
Application.ScreenUpdating = True
MsgBox "変わったセル：" & (outRow - 2) & " 件"
End Sub
Private Function CellText(ByVal c As Range) As String
If IsError(c.Value) Then
CellText = c.Text
Else
CellText = CStr(c.Value)
End If
End Function
このマクロの判定ルールは次のとおりです。
- 大文字と小文字を区別します。
- 値の種類が変わった場合も「変わった」と判定します。たとえば、数値の1が文字列の"1"になった場合や、日付が数値になった場合です。
- 何も入っていないセルと、数式で
""を返しているセルは、見た目は同じでも別のものとして判定されます。 - 一覧に書き込む値の先頭には
'を付けています。「001」が「1」に変わったり、日付が数値に変わったりせず、元の文字のまま表示させるためです。
このマクロは、方法1の EXACT とは数値と文字列の扱いが違います。そのため、件数がわずかに食い違うことがあります。食い違ったセルを見ると、数値と文字列が入れ替わったセルを見つける手がかりになります。
一覧ができたら確認すること
- 件数が想定どおりか：「今回直したのは3か所のはず」と分かっているなら、件数がそれと合うかを見ます。件数が想定よりずっと多い場合は、行のずれ、余分な空白、数値と文字列の違いが原因のことがよくあります。
- 意図しない変更が混ざっていないか：一覧を上から順に見て、直した覚えのないセルがあれば、修正後のファイルで確認します。
- 範囲の外に変更がないか：比べたのは指定した範囲だけです。最終行が増えた場合や、範囲外の列を使っている場合は、範囲を広げて比べ直してください。
