ExcelVBAとピボットテーブル:第1回 はじめに&基本的な例

カテゴリー名: [ExcelVBAとピボットテーブル

2017/07/30

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

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

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

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

カフェ・ホテル・空港の無料Wi-Fiから安全ネット接続【アバスト セキュアラインVPN】


《このページの目次》


    

はじめに

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

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

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

楽天ブックス

◇ 実行環境

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

 上記以外でも、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のバージョンによって違うのでここでは省略します。

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

    

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

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

 中身は下のとおり。

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

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

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

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

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

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

目次に戻る


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

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

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

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

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

ネスレ通販

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

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

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

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

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

 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  ' 第1シートをアクティブに
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 がソースデータの種類の指定です。

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

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

    

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

 もし Book1.xlsm の第1ワークシートにソースデータが書かれており、
第2シートにピボットテーブルを作るのであれば、
[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") とすれば、アクティブシートでなく第3シートの A1セルを開始位置にできます。

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

    

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

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

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

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

目次に戻る


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

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

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

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

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

ptObj.AddFields RowFields:="性別"

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

    

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

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

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

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

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

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

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

目次に戻る


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)
というのが規定なので、今回は第1パラメータとして "性別" を置きました。

 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 の場合と同じように配置するにはどうするかですが、
データフィールドの設定が一通り済んでから次ぎの1行を置きます。

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 を指定しなくても「平均身長」などが縦に並びます。

 ピボットキャッシュを作成するとき、第3のパラメータ 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 も含まれています。


※ 当ページに掲載した広告一覧