EXCEL VBA 文字列・数値データの突合・照合・比較・マッチング（テクニック）

●はじめに

会社の業務をしている時、２つのEXCELデータがあり、このデータは、同じデータであるのか、異なるデータがあるのかなど、その様なマッチング処理を行う事があります。特に、経理・財務処理などで、請求・入金処理での入金消込処理などが該当します。照合するデータが大量にある場合は、目視で突合（マッチング）させるのは、正直に不可能です。ここでは、EXCEL VBAプログラムを使い、効率的に突合処理を行う方法を説明いたいます。

同じシートのセル同士の突合・参照・比較

●プログラム説明　（ForNextを使った場合）　※サンプル①

上記に「入金データ」と「請求データ」があります。このデータの請求書番号同士と請求金額・入金額を突合・比較して、入金結果に突合内容を表示させます。
「入金結果」（条件）
請求書番号及び請求額・入金額が一致した場合　・・・〇
請求書番号が不一致（入金データが無い）　　　・・・未収
請求書番号が一致・入金額少ない場合　　　　　・・・入金不足
請求書番号が一致・入金額が多い場合　　　　　・・・過剰入金

Sub tottsugo01()
        
    Dim L, I, lRow, mRow As Long
    Dim KIN As Single

    lRow = Cells(Rows.Count, "I").End(xlUp).Row  '請求データの最終行
    mRow = Cells(Rows.Count, "A").End(xlUp).Row  '入金データの最終行
    
    For L = 2 To lRow  '請求データをループ
    
        Cells(L, "K") = "未収"  '入金データが無い場合　未収とする
    
        For I = 2 To mRow  '入金データをループ
        
            If Cells(L, "I") = Cells(I, "A") Then      '請求データと入金データの請求書番号を確認
                    KIN = Cells(L, "J") - Cells(I, "F")  '請求書番号が一致した場合、請求金額と入金額の差額を計算する。
                                   
                    Select Case KIN
                    
                      Case Is = 0
                            Cells(L, "K") = "〇"  '差額の計算結果が０の場合は、”〇”
                      Case Is < 0
                            Cells(L, "K") = KIN & "入金不足"  '差額計算で請求額に対して入金額が不足している場合は、不足金額と”入金不足”を表示
                      Case Is > 0
                            Cells(L, "K") = KIN & "過剰入金"  '差額計算で請求額に対して
                    End Select
                    
                    Exit For 'I (入金データのループから抜ける)
            End If                
        Next I    
    Next L    
End Sub

●実行結果（実行後）：請求書番号と入金額・請求金額を比較し「入金結果」が表示されました。（画像クリックにて拡大）

同じシートの複数セル同士の突合・参照・比較②（Matchで比較）

●プログラム説明　（Matchを使った場合）　※サンプル②

 
Sub tottsugo02()
    
    Dim L, lRow, mRow, xRow As Long
    Dim KIN As Single

    lRow = Cells(Rows.Count, "I").End(xlUp).Row  '請求データの最終行
    mRow = Cells(Rows.Count, "A").End(xlUp).Row  '入金データの最終行
    
    
    For L = 2 To lRow
    
        xRow = 0
        
        On Error Resume Next　’エラーが発生しても続行プログラム実行を続行させる。
        xRow = WorksheetFunction.Match(Cells(L, "I"), Range("A2:A" & mRow), 0) 'I列の請求書番号からA列にある請求書番号と一致する。
        On Error GoTo 0　’比較データが無い場合に、エラーでも続行させる。
        
            If xRow <> 0 Then
                    KIN = Cells(L, "J") - Cells(xRow + 1, "F") '請求書番号が一致した場合、請求金額と入金額の差額を計算する。
                                   
                    Select Case KIN
                    
                      Case Is = 0
                            Cells(L, "K") = "〇"  '差額の計算結果が０の場合は、”〇”
                      Case Is < 0
                            Cells(L, "K") = KIN & "入金不足"  '差額計算で請求額に対して入金額が不足している場合は、不足金額と”入金不足”を表示
                      Case Is > 0
                            Cells(L, "K") = KIN & "過剰入金"  '差額計算で請求額に対して
                    End Select
                    
                   Else
                        Cells(L, "K") = "未収"  '入金データが無い場合　未収とする
            End If
            
        Next L

