ピボットテーブルを作成する方法
ピボットテーブルは、データの集計や分析をするのに便利な機能です。
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
ピボットキャッシュを格納する変数「キャッシュ」をオブジェクト型(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桁区切りの書式を設定します。
実行結果
ピボットテーブルを操作する方法については
ピボットテーブルを操作する
ピボットグラフを作成する
ピボットテーブル を期間でグループ化して集計するをご覧ください。
以上で、ピボットテーブルを作成する方法についての解説を終了します。ありがとうございました。