【EXCEL：VBA】2つのデータを比較したい

この記事では、EXCELで2つのデータを比較して、追加・変更・削除したデータを調べる機能について説明しています。

比較したいデータを2つのシートにそれぞれ入力し、キーとなる列を指定すれば比較できます。

キーとなる列とは、その列に入力されている値の組み合わせで、行を特定できる列のことです。

もちろん、従業員番号のように1列で対象データを特定できるデータでも比較可能です。

2つのデータそれぞれから、もう一方のデータを比較するので、追加したデータや削除したデータも見つけることができます。

データ比較は、結構時間がかかる作業ですし、比較ミスしやすい作業です。

こういう単純で、もれなく行う必要がある作業は、VBAを使うことをオススメします。

それでは、EXCELで2つのデータを比較して、追加・変更・削除したデータを調べる機能を作成していきましょう。

困っている女性

EXCELに入力されているデータを比較したいの・・・。

困っている女性

以前のデータに対して、追加したり、変更したりした箇所を忘れてしまって、調べるのに時間がかかってしまうの。

それなら、VBAを使えば2つのデータを比較して、追加した行・変更した行・削除した行を簡単に調べれるよ。

解決する男性

困っている女性

それそれ、早く教えてよ。

以下の記事では、2つのデータの差分を抽出する機能について、説明しています。

【EXCEL：VBA】2つのデータの差分を抽出したい

EXCELでデータを比較する機能に必要なものを、1つ1つ順番に説明していきます。

１．データを比較するために必要なシートを作成する

まず、「区切り文字」、「くくり文字」、「CSVファイルパス」を指定する欄を作成します。

「メイン」シートを作成する

メインシートを追加する

「メイン」という名前のシートを作成します。
1行目に「キーとなる列」のタイトル行を作成します。
2行目以降は、実際に「キーとなる列」を指定する行となります。

「キーとなる列」には、列番号（1、2、3など）を数字で入力します。

また、列番号は重複して入力しないようにしてください。

「データ①」シートを作成する

データ①シートを追加する

「データ①」という名前のシートを作成します。
1行目には、各項目の項目名を入力します。いわゆる、ヘッダー行です。
2行目以降は、実際のデータを入力します。

「データ②」シートを作成する

データ②シートを追加する

「データ②」という名前のシートを作成します。
1行目には、各項目の項目名を入力します。いわゆる、ヘッダー行です。
2行目以降は、実際のデータを入力します。

２．2つのデータを比較する機能を記入する

「Microsoft Visual Basic for Applications」を起動します。

VBAプログラムを記入する（その１）

VBAプログラムを記入する（その２）

VBAプログラムを記入する（その３）

標準モジュールを追加して、以下のソースを書きます。

