Excel (VBA)

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

 
(Windows 10 Pro : その他)
フィルターフィールドで1つだけチェックを入れていないが「(複数のアイテム)」表記になる
投稿日時: 21/06/29 16:46:18
投稿者: Alice

Microsoft Excel for Microsoft 365 MSOを使用しています。
 
ピボットテーブルのフィルターフィールドが「(すべて)」になっている為、下記コードで指定のコードのみチェックが入るようにしました。
 
For Each p In ActiveSheet.PivotTables(1).PivotFields(filterField).PivotItems
                                 
   If p.Value = active_depCODE Then
                                     
       p.Visible = True
                                         
    Else
                                     
      p.Visible = False
                                     
   End If
Next p
 
※ 「filterField」はフィールド名の変数
※ active_depCODEはチェックを入れたいピボットアイテムの変数
※ シート上にあるピボットテーブルは1つだけ
 
その点は、正しくなされているのですが、表記が「(複数のアイテム)」になっています。
フィルターフィールドの全アイテムを上から下まで見ましたが、他にチェックは入っていません。
 
手動でフィルターのボタンをクリックすると表れるフィルターのリスト(=チェックボックスがあるリスト)の下の方にある「OK」をクリックすると、「(複数のアイテム)」表記からチェックを入れた番号のみの表記に変わります。
 
VBA処理時、どのようにすればチェックをいれた名称のみの表記になるか、手詰まり状態の為投稿しました。
 
何か解決策をご存知の方がいらっしゃいましたら、アドバイスをいただけると幸いです。
 
宜しくお願い致します。
 
 
 

回答
投稿日時: 21/06/30 06:33:56
投稿者: simple

ピボットのレポートフィルタに関する話題ですね?
 
(1)
> その点は、正しくなされているのですが、表記が「(複数のアイテム)」になっています。
> フィルターフィールドの全アイテムを上から下まで見ましたが、他にチェックは入っていません。

原因はにわかには判明しませんが、いったんピボットキャッシュをリフレッシュしてみては?

ActiveSheet.PivotTables(1).PivotCache.Refresh

(2)
> 手動でフィルターのボタンをクリックすると表れるフィルターのリスト(=チェックボックスがあるリスト)の下の方にある「OK」をクリックすると、「(複数のアイテム)」表記からチェックを入れた番号のみの表記に変わります。
> VBA処理時、どのようにすればチェックをいれた名称のみの表記になるか、手詰まり状態の為投稿しました。

その「表記」というのは、
・フィルタの結果ということですか?
・それとも、項目の選択肢の表記ということですか?
(a)
後者の積もりであれば、プロパティはたしかにVisibleですが、
これはチェックの有無を示すものであって、
選択肢の表示の有無とは無関係ではないですか?(少なくとも私はそう理解しています。)
できないと思いますよ。だって、追加でチェックしたくても、できないじゃないですか。
(b)
前者であれば、可能なはずです。
できなければ、その例を示されるとよいでしょう。
事象が再現する簡単なものを提示してください。
 
なお、よく話題になるのは、すべてを同時にFalseにすることはできない点です。
例えば、最初の項目だけに既にチェックが入っている状態で、
    If p.Value = "2番目の項目" Then
        p.Visible = True
    Else
        p.Visible = False
    End If
とすると、唯一チェックされていた、最初の項目に対して
p.Visible = Falseを実行しようとするので、そこでエラーになります。

投稿日時: 21/06/30 10:38:45
投稿者: Alice

simple様
 
早速のコメントありがとうございます。
 
下記コードを入れて見ましたが、状況は変わらずでした。
 

引用:
ActiveSheet.PivotTables(1).PivotCache.Refresh

 
引用:
その「表記」というのは、
・フィルタの結果ということですか?

 
→そうです。
下記サイトの「レポートフィルターの確認」項目で、「販売店フィールドには、「熊本」「佐賀」「大分」「長崎」「福岡」の項目があります。」の記載の下にある画面でいうと、B1セルにある「(すべて)」の箇所がこちらの状況だとチェックは1箇所しかつけていないのに「(複数のアイテム)」となっています。
 
