Excel VBA ピボットテーブルを操作する【小計非表示編】

前の記事で「従来のピボットテーブルレイアウトを使用する」に設定する方法を解説しました。設定する際に、行ラベルを2つ以上フィールドに設定していた場合、必要ない「小計」が勝手に設定されてしまうので困ります。では今回はVBAマクロで「小計を非表示にする」ように設定していこうと思います。

くるみこ

これも毎回手動で小計を非表示に設定していて、手間がかかっているのでVBAマクロで小計を非表示にする方法を勉強しましょう(^^)/

いいですね！私もこれ気になっていました(^^)　今回もよろしくお願いします！

【この記事でわかることは】
・VBAマクロでピボットテーブルの小計を非表示にする方法がわかります

前回記事のおさらいは、下のカードをクリックすれば開きます(^^ゞ

Excel VBA ピボットテーブルを操作する【オプション設定】

Excel VBA ピボットテーブルオプションで設定するプロパティやメソッドを知る方法がわかります。ピボットテーブルオプションの設定をVBAマクロで行う方法。「従来のピボットテーブルレイアウトを使用する」に設定する方法について解説しています

くるみこ

前回記事は、ピボットテーブルオプションの設定をVBAマクロで行う方法と「従来のピボットテーブルレイアウトを使用する」に設定する方法などについての解説記事でした。よければ覗いてみてね(^^)/

ボットテーブルの小計を非表示にする方法
まとめ（おわりに）
1. まとめと感想など
2. 今後の記事について
記事のサンプルファイルをダウンロードできます

ボットテーブルの小計を非表示にする方法

前回記事で「従来のピボットテーブルレイアウトを使用する」に設定した画像を見てください。

行ラベルの「年」と「四半期」に集計行が勝手に出来ています。これを非表示にする操作を「マクロの記録」で記録して調べてみましょう。

手動操作では［ピボットテーブルツール］－［デザイン］－［小計］－［小計を表示しない］選択でピボットテーブルの小計を非表示にできます。
※ 個別に解除するなら「ピボットテーブル」の該当行でマウスを右クリックして［小計］を解除します。

「マクロの記録」で記録できたコード

・［小計を表示しない］を記録したコードです。余白部分はカットしています。

Sub Macro1()
    ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("日付").Subtotals = Array(False, _
        False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("PCR 検査実施件数(単日)").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("PCR 検査陽性者数(単日)").Subtotals = _
        Array(False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("四半期").Subtotals = Array(False _
        , False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("年").Subtotals = Array(False, _
        False, False, False, False, False, False, False, False, False, False, False)
End Sub

小計が表示されていた部分だけでなく、ピボットテーブルの元データすべてのフィールドの Subtotals プロパティを、一つづつ False にしているコードですね。

ピボットテーブルの各 .PivotFields の .Subtotals を False にすればいいことがわかりました。

ピボットテーブルの小計を非表示にするコード

PivotField プロパティをリファレンスで調べると、「インデックスが True の場合、フィールドにその集計が表示されます。インデックス 1 (自動) が True である場合、その他のすべての値に False が設定されます。」となっていて、インデックスは12個あることがわかります。

だから、False が12個も並んでいたんですね。

これを踏まえて調整したコードがこちらです

'全フィールドの「小計を表示しない」ようにする
Sub HidepivfldSubtotals()
    Dim pivfld As PivotField
    On Error Resume Next
    For Each pivfld In ActiveSheet.PivotTables(1).PivotFields
        pivfld.Subtotals(1) = True
        pivfld.Subtotals(1) = False
    Next
    On Error GoTo 0
End Sub

・4行目、PivotField オブジェクトの変数を用意します。
・5行目、For Each～Nextで、PivotFieldsコレクション内の PivotField 数のループ処理です。
・6行目の「pivfld.Subtotals(1) = True」は、インデックス(1)の要素を一旦 True にしています。こうすることで、他の11個の要素が全て False に設定される仕様です。
・7行目で「pivfld.Subtotals(1) = False」であらためてインデックス(1)の要素も False にしています。これで全部が False になり、小計が非表示になりました！
・いろいろなケースでテスト実行を繰り返しました。そこで、小計の設定が無いフィールドでエラーが発生する場合があるので「On Error Resume Next」で回避設定しています。

では、前回のコードに組み入れてみましょう。

「従来のピボットテーブルレイアウトに変更する」マクロに組み入れます

・下のコードは同じプロシージャに「小計非表示」の処理を入れていますが、別プロシージャを Call して呼び出す形の方が良いでしょう。9～13行目を「Call HidepivfldSubtotals」とすればOKです。

'従来のピボットテーブルレイアウトに変更して小計を非表示にします
Sub pvtRowAxisTabularRow02()
    Dim pvt As PivotTable
    Set pvt = ActiveSheet.PivotTables(1)
    With pvt
        .InGridDropZones = True 'グリッド内でフィールドのドラッグを可能にするプロパティ
        .RowAxisLayout xlTabularRow  '表形式にするメソッド
    End With
    Dim pivfld As PivotField
    On Error Resume Next
    For Each pivfld In ActiveSheet.PivotTables(1).PivotFields
        pivfld.Subtotals(1) = True
        pivfld.Subtotals(1) = False
    Next
    On Error GoTo 0
End Sub

・実行動画がこちらです。

まとめ（おわりに）

・いかがでしたでしょうか？
・「いつも汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーを念頭に記事を書いています。
・今回も、記事内で使用したコードのサンプルファイルを登録していますのでご利用ください。
・今までの記事のサンプルも登録していますのでよろしければお使いください(^^)

まとめと感想など

くるみこ

どうですか。これで「従来のピボットテーブルレイアウトに変更する」で表形式にした場合の悩みが一つ改善できました(^^)

はい！　「False」が12個も出てきたときはどうなるのかと思いました。ヘルプで調べるのもだいぶ慣れてきました(^^)

くるみこ

もう少しピボットテーブルの勉強を継続していきます。楽しみにしていてね(^^)/

【今回分かったことは】
・VBAマクロでピボットテーブルの小計を非表示にする方法がわかりました

★★★ ランキング参加中！クリックしてね(^^)/ ★★★

にほんブログ村

Visual Basicランキング

今後の記事について

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m

【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にもご期待ください(^^)/