ExcelVBAとピボットテーブル:第2回 項目や数値桁数の表示に関する調整

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

2017/07/30

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

 今回は「第1回 はじめに&基本的な例」で作成した表に修正を施します。

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

    


《このページの目次》


    

1. AddFields,AddDataFieldを使わない書き方

 今回の本論に入る前に、ピボットフィールドの設定に AddFields,AddDataFieldを使わない書き方をみておきます。

 ピボットフィールドに関していろいろ調整を加える場合、その方が便利だとおもいます。

 まず、「第1回 はじめに&基本的な例」の該当箇所を引き合いに出して、
それをどのように書き換えるか示します。

    

ptObj.AddFields RowFields:="性別"

 上の1行を下の3行に変更します。

With ptObj.PivotFields("性別")
    .Orientation = xlRowField
End With

 Orientationプロパティは、フィールドの方向付けを指定するものです。

 xlRowField だと、縦方向(行フィールド)になります。

    

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

 上の2行は下のようにします。

With ptObj.PivotFields("身長")
    .Orientation = xlDataField
    .Function = xlAverage
    .Caption = "平均身長"
End With

 Orientationプロパティに xlDataField を代入すると、データフィールドになります。

 「方向付け」という意味合いからはそれてしまいますが、こういう書き方をするようです。

 「体重」についてのフィールドも同じ書き方になりますが、念のため掲げておきます。

With ptObj.PivotFields("体重")
    .Orientation = xlDataField
    .Function = xlAverage
    .Caption = "平均体重"
End With

 今回は、この With を用いた書き方を使います。


2. 項目名に関する調整

 第1回で作成した表は下のとおりです。

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

 この表で、「女性」と「男性」の順番の入れ替えも行います。

 また、「(空白)」 → 「記載無し」
「総計」 → 「全体」に変更する方法を記します。

    

(1) 項目の順番の入れ替え

 「女性」とか「男性」という項目のことをピボットアイテムといいます。

 「性別」に関するピボットフィールドが変数 ptFld に代入されている場合、

 ptFld.PivotItems(1) あるいは ptFld.PivotItems("女性") というのは
第1のピボットアイテム、つまり「女性」のアイテムを指し示します。

 そして、アイテムの順番は、Positionプロパティで調整できます。

 調整なしの場合、「女性」が1番で「男性」が2番です。

 おそらく、漢字に割り当てられているコード(数値)の順番になるのだとおもいます。

.PivotItems("女性").Position = 2
.PivotItems("男性").Position = 1

 上のように記述すれば、「男性」が1番・「女性」が2番になります。

 該当の With の部分を掲げると次のとおり。

With ptObj.PivotFields("性別")
    .Orientation = xlRowField
    .PivotItems("女性").Position = 2
    .PivotItems("男性").Position = 1
End With

目次に戻る


(2) 項目名の変更

 「性別」に関するピボットフィールドが変数 ptFld に代入されている場合、

ptFld.PivotItems(3).Name = "記載無し"

 上のようにすると、3番目のアイテムが「記載無し」に変更されます。

 つまり、「(空白)」が「記載無し」になります。

 Nameプロパティがアイテムの名前を扱うためのものです。代入と参照の両方可能。

    

 ピボットアイテムは、PivotItems("女性") のように番号でなく名前で指定することも可能です。

 なので、ptFld.PivotItems("女性").Name = "female" のようにしてその名前を変更することができます。

 しかし、ややこしいことに次の記述はエラーになります。

ptFld.PivotItems(“(空白)”).Name = “記載無し”

 下のように書かなければなりません。

ptFld.PivotItems("(blank)").Name = "記載無し"

 もしかすると Excel2013以降では「(空白)」が使えるようになっているのかもしれませんが、すくなくとも Excel2010 では「(blank)」でないとダメみたいです。

目次に戻る


(3) 「総計」および「行ラベル」の欄は別扱い

 「総計」というのはピボットアイテムではありません。

 「総計」が4番目に出てくるからといって

