ピボットで、総計の値が求めている値を表示してくれない

質問

ピボットを作成したときに、行や列の総計の値が本来の値と異なる場合があります。このような時どのように対処したら宜しいのでしょうか。

＜例＞
映画館　収容人数　観客数　占有率
A館　　　100人　　　10人　　10.0％
B館　　　150人　　　20人　　13.3％
C館　　　200人　　　30人　　15.0％
D館　　　250人　　　40人　　16.0％

全館の占有率は、
（全観客数：100人）÷（全収容人数：700人）＝14.3％

しかしピボットでの表示は、14.3％とは異なる値になってしまいます。
【合計】で表示すると54.3％となり、
【平均】を使ったとしても、全占有率を足し上げて単純に4で割った値となります。

どうすれば、（全館客数）÷（全収容人数）での値が表示されるのでしょうか。
よろしくお願いします。

回答No.4

noname#204879 · ベストアンサー

[No.3回答に対するお礼]に対するコメント、

「＜例＞のそのものが元データ」として、ピボテを組んでみます。占有率はピボテの方で作成するので「元データ」から削除しておきます。「元データ」の範囲を A1:C5 とします。簡単のために「100人」、「10人」等は、それぞれ 100、10 等の無名数値としておきます。
列フィールドに[映画館]を、データフィールドに[収容数]および[観客数]を配置して、次のピボテを得たところから始めます。
　　　　　　映画館　データ　　　 合計
　　　　　　A館　　 合計/収容数　 100
　　　　　　　　　　合計/観客数　　10
　　　　　　B館　　 合計/収容数　 150
　　　　　　　　　　合計/観客数　　20
　　　　　　C館　　 合計/収容数　 200
　　　　　　　　　　合計/観客数　　30
　　　　　　D館　　 合計/収容数　 250
　　　　　　　　　　合計/観客数　　40
　　　　　　全体の合計/収容数　　 700
　　　　　　全体の合計/観客数　　 100

１．ピボテ内の任意のセル、あるいは、ピボテ全体を選択しておいて、[ピボットテーブル]ツー
ルバーの[ピボットテーブル]→[数式]→[集計フィールド]を実行
→[集計フィールドの挿入]メニューが出現！

２．[フィールド名]ボックス内の“フィールド1”を“占有率”に上書き

３．[数式]ボックス内の“= 0”全体を選択した後、
　　３.１．= を入力
　　３.２．[フィールド]窓内の“観客数”を選択して[フィールドの挿入]をクリック
　　　　　 →[数式]ボックス内が“= 観客数”に変化
　　３.３．/ を入力
　　　　　 →[数式]ボックス内が“= 観客数/”に変化
　　３.２．[フィールド]窓内の“収容数”を選択して[フィールドの挿入]をクリック
　　　　　 →[数式]ボックス内が“= 観客数/収容数”に変化

４．[追加]→[OK]を実行すると、ピボテが次のように変化
　　　　　　映画館　データ　　　 合計
　　　　　　A館　　 合計/収容数　 100
　　　　　　　　　　合計/観客数　　10
　　　　　　　　　　合計/占有率　 0.1
　　　　　　B館　　 合計/収容数　 150
　　　　　　　　　　合計/観客数　　20
　　　　　　　　　　合計/占有率　0.13
　　　　　　C館　　 合計/収容数　 200
　　　　　　　　　　合計/観客数　　30
　　　　　　　　　　合計/占有率　0.15
　　　　　　D館　　 合計/収容数　 250
　　　　　　　　　　合計/観客数　　40
　　　　　　　　　　合計/占有率　0.16
　　　　　　全体の 合計/収容数　　700
　　　　　　全体の 合計/観客数　　100
　　　　　　全体の 合計/占有率　 0.14

５．ピボテ上の[データ]フィールドボタンをマウスでドラッグして、その右隣の[合
　　計]のセル上にドロップするとピボテが次のように変化
　　　　　　　　　　データ
　　　　　　映画館　合計/収容数　合計/観客数　合計/占有率
　　　　　　A館　　　　　　 100　　　　　 10　　　　　0.1
　　　　　　B館　　　　　　 150　　　　　 20　0.133333333
　　　　　　C館　　　　　　 200　　　　　 30　　　　 0.15
　　　　　　D館　　　　　　 250　　　　　 40　　　　 0.16
　　　　　　総計　　　　　　700　　　　　100　0.142857143

