質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.57%

【VBA】ピボットテーブルにおいて、フィールドをある文字から始まる値で絞りたい

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 6,166

floor

score 12

前提・実現したいこと

ピボットテーブルのフィルターにおいて、
あるフィールドのある値がある文字から始まる値で絞りたいです。
VBA実行後は、フィルターの該当する要素にチェックが入った状態としたいです。

・ある要素に格納されているのは数式で別シートから参照している文字列です
・参照先の文字列には値がなく、0と表示される要素もあります

発生している問題

「5*」の値のみ表示(チェックが付いた状態)、
「5*」以外の値は非表示(チェックが外された状態)を望んでいましたが、
「0」のみチェックが外れた状態となり、他の要素はすべてチェックがついていました。
ちなみに、VBAを使わず手動でフィルターを「5*」で検索した場合は問題なく動作します。

該当のソースコード

For Each pvt In Ws.PivotTables
    pvt.PivotCache.Refresh
Next

Set pf = Ws.PivotTables("ピボットテーブル").PivotFields("番号")
pf.Orientation = xlPageField
pf.ClearAllFilters

For Each p In pf.PivotItems
    Select Case True
    Case p.Value Like "5*"
    Case Else
        p.Visible = False
    End Select
Next

試したこと

Case文ではなくIf文で試しましたが同様の結果となりました。

If p.Value Like "5*" Then
Else
    p.Visible = False
End If


全件非表示にするように試してみたところ、
VBA実行後にフィルターの要素を確認してみましたが、
途中まではチェックが外れて非表示となっており、
途中からはチェックが付いたままの状態となり全件非表示にはなりませんでした。

For i = 1 To pf.PivotItems.count - 1
    pf.PivotItems(i).Visible = False
Next


今回の事象に関係ないかもしれませんが、
PivotItemsの数は200件程あり、重複した要素を除くと100件程となります。
上記、VBA実行後は50件までチェックが外れていたイメージです。

ActiveSheet.PivotTables("ピボットテーブル").PivotFields("番号").CurrentPage = "(All)"
With ActiveSheet.PivotTables("ピボットテーブル").PivotFields("番号")
    .PivotItems("12345").Visible = False
    .PivotItems("23456").Visible = False
    .PivotItems("34567").Visible = False
    '以下省略
End With


また、VBAを使わず手動でフィルターを「5*」で検索した場合のマクロの記録では、
上記の結果となり、「5*」以外の値を明記する必要が生じてしまいます。

補足情報(FW/ツールのバージョンなど)

EXCEL 2016 / Windows10 使用

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • morinatsu

    2018/12/05 22:18

    「VBAを使わず手動でフィルターを「5*」で検索した場合は問題なく動作します。」この操作をマクロの記録機能で記録してみると、ヒントが得られるのではないでしょうか。手で操作するときのアクションと、それを記録したマクロの記述が大きく違っていることはよくあるので。

    キャンセル

  • floor

    2018/12/05 23:53

    コメント有難うございます。マクロの記録の結果を記載しておりませんでしたので、追記させて頂きました。質問本文にも記載させて頂いたのですが、マクロの記録の結果では「5*」以外の値を明記する必要が生じてしまうため、効率の良い記載方法がないものかと途方に暮れています。

    キャンセル

回答 2

checkベストアンサー

+2

当方の環境(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 19:22

    丁寧なご回答有難うございます。
    PivotItems.Countは200件程ございまして、
    ループ内でカウントしたところPivotItems.Countの分だけ処理は実行されてはいるものの、
    実行結果を確認しますとフィルターは途中まではチェックが外れて非表示となっていましたが、
    途中から(50数件目)はチェックが付いたままの状態となり全件非表示にはなりませんでした。
    検証端末を借りることができれば、他環境でも確認したいのですが現状難しく・・・。

    キャンセル

  • 2018/12/07 13:18

    あてずっぽうにいくつか案を挙げさせていただきます。

    ①単純にピボットテーブルを作りなおしてみてもダメでしょうか?
    PivotItemsのコレクションの中身は実際のデータと食い違うことがあり、そのせいでVisibleプロパティが反映されないことがあるようです。
    これ自体は
    ・ピボットテーブルのオプションメニューから「1フィールドに保持するアイテム数」を「なし」に変更する
    ・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
    ```
    といったシンプルなコードを動かしてみても現象は変わらないでしょうか?
    もし少量のデータでうまく動くのなら、また別途新しいブックで大きなデータを作成し試してみるのもいいと思います。

    ---
    こちらで現象が出ていないため的確なアドバイスができず申し訳ないのですが、ご確認ください。

    キャンセル

  • 2018/12/10 13:56 編集

    ご回答有難うございます。

    ①の下記を試してみたところ、望み通りの結果を得ることができました。
    ・ピボットテーブルのオプションメニューから「1フィールドに保持するアイテム数」を「なし」に変更する

    該当のソースコードの2行目に下記を追記しました。
    pvt.PivotCache.MissingItemsLimit = xlMissingItemsNone

    >PivotItemsのコレクションの中身は実際のデータと食い違うことがあり、そのせいでVisibleプロパティが反映されないことがあるようです。
    →この度は大変勉強になりました。本当に有難うございます!!

    キャンセル

+1

また、VBAを使わず手動でフィルターを「5*」で検索した場合のマクロの記録では、
上記の結果となり、「5*」以外の値を明記する必要が生じてしまいます。

そのような結果になったということは、「5*」そのものをVBAでストレートに表現するのは難しい、ということだと思います。Excelが裏でそれだけのユーザー操作を代行していた、ということですね。私もつい最近、似たような事例に遭遇しました。
私の場合はPivotItemsが固定かつ数も少なかったので、それぞれのVisibleTrueFalseをいちいち設定することで解決しました。

質問者さんの事例でPivotItemsが可変であるならば、PivotItemsコレクションの要素一つ一つが「5*」に当てはまるか判定しながらVisibleを切替えるようなロジックが必要になると思います。
つまり、質問者さんの示されているコードが最善に近い、というのが結論です。


追記
ピボットテーブルのフィルタの数にも制限があるようです。
Excel の仕様および制限
回避策ではありますが、「5*」を実現するための列(先頭1文字抽出とか)を追加して、その列にフィルタをかける手もなくはないです。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/12/06 00:19 編集

    丁寧なご回答有難うございます。
    PivotItemsが可変となり、かつ、処理月によっては100件を超えるため非効率ではありますが、
    着地点としては、各コレクション要素に対し、Visibleの切替をする必要があるというところでしょうか。
    ただ、気になる点としましては、質問本文でも記載させて頂いてはおりましたが、
    「5*」とは関係のない、全件非表示となる想定でいた挙動にならなかったことです。

    ++++++++++++++++++++++++++++++

    For i = 1 To pf.PivotItems.count - 1
    pf.PivotItems(i).Visible = False
    Next

    全件非表示となるか否か上記のソースにて試してみたところ、
    VBA実行後にフィルターの要素を確認してみましたが、
    途中まではチェックが外れて非表示となっていたのに対し、
    途中からはチェックが付いたままの状態となり全件非表示にはなりませんでした。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.57%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る