Q&A
「VBAを使わず手動でフィルターを「5*」で検索した場合は問題なく動作します。」この操作をマクロの記録機能で記録してみると、ヒントが得られるのではないでしょうか。手で操作するときのアクションと、それを記録したマクロの記述が大きく違っていることはよくあるので。
前提・実現したいこと
ピボットテーブルのフィルターにおいて、
あるフィールドのある値がある文字から始まる値で絞りたいです。
VBA実行後は、フィルターの該当する要素にチェックが入った状態としたいです。
・ある要素に格納されているのは数式で別シートから参照している文字列です
・参照先の文字列には値がなく、0と表示される要素もあります
発生している問題
「5*」の値のみ表示（チェックが付いた状態）、
「5*」以外の値は非表示（チェックが外された状態）を望んでいましたが、
「0」のみチェックが外れた状態となり、他の要素はすべてチェックがついていました。
ちなみに、VBAを使わず手動でフィルターを「5*」で検索した場合は問題なく動作します。
該当のソースコード
vba
1For Each pvt In Ws.PivotTables 2 pvt.PivotCache.Refresh 3Next 4 5Set pf = Ws.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ").PivotFields("番号") 6pf.Orientation = xlPageField 7pf.ClearAllFilters 8 9For Each p In pf.PivotItems 10 Select Case True 11 Case p.Value Like "5*" 12 Case Else 13 p.Visible = False 14 End Select 15Next
試したこと
Case文ではなくIf文で試しましたが同様の結果となりました。
vba
1If p.Value Like "5*" Then 2Else 3 p.Visible = False 4End If
全件非表示にするように試してみたところ、
VBA実行後にフィルターの要素を確認してみましたが、
途中まではチェックが外れて非表示となっており、
途中からはチェックが付いたままの状態となり全件非表示にはなりませんでした。
vba
1For i = 1 To pf.PivotItems.count - 1 2 pf.PivotItems(i).Visible = False 3Next
今回の事象に関係ないかもしれませんが、
PivotItemsの数は200件程あり、重複した要素を除くと100件程となります。
上記、VBA実行後は50件までチェックが外れていたイメージです。
vba
1ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ").PivotFields("番号").CurrentPage = "(All)" 2With ActiveSheet.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ").PivotFields("番号") 3 .PivotItems("12345").Visible = False 4 .PivotItems("23456").Visible = False 5 .PivotItems("34567").Visible = False 6 '以下省略 7End With
また、VBAを使わず手動でフィルターを「5*」で検索した場合のマクロの記録では、
上記の結果となり、「5*」以外の値を明記する必要が生じてしまいます。
補足情報（FW/ツールのバージョンなど）
EXCEL 2016 / Windows10 使用
コメント有難うございます。マクロの記録の結果を記載しておりませんでしたので、追記させて頂きました。質問本文にも記載させて頂いたのですが、マクロの記録の結果では「5*」以外の値を明記する必要が生じてしまうため、効率の良い記載方法がないものかと途方に暮れています。
回答2件
0
ベストアンサー
当方の環境(Win7/Office2010)で、簡単なサンプルデータをもとにピボットテーブルを作成し、「該当のソースコード」で提示いただいたコードを動かしてみました。
結果は、500件程の候補（重複を除くと400ほど）から期待する絞り込みができました。
以上より、50件程度で止まってしまうというのは環境依存なのかもしれません。
試しに他の環境で同様のコードを含むExcelを作成し、確認されてみては如何でしょうか？
また、ループが想定している回数だけ回っているのかも気になるところです。
MsgBox pf.PivotItems.Count
を実行してみて期待する件数が取れているでしょうか？
またループ内で
cnt = cnt + 1: If cnt Mod 50 = 0 Then MsgBox cnt
のような記述をしたとき、期待する件数まで処理が行われているでしょうか？
⇒On Errorで事前に例外処理をしている場合、意図した回数を行う前に処理が中断している可能性があります。
明確な回答でなく申し訳ありません。
念のためご確認ください。
投稿2018/12/06 08:15
編集2018/12/06 08:20
総合スコア3013
丁寧なご回答有難うございます。
PivotItems.Countは200件程ございまして、
ループ内でカウントしたところPivotItems.Countの分だけ処理は実行されてはいるものの、
実行結果を確認しますとフィルターは途中まではチェックが外れて非表示となっていましたが、
途中から(50数件目)はチェックが付いたままの状態となり全件非表示にはなりませんでした。
検証端末を借りることができれば、他環境でも確認したいのですが現状難しく・・・。
あてずっぽうにいくつか案を挙げさせていただきます。
①単純にピボットテーブルを作りなおしてみてもダメでしょうか？
PivotItemsのコレクションの中身は実際のデータと食い違うことがあり、そのせいでVisibleプロパティが反映されないことがあるようです。
これ自体は
・ピボットテーブルのオプションメニューから「１フィールドに保持するアイテム数」を「なし」に変更する
・pf.autoSort = Falseにする
などで解消することがあるようですが、基本的にピボットテーブルを作った直後は正常に動作しているというのがほとんどのようです。
②シンプルな動作を確認する
例えば新しいExcelブックを作成し、そこに適当な軽めのデータを作成し、そのデータでピボットテーブルを作ります。
そのピボットテーブルに対し、
```
Sub PivTest()
Dim Ws As Worksheet
Dim pt As PivotTable
Dim pf As PivotField
Dim itm As PivotItem
Set Ws = Worksheets("Sheet1")
Set pt = Ws.PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1")
Set pf = pt.PivotFields("番号")
pf.ClearAllFilters
For Each itm In pf.PivotItems
'先頭一文字を判断
If itm.Value Like "5*" Then
Else
itm.Visible = False
End If
Next
End Sub
```
といったシンプルなコードを動かしてみても現象は変わらないでしょうか？
もし少量のデータでうまく動くのなら、また別途新しいブックで大きなデータを作成し試してみるのもいいと思います。
---
こちらで現象が出ていないため的確なアドバイスができず申し訳ないのですが、ご確認ください。
ご回答有難うございます。
①の下記を試してみたところ、望み通りの結果を得ることができました。
・ピボットテーブルのオプションメニューから「１フィールドに保持するアイテム数」を「なし」に変更する
該当のソースコードの2行目に下記を追記しました。
pvt.PivotCache.MissingItemsLimit = xlMissingItemsNone
>PivotItemsのコレクションの中身は実際のデータと食い違うことがあり、そのせいでVisibleプロパティが反映されないことがあるようです。
→この度は大変勉強になりました。本当に有難うございます！！
0
また、VBAを使わず手動でフィルターを「5*」で検索した場合のマクロの記録では、
上記の結果となり、「5*」以外の値を明記する必要が生じてしまいます。
そのような結果になったということは、「5*」そのものをVBAでストレートに表現するのは難しい、ということだと思います。Excelが裏でそれだけのユーザー操作を代行していた、ということですね。私もつい最近、似たような事例に遭遇しました。
私の場合はPivotItemsが固定かつ数も少なかったので、それぞれのVisibleのTrueとFalseをいちいち設定することで解決しました。
質問者さんの事例でPivotItemsが可変であるならば、PivotItemsコレクションの要素一つ一つが「5*」に当てはまるか判定しながらVisibleを切替えるようなロジックが必要になると思います。
つまり、質問者さんの示されているコードが最善に近い、というのが結論です。
追記
ピボットテーブルのフィルタの数にも制限があるようです。
Excel の仕様および制限
回避策ではありますが、「5*」を実現するための列(先頭1文字抽出とか)を追加して、その列にフィルタをかける手もなくはないです。
投稿2018/12/05 14:52
編集2018/12/06 13:01総合スコア395
丁寧なご回答有難うございます。
PivotItemsが可変となり、かつ、処理月によっては100件を超えるため非効率ではありますが、
着地点としては、各コレクション要素に対し、Visibleの切替をする必要があるというところでしょうか。
ただ、気になる点としましては、質問本文でも記載させて頂いてはおりましたが、
「5*」とは関係のない、全件非表示となる想定でいた挙動にならなかったことです。
++++++++++++++++++++++++++++++
For i = 1 To pf.PivotItems.count - 1
pf.PivotItems(i).Visible = False
Next
全件非表示となるか否か上記のソースにて試してみたところ、
VBA実行後にフィルターの要素を確認してみましたが、
途中まではチェックが外れて非表示となっていたのに対し、
途中からはチェックが付いたままの状態となり全件非表示にはなりませんでした。
あなたの回答
tips
プレビュー