Public Sub MainProc()
    Dim varKey As Variant
    Dim varNoKey() As Long
    Dim shtMain As Worksheet
    Dim shtData1 As Worksheet
    Dim shtData2 As Worksheet
    Dim lastRow1 As Long
    Dim lastRow2 As Long
    Dim lastCol As Long
    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim m As Long
    Dim blnSame As Boolean
    Dim blnExist As Boolean
    
    '①「メイン」シートを変数に格納する
    Set shtMain = ThisWorkbook.Sheets("メイン")
    
    '②キーとなる列を配列に格納する
    varKey = shtMain.Range(shtMain.Cells(2, 1), _
                           shtMain.Cells(shtMain.Cells(shtMain.Rows.Count, 1).End(xlUp).Row, 1))
            
    '③「データ①」シートを変数に格納する
    Set shtData1 = ThisWorkbook.Sheets("データ①")
    
    '④「データ①」シートの最終行を取得する
    lastRow1 = shtData1.Cells(shtData1.Rows.Count, 1).End(xlUp).Row
    
    '⑤「データ①」シートの最終列を取得する
    lastCol = shtData1.Cells(1, shtData1.Columns.Count).End(xlToLeft).Column
    
    '⑥「データ②」シートを変数に格納する
    Set shtData2 = ThisWorkbook.Sheets("データ②")
        
    '⑦「データ②」シートの最終行を取得する
    lastRow2 = shtData2.Cells(shtData2.Rows.Count, 1).End(xlUp).Row
    
    '⑧キーではない列を格納する配列を初期化する
    ReDim varNoKey(0)
    
    '⑨キーではない列を配列に格納する
    For i = 1 To lastCol
        
        blnExist = False
    
        For j = 1 To UBound(varKey)
            If i = varKey(j, 1) Then
                blnExist = True
                Exit For
            End If
        Next
        
        If blnExist = False Then
            ReDim Preserve varNoKey(UBound(varNoKey) + 1)
            varNoKey(UBound(varNoKey)) = i
        End If
    Next
    
    '⑩「データ①」シートの最終列に「結果」列を追加する
    shtData1.Cells(1, lastCol + 1) = "結果"
    
    '⑪「データ②」シートの最終列に「結果」列を追加する
    shtData2.Cells(1, lastCol + 1) = "結果"
    
    '⑫「データ①」シートと「データ②」シートを比較する
    For i = 2 To lastRow1
    
        blnExist = False
    
        For j = 2 To lastRow2
        
            blnSame = True
            
            For k = 1 To UBound(varKey)
                If shtData1.Cells(i, varKey(k, 1)) <> shtData2.Cells(j, varKey(k, 1)) Then
                    blnSame = False
                    Exit For
                End If
            Next
            
            If blnSame = True Then
                blnExist = True
                
                For m = 1 To UBound(varNoKey)
                    If shtData1.Cells(i, varNoKey(m)) <> shtData2.Cells(j, varNoKey(m)) Then
                        blnSame = False
                        Exit For
                    End If
                Next
                
                If blnSame = True Then
                    shtData1.Cells(i, lastCol + 1) = "変更なし"
                    shtData2.Cells(j, lastCol + 1) = "変更なし"
                Else
                    shtData1.Cells(i, lastCol + 1) = "変更あり"
                    shtData2.Cells(j, lastCol + 1) = "変更あり"
                End If
                
                Exit For
            End If
        Next
        
        If blnExist = False Then
            shtData1.Cells(i, lastCol + 1) = "削除"
        End If
    Next
    
    '⑬「データ②」シートの追加された行の結果を記入する
    For i = 1 To lastRow2
        If shtData2.Cells(i, lastCol + 1) = "" Then
            shtData2.Cells(i, lastCol + 1) = "追加"
        End If
    Next
    
    MsgBox "完了"
End Sub

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

Public Sub MainProc()

Dim varKey As Variant

Dim varNoKey() As Long

Dim shtMain As Worksheet

Dim shtData1 As Worksheet

Dim shtData2 As Worksheet

Dim lastRow1 As Long

Dim lastRow2 As Long

Dim lastCol As Long

Dim i As Long

Dim j As Long

Dim k As Long

Dim m As Long

Dim blnSame As Boolean

Dim blnExist As Boolean

'①「メイン」シートを変数に格納する

Set shtMain = ThisWorkbook.Sheets("メイン")

'②キーとなる列を配列に格納する

varKey = shtMain.Range(shtMain.Cells(2, 1), _

shtMain.Cells(shtMain.Cells(shtMain.Rows.Count, 1).End(xlUp).Row, 1))

'③「データ①」シートを変数に格納する

Set shtData1 = ThisWorkbook.Sheets("データ①")

'④「データ①」シートの最終行を取得する

lastRow1 = shtData1.Cells(shtData1.Rows.Count, 1).End(xlUp).Row

'⑤「データ①」シートの最終列を取得する

lastCol = shtData1.Cells(1, shtData1.Columns.Count).End(xlToLeft).Column

'⑥「データ②」シートを変数に格納する

Set shtData2 = ThisWorkbook.Sheets("データ②")

'⑦「データ②」シートの最終行を取得する

lastRow2 = shtData2.Cells(shtData2.Rows.Count, 1).End(xlUp).Row

'⑧キーではない列を格納する配列を初期化する

ReDim varNoKey(0)

'⑨キーではない列を配列に格納する

For i = 1 To lastCol

blnExist = False

For j = 1 To UBound(varKey)

If i = varKey(j, 1) Then

blnExist = True

Exit For

