Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


2447 / 13645 ツリー ←次へ | 前へ→

【67708】ピボットテーブルのフィルタ操作 H=R 10/12/29(水) 14:12 質問[未読]
【67795】Re:ピボットテーブルのフィルタ操作 n 11/1/8(土) 22:50 発言[未読]
【67930】Re:ピボットテーブルのフィルタ操作 H=R 11/1/17(月) 18:45 質問[未読]
【67933】Re:ピボットテーブルのフィルタ操作 n 11/1/17(月) 19:39 発言[未読]
【67943】Re:ピボットテーブルのフィルタ操作 H=R 11/1/18(火) 14:29 お礼[未読]
【67952】Re:ピボットテーブルのフィルタ操作 n 11/1/18(火) 20:23 発言[未読]
【67969】Re:ピボットテーブルのフィルタ操作 H=R 11/1/19(水) 21:28 お礼[未読]
【67970】Re:ピボットテーブルのフィルタ操作 H=R 11/1/19(水) 23:50 質問[未読]
【67971】Re:ピボットテーブルのフィルタ操作 n 11/1/20(木) 0:38 発言[未読]
【67972】Re:ピボットテーブルのフィルタ操作 H=R 11/1/20(木) 8:55 お礼[未読]

【67708】ピボットテーブルのフィルタ操作
質問  H=R  - 10/12/29(水) 14:12 -

引用なし
パスワード
   お世話になります。

ピボットテーブルのフィルタで「(すべて)」をクリックして全てのチェックをはずす
操作はVBAで実現できますでしょうか。
1000以上ある部署のデータから、1部署だけを取り出したい場合に、残りの全て
のVisibleプロパティをFalseに変更していくと5分以上かかってしまいます。
手動でフィルタを操作するならば、まず「(すべて)」で全部のチェックを
はずしてから目的の部署にチェックを入れるので、その操作が実現できれば
処理を短縮化できるのではないかと思っています。
おそらくはVisibleItemsListが目的に合致していると思うのですが、OLAP
ピボットテーブルではありませんので使うことができません。

どなたか解決方法をお教えいただけますと幸いです。
よろしくお願いいたします。

【67795】Re:ピボットテーブルのフィルタ操作
発言  n  - 11/1/8(土) 22:50 -

引用なし
パスワード
   p://www.vbalab.net/vbaqa/c-board.cgi?cmd=ntr;tree=65160;id=excel
【65195】を応用すれば多少は速くなるかと思います。
考え方としては、
先頭のItemだけ残して以下をまとめてDelete。
目的のItemをVisible = Trueにした後に
先頭のItemをVisible = Falseです。

【67930】Re:ピボットテーブルのフィルタ操作
質問  H=R  - 11/1/17(月) 18:45 -

引用なし
パスワード
   ご回答ありがとうございます。
返信が遅れて申し訳ありません。

>先頭のItemだけ残して以下をまとめてDelete。
>目的のItemをVisible = Trueにした後に
>先頭のItemをVisible = Falseです。

Deleteがどのような操作に相当するのか分かりませんが、とりあえず
.PivotItems("hogehoge").Delete
.PivotItems(i).Delete
などとしてみたところ、
「アプリケーション定義またはオブジェクト定義のエラーです。」
となりました。
ページフィールドのフィルタですので、リンク先の方法を参考にするにも、
RowやColumnの部分をどうすればいいのかが分かりません。
アドバイスお願いします。

【67933】Re:ピボットテーブルのフィルタ操作
発言  n  - 11/1/17(月) 19:39 -

引用なし
パスワード
   >ページフィールドのフィルタ..
..ですか。ページフィールドだと簡単なはずなので
行フィールドだとばかり思ってました。
バージョンがExcel2007 or 2010ですね。

常に1部署だけ取り出す場合は、
EnableMultiplePageItems = False
『複数のアイテムを選択』チェックを外しておけば、

ActiveSheet.PivotTables(1).PageFields(1).CurrentPage = "取り出す部署名"

で良いはず。

ただし、『複数のアイテムを選択』チェックがあるとエラーになりますから
そのようなケースに対応するには、PageFieldsを配置し直して処理すれば
良いのではないかと思います。

With ActiveSheet.PivotTables(1).PivotFields("ページフィールド名")
  .Orientation = xlHidden
  .Parent.RefreshTable
  .Orientation = xlPageField
  .CurrentPage = "取り出す部署名"
End With

【67943】Re:ピボットテーブルのフィルタ操作
お礼  H=R  - 11/1/18(火) 14:29 -

引用なし
パスワード
   ご返答ありがとうございます。

▼n さん:
>バージョンがExcel2007 or 2010ですね。

2007です。

>常に1部署だけ取り出す場合は、
>EnableMultiplePageItems = False
>『複数のアイテムを選択』チェックを外しておけば、
>
>ActiveSheet.PivotTables(1).PageFields(1).CurrentPage = "取り出す部署名"

1部署取りだすことが多いのですが、常にではなく、2部署などの場合もあるため、
EnableMultiplePageItems = Falseはあまり使いたくないという状況です。

最初の投稿をしてから、失礼ながらしばらくレスに気づいていなかったため、
2chなどでも質問し、テーブルを一旦初期化してフィルタをかけた後に項目を配置する
という方法をお教えいただいたこともあり、かなり短縮化されてはいますが、
もし他にも効率的な方法があるならばお教えいただけますと幸いです。

