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

2021/03/04

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

比較したいデータを2つのシートにそれぞれ入力し、比較元シートと比較先シートを指定すれば差分行を抽出できます。

1行ずつを比較するので、全く同じ行が存在しない場合、差分として抽出します。

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

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

それでは、EXCELで2つのデータを比較して、差分を抽出する機能を作成していきましょう。

困っている女性

EXCELに入力されている2つのデータの差分を抽出したいの・・・。

困っている女性

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

それなら、VBAを使えば2つのデータを比較して、差分行を簡単に抽出できるよ。

解決する男性

困っている女性

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

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

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

今回の記事と合わせて、読んでみてください。

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

１．2つのデータの差分を抽出するために必要なシートを作成する

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

メインシートを追加する

「メイン」という名前のシートを作成します。
1行目に「比較元シート」、「比較先シート」のタイトル行を作成します。
2行目は「比較元シート」、「比較先シート」の名前を入力する行となります。

「比較元シート」、「比較先シート」には、準備したシートの名前を入力します。

比較したいシートを2つ作成する

比較したいシートその１

比較したいシートの1つ目を作成します。

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

比較したいシートその２

比較したいシートの2つ目を作成します。

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

「差分」シートを作成する

差分シートを追加する

2つのシートを比較して、抽出した差分を書き込むシートを作成します。

「差分」という名前のシートを作成します。
中身は特に何も入力する必要はありません。

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

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

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

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

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

Public Sub MainProc()
    Dim shtMain As Worksheet
    Dim motoName As String
    Dim sakiName As String
    Dim shtMoto As Worksheet
    Dim shtSaki As Worksheet
    Dim shtSabun As Worksheet
    Dim lastRowMoto As Long
    Dim lastRowSaki As Long
    Dim lastCol As Long
    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim blnSame As Boolean
    Dim blnExist As Boolean
    Dim nowRow As Long
    
    '①「メイン」シートを変数に格納する
    Set shtMain = ThisWorkbook.Sheets("メイン")
    
    '②比較元シート名を変数に格納する
    motoName = shtMain.Range("A2")
    
    '③比較先シート名を変数に格納する
    sakiName = shtMain.Range("B2")
    
    '④比較元シートを変数に格納する
    Set shtMoto = ThisWorkbook.Sheets(motoName)
            
    '⑤比較先シートを変数に格納する
    Set shtSaki = ThisWorkbook.Sheets(sakiName)
    
    '⑥差分シートを変数に格納する
    Set shtSabun = ThisWorkbook.Sheets("差分")
    
    '⑦比較元シートの最終行を取得する
    lastRowMoto = shtMoto.Cells(shtMoto.Rows.Count, 1).End(xlUp).Row
    
    '⑧比較元シートの最終列を取得する
    lastCol = shtMoto.Cells(1, shtMoto.Columns.Count).End(xlToLeft).Column
    
    '⑨比較先シートの最終行を取得する
    lastRowSaki = shtSaki.Cells(shtSaki.Rows.Count, 1).End(xlUp).Row
    
    '⑩差分シートをクリアする
    shtSabun.Cells.Clear
    
    '⑪比較元シートのヘッダー行を差分シートにコピーする
    shtMoto.Range(shtMoto.Cells(1, 1), shtMoto.Cells(1, lastCol)).Copy (shtSabun.Cells(1, 1))
    
    nowRow = 1
    
    '⑫比較元シートと比較先シートを比較し、差分行を差分シートにコピーする
    For i = 2 To lastRowMoto
    
        blnExist = False
    
        For j = 2 To lastRowSaki
        
            blnSame = True
            
            For k = 1 To lastCol
                If shtMoto.Cells(i, k) <> shtSaki.Cells(j, k) Then
                    blnSame = False
                    Exit For
                End If
            Next
            
            If blnSame = True Then
                blnExist = True
                Exit For
            End If
        Next
        
        If blnExist = False Then
            nowRow = nowRow + 1
            shtMoto.Range(shtMoto.Cells(i, 1), shtMoto.Cells(i, lastCol)).Copy (shtSabun.Cells(nowRow, 1))
        End If
    Next
    
    MsgBox "完了"
End Sub

Public Sub MainProc()

Dim shtMain As Worksheet

Dim motoName As String

Dim sakiName As String

Dim shtMoto As Worksheet

Dim shtSaki As Worksheet

Dim shtSabun As Worksheet

Dim lastRowMoto As Long

Dim lastRowSaki As Long

Dim lastCol As Long

Dim i As Long

Dim j As Long

Dim k As Long

