ExcelVBAとピボットテーブル：第１回　はじめに＆基本的な例

カテゴリー名：　［ExcelVBAとピボットテーブル］

2017/07/30

当シリーズでは、Excelのピボットテーブルをプログラミングのノウハウで生成するよう試みます。何回かに分けて掲載。

　主に VBAの記述方法を見ていきます。

　OLEを利用した場合の VBScript, JScript も掲載しますが、
その解説は要点のみとします。

　当Webページで紹介するマクロファイルやVBScriptのファイル一式は、 vovPIVOT01.zip という圧縮ファイルに同梱しておきます。

《このページの目次》

はじめに
1.　ピボットテーブル作成の基本的な流れ
2.　VBScript
3.　JScript
4.　データフィールドの配置変更(Excel2003との違い)

はじめに

　Excelのピボットテーブルは、集計表を簡単に作るためのものです。

　「性別、身長、体重」が400人分記録されているワークシートを元にして、
男女別に平均身長と平均体重を求めて表にする、といったことができます。

　ピボットテーブルの話題に入る前に、前提となる事柄を少々。

◇　実行環境

　マクロとスクリプトを実行した環境は次のとおり。

MS-Windows seven (64bit)
Excel2010
Script Engine Version: 5.8.18347, WSH Version: 5.8(18283)

　上記以外でも、Excelがインストールされていれば、たいていはマクロとスクリプトを動かせると思います。

　Excel2007以降で動作するものをサンプルとして掲げますが、
必要に応じて Excel2003との違いに触れたいとおもいます。

◇　SetExcelMacro_xlsm.vbsによるマクロの組み込み

　SetExcelMacro_xlsm.vbs を実行すると、
ファイル選択ダイアログボックスが出るので、
macro01.txtなどのマクロファイルを選びます。

　すると、そのマクロが組み込まれて Book1.xlsm というExcelファイルが作成されます。

　また、マクロには Control + j というショートカットキーが割り当てられます。

　Book1.xlsm をExcelで開いてから Control + j を入力するとマクロが実行されます。

　SetExcelMacro_xlsm.vbsを利用する場合は
あらかじめExcelのマクロセキュリティを緩めておく必要があります。

　ExcelをGUI操作して、次の設定変更を行います。具体的な操作方法は、Excelのバージョンによって違うのでここでは省略します。

「Visual Basic プロジェクトへのアクセスを信頼する」をonにする。
マクロのセキュリティレベルを調整する。「すべてのマクロを有効にする」にする。

　なお、手作業によるマクロの組込みが苦にならないなら、SetExcelMacro_xlsm.vbsを使う必要はありません。

◇　素材となるソースデータ

　ソースデータとして pt_source.xls を用意しました。

　中身は下のとおり。

ＩＤ	性別	身長	体重
C3	女性	159.1	57.8
W5	男性	163.8	78.2
W11	女性	162.7	59.5
H1	女性	157	59.6

　上の形式で Sheet1 に 400人分のデータが書かれています。

　セル範囲でいえば A1:D401 にデータが書かれています。

　このセル範囲には SourceDataRange という名前を付けてあります。
つまり、「名前付き範囲」になっています。

　ＩＤの列には空欄がありませんが、性別、身長、体重の列には少数ながら空欄があります。つまり、記載のない空白セルがあります。

　ピボットテーブルの数値の集計(平均値、標準偏差あるいは個数の数え上げなど)では、空欄を「存在しないもの」としてスキップするようです。

目次に戻る

1.　ピボットテーブル作成の基本的な流れ

　下の表を作る手順をたどってみます。

	値
行ラベル	平均身長	平均体重
女性	160.099479	55.495263
男性	166.715657	66.446269
(空白)	164.15	67.4
総計	163.46199	61.156743

　「(空白)」のところは、「性別」が空欄の人の集計値です。

　項目名を変更したくなるかもしれませんが、それは後回しにして、とりあえず上の表を作ります。

(1)　ピボットテーブルを作る手順とそのVBAマクロ

　ピボットテーブルを作る手順を箇条書きにすると次のとおり。

