EXCEL VBA ピボットテーブルで全体の最終作業日だけ表示したい

Question

EXCEL2013 Win7

図のようなデータからピボットテーブルを作るVBAを書きました。
「作業日受入日」の最大日付を集計する式をつけくわえたところ
全ての項目に対する最大日付が表示されてしまいました。
「区分」による集計はしつつも
全体の最終作業日だけを表示する方法はないのでしょうか。

皆様よろしくお願いいたします。

 Sub ピボットテーブルを作成するVBA()
   
    Dim ds As Variant
    Dim er As Long
    
    Dim ws As Worksheet
    Dim pvc As PivotCache
    Dim pvt As PivotTable
    Dim pvf As PivotField

     
'●集計しようとしているシート名の取得
    ds = ActiveSheet.Name
    
'●データの最終行番号を取得
    er = Worksheets(ds).Cells(Rows.Count, 1).End(xlUp).Row

'●ピボットテーブルの作成
    Set ws = Sheets.Add
    Set pvc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                                SourceData:=ds & "!R1C1:R" & er & "C7", _
                                                Version:=xlPivotTableVersion15)
    Set pvt = pvc.CreatePivotTable(TableDestination:=ws.Name & "!R3C1", _
                                                TableName:="PIVOT", _
                                                DefaultVersion:=xlPivotTableVersion15)                                         
    With pvt
            With .PivotFields("工場コード")
                .Orientation = xlRowField
                .Subtotals(1) = False

            End With
            With .PivotFields("作業整理番号")
                .Orientation = xlRowField
                .Subtotals(1) = False

            End With
            With .PivotFields("品名")
                .Orientation = xlRowField
                .Subtotals(1) = False

            End With

            With .PivotFields("区分")
                .Orientation = xlColumnField
                .Position = 1
            End With
            
            With .PivotFields("金額")
                .Orientation = xlDataField
                .Function = xlSum
                .Caption = "合計金額"
                .NumberFormat = "#,##0"
            End With
            
            With .PivotFields("作業日受入日")
                .Orientation = xlDataField
                .Function = xlMax
                .Caption = "最終作業日"
                .NumberFormat = "yyyy/mm/dd"
            End With
            
 　　   .RowAxisLayout xlTabularRow
 
    End With    
    End Sub

CoalTar · Accepted Answer

できたようでよかったです
配列数式で一部不足があったので訂正します
 "=MAX(IF(R2C1:R" & er & "C1=RC[-7],IF(R2C2:R" & er _
& "C2=RC[-6],IF(R2C3:R" & er & "C3=RC[-5],R2C7:R" & er & "C7))))"
試験したときの32(行)が一部残ったまんまでしたm(_ _)m

「工場コード、作業整理番号、品名が一致する」条件のもと、作業受入日の最大値を計算しています。
本当はこの計算結果もピボットテーブルで出せるのでそこから持って来たかった。
元の表→ピボットテーブルで最終日計算→元の表に追加→ピボットテーブル作成
が
元の表→元の表に配列数式で最終日計算を追加→ピボットテーブル作成
となってます。
配列数式利用で計算速度が遅いかも。なのでFor～nextに
.Range("h" & n) = .Range("h" & n).Value
を入れてしまえばいいかもです。

ピボットテーブル作成マクロの勉強になりました(^^)/

CoalTar · Answer

No1の回答は作成したピボットテーブルのデータ取得でしたが、わからないので
配列数式で最終日を取得しました。
ピボットテーブルに組み込んだので位置がいまいちかも

Sub ピボットテーブルを作成するVBA2()
　 　 Dim ds As Variant
　 　 Dim er As Long

Dim ws As Worksheet
　 　 Dim pvc As PivotCache
　 　 Dim pvt As PivotTable
　 　 Dim pvf As PivotField
　 　 Dim n As Long '追加

'●集計しようとしているシート名の取得
　 　 ds = ActiveSheet.Name

'●データの最終行番号を取得
　 　 er = Worksheets(ds).Cells(Rows.Count, 1).End(xlUp).Row

