今回は、ピボットテーブルの値フィールドの集計方法に関するお話です。
"ピボットテーブルのフィールド" というのがよくわからない、という方は こちら をご覧ください。
基本的な話
数値データのみが格納されている列をもとに [値] エリアに値フィールドを作成すると、既定で "合計" の集計方法が適用されるため、ピボットテーブルにはその値フィールドの合計値が表示されます。
1 つでも文字列データのセルが含まれている列をもとに [値] エリアに値フィールドを作成した場合は、既定で "個数" の集計方法が適用され、ピボットテーブルにはその値フィールドのセルの個数が表示されます。
既定の動きをうまく使って効率よくピボットテーブルで集計をしたいのなら、データ ソースの列の値の種類は統一しておくべきです。
数値が入るべき列には数値のみを、日付を入れるべき列には日付のみを格納するように整えることが望ましいです。
ただし、文字列が混ざっているからといって、その他の数値の合計を集計できないわけではないので、集計方法の変更について次にご紹介します。
個数が算出された場合の集計方法の変更
合計を算出したい値フィールドで個数が算出されてしまった、というときは、おそらくデータ ソースの該当列に文字列 (またはシリアル値による日付) が混在しています。データ ソースを修正せずに値フィールドの集計方法を変更することで対応する場合は次のように設定します。
-
個数が算出されている値フィールドを選択し、[値フィールドの設定ダイアログ ボックス] を表示します。
-
[集計方法] タブの [値フィールドの集計] で [合計] を選択して [OK] をクリックします。
-
選択している値フィールドの集計方法が合計に変更されます。
このとき、文字列が含まれているセルは計算から除外されています。
[値] エリアに複数の値フィールドを作成する
下図の [値] エリアには、[合計 / 金額] フィールドが 1 つだけ配置されていますが、単価を知らないユーザーがこれを見たとき、1 回の販売でその金額になっているのか、複数回 (だったら何回？) の販売でその金額になっているのかがわかりません。
このような場合に、[値] エリアに個数 (=販売回数) を算出するフィールドも作成してあげるとわかりやすいでしょう。
-
[金額] 列の名前を [値] エリアにドラッグして、2 つ目の値フィールドを作成します。(個数がわかればよいので、[金額] 列でなくてもよいですが、ここではあえて同じ列を元にしています。)
-
[値] エリアに 2 つ目の値フィールドが作成され、合計値が算出されます。
-
作成した 2 つ目の値フィールドの集計方法を変更します。
個数が算出されている値フィールドを選択し、[値フィールドの設定ダイアログ ボックス] を表示します。
-
[集計方法] タブの [値フィールドの集計] で [個数] を選択して [OK] をクリックします。
-
選択している値フィールドの集計方法が個数に変更されます。集計方法の変更に合わせてフィールド名も [個数 / 金額2] に変更されます。
[計算の種類] を追加して比率を求める
値フィールドの [集計方法] で指定している集計方法によって得られた結果 (合計値) が、総計に対してどのくらいの比率なのかを表示したり、集計結果の順位を表示したりするには、値フィールドに [計算の種類] を追加します。
たとえば下図の A4ノート の 1,500 円という合計値は、総計の 53,200 円の何パーセントなのかを算出したい、ということです。
[値] エリアに、[金額] 列をもとにした 3 つ目の値フィールドを作成して合計を算出し、さらに「総計に対する比率」を追加して割合を求めます。
-
[金額] 列の名前を [値] エリアにドラッグして、3 つ目の値フィールドを作成します。
-
[値] エリアに 2 つ目の値フィールドが作成され、合計値が算出されます。
作成した 3 つ目の値フィールドを選択し、[値フィールドの設定ダイアログ ボックス] を表示します。
-
[集計方法] タブで [合計] が指定されていることを確認し、[計算の種類] タブで [総計に対する比率] を選択して [OK] をクリックします。
-
選択している値フィールドの集計方法 (合計) に、計算の種類 (総計に対する比率) が追加されて割合が表示されます。
値フィールドの名前は、セルを選択して編集できます ([値フィールドの設定] ダイアログ ボックスなどでも編集可)。
チェック ボックスを使ってピボットテーブル フィールドを作成する
フィールド リストの [フィールド セクション] のチェック ボックスを利用すると、データ ソースの値の種類によって自動的にピボットテーブル フィールドが作成されます。たとえば、チェック ボックスをオンにした列に数値データのみが格納されている場合は、自動的に [値] エリアに値フィールドが作成されます。
データ ソースの列に文字列データや日付データが 1 つでも含まれている場合は [行] エリアにピボットテーブル フィールドが作成されます。
3つ目の列のチェック ボックスをオンにしたからといって、[列] エリアにピボットテーブル フィールドは作成されません。データ ソースの値の種類に応じて、[値] エリアまたは [行] エリアにフィールドが作成されます。
ということは、[列] エリアや [フィルター] エリアにフィールドを配置したい場合は、ドラッグ アンド ドロップでピボットテーブル フィールドを作成するか、またはチェック ボックスのオンによって作成されたフィールドを移動することで対応しなければいけません。
ピボットテーブルにおいてデータ ソースは非常に重要です。データ ソースが整っていれば既定で機能する設定がいくつもあります。
とはいえ業務で使うデータは意外と混在が常態化していたりしますよね。。。少なくともデータ ソースの値の種類によって動きが変わることや、対応方法 (フィールドの設定) を知っておく必要があるでしょう。
石田 かのこ
