ＶＢＡによりマッチング操作

ＶＢＡによりマッチング操作

基本的な知識がないので、まずはそこが課題ですが、ＶＢＡのコードとしてはどんな形になりますか。
まったくの初心者です

(1)２つのファイルがあり共通するキーを照合することにより得られたマスターファイルから得られた係数をもとにトランザイクションデータの各列の係数と掛け算をして、その結果をトランザクションデータのＡ列からＧ列とともに別のファイル（シート１）に書き込む。
(2)アンマッチデータは結果の書き込まれたファイルのシート２にトランザクションデータの内容をそのまま書き込むというものです。
(3)トランザクションデータのデータ数は未知ですが、列数は定まっています。

やりことのあらましは以上のようなことです。

No.3 ベストアンサー 回答者： end-u 回答日時： 2010/07/21 11:14

読み解くほうがちょっと苦労しますね。

u,FなのかＵ,Ｅなのか、はそちらで修正してもらえばいい事なので
あまりツっこみませんが
マッチング処理というより、やはりフィルタ処理の案件と言えそう。

・TRN.xlsのSheets("販売予測")に作業列を追加。
・MST.xlsのSheets("Ｇ数量")のM列値をVLOOKUP関数で引っ張る。
・それをAdvancedFilterメソッドの抽出条件とする。
・抽出後、MATCHデータについては作業列の値をコピー、乗算貼り付け。

勘違いしてなければ以上の流れで済むはず。

Ｇ数量.xlsに下記コードを置き、TRN.xls、MST.xlsを開いて実行。
'--------------------------------------------------------------------
Sub Macro2()
　　Dim ws As Worksheet '抽出先
　　Dim r　As Range　　 '元データ範囲
　　Dim n　As Long

　　With Workbooks("TRN.xls").Sheets("計算結果")
　　　　Set r = .Range("A1").CurrentRegion.Resize(, 64)
　　　　.Range("BL1").Value = "chk"
　　　　.Range("BL2").Resize(r.Rows.Count - 1).Formula = _
　　　　　　　　　　 "=IF(AND(A2=""u"",P2=""F"")," & _
　　　　　　　　　　 "VLOOKUP(C2,[MST.xls]Ｇ数量!$A:$M,13,0))"
　　　　.Range("BM2").Formula = "=ISNA(BL2)"
　　　　Set ws = ThisWorkbook.Sheets.Add
　　　　'UNMATCH
　　　　r.AdvancedFilter Action:=xlFilterCopy, _
　　　　　　　　　　　　 CriteriaRange:=.Range("BM1:BM2"), _
　　　　　　　　　　　　 CopyToRange:=ws.Range("A1"), _
　　　　　　　　　　　　 Unique:=False
　　　　.Range("BM2").Formula = "=ISNUMBER(BL2)"
　　　　Set ws = ThisWorkbook.Sheets.Add
　　　　'MATCH
　　　　r.AdvancedFilter Action:=xlFilterCopy, _
　　　　　　　　　　　　 CriteriaRange:=.Range("BM1:BM2"), _
　　　　　　　　　　　　 CopyToRange:=ws.Range("A1"), _
　　　　　　　　　　　　 Unique:=False
　　　　.Columns("BL:BM").ClearContents
　　End With

　　n = ws.Range("A1").CurrentRegion.Rows.Count - 1
　　If n > 0 Then
　　　　ws.Range("BL2").Resize(n).Copy
　　　　'[形式を選択して貼り付け]「乗算」
　　　　ws.Range("V2:BK2").Resize(n) _
　　　　　　　　.PasteSpecial Paste:=xlPasteValues, _
　　　　　　　　　　　　　　　Operation:=xlMultiply
　　　　Application.CutCopyMode = False
　　　　ws.Columns("BL").ClearContents
　　End If

　　Set r = Nothing
　　Set ws = Nothing
End Sub
'--------------------------------------------------------------------
[フィルタオプション]も「乗算での貼り付け」も
一般操作をマクロ記録できますからコーディングのヒントにしてください。
また、データ量が多ければ重くて使えない場合もあるかもしれません。
その時は工夫して改善してみてください。

