Excel VBA ピボットテーブルを作成する

スポンサーリンク
スポンサーリンク

ピボットテーブルを作成する方法

ピボットテーブルは、データの集計や分析をするのに
便利な機能です。
VBAでピボットテーブルを操作するには
以下の3つのステップが必要になります。

ピボットテーブル作成の流れ

  • Step1:ピボットテーブルキャッシュを作成
    PivotCaches【ピボットキャシーズ】コレクションの
    Create【クリエイト】メソッドでピボットテーブルの元となる
    データをメモリ内に格納します。
  • Step2:ピボットテーブルの作成
    PivotCache【ピボットキャッシュ】オブジェクトの
    CreatePivotTable【クリエイトピボットテーブル】メソッド
    空白のピボットテーブルを作成します。
  • Step3:ピボットテーブルにフィールドを追加する
    PivotField【ピボットフィールド】オブジェクトで
    行フィールド、列フィールド、値フィールドを
    追加してピボットテーブルを完成させます。

ピボットテーブル作成で使用するメソッドとプロパティ一覧

  • Create【クリエイト】メソッド
    ピボットキャッシュオブジェクトを作成します。
  • CreatePivotTable【クリエイトピボットテーブル】メソッド
    Create【クリエイト】メソッドで作成されたピボットキャッシュを元に
    ピボットテーブルオブジェクトを作成します。
  • PivotFields【ピボットフィールズ】メソッド
    ピボットテーブルのフィールド(項目)を参照します。
  • Orientation【オリエンテーション】プロパティ
    PivotFields【ピボットフィールズ】メソッドで参照したフィールドに対して
    フィールドの配置についての設定を行います。
  • Function【ファンクション】プロパティ
    PivotFields【ピボットフィールズ】メソッドで参照した値フィールドに対して
    集計方法を設定します。

以上のメソッドやプロパティについて詳細に説明します。


Create【クリエイト】メソッド

ピボットテーブルキャッシュを作成するには
PivotCaches【ピボットキャシーズ】コレクションの
Create【クリエイト】メソッドを使用します。
Create【クリエイト】メソッドにより
PivotCache【ピボットキャッシュ】オブジェクトが作成され
作成されたPivotCache【ピボットキャッシュ】オブジェクトが返ります。

Create【クリエイト】メソッドの書式

[]内は省略可能です。
オブジェクト.Create( SourceType[, SourceData, Version] )


設定値(引数)の説明

  • オブジェクト(必須)
    PivotCaches【ピボットキャッシーズ】コレクションを指定します。
    PivotCaches【ピボットキャッシーズ】コレクションは
    Workbookオブジェクトの
    PivotCaches【ピボットキャッシーズ】プロパティで取得できます。
  • SourceType【ソースタイプ】(必須)
    xlPivottableSourceType列挙型の定数で元データの種類を指定します。

    xlPivottableSourceType列挙型の定数
    定数内容
    xlDatabase1Excelの表
    xlExternal2外部アプリケーションデータ
    xlConsolidation3複数のワークシート範囲
    xlScenario4[シナリオの登録と管理] を
    使用して作成されたシナリオに基づきます。
    xlPivotTable-4148既存のピボットテーブル レポート
  • SourceData【ソースデータ】(省略可)
    新しいピボットテーブルキャッシュのデータを指定します。
    引数SourcTypeがxlDatabase(Excelの表)または
    xlConsolidation(複数のワークシート範囲)の場合は
    データ元となるセル範囲を指定します。
  • Version【バージョン】(省略可)
    ピボットテーブルのバージョンを
    xlPivotTableVersionList列挙型の定数で指定します。

    xlPivotTableVersionList列挙型の主な定数
    定数内容
    xlPivotTableVersion112Excel 2003
    xlPivotTableVersion123Excel 2007
    xlPivotTableVersion144Excel 2010
    xlPivotTableVersion155Excel 2013
    6Excel 2016

※省略した場合はxlPivotTableVersion12(Excel2007)になりますが
Excel2016/2013/2010でも動作します。
Excel2016のバージョンを指定する場合は定数ではなく
値の6を指定してください。


CreatePivotTable【クリエイトピボットテーブル】メソッド

ピボットテーブルを作成するには
PivotCache【ピボットキャッシュ】オブジェクトの
CreatePivotTable【クリエイトピボットテーブル】メソッドを使用します。
PivotTableオブジェクトが作成され
作成されたPivotTableオブジェクトが返されます。

CreatePivotTable【クリエイトピボットテーブル】メソッドの書式

[]内は省略可能です。
オブジェクト.CreatePivotTable( TableDestination[,TableName] )


設定値(引数)の説明

  • オブジェクト(必須)
    PivotCache【ピボットキャッシュ】オブジェクトを指定します。
  • TableDestination【テーブルディスティネーション】(必須)
    作成するピボットテーブルの左上端のセルを指定します。
    ピボットテーブルキャッシュを作成したブック内のセルを指定してください。
  • TableName【テーブルネーム】(省略可)
    ピボットテーブル名を指定します。
    省略した場合は「ピボットテーブル1」のような名前が自動で設定されます。

PivotFields【ピボットフィールズ】メソッド

ピボットテーブルのフィールドを参照するには
PivotTable【ピボットテーブル】オブジェクトの
PivotFields【ピボットフィールズ】メソッドを使用します。

PivotFields【ピボットフィールズ】メソッドの書式

[]内は省略可能です。
オブジェクト.PivotFields([Index])


設定値(引数)の説明

  • オブジェクト(必須)
    PivotTableオブジェクトを指定します。
  • Index【インデックス】(省略可)
    参照したいフィールドのインデックス番号または、フィールド名を文字列で指定します。

