ExcelVBAとピボットテーブル:第5回 日付のグループ化

カテゴリー名: [ExcelVBAとピボットテーブル

2017/08/11

当シリーズでは、Excelのピボットテーブルをプログラミングのノウハウで生成するよう試みます。

 今回は、日付のグループ化を取り上げます。

 日付ごとに「商品」 「売上」 「仕入原価」を記録したデータがあるとき、
四半期単位とか月単位の集計を行います。

 また、グループ化からは脱線しますが、最後の方で集計フィールドを取り上げています。売上から仕入原価を引き算して「利益」の項を設ける方法です。

 当Webページで紹介するマクロファイルやVBScriptのファイル一式は、 vovPIVOT05.zip という圧縮ファイルに同梱しておきます。


《このページの目次》


    

1. 阻害データの概要

 今回の素材データは pt_source02.xls です。

 「日付」 「商品」 「売上」 「仕入原価」が記録されています。

 日付には、「2016/3/21」のような2016年1月1日〜12月31日の日付が入っています。

 商品には四種類あります。1) 調味料、2) 飲料、3) 乳製品、4) 魚介類。

 売上と仕入原価は、乱数発生させた値です。

 具体的には次のようなデータです。冒頭のみ掲載。

日付 商品 売上 仕入原価
2016/1/1 調味料 35 21
2016/1/1 飲料 80 57
2016/1/1 乳製品 234 173
2016/1/1 魚介類 196 150
2016/1/2 調味料 64 41
2016/1/2 飲料 40 29
2016/1/2 乳製品 93 62
2016/1/2 魚介類 360 285

    

 今回はデータ中に空白セルはありません。

 また、データ領域に名前を付けてありません。

 つまり「名前付き範囲」になっていません。

 それから、pt_source02.xls には三つのワークシートがあります。

 第一が「東京支店」、第二が「名古屋支店」、第三が「大阪支店」です。

 今回は第一の「東京支店」だけを扱います。

 複数のワークシートを素材にする方法は、次回に取り上げたいとおもいます。

目次に戻る


2. 日付のグループ化の基本形

 四半期単位でのグループ化を取り上げます。

(1) Groupメソッドのパラメータ

 「日付」を手がかりにしてピボットフィールドを設定し、その DataRange に対して Groupメソッドを施すという点は、身長のような数値によるグループ化と同じです。

 ただ、Groupメソッドの第四パラメータ(日付処理用のパラメータ)を指定するところが違います。

 Groupメソッドのパラメータには次のものがあります。

    

 Periodsというパラメータは、7つの要素からなる配列で指定します。

 各要素には True または False をセットします。

 たとえば、6番目のみTrueで、残りがFalseだと、四半期単位のグループ化になります。

 配列の要素の意味は下のとおり。

 1:秒、2:分、3:時、4:日、5:月、6:四半期、7:年。

目次に戻る


(2) 四半期単位でのグループ化

 「日付」に四半期単位でのグループ化を施します。

 次のような表を作ります。

期間区分 合計/売上 合計/仕入原価
第1四半期 54778 40924
第2四半期 52089 38850
第3四半期 57021 42221
第4四半期 54357 40056
総計 218245 162051

    

 「日付」のピボットフィールドに四半期単位のグループ化を施す箇所を、VBAのコードで示すと次のようになります。

 変数argAryは、七個の要素を記録できる配列です。

 予め Dim argAry(7) As Boolean のように宣言してあります。

For i = 1 To 7
    argAry(i) = False
Next
argAry(6) = True
With ptObj.PivotFields("日付")
    .Orientation = xlRowField
    .LabelRange.Value = "期間区分"
    .DataRange.Cells(1).Group Periods:=argAry
End With

 上記が日付のグループ化の要点となる記述です。

 argAry(6) = True のところを argAry(5) = True とすれば月単位のグループ化になります。

目次に戻る


(3) 四半期単位でのグループ化のVBAマクロ

 先に掲げた表を作成する VBAマクロの全体は下のとおりです。

 配列の添え字を 0 からではなく 1 から始めるようにしました。

 1Option Base 1  ' 配列の添え字を 1 から始める
 2Sub Macro1()
 3    Dim pName As String, bName As String
 4    Dim ws As WorkSheet, rngName As String
 5    Dim ptCache As PivotCache, ptObj As PivotTable
 6    Dim argAry(7) As Boolean, i As Integer
 7    
 8    pName = ThisWorkbook.Path  ' 本ワークブックのフォルダ名
 9    bName = ThisWorkbook.Name  ' 本ワークブックの名前
