カテゴリー名： ［ExcelVBAとピボットテーブル］
当シリーズでは、Excelのピボットテーブルをプログラミングのノウハウで生成するよう試みます。
今回は、ページフィールドの扱い方、それから、複数のワークシートを素材として集計する方法を取り上げます。
ソースデータとして、第５回(日付のグループ化)で用いた pt_source02.xls を利用します。
当Webページで紹介するマクロファイルやVBScriptのファイル一式は、 vovPIVOT06.zip という圧縮ファイルに同梱しておきます。
今回の素材データは pt_source02.xls です。
日付、商品、売上、仕入原価の四つの列から構成されています。
日付には、「2016/3/21」のような2016年１月１日〜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 には三つのワークシートがあります。
第1ワークシートの名前が「東京支店」、第2が「名古屋支店」、第3が「大阪支店」です。
ページフィールドの話に入る前に、プロシージャのパラメータを省略する方法について少し記します。
多少実践っぽくするため集計フィールドを設けることにします。
第５回「日付のグループ化」で、「売上」と「仕入原価」に加えて「利益」という集計フィールドを設ける方法を取り上げました。
「売上」 マイナス 「仕入原価」 イコール 「利益」です。
それらを四半期単位で集計した訳ですが、更に「利益率」という集計フィールドを追加します。
「利益」 ÷ 「売上」 × 100 イコール 「利益率(％)」とします。
下のような表を作ります。
| 期間区分 | 売上(a) | 仕入原価(b) | 利益(c:a-b) | 利益率(d:c/a) |
| 第1四半期 | 54,778 | 40,924 | 13,854 | 25.3% |
| 第2四半期 | 52,089 | 38,850 | 13,239 | 25.4% |
| 第3四半期 | 57,021 | 42,221 | 14,800 | 26.0% |
| 第4四半期 | 54,357 | 40,056 | 14,301 | 26.3% |
| 総計 | 218,245 | 162,051 | 56,194 | 25.7% |
データフィールドを四つ設けるので、プロシージャを設定して、それを呼び出す形にします。
データフィールド設定用のプロシージャをVBAコードで示すと次のとおり。
Sub SetDataFld(ByRef ptFld As PivotField, Optional ByVal funcVal, _
Optional ByVal capName, Optional ByVal numFmt)
With ptFld
.Orientation = xlDataField
If Not IsMissing(funcVal) Then .Function = funcVal
If Not IsMissing(capName) Then .Caption = capName
If Not IsMissing(numFmt) Then .NumberFormat = numFmt
End With
End Sub
上のプロシージャを呼び出すための記述は次のとおり。
SetDataFld ptObj.PivotFields("売上"), xlSum, "売上(a)", "#,##0"
SetDataFld ptObj.PivotFields("仕入原価"), xlSum, "仕入原価(b)", "#,##0"
SetDataFld ptObj.CalculatedFields.Add("利益", "=売上 - 仕入原価"),, _
"利益(c:a-b)", "#,##0"
SetDataFld ptObj.CalculatedFields.Add("利益率", "=利益 / 売上"),, _
"利益率(d:c/a)", "0.0%"
集計フィールドでは Functionプロパティを設定しません。
なので、プロシージャを呼び出すときに、そのパラメータを省略できるようにしました。
プロシージャを定義する際、パラメータに Optional という指示語を付けます(データ型は指定しない)。
……, Optional ByVal funcVal, ……
すると、そのパラメータは省略可能となります。
そして、プロシージャの中身の方で、あるパラメータが省略されているかどうかを確認するには IsMissing() を使います(IsMissingがTrueを返せば省略されている)。
If Not IsMissing(funcVal) Then .Function = funcVal
上は、funcValというパラメータが省略されていないときに、Functionパラメータに funcVal の値を代入するという意味です。
IsMissing() に渡す変数は、バリアント型でないと正しく判定されないようです。
なので、プロシージャ定義のときに Optional を付けたパラメータは、データ型を宣言しないでおきます。
なお、あるパラメータに Optional を付けると、
それ以降のパラメータすべてに Optional を付けなければいけないようです。
VBScriptにもJScriptにも、IsMissingに該当するものがありません。
プロシージャを設定する際に、パラメータの省略をどう扱うかですが、
容易な方法としては Null を使う方法があります。
省略したいパラメータをNullにするやり方です。
SetDataFld a, b, , d のような呼び出し方を認めず
SetDataFld a, b, Null, d と書くようにするタイプです。
プロシージャの中身の方では、パラメータが Null かどうかをチェックすればいいので、If Not IsNull(funcVal) Then …… のように記述すればＯＫです。
実際、JScriptでは、これまでピボットテーブルを扱ういろいろなメソッドにおいて null を使ってきました。
グループ化を行う Groupメソッドの場合、
ptFld.Group(null, null, null, argAry); のように書いてきました。
なので(？)、今回の自作関数でも null方式を採るようにします。
問題は VBScript の方です。
Null方式でもいいのですが、少しでもVBAマクロに近づけるため、IsMissingというプロシージャを設けてみます。
具体的には次のようなスクリプトにします。
Function IsMissing(p)
IsMissing = (VarType(p) = vbError)
End Function
Sub SetDataFld(ByRef ptFld, ByVal funcVal, ByVal capName, ByVal numFmt)
With ptFld
.Orientation = xlDataField
If Not IsMissing(funcVal) Then .Function = funcVal
If Not IsMissing(capName) Then .Caption = capName
If Not IsNull(numFmt) Then .NumberFormat = numFmt
End With
End Sub
上のようにプロシージャを設定すれば、SetDataFld ptFld, xlSum, , "#,##0" といった記述が可能になります。
ところが、残念なことに、呼び出し時に最後のパラメータを省略できません。
SetDataFld ptFld, xlSum, "売上(a)" のように書くことはできません。
「エラー: 引数の数が一致していません。または不正なプロパティを指定しています。」というメッセージが出ます。
それなら最後にカンマを付ければいいかというと、それでもエラーになります。
最後のパラメータを省略するときは SetDataFld ptFld, xlSum, , Null のように Null を置きます。
なんとも中途半端で使いにくい感じです。実践的には Null方式にしてしまった方が統一性があっていいでしょうが、一つの話のネタとして書いてみました。
なにか良い方法はないでしょうか。
参考まで JScript の該当部分を掲げておきます。null方式です。
function SetDataFld(ptFld, funcVal, capName, numFmt) {
with (ptFld) {
Orientation = xlDataField
if (funcVal !== null) Function = funcVal;
if (capName !== null) Caption = capName;
if (numFmt !== null) NumberFormat = numFmt;
}
}
上の関数を呼び出す方の記述は下のとおり。
SetDataFld(ptObj.PivotFields("売上"), xlSum, "売上(a)", "#,##0");
SetDataFld(ptObj.PivotFields("仕入原価"), xlSum, "仕入原価(b)", "#,##0");
SetDataFld(ptObj.CalculatedFields().Add("利益", "=売上 - 仕入原価"), null,
"利益(c:a-b)", "#,##0");
SetDataFld(ptObj.CalculatedFields().Add("利益率", "=利益 / 売上"), null,
"利益率(d:c/a)", "0.0%");
ソースデータの「商品」の内訳は四種類です。調味料、飲料、乳製品、魚介類。
もし「商品」の列をページフィールドに設定したとすると、
最終的な集計表が四つできます(総計を入れると五つ)。
その四つ(五つ)は、別々のワークシートに書き出されます。
ワークシートが分かれてしまうのがページフィールドです。
ピボットフィールドの Orientationプロパティに xlRowFiled を代入すれば行フィールド、xlColumnField を代入すれば列フィールドになります。
そして、xlPageField を代入すればページフィールドです。
今回、「商品」をページフィールドにするので、適当な箇所に次ぎの３行を挿入します。VBAコードでの記述を記します。
With ptObj.PivotFields("商品")
.Orientation = xlPageField
End With
ただ、上記を挿入しただけだと、「全体」に当たるものしか書き出されません。
調味料、飲料などの内訳の集計が出てきません。
そこで、下の１行をピボットフィールド設定の最後の方に挿入します。
ptObj.ShowPages
ばらばらに掲げても分かりにくいとおもうので、ちょっと長くなりますが、ピボットフィールド設定箇所を掲載します。VBAコードです。
With ptObj.PivotFields("商品")
.Orientation = xlPageField
End With
For i = 1 To 7
argAry(i) = False
Next
argAry(6) = True ' 6番目の要素をtrueに。四半期単位の指定
With ptObj.PivotFields("日付")
.Orientation = xlRowField
.LabelRange.Value = .Name
.DataRange.Cells(1).Group Periods:=argAry
End With
SetDataFld ptObj.PivotFields("売上"), xlSum, "売上(a)", "#,##0"
SetDataFld ptObj.PivotFields("仕入原価"), xlSum, "仕入原価(b)", "#,##0"
SetDataFld ptObj.CalculatedFields.Add("利益", "=売上 - 仕入原価"),, _
"利益(c:a-b)", "#,##0"
SetDataFld ptObj.CalculatedFields.Add("利益率", "=利益 / 売上"),, _
"利益率(d:c/a)", "0.0%"
ptObj.ShowPages
上記のコードを実行してできるワークシートは下のとおりです。
このほか、空の Sheet2, Sheet3 というワークシートもあります。
第５ワークシートは「全体」に該当するもので、ピボットアイテムには含まれませんが、第一〜第四は、ピボットアイテムとして操作できます。
順番を入れ替えたり、非表示にしたりといった処理が可能です。
まず、「商品」の順番を 1) 調味料、2) 飲料、3) 乳製品、4) 魚介類に入れ替えてみます。
バリアント型の配列を使うと、たとえば下のように書けます(VBAコード)。
iNames = Array("調味料", "飲料", "乳製品", "魚介類")
With ptObj.PivotFields("商品")
.Orientation = xlPageField
For i = LBound(iNames) To UBound(iNames)
.PivotItems(iNames(i)).Position = i
Next
End With
iNamesは、予め iNames As Variant と宣言しておきます。
配列の添え字を 0 からでなく 1 から始めるようにしてあるので上のように書きましたが、0 からの場合は .PivotItems(iNames(i)).Position = i + 1 とします。
非表示については後の方でも述べますが、
「飲料」を非表示にするなら .PivotItems("飲料).Visible = False と書きます。
そうすると、ワークシートの数が一つ減ります。
調味料、乳製品、魚介類の三つのワークシートと、「全体」に当たるシートが作られます。
この場合、「全体」に当たる第４ワークシートの名前が「ピボットテーブルのシート」になります。「Sheet1」ではなくなります。
また、商品名が「(すべて)」から「(複数のアイテム)」に変化します。
これまでマクロやスクリプトの全体像を掲載してきませんでしたが、長くなるのでここには記しません。
別のWebページに載せておくので必要ならのぞいてみて下さい。
今度は「日付」の四半期単位をページフィールドにします。「商品」の方を行フィールドにする。
第1四半期を第一ワークシート、第2四半期を第２ワークシートなどとする訳です。
このとき、次の二つの問題が発生します。
対処方法は下のとおりです。
「範囲外」は、ピボットアイテムの１番と６番です。
上の二つの対処方法を実現する VBAコードは下のとおり。
With ptObj.PivotFields("日付")
.Orientation = xlColumnField ' 列フィールドにする
.DataRange.Cells(1).Group Periods:=argAry
.PivotItems(1).Visible = False
.PivotItems(6).Visible = False
.Orientation = xlPageField ' ページフィールドにする
End With
しかし、残念ながら上のコードはエラーになります。
『PivotItem クラスの Visible プロパティを設定できません。』というエラーです。
これに関連してあれこれ調べてみましたが、原因は分かりませんでした。
ですが、エラーを起こさないための対処療法として、ピボットアイテムの Name を変更するというのがあります。
この対処療法が汎用的に使えるかどうかは不明ですが、とりあえず今回はこの方法を用います。
VBAコードとしては次のようにします。
With ptObj.PivotFields("日付")
.Orientation = xlColumnField
.DataRange.Cells(1).Group Periods:=argAry
.PivotItems(1).Name = "<2016-1-1" ' 元は <2016/1/1
.PivotItems(6).Name = ">2017-1-1" ' 元は >2017/1/1
.PivotItems(1).Visible = False
.PivotItems(6).Visible = False
.Orientation = xlPageField
End With
上記のコードだと、エラーが発生せず意図通りの結果になります。
上のコードは、「範囲外」がアイテムの１番と６番であることを前提にしたものです。
なので、四半期単位でなく月単位でグループ化したときはコードを書き換える必要があります。
そこで、ピボットアイテムの RecordCountプロパティを利用して、月単位の場合にも使えるものに改めます。
RecordCount は、該当のアイテムが集計の際に参照するソースデータ中のレコードの件数を示します。
今回のケースでは「範囲外」の RecordCount は 0 です。
「範囲外」か否かにかかわらず、RecordCount が 0 なら意味のある集計が出てくるはずないので、RecordCount が 0 の場合に非表示にするのは、それなりに汎用性があるといえます。
VBAコードでは次のようにします。
With ptObj.PivotFields("日付")
.Orientation = xlColumnField
.DataRange.Cells(1).Group Periods:=argAry
For i = 1 To .PivotItems.Count
If .PivotItems(i).RecordCount = 0 Then
.PivotItems(i).Name = "!Item" & .PivotItems(i).Position
.PivotItems(i).Visible = False
End If
Next
.Orientation = xlPageField
End With
RecordCount を利用するマクロとスクリプトを別Webに掲載しておきます。
ソースデータの pt_source02.xls には三つのワークシートがあります。
東京支店、名古屋支店、大阪支店の三つです。
ここでは、三つのワークシートを一括して取り扱う方法を見てみます。
ピボットキャッシュを生成するときは、少なくとも SourceType, SourceData の二つを指定します。
これまで、SourceTypeを xlDatabase に指定してきました。
これは、Excelのワークブックをソースデータとして扱うことを意味しますが、扱うのはワークシート一枚だけです。
複数のワークシートを扱うときは xlConsolidation を指定します。
SourceDataの方は、ソースデータとして、どのワークブックの，どのワークシートの，どのセル領域を使うかの指定です。
"[pt_source02.xls]Sheet1!R1C1:R367C4" のような文字列で指定できます。
しかし、複数のワークシートをソースデータにする場合は配列で指定します。
今回のサンプルに即して書くと次のとおり。
Array( _
Array("[pt_source02.xls]東京支店!R1C1:R1465C4", "東京支店"), _
Array("[pt_source02.xls]名古屋支店!R1C1:R1465C4", "名古屋支店"), _
Array("[pt_source02.xls]大阪支店!R1C1:R1465C4", "大阪支店"))
親配列の中に子配列が三つ入っている形です。
子配列は、第一要素がソースのエリア、第二要素がエリアに割り当てる名前です。
後で見るように、三つのエリアが一つのページフィールドに所属し、
三つのエリア(つまりワークシート)がピボットアイテムになります。
そして、エリアに割り当てた名前は、ピボットアイテムの名前になります。
SourceDataを指定するのに、上記のような固定的な配列として記述したのでは応用性がないので、pt_source02.xls から情報を読み取って、バリアント型の変数 srcAry に配列をセットすることにします。
ピボットキャッシュを設定するまでを掲げます。VBAコードです。
Option Base 1 ' 配列の添え字を 1 から始める
Sub Macro1()
Dim pName As String, bName As String
Dim ws As WorkSheet, rng As Range
Dim ptCache As PivotCache, ptObj As PivotTable
Dim srcAry As Variant, i As Integer
pName = ThisWorkbook.Path ' 本ワークブックのフォルダ名
bName = ThisWorkbook.Name ' 本ワークブックの名前
Workbooks.Open pName & "\pt_source02.xls" ' ソースデータを開く
ReDim srcAry(WorkSheets.Count) ' 配列として宣言
For i = 1 To WorkSheets.Count ' シート枚数だけ処理
Set ws = WorkSheets(i)
Set rng = ws.UsedRange ' 書き込みのある領域全体
srcAry(i) = Array("[" & ActiveWorkbook.Name & "]" & ws.Name & _
"!" & rng.Address(True, True, xlR1C1), ws.Name)
Next
Workbooks(bName).Activate ' 本ワークブックをアクティブに
WorkSheets(1).Activate ' 第１シートをアクティブに
Set ptCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlConsolidation, _
SourceData:=srcAry)
Set ptObj = ptCache.CreatePivotTable( _
TableDestination:="R1C1", TableName:="BasePivot")
（後略）
End Sub
ピボットキャッシュを設定した後のピボットテーブルの生成方法は、これまでと同じなので、そこまでを示しました。
なお、理由は分かりませんが、エリアのセル番地は、R1C1形式でないとエラーになります。少なくとも私のところではそうです。
これまでは $A$1 の形式で大丈夫でしたし、xlConsolidation 指定時でも VBScript, JScript の方では $a$1形式で大丈夫なのですが……
これまで、行フィールド，列フィールド，データフィールド，そしてページフィールドを扱ってきました。
それらフィールドは、ユーザーが設定したから生成された訳ですが、xlConsolidation を指定したときは自動的に生成されます。
変数ptObjにピボットテーブルオブジェクトがセットされている場合、
Set ptfRow = ptObj.RowFields(1)
Set ptfCol = ptObj.ColumnFields(1)
Set ptfData = ptObj.DataFields(1)
Set ptfPage = ptObj.PageFields(1)
上のようにして各種フィールドのオブジェクトを得ることができます。
今回のサンプルでいうと、各フィールドとも一つずつ生成され、その内容は次のとおりです。
他に「データの個数 / 値」というNameのデータフィールドもできます。
データフィールドにピボットアイテムはありません。
ソースデータ中で最も左の列が「日付」ですが、それが行フィールドになり、それ以外の列は列フィールドになります。
一方、ピボットテーブルオブジェクトに所属する PivotFields は、下の四つが用意されます。index(番号)とNameを記します。
それぞれ前述の各種フィールドに対応しています。
「3番: 値」はデータフィールドです。
こうしたフィールド群を目的に合わせて設定し直します。
xlConsolidation を指定したときは、既定のフィールド群に修正を施すやり方なので、自由のきかない面があります。
ここでは、基本的な例を取り上げます。
次の集計表を作成します。
| 合計 | 売上，仕入原価 | |||||||
| 売上 | 売上 集計 | 仕入原価 | 仕入原価 集計 | |||||
| 期間区分 | 東京支店 | 名古屋支店 | 大阪支店 | 東京支店 | 名古屋支店 | 大阪支店 | ||
| 第1四半期 | 54,778 | 54,335 | 55,446 | 164,559 | 40,924 | 40,412 | 41,429 | 122,765 |
| 第2四半期 | 52,089 | 54,849 | 56,718 | 163,656 | 38,850 | 40,661 | 42,138 | 121,649 |
| 第3四半期 | 57,021 | 53,608 | 55,352 | 165,981 | 42,221 | 39,542 | 41,428 | 123,191 |
| 第4四半期 | 54,357 | 52,894 | 56,709 | 163,960 | 40,056 | 39,220 | 41,903 | 121,179 |
| 総計 | 218,245 | 215,686 | 224,225 | 658,156 | 162,051 | 159,835 | 166,898 | 488,784 |
フィールド設定の要点は次のとおり。
既定の列フィールドとデータフィールドは密接に連動していて、
データフィールドのFunctionを xlCount から xlSum に変更すると、
既定の列フィールドの「売上」と「仕入原価」が合計値になります。
「売上」を合計値、「仕入原価」を平均値にするといったことは、おそらくできない(？)とおもいます。
「商品」は、データフィールドと関連づけても意味ないので非表示にします。
VBAコードのフィールド設定の箇所を掲げます。
For i = 1 To 7
argAry(i) = False
Next
argAry(6) = True ' 6番目の要素をtrueに。四半期単位の指定
With ptObj.RowFields(1)
.DataRange.Cells(1).Group Periods:=argAry
.LabelRange.Value = "期間区分"
End With
With ptObj.PageFields(1)
.Orientation = xlColumnField ' ページフィールドを列フィールドに変更
i = 0
For Each vObj In Array("東京支店", "名古屋支店", "大阪支店")
i = i + 1
.PivotItems(vObj).Position = i
Next
End With
With ptObj.ColumnFields(1)
i = 0
For Each vObj In Array("売上", "仕入原価", "商品")
i = i + 1
.PivotItems(vObj).Position = i
Next
.PivotItems("商品").Visible = False
.LabelRange.Value = "売上，仕入原価"
End With
With ptObj.DataFields(1)
.Function = xlSum
.NumberFormat = "#,##0"
.Caption = "合計"
End With
ptObj.RowGrand = False ' 最右列の「早計」を非表示
最後の１行は、集計表において一番右端に表示される「早計」を非表示にするためのものです。
これがないと、「売上」と「仕入原価」を合算した値が「早計」として表示されます。
でも、そんな値は意味ないので非表示にします。
xlConsolidationを指定するマクロとスクリプトを別Webに掲載しておきます。
先のVBAコードでは「商品」を分類の手がかりとして利用することができません(私が知らないだけで何か方法があるのかもしれませんが)。
商品別×支店別の集計表を作りたい場合は、ソースデータのうち最も左の列にある「日付」を対象外にすればＯＫです。
そうすると、「商品」が素材中で一番左に位置する形になり、xlConsolidation を指定したときに、「商品」が既定の行フィールドになります。
ソースデータの２列目以降をエリアに指定したい場合は、RangeのOffset, Resize を使います。該当箇所だけ示すと次のとおり。
For i = 1 To WorkSheets.Count ' シート枚数だけ処理
Set ws = WorkSheets(i)
Set rng = ws.UsedRange ' 書き込みのある領域全体
Set rng = rng.Offset(0,1).Resize(,rng.Columns.Count-1) ' 2列目以降
srcAry(i) = Array("[" & ActiveWorkbook.Name & "]" & ws.Name & _
"!" & rng.Address(True, True, xlR1C1), ws.Name)
Next
上のようにしたうえで、既定の行フィールドや列フィールドを適切に修正すれば、商品別×支店別の集計表を作ることができます。
zip圧縮ファイルにその処理を行うマクロとスクリプトを入れてあります。
xlConsolidationを指定して複数のワークシートを一括処理する方法には、思うように設定できない不自由な面があります。
その制約から逃れる工夫にはいくつかあるとおもいますが、ソースデータがそれほど大規模でないなら、ソースデータを一つのワークシートにまとめてからピボットテーブルの処理を施すというのが最も簡単だとおもいます。
その場合は、xlConsolidation でなく xlDatabase を指定すればいいということになります。
そのほか、ソースデータを外部データベースとみなして SQL命令を併用するというのも一つの方法でしょうか。
具体例を示さず、とってつけたようなコメントだけで申し訳ないのですが……
〜 以上 〜