Excel VBA質問箱 IV

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

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


8875 / 13644 ツリー ←次へ | 前へ→

【30566】月毎の集計方法についてです ebi 05/10/31(月) 0:34 質問[未読]
【30576】Re:月毎の集計方法についてです ちくたく 05/10/31(月) 10:20 回答[未読]
【30580】Re:月毎の集計方法についてです ちくたく 05/10/31(月) 11:28 発言[未読]
【30579】Re:月毎の集計方法についてです とまと 05/10/31(月) 11:26 発言[未読]
【30581】Re:月毎の集計方法についてです Statis 05/10/31(月) 11:43 回答[未読]
【30583】Re:月毎の集計方法についてです とまと 05/10/31(月) 13:50 回答[未読]
【30599】みなさん、ありがとうございました ebi 05/10/31(月) 17:07 お礼[未読]

【30566】月毎の集計方法についてです
質問  ebi  - 05/10/31(月) 0:34 -

引用なし
パスワード
   初めての質問ですが、お願いします。
VBAは初心者ですが、よろしくお願いします。
PCのOSはwin2000、エクセルのバージョンは2000です。

月別の集計をしています。

シート1に
A列     B列      C列
名前    日付      金額  
佐藤  2004/4/13    19000
鈴木  2004/6/20    20000

といった感じでデータ下の方向に増えます。

シート2に下のように集計(合計)をしたいのですが、
A列    B列     C列   D列 ・・・・ N列    
名前    4月    5月    6月      3月 
佐藤   19000
鈴木               20000        
田中

シート1のデータを元にピボットテーブルで作成しましたが、それだとシート2にある田中さんやデータがない月が出てこないため、困っています。
シート2で表示したい項目が決まっていて、0(データがなくても)必ず表示されるように作りたいのです。
セルに計算式を代入する方法も考えましたが、作りたいのがこの表だけではなく、セルに代入する計算式が増えてしまうので、出来たらVBAでやりたいと思っています。
なにか方法がありましたら、ご教授よろしくお願いいたします。

【30576】Re:月毎の集計方法についてです
回答  ちくたく  - 05/10/31(月) 10:20 -

引用なし
パスワード
   ebi さん
こんにちは。

大変、質問はわかりよかったのですが、
日付の形式によっては下記のコードは動かないかもしれません。
また、私は、4月から始まる場合の処理がよくわからないので、
書式設定とFor~Nextのネストでごまかしました。

Sub 形式変換()
  
  Dim oriWs As Worksheet, myWs As Worksheet
  Dim myMon As Integer, myVal As Long
  Dim i As Integer, j As Integer
  
  Set oriWs = Worksheets("Sheet1")
  Set myWs = Worksheets("Sheet2")
  
  'myWsにいろいろ記述。
  myWs.Columns(1).Value = oriWs.Columns(1).Value
  Range("B1:M1") = Array("4", "5", "6", "7", "8", "9", "10", "11", "12", "1", "2", "3")
  Range("B1:M1").NumberFormatLocal = "0""月"""
  
  '4月から始まる場合の処理がよくわかんないから、
  'ちょっとひねくれて処理をしてみる。
  For i = 2 To CInt(oriWs.Range("A65536").End(xlUp).Row)
    myMon = Month(oriWs.Range("B" & i).Value)
    myVal = oriWs.Range("C" & i).Value
    For j = 2 To 13
      If myWs.Cells(1, j).Value = myMon Then
        Cells(i, j).Value = myVal
      End If
    Next j
  Next i
  
End Sub

【30579】Re:月毎の集計方法についてです
発言  とまと  - 05/10/31(月) 11:26 -

引用なし
パスワード
   こんにちは。
考え方だけですけど。

D列ににからA列とB列を連結する関数をコピー


A列     B列      C列   D列
名前    日付      金額  
佐藤  2004/4/13    19000   =A2&MONTH(B2)
鈴木  2004/6/20    20000


