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

ここまでピボットテーブルの作成から更新までを解説してきました。でもピボットテーブルのスタイル設定などは何も行っていませんでした。
そこで、今回は「ピボットテーブルオプション」を操作して色々な設定変更にチャレンジしていきます。

くるみこ

毎回設定しているような、よく設定するオプションをVBAマクロで自動設定するようにしておけば時短になりますよね。頑張って勉強しましょう(^^)/

わかりました～(^^)　今回もよろしくお願いします！

【この記事でわかることは】
・ピボットテーブルオプションのプロパティやメソッドを知る方法
・ピボットテーブルオプションの設定をVBAマクロで行う方法
・「従来のピボットテーブルレイアウトを使用する」に設定する方法

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

Excel VBA ピボットテーブルを操作する【セル範囲変更編】

Excel VBA ピボットテーブルの元データがセル範囲の場合、データの追加などの変更を反映させるにはChangePivotCacheメソッドを使います。サンプルコードを使ってその方法を解説しています。ピボットキャッシュのサイズ検証も解説。

www.kurumico.com

くるみこ

前回記事は、ピボットテーブルの元データがセル範囲だった場合の更新方法についての解説記事でした。よければ覗いてみてね(^^)/

ピボットテーブルオプション設定を調べる

ピボットテーブルオプション各項目の設定方法は、「マクロの記録」を使って調べましょう。
ここでは、自分がよく設定している項目だけ表示しておきます。そのほかのオプション設定を知りたい場合は必要に応じて自分で調べてみてくださいね(^^)

【ピボットテーブルオプション】

「マクロの記録」で次のような、ピボットテーブルのオプションやメソッドがわかりました。

・ピボットテーブル名の変更
　　　　ActiveSheet.PivotTables(“ピボットテーブル1”).Name = “PivotTable100”

・レイアウトと書式
　　更新時に列幅を自動調整する
　　　　ActiveSheet.PivotTables(“ピボットテーブル1”).HasAutoFormat = False
　　更新時にセル書式を保持する
　　　　ActiveSheet.PivotTables(“ピボットテーブル1”).PreserveFormatting = False

・集計とフィルター
　　行の総数を表示する
　　　　ActiveSheet.PivotTables(“ピボットテーブル1”).ColumnGrand = False
　　列の総数を表示する
　　　　ActiveSheet.PivotTables(“ピボットテーブル1”).RowGrand = False

・表示
　　従来のピボットテーブルレイアウトを使用する
　　（グリッド内でのフィールドのドラッグが可能）
　　　　With ActiveSheet.PivotTables(“ピボットテーブル1”)
　　　　　　.InGridDropZones = True
　　　　　　.RowAxisLayout xlTabularRow
　　　　End With

・印刷

・データ
　　ファイルに元のデータを保存する
　　　　ActiveSheet.PivotTables(“ピボットテーブル1”).SaveData = True
　　　　※このオプションを変更すると注意メッセージダイアログが表示されます。
　　ファイルを開くときにデータを更新する
　　　　ActiveSheet.PivotTables(“ピボットテーブル1”). _
　　　　　　　　　　　　　　　　PivotCache.RefreshOnFileOpen = True
　　　　※このオプションを変更すると注意メッセージダイアログが表示されます。

・代替テキスト

ピボットテーブルツール_デザインタブ_レイアウト設定

次は、ピボットテーブルを選択して［ピボットテーブル ツール］の［デザイン］タブの中の「レポートのレイアウト］をクリックして設定できる項目です。

次のように「RowAxisLayout メソッド」で設定されています。

・コンパクト形式で表示：
　　ActiveSheet.PivotTables(“ピボットテーブル1”).RowAxisLayout xlCompactRow

・アウトライン形式で表示：
　　ActiveSheet.PivotTables(“ピボットテーブル1”).RowAxisLayout xlOutlineRow

・表形式で表示：
　　ActiveSheet.PivotTables(“ピボットテーブル1”).RowAxisLayout xlTabularRow

・アイテムのラベルを繰り返す：
　　ActiveSheet.PivotTables(“ピボットテーブル1”).RepeatAllLabels xlRepeatLabels

スポンサーリンク

Amazonセールで上手に買い物する【必須情報】

