EXCEL VBA 文字列・数値データの突合・照合・比較・マッチング（テクニック）
EXCEL VBA 文字列・数値データの突合・照合・比較・マッチング（テクニック）
●はじめに
会社の業務をしている時、２つのEXCELデータがあり、このデータは、同じデータであるのか、異なるデータがあるのかなど、その様なマッチング処理を行う事があります。特に、経理・財務処理などで、請求・入金処理での入金消込処理などが該当します。照合するデータが大量にある場合は、目視で突合（マッチング）させるのは、正直に不可能です。ここでは、EXCEL VBAプログラムを使い、効率的に突合処理を行う方法を説明いたいます。
同じシートのセル同士の突合・参照・比較
●プログラム説明 （ForNextを使った場合） ※サンプル①
上記に「入金データ」と「請求データ」があります。このデータの請求書番号同士と請求金額・入金額を突合・比較して、入金結果に突合内容を表示させます。
「入金結果」（条件）
請求書番号及び請求額・入金額が一致した場合 ・・・〇
請求書番号が不一致（入金データが無い） ・・・未収
請求書番号が一致・入金額少ない場合 ・・・入金不足
請求書番号が一致・入金額が多い場合 ・・・過剰入金
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
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を使った場合） ※サンプル②
上記に「入金データ」と「請求データ」があります。このデータの請求書番号同士と請求金額・入金額を突合・比較して、入金結果に突合内容を表示させます。
「入金結果」（条件）
請求書番号及び請求額・入金額が一致した場合 ・・・〇
請求書番号が不一致（入金データが無い） ・・・未収
請求書番号が一致・入金額少ない場合 ・・・入金不足
請求書番号が一致・入金額が多い場合 ・・・過剰入金
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
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 |
●実行結果（実行後）：請求書番号と入金額・請求金額を比較し「入金結果」が表示されました。（画像クリックで拡大） ※実行結果は、サンプル①とサンプル②は、同じです。
別シートの複数セル同士の突合・参照・比較
●プログラム説明 ※サンプル③ （クリックして拡大表示）
今回のプログラムは、別のシートに「請求データ」・「入金データ」・「入金結果」があります。この「請求データ」の請求書番号と「入金データ」の請求書番号データを突合して、請求データを元に入金データを突合して、「入金結果」に突合・比較内容を表示させます。
「入金結果」（条件）
請求書番号及び請求額・入金額が一致した場合 ・・・〇
請求書番号が不一致（入金データが無い） ・・・未収
請求書番号が一致・入金額少ない場合 ・・・入金不足
請求書番号が一致・入金額が多い場合 ・・・過剰入金
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
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に関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。