VBAで２つの表の違いを調べる｜ Excel作業をVBAで効率化

２つの表の比較

Excelブックを更新していてたまに必要になるのが更新前後の比較です。比較対象にはいくつかありますが、主に、更新前後のブックや、修正前後のシートが挙げられます。

２つのブックの比較はExcelのInquireアドインを使った方が簡単なのでそちらをお勧めします。Inquireアドインは、Excelのファイルメニュー→オプション→アドイン→管理コンボでCOMアドインを選択→Inquireにチェック、で、リボンに「検査」もしくは「Inquire」タブが追加され、そこで「ファイル比較」からブックの比較ができるようになります。

ここでは２つのシートの内容を比較するマクロを紹介します。

２つのシートを比較するマクロ

以下のマクロは指定された２つのシートの内容を比較し、異なる箇所に黄色背景色を付けます。

２つ関数がありますが、１つ目のDiffSheet関数がシート比較の主処理を行います。２つ目のGetLarger関数は渡された２つの値の大きい方を返す関数です。コードが長くなるので２つに分けました。

やっていることはコメントに書いてある通りですが、２つのシートのそれぞれの入力されているセル範囲の右終端と下終端をUsedRangeプロパティを使って取得し、２つのシートで終端がより大きい方を採用して、A1セルから右下の終端に向かってセルを１つずつループし、内容を比較して異なっていれば背景色を付けています。

UsedRangeプロパティを使って終端を求めている理由は、入力されていないセルは比較する必要がないため、入力されている範囲のみを比較するためです。

'// 引数1：比較元のWorksheetオブジェクト
'// 引数2：比較先のWorksheetオブジェクト
Sub DiffSheet(a_sht1 As Worksheet, a_sht2 As Worksheet)
    Dim iRowMax As Long     '// 行数最大値
    Dim iColMax As Long     '// 列数最大値
    Dim iRow    As Long     '// 行数カウンタ
    Dim iCol    As Long     '// 列数カウンタ
    Dim r1      As Range    '// シート1のセル
    Dim r2      As Range    '// シート2のセル
    
    '// シート1と2の行数で大きい方を取得
    iRowMax = GetLarger(a_sht1.UsedRange.Rows.Count, a_sht2.UsedRange.Rows.Count)
    '// シート1と2の列数で大きい方を取得
    iColMax = GetLarger(a_sht1.UsedRange.Columns.Count, a_sht2.UsedRange.Columns.Count)
    
    '// シート1と2のA1セルのRangeオブジェクトを取得
    Set r1 = a_sht1.Range("A1")
    Set r2 = a_sht2.Range("A1")
    
    '// 行ループ
    For iRow = 0 To iRowMax
        '// 列ループ
        For iCol = 0 To iColMax
            '// シート1と2のセルの値が異なる場合
            If (r1.Offset(iRow, iCol).Value <> r2.Offset(iRow, iCol).Value) Then
                '// 背景色設定
                r1.Offset(iRow, iCol).Interior.Color = RGB(255, 255, 0)
                r2.Offset(iRow, iCol).Interior.Color = RGB(255, 255, 0)
                
                '// 座標を出力
                Debug.Print r1.Offset(iRow, iCol).Address(False, False)
            End If
        Next
    Next
End Sub

'// 大きい値を採用する
Function GetLarger(a_First, a_Second)
    If a_First >= a_Second Then
        GetLarger = a_First
    Else
        GetLarger = a_Second
    End If
End Function

'// 引数1：比較元のWorksheetオブジェクト

'// 引数2：比較先のWorksheetオブジェクト

Sub DiffSheet(a_sht1 As Worksheet, a_sht2 As Worksheet)

Dim iRowMax As Long '// 行数最大値

Dim iColMax As Long '// 列数最大値

Dim iRow As Long '// 行数カウンタ

Dim iCol As Long '// 列数カウンタ

Dim r1 As Range '// シート1のセル

Dim r2 As Range '// シート2のセル

'// シート1と2の行数で大きい方を取得

iRowMax = GetLarger(a_sht1.UsedRange.Rows.Count, a_sht2.UsedRange.Rows.Count)

'// シート1と2の列数で大きい方を取得

iColMax = GetLarger(a_sht1.UsedRange.Columns.Count, a_sht2.UsedRange.Columns.Count)

'// シート1と2のA1セルのRangeオブジェクトを取得

Set r1 = a_sht1.Range("A1")

Set r2 = a_sht2.Range("A1")

'// 行ループ

For iRow = 0 To iRowMax

'// 列ループ

For iCol = 0 To iColMax

'// シート1と2のセルの値が異なる場合

If (r1.Offset(iRow, iCol).Value <> r2.Offset(iRow, iCol).Value) Then

'// 背景色設定

r1.Offset(iRow, iCol).Interior.Color = RGB(255, 255, 0)

r2.Offset(iRow, iCol).Interior.Color = RGB(255, 255, 0)

'// 座標を出力

Debug.Print r1.Offset(iRow, iCol).Address(False, False)

End If

End Sub

'// 大きい値を採用する

Function GetLarger(a_First, a_Second)

If a_First >= a_Second Then

GetLarger = a_First

Else

GetLarger = a_Second

End If

End Function

使い方

上の比較関数のDiffSheet()の使い方は、引数に比較元と比較先のWorksheetオブジェクトを渡して実行するだけです。

以下はシート名が”Sheet1″と”Sheet2″の比較を行うサンプルです。

Sub DiffSheetTest()
    Call DiffSheet(Worksheets("Sheet1"), Worksheets("Sheet2"))
End Sub

Sub DiffSheetTest()

Call DiffSheet(Worksheets("Sheet1"), Worksheets("Sheet2"))

End Sub

実行すると、以下のように２つのシートで異なる部分に背景色が設定されます。

イミディエイトウィンドウには相違セルのアドレスが出力されます。

２つの表の比較

２つのシートを比較するマクロ

使い方

関連記事