ソースデータが書かれている pt_source.xls のオープン
ピボットキャッシュの作成(ソースデータの指定)
ピボットテーブルオブジェクトの作成(テーブルの開始位置とテーブル名を指定)
ピボットフィールドの設定(横方向・縦方向に何を配置するかなどを指定)

　マクロ全体を掲げると下のとおり。

　マクロは Book1.xlsm に組み込まれており、
おなじフォルダに pt_source.xls があるとの前提です。

　ピボットテーブルは、Book1.xlsm の第１ワークシートに作ります。

 1Sub Macro1()
 2    Dim pName As String, bName As String
 3    Dim ptCache As PivotCache, ptObj As PivotTable
 4　　　　
 5    pName = ThisWorkbook.Path  ' 本ワークブックのフォルダ名
 6    bName = ThisWorkbook.Name  ' 本ワークブックの名前
 7    Workbooks.Open pName & "\pt_source.xls"  ' ソースデータを開く
 8    Workbooks(bName).Activate  ' 本ワークブックをアクティブに
 9    WorkSheets(1).Activate  ' 第１シートをアクティブに
10　　　　
11    Set ptCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
12        SourceData:="[pt_source.xls]Sheet1!SourceDataRange")
13    Set ptObj = ptCache.CreatePivotTable( _
14        TableDestination:="R1C1", TableName:="ピボット01")
15　　　　
16    ptObj.AddFields RowFields:="性別"
17    ptObj.AddDataField Field:=ptObj.PivotFields("身長"), _
18        Caption:="平均身長", Function:=xlAverage
19    ptObj.AddDataField Field:=ptObj.PivotFields("体重"), _
20        Caption:="平均体重", Function:=xlAverage
21End Sub

　最初の方では、ソースデータが書かれた pt_source.xls を開いた上で、
Book1.xlsm を再びアクティブにしています。

　ピボットテーブルに関する部分については以降で説明。

目次に戻る

(2)　ピボットキャッシュの作成

　ピボットキャッシュは、ソースデータを取り込むための枠組みです。通常、ワークブックに所属します。

　今回は Excelのワークブックをソースデータとして用いますが、Accessデータベースを利用することもできます。

　どんな種類のソースデータを利用するのか、
また、どの範囲を利用するのかを決めてキャッシュを設定します。

　該当のプログラム記述は下のとおり。

Set ptCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
    SourceData:="[pt_source.xls]Sheet1!SourceDataRange")

　上の SourceType:=xlDatabase がソースデータの種類の指定です。

　種類として次の値を指定できます。

xlDatabase：　Excelワークブックのデータ
xlConsolidation：　Excelの複数のワークシート範囲
xlExternal：　外部のアプリケーションのデータ

　xlExternal, xlConsolidation については、いずれ触れたいとおもいます。

　SourceData:="[pt_source.xls]Sheet1!SourceDataRange" というのは、
ソースデータとして、どのワークブックの、どのワークシートの、どのセル範囲を利用するかを指定するものです。

　もし Book1.xlsm の第１ワークシートにソースデータが書かれており、
第２シートにピボットテーブルを作るのであれば、
[pt_source.xls] という記述は不要です。

　SourceData:="Sheet1!SourceDataRange" と書くだけで大丈夫です。

　セル範囲は、R1C1:R401C4 のようにセル番地で指定することもできます。

　SourceData:="Sheet1!R1C1:R401C4" と書けます。

　ただし、番地で指定するよりセル範囲の名前で指定する方が、柔軟性を持たせることになります。

　ピボットテーブルは、更新(refresh)を簡単に行えます。

　ソースデータに変更があれば、refreshによりピボットテーブルの集計結果もそれに対応して変わります。

　その際、ソースデータの削除や追加によってセル範囲が変化してしまうと、
番地で指定している場合は意図したように更新されません。

　セル範囲の名前で指定していれば、ソースデータ側で名前付けを調整する必要はありますが、ピボットテーブル側では単にrefreshするだけで適切に更新されます。

目次に戻る

(3)　ピボットテーブルオブジェクトの作成

