【Excel VBA】ブック間の表データ差分を抽出する方法!差分箇所の色付けや詳細出力も!

2つのブックにある表データの差分を抽出したいときはないでしょうか。

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

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

・ツールを使わずにExcelブック間の表データの差分を抽出したいが方法がわからない。
・関数を使うと条件式が複雑になり設定も面倒なので避けたい。
・VBAでエクセルの表データの差分抽出と差分箇所の色付けを一括で処理したいがやり方がわからない。

ですよね。

今回はそんなお悩みを解決する
ブック間の表データの差分を抽出する方法と差分箇所の色付けや詳細出力も一括処理する方法
についてまとめます!

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に先ほど作成した表データの場所を設定しましょう。

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

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

strHikakuMotoBook = “比較元.xlsx”

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

strHikakuSakiBook = “比較先.xlsx”

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

strHikakuMotoSheet = “比較元”

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

strHikakuSakiSheet = “比較先”

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

strHikakuRange = “A2:C7”

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

strExpCol = “F”

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

k = 2

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

VBAを実装し実行する

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

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

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

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

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

VBAの説明

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

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

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

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) & “です!”



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

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

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

Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Cells(k + i – 1, j).Interior.Color = 65535 ‘黄色
↓ へ変更します。
Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Cells(k + i – 1, j).Interior.Color = 5287936 ‘緑

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

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

Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Cells(k + i – 1, j).Interior.Color = 65535 ‘黄色
Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Cells(k + i – 1, j).Interior.Color = 5287936 ‘緑
Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Cells(k + i – 1, j).Interior.Color = 255 ‘赤
Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Cells(k + i – 1, j).Interior.Color = 10498160 ‘紫

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



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

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

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

‘コメントを削除します。
Workbooks(strHikakuMotoBook).Sheets(strHikakuMotoSheet).Range(strHikakuRange).ClearComments
‘各セルへメモを追加します。
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) & “です!”

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

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」をクリックし実行したいマクロを選択し、「実行」をクリックします。

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


さいごに

いかがでしょうか。

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

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

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

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



この記事の関連キーワード

こちらの記事の関連キーワード一覧です。クリックするとキーワードに関連する記事一覧が閲覧できます。



コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です