Dim blnSame As Boolean

Dim blnExist As Boolean

Dim nowRow As Long

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

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

'②比較元シート名を変数に格納する

motoName = shtMain.Range("A2")

'③比較先シート名を変数に格納する

sakiName = shtMain.Range("B2")

'④比較元シートを変数に格納する

Set shtMoto = ThisWorkbook.Sheets(motoName)

'⑤比較先シートを変数に格納する

Set shtSaki = ThisWorkbook.Sheets(sakiName)

'⑥差分シートを変数に格納する

Set shtSabun = ThisWorkbook.Sheets("差分")

'⑦比較元シートの最終行を取得する

lastRowMoto = shtMoto.Cells(shtMoto.Rows.Count, 1).End(xlUp).Row

'⑧比較元シートの最終列を取得する

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

'⑨比較先シートの最終行を取得する

lastRowSaki = shtSaki.Cells(shtSaki.Rows.Count, 1).End(xlUp).Row

'⑩差分シートをクリアする

shtSabun.Cells.Clear

'⑪比較元シートのヘッダー行を差分シートにコピーする

shtMoto.Range(shtMoto.Cells(1, 1), shtMoto.Cells(1, lastCol)).Copy (shtSabun.Cells(1, 1))

nowRow = 1

'⑫比較元シートと比較先シートを比較し、差分行を差分シートにコピーする

For i = 2 To lastRowMoto

blnExist = False

For j = 2 To lastRowSaki

blnSame = True

For k = 1 To lastCol

If shtMoto.Cells(i, k) <> shtSaki.Cells(j, k) Then

blnSame = False

Exit For

End If

If blnSame = True Then

blnExist = True

Exit For

End If

If blnExist = False Then

nowRow = nowRow + 1

shtMoto.Range(shtMoto.Cells(i, 1), shtMoto.Cells(i, lastCol)).Copy (shtSabun.Cells(nowRow, 1))

End If

MsgBox "完了"

End Sub

３．2つのデータを比較して差分を抽出する機能の使い方

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

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

まず、「メイン」シートのA2セルに「比較元シート名」を入力します。

上図では、「データ①」を入力しています。

そして、B2セルに「比較先シート名」を入力します。

上図では、「データ②」を入力しています。

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

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

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

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

少し待つと、データ比較して抽出した差分が「差分」シートに追加されます。

それでは、「差分」シートに追加された差分データを確認してみましょう。

差分抽出結果その１

比較元シート「データ①」に存在しており、比較先シート「データ②」に存在しない行が、差分シートに追加されています。

次は、逆にして実行してみます。

比較元シートに「データ②」、比較先シートに「データ①」を入力して、実行します。

比較先と比較元を変更

差分の結果は、以下です。

差分結果その2

比較元シート「データ②」に存在しており、比較先シート「データ①」に存在しない行が、差分シートに追加されています。

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

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

４．2つのデータを比較して差分を抽出するVBAプログラムの説明

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

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

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

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

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

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

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

②比較元シート名を変数に格納する

'②比較元シート名を変数に格納する
motoName = shtMain.Range("A2")

1 2	'②比較元シート名を変数に格納する motoName = shtMain.Range("A2")

ここでは、A2セルに入力されている「比較元シート名」を変数に格納しています。

③比較先シート名を変数に格納する

'③比較先シート名を変数に格納する
sakiName = shtMain.Range("B2")

1 2	'③比較先シート名を変数に格納する sakiName = shtMain.Range("B2")

ここでは、B2セルに入力されている「比較先シート名」を変数に格納しています。

④比較元シートを変数に格納する

'④比較元シートを変数に格納する
Set shtMoto = ThisWorkbook.Sheets(motoName)

1 2	'④比較元シートを変数に格納する Set shtMoto = ThisWorkbook.Sheets(motoName)

ここでは、比較元シートを変数に格納しています。

⑤比較先シートを変数に格納する

'⑤比較先シートを変数に格納する
Set shtSaki = ThisWorkbook.Sheets(sakiName)

1 2	'⑤比較先シートを変数に格納する Set shtSaki = ThisWorkbook.Sheets(sakiName)

ここでは、比較先シートを変数に格納しています。

⑥差分シートを変数に格納する

'⑥差分シートを変数に格納する
Set shtSabun = ThisWorkbook.Sheets("差分")

1 2	'⑥差分シートを変数に格納する Set shtSabun = ThisWorkbook.Sheets("差分")

ここでは、差分シートを変数に格納しています。

⑦比較元シートの最終行を取得する