End Sub

●実行結果（実行後）：請求書番号と入金額・請求金額を比較し「入金結果」が表示されました。（画像クリックで拡大）　※実行結果は、サンプル①とサンプル②は、同じです。

別シートの複数セル同士の突合・参照・比較

●プログラム説明　※サンプル③　（クリックして拡大表示）

今回のプログラムは、別のシートに「請求データ」・「入金データ」・「入金結果」があります。この「請求データ」の請求書番号と「入金データ」の請求書番号データを突合して、請求データを元に入金データを突合して、「入金結果」に突合・比較内容を表示させます。

「入金結果」（条件）
請求書番号及び請求額・入金額が一致した場合　・・・〇
請求書番号が不一致（入金データが無い）　　　・・・未収
請求書番号が一致・入金額少ない場合　　　　　・・・入金不足
請求書番号が一致・入金額が多い場合　　　　　・・・過剰入金

Sub tottsugo03()
    
    Dim ws01, ws02, ws03 As Worksheet
    Dim L, lRow, mRow, xRow As Long
    Dim KIN As Single

    Set ws01 = Worksheets("請求データ")
    Set ws02 = Worksheets("入金データ")
    Set ws03 = Worksheets("入金結果")

    lRow = ws01.Cells(Rows.Count, "A").End(xlUp).Row  'シート：「請求データ」A列の最終行
    mRow = ws02.Cells(Rows.Count, "A").End(xlUp).Row  'シート：「入金データ」A列の最終行
    
    nRow = 2 '入金結果の初期設定（2行名）
    
    For L = 2 To lRow  '入金データの２～最終行まで繰り返す。
    
    
        xRow = 0
        On Error Resume Next  'エラーが発生しても続行プログラム実行を続行させる。
        xRow = WorksheetFunction.Match(ws01.Cells(L, "A"), ws02.Range("A2:A" & mRow), 0) 'I列の請求書番号からA列にある請求書番号と一致する。
        On Error GoTo 0  '比較データが無い場合に、エラーでも続行させる。
                    
        ws03.Cells(L, "A") = ws01.Cells(L, "C")   '取引番号
        ws03.Cells(L, "B") = ws01.Cells(L, "D")   '入金額
        ws03.Cells(L, "C") = ws01.Cells(L, "E")   '振込人名
           
        If xRow <> 0 Then
                ws03.Cells(L, "D") = ws02.Cells(xRow + 1, "F") '入金額
                KIN = ws02.Cells(xRow + 1, "F") - ws01.Cells(L, "D")  '請求書番号が一致した場合、請求金額と入金額の差額を計算する。
                                                                                   
                Select Case KIN
                
                  Case Is = 0
                        ws03.Cells(L, "E") = "〇"  '差額の計算結果が０の場合は、”〇”
                  Case Is < 0
                        ws03.Cells(L, "E") = KIN & "円：入金不足"  '差額計算で請求額に対して入金額が不足している場合は、不足金額と”入金不足”を表示
                  Case Is > 0
                        ws03.Cells(L, "E") = KIN & "円：過剰入金"  '差額計算で請求額に対して
                End Select
                
            Else
                    ws03.Cells(L, "E") = "未収"  '入金データが無い場合　未収とする
                    
        End If
              
        Next L

End Sub

●実行結果　　※シート「請求データ」と「入金データ」を突合・比較した結果が、「入金結果」シートに表示されました。（画像クリックで拡大）

最後まで、ご覧いただきまして誠に有難うございました。
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。

AKIRA