　ピボットキャッシュからピボットテーブルオブジェクトを作ります。

　一つのピボットキャッシュから複数のピボットテーブルオブジェクトを作ることができますが、まずは一つです。

　テーブルの開始番地、テーブルの名前の二つを指定します。

Set ptObj = ptCache.CreatePivotTable( _
    TableDestination:="R1C1", TableName:="ピボット01")

　上の TableDestination:="R1C1" は、アクティブシートの A1セルを開始位置にするとの指定です。

　TableDestination:=WorkSheets(3).Range("A1") とすれば、アクティブシートでなく第３シートの A1セルを開始位置にできます。

　ただ、ピボットテーブルにいろいろな処理を施すなら、アクティブシートにピボットテーブルを設けるのが無難だとおもいます。

　TableName:="ピボット01" は、テーブルの名前を指定するものです。

　省略してもかまいません。そのときは、「ピボット1」(半角カタカナ)のような適当な名前が割り当てられます。

　複数のテーブルを設ける場合は、そのうちの一つを呼び出すのにテーブル名を手がかりにします。

　なので、明示的にテーブル名を指定しておくのが無難かもしれません。

目次に戻る

(4)　ピボットフィールドの指定

　ピボットフィールドの指定は、テーブルの行と列をどんな形にするかの指定です。

　今回の「性別」の内訳は、女性・男性・空白(記載無し)の三種類ですが、これを縦に並べるか横に並べるかを指定しなければなりません。

　縦に並べるなら「行フィールド」にします。横に並べるときは「列フィールド」です。

　マクロ記述としては下のとおり。

ptObj.AddFields RowFields:="性別"

　上の RowFields を ColumnFields にすれば「列フィールド」になります。

　一方、「身長」と「体重」は、その内訳による分類に使う訳ではありません。

　「性別」とは違って、計算処理の手がかりにします。今回は平均値を算出します。

　このような数値計算のフィールドは、AddFields でなく AddDataField で指定します。

ptObj.AddDataField Field:=ptObj.PivotFields("身長"), _
    Caption:="平均身長", Function:=xlAverage
ptObj.AddDataField Field:=ptObj.PivotFields("体重"), _
    Caption:="平均体重", Function:=xlAverage

　上のようにすると、「平均身長」と「平均体重」が横方向に並べられます。

　上の Function に指定できるものとしては次のものがあります。

xlAverage　平均
xlCount　データの個数
xlCountNums　数値の個数
xlMax　最大値
xlMin　最小値
xlProduct　積
xlStDev　標本標準偏差
xlStDevP　標準偏差
xlSum　合計
xlVar　標本分散
xlVarP　分散

　以上で、とりあえずピボットテーブルが形になります。

目次に戻る

2.　VBScript

　前述のマクロと同じ処理を行う VBScript を掲げます。

　ワークブックにマクロを組み込む訳ではないので Book1.xlsx を生成します。

　xlDatabase などの定数は、constants_xl.vbs を取り込むことで利用可能にしています。constants_xl.vbsは zip圧縮ファイルに同梱。

 1Option Explicit
 2Dim fso, bookPath, srcPath
 3Dim exlApp, wbObj
 4Dim ptCache, ptObj
 5Include "constants_xl.vbs"
 6　　　　
 7Set fso = CreateObject("Scripting.FileSystemObject")
 8bookPath = fso.GetAbsolutePathName("Book1.xlsx")
 9If (fso.FileExists(bookPath) = True) Then fso.DeleteFile(bookPath)
