【Excel VBA】2つのファイルを比較し差分抽出する方法！違う箇所の色付けも！

2つのファイル（ブック）にある表データを比較し差分を抽出したいときはないでしょうか。

例えば同じフォーマットの表が2つのブックに存在し、更新した表と元の表のデータで異なる箇所を確認したいときなどです。

けど、そんな中で悩むことは、

ですよね。

今回はそんなお悩みを解決する

についてまとめます！

VBAでExcel の表データの差分を抽出するイメージ

VBAでExcel の表データの差分を抽出するイメージについて説明をします。

前回、同一ブック内のシート間の差分抽出を行いましたが、

今回はブック間にあるシートを対象に処理を行っていきます。

具体的には、

比較対象となる2つの表を別ブックで用意します。

比較元ブック

比較先ブック

VBAを実行すると・・・

比較元シートの表に色付けがされ、また差分箇所の詳細が出力されます！

そしてさらに、

色付けの背景色も好みの色に変更できます！

さらに、さらに、
差分内容を差分箇所のセルにメモ挿入することなんてもできてしまいます！！

どこのセルに差分があり、その値が何であるか一目でわかりますね！

それでは早速VBAを実装して使ってみましょう！

タカヒロ

VBA版の場合、アプリのインストールが不要になるところもうれしいところですね。

比較したい表データを用意する

まずは、比較したい表データを用意しましょう。

ブックを2つ作成し、それぞれのシートにデータを入力していきます。

データの内容はなんでもよいですが、表の行と列の数は同じにするようにしてください。

サンプルは以下のようにしました。

比較対象となる2つの表を別ブックで用意します。

比較元ブック

比較先ブック

参照元ブックのF列に差分詳細のリストが出力されますので、開けてあります。

表データの差分を抽出するVBA

表データの差分を抽出するVBAを準備します。

サンプルコードは以下の通りです。

Sub 表データの差分を抽出_別ブック版()


    Dim arrHikakuMotoHani As Variant
    Dim arrHikakuSakiHani As Variant
    Dim strHikakuMotoSheet As String
    Dim strHikakuSakiSheet As String
    Dim strHikakuMotoBook As String
    Dim strHikakuSakiBook As String
    Dim strHikakuRange As String
    Dim strExpCol As String
    
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim l As Integer
    
    '設定ここから-----------
    

    '比較元シート名を指定します。
    strHikakuMotoBook = "比較元.xlsx"

    '比較先シート名を指定します。
    strHikakuSakiBook = "比較先.xlsx"
    

    '比較元シート名を指定します。
    strHikakuMotoSheet = "比較元"

    '比較先シート名を指定します。
    strHikakuSakiSheet = "比較先"
    
    '比較する表の範囲を指定します。
    strHikakuRange = "A2:C7"

    '差分詳細を出力する列を指定します。
    strExpCol = "F"
    
    '差分詳細を出力する開始セルの行番号を指定します。例えばF2セルの場合2を指定します。
    k = 2
    
    '設定ここまで-----------
    
    l = 0
    
    '比較元の表データを配列に格納します。
    arrHikakuMotoHani = Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Range(strHikakuRange)
    
    '比較先の表データを配列に格納します。
    arrHikakuSakiHani = Workbooks(strHikakuSakiBook).Sheets(strHikakuSakiSheet).Range(strHikakuRange)
    
    '背景色を削除します。
     Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Range(strHikakuRange).Interior.Pattern = xlNone
    
    '差分抽出詳細一覧の値を削除します。
    Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Range(strExpCol & k & ":" & strExpCol & "100000").ClearContents
    

    '配列に格納された値すべてを対象に処理します。
    For i = LBound(arrHikakuMotoHani, 1) To UBound(arrHikakuMotoHani, 1)
    
        For j = LBound(arrHikakuMotoHani, 2) To UBound(arrHikakuMotoHani, 2)
  
            If Not arrHikakuMotoHani(i, j) = arrHikakuSakiHani(i, j) Then
            
                '比較元表のセルへ背景色を追加します。
                Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Cells(k + i - 1, j).Interior.Color = 65535  '黄色
                
                '差分詳細を出力します。
                Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Range(strExpCol & (k + l)).Value = Cells(i, j).Address & "が一致しません！" & strHikakuMotoSheet & "シートの値" & arrHikakuMotoHani(i, j) & "に対して" & strHikakuSakiSheet & "シートの値は" & arrHikakuSakiHani(i, j) & "です！"
                l = l + 1
            
            End If
        Next
    Next
    
End Sub

VBAの設定をする

表データの差分を抽出するVBAに先ほど作成した表データの場所を設定しましょう。

設定箇所は以下の通りです。

比較元シート名を指定します。

比較先シート名を指定します。