10    Workbooks.Open pName & "\pt_source02.xls"  ' ソースデータを開く
11    Set ws = WorkSheets(1)
12    rngName = "[pt_source02.xls]" & ws.Name & "!" & ws.UsedRange.Address
13    Workbooks(bName).Activate  ' 本ワークブックをアクティブに
14    WorkSheets(1).Activate  ' 第1シートをアクティブに
15    
16    Set ptCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
17        SourceData:=rngName)
18    Set ptObj = ptCache.CreatePivotTable( _
19        TableDestination:="R1C1", TableName:="ピボット01")
20    
21    For i = 1 To 7
22        argAry(i) = False
23    Next
24    argAry(6) = True  ' 6番目の要素をtrueに。四半期単位の指定
25    With ptObj.PivotFields("日付")
26        .Orientation = xlRowField
27        .LabelRange.Value = "期間区分"
28        .DataRange.Cells(1).Group Periods:=argAry
29    End With
30    With ptObj.PivotFields("売上")
31        .Orientation = xlDataField
32        .Function = xlSum  ' 合計を算出
33    End With
34    With ptObj.PivotFields("仕入原価")
35        .Orientation = xlDataField
36        .Function = xlSum
37    End With
38End Sub

    

 今回のソースデータは「名前付き範囲」になっていません。

 なので、ピボットキャッシュを設定する際、ソースデータの領域をセル番地で指定しています。

 変数 wsにソースデータのワークシートオブジェクトがセットされている場合、
ws.UsedRange.Address によってデータが書かれている領域全体のセル番地を取得できます。

 $A$1:$D$1465 のような文字列が得られます。

 R1C1形式がほしいときは ws.UsedRange.Address(True, True, xlR1C1) のようにします。

目次に戻る


(4) 四半期単位でのグループ化のVBScript

 先の VBAマクロと同じ働きをする VBScript を掲げます。

 ピボットテーブルを含む Book1.xlsx を作成します。

 VBScript では配列の添え字を 1 から始めることができないようです。0 からになるので要注意。

 1Option Explicit
 2Dim fso, bookPath, srcPath
 3Dim exlApp, wbObj, ws, rngName
 4Dim ptCache, ptObj
 5Dim argAry(6), i
 6Include "constants_xl.vbs"
 7    
 8Set fso = CreateObject("Scripting.FileSystemObject")
 9bookPath = fso.GetAbsolutePathName("Book1.xlsx")
10If (fso.FileExists(bookPath) = True) Then fso.DeleteFile(bookPath)
11srcPath = fso.GetAbsolutePathName("pt_source02.xls")
12Set exlApp = CreateObject("Excel.Application")  ' Excelの起動
13exlApp.Visible = True  ' Excelを見える状態に
14exlApp.Workbooks.Open srcPath
15Set ws = exlApp.ActiveWorkbook.WorkSheets(1)
16rngName = "[pt_source02.xls]" & ws.Name & "!" & ws.UsedRange.Address
17Set wbObj = exlApp.Workbooks.Add()  ' Workbookの新規作成
18wbObj.WorkSheets(1).Activate
19    
20Set ptCache = wbObj.PivotCaches.Create(xlDatabase, rngName)
21Set ptObj = ptCache.CreatePivotTable( _
22    "R1C1", "ピボット01")
23    
24For i = 0 To 6
25    argAry(i) = False
26Next
27argAry(5) = True  ' 6番目の要素をtrueに。四半期単位の指定
28With ptObj.PivotFields("日付")
29    .Orientation = xlRowField
30    .LabelRange.Value = "期間区分"
31    .DataRange.Cells(1).Group ,,,argAry
32End With
33With ptObj.PivotFields("売上")
34    .Orientation = xlDataField
35    .Function = xlSum  ' 合計の算出
36End With
37With ptObj.PivotFields("仕入原価")
38    .Orientation = xlDataField
39    .Function = xlSum
40End With
41wbObj.SaveAs bookPath, xlOpenXMLWorkbook
42exlApp.quit

目次に戻る


(5) 四半期単位でのグループ化のJScript

 前述の VBScript と同じ処理を行う JScript を掲載します。

 JScript の配列は、Groupメソッドにパラメータとして引き渡しても、受け付けてくれないようです。

 仕方ないので ArrayList オブジェクトを利用します。

 このオブジェクトは、ToArray() によって VBA, VBScript の通常の配列に変換できます。このことを利用します。

 1var fso, bookPath, srcPath;
 2var exlApp, wb, ws, rngName;
 3var ptCache, ptObj, argAry, i;
 4eval(ReadFile("constants_xl.js"));
 5    
 6fso = WScript.CreateObject("Scripting.FileSystemObject");
 7bookPath = fso.GetAbsolutePathName("Book1.xlsx");
 8if (fso.FileExists(bookPath))  fso.DeleteFile(bookPath);
 9srcPath = fso.GetAbsolutePathName("pt_source02.xls");
