教えて下さい。２つエクセルファイルをマッチング（突合）する方法。

質問

５月から新しい職場で支払処理をしています。VBAの勉強を始めましたが分からない事だらけなのでお詳しい方に教えて頂ければと思います。

支払の元になっている支払データと、それを元に自分で支出システムに入力したデータを今は印字して読み合わせで確認しているのですが、それをシステム化したいと考えています。

　マッチングしたい元のデータ(エクセル形式)にはA～AU列まであります。
支出システムに入力したものは後からエクセル形式で取り出せるのですがそちらの支出データはA～DP列まであります。

　その中でマッチングを確認したい箇所は以下の３箇所です。

元データ　M列の支払金額(数値)　<=>　支出データ　I列の支払額

元データ　Q列の貸主名(文字列)　<=>　支出データ　S列の債権者

元データAR列の支払先(数値)　<=>　支出データ　F列の支払先コード

　またマッチング後、違っていた箇所は支払先の数値が別のセルに表示されるか、違っていた箇所だけセルの色が変わって表示される様に分かりやすく確認できる様にしたいと考えています。

　このような場合はどのような関数(VBA？)を使えばよいでしょうか？
　VBAと関数の知識は多少持っているのですが、応用が利かず
行き詰ってしまい相談させて頂きました。　
分かりにくい文章で申し訳ありませんが、どなたかご教授頂けると
本当に助かります。

回答No.4

zap35 · ベストアンサー

＃０１です。＃０３にＵＰしたマクロに不適切な部分がありましたので差し替えます

Sub Macro1()
Const wbM As String = "元データ.xls"
Const wsM As String = "支払いシート"
Const wbS As String = "支出データ.xls"
Const wsS As String = "支出シート"
Dim idx As Long
Dim psw As Integer
Dim svAdrs As String
Dim trg As Range
　Application.ScreenUpdating = False
　With Workbooks(wbS).Sheets(wsS)
　　For idx = 2 To .Range("F65536").End(xlUp).Row
　　　psw = 0
　　　Set trg = Workbooks(wbM).Sheets(wsM).Cells(1, "Q").EntireColumn _
　　　　　.Find(.Cells(idx, "S").Value, LookIn:=xlValues)
　　　If Not trg Is Nothing Then
　　　　svAdrs = trg.Address
　　　　psw = 1
　　　　Do
　　　　　If .Cells(idx, "F") = Workbooks(wbM).Sheets(wsM).Cells(trg.Row, "AR") Then
　　　　　　psw = 2
　　　　　　If .Cells(idx, "I") = Workbooks(wbM).Sheets(wsM).Cells(trg.Row, "M") Then
　　　　　　　psw = 3
　　　　　　End If
　　　　　End If
　　　　　Set trg = Workbooks(wbM).Sheets(wsM).Cells(1, "Q").EntireColumn _
　　　　　　　.FindNext(trg)
　　　　Loop Until trg.Address = svAdrs
　　　End If
　　　Select Case psw
　　　　Case Is = 0
　　　　　.Range(.Cells(idx, "A"), .Cells(idx, "DP")).Interior.ColorIndex = 3
　　　　Case Is = 1
　　　　　.Range(.Cells(idx, "A"), .Cells(idx, "DP")).Interior.ColorIndex = 6
　　　　Case Is = 2
　　　　　.Range(.Cells(idx, "A"), .Cells(idx, "DP")).Interior.ColorIndex = 8
　　　　Case Else
　　　　　.Range(.Cells(idx, "A"), .Cells(idx, "DP")).Interior.ColorIndex = xlNone
　　　End Select
　　Next idx
　End With
　Application.ScreenUpdating = True
End Sub

お礼

しばらくお返事が出来ずに大変失礼いたしました。作って頂いたマクロですが、ちょっとうまく動作せずエラーが出てしまいました。
ですが、自分でも考えて出来るところまでやってみたいと思います。
本当にありがとうございました。

補足