【67952】Re:ピボットテーブルのフィルタ操作
発言  n  - 11/1/18(火) 20:23 -

引用なし
パスワード
   全体像が見えないので概略なアドバイスです。

A案)
元データの隣接列にFLAGフィールドを追加、Pivot元データ範囲も拡張させる。
FLAGフィールドには表示させたいItemだけ TRUE。
そのFLAGフィールドをPageFieldに使う。

Sub sample1() 'FLAGフィールドに判定のTRUE/FALSEをセットする例。
  Const Lst = ",部署1,部署2,部署3," '表示させるItemリストを","で囲む
  Dim v
  Dim w() As Boolean
  Dim x As Long
  Dim i As Long

  With Range("A1").CurrentRegion
    '部署名フィールドがA列なら
    v = Intersect(.Offset(1), .Columns("A")).Value
  End With
  x = UBound(v)
  ReDim w(1 To x, 0)
  For i = 1 To x
    w(i, 0) = (InStr(Lst, "," & v(i, 1) & ",") > 0)
  Next
  'FLAGフィールドがF列なら
  Range("F2").Resize(x).Value = w
End Sub

B案)
【65195】の応用で、PageFieldからRowFieldに移動して2番目のItem以降をまとめて非表示。
その後目的のItemをVisible = Trueにした後に先頭のItemをVisible = False。
設定後にRowFieldからPageFieldに戻す。

Sub sample2()
  Const Trg = "フィールド名"   'ページフィールド名
  Const Lst = "部署1,部署2,部署3" '表示させるItemリストをカンマ区切り文字列で
  Dim pf As PivotField
  Dim r  As Range
  Dim rr As Range
  Dim x  As String
  Dim s() As String
  Dim si
  
  With ActiveSheet.PivotTables(1)
    On Error Resume Next
    Set pf = .PivotFields(Trg)
    pf.ClearAllFilters
    pf.Orientation = xlRowField
    pf.Position = 1
    Set r = Intersect(pf.DataRange, .RowRange)
    On Error GoTo 0
    If Not r Is Nothing Then
      Set rr = r.Find(pf.PivotItems(2), , xlValues, xlWhole, , , , False)
      If Not rr Is Nothing Then
        x = pf.PivotItems(1)
        Range(rr, r(r.Count)).Delete
        s = Split(Lst, ",")
        For Each si In s
          pf.PivotItems(si).Visible = True
        Next
        If IsError(Application.Match(x, s, 0)) Then
          pf.PivotItems(1).Visible = False
        End If
      End If
    End If
    pf.Orientation = xlPageField
  End With

  Set pf = Nothing
  Set rr = Nothing
  Set r = Nothing
End Sub
元データの量にもよるかもしれませんが
1,000アイテム程度であれば1秒前後で処理できると思います。

【67969】Re:ピボットテーブルのフィルタ操作
お礼  H=R  - 11/1/19(水) 21:28 -

引用なし
パスワード
   ▼n さん:
>全体像が見えないので概略なアドバイスです。

具体的なコードを提示していただき、本当に助かります。
元データを変更することは難しいので、B案のコードでテストしてみたところ、
今使っているコードよりやや早くなることが分かりました。
使ったことのない関数などもあるのでまずは解読してからになりますが、
参考にさせていただきます。
ありがとうございました。

【67970】Re:ピボットテーブルのフィルタ操作
質問  H=R  - 11/1/19(水) 23:50 -

引用なし
パスワード
   何度もすみません。
案Bについて少しずつコードを改変して、動作を確認した結果、
基本的な処理の流れは理解できたのですが、
どうしても分からない点があり、再度質問させていただきます。
しつこくて申し訳ありませんが、もしお暇があればお教えください。

▼n さん:
>    Set r = Intersect(pf.DataRange, .RowRange)

この部分、pf.DataRangeをそのまま使っても動作するようなのですが、
Intersectを入れる意図は何なのでしょうか。
ちなみに、Intersectだと「空白」が残ってしまいますが、
DataRangeを使えば「空白」も隠されるようです。
何か特定の場合にはIntersectを入れていないと問題が発生するのかと思い、
いろいろと試してみましたが、見つけることができませんでした。
よろしくお願いいたします。

【67971】Re:ピボットテーブルのフィルタ操作
発言  n  - 11/1/20(木) 0:38 -

引用なし
パスワード
   【65195】のコードをベースにしたものをそのまま流用したので不要な処理でした。
混乱させてしまってすみません。

意図としては、
【65195】では対象フィールドが行フィールドにある事が保証されてないケースを考えてました。
On Error Resume Next
Set pf = .PivotFields(Trg)
Set r = Intersect(pf.DataRange, .RowRange)
On Error GoTo 0
If Not r Is Nothing Then

変数rが取得できなければ、対象フィールドが行フィールドに無いと判断できます。

ですが、今回は事前に
pf.Orientation = xlRowField
ここでセットしてるので不要でした。
pf.DataRange のみで大丈夫です。

【67972】Re:ピボットテーブルのフィルタ操作
お礼  H=R  - 11/1/20(木) 8:55 -

引用なし
パスワード
   ▼n さん:
>意図としては、
>【65195】では対象フィールドが行フィールドにある事が保証されてないケースを考えてました。

これで理解できました。勉強になります。
お手数をおかけしましたが、丁寧なご解説ありがとうございました。

2447 / 13645 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free