'⑦比較元シートの最終行を取得する
lastRowMoto = shtMoto.Cells(shtMoto.Rows.Count, 1).End(xlUp).Row

1 2	'⑦比較元シートの最終行を取得する lastRowMoto = shtMoto.Cells(shtMoto.Rows.Count, 1).End(xlUp).Row

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

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

⑧比較元シートの最終列を取得する

'⑧比較元シートの最終列を取得する
lastCol = shtMoto.Cells(1, shtMoto.Columns.Count).End(xlToLeft).Column

1 2	'⑧比較元シートの最終列を取得する lastCol = shtMoto.Cells(1, shtMoto.Columns.Count).End(xlToLeft).Column

ここでは、比較元シートの最終列を変数に格納しています。

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

⑨比較先シートの最終行を取得する

'⑨比較先シートの最終行を取得する
lastRowSaki = shtSaki.Cells(shtSaki.Rows.Count, 1).End(xlUp).Row

1 2	'⑨比較先シートの最終行を取得する lastRowSaki = shtSaki.Cells(shtSaki.Rows.Count, 1).End(xlUp).Row

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

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

⑩差分シートをクリアする

'⑩差分シートをクリアする
shtSabun.Cells.Clear

1 2	'⑩差分シートをクリアする shtSabun.Cells.Clear

ここでは、差分シートに入力されているデータをクリアしています。

何度実行しても良いように差分シートをクリアしています。

⑪比較元シートのヘッダー行を差分シートにコピーする

'⑪比較元シートのヘッダー行を差分シートにコピーする
shtMoto.Range(shtMoto.Cells(1, 1), shtMoto.Cells(1, lastCol)).Copy (shtSabun.Cells(1, 1))

1 2	'⑪比較元シートのヘッダー行を差分シートにコピーする shtMoto.Range(shtMoto.Cells(1, 1), shtMoto.Cells(1, lastCol)).Copy (shtSabun.Cells(1, 1))

ここでは、比較元シートのヘッダー行を差分シートの1行目にコピーしています。

⑫比較元シートと比較先シートを比較し、差分行を差分シートにコピーする

'⑫比較元シートと比較先シートを比較し、差分行を差分シートにコピーする
For i = 2 To lastRowMoto

    blnExist = False

    For j = 2 To lastRowSaki
    
        blnSame = True
        
        For k = 1 To lastCol
            If shtMoto.Cells(i, k) <> shtSaki.Cells(j, k) Then
                blnSame = False
                Exit For
            End If
        Next
        
        If blnSame = True Then
            blnExist = True
            Exit For
        End If
    Next
    
    If blnExist = False Then
        nowRow = nowRow + 1
        shtMoto.Range(shtMoto.Cells(i, 1), shtMoto.Cells(i, lastCol)).Copy (shtSabun.Cells(nowRow, 1))
    End If
Next

'⑫比較元シートと比較先シートを比較し、差分行を差分シートにコピーする

For i = 2 To lastRowMoto

blnExist = False

For j = 2 To lastRowSaki

blnSame = True

For k = 1 To lastCol

If shtMoto.Cells(i, k) <> shtSaki.Cells(j, k) Then

blnSame = False

Exit For

End If

If blnSame = True Then

blnExist = True

Exit For

End If

If blnExist = False Then

nowRow = nowRow + 1

shtMoto.Range(shtMoto.Cells(i, 1), shtMoto.Cells(i, lastCol)).Copy (shtSabun.Cells(nowRow, 1))

End If

ここでは、比較元シートと比較先シートを比較し、差分行を差分シートにコピーしています。

具体的には、比較元シートに存在しているが、比較先シートに存在しない行を差分シートにコピーします。

それでは、比較したいデータを2つ用意して、差分データを抽出してみましょう。

以上です。

6 COMMENTS

たなかしんすけ 2021-07-08

ネットで検索したところ上記記事を見つけて試させて頂いたのですが、
思うように差分を出力が出来ず藁にもすがる思いで連絡させて頂きました。
業務で利用しているデータなので実ファイルを提供できないのですが、
比較元に4134行のデータ、比較先に3658行のデータで差分を出力させたところ
1322件のデータがヒットしました。そこで4134件のデータに1322件のデータを
追加したところ複数の重複したデータが存在していました。
そこで重複したデータの片方（1行）を比較元、データのもう片方（１行）を比較先に
入力して差分を出力させたところ差分は、0件となりました。
これだけの情報では難しいかもしれませんが考えられる原因がわかれば
教えて頂けると大変助かります。以上、宜しくお願い致します。

返信する