【Excel】加重平均を計算する方法!ExcelのSUMPRODUCT関数で重み付き平均を正確に求める

Excelで加重平均を計算したい場面は多いでしょう。例えば、成績評価で各科目の重要度(単位数など)を考慮して平均点を算出する場合です。単純な平均では各項目の重要度が均等に扱われてしまい、正確な評価ができなくなります。この記事では、ExcelのSUMPRODUCT関数を使って、加重平均を正確に計算する方法を解説します。

SUMPRODUCT関数を使えば、指定した範囲の積の合計を計算できます。この性質を利用することで、各項目の値とその重みを掛け合わせた値の合計を、重みの合計で割るという加重平均の計算を効率的に行えます。

SUMPRODUCT関数で加重平均を計算する仕組み

加重平均とは、各データにそれぞれ異なる「重み」を付けて計算される平均値です。例えば、あるテストの点数(値)と、そのテストの重要度(重み)があるとします。単純平均ではすべてのテストを同じ重要度で扱いますが、加重平均では重要度の高いテストほど平均値に与える影響が大きくなります。この重みを考慮した計算をExcelで行う際に、SUMPRODUCT関数が非常に役立ちます。

SUMPRODUCT関数は、指定された配列(範囲)の対応する要素同士を掛け合わせ、その積の合計を返します。加重平均の計算では、この「対応する要素同士を掛け合わせる」という機能が、値とその重みを掛け合わせる作業にそのまま適用できます。そして、「積の合計」が、各値に重みを掛けたものをすべて足し合わせたものに相当します。最後に、この合計値を重みの合計で割ることで、加重平均が求められます。

SUMPRODUCT関数を使った加重平均の具体的な計算手順

ここでは、具体的なデータを用いて、SUMPRODUCT関数で加重平均を計算する手順を解説します。例えば、以下のような成績データがあるとします。

A列:科目名、B列:点数(値)、C列:単位数(重み)

このデータを使って、単位数を考慮した加重平均を計算します。

1. データ範囲の確認
   加重平均を計算したい「点数(値)」の範囲と「単位数(重み)」の範囲を確認します。ここでは、点数がB2セルからB5セル、単位数がC2セルからC5セルとします。
2. SUMPRODUCT関数の入力
   結果を表示したいセル(例えばD2セル)に、以下の数式を入力します。

   =SUMPRODUCT(B2:B5,C2:C5)

   この数式は、B2*C2 + B3*C3 + B4*C4 + B5*C5 という計算を行います。

3. 重みの合計を計算
   次に、重み(単位数)の合計を計算します。別のセル(例えばD3セル)に以下の数式を入力します。

   =SUM(C2:C5)

4. 加重平均の算出
   最後に、SUMPRODUCT関数の結果(D2セル)を、重みの合計(D3セル)で割ります。D2セルに以下の数式を入力します。

   =D2/D3

   あるいは、上記3つのステップを1つのセルにまとめることも可能です。例えば、D2セルに以下の数式を入力すれば、一度の計算で加重平均が求められます。

   =SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5)

SUMPRODUCT関数で加重平均を計算する際の注意点と失敗例

SUMPRODUCT関数は非常に便利ですが、いくつか注意すべき点や、よくある失敗例があります。これらを理解しておくことで、より正確に加重平均を計算できるようになります。

範囲のサイズが一致しない場合

SUMPRODUCT関数は、指定された配列(範囲)の要素数が一致しない場合、#VALUE!エラーを返します。例えば、値の範囲が5行なのに重みの範囲が4行だと、正しく計算できません。

対処法: 数式を入力する前に、必ず値の範囲と重みの範囲の行数(または列数)が同じであることを確認してください。必要であれば、範囲を修正するか、データの欠損がないか確認してください。

重みとして0や負の値が含まれる場合

重みに0が含まれる場合、その項目の値は加重平均の計算に影響を与えなくなります。これは意図した動作であることが多いですが、もし重みが0になるべきでないデータに0が入っている場合は、値が正しくない可能性があります。

負の重みは、加重平均の概念としては一般的ではありません。もし負の値が含まれている場合は、その値が意図したものであるか、あるいは誤入力でないかを確認してください。負の重みは、計算結果を歪める可能性があります。

SUM関数とSUMPRODUCT関数の役割の混同

加重平均の計算式は「(値×重みの合計) ÷ (重みの合計)」です。SUMPRODUCT関数は「値×重み」の合計を計算する部分を担当し、SUM関数は「重みの合計」を計算する部分を担当します。この2つの関数の役割を混同し、例えばSUMPRODUCT関数で重みの合計まで計算しようとすると、意図しない結果になります。

