6
8

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

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

Posted at

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

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

002.png

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

003.png

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

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

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

004.png

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
6
8
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
6
8

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?