Orientation【オリエンテーション】プロパティ

ピボットテーブルにフィールドを追加または、変更するには
PivotField【ピボットフィールド】オブジェクトの
Orientation【オリエンテーション】プロパティを使用します。
値の取得と設定ができます。

Orientation【オリエンテーション】プロパティの書式

【取得】
オブジェクト.Orientation
【設定】
オブジェクト.Orientation = 設定値


設定値の説明

  • オブジェクト(必須)
    PivotField【ピボットフィールド】オブジェクトを指定します。
    PivotField【ピボットフィールド】オブジェクトは
    PivotTable【ピボットテーブル】オブジェクトの
    PivotFields【ピボットフィールズ】メソッドで取得できます。
  • 設定値(必須)
    xlPivotFieldOrientation列挙型の定数で追加先のフィールドを指定します。

    xlPivotFieldOrientation列挙型の定数
    定数内容
    xlHidden0非表示
    xlRowField1
    xlColumnField2
    xlPageField3ページ
    xlDataField4

※xlHiden(非表示)を指定した場合は、指定したフィールドを
ピボットテーブルから削除します。
※xlDataField(値)を指定した場合は、Functionプロパティを
使用して集計方法を指定してください。


Function【ファンクション】プロパティ

ピボットテーブルの集計方法を指定するには
値フィールドを表す、PivotFieldオブジェクトに対して
Function【ファンクション】プロパティで集計方法を設定します。
値の取得および設定ができます。

Function【ファンクション】プロパティの書式

【取得】
オブジェクト.Function
【設定】
オブジェクト.Function = 設定値


設定値の説明

  • オブジェクト(必須)
    値フィールドを参照するPivotFieldオブジェクトを指定します。
  • 設定値(必須)
    集計方法をxlConsolidationFunction列挙型の定数で指定します。

    xlConsolidationFunction列挙型の定数
    定数内容
    xlSum-4157合計
    xlAverage-4106平均
    xlCount-4112個数
    xlCountNums-4113数値の個数
    xlMax-4136最大値
    xlMin-4139最小値
    xlProduct-4149積(掛け算)
    xlStDev-4155標本に基づく標準偏差
    xlStDevP-4156母集団全体に基づく標準偏差
    xlVar-4164標本に基づく変動
    xlVarP-4165母集団全体に基づく変動
    xlDistinctCount111Distinct Count 分析を使ったカウント
    xlUnknown1000小計に使用する関数は指定されません。

各店の7月度の事業別売上表

各店の7月度の事業別売上表からピボットテーブルを作成するコード

Sub ピボットテーブル作成()
Dim キャッシュ As PivotCache
Dim テーブル As PivotTable
Set キャッシュ = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=Range("A1").CurrentRegion)
Set テーブル = キャッシュ.CreatePivotTable _
(tabledestination:=Range("F1"), TableName:="7月度事業別売上")
With テーブル
.PivotFields("事業").Orientation = xlRowField
.PivotFields("店名").Orientation = xlColumnField
With .PivotFields("売上")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
End With
End With
End Sub

コードの解説

2行目
【Dim キャッシュ As PivotCache】

ピボットキャッシュを格納する
変数「キャッシュ」をオブジェクト型で宣言します。


3行目
【Dim テーブル As PivotTable】

ピボットテーブルを格納する
変数「テーブル」をオブジェクト型で宣言します。


4行目~5行目
Set キャッシュ = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=Range(“A1”).CurrentRegion)

Create【クリエイト】メソッドでPivotCache【ピボットキャッシュ】オブジェクトを取得して
Setキーワードを使用してオブジェクト変数「キャッシュ」に代入します。


6行目~7行目
【Set テーブル = キャッシュ.CreatePivotTable _
(tabledestination:=Range(“F1″), TableName:=”7月度事業別売上”)】

4行目~5行目で作成したピボットキャッシュから
CreatePivotTable【クリエイトピボットテーブル】メソッドを使用して
ピボットテーブルを作成してSetキーワードを使用して
オブジェクト変数「テーブル」に代入します。


8行目
【With テーブル】

PivotTableオブジェクトが格納されている
オブジェクト変数「テーブル」を
Withステートメントで指定します。


9行目
【.PivotFields(“事業”).Orientation = xlRowField】

PivotFields【ピボットフィールズ】メソッドで「事業」列を参照し
Orientation【オリエンティーション】プロパティで行フィールドに追加します。


10行目
【.PivotFields(“店名”).Orientation = xlColumnField】

「店名」列を参照して列フィールドに追加します。


11行目
【With .PivotFields(“売上”)】
PivotFields【ピボットフィールズ】メソッドで「売上」列を参照し
Withステートメントで指定します。


12行目
【.Orientation = xlDataField】

Withステートメントで指定した「売上」フィールドに対して
Orientationプロパティで「値」に指定します。


13行目
【.Function = xlSum】

「売上」フィールドに対して
Function【ファンクション】プロパティを使用して
集計方法を「合計」に指定します。


14行目
【.NumberFormat = “#,##0″】

「売上」フィールドに対して
NumberFormat【ナンバーフォーマット】プロパティを使用して
値を3桁区切りの書式を設定します。


実行結果


ピボットテーブルを操作する方法については
ピボットテーブルを操作する
ピボットグラフを作成する
ピボットテーブル を期間でグループ化して集計するをご覧ください。
以上で、ピボットテーブルを作成する方法についての
解説を終了します。
ありがとうございました。

スポンサーリンク
スポンサーリンク

フォローする

スポンサーリンク
スポンサーリンク