Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Excel 2016)
ピボットテーブル内のオートフィルタ
投稿日時: 21/04/02 07:37:29
投稿者: ののののの

ピボットテーブル内のフィルタ絞り込み・解除の操作をVBAでプログラミングしたいと思っています。
 
ピボットテーブル内で、フィルタで絞り込みされているデータがあるとします。
マクロ記録で、上記の絞り込みを解除しようとすると、
ActiveSheet.Range(セル範囲).AutoFilter Field:2 のようになってしまいます。
 
個人的には、
ActiveSheet.PivotTables("ピボットテーブル1").AutoFilter
のような形式で、ピボットテーブルを指定したいと思っています。
 
@この形式で、ピボットテーブルの全ての絞り込みを解除する方法がありましたら、
ご教授ください(ShowAllDataメソッドのイメージです。)
 
Aこの形式で、通常どおりの絞り込み方法も、念のため、確認させてください。
(Field:=2,Criteria:="東京"のイメージです。)
 
どうぞ、よろしくお願いいたします。

回答
投稿日時: 21/04/02 08:10:18
投稿者: simple

フィルタの解除は

Sub test()
    Dim pvfield As PivotField
    For Each pvfield In ActiveSheet.PivotTables("ピボットテーブル1").PivotFields
        pvfield.ClearAllFilters
    Next
End Sub
のようなコードではないですか?
マクロ記録を採ると基本形のコードが得られるので、
これを、各PivotFieldの繰り返しに持ち込むだけです。
 
また、フィルタの設定については、ラベルフィルタ、値フィルタともに、
マクロ記録をとればコードはわかるはずです。
トライして下さい。

投稿日時: 21/04/02 08:35:28
投稿者: ののののの

simple 様
 
早速のご回答ありがとうございます。
 
結論から申しますと、実行時エラー「1004」になってしまいます。
  pvfield.ClearAllFilters
のところで止まってしまいます。
 
コードの入力ミスが無いか、何度も確認しました。
 
For Each文を使用するというのは、頭の中になかったです。
私自身もいろいろ試しますが、改めてご教授くださると助かります。
(なお、エクセル2016を使用しております。)

回答
投稿日時: 21/04/02 12:03:15
投稿者: simple

おかしいですね、Excel2010で動作確認して投稿しましたが。
2019でも確認しましたが、エラーにはなりませんね。
エラーの時、ローカルエリアでみて、どのフィールドが対象になっていて、
どんなフィルタがかかっていますか?
 
なお、一度、ご自分でフィルタの設定と、クリアーをマクロ記録して、
それをこちらにアップしてみてはいかがですか?
回答者側では、そちらのPCが覗けませんので、
そうしてみたら、どなたかから有益なコメントがあるかもしれません。

投稿日時: 21/04/02 13:00:57
投稿者: ののののの

simple様
 
お忙しい中、ご対応いただき、ありがとうございます。
現在、在宅勤務中で、会社PCでマクロを作成しています。
この投稿は、個人用PCを使用しています。
会社PCからの投稿はできず、個人用PCからは、時間的制約もあり、
コードなどのアップロードは難しい点、ご了承ください。
 
意外なことに、
ActiveSheet.AutoFilter.ShowAllData
で、ピボットテーブルの絞り込みの解除は可能でした。
 
 
厚かましいと存じますが、ピボットテーブル内のフィルタの絞り込みの標準的なコードをご教授ください。
ActiveSheet.PivotTables("ピボットテーブル1").AutoFilter Field:=2,Criteria1:="東京"
では動かないです。
 
ちなみに、
ActiveSheet.AutoFilter Field:=2,Criteria1:="東京"
でも動きませんでした。
 
引き続き、よろしくお願いいたします。

回答
投稿日時: 21/04/02 13:16:27
投稿者: simple

何度も恐縮ですが、ご自分でフィルタを掛ける動作をマクロ記録してください

投稿日時: 21/04/02 13:33:10
投稿者: ののののの

こちらの説明が不足しており、申し訳ありません。
 
マクロ記録も対応済みです。
下記のコードになります。
 
ActiveSheet.Range("$A$3:$F$1000").AutoFilter Field:=2,Criteria1:="東京"
 
ピボットテーブルであることは度外視されており、かつ、マクロ記録のコードを
そのまま使用とすると、エラーになってしまいます。
なお、マクロ記録のコードと、それまでの一連のコードとの整合性は取れています。
 
1点、気になることがあります。
ピボットテーブルは、1,000行×6列の構成です。
ピボットテーブルのヘッダー(見出し行)は、3行目にあります。
具体的な実データは、4行目以降に存在します。
ピボットテーブルの形式上、ヘッダー(見出し行)の上の行に空白行は存在しておらず、
B1セルに「列ラベル」、B2セルに「当期」、D2セルに「前期」という値があります。
※1、2行目に存在するデータは、上記3セルだけです。
 
ヘッダー行の上に空白行がないことも、上手く動作しない原因かもしれません。
 
 

回答
投稿日時: 21/04/02 14:34:12
投稿者: simple

それはピボットテーブルのフィルタではないと思います。
.PivotFilters.Add メソッドが記録されると思います。
(ピボットテーブルの上では、オートフィルタが淡色表示になっていて、
  選択できないのでは?)
私では力及ばずです。ここまでとさせていただきます。

投稿日時: 21/04/02 14:52:56
投稿者: ののののの

Simple様
 
私の状況の説明が不充分な中、真摯に対応してくださり、ありがとうございました。
 
