Excel2010以降 ピボットテーブルの面倒回避マクロ等

2683 ワード
ピボットテーブル VBA Excel テーブル マクロ Excel テキストリンク

Excel2010以降(2007からかな？)は、ピボットテーブルの作成が非常に簡単になった。まず表を作ってから

Ctrl+T(またはL)で表をテーブル化する。次に、リボンに出現する「ピボットテーブルで集計」ボタンを押す。もう毎回これだけ、機械的にやってしまえばいい。

でもなぜか、ここで出来上がるピボットテーブルというのは、「コンパクト形式」とかいう形式であり、Excel2003までと同じ「表形式」ではない。

このコンパクト形式、私は一度も採用したことがなくて必ず「表形式」に変換しているのだが、コンパクト形式を愛用されている方はいらっしゃるのだろうか？私としてはコンパクト形式は、一つの列内に何でもかんでもデータを詰め込まれてて、分かりにくいったらないと思っている。これを毎回毎回「表形式」に変換するのが手間なので、次のマクロで一発変換するようにしている。

Sub pivot_autoFormat()
    Dim pvt             As Object

    If MsgBox("ピボットテーブルのフォーマットを自動調整しますか？", vbQuestion + vbOKCancel, "確認") = vbCancel Then Exit Sub

    Set pvt = ActiveSheet.PivotTables(1)

    With pvt
        .RowAxisLayout xlTabularRow     '表形式にする
        .ColumnGrand = False            '列の総計を削除
        .RowGrand = False               '行の総計を削除
        .HasAutoFormat = False          '「更新時に列幅を自動調整する」を「オフ」
        .RepeatAllLabels xlRepeatLabels '「アイテムのラベルをすべて繰り返す」を「オン」

    End With

End Sub

・・・頼むからデフォルトを「表形式」にできるようにしてくれ。
そして、ここから毎回、数値フィールドの扱いには困らされている。数値フィールドはたいてい、３桁ごとにカンマ打ちするものだが、Excelのピボットテーブルは数値フィールドの書式設定は、１個１個ご丁寧に「フィールドの設定」から進めることになっている。

10個のフィールドがあったら10回、面倒な設定作業を大体の人はしていると思う。「フィールドの設定」でなく、セル範囲を直接選んでから「セルの書式設定」で設定する手抜きの方法もあるにはあるが、ピボットテーブルのレイアウトを変えまくることを前提とした場合、根本的な対処法となっていない。
そこで次に、ピボットテーブルの数値フィールドについて一括で書式設定する下記マクロを私は使っている。・・・これくらい、マクロなしで普通にできるようにしてほしいものです。

Sub pivotNumFormat()
'ピボットテーブルの数値フィールドのフォーマットを一括変更
    Dim pvt     As Object
    Dim itm     As Object
    Dim str     As String

    Set pvt = ActiveSheet.PivotTables(1)

    For Each itm In pvt.DataPivotField.PivotItems
        str = itm.Name

        With pvt.PivotFields(str)

            .Function = xlSum '合計
            .NumberFormat = "#,##0;△#,##0"
            .Caption = Replace(.Caption, "合計 /", "") '数値フィールドにすると「合計 /」という余計な文字がつくので削除
        End With
    Next itm
End Sub