ExcelVBAとピボットテーブル:第6回 ページフィールドと複数ワークシートの取扱

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

2017/08/20

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

 今回は、ページフィールドの扱い方、それから、複数のワークシートを素材として集計する方法を取り上げます。

 ソースデータとして、第5回(日付のグループ化)で用いた pt_source02.xls を利用します。

 当Webページで紹介するマクロファイルやVBScriptのファイル一式は、 vovPIVOT06.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 には三つのワークシートがあります。

 第1ワークシートの名前が「東京支店」、第2が「名古屋支店」、第3が「大阪支店」です。

目次に戻る


2. プロシージャのパラメータ省略

 ページフィールドの話に入る前に、プロシージャのパラメータを省略する方法について少し記します。

ネットで買えば断然お得!お買い物なら楽天市場

(1) VBAでのプロシージャ設定とパラメータの省略

 多少実践っぽくするため集計フィールドを設けることにします。

 第5回「日付のグループ化」で、「売上」と「仕入原価」に加えて「利益」という集計フィールドを設ける方法を取り上げました。

 「売上」 マイナス 「仕入原価」 イコール 「利益」です。

 それらを四半期単位で集計した訳ですが、更に「利益率」という集計フィールドを追加します。

 「利益」 ÷ 「売上」 × 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 を付けなければいけないようです。

目次に戻る


(2) VBScriptとJScriptでのプロシージャ設定

 VBScriptにもJScriptにも、IsMissingに該当するものがありません。

 プロシージャを設定する際に、パラメータの省略をどう扱うかですが、
容易な方法としては Null を使う方法があります。
省略したいパラメータをNullにするやり方です。

 SetDataFld a, b, , d のような呼び出し方を認めず
SetDataFld a, b, Null, d と書くようにするタイプです。

 プロシージャの中身の方では、パラメータが Null かどうかをチェックすればいいので、If Not IsNull(funcVal) Then …… のように記述すればOKです。

 実際、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%");

目次に戻る


3. ページフィールドの設定

 ソースデータの「商品」の内訳は四種類です。調味料、飲料、乳製品、魚介類。

 もし「商品」の列をページフィールドに設定したとすると、
最終的な集計表が四つできます(総計を入れると五つ)。

 その四つ(五つ)は、別々のワークシートに書き出されます。

 ワークシートが分かれてしまうのがページフィールドです。

☆ モノを売らないフリーマーケット【ココナラ】

(1) ページフィールド設定の基本形

 ピボットフィールドの Orientationプロパティに xlRowFiled を代入すれば行フィールド、xlColumnField を代入すれば列フィールドになります。

 そして、xlPageField を代入すればページフィールドです。

 今回、「商品」をページフィールドにするので、適当な箇所に次ぎの3行を挿入します。VBAコードでの記述を記します。

With ptObj.PivotFields("商品")
    .Orientation = xlPageField
End With

 ただ、上記を挿入しただけだと、「全体」に当たるものしか書き出されません。

 調味料、飲料などの内訳の集計が出てきません。

 そこで、下の1行をピボットフィールド設定の最後の方に挿入します。

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 というワークシートもあります。

 第5ワークシートは「全体」に該当するもので、ピボットアイテムには含まれませんが、第一〜第四は、ピボットアイテムとして操作できます。

 順番を入れ替えたり、非表示にしたりといった処理が可能です。

目次に戻る


(2) ページフィールドのピボットアイテム操作

 まず、「商品」の順番を 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 と書きます。

 そうすると、ワークシートの数が一つ減ります。

 調味料、乳製品、魚介類の三つのワークシートと、「全体」に当たるシートが作られます。

 この場合、「全体」に当たる第4ワークシートの名前が「ピボットテーブルのシート」になります。「Sheet1」ではなくなります。

 また、商品名が「(すべて)」から「(複数のアイテム)」に変化します。

    

  これまでマクロやスクリプトの全体像を掲載してきませんでしたが、長くなるのでここには記しません。

 別のWebページに載せておくので必要ならのぞいてみて下さい。

ページフィールドの設定・基本形

目次に戻る


(3) グループ化の処理とページフィールド

 今度は「日付」の四半期単位をページフィールドにします。「商品」の方を行フィールドにする。

 第1四半期を第一ワークシート、第2四半期を第2ワークシートなどとする訳です。

 このとき、次の二つの問題が発生します。

 対処方法は下のとおりです。

 「範囲外」は、ピボットアイテムの1番と6番です。

 上の二つの対処方法を実現する 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

 上記のコードだと、エラーが発生せず意図通りの結果になります。

    

 上のコードは、「範囲外」がアイテムの1番と6番であることを前提にしたものです。

 なので、四半期単位でなく月単位でグループ化したときはコードを書き換える必要があります。

 そこで、ピボットアイテムの 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に掲載しておきます。

グループ化の処理とページフィールド

目次に戻る


4. 複数のワークシートを素材にする

 ソースデータの pt_source02.xls には三つのワークシートがあります。

 東京支店、名古屋支店、大阪支店の三つです。

 ここでは、三つのワークシートを一括して取り扱う方法を見てみます。

ネスカフェ

(1) ピボットキャッシュの設定

 ピボットキャッシュを生成するときは、少なくとも 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  ' 第1シートをアクティブに
    
    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形式で大丈夫なのですが……

目次に戻る


(2) 各種フィールドの内容

 これまで、行フィールド,列フィールド,データフィールド,そしてページフィールドを扱ってきました。

 それらフィールドは、ユーザーが設定したから生成された訳ですが、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番: 値」はデータフィールドです。

 こうしたフィールド群を目的に合わせて設定し直します。

目次に戻る


(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  ' 最右列の「早計」を非表示

 最後の1行は、集計表において一番右端に表示される「早計」を非表示にするためのものです。

 これがないと、「売上」と「仕入原価」を合算した値が「早計」として表示されます。

 でも、そんな値は意味ないので非表示にします。

 xlConsolidationを指定するマクロとスクリプトを別Webに掲載しておきます。

複数ワークシートの一括処理

目次に戻る


(4) ソースデータを部分的に参照

 先のVBAコードでは「商品」を分類の手がかりとして利用することができません(私が知らないだけで何か方法があるのかもしれませんが)。

 商品別×支店別の集計表を作りたい場合は、ソースデータのうち最も左の列にある「日付」を対象外にすればOKです。

 そうすると、「商品」が素材中で一番左に位置する形になり、xlConsolidation を指定したときに、「商品」が既定の行フィールドになります。

 ソースデータの2列目以降をエリアに指定したい場合は、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命令を併用するというのも一つの方法でしょうか。

 具体例を示さず、とってつけたようなコメントだけで申し訳ないのですが……

〜 以上 〜


※ 当ページに掲載した広告一覧