[Excel BI] ピボットテーブルで全体に対する％を計算する

ピボットテーブルを使いこなせたら、ものすごい時間短縮になり、仕事の結果も出る！と考えている人は多いと思います。ピボットテーブルが使えない原因のほとんどは「元データ」にあって、それで行き詰まる人が多い、というのはよく聞く話なんですが、逆に、もし元データに問題がなかったらすべてうまくいくのか？というと、実はそうでもありません。

やり方さえ知っていれば超時間短縮になるのに、といつも思うのがピボットテーブルの「全体に対する（占める）割合（％）の計算方法」です。ピボットテーブル作成までできているので、本当に「あとちょっと」なんです。

以下のようにピボットテーブルの外で計算しているワークシートを見ました。数式の =E2/$E$6 が総計に対する割合を計算しています。$は相対・絶対参照云々、、、ということを言いたいわけではないんです。

Excelの素晴らしいところは創意工夫と時間をかければ何とかなるところです。しかし、そのため知っていれば楽になる本来の使い方を知らないまま、無駄に時間をかけてしまうこともしばしばあります。（余談ですが、同じ結果が出る違うやり方があったり、追加された新機能で今まで苦労してたことが楽になった、など、経験者でもそれ知らなかった、なんてことはザラなので、知らないことを卑下することはありません！）

上記のやり方でレポート終了、今後そのワークシートを再利用しないのであれば、これでも全然問題ないのですが、例えば、製品Eが追加された、Bが無くなった、などのデータの追加・更新がはいった時は行数が変わるので計算式を調整する必要があります。1つや2つなら目で確認できますが、1000個ある内の20個くらいが入れ替わる、という状況では目で追うこともできません。それを関数やマクロでチェックする、、、なんていう斜め上の方向に行く前にピボットテーブルの機能を見直してください。めんどくさいなぁ、と思うことはだいたい実現されています。

ピボットテーブルで列に対する割合を計算する

上の図のように総計 30 を 100% として、各項目 A, B, C, D の占める割合 % を出したいパターンは使うケースがかなり多いと思います。とても簡単なので是非覚えてください。

ポイントはセルの書式設定と一緒で「データの見せ方を変えてあげる」なんです。

通常のデータと、割合のデータの2つが必要なので、以下のように個数の列を2つ作ります。

個数のフィールドを同じように値のボックスに何個でもドロップすることで同じ列を作成できます。次にこのように作った「合計/個数2」の列を％の見せ方に変えます。その列にあるセルの上で右クリックをして、右クリックメニュー（コンテキストメニュー）を表示します。

上のメニューの下から4番目にある「計算の種類」を選択します。

ここで「うぁ、、何選べかいいかわからない」となりそうですが、列に対する比率を計算するので、「列集計に対する比率」か「親列集計に対する比率」のいずれかです。とりあえず、「行集計に対する比率」を選んでください。

あとは、セルの書式設定で小数点以下の桁数を調整してもよし、値フィールドの設定から表示形式を調整し小数点以下の桁数を1などにすれば以下のようになります。

いろいろな計算オプションがあるのは、いろいろなケースに対応するためです。たとえば、以下のようなピボットテーブルの場合はどうでしょう。

単純に総計の 30 に対して占める割合であれば、先ほどの「列集計に対する比率」でOKです。でも、だいたいビジネスの世界では以下のように「キッチン用品」とか「庭用品」で小計をしたりします。同じように事業部毎だったり、リージョン・地域ごとだったりします。
(せっかくなので、その小計の出し方を含めてアニメーションGIFにします）

別の計算の種類を選ぶことで、カテゴリ（キッチン用品、庭用品）を100%にして各項目の割合を出すこともできます。いろいろ試してみてください。

忙しいビジネスパーソンこそピボットテーブルを習得すべき

ビジネスパーソンのための、、、というお題で極論すれば、Excelは関数、ましてやマクロやVBAを覚える前に、ピボットテーブルだけを覚えれば、大体の計算はできます。なぜならビジネスの基本のデータは「表」形式であり、表をベースにした計算が求められるからです。

Excelの機能としての「テーブル」、それらを成形・集計するピボットテーブル、さらにクラウドやサーバーからデータを取りこむExcelの「取得と変換」、グラフはExcelの機能としてはかなり複雑で難しいのですが、それでも最新機能で必要なところだけ押さえることでかなりのことができると思います。

あまた機能を内蔵しているExcelはその学習コストがあまりに高い、大きいです。よりに自分に必要な機能を厳選して、それらの機能習得に時間やお金をかけることが望まれます。

何らかの参考になれば幸いです。

過去の投稿ですが、知識としてはまだ使えますのでお時間があればどうぞ参照ください。

[Excel BI]ピボットテーブルで予算と実績を管理する
https://road2cloudoffice.blogspot.com/2019/09/excel-bi.html

[Excel] テーブルのすすめ　ピボットテーブルとリレーションシップ

https://road2cloudoffice.blogspot.com/2014/11/blog-post_7.html

[Excel] テーブルのすすめ　集計行
https://road2cloudoffice.blogspot.com/2014/10/blog-post_31.html