ピボットテーブルに「集計アイテム」を追加することで、ちょっと高度な使い方ができるようになります。
「集計アイテム」を使うと、あるフィールドに項目単位で集計したオリジナルなアイテムを追加できます。「集計フィールド」はピボットテーブルの列側だけに展開する機能ですが、それに対して「集計アイテム」は「行」「列」双方向で使える機能です。「集計アイテム」を使うと、項目名も数式に組み込むことができ、より高度な集計が可能になります。項目同士で計算したり、特定の項目だけをピックアップして集計の対象にすることができるのです。本記事では、同種の項目の総計に対し、個別の項目ごとの構成比を表示させる例題で、集計アイテムを使ったピボットテーブルの応用テクニックを解説していきます。
フィールドに集計アイテムを追加する
下図の集計表の中で、「WEB制作」に係る項目の総計に対し、「スタンダード」「プレミアム」「格安」の各プランごとの構成比を表示させたいと思います。
まず、集計アイテムを追加したいフィールド、及びその項目の一つを選択します。
この最初の手順は重要です。集計値のセルを選択した状態では機能自体を呼び出せません。
「商品」というフィールドに集計アイテムを追加したいと思うなら、上図の赤枠で囲った範囲内のセルを選択しておく必要があります。
上述したセルを選択した状態で、「ピボットテーブル ツール」ー「分析」の「計算方法」グループにある「フィールド/アイテム/セット▼」をクリックして「集計アイテム」をクリックします。
「”商品”への集計アイテムの挿入」と題したダイアログボックスが開き、左側の「フィールド」では「商品」が選択され、その配下のアイテムが右側のボックスに一覧表示されています。
ここでの作業は「名前」を入力することと「数式」を組み立てることです。
「総計」の集計アイテム
- 最初に比率の基になる「WEB制作」の総計を集計アイテムとして追加します。ピボットテーブルに表示される項目の「名前」を入力します。この例では「WEB制作｜総計」としておきました。
- 次に、「数式」の小窓に入っている「０」を消去、「=」の後にカーソルを置いた状態で「WEB制作・スタンダード」をダブルクリックするか、選択して「アイテムの挿入」ボタンをクリック。アイテム名が数式に表示されたら、「+」を入力します。
- その後に続けて「WEB制作・プレミアム」をダブルクリック、「+」を入力、「WEB制作・格安」をダブルクリック。
「WEB制作・スタンダード+WEB制作・プレミアム+WEB制作・格安」という式が入りました。※「= SUM(WEB制作・スタンダード,WEB制作・プレミアム,WEB制作・格安)」としてもOK - これで「WEB制作」の３つのプランを加算する数式が成立したので、「追加」ボタンをクリックします。
「追加」をクリックすると「アイテム」のリストが空になりますが、「フィールド」の「商品」を再度選択すると表示されます。※表示されない時は、他のフィールド名を何度かクリックしてみて下さい。 - 「アイテム」のリストを一番下までスクロールすると、「WEB制作｜総計」が追加されたことが確認できます。
「構成比」の集計アイテム
ダイアログは閉じずに、このまま次の作業へ移ります。
- 「名前」と「数式」には先程の入力値がまだ残っているので、これを「=」を除いて削除し、「WEB制作｜総計」に対する「スタンダード」の構成比を表示させる集計アイテムを新たに設定します。
- 「名前」を「スタンダード｜構成比」とし、「数式」の「=」の後にカーソルを置いて、「アイテム」の「WEB制作・スタンダード」をダブルクリックして入力、続けて「/」、更に続けて新規追加した「WEB制作｜総計」をダブルクリックします。「数式」に「=WEB制作・スタンダード/’WEB制作｜総計’」と入りました。
- ここで、ひとまず「OK」してみましょう。設定した２つの集計アイテムが行ラベルの末尾に表示されました。
- 構成比の値の表示形式を「％」に変えましょう。数値上で右クリック→メニューの「値フィールドの設定」ではなく、「セルの書式設定」→「表示形式」で「パーセンテージ」を選びます。「値フィールドの設定」で表示形式を変更すると、金額の列の値が全て「％」になってしまうので要注意。
- 最終行の「総計」は邪魔なだけなので非表示にしておきましょう。「ピボットテーブル ツール」ー「デザイン」の「レイアウト」グループにある「総計▼」のプルダウンメニューから「行と列の集計を行わない」をクリックします。
- 「プレミアム」と「格安」も同様の操作で集計アイテムを追加します。分かりやすくするため、追加した４行の集計アイテムに塗り潰しと罫線を適用しました。
- 他の項目も同種の「キャラクター」と「ロゴ」に分けて、「WEB制作」と同じ手順で集計アイテムを追加しました。
- このままでもいいかもしれませんが、せっかくなので同種ごとにグループ化して表の体裁をもう一段整えてみましょう。「WEB制作」関連を、追加した集計アイテム（総計・構成比）も含めて選択し、「ピボットテーブル ツール」ー「分析」にある「グループの選択」をクリックします。
- 「キャラクター」、「ロゴ」関連も同様にグループ化。
- グループ１～３の名前を修正して、WEB制作の書式を他の２グループにコピペ、罫線の追加などを適宜行って完了です。
クロス集計表に集計アイテムを追加する
列と行の２軸に配置したフィールドそれぞれに集計アイテムを追加して、一つのピボットテーブルで２方向からの分析を可能にする提案例です。企画書などで少し小洒落たコンパクトな見せ方をしたい時に参考にして下さい。
- 「ピボットテーブルのフィールド」作業ウィンドウで、列エリアに「担当」フィールドを配置します。
- 「担当」フィールドか項目の一つを選択して、「ピボットテーブル ツール」ー「分析」の「計算方法」グループにある「フィールド/アイテム/セット▼」を押して「集計アイテム」をクリックします。
- 「”担当”への集計アイテムの挿入」ダイアログボックスが開いたら、担当者全員の売上総計の「名前」と「数式」を入力します。この手順は「商品」に追加した集計アイテムと同じです。「名前：担当の総計、数式：=担当者A +担当者B +担当者C」。入力が完了したら、「追加」ボタンをクリック。
- そのままダイアログボックスを閉じずに、続けて「構成比」の名前と数式を入力。「名前：A構成比、数式：=担当者A/担当の総計」
- 「追加」を押したら、続けて「B構成比」入力→「追加」、「C構成比」→入力、「OK」。これで列ラベルの集計アイテムも全て表示できました。
- この時点では何が何だかわからない集計表になってます。意味のない集計値も目につきますね。この見栄えを最小限の書式で整えていきましょう。「総計」の範囲を色分けして目立たせるとともに、項目別の売上金額と構成比を隔てる仕切り線の役割を持たせます。罫線も適用してみましたが、この辺りは好みの範疇で。
- 構成比同士を足し算させてるような邪魔な集計値を隠します。不要な集計セルは表の右下の一角に並んでいます。
これらを全て選択（Controlキーを押しながらドラッグ）して、右クリック。カラーパレットで文字色を白（背景色と同色）にします。
- これで完成です。
クロス集計表に集計アイテムを追加する場合の一例としてご参照下さい。
集計アイテムの追加でピボットテーブルの応用範囲を広げましょう
集計アイテムを活用すると、ピボットテーブルの応用範囲が広がります。仕事で分かりやすい集計表を作成するのに役立つテクニックです。少し高度な使い方になりますが、慣れればスムーズに作業できるでしょう。
本記事では担当者別の集計を例にしましたが、集計アイテム機能で商品別、月別、支店別など主に行方向に展開する集計の追加が簡単にできることが解っていただけたと思います。背景色や罫線で見やすい集計表にするテクニックも併せて参考にしていただけると幸いです。