ptFld.PivotItems(4).Name = "全体"

 上のように記述してもエラーになります。

 「総計」は、ピボットフィールドではなく、ピボットテーブルオブジェクトに所属する名前のようです。

 変数 ptObj にピボットテーブルオブジェクトが代入されている場合、次のように書いて変更します。

ptObj.GrandTotalName = "全体"

    

 「女性」の上に位置する「行ラベル」もピボットアイテムではありません。

 詳細は省略しますが、この欄は LabelRange によって変更できます。

 「行ラベル」 → 「性別」に変更するなら次のようにします。

With ptObj.PivotFields("性別")
    .Orientation = xlRowField
    .LabelRange.Value = "性別"
End With

 上は .LabelRange.Value = .Name とした方が汎用的でしょうか。

 ピボットフィールドの名前が「性別」でないケースでも適用できます。

    

 ちなみに、「平均身長」の上に位置する「値」は、データフィールドのラベルなので、データフィールドの設定が済んだあとで下のように書けば変更できます。

ptObj.DataPivotField.LabelRange.Value = "算出値"

目次に戻る


(4) 数値の表示桁数の調整

 今回は、「身長」 「体重」の両フィールドとも、数値として平均値を表示しています。

 調整なしの場合、小数点以下が6桁くらい表示されるケースが多いとおもいます。

 それだと桁数が多いので、ピボットフィールドの NumberFormatプロパティで調整します。

 小数点以下を1桁にするなら下のとおり。

.NumberFormat = "0.0"

 1行だけだと分かりにくいかもしれないので該当箇所を掲げると次のようになります。

With ptObj.PivotFields("身長")
    .Orientation = xlDataField
    .Function = xlAverage
    .Caption = "平均身長"
    .NumberFormat = "0.0"
End With

    

 NumberFormatプロパティに代入する文字列をちゃんと説明しようとすると長くなりますが、おおよそ次のような感じです。


3. VBAマクロ

 これまで述べてきた事柄を踏まえて、項目や数値の表示の調整を行う VBAマクロを掲げます。

 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    With ptObj.PivotFields("性別")
17        .Orientation = xlRowField
18        .PivotItems("女性").Position = 2
19        .PivotItems("男性").Position = 1
20        .PivotItems(3).Name = "記載なし"
21        .LabelRange.Value = .Name  ' 「行ラベル」→「性別」
22    End With
23    With ptObj.PivotFields("身長")
24        .Orientation = xlDataField
25        .Function = xlAverage
26        .Caption = "平均身長"
27        .NumberFormat = "0.0"
28    End With
29    With ptObj.PivotFields("体重")
30        .Orientation = xlDataField
31        .Function = xlAverage
32        .Caption = "平均体重"
33        .NumberFormat = "0.0"
34    End With
35    
36    ptObj.GrandTotalName = "全体"  ' 「総計」→「全体」
37    ptObj.DataPivotField.LabelRange.Value = "平均値"  ' 「値」→「平均値」
38End Sub

4. VBScript

 先の VBAマクロと同じ働きをする VBScript を掲げます。

 Book1.xlsx が生成されます。

 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    
21With ptObj.PivotFields("性別")
22    .Orientation = xlRowField
23    .PivotItems("女性").Position = 2
24    .PivotItems("男性").Position = 1
25    .PivotItems(3).Name = "記載なし"
26    .LabelRange.Value = .Name  ' 「行ラベル」→「性別」
27End With
28With ptObj.PivotFields("身長")
29    .Orientation = xlDataField
30    .Function = xlAverage
31    .Caption = "平均身長"
32    .NumberFormat = "0.0"
33End With
34With ptObj.PivotFields("体重")
35    .Orientation = xlDataField
36    .Function = xlAverage
37    .Caption = "平均体重"
38    .NumberFormat = "0.0"
39End With
40ptObj.GrandTotalName = "全体"  ' 「総計」→「全体」
41ptObj.DataPivotField.LabelRange.Value = "平均値"  ' 「値」→「平均値」
42wbObj.SaveAs bookPath, xlOpenXMLWorkbook
43exlApp.quit