対処法: 加重平均の計算式を正確に理解し、SUMPRODUCT関数とSUM関数を正しく使い分けることが重要です。数式を一度にまとめず、段階的に計算すると間違いが減ります。

Excelのバージョンによる制限

SUMPRODUCT関数は、Excel 2007以降のすべてのバージョンで利用可能です。そのため、Excel 2019やMicrosoft 365でも問題なく使用できます。ただし、配列数式として使用する場合の制限(例えば、数式入力後にCtrl+Shift+Enterで確定する必要がある場合など)は、Excelのバージョンや数式の複雑さによって異なることがあります。

Excel for Microsoft 365では、動的配列機能により、SUMPRODUCT関数が配列を返す場合でも、数式を1つのセルに入力するだけで結果が自動的に展開されるため、より直感的に扱えるようになっています。Excel 2019以前では、配列数式として確定する必要がある場合があることに注意してください。

加重平均の応用例

SUMPRODUCT関数を使った加重平均の計算は、様々なビジネスシーンで応用できます。以下にいくつかの例を挙げます。

成績評価

先述の例のように、学生の成績を評価する際に、単位数やテストの難易度などを重みとして加味して総合的な成績を算出できます。例えば、主要科目は単位数が多く、重要度が高いと判断されるため、その科目の点数が総合評価に与える影響が大きくなります。

商品単価の計算

同じ商品でも、仕入れ時期や仕入れ先によって単価が異なる場合があります。複数の単価で仕入れた商品の平均単価を計算する際に、仕入れ量(購入数)を重みとして加重平均を計算することで、より実態に近い平均単価を把握できます。

ポートフォリオの期待リターン

投資において、複数の金融商品(株式、債券など)に投資している場合、各商品の期待リターンと、ポートフォリオ全体に占める各商品の割合(重み)を用いて、ポートフォリオ全体の期待リターンを計算できます。これは、投資戦略の評価や意思決定に不可欠な情報となります。

在庫管理

複数の倉庫に保管されている商品の平均在庫単価を計算する際にも、各倉庫の在庫数量を重みとして加重平均を用いることで、全体の在庫価値を正確に把握できます。

XLOOKUP関数とSUMPRODUCT関数の組み合わせ

Excel for Microsoft 365やExcel 2021以降では、XLOOKUP関数が利用可能です。XLOOKUP関数は、指定した条件に一致する値を検索し、対応する別の列の値を返す関数です。このXLOOKUP関数とSUMPRODUCT関数を組み合わせることで、より複雑な条件に基づいた加重平均の計算が可能になります。

例えば、複数の部署があり、部署ごとに異なる科目(値)と単位数(重み)で成績評価をしたい場合などが考えられます。まずXLOOKUP関数で対象の部署の点数と単位数を取得し、その結果をSUMPRODUCT関数に渡すことで、部署ごとの加重平均を算出できます。

具体的な数式は、対象の部署名を検索値として、点数と単位数の範囲を指定し、それらをSUMPRODUCT関数で処理する形になります。

項目	SUMPRODUCT関数	XLOOKUP関数との組み合わせ
主な用途	指定範囲の配列の積の合計を計算し、加重平均を算出する	検索値に基づき、対応する値を検索して返す
加重平均計算への応用	値と重みの範囲を直接指定して計算する	複雑な条件や複数の検索条件に基づいて値と重みを動的に取得し、加重平均を計算する
Excelバージョン	Excel 2007以降	Excel for Microsoft 365, Excel 2021以降
柔軟性	範囲指定が固定	検索条件によって動的に範囲を指定できるため、より柔軟なデータ操作が可能

XLOOKUP関数は、VLOOKUP関数やHLOOKUP関数よりも強力で柔軟な検索が可能です。SUMPRODUCT関数と組み合わせることで、より高度なデータ分析が可能になります。

SUMPRODUCT関数は、Excelで加重平均を計算するための強力なツールです。この記事で解説した手順と注意点を理解することで、成績評価、単価計算、投資分析など、様々な場面で正確な加重平均を求めることができます。さらに、XLOOKUP関数との組み合わせにより、より複雑な条件に基づいた分析も可能になります。

Excelの機能を活用し、業務効率の向上とより精度の高いデータ分析を目指しましょう。

次は、SUMPRODUCT関数を使った他の応用例(条件付き合計など)を調べてみるのも良いでしょう。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】