諸君はご存知だろうか。ピボットテーブル作成工程をマクロ自動記録すると、ゴミが生まれるということを。
百聞は一見にしかず。ということで、まあ、まずはゴミっぷりを実際に見ていただこう。
と、その前に、集計するデータはこちら↓
そして、
このように、ピボットらしくなるまでの過程を記録していく。
※なんかめっちゃ古いバージョンのピボットになってしまってる気がするが、なんでそうなったかは知らん。こんなの初めて///みた。
※一応目次をおいとく。ゴミをわざわざ見たくない諸君はすっ飛ばしてくれ。
では、さっそく
マクロ記録のゴミだ↓
Sub Macro1()
Macro1 Macro
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet3!R2C1:R13C7", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Sheet5!R3C1", TableName:="ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1", DefaultVersion _
:=xlPivotTableVersion10
Sheets("Sheet5").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("業者")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").AddDataField ActiveSheet.PivotTables( _
"ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("A-1"), "合計 / A-1", xlSum
ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").AddDataField ActiveSheet.PivotTables( _
"ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("A-2"), "合計 / A-2", xlSum
ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").AddDataField ActiveSheet.PivotTables( _
"ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("A-3"), "合計 / A-3", xlSum
ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").AddDataField ActiveSheet.PivotTables( _
"ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("A-4"), "合計 / A-4", xlSum
ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").AddDataField ActiveSheet.PivotTables( _
"ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("A-5"), "合計 / A-5", xlSum
Range("B4:F8").Select
Range("F8").Activate
Range("B6").Select
ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("合計 / A-1").Function = _
xlAverage
Range("C6").Select
ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("合計 / A-2").Function = _
xlAverage
Range("D6").Select
ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("合計 / A-3").Function = _
xlAverage
Range("E6").Select
ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("合計 / A-4").Function = _
xlAverage
Range("F6").Select
ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("合計 / A-5").Function = _
xlAverage
With ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("会社")
.Orientation = xlRowField
.Position = 1
End With
Columns("A:A").ColumnWidth = 10
ActiveWindow.SmallScroll Down:=-12
Range("C6").Select
Sheets("Sheet5").Select
Range("B6").Select
ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("会社").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("業者").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("A-1").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("A-2").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("A-3").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("A-4").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("A-5").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotSelect "平均 / A-1", _
xlDataAndLabel, True
Range("C4").Select
ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("平均 / A-1").Caption = _
"A1項目"
Range("D4").Select
ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").DataPivotField.PivotItems("平均 / A-2"). _
Caption = "A2項目"
Range("E4").Select
ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").DataPivotField.PivotItems("平均 / A-3"). _
Caption = "A3項目"
Range("F4").Select
ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").DataPivotField.PivotItems("平均 / A-4"). _
Caption = "A4項目"
Range("G4").Select
ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").DataPivotField.PivotItems("平均 / A-5"). _
Caption = "A5項目"
Range("H4").Select
End Sub
※長ったらしいゴミをすっ飛ばしたい人専用のリンク
おわかりいただけただろうか…？
終始ゴミである。
中でも、一層ゴミを極めているのは、
ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotFields("会社").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
の部分と言える。小計を非表示にするだけでこの有様！どんだけFalseすんねん！
こんな状態じゃワケが分からんし、何より使い物にならない。
というわけで、
要らない部分を削っていく事とする。
Sub pivotaro()
Dim pivotaroarea As Range
Set pivotaroarea = Sheets("Sheet3").Range("A2").CurrentRegion
Sheets.Add.Name = ("pivotaro")
ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=pivotaroarea).CreatePivotTable TableDestination:="pivotaro!R3C1", TableName:="ﾋﾟﾎﾞﾀﾛ"
With ActiveSheet.PivotTables("ﾋﾟﾎﾞﾀﾛ")
.PivotFields("会社").Orientation = xlRowField
.PivotFields("会社").Position = 1
.PivotFields("業者").Orientation = xlRowField
.PivotFields("業者").Position = 2
.AddDataField .PivotFields("A-1"), "A1項目", xlAverage
.AddDataField .PivotFields("A-2"), "A2項目", xlAverage
.AddDataField .PivotFields("A-3"), "A3項目", xlAverage
.AddDataField .PivotFields("A-4"), "A4項目", xlAverage
.AddDataField .PivotFields("A-5"), "A5項目", xlAverage
.PivotFields("A1項目").NumberFormat = "0.0"
.PivotFields("A2項目").NumberFormat = "0.0"
.PivotFields("A3項目").NumberFormat = "0.0"
.PivotFields("A4項目").NumberFormat = "0.0"
.PivotFields("A5項目").NumberFormat = "0.0"
End With
Dim pf As PivotField
On Error Resume Next
For Each pf In ActiveSheet.PivotTables("ﾋﾟﾎﾞﾀﾛ").PivotFields
pf.Subtotals(1) = True
pf.Subtotals(1) = False
Next
On Error GoTo 0
ActiveSheet.PivotTables("ﾋﾟﾎﾞﾀﾛ").RowAxisLayout xlTabularRow
End Sub
※実行結果↑
ほらスッキリしただろう！！
※とにかく短くする、ということだけしか考えていない為、この限りではないしなんならもっと短くする方法はあるかもしれないが、あんま気にしない事(とくにNumberFormatはちょっと気に食わんが、どうあがいてもコレ以上には出来なかった)。
では、軽く解説しよう。
まず選択範囲はA3からのアクティブ領域なのでCurrentRegionで指定し変数pivotaroareaに代入する。
そして、新しいシートを追加→命名：pivotaro ※この時点でアクティブなシートは、Sheet3から追加されたシートであるpivotaroに移っている。
次に、ActiveWorkbook.PivotCaches.Create、すなわちピボットのキャッシュを作る。が、バージョンとか要らんのでバージョンは消す事。入れなかったら使用しているエクセルのバージョンに合わせて勝手に生成してくれる。
あとはソースで先ほど決めたpivotaroareaを選択したり、ピボットテーブルを挿入する場所を"pivotaro!R3C1"と決めたり、ピボットテーブルの名前をﾋﾟﾎﾞﾀﾛとしたり、そんな感じだ。
それから
必要なデータを追加していく。
ちなみに、
.PivotFields("会社").Position = 1
.PivotFields("業者").Position = 2
に関しては、名前の通りポジション(位置)決めであるが、
.PivotFields("業者").Position = 1
.PivotFields("会社").Position = 1
と書いてもいいし、なんなら省略してもいい。
全部のポジションを1とする場合は、暫定一位が毎度塗り替えられていく感じで、あとから追加したほうが順番的に優位になる。
省略した場合なら、デフォルトの状態で、順番よく1,2,3,4…と、自動で最下に追加されていく事になる。
位置変更することがありそうなら、事前にPosition引数はあったほうがあとあと楽だと思われるが、テンプレのように毎度決まっているなら省略してもいいだろう。
文面だとちょっとわかりづらかろうなので、省略した文は、一番下のおまけで確認願う。
では次に、DataFieldの部分にいこう。
"A1項目"とか書かれている部分はCaption引数の値で省略した書き方だ。つまりピボットの見出しとして表示する項目名みたいなもん。
xlAverageはFunction引数の値、そのまんま「平均値出すよ」という意味。xlSumだと合計になる。
これだけだと小数点以下が無限に表示されてうるさいので、～.NumberFormat = "0.0"で表示形式を決めている。
そして中でも最も重要な、
Array(False, False, False, …を省略しているコードがこれ↓
Dim pf As PivotField
On Error Resume Next
For Each pf In ActiveSheet.PivotTables("ﾋﾟﾎﾞﾀﾛ").PivotFields
pf.Subtotals(1) = True
pf.Subtotals(1) = False
Next
On Error GoTo 0
これが、小計非表示で発生するおぞましいArray(False, False, False, False, False, False, False, False, False, False, False, False)を簡略化しているのである！！
【小計非表示で生まれるゴミ：falsefalseとおさらばするためのコード解説】
Subtotals(1)：これはSubtotalの計算方法を指定している。1の場合は自動、2が合計、3がカウント、4が平均･･･といった感じで12まである。
わかりやすく、True,Falseで表示・非表示を切り替える仕組みだ。
ここでようやく、マクロ記録によって生まれる12個のFalseの意味が理解できる。
要は、1つ1つFalseするあれは、一問一答なのだ。
「自動小計は非表示にする(´・ω・`)？」
「ひひょうじにします(^o^)」
「合計小計は非表示にする(´・ω・`)？」
「ひひょうじにします(^o^)」
「カウント小計は非表示にする(´・ω・`)？」
「ひひょうじにしまｓ(^o^)」
と、こんなことを12✕フィールド数分続けているのである。めんどくさ！
まあ話は元に戻すが、
pf.Subtotals(2) = Trueとすると合計の小計が表示される。これに追加で平均の小計を入れたい場合は、pf.Subtotals(4) = Trueを実行すればいい。
その状態で、pf.Subtotals(2) = falseを実行すると、合計表示の小計が消え、それ以外の平均やカウントの小計には消えずに残る。
が、(1)の自動だけは動きが異なる。
自動とは…自動である。まあ意味分からなかろうが、考えずに感じてくれ。
たとえば、合計・平均・カウントの小計が表示されている状態でpf.Subtotals(1) = Trueを実行すると、pf.Subtotals(4) = Trueとしたときのように追加される事は無く、合計平均カウントの小計は非表示→値フィールドの計算方法と同じ小計のみ表示するという動きをするのだ。1強すぎである。まるでトランプゲームのルールさながらの反則的動きだ。
それなら、pf.Subtotals(1) = Falseを実行すると、全部非表示になるんじゃないの？
そう、思うだろうか。我輩は思った。
だが違うのだ。
pf.Subtotals(1) = Falseで全部を非表示にするという動きは出来ないのだ。
pf.Subtotals(1) = Falseは文面のまま、自動の小計にのみ有効で、合計や平均などの小計が非表示になることはなく、表示されたままとなるのである。
だからこそ、全小計を非表示にする場合はこれらの仕組みをうまく利用し、
pf.Subtotals(1) = True
pf.Subtotals(1) = False
なのである。
一旦、Subtotals(1)をTrueにすることで自動以外の小計を全て非表示にする
→Subtotals(1)をFalseにすることで自動小計も非表示にする。
この動きを、for文で繰り返す事により、全フィールドに適用する。
ということだ。
最後に、ActiveSheet.PivotTables("ﾋﾟﾎﾞﾀﾛ").RowAxisLayout xlTabularRowだが、これは、レポートのレイアウト→表形式で表示の意である。
以上。
説明として変なところがあるかもしれんが、なんとなくで理解してくれ。我輩もなんとなくで使えているので問題ないだろう。
にしても、軽く解説と言ったのに、だいぶ長文化してしまっているな。
おまけは分けることにしよう。