６．ピボテの「合計/占有率」の列を選択して %書式に設定すれば次のピボテが完成
　　　　　　　　　　データ
　　　　　　映画館　合計/収容数　合計/観客数　合計/占有率
　　　　　　A館　　　　　　 100　　　　　 10　　　　10.0%
　　　　　　B館　　　　　　 150　　　　　 20　　　　13.3%
　　　　　　C館　　　　　　 200　　　　　 30　　　　15.0%
　　　　　　D館　　　　　　 250　　　　　 40　　　　16.0%
　　　　　　総計　　　　　　700　　　　　100　　　　14.3%

お礼

ご連絡が遅くなり申し訳ございませんでした。

回答を頂く上で、色々と試行錯誤して頂き大変ありがとうございました。非常に感謝しております。

改めまして、ありがとうございました！

回答No.3

noname#204879

妙手はないものかと試行錯誤しているのですが、その前に…
「例に関しては、他の項目・データを省略しかなり簡素化させて表示致しました」とのことですが、どういう元データから「例」のピボテ（特に「収容人数」のところ）が作成できるのか、どうしても思い浮かびません。そこのところを教えてくれませんか？

お礼

説明不足で、ご迷惑をお掛けしてすみません。

＜例＞のそのものが元データで、ここからピボットを組むことを想定しております。
しかもこの例自体、質問用として、私自身が作ったものです。

私が本来扱っているデータは、Web関連で使用するクリック率だとか、平均コストといった類です。
どうしても総計の部分で、私の思うような値にならなかったので、その方法を知りたく質問致しました。
皆さんに比較的馴染みのあるデータをと思い作成したことが、かえって混乱を招き申し訳ございませんでした。

上記の回答でご理解頂ければ幸いです。

回答No.2

cockerel

＞行や列の総計の値が本来の値と異なる場合があります。
お気に障るかも知れませんが、ピボットは正しい計算をしています。
占有率として示された値の『合計』が、
（全観客数：100人）÷（全収容人数：700人）＝14.3％
となるべきと考えるのは、「占有率はこうあるべき」と考える人間だけであって、機械は正直に「占有率」という欄に示された値を合計してしまいます（ザインとゾルレンの境界に寛容なのは人間だけです）。「率」という漢字があるからといって機械がその値の合計の計算方法を変更することはありません。事実、「全体の占有率」ではなく「全体の合計：占有率」と表示されています。
機械が人間の希望までもくみ取って計算を行うことができる時代がくるまでは、希望する結果が得られるように相当の定義を行う必要があるようです。

ピボットでお望みの結果を得たいのであれば、たとえばピボットテーブルをコピー＆ペーストした後、全体の合計：占有率の欄の該当セル内に式を書き込めば、見た目はご希望どおりになります。

ところで、例として示された表は、例示のみを目的としたものとは存じますが、ピボットを使用する意味がないように感じられます。

なお、表現等で不愉快な思いをさせた場合にはお許しください。

お礼

ご回答ありがとうございました。

ピボット上では、私が求めている計算を行なうのは難しいのですね。これが分かっただけでも、ここ2、3日の胸のつかえが取れた気がして良かったです（ただ、まだどこかにミラクルな答えが出てくることを祈ってはいますが・・・）。

回答欄にご記入頂きました様に、ピボットをコピー＆ペーストしてセル内に式を書き込んでいくようにします。

例に関しては、他の項目・データを省略しかなり簡素化させて表示致しました。

ご回答ありがとうございました。

回答No.1

keirika

質問の14.3%を出すためには

全館　700人　100人　14.3%

をデータに追加し、ピボット作成後にピボットテーブルオプションにて総計チェックボックスをはずし、総計を非表示にする以外ないと思うのですが。

質問の回答にはならないかもしれませんが、参考までに。

お礼

ご回答ありがとうございました。

全館データを組入れるのも手ですかね。
ただ、他に多くの項目データを追加した時には、
それぞれに対しても項目ごとに合計を入れておく必要になりますね。

たとえば、地域という項目を入れて
A館・B館は、新宿。C館・D館は、渋谷とした時、
A館・B館の合計データ、C館・D館の合計データも
入れなくてはいけないですね。
そうすると、どこかでデータの抜け落ちが出てきそうで少し恐いです。

しかし、参考になりました。
ありがとうございました。