[[20150618132107]]　『ピボットのレポートフィルタを指定したい』（さいき）　ページの最後に飛ぶ

『ピボットのレポートフィルタを指定したい』（さいき）

 いつもお世話になっております

 昨日より、何度も質問を書いては消しております
 説明しようとすると解決策が見え、また新たな質問が浮かび、説明しようとして解決ということを繰り返し……
 ピボットをやめるという選択肢に落ち着きました
 (元も子もない(-"-))

 しかし、このもやもやした疑問を解決したいので、どうかお願いいたします！！

 EXCELよりADOにてACCEESSデータを加工しEXCELにレコード転記したデータがあります
 このデータは週次で更新します
 これを元データとしたピボットテーブル(すでに作成済)を更新し、最新データとします

 ピボットにする理由といたしましては、このデータを必要とする人が自由に組み替えて必要とする表に自分でできることを可能とする為です
 (このメリットを捨てたのでピボットでなくなったわけです)

 データにはテキスト型の"開始日"がありまして、2015/03/02などの文字列となっております
 このフィールドをレポートフィルタに設定してあります

 このレポートフィルタを任意の日付で抽出したいのですが、どうすればよいのでしょうか
 (任意の日付→ユーザーフォームのTextBoxか指定のセルにて入力予定でした)
 現状、For Each にて PivotItems をひとつずつ取り出しては比較して Visible = True か False かの判定をしております

 ～ 一部抜粋 (日付決め打ちにしておりますが…)～

 Dim DPI As PivotItem

 With .PivotFields("開始日")
     .ClearAllFilters

     For Each DPI In .PivotItems
         If DPI.Name >= "2015/03/02" And DPI.Name <= "2015/04/30") Then
             DPI.Visible = True
         Else
             DPI.Visible = False
         End If
     Next

 End With

 質問その１
  抽出したい日付が、2015/03/02以降で終わりの指定がない場合、
  If DPI.Name >= "2015/03/02" Then　となりますよね？
  また、逆のパターンも生まれます
  その場合は期間指定のデータがあるかないかで条件分岐させ、ForEachを３回書くのでしょうか？(もしくはForEachのあとで期間指定での分岐)
  勝手な想像→PivotItemsのインデックス番号をどっかに保持させ、後でまとめて Visible = False にできるのでしょうか？

 →つまり、期間指定が最初だけor最後だけor両方に関わらず、Visible を TrueかFalseとする文を１回ですませられないでしょうか？

 質問その２
  そもそもFor Each で PivotItems をひとつずつ見ていくやり方はまだるっこしくないのでしょうか？
  勝手な想像→どうせ自動で小さい数字からPivotItemsのインデックス番号を振られているのですから、
  小さい方から見て行って2015/03/02をみつけたらフラグ、大きい方から見て行って2015/04/30を見つけたらフラグ
  その間にないものすべて Visible = False みたいにできないのでしょうか？

 →つまり、１個ずつ比較しないやり方はないのでしょうか？

 言っている意味わかんない、となる可能性が大ですがよろしくお願いいたします
 以前にも悩んだことのあるテーマなので、解決したいです

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

 回答ではないです。

 レポートフィルタでなく、ラベルフィルタではだめでしょうか。
 マクロなら、開始日と終了日用のコンボボックスを用意し、フイルタ条件の設定に利用できそうな気がします。

（マナ） 2015/06/18(木) 18:56

 >>マナさん
 ありがとうございます！
 ラベルフィルタやってみました。
 .PivotFields("開始日").PivotFilters がだめでした
 1004：アプリケーション定義またはオブジェクト定義のエラーです
 プロパティやらオブジェクトやらを理解していない為、よくわかっていませんが、行ラベルに移動すればできることから、レポートフィルタには使えないということがわかりました

 行ラベルの先頭に開始日を移動させ、範囲内のフィルタをかけてみたところ、元々の行ラベルが開始日ごとの表示となってしまいました
 そこで、開始日の表示されているものに対してグループ化を行い、元の開始日フィールドを削除して表示されました
 これ、グループ化しなければならないのでしょうか？
 行ラベルの順番をいじくったり小計出したりしてみましたが、できません…
 そもそも、ラベルフィルタの解釈まちがっているのでしょうか？

 またPivotFieldのHELPとにらめっこしてます
 どうしてレポートフィルタにあるフィールドには範囲内のフィルタがかけられないのか、さっぱりです…
（さいき） 2015/06/19(金) 11:08

 よく分かってないですがこんな考え方とか？
    Dim StartDay As String
    Dim EndDay As String
    '初期値
    StartDay = "1000/01/01" 'とにかく小さい値
    EndDay = "9999/12/31" 'とにかく大きな値
    'テキストボックスに入力値があれば採用
    If txtStartDay <> "" Then
        StartDay = txtStartDay
    End If
    If txtEndDay <> "" Then
        EndDay = txtEndDay
    End If
みたいな。
（ご近所PG） 2015/06/19(金) 15:09

 >>ご近所PGさん
 ありがとうございます

 それですー！
 ピボットに惑わされ続けて、もうこんがらがりきって干し葡萄状態(どんな状態…)でした
 そうですよね、最初にこちらで設定しておけばいいんですよね

 とりあえず、疑問その１、解決です！

 引き続き、PivotItems と戯れます……
（さいき） 2015/06/19(金) 16:19

どんなコードでだめだったのかわかりませんが、

 ＞.PivotFields("開始日").PivotFilters がだめでした
 ＞1004：アプリケーション定義またはオブジェクト定義のエラーです

こんな感じでどうでしょうか。
マクロ記録を修正したものです。

 Sub test()
    Dim Day1 As String
    Dim Day2 As String

    With ActiveSheet
        Day1 = .Range("I2").Value
        Day2 = .Range("j2").Value

        .PivotTables(1).PivotFields ("開始日")
            .ClearLabelFilters
            .PivotFilters.Add _
                Type:=xlDateBetween, Value1:=Day1, Value2:=Day2
    End With

 End Sub

（マナ） 2015/06/19(金) 19:08

さいきさん、こんにちは。

提示のマクロコードですが、
.ClearAllFiltersで（すべて）を選択してから処理しており
（Visible=True　はすでに設定されているので）
If 条件 Then Visible=False の処理だけ書けばいいと思います。

なお、シート上の見た目の日付とマクロ処理で使用する日付の記述が異なることがあるので
「マクロの記録」で一般機能での操作を記録し、日付の書式（記述）があっているか確認する
ことをお勧めします。

こんな感じで判定してみたら如何でしょうか？

Dim DPI As PivotItem
Dim myDate As Date

With ActiveSheet.PivotTables(1).PivotFields("開始日")
　　If .Position <> 1 Then Exit Sub
　　Application.ScreenUpdating = False
　　.ClearAllFilters
　　For Each DPI In .PivotItems
　　　　myDate = CDate(DPI.Name)
　　　　If IsDate(myDate) = True Then
　　　　　　If myDate <= CDate("2015/03/02") Or _
　　　　　　　　　　myDate >= CDate("2015/04/30") Then
　　　　　　　　DPI.Visible = False
　　　　　　End If
　　　　End If
　　Next
　　Application.ScreenUpdating = True
End With

ページフィールドでの処理は、まとめて処理はできないのでひとつずつ判定していくしかありません。
その時、判定により設定を変更した都度更新処理が行われるので
データフィールドが配置されている時とそうでない時では処理速度が違います。
そのため、画面の更新を止める処理を書かないと頻繁にちらつくことがあります。

一般機能（手作業）で操作した時ならチェックのON/OFFでもOKを選択するまで
更新されることはないのですが、マクロ処理ではそうなることを知った上で処理を
組まれるとよいと思います。

私が第三者に使ってもらうマクロブックでは、ピボットテーブルを作る前に使用するデータの
期間を指定してから使ってもらうとか、マクロを組む際、対象フィールドを行エリアに配置する
→アイテムを絞り込む→行エリアからページエリアに移動する→データフィールドを配置する、
という手法を使ってなるべく更新の回数が少なくなるように組んでいます。

あと、質問の、期間指定の条件が複数あるならその条件文は記述する必要があると思います。
それは、ページフィールドの処理でも行フィールドの処理でも同じです。

ご参考まで。
（OtenkiAme） 2015/06/20(土) 21:11

 >マナさん
 質問しておきながら、お返事遅れまして申し訳ございません
 やはり、そちらのコードを試してみても、
 実行時エラー438　オブジェクトは、このプロパティまたはメソッドをサポートしていません
 となります

 行ラベルや列ラベルに配置してあるのであれば動くのです
 レポートフィルタ(ページフィールド)に配置してある場合は無理みたいです
 >行ラベルに移動すればできることから、レポートフィルタには使えないということがわかりました
 余計なことを書きすぎて、読みにくくなってしまい申し訳ございません…

（さいき） 2015/06/22(月) 10:25

 >OtenkiAmeさん
 ありがとうございます
 雨のち晴れのOtenkiAmeさんでしょうか？
 会社からはブログ関係他をブロックされておりましてキャッシュで開いている為リンクを開けず、すべてを読みきれておりませんが、拝見させていただいております
 スマホで読むにもExcelがないとわからず…次回、帰省した際に最初から読みます！
 ……話がそれました

 >If 条件 Then Visible=False の処理だけ書けばいいと思います
 目から鱗どころか魚肉ソーセージです
 確かに、すでにVisible=Trueですよね…

 >ページフィールドでの処理は、まとめて処理はできないのでひとつずつ判定していくしかありません
 了解いたしました
 Helpでその記述を見つけられず、やり方の問題なのか仕様なのかの判断がつきかねていました

 →行エリアからページエリアに移動する→データフィールドを配置する
 試しもせずに、そうするとフィルタがクリアされると思い込んでました

 >判定により設定を変更した都度更新処理が行われる
 なるほど…だから、途中でとめるとそこまでのフィルタがかかっていたのか…

 ルーティンワーク終了次第、以上のことを参考にコードを組みます
 またご報告にあがらせていただきます
 まずはお返事まで…
（さいき） 2015/06/22(月) 11:00

 さいちさん、こんにちは。

 > 雨のち晴れのOtenkiAmeさんでしょうか？
 はい。
 お読みいただいたことがあるのですね。
 ありがとうございます。m(_ _)m

 > 最初から読みます！
 もう動かなくなってしまったマクロもあるので
 使えそうなものだけ読んでいただければと……。
 時間、大事ですから、ね。
 でも、使えそうなものがなかったらすみません。m(_ _)m
（OtenkiAme） 2015/06/23(火) 18:40

 ……途中経過メモです(TдT)

 元のACCESSでのデータ構成に変更(!)がありました
 そこで最低限の処理だけSQLにしてEXCELにデータ転記し処理することにしました

 が、DPI.Visible = Falseでエラーがかかるようになりました
 PivotFieldクラスのPivotItemsプロパティが取得できませんとなります

 開始日の列の表示形式が何度戻しても勝手に日付になっております
 修正したいのですが、優先度が高い別の仕事が舞い込んできたので、来週あたりに持ち越しです
 回答いただいているのになかなか終わらなくてすみません

 >OtenkiAmeさん
 以前よりとても助けられております
 ありがとうございます
（さいき） 2015/06/24(水) 14:59

コメント返信：

[ 一覧(最新更新順) ]

[[20150618132107]] 『ピボットのレポートフィルタを指定したい』（さいき） ページの最後に飛ぶ

[[20150618132107]]　『ピボットのレポートフィルタを指定したい』（さいき）　ページの最後に飛ぶ