Amazonセールで上手に買い物するための「必須情報」です。プライムデーやブラックフライデーなどのビッグセールのほかタイムセールなどをお得に利用できるようにするための情報が満載です。記事内にはAmazonの該当ページへのリンクを豊富に設置

www.kurumico.com

従来のピボットテーブルレイアウトを使用する

ピボットテーブルを作成したら表示は「コンパクト形式」になっています。行ラベルを2つ以上フィールドに設定した場合、「コンパクト形式」は下の図のように、行ラベルが縦に表示されています。

「従来のピボットテーブルレイアウトを使用する」というオプションをオンにして表示形式を変更すると、従来の横並びにすることができます。

私は「コンパクト形式」の表示が嫌いです。毎回必ず「表形式」に変換して使っています。
コンパクト形式のまま使っている方はどのくらいの割合いるのでしょうか？
多分、従来の形式にするやり方がわからないか、面倒なのでそのまま使っている方が多いのではないかと勝手に思っています。

でも、これを毎回手動で「表形式」に変換するのはすごく手間です。なので、先ほどのコードを使って、次のようなマクロを実行すれば一発で変換できます。

従来のピボットテーブルレイアウトに変更するマクロ

'従来のピボットテーブルレイアウトに変更するマクロ
Sub pvtRowAxisTabularRow()
    Dim pvt As PivotTable
    Set pvt = ActiveSheet.PivotTables(1)
    With pvt
        .InGridDropZones = True 'グリッド内でフィールドのドラッグを可能にするプロパティ
        .RowAxisLayout xlTabularRow  '表形式にするメソッド
    End With
End Sub

・４行目で、ピボットテーブルをオブジェクト変数に代入しています。
・６行目は、InGridDropZonesプロパティをグリッド内でフィールドのドラッグを可能にしています。（不要ならばこの部分はカットしても良いでしょう）
・７行目が、RowAxisLayoutメソッドで表形式（xlTabularRow）表示に設定しています。
・次の例のようにこのマクロを利用して、設定したいオプションを追加して実行してもOKです。

他のオプションも一緒に設定する例

・総計を非表示にする設定を追加したサンプルコード

'総計を表示しない設定を追加したマクロ
Sub setting_pivot_Format()
    Dim pvt As PivotTable
    Set pvt = ActiveSheet.PivotTables(1)
    With pvt
        .InGridDropZones = True 'グリッド内でフィールドのドラッグを可能にするプロパティ
        .RowAxisLayout xlTabularRow  '表形式にするメソッド
        .ColumnGrand = False         '列の総計を削除
        .RowGrand = False            '行の総計を削除
    End With
End Sub

「コンパクト形式」にする（戻す）マクロ

・「コンパクト形式」に戻すマクロはこちらです。一応書いておきます。

'従来のピボットテーブルレイアウトに変更するマクロ
Sub pvtRowAxisCompactRow()
    Dim pvt As PivotTable
    Set pvt = ActiveSheet.PivotTables(1)
    With pvt
        .InGridDropZones = False 'グリッド内でフィールドのドラッグを不可にする
        .RowAxisLayout xlCompactRow  'コンパクト表示形式にする
        .ColumnGrand = True          '列の総計を表示
        .RowGrand = True             '行の総計を表示
    End With
End Sub

まとめ（おわりに）

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

まとめと感想など

くるみこ

いつも作ったピボットテーブルは必ず「従来のピボットテーブルレイアウトに変更する」で表形式にしていたのをマクロに設定してみました(^^)

はい！　自分の好みに設定して使う方法が良くわかりました(^^)　これですごく使い勝手が良くなりそうですね！

くるみこ

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

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

今後の記事について

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

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

スポンサーリンク

記事のサンプルファイルをダウンロードできます

今回の記事のサンプルをダウンロードできるようリンク先にとうろくしています！

過去の記事で使用したサンプルファイルがダウンロードできるページを設置しています
こちら（このリンク先）からご利用ください

【Excel VBA】サンプルファイル_ダウンロードのページ

【Excel VBA】の記事内で使ったサンプルファイルを（Zipファイルで）ダウンロードできるようにしました。記事内では細かい部分の解説が不十分ですが、実際にファイルをダウンロードして実行することで使えるのかどうか見ていただけたら幸いです。

www.kurumico.com