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【キャプション】はタイトルという意味があります。


実行結果


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

スポンサーリンク

関連記事・広告