End If

If blnExist = False Then

ReDim Preserve varNoKey(UBound(varNoKey) + 1)

varNoKey(UBound(varNoKey)) = i

End If

'⑩「データ①」シートの最終列に「結果」列を追加する

shtData1.Cells(1, lastCol + 1) = "結果"

'⑪「データ②」シートの最終列に「結果」列を追加する

shtData2.Cells(1, lastCol + 1) = "結果"

'⑫「データ①」シートと「データ②」シートを比較する

For i = 2 To lastRow1

blnExist = False

For j = 2 To lastRow2

blnSame = True

For k = 1 To UBound(varKey)

If shtData1.Cells(i, varKey(k, 1)) <> shtData2.Cells(j, varKey(k, 1)) Then

blnSame = False

Exit For

End If

If blnSame = True Then

blnExist = True

For m = 1 To UBound(varNoKey)

If shtData1.Cells(i, varNoKey(m)) <> shtData2.Cells(j, varNoKey(m)) Then

blnSame = False

Exit For

End If

If blnSame = True Then

shtData1.Cells(i, lastCol + 1) = "変更なし"

shtData2.Cells(j, lastCol + 1) = "変更なし"

Else

shtData1.Cells(i, lastCol + 1) = "変更あり"

shtData2.Cells(j, lastCol + 1) = "変更あり"

End If

Exit For

End If

If blnExist = False Then

shtData1.Cells(i, lastCol + 1) = "削除"

End If

'⑬「データ②」シートの追加された行の結果を記入する

For i = 1 To lastRow2

If shtData2.Cells(i, lastCol + 1) = "" Then

shtData2.Cells(i, lastCol + 1) = "追加"

End If

MsgBox "完了"

End Sub

３．2つのデータを比較する機能の使い方

それでは、2つのデータを比較する方法を説明します。

マクロボタンをクリックする

まず、「メイン」シートのA2セル以降に、「キーとなる列」を入力します。

次に、「開発」タブの「マクロ」をクリックします。

実行ボタンをクリックする

先程作成した自作関数「MainProc」が一覧に表示されていますので、選択します。

次に、「実行」ボタンをクリックしてデータ比較を開始します。

少し待つと、データ比較結果が、「データ①」シートと「データ②」シートに追加されます。

それでは、「データ①」シートの結果を確認してみましょう。

データ①シートの比較結果

入力していたデータの左に、列が追加されます。

「結果」という列が追加され、各行に比較結果が入力されます。

「結果」に「変更なし」と入力されている行は、変更がなかったことを意味しています。

「結果」に「変更あり」と入力されている行は、変更があったことを意味します。

キーとなる列で指定した列の値が変更されています。

「結果」に「削除」と入力されている行は、削除されたことを意味します。

「データ①」シートに存在しているが、「データ②」シートに存在していないデータとなります。

次に、「データ②」シート結果を確認してみます。

データ②シートの比較結果

「結果」に「追加」と入力されている行は、追加されたことを意味します。

「データ①」シートに存在していないが、「データ②」シートに存在しているデータとなります。

どうですか、簡単でしょ。

もっと大量のデータでも、同じく簡単ですのでやってみてください。

４．2つのデータを比較するVBAプログラムの説明

それでは、VBAプログラムを説明します。

①「メイン」シートを変数に格納する

'①「メイン」シートを変数に格納する
Set shtMain = ThisWorkbook.Sheets("メイン")

1 2	'①「メイン」シートを変数に格納する Set shtMain = ThisWorkbook.Sheets("メイン")

ここでは、このあとの処理で使う「メイン」シートを変数に格納しています。

そうすることにより、入力するプログラムを短くすることができるからです。

何度も「ThisWorkbook.Sheets(“メイン”)」と入力するより、「shtMain」と入力するほうが少なくてすむからです。

②キーとなる列を配列に格納する

'②キーとなる列を配列に格納する
varKey = shtMain.Range(shtMain.Cells(2, 1), _
                       shtMain.Cells(shtMain.Cells(shtMain.Rows.Count, 1).End(xlUp).Row, 1))

'②キーとなる列を配列に格納する

