送料無料や返品可能な商品の割合を、分類別に求めるには？
送料無料・返品可能をそれぞれ数値1か0で記録し、分類別の平均から該当商品の割合を求めます。同じ商品が両方に当てはまるため、2つの割合の合計は100%を超える場合があります。
送料無料の商品と返品可能な商品は重なるので、それぞれの割合を別々に計算します。元データに該当する場合は1、該当しない場合は0を入力しておけば、その平均が商品の割合になります。2つの条件の該当件数を足して分母にする必要はありません。
1商品を1回だけ数える入力
架空の表の1は該当、0は非該当で、全商品の状態を確認済みです。注文ごとに商品が重複する表では人気商品の比重が大きくなるため、商品単位の一覧を用意します。未確認の状態を勝手に0にしないでください。
| 商品 | 分類 | 送料無料 | 返品可能 |
|---|---|---|---|
| P1 | A | 1 | 1 |
| P2 | A | 1 | 0 |
| P3 | A | 0 | 1 |
| P4 | A | 0 | 1 |
| P5 | B | 1 | 1 |
| P6 | B | 1 | 1 |
属性別に平均を追加する
- 商品属性を結果の基準に選び、「結果を整理」で「集計」を追加します。
- 「まとめる項目」で分類を選びます。最初の集計の「集計方法」を「平均」に変え、「項目」に送料無料、「結果の項目名」に送料無料フラグ平均を設定します。「集計を追加」を押し、返品可能の平均も追加します。こちらの「結果の項目名」には返品可能フラグ平均と入力します。
- 送料無料フラグ平均に固定の数値100を掛ける「計算」を追加し、「送料無料 (%)」とします。
- 返品可能フラグ平均も100倍して「返品可能 (%)」を作り、「適用」を押します。
Aは送料無料50%、返品可能75%
Aの4商品のうち2商品が送料無料、3商品が返品可能です。両方に該当する商品もあるため、50%と75%の合計が125%になっても誤りではありません。Bは2商品とも送料無料かつ返品可能なので、それぞれ100%になります。結果の50はすでに百分率に直した値です。さらに100を掛けないでください。
| 分類 | 送料無料フラグ平均 | 返品可能フラグ平均 | 送料無料 (%) | 返品可能 (%) |
|---|---|---|---|---|
| A | 0.5 | 0.75 | 50 | 75 |
| B | 1 | 1 | 100 | 100 |
空欄は平均から除かれ、属性ごとに分母が変わる場合があります。未確認状態の扱いを決めてから計算してください。原質問はピボットグラフも求めていますが、ここで作るのは割合の表までです。アプリがそのグラフを自動作成する手順ではありません。
自分のファイルで始めましょう
JOIN SHEETを使ってみる ↗Excel・CSVの作業はPCでご利用ください。
新しいタブで開くため、作業中のデータは置き換わりません。
サンプルの検証日:
出典
"clustered column" PivotChart using percentages instead of values - Excel 2010の質問を参考に、独自の架空データで手順を作成しました。
