ピボットテーブルの集計アイテムの不都合について

Question

ピボットテーブルについて質問させていただきます。

Excel は 2003 を使用しています。

「集計アイテム」を利用したピボットを作成したのですが、期待通りに作成できず、２点の問題がありました。

★データ


★ピボットの設定

行エリア：項目

集計アイテムの追加：単価=IF(数量=0,0,金額/数量)

列エリア：分類１、分類２、（データ）

データエリア：予測、実績

表示名称の変更：「合計 / 実績」を「_実績」になど。

★ピボットの結果

（見る人の要望から、ピボットのレイアウトは変更できません。）


★問題点

1. 単価（集計アイテム）の合計欄が、各計算結果が足し算された値になる（赤で囲ったところ）。
2. 存在しない組み合わせが表示される（黄色で囲ったところ）。

１についてですが、もしある分類に複数の子分類があると、折りたたんだ際の単価の表示も正しくありません。

このレイアウトにするためには、予算と実績を「データ」にする必要があったので、単価は「集計アイテム」を利用しています。

たぶんエクセルの仕様としては正しい結果だとは思うのですが、今回の作表結果としては大問題です。

これまで単価のような項目には「集計フィールド」を使用していましたが、その場合、合計欄の各値を使って計算が行われましたので、期待する値にできていました（文末の）。

２についても、集計アイテムを使用するとこうなってしまうようです。

今回作成している実際のピボットでは列エリアの項目（集計キー）がもっと多いので、余分な列がとっても多くなってしまいます。

対策として DataRange のすべての値がゼロか空白の場合、ピボットアイテムの Visible を False にする、という VBA コードで対応してみたのですが、列数がエクセルの制限を超える数になった場合は非表示にするまでにエラーになってしまいそうです。

（Excel のバージョンは 2003 に限定された環境です・・）

集計アイテムに設定する式を変更するだけで済むような対処があればうれしいのですが、解決策はないでしょうか？

ピボットでは無理かなとあきらめかけています・・・

ちなみに、もともとは次のようなデータのレイアウトでピボットを作成していて問題がなかったのですが、ここに今回予算を追加することになったというのがイキサツです。

（こちらの単価は「集計フィールド」で作っています。）


Answer 1

集計アイテムを使うのではなく、単純に以下のような式をB9セル（単価の右のセル）に入力して右方向にオートフィルすればすべて解決するのではないでしょうか？

=IF(B8="","",B7/B8)

Answer 2

TanTonさん　こんにちは

問題点２だけの回答ですが、データが無いのに表示されるのは、表示するように設定しているからだと思います。

ピボットテーブル上で、右ボタンでメニュー表示し、「フィールドの設定」を選択するとダイアログが開きますので、[データのないアイテムを表示する]のチェックを外せば表示されなくなると思います。

Answer 3

石田さんありがとうございます、そのイメージです。

エクセルのままで、詳細の展開・折りたたみ機能も利用したいです。

それなりに調べた結果、無理そうかもと思っているのですが、ダメもとで質問させていただきました。

Answer 4

TanTon さん、こんにちは。

こちらですが、最終的な出力イメージとしては以下でよろしいでしょうか。（もし違っていたら、その旨、ご返信ください）

こちら、どなたかよい方法を知っているという方がいましたら、ぜひご投稿よろしくお願いします。