ピボットテーブルの備忘録 ④ 集計フィールド

先日から、マクロによるピボットテーブルの扱いを纏めている（備忘録）。
先日は、作成したピボットテーブルにフィルターを設定してみた。
infoment.hatenablog.com

今日は、マクロによる集計フィールドの追加に挑戦する。

まず初めに断っておくと、集計フィールドの追加は、意図しない結果となる恐れがあるため要注意だ。また、敢えてマクロでやる必要があるか、という問題もある。それを踏まえたうえで、以下に進む。

今回のサンプル。

ピボットテーブルで集計し、品名を行にセットする。

この状態で、合計金額を集計したい。しかし元の表に、合計金額の列は無い。
この場合、「集計フィールドの追加」という方法がある。
↓ここから、

↓このように設定。

すると、合計金額がセットされる。

しかし、何やら金額がおかしい。実はこのケースでは単価まで合計されてしまい、実際より高い金額になっているのだ。
例）商品Ａの単価

• 正：100円
• 誤：200円（レコード二つ分の単価が合計されている）

この場合は、予めテーブル側でレコード毎に合計金額を求めておき、それをピボットテーブル側で求める方が良いと思う。


※例示のため今回、単価は平均値を表示し、それ以外は合計を表示している。

以上を踏まえたうえで、先日からのクラスにメソッドとして追加してみた。

' 集計フィールド追加。
Public Function SetCalculatedField(added_name As String, _
                                   added_formula As String) As Excel.PivotField
    Pvt.CalculatedFields.Add added_name, added_formula
    Set SetCalculatedField = Pvt.PivotFields(added_name)
    SetCalculatedField.Orientation = xlDataField
End Function

例えば「分」を「時間」に変換する場合などに、集計フィールドは有効と思う。

明日に続きます。

参考まで。