購入数量が違う商品の平均単価を正しく計算するには？
単価の単純平均では購入数量を反映できません。数量×単価を商品別に合計し、総数量で割る加重平均を画面の設定で作る方法です。
数量を反映した平均単価は、各取引の「数量×単価」を合計し、総数量で割って求めます。1個を10000で、9個を20000で買った場合、加重平均単価は19000です。2つの単価だけを平均した15000ではありません。JOIN SHEETでは計算・集計・計算の3段階で作れます。
商品ごとに数量を重みとして計算する
次の架空の仕入明細を使います。金額の単位は統一し、数量と単価は数値として読み込みます。P01とP02は別々に集計します。図はデータと計算の説明図で、実際の画面ではありません。
| 商品 | 数量 | 単価 |
|---|---|---|
| P01 | 1 | 10000 |
| P01 | 9 | 20000 |
| P02 | 2 | 5000 |
| P02 | 2 | 7000 |
掛け算 → 合計 → 割り算を設定する
- シートを結果の基準にして「結果を整理」に「計算」を追加します。「掛け算 · A × B」でAを数量、Bを単価、名前を「購入金額」にします。
- 下に「集計」を追加し「まとめる項目」を商品にします。購入金額の「合計」を「総金額」という名前で追加します。
- 同じ集計内の「集計を追加」で数量の「合計」を「総数量」として追加します。
- 集計の後に「計算」を加え「割り算 · A ÷ B」を選びます。Aは総金額、Bは総数量、名前は「平均単価」です。
- 計算 → 集計 → 計算の順番を確認し「適用」を押します。
P01は19000、P02は6000
| 商品 | 総金額 | 総数量 | 平均単価 |
|---|---|---|---|
| P01 | 190000 | 10 | 19000 |
| P02 | 24000 | 4 | 6000 |
P02は数量が等しいため単純平均と同じですが、P01では違いが出ます。平均だけでなく分子と分母も残すと検算しやすくなります。「比率 (%)」ではなく「割り算」を選んでください。比率は100倍した数値になるため別の結果です。
単価の空欄をそのまま集計してよいですか？
先に確認が必要です。単価が空欄だと購入金額も空欄になり、その金額は集計されないのに数量だけが総数量へ入る可能性があります。そのまま割ると平均単価を過小評価します。集計の前に欠損や不正な数値を調べ、計算対象の明細をそろえてください。
返品を負の数量にすると総数量が0になる場合もあります。0で割った結果は空欄で、単価0を意味しません。この例は税や送料の配賦を含めない購入単価の計算です。0と空欄の計算上の違いも確認してください。
繰り返しの作業を減らす
シートを接続・比較して、必要な結果を確認できます。
この記事はProプランの利用環境を前提としています。
JOIN SHEETを使ってみる ↗Excel・CSVの作業はPCでご利用ください。新しいタブで開くため、作業中のデータは置き換わりません。
サンプルの検証日:
出典
補助列が複雑になる場合の加重平均に関する質問 — Reddit r/excel
公開質問からテーマを選び、独立した仕入例を作りました。原文の数式を転載したものではなく、実際に選択できる計算と集計を組み合わせています。