varKey = shtMain.Range(shtMain.Cells(2, 1), _

shtMain.Cells(shtMain.Cells(shtMain.Rows.Count, 1).End(xlUp).Row, 1))

ここでは、キーとなる列に入力された値を配列に格納しています。

具体的には、1列目の2行目以降に入力されている値をすべて、配列に格納しています。

③「データ①」シートを変数に格納する

'③「データ①」シートを変数に格納する
Set shtData1 = ThisWorkbook.Sheets("データ①")

1 2	'③「データ①」シートを変数に格納する Set shtData1 = ThisWorkbook.Sheets("データ①")

ここでは、このあとの処理で使う「データ①」シートを変数に格納しています。

④「データ①」シートの最終行を取得する

'④「データ①」シートの最終行を取得する
lastRow1 = shtData1.Cells(shtData1.Rows.Count, 1).End(xlUp).Row

1 2	'④「データ①」シートの最終行を取得する lastRow1 = shtData1.Cells(shtData1.Rows.Count, 1).End(xlUp).Row

ここでは、「データ①」シートのデータが入力されている最終行を取得して、変数に格納しています。

最終行を求めておくことで、何行目まで比較するのかを判断するのに利用するからです。

⑤「データ①」シートの最終列を取得する

'⑤「データ①」シートの最終列を取得する
lastCol = shtData1.Cells(1, shtData1.Columns.Count).End(xlToLeft).Column

1 2	'⑤「データ①」シートの最終列を取得する lastCol = shtData1.Cells(1, shtData1.Columns.Count).End(xlToLeft).Column

ここでは、「データ①」シートの1行目に入力されている最大列を取得して、変数に格納しています。

この最大列は、「データ①」と「データ②」で同じになるので、「データ①」シートで取得しています。

⑥「データ②」シートを変数に格納する

'⑥「データ②」シートを変数に格納する
Set shtData2 = ThisWorkbook.Sheets("データ②")

1 2	'⑥「データ②」シートを変数に格納する Set shtData2 = ThisWorkbook.Sheets("データ②")

ここでは、このあとの処理で使う「データ②」シートを変数に格納しています。

⑦「データ②」シートの最終行を取得する

'⑦「データ②」シートの最終行を取得する
lastRow2 = shtData2.Cells(shtData2.Rows.Count, 1).End(xlUp).Row

1 2	'⑦「データ②」シートの最終行を取得する lastRow2 = shtData2.Cells(shtData2.Rows.Count, 1).End(xlUp).Row

ここでは、「データ②」シートのデータが入力されている最終行を取得して、変数に格納しています。

最終行を求めておくことで、何行目まで比較するのかを判断するのに利用するからです。

⑧キーではない列を格納する配列を初期化する

'⑧キーではない列を格納する配列を初期化する
ReDim varNoKey(0)

1 2	'⑧キーではない列を格納する配列を初期化する ReDim varNoKey(0)

ここでは、キーではない列（列番号）を格納する配列を初期化しています。

キーではない列は、2つのデータを比較して変更点を調べるのに利用します。

⑨キーではない列を配列に格納する

'⑨キーではない列を配列に格納する
For i = 1 To lastCol
    
    blnExist = False

    For j = 1 To UBound(varKey)
        If i = varKey(j, 1) Then
            blnExist = True
            Exit For
        End If
    Next
    
    If blnExist = False Then
        ReDim Preserve varNoKey(UBound(varNoKey) + 1)
        varNoKey(UBound(varNoKey)) = i
    End If
Next

'⑨キーではない列を配列に格納する

For i = 1 To lastCol

blnExist = False

For j = 1 To UBound(varKey)

If i = varKey(j, 1) Then

blnExist = True

Exit For

End If

If blnExist = False Then

ReDim Preserve varNoKey(UBound(varNoKey) + 1)

varNoKey(UBound(varNoKey)) = i

End If

ここでは、「メイン」シートに入力されている「キーとなる列」の値を利用して、「キーではない列」を探しています。

「キーではない列」（列番号）は、配列に格納しています。

⑩「データ①」シートの最終列に「結果」列を追加する

