VBA　Excelのテーブルからピボットテーブル／グラフを自動で作成

■ピボットテーブルを作成する
①PivotCachesコレクションのCreateメソッドでにピボットキャッシュを作成する。
※ピボットキャッシュはExcelの内部で保持するピボットテーブル用のデータの集合

オブジェクト.PivotCashes.Create(SourceType,SourceData)

オブジェクト・・・・・Workbookオブジェクト
SourceType・・・・・データのタイプ（ここでは定数xlDataBaseを指定）
SourceData・・・・・データ（通常はセル範囲のRangeオブジェクトを指定する）
戻り値：作成されたPivotTableオブジェクトを返す
②ピボットテーブルのフィールドを設定する
【書式１】

オブジェクト.PivotTable(index)

オブジェクト・・・・・WorkSheetオブジェクト
index・・・・・ピボットテーブルの番号または名前
【書式２】

オブジェクト.PivotFields(index).Orientation = 配置先

オブジェクト・・・・・PivotTableオブジェクト
index・・・・・フィールドの番号または名前
配置先・・・・・フィールドの配置先を下表の定数で指定

定数	配置先
xlColumnField	列
xlDataField	値
xlRowField	行

※記述例

オブジェクト.PivotFields("注文日").Orientation = xlRowField

■サンプルコード

Sub ピボット作成()
    Dim pvc As PivotCache
    Dim pvt As PivotTable
    Dim shp As Shape
    
    'ピボット用ワークシートの追加
    Worksheets.Add after:=Worksheets("Sheet1")
    Worksheets(2).Name = "分析"
    
    'ピボットテーブル作成
    Set pvc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
              SourceData:="注文データ")
    Set pvt = pvc.CreatePivotTable(tabledestination:=Range("A1"))
    
    'フィールドの配置
    With pvt
        .PivotFields("注文日").Orientation = xlRowField
        .PivotFields("商品名").Orientation = xlColumnField
        .PivotFields("小計").Orientation = xlDataField
        .PivotFields("氏名").Orientation = xlPageField
    End With
    
    'グラフの作成
    Set shp = Worksheets("分析").Shapes.AddChart(xlColumnClustered)
    shp.Chart.SetSourceData Source:=pvt.TableRange1
End Sub

■ピボットグラフを作成する
①ShpesコレクションのAddChartメソッドでグラフの表示形式やグラフのタイプを指定してグラフのオブジェクトを生成する

オブジェクト.Shapes.AddChart(Type,Left,Top,Width,Height)

オブジェクト・・・・・Worksheetオブジェクト
Type・・・・・グラフの種類を下表の定数で指定　
※引数を指定する際に「Type:=定数」で記述するとエラーになるので「Type:=」を入れずに指定すること
Left・・・・・左端位置をポイント単位の数値で指定（省略可）
Top・・・・・上端位置をポイント単位の数値で指定（省略可）
Width・・・・・幅をポイント単位の数値で指定（省略可）
Height・・・・・高さをポイント単位の数値で指定（省略可）

定数	グラフの種類
xlColumnClustered	集合縦棒
xlColumnStacked	積み上げ縦棒
xl3DColumn	3-D縦棒
xlBarClustered	集合横棒
xlLine	折れ線
xlLineMarekers	マーカー付き折れ線
xlPie	円
xlDoughnut	ドーナツ

※AddChart2メソッドについて
　Excel2013からはグラフの作成にAddChart2メソッドも使えるようになった。
　第１引数にスタイルを指定できる。スタイルも同時に指定してグラフを作成できる。

②-1 グラフ作成の際の元データを指定する

オブジェクト.SetSourceData(Source,PlotBy)

オブジェクト・・・・・Chartオブジェクト
Source・・・・・グラフのデータに用いる範囲のRangeオブジェクト
PlotBy・・・・・データをプロットする方法を下表の定数で指定
　　　　　　　省略可能であり、省略するとxlColumnと見なされる

定数	グラフの種類
xlColumns	データ系列に対応する値は行にある
xlRows	データ系列に対応する値は列にある

②-1 グラフの元データにピボットテーブルを設定する。
　SetDataSourceDataメソッドの引数Sourceにピポットでーブルの表のセル範囲のオブジェクトを設定する

オブジェクト.TableRange1

オブジェクト・・・・・PivotTableオブジェクト
※ピボットテーブルの表のセル範囲のオブジェクトはPivotTbleオブジェクトのTableRange1プロパティで取得できる
※レポートフィルター（ページフィールド）を含むセル範囲はPivotTableオブジェクトのTableRange2プロパティで取得できる

ray88’s diary

お仕事で困ったとき用の自分用の覚書

VBA　Excelのテーブルからピボットテーブル／グラフを自動で作成