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

カテゴリー名：　［ExcelVBAとピボットテーブル］

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

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

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

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

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

《このページの目次》

• 1.　阻害データの概要
• 2.　日付のグループ化の基本形
  • (1)　Groupメソッドのパラメータ
  • (2)　四半期単位でのグループ化
  • (3)　四半期単位でのグループ化のVBAマクロ
  • (4)　四半期単位でのグループ化のVBScript
  • (5)　四半期単位でのグループ化のJScript
• 3.　四半期単位と月単位の両方を扱う
  • (1)　とりあえずの表作成
  • (2)　四半期単位と月単位の両方の合計値を表示
  • (3)　グループ化したときのピボットアイテムの挙動
• 4.　GroupメソッドのStartパラメータなど
• 5.　売上と仕入原価から利益を算出(集計フィールド)

1.　阻害データの概要

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

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

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

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

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

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

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

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

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

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

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

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

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

目次に戻る

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

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

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

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

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

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

• Start：　グループ化する際の下限値
  　省略すると、該当Rangeの最小値が採用される。
• End：　グループ化する際の上限値
  　省略すると、該当Rangeの最大値が採用される。
• By：　各グループのサイズ(区分幅)
• Periods：　日付のグループ化に使用

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

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

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

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

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

目次に戻る

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

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

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

　「日付」のピボットフィールドに四半期単位のグループ化を施す箇所を、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 から始めるようにしました。

 Option Base 1  ' 配列の添え字を 1 から始める
 Sub Macro1()
     Dim pName As String, bName As String
     Dim ws As WorkSheet, rngName As String
     Dim ptCache As PivotCache, ptObj As PivotTable
     Dim argAry(7) As Boolean, i As Integer
 　　　　
     pName = ThisWorkbook.Path  ' 本ワークブックのフォルダ名
     bName = ThisWorkbook.Name  ' 本ワークブックの名前
    Workbooks.Open pName & "\pt_source02.xls"  ' ソースデータを開く
    Set ws = WorkSheets(1)
    rngName = "[pt_source02.xls]" & ws.Name & "!" & ws.UsedRange.Address
    Workbooks(bName).Activate  ' 本ワークブックをアクティブに
    WorkSheets(1).Activate  ' 第１シートをアクティブに
　　　　
    Set ptCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
        SourceData:=rngName)
    Set ptObj = ptCache.CreatePivotTable( _
        TableDestination:="R1C1", TableName:="ピボット01")
　　　　
    For i = 1 To 7
        argAry(i) = False
    Next
    argAry(6) = True  ' 6番目の要素をtrueに。四半期単位の指定
    With ptObj.PivotFields("日付")
        .Orientation = xlRowField
        .LabelRange.Value = "期間区分"
        .DataRange.Cells(1).Group Periods:=argAry
    End With
    With ptObj.PivotFields("売上")
        .Orientation = xlDataField
        .Function = xlSum  ' 合計を算出
    End With
    With ptObj.PivotFields("仕入原価")
        .Orientation = xlDataField
        .Function = xlSum
    End With
End 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 からになるので要注意。

 Option Explicit
 Dim fso, bookPath, srcPath
 Dim exlApp, wbObj, ws, rngName
 Dim ptCache, ptObj
 Dim argAry(6), i
 Include "constants_xl.vbs"
 　　　　
 Set fso = CreateObject("Scripting.FileSystemObject")
 bookPath = fso.GetAbsolutePathName("Book1.xlsx")
If (fso.FileExists(bookPath) = True) Then fso.DeleteFile(bookPath)
srcPath = fso.GetAbsolutePathName("pt_source02.xls")
Set exlApp = CreateObject("Excel.Application")  ' Excelの起動
exlApp.Visible = True  ' Excelを見える状態に
exlApp.Workbooks.Open srcPath
Set ws = exlApp.ActiveWorkbook.WorkSheets(1)
rngName = "[pt_source02.xls]" & ws.Name & "!" & ws.UsedRange.Address
Set wbObj = exlApp.Workbooks.Add()  ' Workbookの新規作成
wbObj.WorkSheets(1).Activate
　　　　
Set ptCache = wbObj.PivotCaches.Create(xlDatabase, rngName)
Set ptObj = ptCache.CreatePivotTable( _
    "R1C1", "ピボット01")
　　　　