目次に戻る


5. JScript

 先の VBAマクロと同じ働きをする JScript を掲げます。

 Book1.xlsx が生成されます。

 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    
21with (ptObj.PivotFields("性別")) {
22    Orientation = xlRowField;
23    PivotItems("女性").Position = 2;
24    PivotItems("男性").Position = 1;
25    PivotItems(3).Name = "記載なし";
26    LabelRange.Value = Name;  // 「行ラベル」→「性別」
27}
28with (ptObj.PivotFields("身長")) {
29    Orientation = xlDataField;
30    Function = xlAverage;
31    Caption = "平均身長";
32    NumberFormat = "0.0";
33}
34with (ptObj.PivotFields("体重")) {
35    Orientation = xlDataField;
36    Function = xlAverage;
37    Caption = "平均体重";
38    NumberFormat = "0.0";
39}
40ptObj.GrandTotalName = "全体";  // 「総計」→「全体」
41ptObj.DataPivotField.LabelRange.Value = "平均値";  // 「値」→「平均値」
42wb.SaveAs(bookPath, xlOpenXMLWorkbook);
43exlApp.Quit();

目次に戻る


6. 特定の項目を非表示にする

 特定の項目を非表示にして、次のような表を作ります。

  平均値  
性別 平均身長 平均体重
男性 166.7 66.4
女性 160.1 55.5

    

 「性別」の欄に記載のない「(空白)」を非表示にすることができます。

 ピボットアイテムの Visibleプロパティを False にすると、その項目が非表示になります。具体的には下のとおり。

.PivotItems(3).Visible = False

 該当の Withの箇所を掲げると次のとおり。

With ptObj.PivotFields("性別")
    .Orientation = xlRowField
    .PivotItems("女性").Position = 2
    .PivotItems("男性").Position = 1
    .PivotItems(3).Visible = False
    .LabelRange.Value = .Name
End With

 上は、あくまで非表示にする方法です。

 集計対象から除外するというのとは違います。

    

 全体を意味する「総計」の欄を非表示にする場合は、ピボットアイテムではなく
ピボットテーブルオブジェクトのプロパティを使います。

ptObj.ColumnGrand = False

 上の1行を加えると、「総計」が非表示になります。

 プロパティとして ColumnGrand のほかに RowGrand というのもあります。

 今回の「総計」は横方向に広がるものなので ColumnGrand を使います。

 縦方向に広がる「総計」の場合は RowGrand を使います。

 クロス集計表だと両方向の「総計」ができるとおもいますが、両方とも非表示にする場合は下の2行を書きます。

ptObj.ColumnGrand = False
ptObj.RowGrand = False

    

 非表示にするための VBAマクロを掲げます。

 VBScript, JScript は掲げませんが、zip圧縮ファイルに同梱しておきます。

 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    With ptObj.PivotFields("性別")
17        .Orientation = xlRowField
18        .PivotItems("女性").Position = 2
19        .PivotItems("男性").Position = 1
20        .PivotItems(3).Visible = False  ' 非表示の設定
21        .LabelRange.Value = .Name  ' 「行ラベル」→「性別」
22    End With
23    With ptObj.PivotFields("身長")
24        .Orientation = xlDataField
25        .Function = xlAverage
26        .Caption = "平均身長"
27        .NumberFormat = "0.0"
28    End With
29    With ptObj.PivotFields("体重")
30        .Orientation = xlDataField
31        .Function = xlAverage
32        .Caption = "平均体重"
33        .NumberFormat = "0.0"
34    End With
35    
36    ptObj.ColumnGrand = False  ' 「総計」の欄を非表示
37    ptObj.DataPivotField.LabelRange.Value = "平均値"  ' 「値」→「平均値」
38End Sub

〜 以上 〜