Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


176 / 13163 ツリー ←次へ | 前へ→

【79149】2種類の集計方法 たく 17/5/25(木) 15:16 質問[未読]
【79150】Re:2種類の集計方法 カエムワセト 17/5/25(木) 15:41 発言[未読]
【79151】Re:2種類の集計方法 たく 17/5/25(木) 15:53 発言[未読]
【79153】Re:2種類の集計方法 カエムワセト 17/5/26(金) 1:19 発言[未読]
【79165】Re:2種類の集計方法 マナ 17/5/27(土) 19:54 発言[未読]
【79166】Re:2種類の集計方法 マナ 17/5/28(日) 15:23 発言[未読]

【79149】2種類の集計方法
質問  たく  - 17/5/25(木) 15:16 -

引用なし
パスワード
   元データから下記のような二通りの集計をしたいと考えています。
こちらの過去質問も確認しましたが、
アレンジしても思うようにいきません。
お知恵をお貸しいただけたらと思います。
よろしくお願いいたします。


Sheet名[ソート]
※これが元データです。
  A    B     C     D       E     F       G       H    I    J
1 売上日  伝票No.    連番    品名コード    品名   倉庫コード    納入場所   数量    単価    金額
2 2017/4/1 12345   1    62845    ボールペン 692      東京    15,800   20  316000
3 2017/4/1 12845   1    62846    万年筆   258      厚木    2,950   10  29500
4 2017/4/2 32985   1    62843    赤鉛筆   21       長島    6,849   30  205470
5 2017/4/3 62451   1    62841    鉛筆HB   30       京都    30,500   50  1525000
  ・
  ・
  ・
  ・
  ・
30 2017/4/30

Sheet名[商品別集計]←マクロの中でシートを新規作成し、集計という名前を付けて保存。
※商品別の数量・金額の合計を集計する
  A        B     C     D 
1 品名コード      品名   数量    金額
2 62845    ボールペン 98,000  196,000
3 62846    万年筆   47,500   475,000
4 62843    赤鉛筆   38,400  1,152,000
5 62841    鉛筆HB   105,000 5,250,000
  ・
  ・
  ・
  ・
  ・
Sheet名[日時集計]←マクロの中でシートを新規作成し、集計という名前を付けて保存。
※日別の商品別の数量・金額の合計を集計する

  A    B        C     D       E  
1 売上日  品名コード  品名   数量    金額
2 2017/4/1 62845    ボールペン15,800  316000
3 2017/4/1 62846    万年筆  2,950   29500
4 2017/4/2 62843    赤鉛筆  6,849   205470
5 2017/4/3 62841    鉛筆HB  30,500  1525000
  ・
  ・
  ・
  ・
  ・
30 2017/4/30

【79150】Re:2種類の集計方法
発言  カエムワセト  - 17/5/25(木) 15:41 -

引用なし
パスワード
   ここのVBA質問箱基本ポリシーの記述です。


>何をやったか書いてください
>おそらくあなたは、色々なことを試してできなかった末にここに質問を書くのでしょう。しかし回答者は、あなたが今まで何をやってきたか、何を知っていて何を知らないかわかり>ません。今まで試したこと、やろうと思ったけどやり方がわからなかったことなどをできるだけ詳しく書いてください。

【79151】Re:2種類の集計方法
発言  たく  - 17/5/25(木) 15:53 -

引用なし
パスワード
   追記させていただきます。
自分なりに切ったはったで記述したものです。
よろしくお願いいたします。

Sub 商品別集計()
  Dim dKey As String
  Dim c As Range
  Dim dic As Object
  Dim v As Variant
  Dim i As Long
  Dim j As Long
  
  Set dic = CreateObject("Scripting.Dictionary")
  
  With Sheets("ソート") 
    For Each c In .Range("A1", .Range("A" & .Rows.Count).End(xlUp))
      dKey = c.Value & vbTab & c.Offset(, 1).Value
      dic(dKey) = dic(dKey) + c.Offset(, 2).Value
    Next
  End With
  
  With Sheets("集計")  '転記シート
    v = .Range("A1").CurrentRegion.Value
    For i = 2 To UBound(v, 1)
      For j = 2 To UBound(v, 2)
        dKey = v(1, j) & vbTab & v(i, 1)
        v(i, j) = dic(dKey)
      Next
    Next
    .Range("A1").CurrentRegion.Value = v
    .Select
  End With
  
  Set dic = Nothing

  
