|*顧客番号|*氏名|*購入日時|*購入商品|*受注番号|*枝番|*金額|
|1111|徳川家康|2009.10.20 12:00|ふんどし|20090001|1|200|
|1111|徳川家康|2009.10.20 12:00|鍬|20090001|2|145|
|1222|中江藤樹|2009.10.20 12:15|花瓶|20090002|1|240|
|1343|河井継之助|2009.10.20 13:00|ボディーボード|20090003|1|820|
|1111|徳川家康|2009.10.20 13:25|茶碗|200090004|1|247|
ご覧のように、データは受注の枝番号単位で別れています。同じ顧客が一回の購買で複数の商品を購入するとデータは複数に別れます。このデータを、「受注番号」単位でまとめたいと考えています。
① Excelのマクロで、このデータを、
|*顧客番号|*氏名|*購入日時|*購入商品|*受注番号|*点数|*金額|
|1111|徳川家康|2009.10.20 12:00|ふんどし|20090001|2|345|
|1222|中江藤樹|2009.10.20 12:15|花瓶|20090002|1|240|
|1343|河井継之助|2009.10.20 13:00|ボディーボード|20090003|1|820|
|1111|徳川家康|2009.10.20 13:25|茶碗|200090004|1|247|
というように、纏める/変換する方法があれば、ご教示ください。
② アクセスにこのようなデータを取込み、SQLで①のようなテーブルを抜き出す事ができるのであれば、そのSQL構文をご教示ください。
①だけですが、こんな感じでできます。
Sub Macro() Dim lastRow As Long Dim i As Long Columns("A:G").Sort Key1:=Range("E2"), Order1:=xlAscending, Key2:=Range( _ "F2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _ :=False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin, DataOption1:= _ xlSortNormal, DataOption2:=xlSortNormal lastRow = Cells(Rows.Count, "E").End(xlUp).Row For i = lastRow To 3 Step -1 If Cells(i, "E").Value = Cells(i - 1, "E").Value Then Cells(i - 1, "F").Value = Cells(i, "F").Value Cells(i - 1, "G").Value = Cells(i - 1, "G").Value + Cells(i, "G").Value Rows(i).Delete End If Next End Sub
①だけですが、こんな感じでできます。
Sub Macro() Dim lastRow As Long Dim i As Long Columns("A:G").Sort Key1:=Range("E2"), Order1:=xlAscending, Key2:=Range( _ "F2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _ :=False, Orientation:=xlTopToBottom, SortMethod:=xlPinYin, DataOption1:= _ xlSortNormal, DataOption2:=xlSortNormal lastRow = Cells(Rows.Count, "E").End(xlUp).Row For i = lastRow To 3 Step -1 If Cells(i, "E").Value = Cells(i - 1, "E").Value Then Cells(i - 1, "F").Value = Cells(i, "F").Value Cells(i - 1, "G").Value = Cells(i - 1, "G").Value + Cells(i, "G").Value Rows(i).Delete End If Next End Sub
ご回答、ありがとうございます。
恐らく、idSALINGERさんのこれが一番しっくりくるかと思われます。
現在、3290行ほどあるデータで試していますが、上手く挙動しています。
ただ、これぐらいのリストになると、処理時間がかなりかかりますね・・・・
もう少し他のソリューションもみてみたいので、しばらく質問はオープンとさせていただきます。
方法の1つとして紹介してみます。
Option Explicit Public Sub 集計実行() Const adOpenStatic = 3 Const adLockOptimistic = 3 Const adCmdText = &H1 Dim cn, rs Dim sql As String Set cn = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") cn.Provider = "Microsoft.Jet.OLEDB.4.0" cn.Properties("Extended Properties") = "Excel 8.0" cn.Open ThisWorkbook.FullName sql = "SELECT TB1.*, TB2.点数, TB2.金額 FROM ( " sql = sql & " SELECT 顧客番号,氏名,購入日時,購入商品,受注番号 " sql = sql & " FROM [" & ActiveSheet.Name & "$]" sql = sql & " WHERE 枝番 = 1 " sql = sql & ") AS TB1 INNER JOIN ( " sql = sql & " SELECT 受注番号, COUNT(1) AS 点数, SUM(金額) AS 金額 " sql = sql & " FROM [" & ActiveSheet.Name & "$]" sql = sql & " GROUP BY 受注番号 " sql = sql & ") AS TB2 ON TB1.受注番号 = TB2.受注番号 " sql = sql & "ORDER BY TB1.受注番号 " rs.Open sql, cn, adOpenStatic, adLockOptimistic, adCmdText Sheets.Add With ActiveCell .Range("A1").Value = "顧客番号" .Range("B1").Value = "氏名" .Range("C1").Value = "購入日時" .Range("D1").Value = "購入商品" .Range("E1").Value = "受注番号" .Range("F1").Value = "点数" .Range("G1").Value = "金額" End With ActiveSheet.Range("A2").Select Do Until rs.EOF ActiveCell.Value = CStr(rs.Fields.Item("顧客番号").Value) ActiveCell.Offset(0, 1).Value = rs.Fields.Item("氏名").Value ActiveCell.Offset(0, 2).Value = rs.Fields.Item("購入日時").Value ActiveCell.Offset(0, 3).Value = rs.Fields.Item("購入商品").Value ActiveCell.Offset(0, 4).Value = rs.Fields.Item("受注番号").Value ActiveCell.Offset(0, 5).Value = rs.Fields.Item("点数").Value ActiveCell.Offset(0, 6).Value = rs.Fields.Item("金額").Value ActiveCell.Offset(1, 0).Activate rs.MoveNext Loop rs.Close: Set rs = Nothing cn.Close: Set cn = Nothing ActiveSheet.Range("A1").Select With ActiveWindow .ScrollRow = 1 .ScrollColumn = 1 End With End Sub
※EXCEL中で完結させましたが、SQLを使うのであれば
ACCESSにエクスポートして、ソース中のSQLを叩いた方がいいかもしれませんね。
ご回答ありがとうございます!
こちらも上手く挙動しました。
ただ、この方法ですと、おっしゃるように、ACCESSにエクスポートしてSQL叩いたほうが良いと思われます。
・・・といっても私には、SQLを叩けるスキルはなく。
頂戴したソースを眺めて、ちょっと勉強してみます。
他のソリューションも見たいので、もう少し質問はオープンとさせていただきます。
Access2002でやってみました
項目名が多少変わってしまいましたが
目的のデータが出てきました
方法は
Accessにインポートし
インポートしたテーブル名はTableとしました
SELECT Table.[*顧客番号], Table.[*氏名], Table.[*購入日時], First(Table.[*購入商品]) AS [*購入商品の先頭], Table.[*受注番号], Max(Table.[*枝番]) AS [*枝番の最大], Sum(Table.[*金額]) AS [*金額の合計]
FROM [Table]
GROUP BY Table.[*顧客番号], Table.[*氏名], Table.[*購入日時], Table.[*受注番号];
前回同様
Accessにインポートし
インポートしたテーブル名はTableとしました
●次に下記のSQLを実行して 購入商品マスタを作成します
SELECT Table.[*顧客番号], Table.[*受注番号], Table.[*枝番], Table.[*購入商品] INTO 購入商品マスタ
FROM [Table]
WHERE (((Table.[*枝番])=1));
●次に下記のSQLを実行して 目的のデータを表示しました
SELECT Table.[*顧客番号], Table.[*氏名], Table.[*購入日時], 購入商品マスタ.[*購入商品], Table.[*受注番号], Count(Table.[*受注番号]) AS 点数, Sum(Table.[*金額]) AS [*金額の合計]
FROM 購入商品マスタ INNER JOIN [Table] ON (購入商品マスタ.[*顧客番号] = Table.[*顧客番号]) AND (購入商品マスタ.[*受注番号] = Table.[*受注番号])
GROUP BY Table.[*顧客番号], Table.[*氏名], Table.[*購入日時], 購入商品マスタ.[*購入商品], Table.[*受注番号];
ご回答、ありがとうございます。
恐らく、idSALINGERさんのこれが一番しっくりくるかと思われます。
現在、3290行ほどあるデータで試していますが、上手く挙動しています。
ただ、これぐらいのリストになると、処理時間がかなりかかりますね・・・・
もう少し他のソリューションもみてみたいので、しばらく質問はオープンとさせていただきます。