Excel 上手い集計表の作り方、セット商品の集計

Excelで購買データの集計をしています。

文房具で例えます。
最終的な集計結果は顧客ごとの集計（下記２）のみ見られれば良いです。

１）詰合せセットごとの合計金額
セットA 合計￥700　鉛筆￥50ｘ1、消しゴム￥100ｘ2、ボールペン￥150ｘ3
セットＢ 合計￥300　鉛筆￥50ｘ2、消しゴム￥100ｘ2
セットＣ 合計￥500　３色ボールペン￥450ｘ1、替え芯￥50ｘ1
……

２）顧客ごとの詰合せセット購買合計金額
顧客Ａ 合計￥3700　セットＡ￥700ｘ2、セットＢ￥300ｘ1、セットＣ￥1000ｘ2
顧客Ｂ 合計￥8000　セットＢ￥300ｘ10、セットＣ￥500ｘ10
……

最終的に２枚のシートで完結できれば理想です。
　シート１＝集計結果
　シート２＝データシート

今までは３枚のシートで行っていました。
　シート１＝集計結果（２）
　シート２＝集計結果（１）
　シート３＝データベース（１のみ）

良い方法あれば教えてください。

No.2 回答者： goomania 回答日時： 2021/12/30 16:46

EFA15ELさんの回答にあるように、

>別にひとつのシートに、「商品別価格表」「セット内訳」
>「販売データ」を記載したって良いんですけどね。
ということで、添付画像のようなシートを作ってみました。
添付画像①は商品セットの一覧表です。
商品名とその単価、個数を表示してU列にセット金額を算出しています。
画像ではU7に

=SUMPRODUCT(M7:P7:S7,N7:Q7:T7)

という数式を記述し、下方向へコピーしています。
添付画像➁は顧客の購入実績を記録した表です。A列「日付」B列「顧客名」C列「購入セット名」D列「購入数」E列「セット単価」F列「合計金額」として作成し、画像ではE33セルに

=VLOOKUP(C33,$K$6:$U$9,11,0)

という数式を記述し、下方向へコピーしています。
さらにF33セルに

=D33*E33

という数式を記述し、下方向へコピーしています。
その上で、この表をテーブルにしています。

このテーブルを範囲として、添付画像③のようにして、ピボットテーブルを作成したものが添付画像④の集計表です。
購入実績の表をテーブルにしているので、実績を追記し、ピボットテーブルの範囲を選択した上で右クリックして「更新」を行うと添付画像④の集計表が自動更新されます。
当然、ピボットテーブルの集計表のみ別シートにすることも可能です。

余談ですが、ご質問者の説明に、
>顧客Ａ 合計￥3700　セットＡ￥700ｘ2、セットＢ￥300ｘ1、
>セットＣ￥1000ｘ2
しいう部分がありますが、Cセットは500円なので、末尾の部分は
「セットC￥500×4」として合計\3700になるように個数を修正して作表してあります。

No.1 回答者： EFA15EL 回答日時： 2021/12/28 13:01

別にひとつのシートに、「商品別価格表」「セット内訳」「販売データ」を記載したって良いんですけどね。

おっしゃる形が理想なのであれば、データシートに「商品別価格表」「セット内訳」を記載すれば良いだけです。
おそらくは商品数の方が多いでしょうから、上部にセット内訳の表を余裕をもって作り、下部に商品別価格表を記載すればいい。

特に難しいとは思いませんが、どの辺で迷ってるんでしょう？