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構文をご教示ください。

回答の条件
  • 1人2回まで
  • 登録:2009/10/27 22:16:10
  • 終了:2009/11/03 22:20:03

ベストアンサー

id:SALINGER No.1

SALINGER回答回数3454ベストアンサー獲得回数9692009/10/27 22:39:07

ポイント23pt

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

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
id:noiehoie

ご回答、ありがとうございます。

恐らく、idSALINGERさんのこれが一番しっくりくるかと思われます。

現在、3290行ほどあるデータで試していますが、上手く挙動しています。

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

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

2009/10/28 14:39:08

その他の回答(3件)

id:SALINGER No.1

SALINGER回答回数3454ベストアンサー獲得回数9692009/10/27 22:39:07ここでベストアンサー

ポイント23pt

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

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
id:noiehoie

ご回答、ありがとうございます。

恐らく、idSALINGERさんのこれが一番しっくりくるかと思われます。

現在、3290行ほどあるデータで試していますが、上手く挙動しています。

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

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

2009/10/28 14:39:08
id:HALSPECIAL No.2

HALSPECIAL回答回数407ベストアンサー獲得回数862009/10/28 00:29:24

ポイント23pt

方法の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を叩いた方がいいかもしれませんね。

id:noiehoie

ご回答ありがとうございます!

こちらも上手く挙動しました。

ただ、この方法ですと、おっしゃるように、ACCESSにエクスポートしてSQL叩いたほうが良いと思われます。

・・・といっても私には、SQLを叩けるスキルはなく。

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

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

2009/10/28 14:41:13
id:hakkyoku No.3

hakkyoku回答回数32ベストアンサー獲得回数22009/10/28 23:41:02

ポイント22pt

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

id:hakkyoku No.4

hakkyoku回答回数32ベストアンサー獲得回数22009/10/31 01:03:49

ポイント22pt