この画面にある「複数のアイテムを選択」の下にある「OK」をクリックすると「(複数のアイテム)」からチェックをいれている項目のみが表示されるようになります。
(極力イメージがつきやすそうな画面をネットで探したのがこちらです。)
 
https://www.hamachan.net/2013/10/blog-post_10.html
 
当初の質問では、ふれなかったのですが、元々のピボットテーブルで使用されていたレポートフィルターの名称が別名称に変更される可能性がある為、変更時には変更された名称をレポートフィルターに設定するようにしています。(下記コードにて)
 
今回のようなことが生じてしまうのは、その点も関係しているのか?と思ってきました
 
With ActiveSheet.PivotTables(1).PivotFields(filterField)
    .Orientation = xlPageField
    .Position = 1
End With

回答
投稿日時: 21/06/30 13:03:36
投稿者: simple

事象が再現できる簡単なデータを示すことはできませんか?
一部だけ示されても、隠れている条件がある可能性は残ってしまいます。
こちらでも当然何度もトライしていますが、再現しません。
回答する側で事象が再現できないと、前に進まないのでは?

・このようなワークシートのデータをもとに、
・このようなコードを実行すると、
・ひとつしか指定していないのに、複数のアイテムと表示されます。」
こういう説明をしてもらえませんか?

回答
投稿日時: 21/06/30 14:28:04
投稿者: sk

引用:
※ 「filterField」はフィールド名の変数
※ active_depCODEはチェックを入れたいピボットアイテムの変数

引用:
その点は、正しくなされているのですが、表記が「(複数のアイテム)」になっています。
フィルターフィールドの全アイテムを上から下まで見ましたが、他にチェックは入っていません。

引用:
VBA処理時、どのようにすればチェックをいれた名称のみの表記になるか

With ActiveSheet.PivotTables(1).PivotFields(filterField)
    .ClearAllFilters
    .CurrentPage = active_depCODE
    For Each p In .PivotItems
        If p.Value <> active_depCODE Then
            p.Visible = False
        End If
    Next p
    .EnableMultiplePageItems = True
End With
 
-----------------------------------------------------------------
 
上記のコードのような処理を実行したい、ということでしょうか。

回答
投稿日時: 21/06/30 18:11:53
投稿者: sk

sk さんの引用:
.CurrentPage = active_depCODE

CurrentPage プロパティをいじくった状態でブックを保存して開き直すと
ブックの回復を要求される場合があるようなので、次の方式に変更します。
 
------------------------------------------------------------------
 
With ActiveSheet.PivotTables(1).PageFields(filterField)
    .ClearAllFilters
    For Each p In .PivotItems
        If p.Value = active_depCODE Then
            p.Visible = True
        Else
            p.Visible = False
        End If
    Next p
    .EnableMultiplePageItems = True
End With
 
------------------------------------------------------------------

投稿日時: 21/07/01 09:24:54
投稿者: Alice

sk様
 
アドバイスありがとうございます。
 
ただ、下記のようにしてみたのですが、表示は変わらずでしたSad
 

引用:
With ActiveSheet.PivotTables(1).PageFields(filterField)
    .ClearAllFilters
    For Each p In .PivotItems
        If p.Value = active_depCODE Then
            p.Visible = True
        Else
            p.Visible = False
        End If
    Next p
    .EnableMultiplePageItems = True
End With

投稿日時: 21/07/01 09:47:05
投稿者: Alice

新規でExcelを立ち上げて、ピボットテーブルを作成し、今回解決できない部分のコードを実行させてみましたが同様の現象(=チェックは1つだけだが表示は「(複数のアイテム)」表示)になります。
(使用Excel⇒Microsoft Excel for Microsoft 365 MSO)
 
・「Sheet1」シートにピボットテーブルの元データ
※ データ開始行は1行目から
 