10exlApp = WScript.CreateObject("Excel.Application");  // Excelの起動
11exlApp.Visible = true;  // Excelを見える状態に
12exlApp.Workbooks.Open(srcPath);
13ws = exlApp.ActiveWorkbook.WorkSheets(1);
14rngName = "[pt_source02.xls]" + ws.Name + "!" + ws.UsedRange.Address
15wb = exlApp.Workbooks.Add();  // Workbookの新規作成
16wb.WorkSheets(1).Activate();
17    
18ptCache = wb.PivotCaches().Create(xlDatabase, rngName);
19ptObj = ptCache.CreatePivotTable(
20    "R1C1", "ピボット01");
21    
22argAry = WScript.CreateObject("System.Collections.ArrayList");
23for(i=0; i<=6; i++) {
24    argAry.add(false);
25}
26argAry(5) = true;  // 6番目の要素をtrueに。四半期単位の指定
27with (ptObj.PivotFields("日付")) {
28    Orientation = xlRowField;
29    LabelRange.Value = "期間区分";
30    DataRange.Cells(1).Group(null, null, null, argAry.ToArray());
31}
32with (ptObj.PivotFields("売上")) {
33    Orientation = xlDataField;
34    Function = xlSum;  // 合計の算出
35}
36with (ptObj.PivotFields("仕入原価")) {
37    Orientation = xlDataField;
38    Function = xlSum;
39}
40wb.SaveAs(bookPath, xlOpenXMLWorkbook);
41exlApp.Quit();

目次に戻る


3. 四半期単位と月単位の両方を扱う

 四半期単位と月単位の両方を表示する方法を取り上げます。

(1) とりあえずの表作成

 GroupメソッドのPeriodsパラメータには配列を渡します。

 七つの要素のうち、5番目をTrueにすれば月単位、6番目をTrueにすれば四半期単位になります。

 では、その両方をTrueにすればどうなるかですが、下のような表になります。

期間区分 合計/売上 合計/仕入原価
第1四半期    
1月 18505 13822
2月 17821 13244
3月 18452 13858
第2四半期    
4月 17240 12865
5月 17979 13452
6月 16870 12533
第3四半期    
7月 19666 14570
8月 18973 14083
9月 18382 13568
第4四半期    
10月 18562 13705
11月 18071 13357
12月 17724 12994
総計 218245 162051

    

 四半期については項目名があるだけで、合計の値が表示されません。

 なんだか中途半端ですが、上の表を作成する VBAのコード(該当箇所のみ)を掲げます。

ReDim argAry(7)
For i = 1 To 7
    argAry(i) = False
Next
argAry(5) = True  ' 5番目の要素をtrueに。月単位の指定
argAry(6) = True  ' 6番目の要素をtrueに。四半期単位の指定
With ptObj.PivotFields("日付")
    .Orientation = xlRowField
    .LabelRange.Value = "期間区分"
    .DataRange.Cells(1).Group Periods:=argAry
End With

 今回は、Groupメソッドに引き渡すパラメータ argAry を動的配列にしました。配列の要素数を固定にせず可変にします。

 配列の宣言は Dim argAry() As Boolean として括弧内に数字を書きません。

 そして、配列を実際に使う直前の段階で ReDim argAry(7) と添え字の最終番号を指定して、配列のサイズを決めます。

 この後で、argAry の要素数を12個に拡張して、別の用途で利用する予定。

 なので、動的配列にしました。

目次に戻る


(2) 四半期単位と月単位の両方の合計値を表示

 前述の VBAコードを実行して、四半期単位と月単位の 両方を指定すると、新たに「四半期」という名前のピボットフィールドができます。

 この「四半期」というピボットフィールドの Subtotals というプロパティに値をセットすることにより、四半期単位の合計値も表示することができます。

 Subtotals には12個の要素からなる配列をセットします。

 どの要素も True, False のどちらかです。

 合計値を表示するには、第2要素だけを True にし、他は False にします。

 VBAコードを示すと次のとおり。

ReDim argAry(12)
For i = 1 To 12
    argAry(i) = False
Next
argAry(2) = True
With ptObj.PivotFields("四半期")
    .Subtotals = argAry
End With

    

 12個の要素の意味は次のとおり。