ピボットテーブル等のデータも私自身が作成したものではなく、
また、会社の情報であるため、充分な情報開示ができなかった点、ご容赦ください。
 
今後とも、ご指導よろしくお願いいたします。

回答
投稿日時: 21/04/03 09:53:56
投稿者: Suzu

ピボットテーブルである確認をどのようにされていますでしょうか?
 
ピボットテーブルであれば、その範囲を選択した際に
リボンに「ピボットテーブルツール」が出現します。
 
また、「列ラベル」「行ラベル」と表示されている右に ▼ が表示されており、
それをクリックすると 昇順/降順/その他の並べ替えオプション・・等が表示されます。

投稿日時: 21/04/03 11:12:03
投稿者: ののののの

 Suzu 様
 
>ピボットテーブルであれば、その範囲を選択した際に
>リボンに「ピボットテーブルツール」が出現します。
  
 この点、間違いありません。
 
>また、「列ラベル」「行ラベル」と表示されている右に ▼ が表示されており、
>それをクリックすると 昇順/降順/その他の並べ替えオプション・・等が表示されます。
 
 この点も、間違いありません。
 
 私自身、ピボットテーブルの使用歴は長いのですが、ピボットテーブルのVBAについては、
 あまり経験がありません。
 
 
 3行目には、A列〜F列までのヘッダーが存在しています。
 それらのヘッダー内にも、 ▼ が表示されており、絞込み可能です。
 
 マクロ記録で、特定のヘッダーにて、絞り込みを行うと、
 下記のコードが出力されますが、これをそのまま用いても正常に動かないです。
 
 ActiveSheet.Range("$A$3:$F$1000").AutoFilter Field:=2,Criteria1:="東京"
 
 ピボットテーブルであるがゆえに、ヘッダー行の上に、空白行がないため、
 オートフィルタのオブジェクト認識が上手く行かないと思われます。
 ※1、2行目にピボットテーブル関連の「列ラベル」「当期」「前期」のセル値があります。
 
 なお、マクロ記録時は、ヘッダー行(3行目)以降を範囲指定しています。
 (※マクロ記録では、1〜2行目を範囲指定していません。)
 
 3行目以降を他のシートにコピーして、新たなリストを作成すれば、通常のフィルタ操作は可能なので、
 そのようにすることも考えています。
 
 もし可能であれば、ピボットテーブルの3行目のフィルタで、直接絞込みを実施できるコードを知りたいです。
 その場合、マクロ記録のコードは当てにならないので、
 動きそうなVBAのコードがありましたら、ご教授ください。

回答
投稿日時: 21/04/03 12:10:50
投稿者: Suzu

EX)

FLD1	FLD2	FLD3	FLD4
1	A	い	10
2	A	い	20
3	A	ろ	30
4	B	ろ	40
5	B	は	50
6	B	は	60
7	C	に	70
のデータがあるとして
 
合計 / FLD4	列ラベル								
	A		A 集計	B		B 集計	C	C 集計	総計
行ラベル	い	ろ		は	ろ		に		
1	10		10						10
2	20		20						20
3		30	30						30
4					40	40			40
5				50		50			50
6				60		60			60
7							70	70	70
総計	30	30	60	110	40	150	70	70	280
の ピボットテーブルを作成したとします。
 
 
 
Sub Sample()
  Dim pvt As PivotTable
  Dim pvtFld As PivotField

  Set pvt = ActiveSheet.PivotTables("ピボットテーブル1")

  pvt.ClearAllFilters
  pvt.PivotFields("FLD2").PivotFilters.Add2 Type:=xlValueEquals, DataField:=pvt.PivotFields("合計 / FLD4"), Value1:=20

  pvt.ClearAllFilters
  pvt.PivotFields("FLD1").PivotFilters.Add2 Type:=xlValueEquals, DataField:=pvt.PivotFields("合計 / FLD4"), Value1:=50
  
  pvt.ClearAllFilters
  pvt.PivotFields("FLD2").PivotFilters.Add2 Type:=xlValueEquals, DataField:=pvt.PivotFields("合計 / FLD4"), Value1:=50

  pvt.ClearAllFilters
  pvt.PivotFields("FLD3").PivotFilters.Add Type:=xlCaptionEquals, Value1:="ろ"

  pvt.ClearAllFilters
  pvt.PivotFields("FLD2").PivotFilters.Add Type:=xlCaptionEquals, Value1:="B"
  pvt.PivotFields("FLD1").PivotFilters.Add Type:=xlCaptionEquals, Value1:="5"
  pvt.PivotFields("FLD1").ClearAllFilters
End Sub

 
シングルステップにて、実行し、コードと動作結果 の確認を行ってみてください。

投稿日時: 21/04/03 13:04:55
投稿者: ののののの

Suzu様
 
サンプルを、VBAで実行することが出来ました!
 
なるほど!
ピボットテーブルでのフィルタ絞込みのVBAコードは、
このようになるのですね!
 
ネットや書籍でいろいろ調べたのですが、
なかなか見つかりませんでした。
 
まだ実務版のピボットテーブルで実行した訳ではありませんが、
この度のサンプルコードで充分、対応できると思われます。
「解決済み」とさせていただきました。
 
サンプルコードも、たくさん紹介してくださり、とても感謝しております。
お陰様で、この土日、ゆったりと過ごせます。
 
今後とも、ご指導の程、よろしくお願いいたします。
ありがとうございました。