人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

Excelで作成された次のような受注データがあります

|*顧客番号|*氏名|*購入日時|*購入商品|*受注番号|*枝番|*金額|
|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構文をご教示ください。

●質問者: noiehoie
●カテゴリ:コンピュータ
✍キーワード:00 Excel SQL ふんどし アクセス
○ 状態 :終了
└ 回答数 : 4/4件

▽最新の回答へ

1 ● SALINGER
●23ポイント ベストアンサー

?だけですが、こんな感じでできます。

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行ほどあるデータで試していますが、上手く挙動しています。

ただ、これぐらいのリストになると、処理時間がかなりかかりますね・・・・

もう少し他のソリューションもみてみたいので、しばらく質問はオープンとさせていただきます。


2 ● HALSPECIAL
●23ポイント

方法の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を叩けるスキルはなく。

頂戴したソースを眺めて、ちょっと勉強してみます。

他のソリューションも見たいので、もう少し質問はオープンとさせていただきます。


3 ● hakkyoku
●22ポイント

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.[*受注番号];


4 ● hakkyoku
●22ポイント

前回同様

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.[*受注番号];

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