VBA エクセル ピボットテーブル

質問

皆様、こんにちは。

VBAでいくつかの表を合わせたピボットテーブルのようなものを作ろうとしていますが、やり方がよく分かりません。

例えば、次のような表があって

表１

350 Mat1 5674 t
640 Mat2 247 l
43 Ax1 24 t
0 … 0

表2

458 Mat3 5467 t
674 Mat1 674 t
98 Ax2 13 t
15 Ax3 87 l

…
次のようなピボットテーブルを考えています
↓
ピボットテーブル
1024 Mat1 6318 t
640 Mat2 247 l
458 Mat3 5467 t
43 Ax1 24 t
98 Ax2 13 t
15 Ax3 87 l

つまり、同様な項目を合計し、異なるものを別々に入力したいです。ただし、対象となる表の数はユーザーが決めるので、固定したものではない。対象となる表の形式は同様です。

もしご存知の方がいらっしゃったら、教えてください。

どうぞよろしくお願いいたします m(_ _)m

この投稿のマルチメディアは削除されているためご覧いただけません。

回答No.3

end-u

例えば、
『表がシートごとになっていて、必ずA1セルを起点とする』
『対象となる表の数はユーザーがシートを選択する事によって決定する』
というルールの元、ご提示のレイアウト通りに処理しようとすると、一例ですが

Sub try()
　　Dim ws　As Worksheet
　　Dim dic As Object
　　Dim r　 As Range
　　Dim s　 As String
　　Dim i　 As Long
　　Dim j　 As Long
　　Dim ix　As Long
　　Dim x　 As Long
　　Dim v　 As Variant
　　Dim w(1 To 65535, 1 To 4)

　　Set dic = CreateObject("scripting.dictionary")
　　For Each ws In ActiveWindow.SelectedSheets
　　　　With ws.Range("A1").CurrentRegion.Resize(, 4)
　　　　　　Set r = Intersect(.Cells, .Offset(1))
　　　　End With
　　　　If Not r Is Nothing Then
　　　　　　v = r.Value
　　　　　　For i = 1 To UBound(v)
　　　　　　　　s = v(i, 2) & vbTab & v(i, 4)
　　　　　　　　If dic.Exists(s) Then
　　　　　　　　　　ix = dic(s)
　　　　　　　　　　w(ix, 1) = w(ix, 1) + v(i, 1)
　　　　　　　　　　w(ix, 3) = w(ix, 3) + v(i, 3)
　　　　　　　　Else
　　　　　　　　　　x = x + 1
　　　　　　　　　　dic(s) = x
　　　　　　　　　　ix = x
　　　　　　　　　　For j = 1 To 4
　　　　　　　　　　　　w(ix, j) = v(i, j)
　　　　　　　　　　Next
　　　　　　　　End If
　　　　　　Next
　　　　　　Set r = Nothing
　　　　End If
　　Next
　　ActiveSheet.Select
　　If x > 0 Then
　　　　With Sheets.Add
　　　　　　.Range("A1:D1").Value = Array("field1", "field2", "field3", "field4")
　　　　　　.Range("A2:D2").Resize(x).Value = w
　　　　End With
　　End If
　　Set dic = Nothing
End Sub

こんな感じで処理できます。
ですが"scripting.dictionary"という外部オブジェクトを使いますし、
配列に対して理解しておく事も必要になります。
コード内容が理解できなければメンテナンスも難しいのでハードルは高いです。

でも『いくつかの表をまずコピーしてまとめてしまえば』ピボットテーブルが使えます。
（画像）
そのコピーでまとめる作業と、ピボットテーブルを作る作業をマクロにしてしまえば良いです。
完成形を妥協すればここまででもいいですし、
必要であればピボットテーブルを[コピー]-[値貼り付け]する事でピボットを解除し、
列を組みかえれば良いです。

お礼

end-u様、ありがとうございます。

＞コード内容が理解できなければメンテナンスも難しいのでハードルは高いです。

