Excel VBA ピボットテーブルの日付グループ化を使いこなす

Excel2016のピボットテーブルでは、日付のフィールドを行エリアや列エリアに配置したときに、自動的にグループ化されます。ただし、日付は「シリアル値」でないとダメです（文字列ではグループ化されません）

くるみこ

前回記事のサンプルで使っていたVBAマクロで日付フィールドのグループ化を扱う場合について改めて勉強しましょう(^^)/

日付の部分をフィールドに配置すると自動的にグループ化されるのが何故なのかなんて考えたことありませんでした(^^;　よろしくお願いします！

【この記事でわかることは】
・ピボットテーブルの日付フィールドをグループ化する方法
・ピボットテーブルの日付の自動グループ化を解除する方法

前回記事のおさらいは、下のカードをクリックすれば開きます(^^ゞ

Excel VBA ピボットテーブルのオートフォーマットについて

Excel VBA ピボットテーブルの旧(Excel2003)スタイル「オートフォーマット」を使えるようにする方法の解説です。「オートフォーマット」の「レポート」を使う場合のレイアウトが崩れてしまう挙動について、その注意点を解説しています。

くるみこ

前回記事は、ピボットテーブルの旧スタイル設定「オートフォーマット」についての解説記事でした。よければ覗いてみてね(^^)/

ピボットテーブルの日付をグループ化する方法
1. Range.Group メソッド
2. サンプルでグループ化の動作を確認します
日付の自動グループ化を解除する方法
1. 自動グループ化を無効にする方法
2. 機能自体はOFFにせず手動で解除する操作方法
その他の日付関連グループについて
1. 期間の扱いで利用されているもの
2. 日付データから計算する方法
まとめ（おわりに）
1. まとめと感想など
2. 今後の記事について
記事のサンプルファイルをダウンロードできます

ピボットテーブルの日付をグループ化する方法

初めに「マクロの記録」で記録したコードを見てみます。（該当部分を抜粋しています）

    ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("入荷日").AutoGroup
    Range("A4").Select
    Selection.Group Start:=True, End:=True, Periods:=Array(True, True, True, _
        True, True, True, True)

・グループの要素を全部（7項目）適用してみたコードです。
・1行目で、.AutoGroupが適用されているのがわかります。
・3行目、RangeオブジェクトのGroupメソッドで「True」が7個設定されています。

Range.Group メソッド

Rangeオブジェクトの Group メソッドを調べてみました。
「ピボットテーブルフィールドのデータ範囲内の単一セル選択で、そのフィールドの数値または日付ベースでグループ化を実行します」とのことです。

Group メソッドの構文／パラメーター／戻り値

構文

Rangeオブジェクト.Group( [Start, End, By, Periods] )
※Periods＝期間
・Rangeオブジェクトは必須です。
　ピボットテーブル内のグループ化したいフィールドのデータ範囲内の単一セルを指定します。

パラメーター

名前	省略可否	データ型	説明
Start	省略可能	Variant	グループ化する最初の値を指定します。省略または True の指定で、フィールドの最初の値が使用されます。
End	省略可能	Variant	グループ化する最後の値を指定します。省略または True の指定で、フィールドの最後の値が使用されます。
By	省略可能	Variant	フィールドが数値の場合、グループごとのサイズを指定します。フィールドが日付の場合、グループごとの日数を指定します (Periods配列の要素4がTrue で、その他の要素が False の場合)。それ以外の場合、この引数は無視されます。この引数を省略すると自動的に既定のグループサイズを選択します。
Periods	省略可能	Variant	グループの期間を指定するブール値の配列。配列内の要素がTrueの場合、対応する要素に対してグループが作成されます。要素がFalseの場合、グループは作成されません。日付フィールドでない場合、この引数は無視されます。

Periods パラメーターで指定するブール型 (Boolean) の配列は、次の要素です。

配列要素	ピリオド
1	秒
2	分
3	時間
4	日
5	月
6	四半期
7	年

最初の「マクロの記録」のコードは全部「True」だったので「秒」までグループ化しています。
Periods:=Array(True, True, True,True, True, True, True)

では、次の指定は何でしょうか？
Periods:=Array(False,False,False,False,True,True,True)
答え：「月」「四半期」「年」をグループ化指定しています。

戻り値

戻り値は「Variant」バリアント型です。

サンプルでグループ化の動作を確認します

・前回使用したサンプルがこちらです「年」「四半期」「月」がグループ化されています。

グループ化をしないで作成したピボットテーブル

・はじめにグループ化しないでピボットテーブルを作成しました。

'サンプルピボットテーブル作成（グループ化なし）
Sub pivMakeSample()
    ActiveWorkbook.PivotCaches.Create(xlDatabase, "テーブル1") _
            .CreatePivotTable Sheets.Add.Range("A1")
    With ActiveSheet.PivotTables(1)
        .PivotFields("入荷日").Orientation = xlRowField
        .PivotFields("エリア").Orientation = xlColumnField
        With .PivotFields("数量")
            .Orientation = xlDataField
            .NumberFormat = "#,##0_ "
        End With
    End With
End Sub

「年」だけグループ化するVBAマクロ

'日付グループ化サンプル（年）
Sub pivGroupSample01()
    With ActiveSheet.PivotTables(1)
        '1=秒,2=分,3=時間,4=日,5=月,6=四半期,7=年
        .PivotFields("入荷日").DataRange.Item(1).Group _
                Periods:=Array(False, False, False, _
                    False, False, False, True)
    End With
End Sub

・Periods:=Array(False,False,False,False,False,False,True)
・要素は、「1番目=秒,2番目=分,3=時間,4=日,5=月,6=四半期,7番目=年」です。
・7番目の要素だけ True を指定しています。
・このようにしてグループ化したい部分を True にすればOKです。

テスト用に要素を指定しやすくしたサンプルコード

'日付グループ化サンプル（四半期だけ指定した例）
Sub pivGroupSample02()
    Dim i As Long
    Dim flName As String
    Dim blArr(1 To 7) As Boolean
    For i = 1 To 7
        blArr(i) = False
    Next
    '1=秒,2=分,3=時間,4=日,5=月,6=四半期,7=年
    For i = 6 To 6　　'←ここの数字を変更してください
        blArr(i) = True
    Next
    With ActiveSheet.PivotTables(1)
        .PivotFields("入荷日").DataRange.Item(1).Group _
                Periods:=blArr
        If blArr(6) = True Then
            On Error Resume Next
            If IsError(.PivotFields("四半期")) Then
                flName = "入荷日"
            Else: flName = "四半期"
            End If
            On Error GoTo 0
            With .PivotFields(flName) '入荷日
                .PivotItems("Qtr1").Caption = "第4四半期"
                .PivotItems("Qtr2").Caption = "第1四半期"
                .PivotItems("Qtr3").Caption = "第2四半期"
                .PivotItems("Qtr4").Caption = "第3四半期"
            End With
        End If
    End With
End Sub

【コード解説】
・5行目「Dim blArr(1 To 7) As Boolean」で、ブール型の配列（1～7）を宣言しています。
・6～8行目のループで一旦すべての要素に「False」を代入しています。
・10～12行目のループで「グループ化」する要素を番号で指定します。サンプルは「6=四半期」だけ指定している例です。「True」を代入しています。
・16～28行目では、「四半期」が指定された場合の設定を処理する部分です。
・16行目で「四半期」の要素「6」が指定されているか判定しています。
・18行目「IsError(.PivotFields(“四半期”))」で「四半期」というフィールドがあるかどうか調べています。無い場合はエラーが発生するので、処理停止しないように「On Error Resume Next」を17行目に設定しています。
・24～27行目で、PivotItem の見出しを変更しています。変更しない場合は、「第１四半期」が1月～3月となってしまうので、4月スタートの年度の指定にしています。（下表参照）

Caption	指定しない場合の期間	指定した期間（年度で指定）
第１四半期	1月～3月	４月～6月
第2四半期	４月～6月	7月～9月
第3四半期	7月～9月	10月～12月
第4四半期	10月～12月	1月～3月

「年」「四半期」をグループ化する例

For i = 6 To 7　　　　　　'「6=四半期」「7=年」を指定
    blArr(i) = True
Next

「年」～「日」までグループ化する例

For i = 4 To 7　　　'「4=日」「5=月」「6=四半期」「7=年」を指定
    blArr(i) = True
Next

日付の自動グループ化を解除する方法

「自動グループ化」機能自体を無効にする方法と、「自動グループ化」後に解除する方法を解説しておきます。

自動グループ化を無効にする方法

そもそも昔のピボットテーブルは「自動グループ化」はなかったから「自動グループ化」なんてしてほしくないという方も結構いるようです。

オプション設定で機能を無効化にする方法は、次の順に操作してください。（Excel2016の場合）
［ファイルメニュー］⇒一番下の「オプション」⇒［Excel のオプション］ダイアログ⇒［詳細設定］⇒［データ］の項目 [ピボットテーブルで日付 / 時刻列の自動グループ化を無効にする] のチェックボックスをオンにすると無効化されます。

これで、ピボットテーブル作成時に勝手にグループ化されてしまうことはなくなります。

機能自体はOFFにせず手動で解除する操作方法

配置直後のタイミングで簡単に解除する方法

[日付] をフィールドに配置した直後にグループ化されたら、[Ctrl] キー＋ [Z] キー (元に戻すのショートカットキー) を実行すると、グループが解除されます。（グループ化されていない状態に戻るということです）

手動でグループを解除する方法

グループ化を解除したいフィールドを右クリックして [グループ解除] をクリック、または [ピボットテーブルツール] の [分析] タブの [グループ解除] をクリックすると解除できます。

ただし [グループ解除] したり［グループ化］ダイアログボックスから、グループ化対象から外すと、フィールドの一覧からも削除されます。

列ラベルなどで使用したいなどの場合は、 [グループ解除] したり［グループ化］ダイアログボックスで削除せずに、［フィールドリスト］からチェックボックスをオフにしておいた方がいいと思います。

［フィールドリスト］に残っていればいつでもすぐに利用できます。

その他の日付関連グループについて

日付のグループ化で規定以外に指定したいものにはどんなものがあるでしょうか。
思いつくものを列挙してみたいと思います。

期間の扱いで利用されているもの

・四半期の扱い（期の始まりが6月からスタートするなど特殊なもの）
・年度の扱い（これも四半期と同様）
　日本では通常、4月が年度開始だけど決算期に合わせる場合等のケースもあります。
・上旬／中旬／下旬（月）
・上期／下期（月）
・上半期／下半期（年・年度）
・第1週~第5週（月）
・週間（始まりが日曜・月曜の違いなどあり）
・平日・休日
・営業日・休業日
などなど

日付データから計算する方法

「年度の計算」（4月から始まる）

IF関数で年度を計算する方法（A2セルに日付データがあるものとする）

=IF(MONTH(A2)<=3,YEAR(A2)-1,YEAR(A2))

「月」が3以下の場合、「年」を一つ戻す。それ以外は「年」のまま

EDATE関数で3か月前の年を計算する方法

=YEAR(EDATE(A2,-3))&”年度”　（文字列「年度」を連結しています）

EDATE関数で3か月前の日付をYEAR関数で年だけ取り出します。
仕組みは、1日前の3/31は前年度ですよね。でも普通にYEAR関数で年を計算すると4月と同じ年になってしまうので3か月前の12/31の年を計算するわけです。

ただし、これはワークシート上での計算方法です。実は「EDATE関数」はVBAでは利用出来ません。

VBAの場合は「DateAdd関数」を使います

= DateAdd(“m”, -3, Cells(2, 1).Value) 　のようになります。

「DateAdd関数」は任意の日付や時間に特定の間隔を追加してその結果を返します。
※DateAdd関数は無効な日付を返しません。（例：3/31の1か月後は4/30となります）
【DateAdd関数の構文】DateAdd(Interval,Num,Date)
・引数Intervalには、追加する日付や時間の間隔を指定します。
・引数Numには、Dateに対して増加させる日付や時間を指定します。
・引数Dateには、Numを増加させる元になる日付や時間を指定します。
引数Intervalに指定できる間隔は次のとおりです。

設定値	内容
yyyy	年
q	四半期
m	月
y	年間通算日
d	日
w	週日
ww	週
h	時
n	分
s	秒

上期・下期を判定する計算式

IF関数で判定する方法

=IF(AND(3<MONTH(A2),MONTH(A2)<=9),”上期”,”下期”)

「月」が3より大きくて９以下は「上期」それ以外は「下期」です。
先ほどの年度の計算とあわせると
=YEAR(EDATE(A2,-3))&”年度”&IF(AND(3<MONTH(A2),MONTH(A2)<=9),”上期”,”下期”)

のような感じで計算できます。

VBAでマクロを書くのはかなり大変です。それよりも、元データにフィールドを追加して利用したほうが簡単・便利・確実だと思います。

テーブルやセル範囲の元データに必要数の列を「日付」列の右横に挿入し、計算式をセットします。
セットする計算式は、前述のとおりです。セル範囲の元データの場合はデータソースの設定を変更して更新する必要があるので注意してください。（テーブルの場合は更新処理だけでOKです）

Excel VBA テーブルの列を追加／削除する方法について

Excel VBA テーブルの列を追加・挿入したり削除する方法を解説しています。列の追加・挿入ではタイトルを同時に設定する方法。列の削除では最終列をListColumns.Countで求める方法などを解説しています。サンプルも用意しています

まとめ（おわりに）

・いかがでしたでしょうか？
・「いつも汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーを念頭に記事を書いています。
・今回も、記事内で使用したコードのサンプルファイルを登録していますのでご利用ください。
・今までの記事のサンプルも登録していますのでよろしければお使いください(^^)

まとめと感想など

くるみこ

どうですか。規定でグループ化できるもの以外はVBAマクロで設定するよりも、元データに項目を追加して計算式をセットした方が良さそうですね(^^)

グループから外す場合［フィールドリスト］のチェックを外した方が良いことがわかりすごく勉強になりました(^^)/

くるみこ

ここまでピボットテーブルについて勉強してきましたが、次回はピボットグラフについて少し勉強したいと思います。

【今回わかったことは】
・ピボットテーブルの日付フィールドをグループ化する方法がわかりました
・ピボットテーブルの日付の自動グループ化を解除する方法がわかりました
・手動でグループを解除する方法と日付期間の計算方法例がわかりました

★★★ ブログランキング参加中！クリックしてね(^^)/ ★★★

にほんブログ村

Visual Basicランキング

今後の記事について

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m

【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にもご期待ください(^^)/