エクセル 項目、種類の変わる元表から集計したい

質問

エクセルでの質問です。
サンプルにあるように、同じシートに、元表とまとめ表があります。
まとめ表には、別シートから項目と金額が貼り付けられるようになっています。
やりたいのは、元表の項目種類を集計し、まとめて表に金額集計をしたいんです。
だけども例１、例２にあるように元表に貼付られる項目の種類、数は毎回異なり、範囲が
変わってしまう事で悩んでいます。
実はマクロでこのエクセルファイルを開くと、別エクセルファイルのデータを読込、自動で
まとめ表までの結果が計算されることを目指していて、元表に項目と金額が貼付られる
ところまで、出来たところです。
なのですが、毎回変わる元表のデータを、まとめ表に集計する所で悩んでいます。
ピボットテーブルを使えばいいのかなと思うのですが、毎回、範囲とデータが変わるため、
範囲と再計算をしなければならないと思いますが、どのようにしたらよいでしょうか？
頭の中がごちゃごちゃして判らなくなってしまいました。
マクロも詳しくなく、マクロの記憶で、なんとか組み合わせてやっている初心者です。
申し訳ありませんが、私でも判るようなレベルで教えて頂けると、助かります。
宜しくお願いいたします。サンプル見えないと困るので下にも書きます。ずれてますが
すみません。

例１ 　　　　　　　　　　　例２
シート１ 　　　　　　　　シート１
まとめ表 　　　　　　　　まとめ表
項目 　　金額 　　　　　　項目 　　金額
１A 　　　260 　　　　　　１A 　　　　250
２A 　　　500 　　　　　　２B 　　　　510
３A 　　　770 　　　　　　３A 　　　　　20
４A 　　　　30 　　　　　　４B 　　　　780
　　　　　　　　　　　　　　　６A 　　　400
　　　　　　　　　　　　　　　７A 　　　500

元表 　　　　　　　　　　　　元表
項目 　　金額 　　　　　　　　項目 　金額
１A 　　　100 　　　　　　　　　１A　　　100
１A 　　　150 　　　　　　　　　１A 　　　150
１A 　　　　10 　　　　　　　　　２B 　　　　10
２A 　　　200 　　　　　　　　　２B 　　　200
２A 　　300 　　　　　　　　　　２B　　　　　300
３A 　　　　20 　　　　　　　　　３A　　　　　20
３A　　　　300 　　　　　　　　　４B 　　　300
３A 　　　450 　　　　　　　　　４B 　　　450
４A 　　　　30 　　　　　　　　　４B 　　　30
　　　　　　　　　　　　　　　　　６A　 　400
　　　　　　　　　　　　　　　　　７A 　　500

回答No.1

keithin · ベストアンサー

例えばエクセルの「統合」の機能を使い，元表を記入した都度に項目ごと集計した結果を書き出せてみます。
まず試しに「統合」の操作を実際に行ってみて，上手く使えるようになったら新しいマクロの記録で操作をマクロに録り，参考にしてみて下さい。

なお統合する際には元表の範囲の最後のセルを取得して操作しても勿論構いませんが，最後のセルを無視して十分下にある空っぽセル（たとえば６万行目まで）までを範囲指定して統合させても問題ありません。

いまマクロで元表を記入させる事までは出来ているので，ある一つの元表が「どこのセルから開始されている」かは既知です。
既存のマクロを使い，たとえばB21セルに元表の「項目」と記入されたセルがあってそれ以下のセル範囲に元データを記入したところから始めます。

sub sample1()
dim SourceRange as range
dim DestinationRange as range

set sourcerange = range("B21")　　'既存マクロが貼り付けた元表の「項目」セルを指定
set destinationrange = cells(1, sourcerange.column)　’そのセルの１行目セルにまとめるとする

destinationrange.consolidate _
　sources:=activesheet.name & "!" & sourcerange.resize(60000,2).address(referencestyle:=xlR1C1), _
　function:=xlSum, _
　toprow:=True, _
　leftcolumn:=True, _
　createlinks:=False

end sub

実際にはあなたの既存マクロが元表を作成する都度，その元表を配置したセルを基準に（上述サンプルマクロのSoucerangeにセットして）統合結果を書き出させるように組み込んで使います。

回答No.3

tom04

こんばんは！
一例です。
元表はA・B列にあり、データは2行目からとします。

無理矢理って感じの方法です。

操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面がでますので
↓のコードをコピー＆ペーストしてマクロを実行してみてください。

Sub test()
Dim i, k As Long
k = Cells(Rows.Count, 1).End(xlUp).Row
Columns(1).Insert
For i = 2 To k
If WorksheetFunction.CountIf(Range(Cells(2, 2), Cells(i, 2)), Cells(i, 2)) = 1 Then
Cells(i, 1) = WorksheetFunction.SumIf(Range("B:B"), Cells(i, 2), Range("C:C"))
End If
Next i
For i = k To 2 Step -1
If Cells(i, 1) <> "" Then
Cells(i, 3) = Cells(i, 1)
Else
Rows(i).Delete (xlUp)
End If
Next i
Columns(1).Delete
End Sub

尚、一旦マクロを実行すると元に戻せませんので別Sheetにコピー＆ペーストしてマクロを試してみてください。

以上、参考になれば良いのですが・・・m(__)m

回答No.2

mitarashi

ピボットテーブルのデータの更新だけなら、下記でいかがでしょうか。
データはA1から、A,B列にわたって存在し、ピボットテーブルは同じシート中に１個だけ存在するとします。
Sub test()
ActiveSheet.PivotTables(1).SourceData = ActiveSheet.Name & "!" & Range(Range("A1"), Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Address
End Sub
同じシート内なら、Activesheet.Name & "!" &
は不要かもしれません。