自分で実際に色々とやってみたところ1つ追加したい部分がありまして、もしよかったら教えて下さい。
2行目から500行目くらいに(A列～DP列まで)データが入っているのですが、2行連続などで重複したデータが入っている部分があったら、それを確認できる様にしたいのですが何か良い関数などありますでしょうか？
COUNTIF関数で試してみたのですが、一行丸ごと重複したデータを表示する事が出来ませんでした。度々の質問で申し訳ないです。

回答No.3

zap35

＞そのまま支出データの方で間違っている箇所を確認、変更したいと考えています
支出データのＤＱ列に同じようにSUMPRODUCT関数を入力すれば良いと思います。その値によって行の背景色を条件付き書式で設定することもできます。
=SUMPRODUCT(([支払いデータ.xls]支払いシート!$AR$2:$AR$1000=F2)*([支払いデータ.xls]支払いシート!$M$2:$M$1000=I2)*([支払いデータ.xls]支払いシート!$Q$2:$Q$1000=S2))

マクロならこのような感じになるかと思いますが、実際のデータを見ていないのでちゃんと動く自信はあまりありません。だめな場合はSUMPRODCT関数でチェックして下さい
（自分なりに動作確認はしましたが、想定外のデータがあるのではないかと思います）

マクロはALT＋F11でVBE画面を開き、左上のVBA Projectで「支出シート」のシート名を右クリックし「挿入」→「標準モジュール」で表示される画面に貼り付けて下さい。

マクロの実行は両方のブックを開き、「支出シート」アクティブにした状態でALT＋F8でマクロ一覧を開き、マクロ名を選択して「実行」ボタンです。

また２行目～４行目のブック名、シート名は実際の名前に修正が必要です

Sub Macro1()
Const wbM As String = "元データ.xls"
Const wsM As String = "支払いシート"
Const wbS As String = "支出データ.xls"
Const wsS As String = "支出シート"
Dim idx As Long
Dim psw As Integer
Dim svAdrs As String
Dim trg As Range
　Application.ScreenUpdating = False
　With Workbooks(wbS).Sheets(wsS)
　　For idx = 2 To .Range("F65536").End(xlUp).Row
　　　psw = 0
　　　Set trg = Workbooks(wbM).Sheets(wsM).Cells(1, "Q").EntireColumn _
　　　　　.Find(.Cells(idx, "S").Value, LookIn:=xlValues)
　　　If Not trg Is Nothing Then
　　　　svAdrs = trg.Address
　　　　psw = 1
　　　　If .Cells(idx, "F") = Workbooks(wbM).Sheets(wsM).Cells(trg.Row, "AR") Then
　　　　　psw = 2
　　　　　If .Cells(idx, "I") = Workbooks(wbM).Sheets(wsM).Cells(trg.Row, "M") Then
　　　　　　psw = 3
　　　　　End If
　　　　End If
　　　　Set trg = Workbooks(wbM).Sheets(wsM).Cells(1, "Q").EntireColumn _
　　　　　　.FindNext(trg)
　　　　Do Until trg.Address = svAdrs
　　　　　If .Cells(idx, "F") = Workbooks(wbM).Sheets(wbM).Cells(trg.Row, "AR") Then
　　　　　　psw = 2
　　　　　　If .Cells(idx, "I") = Workbooks(wbM).Sheets(wbM).Cells(trg.Row, "M") Then
　　　　　　　psw = 3
　　　　　　End If
　　　　　End If
　　　　Loop
　　　End If
　　　Select Case psw
　　　　Case Is = 0
　　　　　.Range(.Cells(idx, "A"), .Cells(idx, "DP")).Interior.ColorIndex = 3
　　　　Case Is = 1
　　　　　.Range(.Cells(idx, "A"), .Cells(idx, "DP")).Interior.ColorIndex = 6
　　　　Case Is = 2
　　　　　.Range(.Cells(idx, "A"), .Cells(idx, "DP")).Interior.ColorIndex = 8
　　　　Case Else
　　　　　.Range(.Cells(idx, "A"), .Cells(idx, "DP")).Interior.ColorIndex = xlNone
　　　End Select
　　Next idx
　End With
　Application.ScreenUpdating = True
End Sub