1:自動 2:合計 3:データの個数 4:平均
5:最大値 6:最小値 7:積 8:数値の個数
9:標本標準偏差 10:標準偏差 11:標本分散 12:分散

 「1:自動」にした場合、今回のケースでいうと、四半期単位の欄が月単位とおなじ「合計」になるということだとおもいます。

 argAry(1) = True としても、やはり四半期の欄に合計値が表示されます。

 「1:自動」にしておけば、多くのケースで適切に処理されるはずです。

 後述の「集計フィールド」との関連でいうと、むしろ「1:自動」の方がお勧めです。

    

 Subtotalsプロパティは、.Subtotals(2) = True のようにして、2番目の要素をピンポイントで指定することができます。もちろん、3番目とか4番目なども指定できます。

 ただ、その場合、他の要素に変更を加えることにはなりません。

 他の要素が True なのか False なのか不安が残ります。

 12個の要素からなる配列をセットする方が安全策といえます。

 デフォルトは、12個すべて False なので、新規にピボットテーブルを作成したときは そうなっているとおもいますが。

    

 四半期単位と月単位の両方の合計値を表示する VBAマクロ、VBScript、JScript がzip圧縮ファイルに同梱してあります。

 VBAの該当のコードは説明の中で掲載しました。VBScript は VBAと基本的に同じですが、JScript の方はちょっと違うので、該当箇所を下に掲げておきます。

argAry = WScript.CreateObject("System.Collections.ArrayList");
for(i=0; i<=6; i++) {
    argAry.add(false);
}
argAry(4) = true;  // 5番目の要素をtrueに。月単位の指定
argAry(5) = true;  // 6番目の要素をtrueに。四半期単位の指定
with (ptObj.PivotFields("日付")) {
    Orientation = xlRowField;
    LabelRange.Value = "期間区分";
    DataRange.Cells(1).Group(null, null, null, argAry.ToArray());
}
argAry.Clear
for(i=0; i<=11; i++) {
    argAry.add(false);
}
argAry(1) = true;  // 「合計」をonに
with (ptObj.PivotFields("四半期")) {
    Subtotals = argAry.ToArray()
}

目次に戻る


(3) グループ化したときのピボットアイテムの挙動

 細かい事柄ですが、グループ化したときのピボットアイテムの挙動について記します。

 まず、グループ化する前の段階をみます。

 Set ptf = ptObj.PivotFields("日付") として、「日付」のピボットフィールドを変数 ptf に代入したとします。

 このとき、ptf に属するピボットアイテムは 366個あります(2016年は閏年)。

 ptf.PivotItems.Count でその個数を確認できます。

 ピボットアイテムの名前は、「1/1/2016」とか「10/17/2016」のような文字列です。

 ptf.PivotItems(1).Name などとして名前を確認できます。

    

 次に、四半期単位でグループ化した後の状態をみます。

 ピボットアイテムの個数は 366個から 6個に減少します。

 それらの Name は下のとおり。

<2016/1/1, Qtr1, Qtr2,
Qtr3, Qtr4, >2017/1/1

 四半期の前後に「範囲外」がある形です。

 今回のソースデータからは範囲外に該当するデータは出てきませんが、アイテムの上では「範囲外」が用意されています。

 ところで、集計表に出てくる「第1四半期」といった名称はどこに格納されているのかというと、ピボットアイテムの LabelRange に記録されています。

 ptf.PivotItems(2).LabelRange.Value を見ると、「第1四半期」となっています。

 ここで注意していただきたいのは、1番目と6番目の「範囲外」には LabelRange を適用できない点です。

 ptf.PivotItems(1).LabelRange と書くと、エラーが発生します。

    

 最後に、四半期単位と月単位の両方を指定してグループ化したケースをみます。

 この場合、「日付」のピボットフィールドの他に、「四半期」のピボットフィールドが新たに設けられます。

 「月」のピボットフィールドが設けられる訳ではありません。大きな枠組みの方のフィールドが新規作成されるようです。

 Set ptf2 = ptObj.PivotFields("四半期") とすれば、変数 ptf2 に四半期のピボットフィールドがセットされます。

 ptf2 に属するピボットアイテムは、個数が6個です。

 その Name, LabelRange の様子は、四半期でのグループ化を行った場合と同じです。

 一方、もともとの「日付」のピボットフィールドの方をみると、

 そのピボットアイテムの個数は 366個から 14個に減少します。

 12ヶ月分と、その前後の「範囲外」です。

 Name と LabelRange の組は次のとおり。

番号 Name LabelRange
1 <2016/1/1  
2 Jan 1月
3 Feb 2月
4 Mar 3月
5 Apr 4月
6 May 5月
7 Jun 6月
8 Jul 7月
9 Aug 8月
10 Sep 9月
11 Oct 10月
12 Nov 11月
13 Dec 12月
14 >2017/1/1  

 こうした細かい情報を用いることはあまりないとおもいますが、一般的でないグループ化が必要になった場合などに役立つかもしれません。

