Excelのピボットテーブルで数も考慮した値段の平均値を出したいのです

質問

Excelのピボットテーブルで数も考慮した値段の平均値を出したいのですが
自分自身で組んだピボットテーブルだと平均値が出せません。

商品A 50個 100円
商品B ・・個 ・・・円
商品A 20個 150円
商品A 20個 180円
商品C ・・個 ・・・円

のようなデータがあった場合、ピボットテーブルの集計の方法で平均を選ぶと
商品の数を考慮せず1行=1個でカウントしてしまうため商品Aの場合
(100円+150円+180円)÷3=143円となってしまいます。

本当は((50個×100円)+(20個×150円)+(20個×180円))÷(50+20+20)個=128円
と計算させたいのですがどのような集計方法を選べばよいのでしょうか。
よろしくお願いします。

回答No.2

end-u · ベストアンサー

ピボットテーブルの場合。

個数と単価を掛けた「金額」などのフィールドは無いですか？
そのデータが必要なので、無い場合は追加して数式を設定し、
それもピボットテーブルの元データ範囲に含めましょう。

ピボットテーブル作成後、エリア内を選択して、
ピボットテーブルツールバーから
[ピボットテーブル]-[数式]-[集計フィールド]。
「集計フィールドの挿入」ウィンドウが開きます。

「名前」に　平均単価　
「数式」に　= 金額 / 個数
（[フィールドの挿入]クリックorフィールド名ダブルクリックで挿入されます）
[OK]クリックです。
あとは[フィールドの設定]で表示形式など調整してくだい。

お礼

おっしゃるとおりにしたら見事思い通りになりました。
どうもありがとうございました！

回答No.1

noname#204879

「ピボットテーブル」を使わないので“別解”ということで…

添付図参照
D2: =B2*C2
G2: =ROUNDDOWN(SUMPRODUCT((A$2:A$10=F2)*(D$2:D$10))/SUMPRODUCT((A$2:A$10=F2)*(B$2:B$10)),0)

お礼

どうもありがとうございます。
ピボットテーブルを使わないときはおっしゃるようにしてみようと思います。