VBAによりマッチング操作
基本的な知識がないので、まずはそこが課題ですが、VBAのコードとしてはどんな形になりますか。
まったくの初心者です
(1)2つのファイルがあり共通するキーを照合することにより得られたマスターファイルから得られた係数をもとにトランザイクションデータの各列の係数と掛け算をして、その結果をトランザクションデータのA列からG列とともに別のファイル(シート1)に書き込む。
(2)アンマッチデータは結果の書き込まれたファイルのシート2にトランザクションデータの内容をそのまま書き込むというものです。
(3)トランザクションデータのデータ数は未知ですが、列数は定まっています。
やりことのあらましは以上のようなことです。
No.3ベストアンサー
- 回答日時:
読み解くほうがちょっと苦労しますね。
u,FなのかU,Eなのか、はそちらで修正してもらえばいい事なので
あまりツっこみませんが
マッチング処理というより、やはりフィルタ処理の案件と言えそう。
・TRN.xlsのSheets("販売予測")に作業列を追加。
・MST.xlsのSheets("G数量")のM列値をVLOOKUP関数で引っ張る。
・それをAdvancedFilterメソッドの抽出条件とする。
・抽出後、MATCHデータについては作業列の値をコピー、乗算貼り付け。
勘違いしてなければ以上の流れで済むはず。
G数量.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]G数量!$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
'--------------------------------------------------------------------
[フィルタオプション]も「乗算での貼り付け」も
一般操作をマクロ記録できますからコーディングのヒントにしてください。
また、データ量が多ければ重くて使えない場合もあるかもしれません。
その時は工夫して改善してみてください。
このコードをもとに一部を修正し完成させました。
データ量が少ないので処理時間は問題ではなかったです。コードを読み解いていくなかでよい勉強になりました。片手間でやっていますのでなかなか着手できなかったのですがわりとすんなり解決できました。
マッチングというよりフィルター処理ということがよく分かりました。ありがとうございました。
No.4
- 回答日時:
失礼。
>With Workbooks("TRN.xls").Sheets("計算結果")
With Workbooks("TRN.xls").Sheets("販売予測")
です...orz
No.2
- 回答日時:
>VBAのコードとしてはどんな形になりますか。
いろんな手法があります。
一般機能の[フィルタオプション]を使うと比較的簡単かもしれません。
・[data.xls]Sheet1に抽出したい元データがある(トランザクションデータ)
・[data.xls]Sheet1のA列と[masta.xls]sheet1(マスタファイル)のA列に
共通するキーがある
この前提で、2つのファイルが開かれた状態で
新規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にシートが3枚追加されます。
抽出条件用(実行後削除可)と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ドライブの情報出力フォルダに存置)
A列~U列:文字列情報、V列~BK列:数値データ
1行目にはA列~BK列までの情報の内容を表す項目名称がある
●マスターデータ:MST.xls、シート=G数量(Sドライブのマスターフォルダに存置)
●マッチングの結果データ:G数量.xls、シート=計算結果(Sドライブのマスターフォルダに存置)
●アンマッチの結果データ:G数量.xls、シート=アンマッチ(Sドライブのマスターフォルダに存置)
(1)トランザクションデータであるTRN.xlsのA列:u、P列:Fを対象にC列の製品コードと、マスターデータMST.xlsのA列の製品コードをマッチングさせて、TRN.xlsのV列からBK列までの数値データとMST.xlsのM列の数値データを掛けた結果をG数量.xls、シート=計算結果に書きこむ(TRN.xlsのA列からU列の文字列情報もこのファイルに書き、それに続けてV列から掛けた結果を順次書き込む)。
(2)TRN.xlsのA列:u、P列:Fとは、A列にはUというキーワード、P列にはFというキーワードが登録されているということです。マスターデータとマッチングさせるのはトランザクションデータのA列=U、P列=Eのキーワードのついたデータが対象になります。書き込む情報が多いですが、それは月々の情報が列方向にあるためです。
(3)アンマッチデータはG数量.xls、シート=アンマッチにトランザクションデータ(項目全部A列~BK列まで)を書き込む。
(4)なお、マッチングの結果データおよびアンマッチの結果データをシートに書く際は、トランザクションファイル1行目にある項目名称(数量とか製品名など)を書き込む。
No.1
- 回答日時:
回答が無いのかもしれないが、2度3度同じ質問して無いですか。
何度も見ている気がするが、>VBAのコードとしてはどんな形になりますか、といわれても、丸投げ依頼で、こたえる気がしないが。もっとWEBなりを調べて勉強しては。マッチングという言葉は何処から得たのかな。
今ではバッチ処理時代に良く使ったマッチング処理は、余り言われないようだ。
ウィンドウズ時代以前の90年以前は情報処理試験にも出ていたアルゴリズムであったが。
アルゴリズムの教科書でも調べると良い。
>マッチング
といっても色々なロジックがある。
昔からのバッチ処理のアルゴリズムに従う、
ファインドして相手を見つける、
キーを同列にあわせてファイルAのシートの下部にファイルBをコピー貼り付けしてキーでソートして
カップルを作って処理する、
SQL(アクセス的)など使う
など色々ありえるのかな。
ーー
どうしても情報が無ければ、CSVでVB的な例だが
http://soudan1.biglobe.ne.jp/qa5512095.html(imogasi マッチングでWEB照会)
の私のコードなど参考にして。
ここのInput、OutputはエクセルVBAではどうなるか勉強のこと。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで空白行を削除する ...
-
エクセルのデータがない行には...
-
マクロで最終行を取得してコピ...
-
vbaエクセルマクロについて she...
-
VBでスプレッドをしようする際...
-
Excel マクロ 検索結果を別シ...
-
エクセルで階層図を作る方法
-
【VBA】条件に一致しない行を削...
-
VBAで入力の結果を他のセルに反...
-
エクセルのVBAで指定した行数の...
-
空白を複数行一気に挿入するには?
-
Access2003レポート:最終ペー...
-
Excel 別ブックから該当データ...
-
【VBA】条件に一致しない行を削...
-
【至急】Excel 同一人物の情報...
-
数値に見えるものはすべて数値...
-
VBAで保存しないで閉じると空の...
-
WPSOffice_マクロの有効化について
-
マクロの保存先、開いてるすべ...
-
複数のマクロボタンをまとめて...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【VBA】条件に一致しない行を削...
-
エクセルで空白行を削除する ...
-
マクロで最終行を取得してコピ...
-
エクセルのデータがない行には...
-
数値に見えるものはすべて数値...
-
【マクロ】A列最終行までを、カ...
-
VB.net
-
Excel VBAでオートフィルタで抽...
-
【VBA】条件に一致しない行を削...
-
エクセルで階層図を作る方法
-
列から特定の文字列検索→該当以...
-
Excel マクロ 検索結果を別シ...
-
各個体に対する平均値の自動計...
-
Excel97 指定した行だけマク...
-
空白を複数行一気に挿入するには?
-
EXCEL2007マクロ/オートフィル...
-
Access2003レポート:最終ペー...
-
EXCEL VBAでA列にある空白行よ...
-
Excel VBA オートフィルタの結...
-
VBAでの重複データに色付け
おすすめ情報