目次に戻る


4. GroupメソッドのStartパラメータなど

 Groupメソッドには、Start, End, Byというパラメータがあります。

 日付のグループ化でもこれらを使うことができます。

 Byパラメータは区分幅を指定するもので、日付のグループ化の場合は「日」を単位とするグループ化のときだけ指定できます。

 By:=7 とすれば、七日間(1週間)ごとに区切ったグループ化になります。

 Start(グループ化する際の下限値), End(グループ化する際の上限値)は、日付のシリアル値で指定してもいいですが、"2016/7/1" のような文字列でもいいようです。

 応用性に欠けるイレギュラーな方法ですが、Start, By を指定する例として、半年単位のグループ化をやってみます。

 作成する表は下のとおり。

期間区分 合計/売上 合計/仕入原価
上半期 106867 79774
下半期 111378 82277
総計 218245 162051

    

 下半期の開始日の7月1日を Start に設定し、By には 184 を設定します。

 上半期が「範囲外」の扱いになってしまいますが、ピボットアイテムの Name を変更して、表の上での表示を取り繕います。

 VBAコード(関連部分)は下のとおり。

For i = 1 To 7
    argAry(i) = False
Next
argAry(4) = True  ' 4番目の要素をtrueに。「日」単位の指定
With ptObj.PivotFields("日付")
    .Orientation = xlRowField
    .LabelRange.Value = "期間区分"
    .DataRange.Cells(1).Group Start:="2016/7/1", By:=184, Periods:=argAry
    .PivotItems(1).Name = "上半期"
    .PivotItems(2).Name = "下半期"
End With

 ちなみに、ピボットアイテムの Name を変更しなければ、上半期が「<2016/7/1」、下半期は「2016/7/1-2016/12/31」と表示されます。

目次に戻る


5. 売上と仕入原価から利益を算出(集計フィールド)

 グループ化からは脱線しますが、

 「売上」 マイナス 「仕入原価」 イコール 「利益」
という算出式に即して「利益」も合わせて表示する方法を取り上げます。

 このように、他のフィールドの値から算出されるフィールドを集計フィールドというようです。

 作成する表は下のとおり。

期間区分 合計/売上 合計/仕入原価 合計/利益
第1四半期 54778 40924 13854
第2四半期 52089 38850 13239
第3四半期 57021 42221 14800
第4四半期 54357 40056 14301
総計 218245 162051 56194

    

 集計フィールドは、ピボットテーブルオブジェクトに CalculatedFields.Add を適用して作成します。

 具体的には下のとおり(VBAコード)。

Set calcFld = ptObj.CalculatedFields.Add(Name:="利益", _
    Formula:="=売上 - 仕入原価")
calcFld.Orientation = xlDataField

 CalculatedFields.Add は、ピボットフィールドオブジェクトを返します。

 新たな集計フィールドの名前は、Nameパラメータで指定したとおり「利益」になります。

 計算式は、Formulaパラメータで指定します。

 CalculatedFields.Add を適用しただけだと、それが集計表に盛り込まれません。

 Orientationプロパティに xlDataField をセットして、新たに作られた集計フィールドがデータフィールドであることを明示します。

 zip圧縮ファイルには、四半期単位でグループ化した表に「利益」を盛り込むマクロとスクリプトが入っています。

 JScript の場合、CalculatedFields の後に括弧をつけて CalculatedFields().Add としないとエラーになるので注意してください。

 該当箇所をJScriptのwithステートメントで書くと次のとおり。

with (ptObj.CalculatedFields().Add("利益", "=売上 - 仕入原価")) {
    Orientation = xlDataField;
}

    

 この集計フィールドについて注意しなければならないのは、Subtotalsプロパティとの関連です。

 四半期単位と月単位の両方を表に盛り込むとき、売上と仕入原価の合計値を、月単位だけでなく四半期単位でも表示させるために Subtotalsプロパティに値をセットしました。

 先のVBAコードの例では「2:合計」を用いましたが、これだと「利益」の四半期の欄の合計値が表示されません。

 「売上」と「仕入原価」の合計値は表示されるのですが、「利益」はダメです。

 ちゃんと表示させるには、「2:合計」でなく「1:自動」を使います。

 .Subtotals(1) = True とすれば、「利益」についても表示されるようになります。

    

 今回は、この辺でおわりにします。

 「商品」の列を使わないままでした。次回、利用したいとおもいます。

〜 以上 〜