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

スポンサーリンク

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

くるみこ
くるみこ

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

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

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

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

くるみこ
くるみこ

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

スポンサーリンク

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

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

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

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

・ピボットテーブル名の変更
    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

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

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

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

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

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

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

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

・4行目で、ピボットテーブルをオブジェクト変数に代入しています。
・6行目は、InGridDropZonesプロパティをグリッド内でフィールドのドラッグを可能にしています。(不要ならばこの部分はカットしても良いでしょう)
・7行目が、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マクロで行う方法がわかりました
・「従来のピボットテーブルレイアウトを使用する」に設定する方法と戻す方法やその他の設定も同時に実行する方法がわかりました

今後の記事について

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

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

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

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

スポンサーリンク

スポンサーリンク