EXCELのピボットテーブルのデータ更新で、表示で困っています

質問

データ更新の際の増えたデータの扱いで困っております。

増えたデータは更新後テーブルの最下部(集計除く)に表示されると思うのですが、
行のフィールドで更新前から「表示する」設定に設定している条件に合ったものだけを
更新後表示するようにマクロなどで出来るものなのでしょうか？

同じデータを複数のシートで用途別にピボットテーブルにしており、
「表示する」にチェックされているものもバラバラですので
ブックを開いた時に全てに増えたデータが付いてきて手動でチェックしていくのも大変ですし、
データとして更新はしたいけど、勝手に表示されては困る状態なのです。

どなたか良いアドバイスがありましたら宜しくお願い致します。

回答No.3

OtenkiAme · ベストアンサー

こんにちは。

> しかし、自己責任で使いますので、…
ということなので、参考マクロを貼り付けておきます。

私の使用環境（XL2000,XL2002,XL2003）で動作するよう作成していますが、
Ajiponponさんの使用環境もファイルも知らないので動作保証はいたしかねます。
不具合の箇所は、VBAを勉強されてAjiponponさんが修正してくださいね。

マクロを試す時は、元データに新規データが追加されたら、
エクセルで用意された［すべて更新］や［更新］ボタンを使用せず、
下記のマクロを実行してみてください。

Sub PvtTblRefreshAfterVisibleItemSet()

　　Dim TempSht As Worksheet
　　Dim PvtSht As Worksheet
　　Dim PvtTbl As PivotTable
　　Dim PvtFld As PivotField
　　Dim PvtItem As PivotItem
　　Dim TargetRange As Range
　　Dim Target As Range
　　Dim TorF As Boolean
　　Dim i As Long, j As Long, y As Long

'現在のピボットテーブルの状況を作業用シートに書き出す
　　With Application
　　　　.EnableEvents = False
　　　　.ScreenUpdating = False
　　End With
　　Set TempSht = Worksheets.Add
　　j = 1
　　For Each PvtSht In ActiveWorkbook.Worksheets
　　　　For Each PvtTbl In PvtSht.PivotTables
　　　　　　i = 1
　　　　　　For Each PvtFld In PvtTbl.PivotFields
　　　　　　　　If PvtFld.Name <> "データ" Then
　　　　　　　　　　If PvtFld.Orientation = xlRowField Or _
　　　　　　　　　　　　PvtFld.Orientation = xlColumnField Then
　　　　　　　　　　　　TempSht.Cells(i, j).Value = PvtSht.Name
　　　　　　　　　　　　TempSht.Cells(i + 1, j).Value = PvtTbl.Name
　　　　　　　　　　　　TempSht.Cells(i + 2, j).Value = PvtFld.Caption
　　　　　　　　　　　　i = 3
　　　　　　　　　　　　For Each PvtItem In PvtFld.PivotItems
　　　　　　　　　　　　　　If PvtItem.Visible = True Then
　　　　　　　　　　　　　　　　i = i + 1
　　　　　　　　　　　　　　　　TempSht.Cells(i, j) = PvtItem.Caption
　　　　　　　　　　　　　　End If
　　　　　　　　　　　　Next PvtItem
　　　　　　　　　　　　j = j + 1
　　　　　　　　　　End If
　　　　　　　　End If
　　　　　　　　i = 1
　　　　　　Next PvtFld
　　　　Next PvtTbl
　　Next PvtSht

'外部データ範囲とピボットテーブルをすべて更新する
　　ActiveWorkbook.RefreshAll

