[[20171128112908]]　『ピボットテーブルフィールドの最後のアイテムを取』（わたなべ）　ページの最後に飛ぶ

『ピボットテーブルフィールドの最後のアイテムを取得したい』（わたなべ）

 外部データソース使用のピボットテーブルがあります
 日のフィールドにおける最終日を取得したいです

 このフィールドは、行ラベルやレポートフィルターにいれると現在は01～22までを表示(日々変わります)します
 この22(=最終データ)を取得したいのです
 .PivotItems.Countをインデックス番号にいれると、カウントは31、データも31とでました
 つまり、どうやら31まで入っているらしいのですが、ピボットテーブル上では22までしか表示されません

 以下を実行すると、i=23でエラーがでます(そりゃないのですものね）
 1004：PivotItem クラスの Visible プロパティを設定できません。

 Sub ttt()
 Dim i As Long
     With ActiveSheet.PivotTables(1).PivotFields("日")
         For i = 1 To .PivotItems.Count
              .PivotItems(i).Visible = True
         Next i
     End With
 End Sub

 列ラベルにいれて、シートの最終行のデータを取得(.End(xlDown).value)するのはできています

 レポートフィルターにいれたままで22を取得できないのでしょうか？

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

データとピボットテーブルの設定を見ないと、何を言っているのかさっぱり判りません。具体的なデータ例と、行、列、値に何を指定しているのかを教えてください。

そして、ラベルフィルタをどうかけていてもデータ数が知りたい、という意味ならば、ピボットのプロパティではなく、元データの最終行が利用できないのでしょうか？
（???） 2017/11/28(火) 16:02

 >???さん
 説明が不足している上にまったくできておらずすみません

 >データとピボットテーブルの設定
 >具体的なデータ例と、行、列、値に何を指定しているのかを教えてください。

 元のデータは外部ソースになっていて、権限もなくどういったデータかわかりません
 データが多すぎて、詳細表示にするとかたまります

 ピボットの行ラベルに名前、列ラベルは値、値には数Aや数B(仮名)をいれております
 フィルタはかかっていません
 このピボットがいつまでの期間のデータなのかを知りたくて、期間最終日を取得したいです

 おそらくイメージ的には、A列から年月、日、名前、数値A、数値B、数値C　…　と続いていくデータかと思います

 年月フィールドのアイテムは文字列で201707～201711までありますが、データのないアイテムの為、201711しか表示されません
 日フィールドのアイテムは文字列で01から31までありますが、データのないアイテムの為、01～22までしか表示されません
 上記どちらも、フィールドの設定でデータのないアイテムを表示するにチェックを入れるとすべて表示されます
 設定でデータのないアイテムを表示するにチェックをいれなければピボット上では表示されないので、年月フィールドは201711のみ、日フィールドは01～22までの表示となります

 しかし、アイテム数としては前述のとおりなので、
 PivotFields("日").PivotItems(PivotFields("日").PivotItems.Count)とすると、(日付とインデクッス番号が同じでややこしいのですが)31個目のアイテム名は31なので、31をしめします
 PivotFields("年月").PivotItems(PivotFields("年月").PivotItems.Count)とすると、5個目のアイテム名、201711をしめします

 データのないアイテムは無視した"日フィールド"の最終インデックスである22個目のアイテム"22"を取得したいのです
 行ラベルに日フィールドをつっこめば、01から下に向かって順に並び1番下に22がでますので、それで取得できるのですが、何しろデータが膨大でピボットの組換えに3分弱かかるので避けたいです
 そのアイテムにデータのあるなしのプロパティがあるのでしょうか？？

（わたなべ） 2017/11/28(火) 17:32

何もわかっていませんが、アイテムを削除してしまうと問題ですか？

 Sub test()
    Dim i As Long
    Dim d As Long

    With ActiveSheet.PivotTables(1)
        .PivotCache.MissingItemsLimit = xlMissingItemsNone
        .PivotCache.Refresh

        With .PivotFields("日")
            For i = 1 To .PivotItems.Count
                d = WorksheetFunction.Max(d, Val(.PivotItems(i).Value))
            Next
        End With

        .PivotCache.MissingItemsLimit = xlMissingItemsDefault

    End With

    MsgBox d

 End Sub

（マナ） 2017/11/28(火) 21:06

う～ん。

ちょっと、同じようにピボットテーブルが作ってないと、
代わりにデバッグをしてあげるのは難しいですが、
With句を使わずに、ちゃんと変数を用意して代入してみたら、
ローカルウィンドウで中身が見れるので、
そこから使えそうなプロパティを探してみるというのはいかがでしょうか？

また、

         For i = 1 To .PivotItems.Count
 >              .PivotItems(i).Visible = True
 >
↑のどこでエラーになることが事前にわからないということなら、
On Error ～　ステートメントを使って
エラー回避をすればいいと思います。

Sub ttt()

    Dim objPvt As PivotField
    Dim i As Long
    Dim flg As Boolean

    Set objPvt = ActiveSheet.PivotTables(1).PivotFields("日")
    Stop

    With objPvt
        For i = 1 To .PivotItems.Count
            On Error GoTo ErrH
            .PivotItems(i).Visible = True
            On Error GoTo 0
            If flg Then Exit For
        Next i
    End With

    Exit Sub

ErrH:

    flg = True
    Resume Next
End Sub

（まっつわん） 2017/11/29(水) 09:03

 >マナさん
 うまく説明できずにもうしわけありません
 >.PivotCache.Refresh
 外部ソースデータで権限がないのでできません
 つなぐのに、パスワードも必要です

 >.PivotCache.MissingItemsLimit = xlMissingItemsNone
 まだ理解できていませんので引き続き調べます

（わたなべ） 2017/11/29(水) 10:02

  >まっつわんさん
 説明がうまくできずにすみません
 ありがとうございます

 >どこでエラーになることが事前にわからないということ～エラー回避をすればいいと思います。
 ひらめきました！
 データのないアイテムは.Visibleプロパティが設定できずエラーになります、
 エラーになるとこでループから出せば、データのないアイテムがどこからかわかります!

 ということで以下となりました

  Sub test2()
     Dim i As Long
     Dim d As Long

     With ActiveSheet.PivotTables(1).PivotFields("日")
         d = .PivotItems(.PivotItems.Count)
         'この時点ではデータのないアイテムも何もかもひっくるめて31個のアイテムがあるので
         '.PivotItems.Count は31、.PivotItems(31)は31になります

          On Error GoTo ErrH     
          For i = 1 To .PivotItems.Count
              .PivotItems(i).Visible = True
             'データのないアイテムはエラーになるので飛び出します
             'エラーでないなら次に続きます
         Next i
         On Error GoTo 0

     End With

     Exit Sub
     'エラーでない=31個目までデータがある場合はExitさせる

 ErrH:
     d = ActiveSheet.PivotTables(1).PivotFields("日").PivotItems(i - 1)
     'データがない=エラーになったらここへ飛んでくるので、
     'その１個前のデータのあるアイテムを取得

  End Sub

（わたなべ） 2017/11/29(水) 10:15

 >ひらめきました！

あぁ。。。
ちなみに,,,,,
これだとやっぱりエラーになりますか？

Sub ttt2()

    Dim objPvt As PivotField
    Dim pi As PivotItem
    Dim piLast As PivotItem

    Set objPvt = ActiveSheet.PivotTables(1).PivotFields("日")

    'On Error GoTo WayOut
    For Each pi In objPvt.PivotItems
        pi.Visible = True
        Set piLast = pi
    Next

WayOut:

    MsgBox piLast.Name
End Sub
（まっつわん） 2017/11/29(水) 10:26

 今日になって冷静に考えてみると、余計なことを書きすぎました

 【本来やりたかったこと】
 日というフィールドの、データのないアイテムを無視したアイテム個数が知りたい

 ⇒
  ・日は01からインデックス番号順に昇順で並んでいる
  ・2月のデータだろうと11月のデータだろうと8月のデータだろうと、日フィールドには31までのアイテムが存在する
  ・日毎のデータで、歯抜けにデータのないアイテムが並ぶことはない

 とりあえず、エラーを逆手にとって取得することには成功しました
 フィールドの設定ででーたのないアイテムを表示するという項目があることから、なにかそういったプロパティがあるのではないかと思って質問したのでした

 で、まっつわんさんの
 >変数を用意して代入してみたらローカルウィンドウで中身が見れるので、プロパティを探して
 こんな方法があるとは目からウロコで
 (いつもうなりながらヘルプの文章とネット検索してました)
 やってみて、RecordCountというものがあるのを知りました

 Sub test3()
    Dim i As Long, d As Long

    With ActiveSheet.PivotTables(1).PivotFields("日")
        For i = 1 To .PivotItems.Count
            If .PivotItems(i).RecordCount = 0 Then Exit For
        Next i
        d = .PivotItems(i - 1)
    End With
 End Sub

 これで済みました
 ヘルプにも書いてあったはずですが、目が滑って認識していませんでした
 まるまる変数につっこんでプロパティを探すやり方があっていそうなので、これから活用していきたいと思います

 みなさま、ご迷惑をおかけいたしました
 本当にありがとうございました！！！！

 と、衝突しました
 今から試してみます！

（わたなべ） 2017/11/29(水) 10:48

コメント返信：

[ 一覧(最新更新順) ]

[[20171128112908]] 『ピボットテーブルフィールドの最後のアイテムを取』（わたなべ） ページの最後に飛ぶ

[[20171128112908]]　『ピボットテーブルフィールドの最後のアイテムを取』（わたなべ）　ページの最後に飛ぶ