比較元シート名を指定します。

比較先シート名を指定します。

比較する表の範囲を指定します。

差分詳細を出力する列を指定します。

差分詳細を出力する開始セルの行番号を指定します。例えばF2セルの場合2を指定します。

タカヒロ

それぞれの値は各表形式に合わせて変更してください。

VBAを実装し実行する

VBAを実装し実行してみましょう。

実装方法は「VBAの実装手順」をご覧ください。

実装できたらマクロから「表データの差分を抽出」を見つけ実行します。

タカヒロ

各ブックは開いている状態にするようお願いします。

はい、差分抽出の内容が出力され、差分があるセルの背景も黄色に変わっていますね！

VBAの説明

表データの差分を抽出するVBAについて説明をします。

タカヒロ

前回のシート版に対してWorkbooksオブジェクトを追加した形になっています。

比較元の表データを配列に格納します。

比較先の表データを配列に格納します。

背景色を削除します。

差分抽出詳細一覧の値を削除します。

配列に格納された値すべてを対象に処理します。

一致していない場合、詳細出力します。

比較元表のセルへ背景色を追加します。

差分詳細を出力します。

差分があるセルに色付けされる色を変更する

次に差分があるセルに色付けされる色を変更してみましょう。

サンプルでは黄色でしたが、こちらを落ち着いた緑色にしてみます。

はい、緑色に変更されましたね。

その他のカラーコードは以下になります。

タカヒロ

表の種類で色をかえてもよいかもしれませんね。

差分があるセルにメモで差分内容を挿入する

現在表の欄外で一覧出力される差分内容について、差分があるセルにメモで挿入してみましょう。

追加するコードはこちらになります。

コード全体はこちらになります。

Sub 表データの差分を抽出_別ブック版_メモ追加版()

    Dim arrHikakuMotoHani As Variant
    Dim arrHikakuSakiHani As Variant
    Dim strHikakuMotoSheet As String
    Dim strHikakuSakiSheet As String
    Dim strHikakuMotoBook As String
    Dim strHikakuSakiBook As String
    Dim strHikakuRange As String
    Dim strExpCol As String
    
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim l As Integer
    
    '設定ここから-----------

    '比較元シート名を指定します。
    strHikakuMotoBook = "比較元.xlsx"

    '比較先シート名を指定します。
    strHikakuSakiBook = "比較先.xlsx"
    
    '比較元シート名を指定します。
    strHikakuMotoSheet = "比較元"

    '比較先シート名を指定します。
    strHikakuSakiSheet = "比較先"
    
    '比較する表の範囲を指定します。
    strHikakuRange = "A2:C7"

    '差分詳細を出力する列を指定します。
    strExpCol = "F"
    
    '差分詳細を出力する開始セルの行番号を指定します。例えばF2セルの場合2を指定します。
    k = 2
    
    '設定ここまで-----------
    
    l = 0
    
    '比較元の表データを配列に格納します。
    arrHikakuMotoHani = Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Range(strHikakuRange)
    
    '比較先の表データを配列に格納します。
    arrHikakuSakiHani = Workbooks(strHikakuSakiBook).Sheets(strHikakuSakiSheet).Range(strHikakuRange)
    
    '背景色を削除します。
     Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Range(strHikakuRange).Interior.Pattern = xlNone
    
    '差分抽出詳細一覧の値を削除します。
    Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Range(strExpCol & k & ":" & strExpCol & "100000").ClearContents
    
    'コメントを削除します。
    Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Range(strHikakuRange).ClearComments
        
    
    '配列に格納された値すべてを対象に処理します。
    For i = LBound(arrHikakuMotoHani, 1) To UBound(arrHikakuMotoHani, 1)
    
        For j = LBound(arrHikakuMotoHani, 2) To UBound(arrHikakuMotoHani, 2)
  
            If Not arrHikakuMotoHani(i, j) = arrHikakuSakiHani(i, j) Then
            
                '比較元表のセルへ背景色を追加します。
                Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Cells(k + i - 1, j).Interior.Color = 65535  '黄色
                
                '各セルへメモを追加します。
                Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Cells(k + i - 1, j).AddComment
                Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Cells(k + i - 1, j).Comment.Text Text:=strHikakuSakiSheet & "シートの値は" & arrHikakuSakiHani(i, j) & "です！"

                '差分詳細を出力します。
                Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Range(strExpCol & (k + l)).Value = Cells(i, j).Address & "が一致しません！" & strHikakuMotoSheet & "シートの値" & arrHikakuMotoHani(i, j) & "に対して" & strHikakuSakiSheet & "シートの値は" & arrHikakuSakiHani(i, j) & "です！"
                l = l + 1
            
            End If
        Next
    Next
    
End Sub

実行してみましょう。