回答No.2

zap35

＃０１です。補足ありがとうございました
元データのM,Q,ARと完全一致する支出データがあるかないかだけのチェックでよければSUMPRODUCT関数で可能です。まずその方法を説明します。

元データのＡＶ２セルに
=SUMPRODUCT(([支出データ.xls]支出シート!$I$2:$I$1000=M2)*([支出データ.xls]支出シート!$S$2:$S$1000=Q2)*([支出データ.xls]支出シート!$F$2:$F$1000=AR2))
と入力し下方向にコピーします。（BOOK名、シート名は修正してください。また両方のシートを開いておいて下さい）

すると３項目が一致すると結果が正の数値となり、３項目ともに一致するデータがない場合は０になります。
結果が１なら合致する行は支出シートに１行のみ。２なら同じデータが２つ重複して存在することになります。

個人的にはこれだけでもかなり作業が楽になるとは思うのですが、もっときめ細かなチェックをお望みの場合は条件を再度補足お願いします。

例）
1)支出シートに貸し主が存在しない場合はＱ列のセルの背景色を赤色にする
2)支出シートに貸し主は存在しているが、支払先コードがないときはＡＲ列のセルの背景色を黄色にする
3)支出シートに貸し主、支出先コードが一致して支払額が合致しない場合はＭ列のセルの背景色を青色にする
4)支出シートに同じ貸し主、支払先コードのデータが複数存在するときはどうする、こうする…　→VBAではこのパターンのCheckは処理的に面倒かも？

補足

早速の回答ありがとうございます。
感動しました。すばらしいですね。
自分ももっと勉強したくなりました。

　甘えてしまって申し訳ないのですが、教えて頂いた数式を
少し変更して支出データのDQ列に結果を反映するようにいたしました。
=SUMPRODUCT(([元データ.xls]元データ!$M$2:$M$1000=I2)*([元データ.xls]元データ!$Q$2:$Q$1000=S2)*([元データ.xls]元データ!$AR$2:$AR$1000=F2))
出来れば、そのまま支出データの方で間違っている箇所を確認、変更したいと考えています。
例に挙げて下さった
1)支出シートに貸し主が存在しない場合はＱ列のセルの背景色を赤色にする
2)支出シートに貸し主は存在しているが、支払先コードがないときはＡＲ列のセルの背景色を黄色にする
3)支出シートに貸し主、支出先コードが一致して支払額が合致しない場合はＭ列のセルの背景色を青色にする

でかつ、その間違っている行を一行全部背景色を同じ色に変更することは可能でしょうか？条件付書式などでは全然ダメでした。
やっぱりVBAですかね？

回答No.1

zap35

補足要求です
目視で突合するなら不要かもしれませんが、VBAで機械的に突合するなら例えば伝票番号などユニークなマッチングキーが必要です。この場合マッチングキーは何ですか？

支払先コードがそうだというなら、同じ支払先コードが複数ある時は、どちらの行と突合するのかをどのように判断すればよいですか？

また
＞元データ　M列の支払金額(数値)　<=>　支出データ　I列の支払額
＞元データ　Q列の貸主名(文字列)　<=>　支出データ　S列の債権者
＞元データAR列の支払先(数値)　<=>　支出データ　F列の支払先コード
と、左右で表現が微妙に異なっていますが、これらは全く同じ値が入るのですか？

質問内容だけでは情報が欠けているように思います。

補足

ご回答ありがとうございます。
言葉足らずで申し訳ありませんでした。

まず、元データと支出データには表現が違っていますが
それぞれ同じ値が入ります。

そして問題のマッチングキーですが、ご指摘の通り支払先コードが同じになってしまっている箇所が600件程のデータなのですが数箇所ありました。
しかし探したのですが他に個別の共通する番号が無く、元データと支出データでは順番が入れ替わってしまうので新しく番号を振り直す事も難しいです。
これでは機械的なマッチングは無理でしょうか？
もし何か良い考えがございましたらお教え願います。
ご面倒をおかけしてすいません。