そうですね、難しそうです。とりあえず、全部の表を一つの表にコピーして、そこからフィルターで整理し選び出すようなプロシージャを考えてみます。

回答No.2

A88No8

こんにちは
　ＶＢＡでなくてもピボットウィザードの「複数のワークシート範囲」を選べば画像のようなものは作れますが．．

　その前に表自体を設計し直した方がよさげな気がします。
　ピボットテーブルを作ると無条件に集計されるので集計して意味のあるものと無いものをしっかり区別しないと。

回答No.1

imogasi

質問の表現が問題あり、もっと丁寧に表現すること。
表１、表２、・・はシートが別か？別として。
シートの例を挙げるなら、項目見出し（列）ぐらいそれらしきものを考えてあげよ。Mat1に関しては商品名とか。
列見出しは各シートで同じ内容で同じ順序なんですね？。
行の（例で言うとＢ列？）のMat1などの（例えば商品別の）合計を
各シートに渉って合計したいと言うことか。
ーー
ピボットや「串刺し演算」や「統合」がエクセルの機能にあるのに、なぜＶＢＡを持ち出すのか？
それにこの問題はＶＢＡでコードを書いてくださいという丸投げではないか。丸投げは規約上ダメです。
＞存知の方がいらっしゃったら
誰かだけが知っているというより、色んな方法が考えられて、どの方法でやるかの問題です。
中には（統合）その操作をして、マクロの記録を取り、必要箇所を修正すると言う方法もあり、エクセルＶＢＡらしいやり方である。
＞異なるものを別々に入力したいです
手作業で出すわけでないなら、入力とは言わない。コンピュタ処理の結果は、どちらかと言えば出力である。
表現は、「商品別に別シートに合計したい」で良い。
数字項目は合計らしいが、ｔやｌはどうする？
これは単位記号か？
ーーーー
例えばMat1は１つのシートに２度以上現れないのか？
ーー
およそのシート数の最多上限や最大行数はどんなものか？

補足

ご返答をどうもありがとうございます。

＞シートの例を挙げるなら、項目見出し（列）ぐらいそれらしきもの＞を考えてあげよ。Mat1に関しては商品名とか。
＞列見出しは各シートで同じ内容で同じ順序なんですね？。

こちらのサイトでどうやって画像をアップロードすればいいか分からなくて、文章で作ってみましたが、ダメでした。さっき、画像の追加という機能を見つけて、追加しましたが、小さすぎて何もわかりませんね。列見出しは各シートで同じ内容で同じ順序です。

＞ピボットや「串刺し演算」や「統合」がエクセルの機能にあるのに、なぜＶＢＡを持ち出すのか？
それは、ユーザーが1回くりっくするだけで簡単に結果を得るために作っているからです。

＞それにこの問題はＶＢＡでコードを書いてくださいという丸投げではないか。丸投げは規約上ダメです。
コードを書けと言うのではなく、アイディアやヒントをいただきたいです。VBAの初心者なので、どこから考えればいいかさえ分からなくて、コードを書きたくても書けません。

＞誰かだけが知っているというより、色んな方法が考えられて、どの方法でやるかの問題です。
中には（統合）その操作をして、マクロの記録を取り、必要箇所を修正すると言う方法もあり、エクセルＶＢＡらしいやり方である。

対象とする表の数をユーザーが決める（ユーザーがあるフォームに従ってそれぞれの表を作ることになっている）ので、マクロの記録でコードを作ることが難しいかと思います。

＞手作業で出すわけでないなら、入力とは言わない。コンピュタ処理の結果は、どちらかと言えば出力である。
表現は、「商品別に別シートに合計したい」で良い。

外国人なので、日本語のおかしいところをお許しください。それでも一所懸命考えて書いていますから。

＞数字項目は合計らしいが、ｔやｌはどうする？
これは単位記号か？

そのとおりです。表には数値と文字列、両方入っています。

＞例えばMat1は１つのシートに２度以上現れないのか？

そのとおりです。