エクセルの集計

質問

A　　　　B　　　C　　　D
　　　　　　数量　　単価　　金額
2009/10/4　　26　　43　　\1,118
2009/11/12　23　　300　　\6,900
2009/11/25　30　　150　　\4,500
2010/1/27 　　12　　80　　　\960
2010/1/28 　　17　　210　　\3,570

の様なデータがあったときに
A列を「セルの書式の書式設定]でYYYY"年"MM"月"形式にして
「データ」→「集計」で月次ごとの集計をしています。
今回のデータでは１２月分のデータがないため
このままでは昨年以前のデータとの集計でずれが出てしまいます。
１２月分の空データを入れる以外に
集計表でのデータの補完が出来るようにはできませんか？
VBA初心者ですがよろしくお願いします。

回答No.2

ASIMOV · ベストアンサー

飛んでいる「月」の分の空データーを挿入するVBAです
１．日付（A列）はソートされている事
２．データーの最後は、空セルである事
３．途中（A列）に空セルがない事
といった制限は有ります．．．
------------------
Sub Mac1()
　lp = 2
　mb = Month(Cells(lp, 1))
　yn = Year(Cells(lp, 1))
　lp = lp + 1
　Do Until Cells(lp, 1) = ""
　　Select Case Month(Cells(lp, 1))
　　Case mb
　　　lp = lp + 1
　　Case mb + 1
　　　mb = mb + 1
　　　lp = lp + 1
　　Case Else
　　　If mb = 12 Then
　　　　If Month(Cells(lp + 1, 1)) = 1 Then
　　　　　mb = 1
　　　　　lp = lp + 1
　　　　Else
　　　　　mb = 0
　　　　　yn = yn + 1
　　　　End If
　　　End If
　　　Rows(lp & ":" & lp).Insert Shift:=xlDown
　　　mb = mb + 1
　　　Range("A" & lp).FormulaR1C1 = mb & "/1/" & yn
　　　Range("B" & lp).FormulaR1C1 = "0"
　　　Range("C" & lp).FormulaR1C1 = "0"
　　　Range("D" & lp).FormulaR1C1 = "0"
　　　lp = lp + 1
　　End Select
　Loop
End Sub
-----------------------

お礼

早々の回答ありがとうございます。

自分のしたいことに近いので参考にさせて頂くと同時に
良回答とさせて頂きました。
また自分の知らないコードには勉強になりました。

別件ですが
空データを入れることで
ゼロの除算をしたり最小値が０になったりしました。
まだまだ勉強しなければいけませんが
今後もよろしくお願いします

回答No.1

avanzato

こんにちは。
回答にあたり幾つか不明な点があります。
集計は「データ」→「集計」の方法・機能ではならないといけないのでしょうか？
書式だけ同じならばと言うことであれば
Sub sample()
Dim I As Integer, I2 As Integer
I = 3
I2 = I - 1
Do While Range("A" & I).Value <> ""
If Month(Range("A" & I).Value) <> Month(Range("A" & I - 1).Value) Then
Rows(I & ":" & I).Insert Shift:=xlDown
Range("A" & I).Value = Year(Range("A" & I - 1).Value) & "年" & Month(Range("A" & I - 1).Value) & "月 合計"
Range("D" & I).Formula = "=SUM(D" & I2 & ":D" & I - 1 & ")"
I = I + 2
I2 = I - 1
Else
I = I + 1
End If
Loop
Range("A" & I).Value = Year(Range("A" & I - 1).Value) & "年" & Month(Range("A" & I - 1).Value) & "月 合計"
Range("D" & I).Formula = "=SUM(D" & I2 & ":D" & I - 1 & ")"
End Sub
でも同じ書式にはなります。
12月分の空データを入れることでどのような不具合が発生するのでしょうか？
空データを入れることで容易に処理をすることが出来るかと思いますが・・・。
「データ」→「集計」の場合、仮に仮想12月データがあったとして行を「表示しない」にしていても表示できてしまうというのが問題なのでしょうか？
尚、仮想12月データを入れ該当行を「表示しない」にして上記コードを実行すると仮想データは表示されませんが12月の集計は出ます。

質問のサンプルにはありませんが、もし2009年12月～2010年1月のデータが無い場合を想定すると若干VBAが複雑になります。

お礼

早々の回答ありがとうございます。

空データを入れたくないのは
マスターデータを集計用のシートにコピーして
ＶＢＡで集計しているためです。
（マスターデータには余分なデータ、手間を入れたくないので）
言葉足らずで申し訳ありませんでた。

別件ですが
空データを入れることで
ゼロの除算をしたり最小値が０になったりしました。
まだまだ勉強しなければいけませんが
今後もよろしくお願いします。