はい、各セルのメモが追加され差分の詳細が入力されていますね！

VBAの実装手順

実装手順は以下の通りです。

今回はExcel側にこのVBAを実装します。

①Excelを新規に開き、「開発」タブをクリックし、「VisualBasic」をクリックします。
もしくはショートカットキー「Alt」＋「F11」でもOKです。

②標準モジュールを追加します。
左ペインのVBAProjectを右クリックし、「挿入」、「標準モジュール」を選択します。

③右ペインのウインドウに上記のVBAを入力します。

こちらで完了です。

VBAを実行する

では早速VBAの実行をしてみましょう。

①「開発」タブの「VBA」をクリックし実行したいマクロを選択し、「実行」をクリックします。

②処理がされたことが確認できれば完了です。
※完了メッセージやステータス管理など必要に応じて実装してもらえばと思います。

＜改良版＞比較する表の範囲をB列以降でも可能にする

ご利用者様より、A列からではなく、B列以降の後ろの列で範囲指定すると、セルの色塗りの列がズレてしまう報告がありました。

例えば”B5:D10″を範囲指定すると、以下のようになります。

原因としてはCellsで指定している出力先をA列からはじまるよう設定している（二次元配列の列要素が1から始まる）ためとなります。

以下の部分です。

やはりA列固定では柔軟性に欠けることから、B列以降の指定でも対応できるよう改良をしましたので、改良版のコードを公開したいと思います。

タカヒロ

ご指摘いただけましたこと、大変感謝いたします。

表データの差分を抽出_別ブック版_改良版

修正版サンプルコードは以下の通りです。

Sub 表データの差分を抽出_別ブック版_改良版()

    Dim arrHikakuMotoHani As Variant
    Dim arrHikakuSakiHani As Variant
    Dim strHikakuMotoSheet As String
    Dim strHikakuSakiSheet As String
    Dim strHikakuMotoBook As String
    Dim strHikakuSakiBook As String
    Dim strHikakuRange As String
    Dim strExpCol As String
    
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim l As Integer
    
    Dim intRangCol As Integer
    Dim intRangRow As Integer
    Dim objHikakuMotoRange As Range
    
    
    '設定ここから-----------

    '比較元シート名を指定します。
    strHikakuMotoBook = "比較元.xlsm"

    '比較先シート名を指定します。
    strHikakuSakiBook = "比較先.xlsx"
    
    '比較元シート名を指定します。
    strHikakuMotoSheet = "比較元"

    '比較先シート名を指定します。
    strHikakuSakiSheet = "比較先"
    
    '比較する表の範囲を指定します。
    strHikakuRange = "B5:D10"


    '差分詳細を出力する列を指定します。
    strExpCol = "G"
    
    '差分詳細を出力する開始セルの行番号を指定します。例えばF2セルの場合2を指定します。
    k = 2
    
    '設定ここまで-----------
    
    l = 0
    
    
    '比較元の表Rangeをオブジェクトにセットします。
    Set objHikakuMotoRange = Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Range(strHikakuRange)
    '比較元の表Rangeの左端列番号を取得します。
    intRangCol = objHikakuMotoRange.Column - 1
    '比較元の表Rangeの上部行番号を取得します。
    intRangRow = objHikakuMotoRange.Row - 1
    
    
    '比較元の表データを配列に格納します。
    arrHikakuMotoHani = Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Range(strHikakuRange)
    
    '比較先の表データを配列に格納します。
    arrHikakuSakiHani = Workbooks(strHikakuSakiBook).Sheets(strHikakuSakiSheet).Range(strHikakuRange)
    
    '背景色を削除します。
     Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Range(strHikakuRange).Interior.Pattern = xlNone
    
    '差分抽出詳細一覧の値を削除します。
    Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Range(strExpCol & k & ":" & strExpCol & "100000").ClearContents
    
    
    '配列に格納された値すべてを対象に処理します。
    For i = LBound(arrHikakuMotoHani, 1) To UBound(arrHikakuMotoHani, 1)
    
        For j = LBound(arrHikakuMotoHani, 2) To UBound(arrHikakuMotoHani, 2)
  
            If Not arrHikakuMotoHani(i, j) = arrHikakuSakiHani(i, j) Then
            
                '比較元表のセルへ背景色を追加します。
                Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Cells(i + intRangRow, j + intRangCol).Interior.Color = 65535   '黄色
                
                '差分詳細を出力します。
                Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Range(strExpCol & (k + l)).Value = Cells(i + intRangRow, j + intRangCol).Address & "が一致しません！" & strHikakuMotoSheet & "シートの値" & arrHikakuMotoHani(i, j) & "に対して" & strHikakuSakiSheet & "シートの値は" & arrHikakuSakiHani(i, j) & "です！"
                l = l + 1
            
            End If
        Next
    Next
    
