Excel VAB ピボットテーブル を期間でグループ化して集計する

スポンサーリンク
スポンサーリンク

ピボットテーブル を期間でグループ化して集計する方法

ピボットテーブルにある日付フィールドを
期間でグループ化するには
Rangeオブジェクトの
Group【グループ】メソッドを使用します。
Group【グループ】メソッドは
引数Periods【ピリアズ】の設定方法により
日付フィールドを月単位、四半期単位、年単位に
グループ化して集計することができます。
Periods【ピリアズ】は期間という意味があります。

Group【グループ】メソッド

Group【グループ】メソッドの書式

[]内は省略可能です。
オブジェクト.Group( [Start, End, By, Periods] )


設定値(引数)の説明

  • オブジェクト(必須)
    Rangeオブジェクトを指定します。
    ピボットテーブル内のグループ化したいフィールド内の
    単一セルを指定します。
    セル範囲を指定するとエラーにはなりませんが処理されません。
  • Start【スタート】(省略可)
    グループ化する最初の値を指定します。
    省略した場合はTrueが指定され、フィールドの最初の値になります。
    開始日を指定する場合はDateSerial【デイトシリアル】関数で指定します。
  • End【エンド】(省略可)
    グループ化する最後の値を指定します。
    省略した場合はTrueが指定され、フィールドの最後の値になります。
    終了日を指定する場合はDateSerial【デイトシリアル】関数で指定します。
  • By【バイ】(省略可)
    各グループのサイズを指定します。
    数値フィールドの場合は、各グループのサイズを指定します。
    引数Periods【ピリアズ】で指定する配列の4つ目の要素のみがTrueの場合は
    各グループ内の日数を指定します。
    上記以外は無視され既定のグループサイズが設定されます。
  • Periods【ピリアズ】(省略可)
    日付フィールドの場合に指定します。
    グループの期間を指定するブール型の配列をArray関数を使用して指定します。
    配列の要素をTrueにすると対応する期間のグループが作成され
    Falseにするとグループは作成されません
    例えば、月単位でグループ化する場合は


    Periods:=Array(False,False,False,False,True,False,False)


    として5つ目だけをTrueにします。

    配列の要素グループ期間
    1
    2
    3時間
    4
    5
    6四半期
    7

各店の1年間の日別売上データを期間単位でグループ化して集計する例

実行前

ピボットテーブルを作成するコード例

Sub テーブル作成()
Dim キャッシュ As PivotCache
Dim テーブル As PivotTable
Set キャッシュ = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=Range("A1").CurrentRegion)
Set テーブル = キャッシュ.CreatePivotTable _
(tabledestination:=Range("F1"), TableName:="2017年度各店売上")
With テーブル
.PivotFields("売上日").Orientation = xlRowField
.PivotFields("店名").Orientation = xlColumnField
With .PivotFields("売上額")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
End With
End With
End Sub

ピボットテーブルの作成方法については
ピボットテーブルを作成するをご覧下さい。

実行結果

期間をグループ化するコード例

Sub 期間グループ化()
Range("F3").Group _
periods:=Array(False, False, False, False, True, True, True)
With ActiveSheet.PivotTables("2017年度各店売上").PivotFields("四半期")
    .PivotItems("Qtr1").Caption = "第4四半期"
    .PivotItems("Qtr2").Caption = "第1四半期"
    .PivotItems("Qtr3").Caption = "第2四半期"
    .PivotItems("Qtr4").Caption = "第3四半期"
End With
End Sub

コードの解説

2行目
【Range(“F3”).Group _】

Grpup【グループ】メソッドのオブジェクトは
グループ化するフィールドの単一セルを指定するので
F3セルを指定します。


3行目
【periods:=Array(False, False, False, False, True, True, True)】
Grpup【グループ】メソッドの引数Periods【ピリアズ】で
グループ化する期間を指定します。
配列の要素5の月、6の四半期、7の年をTrueに指定して
グループ化します。7の年をTrueにしないと
元データは4月から始まりますが1月始まりに並べ替えられてしまします。


4行目~8行目
【With ActiveSheet.PivotTables(“2017年度各店売上”).PivotFields(“四半期”)
.PivotItems(“Qtr1”).Caption = “第4四半期”
.PivotItems(“Qtr2”).Caption = “第1四半期”
.PivotItems(“Qtr3”).Caption = “第2四半期”
.PivotItems(“Qtr4”).Caption = “第3四半期”】

エクセルでは、第1四半期を年度に関わらず1月~3月に
自動的に設定してしまうので、4月始まりの場合は
4月から6月を第1四半期にしたいので、ラベルを変更する必要があります。
ラベルを変更するには、Worksheetオブジェクトの
PivotTables【ピボットテーブルズ】メソッドを使用して
ピボットテーブルを参照し、PivotFields【ピボットフィールズ】メソッド
ラベルを変更したいフィールドを参照します。
さらに、PivotItems【ピボットアイテム】メソッドでラベルを参照します。
引数のラベル名は四半期の場合は
第1四半期はQtr1、第2四半期はQtr2、第3四半期はQtr3、第4四半期はQtr4
と指定して参照します。QtrはQuarter【クウォータ】(四半期)の略です。
参照したラベルをCaption【キャプション】プロパティを使用して
変更します。Caption【キャプション】はタイトルという意味があります。


実行結果


以上で
ピボットテーブル を期間でグループ化して集計する
方法についての解説を終了します。
ありがとうございました。

スポンサーリンク
スポンサーリンク

フォローする

スポンサーリンク
スポンサーリンク