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

    xlPivotTableVersionList列挙型の主な定数
    定数 内容
    xlPivotTableVersion11 2 Excel 2003
    xlPivotTableVersion12 3 Excel 2007
    xlPivotTableVersion14 4 Excel 2010
    xlPivotTableVersion15 5 Excel 2013
    6 Excel 2016

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


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

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

CreatePivotTable【クリエイトピボットテーブル】メソッドの書式と設定値(引数)の説明

[]内は省略可能です。
オブジェクト.CreatePivotTable( TableDestination[,TableName] )
【戻り値】PivotTable【ピボットテーブル】オブジェクト

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

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

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

PivotFields【ピボットフィールズ】メソッドの書式と設定値(引数)の説明

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

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

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

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

Orientation【オリエンテーション】プロパティの書式と設定値の説明

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

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

    xlPivotFieldOrientation列挙型の定数
    定数 内容
    xlHidden 0 非表示
    xlRowField 1
    xlColumnField 2
    xlPageField 3 ページ
    xlDataField 4

※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 母集団全体に基づく変動
    xlDistinctCount 111 Distinct Count 分析を使ったカウント
    xlUnknown 1000 小計に使用する関数は指定されません。

各店の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】
ピボットキャッシュを格納する変数「キャッシュ」をオブジェクト型(PivotCache)で宣言します。


3行目【Dim テーブル As PivotTable】
ピボットテーブルを格納する変数「テーブル」をオブジェクト型(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桁区切りの書式を設定します。


実行結果


ピボットテーブルを操作する方法については
ピボットテーブルを操作する
ピボットグラフを作成する
ピボットテーブル を期間でグループ化して集計するをご覧ください。


以上で、ピボットテーブルを作成する方法についての解説を終了します。ありがとうございました。

スポンサーリンク

関連記事・広告