End Sub

比較対象の表の範囲をB列以降に設定し、VBAを実行してみましょう。

はい、背景色の位置が修正されていることが確認できましたね。

修正箇所

修正箇所は以下の通りです。

比較元の表Rangeをオブジェクトにセットします。

比較元の表Rangeの左端列番号を取得します。

比較元の表Rangeの上部行番号を取得します。

背景色を追加するセル番号を変数intRangRow、intRangColにて指定します。

差分詳細を出力するセル番号を変数intRangRow、intRangColにて指定します。

表データの差分を抽出_別ブック版_メモ追加版_改良版

続いてメモ追加版も修正をしてみます。

Sub 表データの差分を抽出_別ブック版_メモ追加版_改良版()

    Dim arrHikakuMotoHani As Variant
    Dim arrHikakuSakiHani As Variant
    Dim strHikakuMotoSheet As String
    Dim strHikakuSakiSheet As String
    Dim strHikakuMotoBook As String
    Dim strHikakuSakiBook As String
    Dim strHikakuRange As String
    Dim strExpCol As String
    
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim l As Integer
    
    Dim intRangCol As Integer
    Dim intRangRow As Integer
    Dim objHikakuMotoRange As Range
    
    
    '設定ここから-----------

    '比較元シート名を指定します。
    strHikakuMotoBook = "比較元.xlsm"

    '比較先シート名を指定します。
    strHikakuSakiBook = "比較先.xlsx"
    
    '比較元シート名を指定します。
    strHikakuMotoSheet = "比較元"

    '比較先シート名を指定します。
    strHikakuSakiSheet = "比較先"
    
    '比較する表の範囲を指定します。
    strHikakuRange = "B5:D10"


    '差分詳細を出力する列を指定します。
    strExpCol = "G"
    
    '差分詳細を出力する開始セルの行番号を指定します。例えばF2セルの場合2を指定します。
    k = 2
    
    '設定ここまで-----------
    
    l = 0
    
    
    '比較元の表Rangeをオブジェクトにセットします。
    Set objHikakuMotoRange = Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Range(strHikakuRange)
    '比較元の表Rangeの左端列番号を取得します。
    intRangCol = objHikakuMotoRange.Column - 1
    '比較元の表Rangeの上部行番号を取得します。
    intRangRow = objHikakuMotoRange.Row - 1
    
    
    '比較元の表データを配列に格納します。
    arrHikakuMotoHani = Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Range(strHikakuRange)
    
    '比較先の表データを配列に格納します。
    arrHikakuSakiHani = Workbooks(strHikakuSakiBook).Sheets(strHikakuSakiSheet).Range(strHikakuRange)
    
    '背景色を削除します。
     Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Range(strHikakuRange).Interior.Pattern = xlNone
    
    '差分抽出詳細一覧の値を削除します。
    Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Range(strExpCol & k & ":" & strExpCol & "100000").ClearContents
    
    'コメントを削除します。
    Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Range(strHikakuRange).ClearComments
        
    
    '配列に格納された値すべてを対象に処理します。
    For i = LBound(arrHikakuMotoHani, 1) To UBound(arrHikakuMotoHani, 1)
    
        For j = LBound(arrHikakuMotoHani, 2) To UBound(arrHikakuMotoHani, 2)
  
            If Not arrHikakuMotoHani(i, j) = arrHikakuSakiHani(i, j) Then
            
                '比較元表のセルへ背景色を追加します。
                Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Cells(i + intRangRow, j + intRangCol).Interior.Color = 65535   '黄色
                
                '各セルへメモを追加します。
                Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Cells(i + intRangRow, j + intRangCol).AddComment
                Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Cells(i + intRangRow, j + intRangCol).Comment.Text Text:=strHikakuSakiSheet & "シートの値は" & arrHikakuSakiHani(i, j) & "です！"

                '差分詳細を出力します。
                Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Range(strExpCol & (k + l)).Value = Cells(i + intRangRow, j + intRangCol).Address & "が一致しません！" & strHikakuMotoSheet & "シートの値" & arrHikakuMotoHani(i, j) & "に対して" & strHikakuSakiSheet & "シートの値は" & arrHikakuSakiHani(i, j) & "です！"
                l = l + 1
            
            End If
        Next
    Next
    
End Sub

同じく比較対象の表の範囲をB列以降に設定し、VBAを実行してみましょう。

はい、背景色の位置に加え、メモの位置も修正されていることが確認できましたね。

さいごに

いかがでしょうか。

今回は、

についてまとめました。

また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。

今回は、
ブック間の表データの差分を抽出する方法と差分箇所の色付けや詳細出力も一括処理する方法
についてまとめました。

また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。