前回同様

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

  • id:noiehoie
    テーブル記法に失敗しました。
    読みにくい点、ご容赦願います。
  • id:HALSPECIAL
    HALSPECIAL 2009/10/27 22:31:37
    通常は「集計」というと、

    |*顧客番号|*氏名|*受注番号|*点数|*金額|
    |1111|徳川家康|20090001|2|345|
    |1222|中江藤樹|20090002|1|240|
    |1343|河井継之助|20090003|1|820|
    |1111|徳川家康|200090004|1|247|

    のような形にまとまります。
    購入日時、購入商品は枝番1のものを取得したいということでしょうか?
    できないことはありませんが、EXCELでもACCESSでもできないことはありませんが、面倒ですね。
  • id:noiehoie
    id:HALSPECIALさん コメントありがとうございます。
     
    >購入日時、購入商品は枝番1のものを取得したいということでしょうか?
    はい。そういうことです。
    できれば、購入商品については一つのセルに文字列として全てつなげられればベストなんですが、余計ややこしいと思いまして・・・。


  • id:kn1967
    SALINGER さんが既に回答しておられるのし(多分Excelのほうだけだと思うけど)
    動作確認してないから、コメント欄。

    (1)質問文に即したもの
    SELECT 顧客番号, First(氏名) AS 氏名, First(購入日時) AS 購入日時,
    DLOOKUP("購入商品", "受注データ", "顧客番号 = " & 顧客番号 & " AND 受注番号 = " & 受注番号 " AND 枝番 = " & MAX(枝番)) AS 購入商品,
    受注番号, count(*) AS 点数, sum(金額) AS 金額
    FROM 受注データ
    GROUP BY 顧客番号, 受注番号;

    (2)略したもの
    SELECT 顧客番号, First(氏名) AS 氏名, First(購入日時) AS 購入日時,
    受注番号, count(*) AS 点数, sum(金額) AS 金額
    FROM 受注データ
    GROUP BY 顧客番号, 受注番号;

    id:HALSPECIAL さんのおっしゃる方法のほうがシンプルで楽ではあるのだけど、
    「受注一覧」と「ピッキング詳細」とを見比べたりする事があり、
    受注一覧側にも、どれか1つ商品名を入れてあると探しやすくなって便利なので、
    物流業界においての「通常」は「品名を1つ入れておく」ということになるのです。
  • id:SALINGER
    書き忘れましたが、左上詰めの表を想定しています。
    購入商品をつなげる場合は、下から5行目に
    >>
    Cells(i - 1, "D").Value = Cells(i - 1, "D").Value & vbNewLine & Cells(i, "D").Value
    <<
    を追加してみてください。
  • id:kn1967
    DLookup じゃなくて DFirst です。スミマセン。
  • id:noiehoie
    各位ありがとうございます!!
     
    検証させていただこうと思い、win機を立ち上げたところ、ブルースクリーンに・・・
    現在自宅で手元にMac機しかなく(ご存知のようにMac用officeはver2008よりVBA使えなくなりました)、検証できません。

    本当にごめんなさい。

    明日の日中、検証の上、返信&ポイント付けさせていただきます。

    もちろんそれまで質問はオープンにしておきます。

    早速にご回答やコメントを頂戴しているのに、本当にすいません。
  • id:Reiaru
    > テーブル記法に失敗しました。

    いえいえ、noiehoie 様は何も失敗されておりません。
    質問文では何故か「記法」が使えないだけですので、気にされる必要は無いと思います。
  • id:HALSPECIAL
    HALSPECIAL 2009/10/28 00:38:28
    id:kn1967さんへ
    未ソートのレコードセットに対して、First関数をお使いの場合はその並びが保証されないため、購入日時の取得があやしいでしょう。
    サブクエリ可してソートを噛ませたほうがよろしいかと思います。
    ん?、それよりも、Min(購入日時)でいけるような気がしますね。
  • id:HALSPECIAL
    HALSPECIAL 2009/10/28 00:44:59
    id:noiehoieさんへ
    記述し忘れたのですが、回答の関数は
    受注データのシートをアクティブにした状態で実行してください。
  • id:kn1967
    >First関数をお使いの場合はその並びが保証されない

    枝番を付加することによって検索結果が1レコードになるように組んでありますので、
    並び順は関係ないですよ。でも、その枝番の部分が間違ってるので
    「ふんどし」ではなく「鍬」が返ってきちゃいますけど・・・。

    SELECT First(顧客番号) AS 顧客番号, First(氏名) AS 氏名, First(購入日時) AS 購入日時,
      DFirst("購入商品", "受注データ", "受注番号 = " & 受注番号 & " AND 枝番 = " & Min(枝番)) AS 購入商品,
      受注番号, count(*) AS 点数, sum(金額) AS 金額
    FROM 受注データ
    GROUP BY 受注番号;

    枝番1が欠番した場合も一応考慮して =1 じゃなく = Min(枝番) にしてみました。
    サブクエリでも、もちろん、かまいませんが、動作確認してないものをいくつも書いてもねぇ・・・。
  • id:HALSPECIAL
    HALSPECIAL 2009/10/28 01:26:07
    上手く伝えられなくてすみません。
    私の申し上げたのは、購入商品ではなく、
    First(購入日時)
    の部分です。
    受注番号でグルーピングした際の、Firstでとってきた項目は並び順が保証されないので何が来るか分からないと言いたかったのです。


    ですが、例題のデータだと受注番号が同じで枝番が異なっても購入日時は同じなので、並びは関係ありませんね。異なるのかと思っていました。問題ないですね。失礼しました。
  • id:noiehoie
    id:kn1967さんのsqlを試してみました。

    Dfirstの構文エラー 演算子がありません。 とでて、上手く挙動しませんでした。
  • id:SALINGER
    >ただ、これぐらいのリストになると、処理時間がかなりかかりますね・・・・
    それは簡単に解決できます。
    コードの最初と最後に次の処理をいれて画面の描画を切ればいいのです。
    >>
    Sub Macro()
    Application.ScreenUpdating = False


    Application.ScreenUpdating = True
    End Sub
    <<
  • id:HALSPECIAL
    HALSPECIAL 2009/10/29 11:08:30
    ■First関数
    たとえば、ACCESSのテーブルに次のデータを順に入力した場合、
    ただFirst関数を使ったのでは枝番1の「ふんどし」は取ってこれません。


    |*顧客番号|*氏名|*購入日時|*購入商品|*受注番号|*枝番|*金額|
    |1111|徳川家康|2009.10.20 12:00|@@@@|20090001|3|200|
    |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|


    ソートしたレコードセットに対してFirst関数を使用しなければなりません。
    テーブルへの入力順序を受注番号、枝番の順で入力していけば正しく取得できるかもしれませんが、それは保証されないんです。
  • id:kn1967
    受注番号が同一なら、顧客番号/氏名/購入日時もそれぞれ同一なので、
    どの並びから持ってきても構わない。従ってFirst集計関数が使える。
    購入商品名は、並び順が問題となるためFirst集計関数では対応できず、
    VBAのDFirst関数を持ってきた。

    「演算子がありません」であって「未定義関数」と言われている訳でも無いので、
    クエリ内でDFirst関数が使えないという事ではないと思われ、
    演算子に抜けがあるとか、ダブルクォーテーションの数が合わないとか・・・、
    いずれにしても、現在、動作環境が無く確認できないので、この点は申し訳ない。
  • id:HALSPECIAL
    HALSPECIAL 2009/10/29 14:38:29
    kn1967さんへ
    言葉が足らずすみません。
    先ほどのコメントは
    3 回答者:hakkyoku さん向けのコメントでした。


    >受注番号が同一なら、顧客番号/氏名/購入日時もそれぞれ同一なので、
    >どの並びから持ってきても構わない
    そうですね。その通りだと思います。
    Firstも不要で、
    ORDER BY 受注番号,顧客番号,氏名,購入日時
    でいけそうですね。


    >Dfirstの構文エラー 演算子がありません。 とでて、上手く挙動しませんでした。
    横から口出しして恐縮なのですが、
    テーブルのフィールドの型がkn1967さんの思惑と異なっているからではないでしょうか?


    例えば、[顧客番号]のフィールドが文字列型の場合、SQL文ではクォーテーションで囲まなければなりませんし、あと、SQLをテキストエディタにコピペしたら全角の空白が混入していたので、それが原因とかでしょうか。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません