期末試験だけの重み付き平均（SUMPRODUCT）
問題
問題文
成績表 A2:C5 には小テストと期末試験が混在し、それぞれに重みがあります。求められているのは1つだけ：**Final の行だけ**の重み付き平均です。Final grade のセル E2 に入力してください。小テストの重みで下がってはいけません。
分母は「絞り込んだ重みの合計」にします。すべての重みで割ると、ケース1 は 86 ではなく 52 になります。
問題のデータ
表でデータ全体を見る(選択・コピーできます)
| A | B | C | E | |
|---|---|---|---|---|
| 1 | Type | Score | Weight | Final grade |
| 2 | Final | 90 | 0.3 | |
| 3 | Quiz | 70 | 0.3 | |
| 4 | Final | 80 | 0.2 | |
| 5 | Quiz | 88 | 0.2 |
ポイント解説
(A2:A5="Final") のような比較は TRUE/FALSE を返し、SUMPRODUCT の中では 1/0 として扱われます。条件を掛ければ必要な行だけが残ります。分母も同じ条件でなければなりません。一部だけを平均するときは重みを再正規化する必要があります。AVERAGEIF ではできません。あれは値を平均するもので、重みの列を使えません。
解き方のヒント
回答は E2 に入力します。送信時は 3 通りの入力で数式を再実行するので、数値を直接書くと必ず失敗します。数式はセルを参照してください。
- 回答セルは E2 です
- A2:A5 が
Finalの行だけが対象です - SUMPRODUCT の中の比較は 1/0 になるので、そのまま掛けます
- 形は SUMPRODUCT((条件)*点数*重み) / SUMPRODUCT((同じ条件)*重み) です
模範解答
模範解答を表示(2 通り・まずは自分で試してみましょう)
解法1：条件を掛け込む
=SUMPRODUCT(($A$2:$A$5="Final")*$B$2:$B$5*$C$2:$C$5)/SUMPRODUCT(($A$2:$A$5="Final")*$C$2:$C$5)
条件によって小テストの行は分子でも分母でも 0 になるので、計算は期末試験だけを見ます：(90×0.3 + 80×0.2) ÷ (0.3 + 0.2) = 86。
解法2：カンマ区切りと -- の変換
=SUMPRODUCT(($A$2:$A$5="Final")*$B$2:$B$5,$C$2:$C$5)/SUMPRODUCT(--($A$2:$A$5="Final"),$C$2:$C$5)
SUMPRODUCT はカンマで複数の配列を受け取れます。二重のマイナスで TRUE/FALSE を 1/0 に変えてから重みを掛けます。同じ答えで、条件が増えたときに読みやすくなります。
よくある質問
SUMPRODUCT は A2:A5="Final" のような比較をどう扱いますか？
比較結果を 1 と 0 に変えます。掛ければ一致する行だけが残り、他は 0 になります。だから1つの式で絞り込みと計算の両方ができます。
分母を期末試験の重みだけにするのはなぜですか？
平均するのは一部だけだからです。期末試験の重みは全体の 0.5 なので、1.0 で割ると答えが小さくなります。同じ条件の重みで割ることで再正規化されます。86 と 43 の違いがこれです。
AVERAGEIF ではだめなのですか？
AVERAGEIF は値を平均するだけで、2列目の重みを掛けられません。重み付き平均には「点数 × 重み」の積が必要で、それを SUMPRODUCT が掛けてくれます。