'⑩「データ①」シートの最終列に「結果」列を追加する
shtData1.Cells(1, lastCol + 1) = "結果"

1 2	'⑩「データ①」シートの最終列に「結果」列を追加する shtData1.Cells(1, lastCol + 1) = "結果"

ここでは、「データ①」シートの最終列に「結果」列を追加しています。

この「結果」列に、データ比較結果をあとで、入力していきます。

⑪「データ②」シートの最終列に「結果」列を追加する

'⑪「データ②」シートの最終列に「結果」列を追加する
shtData2.Cells(1, lastCol + 1) = "結果"

1 2	'⑪「データ②」シートの最終列に「結果」列を追加する shtData2.Cells(1, lastCol + 1) = "結果"

ここでは、「データ②」シートの最終列に「結果」列を追加しています。

この「結果」列に、データ比較結果をあとで、入力していきます。

⑫「データ①」シートと「データ②」シートを比較する

'⑫「データ①」シートと「データ②」シートを比較する
For i = 2 To lastRow1

    blnExist = False

    For j = 2 To lastRow2
    
        blnSame = True
        
        For k = 1 To UBound(varKey)
            If shtData1.Cells(i, varKey(k, 1)) <> shtData2.Cells(j, varKey(k, 1)) Then
                blnSame = False
                Exit For
            End If
        Next
        
        If blnSame = True Then
            blnExist = True
            
            For m = 1 To UBound(varNoKey)
                If shtData1.Cells(i, varNoKey(m)) <> shtData2.Cells(j, varNoKey(m)) Then
                    blnSame = False
                    Exit For
                End If
            Next
            
            If blnSame = True Then
                shtData1.Cells(i, lastCol + 1) = "変更なし"
                shtData2.Cells(j, lastCol + 1) = "変更なし"
            Else
                shtData1.Cells(i, lastCol + 1) = "変更あり"
                shtData2.Cells(j, lastCol + 1) = "変更あり"
            End If
            
            Exit For
        End If
    Next
    
    If blnExist = False Then
        shtData1.Cells(i, lastCol + 1) = "削除"
    End If
Next

'⑫「データ①」シートと「データ②」シートを比較する

For i = 2 To lastRow1

blnExist = False

For j = 2 To lastRow2

blnSame = True

For k = 1 To UBound(varKey)

If shtData1.Cells(i, varKey(k, 1)) <> shtData2.Cells(j, varKey(k, 1)) Then

blnSame = False

Exit For

End If

If blnSame = True Then

blnExist = True

For m = 1 To UBound(varNoKey)

If shtData1.Cells(i, varNoKey(m)) <> shtData2.Cells(j, varNoKey(m)) Then

blnSame = False

Exit For

End If

If blnSame = True Then

shtData1.Cells(i, lastCol + 1) = "変更なし"

shtData2.Cells(j, lastCol + 1) = "変更なし"

Else

shtData1.Cells(i, lastCol + 1) = "変更あり"

shtData2.Cells(j, lastCol + 1) = "変更あり"

End If

Exit For

End If

If blnExist = False Then

shtData1.Cells(i, lastCol + 1) = "削除"

End If

ここでは、「データ①」シートと「データ②」シートに入力されているデータを比較し、比較結果を「結果」列に入力しています。

具体的には、「データ①」シートの「キーとなる列」で指定された列を、「データ②」シートに存在しているか探します。

存在している場合、「キーではない列」の値が異なるか確認します。

「キーではない列」の値が同じ場合、「データ①」シートと「データ②」シートのそれぞれの行の「結果」列に「変更なし」と入力します。

「キーではない列」の値が異なる場合、「データ①」シートと「データ②」シートのそれぞれの行の「結果」列に「変更あり」と入力します。

存在していない場合、「データ①」シートの「結果」列に「削除」と入力します。

⑬「データ②」シートの追加された行の結果を記入する

'⑬「データ②」シートの追加された行の結果を記入する
For i = 1 To lastRow2
    If shtData2.Cells(i, lastCol + 1) = "" Then
        shtData2.Cells(i, lastCol + 1) = "追加"
    End If
Next

'⑬「データ②」シートの追加された行の結果を記入する

For i = 1 To lastRow2