Excel で加重平均を計算する方法
Excel で加重平均を計算するには、各値に重みを掛け、その積を足し合わせ、重みの合計で割ります: =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)。通常の AVERAGE はすべての値を同じ重要度として扱うため、値ごとに重要度が違う場合は正しくありません。このガイドでは、数式、確認方法、よくあるミスを説明します。
1. 加重平均が必要な場合
項目の重要度が同じでないときに加重平均を使います。たとえば、試験が成績の 50% を占めるコース、1 つの製品が大量に売れた場合の平均価格、1 つの基準が特に重要なスコアなどです。値は 1 つの列にまとめ、その隣の列に重みを入れましょう。1 行に 1 項目ずつ入力します。
2. 数式
SUMPRODUCT は 2 つの範囲の対応するセル同士を掛け合わせ、その積を合計します。そのため、補助列は必要ありません。重みの SUM で割ると平均になります。重みは割合でも、点数でも、個数でもかまいません。割り算で尺度がそろうからです。
例:3 つの要素からなるコースの成績
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 項目 | 点数 | 重み | 加重平均 | |
| 2 | 試験 | 80 | 50 | 81 | |
| 3 | 課題 | 90 | 30 | ||
| 4 | 小テスト | 70 | 20 |
80 × 50 + 90 × 30 + 70 × 20 は 8,100 です。重みの合計 100 で割ると、結果は 81 になります。
3. 単純平均と比べる
同じ 3 つの点数の AVERAGE は 80 です。単純平均は小テストを試験と同じくらい重要なものとして扱うからです。加重平均の 81 のほうが高いのは、高得点の課題のほうが、低得点の小テストより重く扱われるためです。2 つの結果が同じになった場合は、重みがすべて同じになっていないか確認しましょう。
例:同じ点数の単純平均
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 項目 | 点数 | 単純平均 | ||
| 2 | 試験 | 80 | 80 | ||
| 3 | 課題 | 90 | |||
| 4 | 小テスト | 70 |
240 を 3 で割ると 80 です。重みは使われていません。
4. 重みを割合で入力する場合
重みが割合で、合計がすでに 100% になっている場合は、割り算を省略できます: =SUMPRODUCT(B2:B4,C2:C4)。ただし、よくわからない場合は割り算を残しましょう。重みの合計が 100 以外でも正しい答えになり、行を追加しても正しく計算されるからです。
5. 重みが数量の場合
平均価格を求める場合、重みは販売数量になります。価格を 1 つの列に、数量を次の列に入力し、同じ数式を使いましょう。これで 1 個あたりの実際の平均価格が求まります。価格の単純平均とは異なります。
よくあるミス
範囲の大きさが違う。 B2:B4 と C2:C5 の SUMPRODUCT は #VALUE! を返します。
修正: 両方の範囲の行数をそろえましょう。
割り算を省く。 =SUMPRODUCT(B2:B4,C2:C4) は、重みの合計が 1 でない限り、平均ではなく加重の合計です。
修正: 重みの SUM で割りましょう。
重みのセルに文字列が入っている。 SUMPRODUCT は文字列を 0 として扱うため、その項目が結果から静かに消えてしまいます。
修正: すべての重みが数値であることを確認し、COUNT で項目数を確かめましょう。
よくある質問
Excel には加重平均の組み込み関数はありますか?
いいえ。SUMPRODUCT を SUM で割るのが標準的な方法です。
重みの合計は 100 にする必要がありますか?
いいえ。重みの合計で割るので、どの尺度でもかまいません。5、3、2 は 50、30、20 と同じ結果になります。
空白のセルは無視できますか?
SUMPRODUCT では、空白の点数は 0 として扱われます。除外するには、先に行をフィルターするか、点数が空白のときに空になる補助列を使いましょう。
関連する関数
- AVERAGEAVERAGE は、数値または範囲の算術平均を返します。文字列と空白のセルは無視します。構文、例、結果の丸め、よくある間違いを説明します。.
- SUMSUM は、1 つの数式で数値、セル、範囲全体を合計します。構文、範囲と累計の例、そして SUM が 0 を返すことがある理由を説明します。.
- AVERAGEIFAVERAGEIF は、1 つの条件を満たす値の平均を返します。例えば、1 つの地域の平均注文額です。構文、例、よくある間違いを説明します。.
- SUMIFSUMIF は、1 つの条件を満たす値を合計します。例えば 1 つの地域の売上全体や、100 を超えるすべての金額です。構文、例、よくある間違いを説明します。.
加重平均 について詳しく
このガイドでは、SUMPRODUCT の数式と確認方法を説明しました。ここでは、それに入力する表の作り方、条件付きの加重平均、手計算で結果を確かめる方法を説明します。
数式がシンプルになるように表を作る
見出しを付けた上で、値と重みを 2 つの列に並べて入力しましょう。1 行に 1 項目ずつです。重みが割合で与えられている場合は、その形式のまま入力しましょう。両方の範囲は同じ形でなければならないため、中に空白行を入れないでください。後から項目を追加するときは、範囲の内側に行を挿入して、数式が自動で拡張されるようにしましょう。最後の行の下に挿入しても範囲は広がらないため、変更のたびに数式を確認しましょう。Excel のテーブルを使うと、行を末尾に追加したときに範囲が自動的に広がるので、この問題を解決できます。
1 つのグループの加重平均
1 つのカテゴリの行だけを平均するには、SUMPRODUCT の中で条件を掛けます。比較は TRUE または FALSE を返し、それを掛けると 1 と 0 になるため、他の行は 0 として数えられます。
=SUMPRODUCT((A2:A7="North")*B2:B7*C2:C7)/SUMPRODUCT((A2:A7="North")*C2:C7)手計算で結果を確認する
加重平均は、最小値と最大値の間に入らなければなりません。入らない場合は、重みが間違っています。2 つの行でテストしましょう。値 10 と 20、重み 1 と 3 の場合、(10 + 60) / 4 = 17.5 になります。重みが 3 倍の 20 のほうに近くなるためです。結果を単純な AVERAGE と比べると、重みがどれだけ効いているかがわかります。
[SUMPRODUCT のレッスン](/learn/sumproduct) と [統計の基礎](/learn/statistics-basics) で練習しましょう。