みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

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

ピポッドフィールドは

・商品番号
・商品
・価格


・個数

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

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

投稿日時 - 2019-01-31 14:58:18

QNo.9583225

困ってます

質問者が選んだベストアンサー

私だったらピボットテーブルをマクロから読み出すのではなく
マクロで直接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

投稿日時 - 2019-01-31 17:06:14

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(2)

ANo.2

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

投稿日時 - 2019-01-31 18:52:31

お礼

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

投稿日時 - 2019-01-31 19:34:46

あなたにオススメの質問