10srcPath = fso.GetAbsolutePathName("pt_source.xls")
11Set exlApp = CreateObject("Excel.Application")  ' Excelの起動
12exlApp.Visible = True  ' Excelを見える状態に
13exlApp.Workbooks.Open srcPath
14Set wbObj = exlApp.Workbooks.Add()  ' Workbookの新規作成
15wbObj.WorkSheets(1).Activate
16Set ptCache = wbObj.PivotCaches.Create(xlDatabase, _
17    "[pt_source.xls]Sheet1!SourceDataRange")
18Set ptObj = ptCache.CreatePivotTable( _
19    "R1C1", "ピボット01")
20　　　　
21ptObj.AddFields "性別"
22ptObj.AddDataField ptObj.PivotFields("身長"), "平均身長", xlAverage
23ptObj.AddDataField ptObj.PivotFields("体重"), "平均体重", xlAverage
24wbObj.SaveAs bookPath, xlOpenXMLWorkbook
25exlApp.quit
26　　　　
27Sub Include(ByVal FileName)
28    Dim fso, FileObj, MyStr
29    Set fso = CreateObject("Scripting.FileSystemObject") 
30    Set FileObj = fso.OpenTextFile(fso.GetAbsolutePathName(FileName))
31    MyStr = FileObj.ReadAll()
32    FileObj.Close
33    Set fso = Nothing
34    Set FileObj = Nothing
35    ExecuteGlobal MyStr
36End Sub

　VBAのマクロでは ptObj.AddFields RowFields:="性別" のように
パラメータ名の RowFields を指定できますが、VBScript ではできません。

　パラメータを規定の順番に即して配置することで対応します。

　AddFields(RowFields, ColumnFields, PageFields, AddToTable)
というのが規定なので、今回は第１パラメータとして "性別" を置きました。

　ColumnFields にしたければ PTobj.AddFields ,"性別" と書きます。

目次に戻る

3.　JScript

　前述のマクロと同じ処理を行う JScript を掲げます。

　Book1.xlsx を生成します。

　xlDatabase などの定数は、constants_xl.js を取り込むことで利用可能にしています。

 1var fso, bookPath, srcPath;
 2var exlApp, wb;
 3var ptCache, ptObj;
 4eval(ReadFile("constants_xl.js"));
 5　　　　
 6fso = WScript.CreateObject("Scripting.FileSystemObject");
 7bookPath = fso.GetAbsolutePathName("Book1.xlsx");
 8if (fso.FileExists(bookPath))  fso.DeleteFile(bookPath);
 9srcPath = fso.GetAbsolutePathName("pt_source.xls");
10exlApp = WScript.CreateObject("Excel.Application");  // Excelの起動
11exlApp.Visible = true;  // Excelを見える状態に
12exlApp.Workbooks.Open(srcPath);
13wb = exlApp.Workbooks.Add();  // Workbookの新規作成
14wb.WorkSheets(1).Activate();
15　　　　
16ptCache = wb.PivotCaches().Create(xlDatabase,
17    "[pt_source.xls]Sheet1!SourceDataRange");
18ptObj = ptCache.CreatePivotTable(
19    "R1C1", "ピボット01");
20　　　　
21ptObj.AddFields("性別");
22ptObj.AddDataField(ptObj.PivotFields("身長"), "平均身長", xlAverage);
23ptObj.AddDataField(ptObj.PivotFields("体重"), "平均体重", xlAverage);
24wb.SaveAs(bookPath, xlOpenXMLWorkbook);
25exlApp.Quit();
26　　　　
27function ReadFile(filename) {
28    var fso = WScript.CreateObject("Scripting.FileSystemObject");
29    var path = fso.GetAbsolutePathName(filename);
30    var MyStr = null;
31    if (fso.FileExists(path)) {
32        var fobj = fso.OpenTextFile(path, 1);
33        MyStr = fobj.ReadAll();
34        fobj.Close();
35    }
36    return MyStr;
37}

　　JScriptを書く際につまずいたのは下の箇所です。

ptCache = wb.PivotCaches().Create(xlDatabase,
    "[pt_source.xls]Sheet1!SourceDataRange");

　PivotCaches().Create …… で、PivotCaches の後に括弧がないとエラーになるので要注意です。

目次に戻る

4.　データフィールドの配置変更(Excel2003との違い)

　今回、「身長」と「体重」は数値計算要のフィールドとして利用しました。
つまり、データフィールドに指定しました。

　結果、表の中で「平均身長」と「平均体重」が横方向に配置されました。

　では、「性別」を行フィールド(縦方向に並べる)でなく、列フィールドにした場合はどうなるでしょうか。