A列     B列        C列       D列      E列       F列
部門コード 勘定科目コード    勘定科目    4月        5月        6月
12428      65024    有形資産減価償却費    88145     88145 88145
29456      65024    有形資産減価償却費    229443 229443 229443
29456      65024    有形資産減価償却費    830107 830107 830107
10264      65024    有形資産減価償却費    477619 477619 477619
10264      65025    無形資産減価償却費    1775     1775     1775
10264      65025    無形資産減価償却費    750041     750041     750041
10264      65024    有形資産減価償却費    431307     431307     431307
10264      50201    有形資産償却−建付    14280 14280 14280
36970      65025    無形資産減価償却費    13333 13333 13333
36970      65024    有形資産減価償却費    10781     10781     10006
 
・「36970」シート
上記データのピボットテーブルあり
 
-----------------------------------------------------
・「Sheet1」シートのA1セル「部門コード」が「部門コード(テスト)」に変更
(タイトル行が変更になることがある為)
 
※このコードは、上記ピボットテーブルがあるファイルに「標準モジュール」挿入
 (現象が再現するか確認する為だけなので)
 

Sub test()

table_startrow = 1
Table_startCol = 1

 'ピボットテーブルがあるシート名
  active_depCODE = ActiveSheet.Name
            
 'ピボットテーブルのデータソースのシート名取得
  tmpPV = Application.ConvertFormula(ActiveSheet.PivotTables(1).SourceData, xlR1C1, xlA1, xlRelative)
 PVsheet = Left(tmpPV, InStr(tmpPV, "!"))
            
 'ピボットテーブルの最終行番号と列番号
 Worksheets("Sheet1").Activate
            
  Table_EndRow = Cells(Rows.Count, Table_startCol).End(xlUp).Row
  Table_EndCol = Cells(table_startrow, Columns.Count).End(xlToLeft).Column
            
  'ピボットテーブルのデータソース作成
  sourceArea = PVsheet & Range(Cells(table_startrow, Table_startCol), Cells(Table_EndRow, Table_EndCol)).Address(ReferenceStyle:=xlR1C1)
            
  'データソース範囲変更
  Worksheets(active_depCODE).Activate
  ActiveSheet.PivotTables(1).SourceData = sourceArea
  
  'ピボットテーブル更新
  ActiveSheet.PivotTables(1).PivotCache.Refresh
                
  '予算データのタイトル名称が変更の場合、ピボットテーブル設定し直し
   filterfield = "部門コード(テスト)" '←変更になった名称
                        
  With ActiveSheet.PivotTables(1).PivotFields(filterfield)
         .Orientation = xlPageField
         .Position = 1
    End With
                            
 'アクティブシート名にあるコードと同値でフィルタをかける
                            
 For Each p In ActiveSheet.PivotTables(1).PivotFields(filterfield).PivotItems
                                
  If p.Value = active_depCODE Then
     p.Visible = True
                                        
  Else
     p.Visible = False
                                    
   End If
                                
Next p
                                
'ピボットテーブル更新 ←もう一回更新させてみても「(複数のアイテム)」表示
ActiveSheet.PivotTables(1).PivotCache.Refresh
End Sub

回答
投稿日時: 21/07/01 11:02:36
投稿者: simple

事象再現しました。(当方、XL2010で実験しました。)
お疲れさまでした。
しかしながら、原因は不明です。
# 時に違う結果になることもあるようです。
 
対症療法ですが、各項目のVisibleプロパティを操作する前の段階で、

   With ActiveSheet.PivotTables(1).PivotFields(filterfield)
        .Orientation = xlPageField
        .Position = 1
        .EnableMultiplePageItems = True    '' ■■追加
   End With
のようにすると、事象は起きないようです。
 
(なお、くどいですが、10264だけを選択した状態で実行すると、
 エラーになりますから、そのあたりは別途の対応が必要かと思います。
 別の話ですが。)

投稿日時: 21/07/01 14:30:59
投稿者: Alice

simple様
 
回答ありがとうございます。
 
simple様のアドバイスどおりで解決しました!!!
 
本当に大感謝です。
↓ここでの措置で解決につながるとは、思いつきませんでした。。
 

引用:
対症療法ですが、各項目のVisibleプロパティを操作する前の段階で、
   With ActiveSheet.PivotTables(1).PivotFields(filterfield)
        .Orientation = xlPageField
        .Position = 1
        .EnableMultiplePageItems = True '' ■■追加
   End With
のようにすると、事象は起きないようです。