For i = 0 To 6
    argAry(i) = False
Next
argAry(5) = True  ' 6番目の要素をtrueに。四半期単位の指定
With ptObj.PivotFields("日付")
    .Orientation = xlRowField
    .LabelRange.Value = "期間区分"
    .DataRange.Cells(1).Group ,,,argAry
End With
With ptObj.PivotFields("売上")
    .Orientation = xlDataField
    .Function = xlSum  ' 合計の算出
End With
With ptObj.PivotFields("仕入原価")
    .Orientation = xlDataField
    .Function = xlSum
End With
wbObj.SaveAs bookPath, xlOpenXMLWorkbook
exlApp.quit

目次に戻る

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

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

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

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

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

 var fso, bookPath, srcPath;
 var exlApp, wb, ws, rngName;
 var ptCache, ptObj, argAry, i;
 eval(ReadFile("constants_xl.js"));
 　　　　
 fso = WScript.CreateObject("Scripting.FileSystemObject");
 bookPath = fso.GetAbsolutePathName("Book1.xlsx");
 if (fso.FileExists(bookPath))  fso.DeleteFile(bookPath);
 srcPath = fso.GetAbsolutePathName("pt_source02.xls");
exlApp = WScript.CreateObject("Excel.Application");  // Excelの起動
exlApp.Visible = true;  // Excelを見える状態に
exlApp.Workbooks.Open(srcPath);
ws = exlApp.ActiveWorkbook.WorkSheets(1);
rngName = "[pt_source02.xls]" + ws.Name + "!" + ws.UsedRange.Address
wb = exlApp.Workbooks.Add();  // Workbookの新規作成
wb.WorkSheets(1).Activate();
　　　　
ptCache = wb.PivotCaches().Create(xlDatabase, rngName);
ptObj = ptCache.CreatePivotTable(
    "R1C1", "ピボット01");
　　　　
argAry = WScript.CreateObject("System.Collections.ArrayList");
for(i=0; i<=6; i++) {
    argAry.add(false);
}
argAry(5) = true;  // 6番目の要素をtrueに。四半期単位の指定
with (ptObj.PivotFields("日付")) {
    Orientation = xlRowField;
    LabelRange.Value = "期間区分";
    DataRange.Cells(1).Group(null, null, null, argAry.ToArray());
}
with (ptObj.PivotFields("売上")) {
    Orientation = xlDataField;
    Function = xlSum;  // 合計の算出
}
with (ptObj.PivotFields("仕入原価")) {
    Orientation = xlDataField;
    Function = xlSum;
}
wb.SaveAs(bookPath, xlOpenXMLWorkbook);
exlApp.Quit();

目次に戻る

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

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

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

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

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

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

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

　なんだか中途半端ですが、上の表を作成する 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 のどちらかです。

　合計値を表示するには、第２要素だけを 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：自動」にした場合、今回のケースでいうと、四半期単位の欄が月単位とおなじ「合計」になるということだとおもいます。

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

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

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

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

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

　他の要素が 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四半期」となっています。

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

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

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

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

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

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

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

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

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

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

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

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

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

目次に戻る

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

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

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

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

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

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

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

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

　下半期の開始日の７月１日を 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.　売上と仕入原価から利益を算出(集計フィールド)

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

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

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

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

　集計フィールドは、ピボットテーブルオブジェクトに 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コードの例では「２：合計」を用いましたが、これだと「利益」の四半期の欄の合計値が表示されません。

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

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

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

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

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

〜　以上　〜