=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5)は加重平均を求めます。B列の各点数にC列の重みを掛け、その積を足し、合計を重みの合計で割ります。
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Part | Score | Weight | Average | Result | |
| 2 | Homework | 85 | 20% | Weighted | 81.2 | |
| 3 | Quizzes | 78 | 30% | Plain AVERAGE | 80.75 | |
| 4 | Midterm | 72 | 20% | |||
| 5 | Final | 88 | 30% |
加重平均の成績は81.2ですが、ふつうのAVERAGEでは80.75になります。AVERAGEは20%の宿題を、30%の期末試験と同じだけ数えてしまうからです。期末試験の点数を変えると、宿題の点数を同じだけ変えたときより加重平均が大きく動きます。
ExcelにはWEIGHTED.AVERAGE関数がないので、SUMPRODUCTをSUMで割るのが定番の数式です。GoogleスプレッドシートにはAVERAGE.WEIGHTED(B2:B5,C2:C5)があります。
加重平均の数式のしくみ
SUMPRODUCTは2つの範囲を行ごとに掛け、結果を足します。作業列で書き出すと、積の列とそのSUMになります：
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Part | Score | Weight | Score x weight |
| 2 | Homework | 85 | 20% | 17.0 |
| 3 | Quizzes | 78 | 30% | 23.4 |
| 4 | Midterm | 72 | 20% | 14.4 |
| 5 | Final | 88 | 30% | 26.4 |
| 6 | Total | 100% | 81.2 | |
| 7 | Weighted average | 81.2 |
各項目は点数と重みの積を受け持ちます。85 × 20%は17.0、78 × 30%は23.4、という具合です。合計は81.2です。重みの合計が100%なので、ここではC6で割っても何も変わりませんが、そうでないときに数式を正しく保つのはこの割り算です。
重みの合計が100%にならない場合
重みは割合でなくてもかまいません。GPAは単位数で、平均価格は数量で重み付けします。重みのSUMで割れば、合計がいくつでも対応できます。
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Course | Grade points | Credits | Average | Result | |
| 2 | Math | 4 | 4 | Weighted GPA | 3.51 | |
| 3 | History | 3 | 3 | Plain average | 3.48 | |
| 4 | Biology | 3.7 | 4 | Total credits | 14 | |
| 5 | Art | 2.7 | 2 | |||
| 6 | Lab | 4 | 1 |
割り算がないと、数式はGPAではなく成績ポイントと単位数の積の合計、ここでは49.2を返します。割り算があれば、F2は14単位で重み付けしたGPAを表示します。4単位の科目は平均をその成績のほうへ引き寄せ、1単位の実験はほとんど動かしません。B6を2に変えて、F3と比べてF2がどれだけ少ししか変わらないかを見てください。
重みが合計ちょうど100%の割合なら、=SUMPRODUCT(B2:B5,C2:C5)だけでも同じ結果になります。それでも/SUM(...)は残しておいてください。いつか重みが変わって合計が105%になったとき、それがない数式は間違った結果を出し、シートのどこにもそれが表れません。
条件付きの加重平均
一部の行だけに重みを付けるには、SUMPRODUCTの中で条件を掛け、一致する重みをSUMIFで足します。下では、地域ごとの平均価格を売れた数量で重み付けしています。
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Price | Qty | Region | Average price | |
| 2 | North | Apple | $1.20 | 100 | North | $1.45 | |
| 3 | South | Pear | $1.50 | 40 | South | $1.36 | |
| 4 | North | Pear | $1.50 | 60 | |||
| 5 | South | Apple | $1.20 | 120 | |||
| 6 | North | Plum | $2.00 | 40 | |||
| 7 | South | Plum | $2.00 | 20 |
NorthはAppleを100個、Pearを60個、Plumを40個売ったので、平均価格は$1.45です。3つの価格の単純な平均よりもAppleの価格に近くなります。条件(A2:A7=F2)はNorthの行で1、それ以外で0なので、分子にはほかの行が何も加わらず、分母のSUMIFはNorthの数量だけを足します。
練習: 加重平均の価格
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Batch | Price | Qty | Average | Result | |
| 2 | Jan | $4.20 | 100 | Weighted price | ||
| 3 | Feb | $4.50 | 40 | |||
| 4 | Mar | $3.90 | 250 | |||
| 5 | Apr | $4.80 | 10 | |||
| 6 | May | $4.10 | 120 |
やってみよう: 同じ品物を5回に分けて違う価格で買いました。各回の数量で重み付けした、1個あたりの平均価格を計算してください。数式はF2に書きます。
加重平均が間違う原因
- 積のAVERAGE。 点数 × 重みの列に
=AVERAGE(D2:D5)を使うと、重みではなく行数で割るので、小さくて意味のない数になります。積のSUMを重みのSUMで割ってください。 - 重みではなく件数で割る。
=SUMPRODUCT(B2:B6,C2:C6)/COUNT(B2:B6)が正しいのは、すべての重みが1のときだけです。 - 範囲がそろっていない。
=SUMPRODUCT(B2:B6,C3:C7)は各値を次の行の重みと組にします。2つの範囲は同じ行で始まり同じ行で終わる必要があります。大きさが違うと#VALUE!が返ります。 - 重みが空白。 空の重みは0として数えられるので、その行は黙って除かれます。重みがないときに計算を止めたいなら、先に
=COUNTBLANK(C2:C6)で確かめてください。 - 平均の平均。 70点(10人)と90点(30人)の2つのクラスの平均を平均しても80にはなりません。クラスの人数で重み付けすると85になります。AVERAGEIFのページに、条件付きで同じ落とし穴があります。
よくある質問
Excelで加重平均を求めるには？
B列に値、C列に重みを入れ、=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5)を使います。SUMPRODUCTが各値に重みを掛けて結果を足し、重みの合計で割ることで平均になります。
重みの合計は100%でなければいけませんか？
重みのSUMで割るなら、その必要はありません。単位数3、4、2、1や、重み2、1、1でも同じように使えます。割り算をしない近道=SUMPRODUCT(B2:B5,C2:C5)だけは、重みの合計がちょうど100%である必要があります。
ExcelにWEIGHTED.AVERAGE関数はありますか？
ありません。Excelには加重平均の組み込み関数がないので、SUMPRODUCTとSUMの組み合わせが定番の数式です。GoogleスプレッドシートではAVERAGE.WEIGHTED(B2:B5,C2:C5)で同じことができます。
条件付きで加重平均を求めるには？
SUMPRODUCTに条件を加え、重みにはSUMIFを使います：=SUMPRODUCT((A2:A7="North")*B2:B7*C2:C7)/SUMIF(A2:A7,"North",C2:C7)。