'作業用シートに書き出した情報を元にフィールドのアイテムの表示／非表示をする
　　For y = 1 To TempSht.Range("A1").CurrentRegion.Columns.Count
　　　　With TempSht
　　　　　　Set TargetRange = .Range(.Cells(4, y), _
　　　　　　　　　　　　　　　　.Cells(1, y).End(xlDown))
　　　　　　Set PvtFld = Worksheets(.Cells(1, y).Value). _
　　　　　　　　　　PivotTables(.Cells(2, y).Value). _
　　　　　　　　　　PivotFields(.Cells(3, y).Value)
　　　　End With
　　　　For Each PvtItem In PvtFld.PivotItems
　　　　　　PvtItem.Visible = True
　　　　Next PvtItem
　　　　For Each PvtItem In PvtFld.PivotItems
　　　　　　TorF = False
　　　　　　For Each Target In TargetRange
　　　　　　　　If PvtItem.Caption = Target.Value Then
　　　　　　　　　　TorF = True
　　　　　　　　　　Exit For
　　　　　　　　End If
　　　　　　Next Target
　　　　　　If TorF = False Then
　　　　　　　　On Error Resume Next
　　　　　　　　PvtItem.Visible = False
　　　　　　　　On Error GoTo 0
　　　　　　End If
　　　　Next PvtItem
　　Next y
　　Set PvtFld = Nothing
　　Set TargetRange = Nothing

'作業用シートを削除する
　　With Application
　　　　.DisplayAlerts = False
　　　　TempSht.Delete
　　　　Set TempSht = Nothing
　　　　.DisplayAlerts = True
　　　　.ScreenUpdating = True
　　　　.EnableEvents = True
　　End With

End Sub

お礼

またもやお礼が遅くなりまして、大変申し訳ありません

何とか手を加えてできました～～～！！
要領を得ない拙い質問に回答下さって、そして参考マクロをご提示下さって
本当に有難う御座いました！！

回答No.2

OtenkiAme

こんにちは。

> 増えたデータは更新後テーブルの最下部(集計除く)に表示されると思うのですが、
並べ替えの指定があるフィールドもあると思いますから、
最下部に表示されるとは、限らないですね。

> 行のフィールドで更新前から「表示する」設定に設定している条件に合ったものだけを
> 更新後表示するようにマクロなどで出来るものなのでしょうか？
マクロで出来ると思います。
…というか、実務でそのようなマクロを使っております。

私の場合は、予め、各フィールド毎に表示するアイテムを決めていて、
ピボットテーブル作成時に表示／非表示を設定して集計表を作成しています。

> 同じデータを複数のシートで用途別にピボットテーブルにしており、
> 「表示する」にチェックされているものもバラバラですので
のようなので、ajiponpon さんが、マクロを組まれるなら処理の流れとしては、
まず、一つのピボットテーブルをターゲットにして

１．作業用のシートを用意する。
２．> 更新前から「表示する」設定に設定している条件に合ったものだけを
　　を作業用シートにフィールド名と共にアイテム名を書き出しておく。
３．更新する。
４．作業用シートに書き出したフィールドに対して
　　書き出したアイテムと更新後に表示されたアイテム名を照合して
　　表示／非表示の設定をする。
５．作業用シートを削除する。

という流れで組み、うまく処理ができたら２～４の処理を
各ピボットテーブル毎に行えばいいのではないでしょうか？

なお、親フィールドでアイテムを非表示にしてデータを絞り込んでいる時、
子フィールドでアイテムを表示に設定するとエラーが発生しますので、必ず、
On Error Resume Next
対象のアイテム.Visible= True
On Error Goto 0
とした方がいいです。

ところで…
失礼ですが、ピボットテーブルをマクロで操作されたことはありますか？
自己責任で使われるなら、参考となるマクロは提示できますが…。

補足

お礼が大変遅くなり申し訳御座いません。

まさにOtenkiAme様がおっしゃるように出来たら有難いと思っていたのです。
そして恥ずかしながらピボットテーブルをマクロで操作したことがなく、
自動記録以外のその他マクロ自体も、出来上がっているものを
ちょこっとだけ弄る位にしか勉強中で知識がありません。

しかし、自己責任で使いますので、参考となるマクロをご教授頂けますでしょうか？
どうぞ宜しくお願い致します。

回答No.1

noname#176215

抽出したいデータとそうでないデータを分けるのが分かりやすいかと
思います。フラグを表示する列を 元データに追加したらどうですか？

お礼

お礼が大変遅くなり、申し訳ありません。

それが元データをあまり触れない状況にありまして…
でも参考にさせて頂きます、有難う御座いました。