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

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

2017/07/30

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

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

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

《このページの目次》

1.　AddFields，AddDataFieldを使わない書き方
2.　項目名に関する調整
3.　VBAマクロ
4.　VBScript
5.　JScript
6.　特定の項目を非表示にする

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

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

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

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

ptObj.AddFields RowFields:="性別"

　上の１行を下の３行に変更します。

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

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

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

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

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

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

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

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

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

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

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

2.　項目名に関する調整

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

目次に戻る

(2)　項目名の変更

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

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

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

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

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

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

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

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

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

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

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

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

目次に戻る

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

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

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

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)　数値の表示桁数の調整

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

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

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

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

.NumberFormat = "0.0"

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

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

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

数字0(ゼロ)：　数字１桁(0でも表示)。 “0.00”
＃(シャープ)：　数字１桁(0なら表示しない)。 “##0.0#”
？(疑問符)：　小数点位置がそろうように調整(空白を補う)。 “??.??”
，(カンマ)：　1000単位の区切り記号挿入。 “#,#”　”#,#,千円”

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  ' 第１シートをアクティブに
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

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

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

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

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

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

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  ' 第１シートをアクティブに
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

～　以上　～

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

《このページの目次》

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

2. 項目名に関する調整

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

(2) 項目名の変更

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

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

3. VBAマクロ

4. VBScript

5. JScript

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

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

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

2.　項目名に関する調整

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

(2)　項目名の変更

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

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

3.　VBAマクロ

4.　VBScript

5.　JScript

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