この回答へのお礼

このコードをもとに一部を修正し完成させました。
データ量が少ないので処理時間は問題ではなかったです。コードを読み解いていくなかでよい勉強になりました。片手間でやっていますのでなかなか着手できなかったのですがわりとすんなり解決できました。
マッチングというよりフィルター処理ということがよく分かりました。ありがとうございました。

お礼日時：2010/07/28 23:18

No.4 回答者： end-u 回答日時： 2010/07/21 11:19

失礼。

>With Workbooks("TRN.xls").Sheets("計算結果")
With Workbooks("TRN.xls").Sheets("販売予測")
です...orz

No.2 回答者： end-u 回答日時： 2010/07/20 13:08

>ＶＢＡのコードとしてはどんな形になりますか。

いろんな手法があります。
一般機能の[フィルタオプション]を使うと比較的簡単かもしれません。

・[data.xls]Sheet1に抽出したい元データがある（トランザクションデータ）
・[data.xls]Sheet1のA列と[masta.xls]sheet1（マスタファイル）のA列に
　共通するキーがある

この前提で、２つのファイルが開かれた状態で
新規Bookに以下コードを記述して実行。

Sub Macro1()
　Dim ws　　As Worksheet '抽出条件書き出しシート
　Dim Frmla As String　　'抽出条件数式共通分
　Dim r　　 As Range　　 '元データ範囲
　Dim n　　 As Long

　'[data.xls]Sheet1のA列データと[masta.xls]sheet1のA列データを照合
　Frmla = "(MATCH([data.xls]Sheet1!RC1,[masta.xls]sheet1!C1:C1,0))"
　'コメントアウト行は複合キーの場合。
　'With Workbooks("masta.xls").Sheets("Sheet1") _
　　　　　　　　　　　　　　　.Range("A1").CurrentRegion
　'　　n = .Columns.Count
　'　　.Resize(, 1).Offset(, n).Formula = "=A1&""_""&B1"
　'End With
　'n = n + 1
　'Frmla = "(MATCH([data.xls]sheet1!RC1&""_""&[data.xls]sheet1!RC2" _
　　　　　　　 & ",[masta.xls]sheet1!C" & n & ",0))"
　Set r = Workbooks("data.xls").Sheets("Sheet1") _
　　　　　　　　　　　　　　　 .Range("A1").CurrentRegion
　With ThisWorkbook
　　　Set ws = .Sheets.Add
　　　'MATCH条件をセット
　　　ws.Range("A2").FormulaR1C1 = "=ISNUMBER" & Frmla
　　　'[フィルタオプションの設定]
　　　r.AdvancedFilter Action:=xlFilterCopy, _
　　　　　　　　　　　 CriteriaRange:=ws.Range("A1:A2"), _
　　　　　　　　　　　 CopyToRange:=.Sheets.Add.Range("A1"), _
　　　　　　　　　　　 Unique:=False
　　　'UNMATCH条件をセット
　　　ws.Range("A2").FormulaR1C1 = "=ISNA" & Frmla
　　　r.AdvancedFilter Action:=xlFilterCopy, _
　　　　　　　　　　　 CriteriaRange:=ws.Range("A1:A2"), _
　　　　　　　　　　　 CopyToRange:=.Sheets.Add.Range("A1"), _
　　　　　　　　　　　 Unique:=False
　End With
　'Workbooks("masta.xls").Sheets("Sheet1").Columns(n).ClearContents
　Set r = Nothing
　Set ws = Nothing
End Sub

新規Bookにシートが３枚追加されます。
抽出条件用（実行後削除可）とMATCHデータ、UNMATCHデータ。
引数CopyToRangeで既存シートにも変更可能。

MATCH条件は
"=ISNUMBER(MATCH([data.xls]Sheet1!A2,[masta.xls]sheet1!$A:$A,0))"
UNMATCH条件は
"=ISNA(MATCH([data.xls]Sheet1!A2,[masta.xls]sheet1!$A:$A,0))"
実態に応じて数式のBook名、Sheet名、共通キーの列を書き直してください。
（参考）
http://support.microsoft.com/kb/402757/ja

