ピボットテーブルの見栄え良くしたいとき、基本はピボットテーブルツールの「デザイン」タブの中にある「ピボットテーブルスタイル」一覧から選択していると思います。
実は、その他にExcel2003で実装されていた「オートフォーマット」という機能も使えるんです。今回は、その方法を解説します。
ピボットテーブルを見栄えよくするには「ピボットテーブルスタイル」からさらに細かい部分の書式を変更したりして結構大変です。今回は、Excelに隠されている機能「オートフォーマット」について解説します(^^)/
センスが無いから一番苦労する部分なんですよね(^^;
今回もよろしくお願いします！
【この記事でわかることは】
・ピボットテーブルのスタイル設定にオートフォーマット機能が使えます
・オートフォーマットを使えば見栄えの良い書式設定が一瞬で完了します
前回記事のおさらいは、下のカードをクリックすれば開きます(^^ゞ
前回記事は、ピボットテーブルの数値データを3桁ごとのカンマ区切りとマイナス数値を赤色表示にする方法の解説でした。よければ覗いてみてね(^^)/
オートフォーマットを使えるようにします
オートフォーマットとは
オートフォーマットとは、あらかじめ用意されたフォーマットを使って見栄えの良い表を簡単に作成する機能です。この機能は、Excel2003に実装されていた機能ですが、Excel2007以降は現在のように「ピボットテーブルスタイル」や「テーブルスタイル」を使って書式設定するように変わりました。
「オートフォーマット」は無くなったと思っていたのですが、実はまだ機能として残っていました。のでしょうか？はコマンドとして残っており、リボンに追加することで利用することが可能です。
「オートフォーマット」リボンを追加します
・まず「ファイルタブ」をクリックし、次に一番下の「オプション」をクリックします。
・すると下図の「Excelのオプション」ダイアログが表示されるので番号順に操作します。
❶ 「リボンのユーザー設定」を選択します。
❷ コマンドの選択で、「すべてのコマンド」か「リボンにないコマンド」を選択します。
❸ 「オートフォーマット」がありました。
❹ リボンのユーザー設定で「オートフォーマット」ボタンを登録したいタブやグループを選択します。「新しいタブ」と「新しいグループ」を選択追加して必要に応じて名前を変更します。
❺ ここでは「ホーム」タブの下に「オートフォーマット」という名前に変更した新しいグループが作成されています。
❻ これでリボン設置の準備できたので「追加」ボタンを押して「オートフォーマット」リボンを追加します。
❼ 「OK」ボタンで無事作業完了です。
・リボンが追加されていることが確認できました。
オートフォーマットを利用してみます
・「レポート」と「テーブル」がそれぞれ10種類づつあるのがわかるでしょうか。
・やはり古いスタイルなので、現行の「ピボットテーブルスタイル」のようにマウスを乗せるとプレビューが表示されるようにはなっていません。
・Excel2003当時のものを見ると、ダイアログの左下に「オプション」設定ができるようにボタンが設置されていましたが、無くなっているようです。
・ただ「ピボットテーブルスタイル」のものとは違うスタイルなので選択幅が広がります。
「マクロの記録」で動作を記録してみます
・「オートフォーマット」と「ピボットテーブルスタイル」の動作の違いを確認します。
「オートフォーマット」を選択したコード
Sub Macro1()
Range("H11").Select
ActiveSheet.PivotTables("ピボットテーブル1").PivotSelect "", xlDataAndLabel, True
ActiveSheet.PivotTables("ピボットテーブル1").Format xlReport6
End Sub・自動的にピボットテーブルが全選択されてから「オートフォーマット」を適用しています。
「ピボットテーブルスタイル」を選択したコード
Sub Macro2()
Range("H11").Select
ActiveSheet.PivotTables("ピボットテーブル1").Format xlPTNone
ActiveSheet.PivotTables("ピボットテーブル1").TableStyle2 = "PivotStyleMedium5"
End Sub・こちらはピボットテーブルは全選択されていません。
・最初に「.Format xlPTNone」でフォーマットを「なし」を適用しています。
・そのあとに選択した「ピボットテーブルスタイル」を適用しています。
オートフォーマット「レポート」の気になった挙動
・選択するオートフォーマットの「レポート」を選択するとレイアウトが狂うようです。
・「列ラベル」に設定していた項目が勝手に「行フィールド」に移動されていました。
・次の動画で「列」にある「エリア」が設定後「行」に移動しているのがわかります(-_-;)
・オートフォーマットの「レーポート」を使いたかったのにこれではダメですね！
・諦めずに何とかできないか検討してみましょう。
・どうやら「レポート」を後から設定するとダメな感じですね！
VBAで「オートフォーマット」の「スタイル」を設定
・VBAマクロで動作検証してみます。
「レポート」設定後に「列」フィールドを再設定する
'オートフォーマットサンプル（後から設定）
Sub pivAutoFormatSample01()
ActiveWorkbook.PivotCaches.Create(xlDatabase, "テーブル1") _
.CreatePivotTable Sheets.Add.Range("A1")
With ActiveSheet.PivotTables(1)
.PivotFields("入荷日").Orientation = xlRowField
.PivotFields("入荷日").DataRange.Item(1).Group _
periods:=Array(False, False, False, _
False, True, True, True)
With .PivotFields("四半期")
.PivotItems("Qtr1").Caption = "第4四半期"
.PivotItems("Qtr2").Caption = "第1四半期"
.PivotItems("Qtr3").Caption = "第2四半期"
.PivotItems("Qtr4").Caption = "第3四半期"
End With
.PivotFields("品名").Orientation = xlRowField
.PivotFields("エリア").Orientation = xlColumnField
With .PivotFields("数量")
.Orientation = xlDataField
.NumberFormat = "#,##0_ "
End With
With .PivotFields("金額")
.Orientation = xlDataField
.NumberFormat = "#,##0_ "
End With
.Format xlReport6 'オートフォーマットをここで適用
With .PivotFields("エリア")
.Orientation = xlColumnField
.Position = 1
End With
End With
End Sub
・26行目で、「オートフォーマット」の「レポート6」を適用しています。
・27行目から「列」フィールドの項目を（ポジション１で）再配置しています。
「レポート」をフィールド設定前に適用する
'オートフォーマットサンプル（先に設定）
Sub pivAutoFormatSample02()
ActiveWorkbook.PivotCaches.Create(xlDatabase, "テーブル1") _
.CreatePivotTable Sheets.Add.Range("A1")
With ActiveSheet.PivotTables(1)
.Format xlReport6 'オートフォーマットをここで適用
.PivotFields("入荷日").Orientation = xlRowField
.PivotFields("入荷日").DataRange.Item(1).Group _
periods:=Array(False, False, False, _
False, True, True, True)
With .PivotFields("四半期")
.PivotItems("Qtr1").Caption = "第4四半期"
.PivotItems("Qtr2").Caption = "第1四半期"
.PivotItems("Qtr3").Caption = "第2四半期"
.PivotItems("Qtr4").Caption = "第3四半期"
End With
.PivotFields("品名").Orientation = xlRowField
.PivotFields("エリア").Orientation = xlColumnField
With .PivotFields("数量")
.Orientation = xlDataField
.NumberFormat = "#,##0_ "
End With
With .PivotFields("金額")
.Orientation = xlDataField
.NumberFormat = "#,##0_ "
End With
End With
End Sub・6行目で、フィールド設定前に「オートフォーマット」の「レポート6」を適用しています。
・これなら、前のように「列」フィールドの項目を再配置しなくて済みます。
・下の画像が今回のコードで作成した「レポート6」のピボットテーブルです。
まとめ（おわりに）
・いかがでしたでしょうか？
・「いつも汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーを念頭に記事を書いています。
・今回も、記事内で使用したコードのサンプルファイルを登録していますのでご利用ください。
・今までの記事のサンプルも登録していますのでよろしければお使いください(^^)
まとめと感想など
どうですか。なんだかちょっと中途半端でスッキリしませんでした。「レポート」の中に使いたいスタイルがあったので検証してみました。参考として見てください(^^)
ピボットテーブルのスタイルって難しいんですね！ いろいと迷わないように好みに合ったスタイルを事前に見つけておく方が良いですね。今回を参考にして色々設定して探してみます(^^;
今回のコードで、日付データの「グループ化」を使っているんですが、次回はそのの「グループ化」について勉強したいと思います(^^)/
★★★ ランキング参加中！ クリックしてね(^^)/ ★★★
今後の記事について
今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m
【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にもご期待ください(^^)/
記事のサンプルファイルをダウンロードできます
今回の記事のサンプルをダウンロードできるようリンク先に登録しています！
過去の記事で使用したサンプルファイルがダウンロードできるページを設置しています
こちら（このリンク先）からご利用ください
【今回わかったことは】
・ピボットテーブルの旧スタイル「オートフォーマット」機能の利用方法
・オートフォーマットの「レポート」の挙動には注意が必要