Excel。何らかの重み付けした平均である「加重平均」を算出する関数ってあるの？
＜SUMPRODUCT関数・SUM関数＞
「平均」といえば、AVERAGE関数と思うかもしれませんが、平均には色々な種類の平均があります。
オートSUMボタンにある、お馴染みのAVERAGE関数は、平均を算出しますが、単なる平均ではなくて、「算術平均」とか「相加平均」といったりします。
Excelには、
上位と下位から一定の割合を除外した平均を算出する、TRIMMEAN関数。
成長率などの倍率の平均を算出する、GEOMEAN関数。
時速の平均などの単位当たりの数値の平均を算出する、HARMEAN関数。
というように、色々な平均を算出する関数が用意されているのです、残念ながら、何らかの重み付けした平均である「加重平均」を算出するための関数はありません。
なぜ、加重平均が必要なのか、次の表を使って確認していきましょう。
ある商品の販売価格と販売数の表です。
仙台店は、セール期間ということで割引して販売したところ、割引の効果もあって、販売数が伸びているのがわかります。
そして、B列の販売価格の平均値を算出するにあたり、お馴染みの平均を算出するAVERAGE関数をつかって、B6に算出してみたのがこの表です。
販売価格の平均ではあるのですが、販売数は全く考慮していません。
しかしよく考えてみると、
新宿店の販売金額は、1500×477= 715500
仙台店の販売金額は、 700×1587 1110900
平均は、合算値をそのデータ件数で除算するわけです。
ということから、販売数を考慮したほうが合理的ですね。
安い販売価格の販売数は、高い販売価格よりも多いので全体としては、平均値は下がるはずです。
このような時につかうのが、「加重平均」です。
「加重平均」を算出するには、販売数を重み付けして、販売価格に積算してからその総和を販売数の総和で除算することで算出することができます。
このことから、今回使用する関数は、SUMPRODUCT関数とSUM関数で算出していきます。
B7に「加重平均」を算出していきます。
最初に、販売価格に積算してからその総和をするために、SUMPRODUCT関数をつかった数式を作っていきます。
そして、SUM関数で販売数の合算値で除算するわけですから、
B7の数式は、
=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5)
とします。
算出結果は、1052。
AVERAGE関数で算出した数値よりも、予想通り下がる結果となりましたね。
ご覧のように、加重平均を一発で算出するための関数は今のところありませんが、SUMPRODUCT関数とSUM関数を使うことで、算出することができますので、会議資料などで加重平均が必要な時には、覚えておくと便利かもしれませんね。
最近いろいろな関数が追加されているので、もしかしたら、登場するかもしれませんね。