End Sub

【79153】Re:2種類の集計方法
発言  カエムワセト  - 17/5/26(金) 1:19 -

引用なし
パスワード
   考え方の一例です。


1.※商品別の数量・金額の合計を集計する

「ソート」シートのD列をキーに重複無しの品名コード一覧を作成

品名コード一覧から項目を一つづつ取出して、SUMIF等を使って数量合計、金額合計を算出
ループは1回

2.※日別の商品別の数量・金額の合計を集計する
「ソート」シートのD列をキーに重複無しの品名コード一覧を作成
同じく重複無しの日付一覧を作成
品名コード一覧から項目を一つづつ取出して、SUMIFS等を使って品名コードと日付一覧をキーに数量合計、金額合計を算出
ループは2回/品名コードループ中に日付ループを組み入れる

※ピボットテーブルだともっと簡単に行けると思います。ピボットテーブルは使ったことが無いので解説できません。

【79165】Re:2種類の集計方法
発言  マナ  - 17/5/27(土) 19:54 -

引用なし
パスワード
   ▼たく さん:

dicには、配列も登録できます。
商品別集計も同じ感じでできると思います。

Sub 日時集計()
  Dim dKey As String
  Dim c As Range
  Dim dic As Object
  
  Set dic = CreateObject("Scripting.Dictionary")
 
  With Sheets("ソート")
    For Each c In .Range("A1", .Range("A" & .Rows.Count).End(xlUp))
      dKey = c.Value & vbTab & c.Offset(, 3).Value
      If Not dic.exists(dKey) Then
        dic(dKey) = Array(Empty, Empty, Empty, Empty, Empty)
      End If
      dic(dKey) = Array( _
        c.Value, _
        c.Offset(, 3).Value, _
        c.Offset(, 4).Value, _
        dic(dKey)(3) + c.Offset(, 7).Value, _
        dic(dKey)(4) + c.Offset(, 9).Value)
    Next
  End With
  
  With Worsheets.Add
    .Name = "日時集計"
    .Range("A1").Resize(dic.Count, 5).Value = _
      WorksheetFunction.Transpose(WorksheetFunction.Transpose(dic.items))
    .Select
  End With
 
  Set dic = Nothing
  
End Sub

【79166】Re:2種類の集計方法
発言  マナ  - 17/5/28(日) 15:23 -

引用なし
パスワード
   ▼たく さん:

集計用に配列は別に用意し、
dicにはインデックスを登録でももよいです。
理解しやすいほうをお使いください。

Sub 日時集計2()
  Dim dKey As String
  Dim c As Range
  Dim dic As Object
  Dim v()

  Set dic = CreateObject("Scripting.Dictionary")

  With Sheets("ソート")
    For Each c In .Range("A1", .Range("A" & .Rows.Count).End(xlUp))
      dKey = c.Value & vbTab & c.Offset(, 3).Value
      If Not dic.exists(dKey) Then
        n = dic.Count + 1
        dic(dKey) = n
        ReDim Preserve v(1 To 5, 1 To n)
        v(1, n) = c.Value
        v(2, n) = c.Offset(, 3).Value
        v(3, n) = c.Offset(, 4).Value
      End If
      n = dic(dKey)
      v(4, n) = v(4, n) + c.Offset(, 7).Value
      v(5, n) = v(5, n) + c.Offset(, 9).Value
    Next
  End With
 
  With Worsheets.Add
    .Name = "日時集計"
    .Range("A1").Resize(UBound(v, 2), 5).Value = _
      WorksheetFunction.Transpose(v)
    .Select
  End With

  Set dic = Nothing
 
End Sub

176 / 13163 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free