【手軽でミス無し】加重平均をエクセルのピボットテーブルで計算する方法

仕事で何等かのデータ分析する場合には、加重平均を計算するケースがあると思います。

加重平均を求める際には、計算ソフトとしてエクセル/Excelを用いる事が多いのではないでしょうか？
加重平均を計算する際には、SUMPRODUCT関数とSUM関数を組み合わせて使うことが一般的に知られています。
しかしながら、加重平均値を計算する際にSUMPRODUCT関数を用いると、
加重する対象データ群を手動で変更する必要があり、手間がかかる上にミスも発生しやすいという問題があります。

個人的に致命的だと思うのは、「面倒でやる気が起きない」ということです。

そんな加重平均計算を面倒だと思っている方へ朗報です。実は、簡単に手間なく加重平均を計算する別の方法があります。

それは、エクセルのピボットテーブルを使って加重平均を計算する方法です。

その方法について紹介しまので、是非ご覧ください。

ピボットテーブルを用いた加重平均の算出方法

加重平均を求める例を次の手順に沿って説明します。

1.　データの準備
2.　ピボットテーブルで加重平均を計算

1.　データの準備

まず、データを準備します。
簡単な例として、ある果物屋の2日間の売上データを用意しました。

加重平均を求めるために最低限必要な項目は、品目/数量/金額の3つです。
それ以外の項目は分析の切口として、適宜用意してください。
今回の例は、「2019/1/2に高級品の20％引き販促セールを実施した果物屋」の売上分析となります。

2.　ピボットテーブルで加重平均を計算

次にピボットテーブルを作成し、加重平均を計算します。

データ範囲を選択した状態で、「挿入タブ → ピボットテーブル」をクリックします。

そのまま「OK」を選択し、新しいシートにピボットテーブルを作成します。

ピボットテーブルで、「品目」を「行欄」にドラッグし、分析したい項目を表示させます。
加重平均を求める計算式を追加するために、「分析タブ → フィールド/アイテム/セット → 集計フィールド」をクリックします。

集計フィールドに次のように入力します。

名前：加重平均
数式：金額/数量　　※フィールドの挿入ボタンを使用します。

そしてOKを押します。
この作業によって、加重平均売価である「ぶどうの合計売上金額 ÷ ぶどうの合計数量」を計算できるようになります。

フィールドに追加された「加重平均」を「Σ値」にドラッグします。
その結果、
2019/1/1~2019/1/2の2日間における、ぶどうの加重平均売価、みかんの加重平均売価、りんごの加重平均売価、果物屋全体の加重平均売価の4つが計算されます。

あとは、切口を追加して加重平均売価の動きを追えば分析が充実します。
例えば、「日付」を列に追加し、「分類1」を行に追加するしてみます。
分析してみると、
「果物屋全体としては、平均売価を上昇させることが出来ている。
ぶどうとりんごは、高級品の値下げをしても平均売価を上昇させることが出来たが、
みかんは、高級品の値下げをして平均売価も低下させてしまった。」
という事が分かります。
分析結果からは、「みかの平均売価を低下させないためには、みかんは値下げ率を10％くらいに抑えても良かったのではないか？」という次へのヒントが得られます。
※何を狙うかによって、分析からのヒントは変わります。

まとめ

以上、エクセルのピボットテーブルを用いて、手軽にミスなく加重平均を算出する方法を紹介しました。

ピボットテーブルで加重平均を簡単に捉えられるようになると、加重平均を構成する中身の部分もドリルダウンして深堀していく事が出来るようになります。
つまり、「戦略をどのように変化させれば、加重平均を変化させられるのか？」ということのイメージを掴むことが出来るので、
「新たな戦略を考え、実行し、効果測定し、戦略を改善するというPDCAサイクル」を回す事が出来るようになります。

私の場合では、セールスミックス、プロダクトミックス分析を行う際に、セグメント別や品目別の加重平均売価・加重平均原価・加重平均利益を算出し、商品戦略の立案に役立てています。

一度やり方を覚えれば、それ以降は簡単に出来るになります。
ピボットテーブルでの加重平均算出方法をマスターして仕事に役立ててみてください。

膨大なデータの多面的な分析結果＆提案を涼しい顔で提示し、周囲を驚かせましょう。