ピボットテーブルの備忘録 ④ 集計フィールド
先日から、マクロによるピボットテーブルの扱いを纏めている(備忘録)。
先日は、作成したピボットテーブルにフィルターを設定してみた。
infoment.hatenablog.com
今日は、マクロによる集計フィールドの追加に挑戦する。
まず初めに断っておくと、集計フィールドの追加は、意図しない結果となる恐れがあるため要注意だ。また、敢えてマクロでやる必要があるか、という問題もある。それを踏まえたうえで、以下に進む。
今回のサンプル。
ピボットテーブルで集計し、品名を行にセットする。
この状態で、合計金額を集計したい。しかし元の表に、合計金額の列は無い。
この場合、「集計フィールドの追加」という方法がある。
↓ここから、
↓このように設定。
すると、合計金額がセットされる。
しかし、何やら金額がおかしい。実はこのケースでは単価まで合計されてしまい、実際より高い金額になっているのだ。
例)商品Aの単価
- 正: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
例えば「分」を「時間」に変換する場合などに、集計フィールドは有効と思う。
明日に続きます。
参考まで。