'●追加
　 　 With Worksheets(ds)
　 　 　 　 .Range("H1") = "最終"
　 　 　 　 For n = 2 To er
　 　 　 　 　 　 .Range("H" & n).FormulaArray = _
　 　 　 　 　 　 "=MAX(IF(R2C1:R32C1=RC[-7],IF(R2C2:R" & er & "C2=RC[-6],IF(R2C3:R" & er & "C3=RC[-5],R2C7:R" & er & "C7))))"
　 　 　 　 Next n
　 　 　 　 .Range("h2:h" & er) = .Range("h2:h" & er).Value
　 　 End With

'●ピボットテーブルの作成
　 　 Set ws = Sheets.Add
　 　 Set pvc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
　 　 SourceData:=ds & "!R1C1:R" & er & "C8", _
　 　 Version:=xlPivotTableVersion15) 'C7→C8に変更
　 　 Set pvt = pvc.CreatePivotTable(TableDestination:=ws.Name & "!R3C1", _
　 　 TableName:="PIVOT", _
　 　 DefaultVersion:=xlPivotTableVersion15)
　 　 With pvt
　 　 　 　 With .PivotFields("工場コード")
　 　 　 　 　 　 .Orientation = xlRowField
　 　 　 　 　 　 .Subtotals(1) = False
　 　 　 　  End With
　 　 　 　 With .PivotFields("作業整理番号")
　 　 　 　 　 　 .Orientation = xlRowField
　 　 　 　 　 　 .Subtotals(1) = False
　 　 　 　  End With
　 　 　 　  With .PivotFields("品名")
　 　 　 　 　 　  .Orientation = xlRowField
　 　 　 　 　 　 .Subtotals(1) = False
　 　 　 　 End With
　 　 　 　 With .PivotFields("区分")
　 　 　 　 　 　 .Orientation = xlColumnField
　 　 　 　 　 　 .Position = 1
　 　 　 　 End With
　 　 　 　  With .PivotFields("金額")
　 　 　 　 　 　  .Orientation = xlDataField
　 　 　 　 　 　 .Function = xlSum
　 　 　 　 　 　 .Caption = "合計金額"
　 　 　 　 　 　 .NumberFormat = "#,##0"
　 　 　 　 End With

'行フィールドへ
　 　 　 　 With .PivotFields("最終")
　 　 　 　 　 　 .Orientation = xlRowField
　 　 　 　 　 　 .Subtotals(1) = False
　 　 　 　 　 　 .Caption = "最終作業日"
　 　 　 　 　 　 .NumberFormat = "yyyy/mm/dd"
　 　 　 　 End With

.RowAxisLayout xlTabularRow
　 　  End With
 End Sub

CoalTar · Answer

実際に試していないので、、、

ご質問のマクロ
With .PivotFields("金額")
 .Orientation = xlDataField
 .Function = xlSum
 .Caption = "合計金額"
 .NumberFormat = "#,##0"
 End With
を除いて作成

元の表に作業列を追加し、GetPivotedata関数で
=GETPIVOTDATA("作業日受入日",Sheet3!$A$3,"工場コード",A2,"作業整理番号",B2,"品名",C2)
で最大値列を追加

コードは
ピボットテーブルを作成する元の範囲を作業列分の1列追加し、(C7→C8)
With .PivotFields("作業日受入日")
 .Orientation = xlDataField
 .Function = xlMax
 .Caption = "最終作業日"
 .NumberFormat = "yyyy/mm/dd"
 End With
を削除し
作業列を行フィールドに追加してはどうでしょうか？

ただし、画像がとても荒く、目的のものになっているか不明。
当方、力不足でピボットテーブル作成時のシート名取得方法がわかりません。
ご質問のコード、絶対自分じゃ作れませんから(^^;
回答が内容でしたので投稿。参考まで

EXCEL VBA ピボットテーブルで全体の最終作業日だけ表示したい

できたようでよかったです

No1の回答は作成したピボットテーブルのデータ取得でしたが、わからないので

実際に試していないので、、、

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング