Exploratory アワー #438 - ピボットテーブル：集計前または後のデータを使って合計値を計算したい

例えば、集計テーブルやピボットテーブルを使ってグループごとに平均値を計算したときに、合計として「全体の平均値」を求めたいこともあれば、「グループごとの平均値」の「平均値」を求めたいことがあります。

そこで合計値を計算するときに、集計の目的に合わせて、集計前後のどちらのデータを利用するかを指定する方法を紹介いたします。

問題

例えば、グループごとに平均値を計算したときに、「全体の平均値」を求めたいこともあれば、グループごとの平均値の「平均値」を求めたいことがあります。

集計テーブル/ピボットテーブルのの合計値の計算には、「集計前データ」と「集計後データ」の2つのオプションがあり、「集計前データ」あるいは「集計後データ」のどちらのデータを使って合計値を集計するかを指定できます。

今回は以下の2つのデータを使用します。

従業員データ

1行が1人の従業員を表し、年齢、性別、職種、マネージャー区分、給料などの情報を列に持ちます。

新規顧客の売上

1行が1人の新規顧客を表し、注文日、顧客セグメントなどの情報を列に持ちます。

まずは、従業員データを使って、マネージャー区分と性別で給与の平均値を集計するピボットテーブルを作成します。

チャートビューに移動し、ピボットテーブルを作成します。

行に「マネージャーかどうか」、列に「性別」を選択し、値に「給料」の平均値を設定します。

例えば、女性の従業員に注目したとき、合計オプションメニューから「集計前データ」を選択すると全女性の給料の合計値（平均値）が計算されます。

このとき、合計オプションメニューから「集計後データ」を選択することで、集計後の値を使用した合計値（平均値）が計算されます。

例えば、女性の給料の平均値に注目すると、集計後のデータを使って平均値を計算しているので、以下の計算をもとに平均値が計算されることになります。

10687 = (16184 + 5190) / 2

続いて、新規顧客の売上データを利用して、月次の新規顧客数の累積値を計算します。チャートビューに移動し、ピボットテーブルを作成します。

行に「注文日」を選択し、単位を月に設定し、列に「顧客セグメント」を選択します。

続いて、値に「（行の数）」を選択し、値のメニューから「クイック表計算」を選択し、「累積合計値」を選択します。

この時点では集計前データが使用されているため、合計行に表計算適用前の元の値が表示されます。

例えば、2月の新規顧客数が個人94人、法人57人の場合、2月の合計は151人と表示されます。

これは累積計算前の値であり、実際の累積合計（個人214人、法人115人の合計329人）とは一致しません。

そこで、合計オプションメニューで「集計後データ」を選択することで、累積値に基づいた正しい合計値が表示されます。