ピポットテーブルをVBAで組む方法

質問

B列～L列に並んでいるデータがあり、
商品が何個ずつ売れているかピポットテーブルで出したいと思っています。

データは1000を超えると想定しておりますので
マクロで処理ができればと思っているのですが、
初心者のため、どういう風にマクロを組めばいいかわかりません。。。

ピポッドフィールドは
行
・商品番号
・商品
・価格

値
・個数

が入るようにしたいです。

ご教授いただければ幸いです。
どうぞよろしくお願いいたします。

回答No.1

HohoPapa · ベストアンサー

私だったらピボットテーブルをマクロから読み出すのではなく
マクロで直接SQL文を投げ、同等の処理を行います。

Option Explicit

'//　サンプル
Sub Sample()

　Dim cn As Object
　Dim rs As Object
　Dim wkSQL As String

　Set cn = CreateObject("ADODB.Connection")
　Set rs = CreateObject("ADODB.Recordset")
　cn.Provider = "Microsoft.ACE.OLEDB.12.0"
　cn.Properties("Extended Properties") = "Excel 12.0;HDR=YES;IMEX=1"
　cn.Open ThisWorkbook.FullName

　With ThisWorkbook.Sheets(1)

　　'SQL文組み立て
　　wkSQL = ""
　　wkSQL = wkSQL & "SELECT [商品番号],[商品],[価格],sum([合計]) " & vbCrLf
　　wkSQL = wkSQL & "FROM [Sheet1$A1:L65000]" & vbCrLf
　　wkSQL = wkSQL & "GROUP BY [商品番号],[商品],[価格]" & vbCrLf
　　wkSQL = wkSQL & "ORDER BY [商品番号]" & vbCrLf

　　'SQL文実行
　　rs.Open wkSQL, cn

　　'タイトルを出力
　　.Cells(3, 15).Value = "商品番号"
　　.Cells(3, 16).Value = "商品"
　　.Cells(3, 17).Value = "価格"
　　.Cells(3, 18).Value = "集計"

　　'結果セットを格納
　　.Cells(4, 15).CopyFromRecordset rs

　End With

　'後処理
　rs.Close
　Set rs = Nothing
　cn.Close
　Set cn = Nothing

End Sub

回答No.2

imogasi

何を考えているのかわからない。
なぜVBA（マクロ）でやるのか。その理由は何か？
VBAで、ピボットでできる表を作るとすれば、プログラムの修業が必要だ。
独学では生易しくないと思う。甘く見ないで。出来る人は、結果だけ書いて、丸投げし、回答のコピペで満足する人とは、タイプが違うと思う。
＞データは1000を超えると想定しておりますので
こんなのデータ数として、当たり前の数だろう。分類・集計後の項目数としても
驚く数ではない。
ーー
目的の出来上がり表（数種でもよい）が決まっていて、その、あるきまったの操作を、毎回くりかえしたくないなら、VBAでコード化しておくと、ボタンクリックなどで、早いと感じる人もいるだろう、とは思う。
ーー
ピボットは操作をすれば、マクロの記録が取れて、操作ごとに、どういうコードになるかわかるのではないか。
　基本は、操作で決める要素を、VBA定数を使い、しかるべきステートメントに記述して、決めるだけ。
　ただし、ピボットの操作を知らないで、VBAでそれをやろうとするのはおかしいと思う。
ーー
処理スピードでもかなわないと思うが、VBAで統計表も作れる。
　昔（パソコン出現・流布以前）は、ピボットのような、こういうソフトがなかった（高価な）時代もあって、システム部門は、ソート処理を使ったりして、作るのが仕事だった。
今でも、しこしこと、VBAで作れる。基本はソートして、集計するだけ。
ピボットの方法は、操作で、色々な様式の表に瞬時に切り替えて（組み替えて）出せて、分析などできるのが特徴。これを逆戻りさせる方法（自作）にこだわることはない。
ーー
既出の回答にあるように、SQLを駆使して、表を作るのが、考え方の上で、一番親縁なやり方かもしれない。習得すると、他の分野や、データベース・ソフトでも、その経験が生きると思うから（汎用的）。Accessの勉強でも始めては。

お礼

いろいろとご指摘いただきありがとうございます。
ピボットテーブルはVBAで組むことではないということが勉強になりました。
また機会がありましたらどうぞよろしくお願いします。