>得られた係数をもとにトランザイクションデータの各列の係数と掛け算をして
ここは詳細不明なので取り敢えず
MATCH/UNMATCHのデータをシートに書き出すまでです。
シート上にデータがあれば、あとは作業列や数式を使ってでも、
何とかなりますよね。

この回答へのお礼

end-u様
ありがとうございます。とりあえず、サンプルコードを解読・理解し、コーディングして実行させてみます。少し時間がかかるかもしれませんが結果についてはお知らせしたいと思います。やりたいことはあらまし以下のことです。
-------------------------------------------------------------------------------
●トランザクションデータ：TRN.xls、シート＝販売予測（Sドライブの情報出力フォルダに存置）
　Ａ列～Ｕ列：文字列情報、Ｖ列～ＢＫ列：数値データ
　１行目にはＡ列～ＢＫ列までの情報の内容を表す項目名称がある
●マスターデータ：MST.xls、シート＝Ｇ数量（Sドライブのマスターフォルダに存置）
●マッチングの結果データ：Ｇ数量.xls、シート＝計算結果（Sドライブのマスターフォルダに存置）
●アンマッチの結果データ：Ｇ数量.xls、シート＝アンマッチ（Sドライブのマスターフォルダに存置）

(1)トランザクションデータであるTRN.xlsのＡ列：u、Ｐ列:Fを対象にＣ列の製品コードと、マスターデータMST.xlsのＡ列の製品コードをマッチングさせて、TRN.xlsのＶ列からＢＫ列までの数値データとMST.xlsのＭ列の数値データを掛けた結果をＧ数量.xls、シート＝計算結果に書きこむ（TRN.xlsのＡ列からＵ列の文字列情報もこのファイルに書き、それに続けてＶ列から掛けた結果を順次書き込む）。
(2)TRN.xlsのＡ列：u、Ｐ列:Fとは、Ａ列にはUというキーワード、Ｐ列にはFというキーワードが登録されているということです。マスターデータとマッチングさせるのはトランザクションデータのＡ列＝Ｕ、Ｐ列＝Ｅのキーワードのついたデータが対象になります。書き込む情報が多いですが、それは月々の情報が列方向にあるためです。
(3)アンマッチデータはＧ数量.xls、シート＝アンマッチにトランザクションデータ（項目全部Ａ列～ＢＫ列まで）を書き込む。
(4)なお、マッチングの結果データおよびアンマッチの結果データをシートに書く際は、トランザクションファイル１行目にある項目名称（数量とか製品名など）を書き込む。

お礼日時：2010/07/20 22:23

No.1 回答者： imogasi 回答日時： 2010/07/20 00:19

回答が無いのかもしれないが、２度３度同じ質問して無いですか。

何度も見ている気がするが、＞ＶＢＡのコードとしてはどんな形になりますか、といわれても、丸投げ依頼で、こたえる気がしないが。
もっとＷＥＢなりを調べて勉強しては。マッチングという言葉は何処から得たのかな。
今ではバッチ処理時代に良く使ったマッチング処理は、余り言われないようだ。
ウィンドウズ時代以前の90年以前は情報処理試験にも出ていたアルゴリズムであったが。
アルゴリズムの教科書でも調べると良い。
＞マッチング
といっても色々なロジックがある。
昔からのバッチ処理のアルゴリズムに従う、
ファインドして相手を見つける、
キーを同列にあわせてファイルＡのシートの下部にファイルＢをコピー貼り付けしてキーでソートして
カップルを作って処理する、
ＳＱＬ（アクセス的）など使う
など色々ありえるのかな。
ーー
どうしても情報が無ければ、ＣＳＶでＶＢ的な例だが
http://soudan1.biglobe.ne.jp/qa5512095.html（imogasi マッチングでＷＥＢ照会）
の私のコードなど参考にして。
ここのＩｎｐｕｔ、ＯｕｔｐｕｔはエクセルＶＢＡではどうなるか勉強のこと。