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