シート2のB2に連結列をsumifする関数をコピー
それを対象エリアにコピー

シート2に下のように集計(合計)をしたいのですが、
A列    B列     C列   D列 ・・・・ N列    
名前    4月    5月    6月      3月 
佐藤   =SUMIF(Sheet1!$D:$D,$A2&B$1,Sheet1!C:C)
鈴木                       
田中

問題はsheet2の見出しですがこれを数値にしておいて
表示形式で 0"月" に変更しておく

これで集計できればそれをマクロ化できると思います。

【30580】Re:月毎の集計方法についてです
発言  ちくたく  - 05/10/31(月) 11:28 -

引用なし
パスワード
   ごめんなさい、最後のほうに赤字の部分を加えてください。

>    For j = 2 To 13
>      If myWs.Cells(1, j).Value = myMon Then
>        myWs.Cells(i, j).Value = myVal
>      End If
>    Next j

【30581】Re:月毎の集計方法についてです
回答  Statis  - 05/10/31(月) 11:43 -

引用なし
パスワード
   こんにちは
お試しを(Sheet2=集計シートのレイアウトもコード化してあります)

Sub test()
Dim Sh As Worksheet, R As Range, C As Range
Dim Fi As Range, Ad As String, Ma As Variant, Da As String

Set Sh = Worksheets("Sheet2")
Sh.Cells.ClearContents
Sh.Range("B1").Value = "4月"
Sh.Range("B1").AutoFill Destination:=Sh.Range("B1:M1"), Type:=xlFillDefault

With Worksheets("Sheet1")
   .Columns(1).AdvancedFilter xlFilterCopy, , Sh.Range("A1"), True
   Set R = Sh.Range("A2", Sh.Range("A65536").End(xlUp))
   For Each C In R
     Set Fi = .Columns(1).Find(C.Value, , xlValues, xlWhole)
     If Not Fi Is Nothing Then
      Ad = Fi.Address
      Do
       Set Fi = .Columns(1).FindNext(Fi)
       Da = Month(Fi.Offset(, 1))
       Ma = Application.Match(Da & "月", Sh.Rows(1), 0)
       If Not IsError(Ma) Then
         C.Offset(, Ma - 1).Value = Fi.Offset(, 2).Value
       End If
      Loop Until Ad = Fi.Address
     End If
     Set Fi = Nothing
   Next C
   Set R = Nothing:Set Sh = Nothing
End With

End Sub

【30583】Re:月毎の集計方法についてです
回答  とまと  - 05/10/31(月) 13:50 -

引用なし
パスワード
   編集作業をマクロ化すると
こんなかんじで書けると思います。


Sub 集計()

Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim rowA As Long

Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")


With sh1.Range("C2", sh1.Range("C65536").End(xlUp)).Offset(, 1)
 .Formula = "=A2&MONTH(B2)"
 .Value = .Value
End With


With sh2.Range("B1:M1")
 .Value = Array(4, 5, 6, 7, 8, 9, 10, 11, 12, 1, 2, 3)
 .NumberFormatLocal = "0""月"""
End With

rowA = sh2.Range("A65536").End(xlUp).Row

With sh2.Range("B2").Resize(rowA - 1, 12)
 .Formula = "=SUMIF(Sheet1!$D:$D,$A2&B$1,Sheet1!$C:$C)"
 .Value = .Value
End With

sh1.Columns("D").ClearContents

End Sub

【30599】みなさん、ありがとうございました
お礼  ebi  - 05/10/31(月) 17:07 -

引用なし
パスワード
   ちくたくさん、Statisさん、とまとさん、どうもありがとうございました。

シート2で、名前と月ごとの合計を表示させたかったため、(私の書いた表の書き方が悪かったです。申し訳ありません)とまとさんの教えてくださった方法で、希望していた結果を得る事ができました。
皆さんの回答を参考に、自分でもこのように作れるように精進したいと思います。
本当にありがとうございました。

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