　Excel2003でピボットテーブルを設定したことのある人なら、
「平均身長」と「平均体重」が自動的に縦に配置されることを知っているでしょう。

　つまり、下のような表になります。

データ	女性	男性	(空白)	総計
平均身長	160.099479	166.715657	164.15	163.46199
平均体重	55.495263	66.446269	67.4	61.156743

　しかし、Excel2007以降ではそうなりません。

　「平均身長」と「平均体重」は、相変わらず横方向に配置されます。

　下のような表になります(数値は短縮して表示)。

女性		男性		(空白)		全体の平均身長	全体の平均体重
平均身長	平均体重	平均身長	平均体重	平均身長	平均体重
160.1	55.5	166.7	66.4	164.2	67.4	163.5	61.2

　これを Excel2003 の場合と同じように配置するにはどうするかですが、
データフィールドの設定が一通り済んでから次ぎの１行を置きます。

ptObj.DataPivotField.Orientation = xlRowField

　そうすると、データフィールドが行フィールドとして縦方向に配置されます。

　あらためて該当箇所を示すと次のとおり。

ptObj.AddFields ColumnFields:="性別"
ptObj.AddDataField Field:=ptObj.PivotFields("身長"), _
    Caption:="平均身長", Function:=xlAverage
ptObj.AddDataField Field:=ptObj.PivotFields("体重"), _
    Caption:="平均体重", Function:=xlAverage
ptObj.DataPivotField.Orientation = xlRowField

　本筋からは脱線ですが、Excel2003バージョンのピボットテーブルを作成すれば、
DataPivotField.Orientation を指定しなくても「平均身長」などが縦に並びます。

　ピボットキャッシュを作成するとき、第３のパラメータ Version を指定することで Excel2003 のピボットテーブルにすることができます。

　具体的には次のとおり。

Set ptCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
    SourceData:="[pt_source.xls]Sheet1!SourceDataRange", _
    Version:=xlPivotTableVersion11)

　バージョンとして指定できるのは下の定数です。

xlPivotTableVersion2000	0	Excel2000
xlPivotTableVersion10	1	Excel2002
xlPivotTableVersion11	2	Excel2003
xlPivotTableVersion12	3	Excel2007(default)
xlPivotTableVersion14	4	Excel2010
xlPivotTableVersion15	5	Excel2013

　あるいは、ピボットキャッシュを作るとき、Create でなく Add を使えば、バージョンを指定しなくても Excel2003バージョンになるようです。

Set ptCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
    SourceData:="[pt_source.xls]Sheet1!SourceDataRange")

　上のようにすれば Excel2003バージョンになります。

～　以上　～

追伸

　zip圧縮ファイルには macro01(基本形).txt, macro02(データフィールドの配置変更).txt, macro03(Excel2003バージョンで作成).txt が入っています。

　また、それぞれについて同じように動作する VBScript, JScript も含まれています。

ExcelVBAとピボットテーブル：第１回 はじめに＆基本的な例

《このページの目次》

はじめに

◇ 実行環境

◇ SetExcelMacro_xlsm.vbsによるマクロの組み込み

◇ 素材となるソースデータ

1. ピボットテーブル作成の基本的な流れ

(1) ピボットテーブルを作る手順とそのVBAマクロ

(2) ピボットキャッシュの作成

(3) ピボットテーブルオブジェクトの作成

(4) ピボットフィールドの指定

2. VBScript

3. JScript

4. データフィールドの配置変更(Excel2003との違い)

ExcelVBAとピボットテーブル：第１回　はじめに＆基本的な例

◇　実行環境

◇　SetExcelMacro_xlsm.vbsによるマクロの組み込み

◇　素材となるソースデータ

1.　ピボットテーブル作成の基本的な流れ

(1)　ピボットテーブルを作る手順とそのVBAマクロ

(2)　ピボットキャッシュの作成

(3)　ピボットテーブルオブジェクトの作成

(4)　ピボットフィールドの指定

2.　VBScript

3.　JScript

4.　データフィールドの配置変更(Excel2003との違い)